Data Warehousing and OLAP Queries: Database Systems

Desklib provides solved assignments and past papers to help students succeed.

Contents
1: Designing Data Warehouse....................................................................................................2
Data Dictionary......................................................................................................................2
ER Diagram............................................................................................................................7
2: OLAP Queries........................................................................................................................8
References..................................................................................................................................9
List of Figures
Figure 1: Table Customers.........................................................................................................4
Figure 2: Table ScoreInfo.........................................................................................................4
Figure 3: Table StockInfo..........................................................................................................4
Figure 4: Table TradesStockInfo...............................................................................................5
Figure 5: Customers...................................................................................................................6
Figure 6: ScoreInfo....................................................................................................................6
Figure 7: StockInfo....................................................................................................................6
Figure 8: TradesStockInfo..........................................................................................................6
Figure 9: ER Diagram................................................................................................................7
Figure 10: Output 1....................................................................................................................8
Figure 11: Output 2....................................................................................................................8

1: Designing Data Warehouse
Data Dictionary
Entity Customers
Column Data Type Null Constraints
C_ID Number No PK
C_Fname Varchar (40) No
C_Lname Varchar (40) No
C_Contact Integer (11) No
C_Address Varchar (40) No
C_Age Integer (11) Yes
(Jukic, et al., 2016)
Entity StockInfo
Column Data Type Null Constraints
Stock_Id Integer (11) No PK
Stock_name Varchar(40) No Check constraint
Entity ScoreInfo
Column Data Type Null Constraints
S_ID Integer (11) No PK
S_Number Integer (11) No Check constraint
S_Commission Integer (11) No

Entity TradeStockInfo
Column Data Type Null Constraints
T_ID Integer (11) No PK
T_Date Date No
T_Type Varchar(40) No
C_ID Integer (11) No
Stock_Id Integer (11) No
S_ID Integer (11) No
Acc_No. Varchar(40) No
(Goswami, et al., 2016)

Table creation:
Figure 1: Table Customers
Figure 2: Table ScoreInfo
Figure 3: Table StockInfo

Figure 4: Table TradesStockInfo

Populating Data:
Figure 5: Customers
Figure 6: ScoreInfo
Figure 7: StockInfo
Figure 8: TradesStockInfo

ER Diagram
Figure 9: ER Diagram

2: OLAP Queries
1. Select count (*) as Records from StockInfo.Customers
INNER JOIN StockInfo.TradesStockInfo on Customers.C_ID = TradesStockInfo.C_ID
WHERE CONCAT (Customers.C_fname, Customer.C_Lname) = 'JohnAlan' AND
TO_CHAR (TradesStockInfo.T_Date ,'MM') = 07;
Figure 10: Output 1
2. Select SUM (ScoreInfo.S_ Commission) as total_charges
from ScoreInfo inner join TradesStockInfo
on ScoreInfo.S _ID = TradesStockInfo.S _ID
Where (ScoreInfo.S_Number = 4 or ScoreInfo.S_Number = 5)
AND TO_CHAR (TradeStockInfo.T_Date,'YYYY')=2003; (Pisano, et al., 2016)
Figure 11: Output 2

References
 Pisano, V.I., Risi, M. and Tortora, G., 2016, September. Exploiting CoDe modeling for the
optimization of OLAP queries. In 2016 Eleventh International Conference on Digital
Information Management (ICDIM) (pp. 62-67). IEEE.
 Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases
and data warehouses. Prospect Press.
 Goswami, R., Bhattacharyya, D.K., Dutta, M. and Kalita, J.K., 2016. Approaches and issues
in view selection for materialising in data warehouse. IJBIS, 21(1), pp.17-47.