NIT6160 Data Warehouse and Mining Assignment 1: Design and Queries

This assignment details the design and implementation of a data warehouse for an online brokerage company, focusing on the creation of a Trades fact table and associated dimensions such as Date, Customer, Account, Security, and TradeType. The project involves setting up the data warehouse using SQL Developer, creating tables for Customers, Trades, and Commissions, and inserting sample data. Furthermore, the assignment includes the development and execution of OLAP queries to analyze trade data, specifically targeting trades placed in July by a specific customer and commission earned per quarter based on customer profit scores. The document provides SQL code snippets, screenshots of the implementation process, and references used in the project.

1
Data Warehouse And Mining
DATA WAREHOUSE AND MINING
[Student name]
[University name]
[Professor Name]
[Date]

2
Data Warehouse And Mining
Table of Contents
1 Task 1: Data Warehouse Design...............................................................................................3
1.1 Design................................................................................................................................3
1.2 Implementation.................................................................................................................3
1.2.1 Start SQL developer application................................................................................3
1.2.2 Create new connection..............................................................................................4
1.2.3 Give the connection name as “datawarehouse”.......................................................4
1.2.4 Connect connection...................................................................................................5
1.2.5 Test the connection....................................................................................................5
1.2.6 Save connection.........................................................................................................6
1.2.7 Creating tables...........................................................................................................7
1.2.8 Inserting records in the database............................................................................11
1.2.9.......................................................................................................................................13
2 Task 2: OLAP Queries.............................................................................................................13
2.1 Trades placed in July by “John Alan”..............................................................................13
2.1.1 SQL codes.................................................................................................................13
2.1.2 Results......................................................................................................................13
2.2 Commission earned each quarter of 2003 for customer with 4/5 profit scores..............13
2.2.1 1st quater.................................................................................................................13
2.2.2 SQL codes.................................................................................................................13
2.2.3 Results......................................................................................................................13
2.2.4 2nd quarter................................................................................................................13
2.2.5 SQL codes.................................................................................................................13
2.2.6 Results......................................................................................................................14
2.2.7 3rd quarter................................................................................................................14
2.2.8 SQL codes.................................................................................................................14
2.2.9 Results......................................................................................................................14
3 Reference...............................................................................................................................15

3
Account table
Accountid
Customerid
Tradeid
Transactiondate
Securitydetails
Scores
Date
Customer
Account
Security
Tradetype
Dimensions Fact table Dimensions
Data Warehouse And Mining
1 Task 1: Data Warehouse Design
1.1 Design
Implementation
1.2 Implementation
1.2.1 Start SQL developer application

4
Data Warehouse And Mining
1.2.2 Create new connection
1.2.3 Give the connection name as “datawarehouse”

5
Data Warehouse And Mining
1.2.4 Connect connection
1.2.5 Test the connection

6
Data Warehouse And Mining
1.2.6 Save connection

7
Data Warehouse And Mining
1.2.7 Creating tables
1.2.7.1Customer table
1.2.7.1.1 Sql code
1.2.7.1.2 CREATE TABLE customers
1.2.7.1.3 (
1.2.7.1.4 Customerid INT NOT NULL
1.2.7.1.5 , Customername VARCHAR2(20)
1.2.7.1.6 , Telephone VARCHAR2(20)
1.2.7.1.7 , Locations VARCHAR2(20)
1.2.7.1.8 , CONSTRAINT customers_PK PRIMARY KEY ( Customerid )
1.2.7.1.9 );
1.2.7.1.10Screen shots

8
Data Warehouse And Mining
Customerid
Customername
Telephone
Location
1.2.7.2Trade table
1.2.7.2.1 Sql code
CREATE TABLE trade
(
Tradeid INT NOT NULL
, DESCRIPTION VARCHAR2(20)
, Month VARCHAR2(20)
, Year INT
, Customerid INT
, CONSTRAINT trade_PK PRIMARY KEY ( Tradeid )
,CONSTRAINT fk_trade FOREIGN KEY (Customerid)REFERENCES customers (Customerid)
);

