Data Warehouse Design, Implementation and Querying: Project Report

This report details the implementation of a data warehouse for the Global Trade Company, focusing on storing customer, product, and sales information. The project utilizes a star schema, with dimension tables for company, customer, warehouse, product, and date, linked to a central sales fact table. The report covers database description, identification of primary and foreign keys, and the creation of an Entity Relationship Diagram (ERD). The implementation uses Wamp server and SQL queries. The project includes creating tables, inserting data, and executing various queries to retrieve specific information, such as customer details, sales figures, and product sales by state and warehouse location. The conclusion summarizes the successful implementation and the application of queries. The report provides a comprehensive analysis of the company's business model through database design and querying.

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’;