Data Warehousing Assignment 1

This assignment covers data warehouse design including data dictionary, ER diagram, queries, and table data. It also includes OLAP queries and references for further study.

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

P a g e | 3
ER Diagram
Figure 1: ER diagram

P a g e | 4
Design queries
Customer_details table
Figure 2: customer table
Score_details table
Figure 3: score table
Stock_details table
Figure 4: stock table
Stock_trade table

P a g e | 5
Figure 5:stock_trade

P a g e | 6
Table data
customer_details
Figure 6: customer
Stock_trade
Figure 7: Stock_trade
Score_details
Figure 8: Score
Stock_details
Figure 9: stock

P a g e | 7
Task 2: OLAP Queries
Query 1
select count(*)AS Records from stock_exchange.customer_details INNER JOIN
stock_exchange.stock_trade on customer_details.customer_customer_id = stock_trade.CUST_ID
WHERE
CONCAT(customer_details.customer_customer_fname,customer_details.customer_customer_lastna
me)='JohnAlan' AND TO_CHAR(stock_trade.trade_trade_date,'MM')=07
Figure 10: output 1
Query 2
select SUM(score_details.score_score_commission) as total_charges from score_details INNER JOIN
stock_trade on score_details.score_score_id=stock_trade.score_id Where
(score_details.score_score_number=4 or score_details.score_score_number=5) AND
TO_CHAR(stock_trade.trade_trade_date,'YYYY')=2003
Figure 11: Output 2

P a g e | 8
References
 Subramanian, S., Brierley, S., & Jozsa, R. (2018). Implementing smooth functions of a
Hermitian matrix on a quantum computer. arXiv preprint arXiv:1806.06885.
 Medina, J. M., Barranco, C. D., Pons, O., & Sanchez, D. (2017, July). Building and evaluation
of indexes for possibilistic queries on a fuzzy object-relational database management system.
In Fuzzy Systems (FUZZ-IEEE), 2017 IEEE International Conference on (pp. 1-6). IEEE.
 Takahashi, D., Xiao, Y., & Li, T. (2018, June). Database Structures for Accountable Flow-Net
Logging. In 2018 13th APCA International Conference on Control and Soft Computing
(CONTROLO) (pp. 254-258). IEEE.