Assignment Solution for Data Warehouse

Running head: DATA WAREHOUSE
Data warehouse
Name of the Student
Name of the University
Author Note

1DATA WAREHOUSE
Table of Contents
Introduction................................................................................................................................2
Discussion..................................................................................................................................2
Identification of primary and foreign key..............................................................................3
Database Description.............................................................................................................3
Database Diagram..................................................................................................................5
Data Dictionary......................................................................................................................5
Method...................................................................................................................................8
Queries...................................................................................................................................9
Conclusion................................................................................................................................16
References................................................................................................................................17

2DATA WAREHOUSE
Introduction
Collecting, Analysing, Filtering, Storing and Fetching, these all processes play vital
role for any business organisation. These processes can be availed by using database
management system and its tools (Jukic, Vrbsky and Nestorov 2016). Database management
system is a system where the collections of data is stored as per requirement and fetched for
later purpose. This really helps in improving any business organisation (Cortez et al. 2015).
This report intends to discuss about the implementation of database management system for
Global Trade Company in Australia. The company deals with selling various building and
gardening tools. Many offices and warehouses of the company are situated all over Australia.
The aim of the system is to store the customer and product details in a database. The
customer is related with the product by the other information that is sales information and
from which warehouse the product is brought along with the office that sold to the customer.
According to the case study, the design of the database is built as a star schema where the
tables are organised into fact and dimension tables. In the later sections of the report, the data
is loaded into the built schema, the primary keys and foreign keys are identified and queries
have been applied to fetch various type of result.
Discussion
The main objective of the database design is to generate a logical design that is called
schema. Later the conceptual design is used to implement the physical design models of the
database management system (Hanson et al. 2015). The logical part deals with the data
requirement analysis and physical design deals with the method of storing the data. The data
that are used to design and stored in this case was structured on dimension and facts table.
Hence, the schema of the database is generally a star schema (Sayal, Casati and Shan 2013).

3DATA WAREHOUSE
The star schema is a type of schema where the data warehouse and data marts are developed
using the dimension a fact tables (Kimball and Ross 2013). The schema looks like a star
shape as all the dimension tables are connected to the one fact table which is a strong entity.
It also called a snowflake schema sometimes (Cherniack, Lawande and Tran 2014).
Identification of primary and foreign key
The identification of primary keys has been done on the basis of selecting the column
which is a unique one. That means the column cannot have repeated values and each value
have be different than other values (Bush 2015). In a star schema all the tables generally have
a primary key. In this case also all the tables are having primary key. Although, the sales fact
table needed to have unique for each order number. However, the sales fact’s primary key is
not mentioned anywhere in the dimension tables as foreign key. Similarly the foreign keys
are identified from its parent tables and placed in the related tables (Pejathaya, Talluri and
Bhide 2017). Since, it is a star schema, all the dimension table is related to the fact tables.
The sales fact table is having foreign keys that is referred to all the dimension tables as parent
table.
Database Description
Database description is basically the outlining the used entities, attributes, variables
and their data type, range and requirement priority (Elmasri 2017). In this case the database is
described by a star schema which is consist of dimension and facts table. The dimension
tables and fact table used in this case are discussed below:
1. Dimension Tables: Dimension tables generally consist with less records than fact
tables. However, the attributes in the dimension table describes the facts in a multiple

4DATA WAREHOUSE
way (Barber 2015). The tables used in this case as dimension tables are:
i. CompanyDim: This table stores the record of each branch of the company, its
address with state and email id. Each branch is identified by a unique id
having attribute name companykey.
ii. CustomerDim: It usually stores the information about each customer, their
addresses, state and emails. The customer s identified by customerkey as a
unique id.
iii. WarehouseDim: This entity records the type and name of product is stored in
the different locations, their division and region. It is also identified by a
unique id warehousekey.
iv. ProductDim: Records the product details, its price, name and brand alongwith
an identification number Productkey.
v. DateDim: It stores the date of sales stored in a date format, along with the
year, month, day and quarter value numerically. It has an identification
number having attribute name Datekey.
2. Facts Tables: These tables stores the events and measurements of the events. It has
generally numeric values and foreign keys to refer to its parent tables. It is designed in
a way that it shows a very little information about the database (Sidi, El Merouani and
El Amin 2016). All the main and descriptive details are kept in the dimension table.
The used fact table in this case is mentioned below:
i. SalesFact: It records the every unique id from the dimension tables to relate
with the facts that have defined dimensions. It also records order number and
quantity of the product that has been ordered along with identification having
attribute name ID.

5DATA WAREHOUSE
Database Diagram
The database diagram can be represent by Entity Relationship Diagrams (ERD).
ERDs help in establishing the links between the tables and schema (Hsu et al. 2014). Figure 1
shows the ERD for this system.
Figure 1: Entity Relationship Diagram (Star Schema)
Source: created by author
Data Dictionary
Data dictionary is simply an organised set of formats, contents and database structure
(Apanowicz et al. 2014). The following data dictionary shows the table name, its attributes,
data type, column’s description, requirement, keys and relationship to other tables.

