Information Systems Program
Module 1
Overview of Relational Database
Support
Lesson 4: Relational Database Schema
Patterns
Lesson Objectives
• Review schema patterns
• Understand data warehouse used in ungraded
exercises
• Understand data warehouse used in graded
assignments
Information Systems Program
Star Schema Example
Store
Item StoreId
ItemId StoreManager
ItemName StoreStreet
ItemUnitPrice StoreCity
ItemBrand StoreSales StoreState
ItemCategory StoreZip
StoreNation
ItemSales DivId
Sales DivName
SalesNo
DivManager
SalesUnits
SalesDollar
Customer SalesCost
TimeDim
CustId TimeNo
CustName TimeSales TimeDay
CustPhone TimeMonth
CustStreet CustSales TimeQuarter
CustCity TimeYear
CustState TimeDayOfWeek
CustZip TimeFiscalYear
CustNation
3
Information Systems Program
Constellation Schema Example
Supplier
SuppId Inventory
SuppName InvNo
SuppCity SuppInv InvQOH StoreInv
SuppState InvCost
SuppZip InvReturns
SuppNation
ItemInv
Store
Item StoreId
ItemId StoreManager
ItemName StoreStreet
ItemUnitPrice StoreCity
ItemBrand StoreSales StoreState TimeInv
ItemCategory StoreZip
StoreNation
ItemSales DivId
Sales DivName
SalesNo
DivManager
SalesUnits
SalesDollar
Customer SalesCost
TimeDim
CustId TimeNo
CustName TimeSales TimeDay
CustPhone TimeMonth
CustStreet CustSales TimeQuarter
CustCity TimeYear
CustState TimeDayOfWeek
CustZip TimeFiscalYear
CustNation
4
Information Systems Program
Extended Constellation Schema
Item sales,
Subscription
sales, Inventory
cost
TimeDim
Supplier Store
Format Item
Package Customer
Lead
5
Information Systems Program
Matrix for a Constellation Schema
Facts
Dimensions
Product Sales Subscription Sales Inventory
Store X
Item X X X
Customer X X
Lead X
Package X
Format X
Supplier X
TimeDim X X X
Information Systems Program
Snowflake Schema Example
Item
ItemId Store
ItemName StoreId
ItemUnitPrice Division
StoreManager
ItemBrand DivId
StoreStreet
ItemCategory
StoreSales DivStore DivName
StoreCity
DivManager
StoreState
ItemSales Sales StoreZip
SalesNo StoreNation
SalesUnits
SalesDollar
Customer SalesCost
TimeSales TimeDim
CustId TimeNo
CustName TimeDay
CustPhone TimeMonth
CustStreet CustSales TimeQuarter
CustCity TimeYear
CustState TimeDayOfWeek
CustZip TimeFiscalYear
CustNation
Information Systems Program
Oracle Diagram for the Store Sales DW
Information Systems Program
Inventory Lifecycles
Work Order Sales Purchasing
Life Cycle Life Cycle Life Cycle
Work Order Header Sales Order Entry Purchase Order Entry
Work Order
Pick List Generation Purchase Order Transfer
Generation
Shipment Purchase Order Receipt
Issue/Scrap Parts
Sales Update Create Voucher
Time Reporting
Inventory
Work Order Completions Transactions
Perpetual
Inventory Adjustments
Inventory
Manufacturing Accounting Master DB
Inventory Transfers
Perpetual Inventory
Transactions
Simple Issues
Inventory
Reclassifications
9
Information Systems Program
Oracle Diagram for the Inventory DW
10
Information Systems Program
Summary
• Understand schema patterns for query formulation
• Store sales data warehouse for practice problems
• Inventory data warehouse for graded problems
• Course documents for both data warehouses
11
Information Systems Program
