Data Warehousing Assignment 1

Data Warehousing
Assignment 1

P a g e | 1
Table of Contents
Task 1: Data Warehouse Design............................................................................................................3
Data Dictionary..................................................................................................................................3
ER Diagram........................................................................................................................................4
Design queries...................................................................................................................................5
Table data..........................................................................................................................................7
Task 2: OLAP Queries.............................................................................................................................8
References.............................................................................................................................................9
Figure 1: ER diagram..............................................................................................................................4
Figure 2: customer table........................................................................................................................5
Figure 3: score table..............................................................................................................................5
Figure 4: stock table..............................................................................................................................5
Figure 5:stock_trade..............................................................................................................................6
Figure 6: customer.................................................................................................................................7
Figure 7: Stock_trade.............................................................................................................................7
Figure 8: Score.......................................................................................................................................7
Figure 9: stock.......................................................................................................................................7
Figure 10: output 1................................................................................................................................8
Figure 11: Output 2...............................................................................................................................8

P a g e | 2
Task 1: Data Warehouse Design
Data Dictionary
Customer table
Column name Data Type Null Constraints comments
Customer_customer_id Number no PK
Customer_customer
_fname
Varchar2(100) no
Customer_customer
_lastnam
Varchar2(100) no
Customer_customer
_contactnumer
Number no
Customer_customer
_address
Varchar(200) no
Customer_customer_age Number yes
Score_details table
Column name Data Type Null Constraints comments
Score_score_id Number no PK
Score_score_number Number no Check constraint
score lies
between 1 and 5
Score_score_commision Number no
Stock_details table
Column name Data Type Null Constraints comments
_stock_Stock_id Number no PK
Stock_Stock_name Varchar2(100) no Check constraint
stock_name can
only be ibm
Stock_Trade
Column name Data Type Null Constraints comments
Trade_trade_id Number no Primary key
Trade_trade_date date no
Trade_Trade_type Varchar2(20) no
Cust_id Number no
Stock_id Number no
Score_id Number no
Account_number Varchar2(50) no