Data Warehouse Design and OLAP Queries for Stock Exchange Data

This document presents a data warehouse design and implementation, focusing on OLAP queries. It includes a data dictionary defining the structure of tables such as Customer, Score, Stock, and Stock_Trade. An ER diagram illustrates the relationships between these entities. The design incorporates SQL queries for data retrieval and analysis. Sample data is provided for each table. OLAP queries are demonstrated to extract insights, such as counting records for specific customers and calculating total commission charges based on score numbers and trade dates. Desklib offers a range of similar solved assignments and past papers for students.

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.