6DATA WAREHOUSE
Table
Name
Attribute Name Description Type Req’d
PK/
FK
FK
Referenced
Table
Customer
Dim
Customerkey Customer’s unique key int Y PK
Customername Customer name varchar Y
Customeraddre
ss
Customer’s address varchar Y
Customerstate Customer’s state varchar Y
customeremail Customer’s email varchar Y
CompanyD
im
Companykey Company’s branch id int Y PK
Branchstate Branch state varchar
Branchaddress Branch address varchar Y
Branchemail Branch’s email address varchar Y
Datedim
Datekey Unique id int Y PK
Saledate Date of the sale date Y
Year Year of the sale int Y
Month Month of the sale int Y
Day Day of the sale int Y
Quarter
Sale belongs to the
quarter
int Y

7DATA WAREHOUSE
Table
Name
Attribute Name Description Type Req’d
PK/
FK
FK Referenced
Table
ProductD
im
productkey Product id int Y PK
productname Name of the product varchar Y
brand Brand of the product varchar Y
unitprice
Unitprice of the
product
decimal Y
Warehous
edim
Warehousekey Warehouse id int Y PK
Warehousedivision Warehouse division varchar Y
WarehouseRegion Warehouse region varchar Y
Salesfac
t
ID Unique id int Y PK
Ordernum Order number int Y
Datekey Date id int Y FK Datedim
Companykey Company id int Y FK Companydim
Customerkey Customer id int Y FK Customerdim
Product key Product id int Y FK Productdim
Warehousekey Warehouse id int Y FK Warehousedim
Salesquantity
Quantity of the each
product sale
int Y

8DATA WAREHOUSE
Method
Implementation of the database according to the case is developed using the data
provided and Wamp SQL server. The provided data was stored in the MS Access file and
which is exported later as excel files (Chen 2014). Then the excel files are saved in comma
separated values (CSV) format. The Entities provided in the Access file were not linked and
they were linked using the relationship tool available in Access. The ERD (Entity
Relationship Diagram) is completed after the linking the entities with relations. Next, the
Wamp server is used to create database and the tables are created (Agrawal and Gupta 2014).
The identified primary keys and foreign keys are introduced in the creation of table according
to the Entity Relationship Diagram. It should be considered that the data type and column
named in the Wamp should match with the excel files. Also the first row in the excel which is
columns name is not required as importing the data into the Wamp server will only need the
data not the attributes name (Cuesta 2013). Then the data is imported into the tables using
Wamp server Import feature. After importing data, the queries have been applied and
screenshots of the output has been taken. Figure 2 shows the import feature location in the
Wamp server.

