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)

13
Data Warehouse And Mining
1.2.9
2 Task 2: OLAP Queries
2.1 Trades placed in July by “John Alan”
2.1.1 SQL codes
select count(*) from CUSTOMERS,TRADE where
CUSTOMERS.CUSTOMERID=TRADE.CUSTOMERID AND CUSTOMERS.CUSTOMERNAME='John
Alan' AND TRADE.MONTH=7 (Stephen, 2014)
2.1.2 Results
2.2 Commission earned each quarter of 2003 for customer with 4/5 profit scores
2.2.1 1st quater
2.2.2 SQL codes
select sum(amount) from commision where yearquarter=1 and score>=4
2.2.3 Results
(Thomas, 2013)
2.2.4 2nd quarter
2.2.5 SQL codes
select sum(amount) from commision where yearquarter=1 and score>=4 (Shamkant, 2015)

14
Data Warehouse And Mining
2.2.6 Results
(Ramez, 2015)
2.2.7 3rd quarter
2.2.8 SQL codes
select sum(amount) from commision where yearquarter=3 and score>=4 (Elmasri, 2013)
2.2.9 Results
(Dick, 2018)

15
Data Warehouse And Mining
3 Reference
Dick,B.(2018) IT Project Management Essentials: Information Systems Project Methodology
Kindle Edition.1st edn.New York:Amazon Digital Services LLC.
Elmasri,E.(2013) Database Systems: Models, Languages, Design and Application Programming.
2nd edn India: PEARSON EDUCATION.
Ramez,E.(2015) Database Systems: Models, Languages, Design and Application Programming
- International Edition Paperback.2nd edn. India:PEARSON EDUCATION.
Shamkant, B.(2015) Fundamentals of Database Systems (7th Edition).India:pearson.
Thomas,C.(2013) Database Systems: A Practical Approach to Design, Implementation and
Management: WITH Success in Your Project.1st edn. USA:Addison Wesley.
Stephen,H.(2014) Management Information Systems with student CD and MISource.3rd edn. New
York:McGraw-Hill.
Paige,B.(2015) Business Driven Information Systems with MISource 2007 and Student CD.1st
edn New York: McGraw-Hill.
Specialist,I.(2018) CISSP Certified Information Systems Security Professional Study Guide
2018. 1st edn.New York:Independently published
Michael,J.(2017) Human Resource Information Systems: Basics, Applications, and Future
Directions. 5th edn.New York :SAGE Publications, Inc.
Dick,B.(2018) IT Project Management Essentials: Information Systems Project Methodology
Kindle Edition. 1st edn.New York:Amazon Digital Services LLC.
Tycho,p.(2014) Personal Finance Simplified: The Step-By-Step Guide for Smart Money
Management Paperback. 2nd edn.New York:Tycho Press.