9
Data Warehouse And Mining
1.2.7.2.2 Screen shots
Tradeid
Description
Month
Year
Customerid

10
Data Warehouse And Mining
1.2.7.3Commission table
1.2.7.3.1 Sql code
CREATE TABLE commision
(
Commissionid INT NOT NULL
, DESCRIPTION VARCHAR2(20)
, Amount INT
, Score INT
, Yearquarter INT
, Customerid INT
,Tradeid INT
, CONSTRAINT commision_PK PRIMARY KEY ( Commissionid )
,CONSTRAINT fk_customers FOREIGN KEY (Customerid)REFERENCES customers (Customerid)
,CONSTRAINT fk_trades FOREIGN KEY (Customerid)REFERENCES trade (Tradeid)
); (Tycho, 2014)
1.2.7.3.2 Screen shots

11
Data Warehouse And Mining
(Dick, 2018).
1.2.8 Inserting records in the database
1.2.8.1Inserting Customer table
SQL codes
INSERT INTO "SYS"."CUSTOMERS" (CUSTOMERID, CUSTOMERNAME, TELEPHONE, LOCATIONS)
VALUES ('1', 'John Alan', '[phone]', 'USA')
INSERT INTO "SYS"."CUSTOMERS" (CUSTOMERID, CUSTOMERNAME, TELEPHONE, LOCATIONS)
VALUES ('2', 'paul james', '[phone]', 'USA')
INSERT INTO "SYS"."CUSTOMERS" (CUSTOMERID, CUSTOMERNAME, TELEPHONE, LOCATIONS)
VALUES ('3', 'ann esther', '[phone]', 'INDA')
Screen shots

12
Data Warehouse And Mining
1.2.8.2Inserting Trade table
1.2.8.2.1 SQL code
INSERT INTO "SYS"."TRADE" (TRADEID, DESCRIPTION, MONTH, YEAR, CUSTOMERID) VALUES ('1',
'sale', '1', '2003', '1')
INSERT INTO "SYS"."TRADE" (TRADEID, DESCRIPTION, MONTH, YEAR, CUSTOMERID) VALUES ('2',
'sale', '5', '200', '2')
INSERT INTO "SYS"."TRADE" (TRADEID, DESCRIPTION, MONTH, YEAR, CUSTOMERID) VALUES ('3',
'buy', '7', '2003', '3')
INSERT INTO "SYS"."TRADE" (TRADEID, DESCRIPTION, MONTH, YEAR, CUSTOMERID) VALUES ('4',
'sale', '7', '2003', '1') (Michael, 2017)
1.2.8.2.2 Screen shot
(Specialist, 2018)
1.2.8.3Inserting Commission table
SQL CODES
INSERT INTO "SYS"."COMMISION" (COMMISSIONID, DESCRIPTION, AMOUNT, SCORE,
YEARQUARTER, CUSTOMERID, TRADEID) VALUES ('1', 'PAID COMISION', '100', '1', '1', '1', '1')
INSERT INTO "SYS"."COMMISION" (COMMISSIONID, DESCRIPTION, AMOUNT, SCORE,
YEARQUARTER, CUSTOMERID, TRADEID) VALUES ('2', 'PAID COMISION', '220', '4', '2', '2', '2')
INSERT INTO "SYS"."COMMISION" (COMMISSIONID, DESCRIPTION, AMOUNT, SCORE,
YEARQUARTER, CUSTOMERID, TRADEID) VALUES ('3', 'PAID COMISION', '1800', '4', '3', '3', '3')
INSERT INTO "SYS"."COMMISION" (COMMISSIONID, DESCRIPTION, AMOUNT, SCORE,
YEARQUARTER, CUSTOMERID, TRADEID) VALUES ('4', 'PAID COMISION', '180', '5', '3', ' (Paige,
2015)