9DATA WAREHOUSE
Figure 2: Import feature of the Wamp server
Source: phpMyAdmin
Queries
a. Creation of table with primary and foreign key.
create table customerdim(customerkey int(10) primary key, customername
varchar(50) not null, customeraddress varchar(50) not null, customerstate varchar(50)
not null, customeremail varchar(50) not null);
create table datedim(datekey int(10) primary key, saledate date not null, year int(4)
not null, month int(2) not null, day int(2) not null, quarter int(1) not null);
create table productdim( productkey int(10) primary key, productname varchar(50)
not null, brand varchar(50) not null, unitprice decimal(6,2) not null);
create table warehousedim(warehousekey int(10) primary key, warehousedivision

10DATA WAREHOUSE
varchar(50) not null, warehouseregion varchar(50) not null);
create table companydim( companykey int(10) primary key, branchstate varchar(50)
not null, branchadress varchar(50) not null, branchemail varchar(50) not null);
create table salesfact(id int(10) primary key, ordernum int(10) not null, datekey
int(10) not null, companykey int(10) not null,customerkey int(10) not null,
productkey int(10) not null, warehousekey int(10) not null, salesquantity int(10),
foreign key(datekey) references datedim(datekey), foreign key (companykey)
references companydim(companykey), foreign key (customerkey) REFERENCES
customerdim(customerkey), foreign key(productkey) references
productdim(productkey), foreign key(warehousekey) references
warehousedim(warehousekey));
b. Insert a new customer.
insert into customerdim values(51, 'Ruffulow, M.', '1/70
brooklyn','VIC','[e-mail]');
c. Creating a new sale fact related to the new customer.
insert into salesfact values(501,501,21,21,51,7,1,5);
d. Display all customer names, their addresses and states.
select customername, customeraddress,customerstate from customerdim;

11DATA WAREHOUSE
e. Display all customer names from Victoria.
select customername from customer where customersate=’VIC’;

12DATA WAREHOUSE
f. Display the total amount of all company sales in 2018.
select sum(productdim.unitprice*salesfact.salesquantity) as total from
productdim,salesfact,datedim where productdim.productkey=salesfact.productkey and
datedim.year=2018;
g. Display total amount of sales for each product separately.
select productname,unitprice, salesquantity, (unitprice*salesquantity) as total from
productdim join salesfact on salesfact.productkey=productdim.productkey group by
productname;

13DATA WAREHOUSE
h. Display total amount of sales for each product and state separately.
select productname,customerstate, (unitprice*salesquantity) as total from productdim
join salesfact on salesfact.productkey=productdim.productkey join customerdim on
customerdim.customerkey=salesfact.customerkey
group by productname;

14DATA WAREHOUSE
i. Display products sold from warehouses located in Victoria.
SELECT productdim.productname from productdim,salesfact,warehousedim where
warehousedim.warehousekey=salesfact.warehousekey and
productdim.productkey=salesfact.productkey and
warehousedim.warehouseregion='VIC';

15DATA WAREHOUSE
j. For each sale, display a customer name and a purchase date.
select salesfact.ordernum, customerdim.customername,datedim.saledate from
salesfact,customerdim,datedim where
customerdim.customerkey=salesfact.customerkey and
datedim.datekey=salesfact.datekey;
k. Display a total number of sales (NOT AMOUNT!) for each customer.
select customerdim.customername, count(salesfact.ordernum) from
salesfact,customerdim where customerdim.customerkey=salesfact.customerkey group
by customerdim.customername;

16DATA WAREHOUSE
l. For each sales office (branch), display: branchKey, branch state, a customer
name a product name purchased by this customer, and a month number of the
purchase.
Select companydim.companykey, companydim.branchstate,
customerdim.customername, productdim.productname, datedim.month from salesfact,
customerdim,companydim,productdim,datedim where
productdim.productkey=salesfact.productkey and
companydim.companykey=salesfact.companykey and
customerdim.customerkey=salesfact.customerkey and
salesfact.datekey=datedim.datekey group by companydim.companykey;

17DATA WAREHOUSE
Conclusion
The objective of the report is to analyse the business model for the Global Trade
Company. The analysis consists of database design, identification of primary keys and
foreign keys and implementation of the developed Entity relationship diagram based on star
schema. The analysis of dimension tables and facts tables are also conducted successfully.
However, the data insertion is done by the importing comma separated values into the
database. The database description and dictionary is also developed in an organised structure.
In the later section of the report, the provided queries have been applied successfully and the
output is attached in the form of snapshots. Apart from this, it is kept in consideration that the
queries should applied by using simple process and use of the primary and foreign keys are
used properly. Hence it can be said that the implementation of the system for this case is
successfully done. However, it is observed that the attributes ordernumber and Id in the

18DATA WAREHOUSE
salesfact table served the same purpose in the database. Hence, one of them can be ignored in
use.
References
Agrawal, S. and Gupta, R.D., 2014. Development and comparison of open source based Web
GIS Frameworks on WAMP and Apache Tomcat Web Servers. The International Archives of
Photogrammetry, Remote Sensing and Spatial Information Sciences, 40(4), p.1.
Apanowicz, C.K., Eastwood, V.K., Slezak, D.A., Synak, P.D., Wojna, A.G., Wojnarski, M.
and Wroblewski, J.Z., Infobright Inc, 2014. Method and system for data compression in a
relational database. U.S. Patent 8,700,579.
Barber, R.J., Chainani, N.K., Lohman, G.M., Pirahesh, M.H., Raman, V., Sidle, R.S. and
Tata, S., International Business Machines Corp, 2015. Efficient join with one or more large
dimension tables. U.S. Patent 9,141,667.
Bush, T.S., Ab Initio Technology LLC, 2015. Database key identification. U.S. Patent
Application 14/156,544.
Chen, X., 2014. Open access in 2013: reaching the 50% milestone. Serials review, 40(1),
pp.21-27.
Cherniack, M., Lawande, S. and Tran, N., Hewlett Packard Development Co LP,
2014. Optimizing snowflake schema queries. U.S. Patent 8,671,091.
Cortez, E., Bernstein, P.A., He, Y. and Novik, L., 2015. Annotating database schemas to help
enterprise search. Proceedings of the VLDB Endowment, 8(12), pp.1936-1939.
Cuesta, H., 2013. Practical data analysis. Packt Publishing Ltd.
Elmasri, R., 2017. Fundamentals of database systems.

19DATA WAREHOUSE
Hanson, S.M., Kalia, S.K., Lovett, M.C., Mamas, E. and Spriet, D.A., International Business
Machines Corp, 2015. Single file serialization for physical and logical meta-model
information. U.S. Patent 9,032,002.
Hsu, S.H., Sun, C.P., Sun, Z.H. and Yian, J.B., Industrial Technology Research Institute,
2013. Systems and methods for generating an entity diagram. U.S. Patent 8,407,262.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases
and data warehouses. Prospect Press.
Kimball, R. and Ross, M., 2013. The data warehouse toolkit: The definitive guide to
dimensional modeling. John Wiley & Sons.
Pejathaya, K., Talluri, G. and Bhide, A.S., Tata Consultancy Services Ltd, 2017. Foreign key
identification in database management systems. U.S. Patent 9,552,379.
Sayal, M., Casati, F. and Shan, M.C., Hewlett Packard Development Co LP, 2013. System
and method for developing a star schema. U.S. Patent 8,412,671.
Sidi, E., El Merouani, M. and El Amin, A.A., 2016. The impact of partitioned fact tables and
bitmap index on data warehouse performance. International Journal of Computer
Applications, 975, p.8887.