Data Warehouse Design and Analysis for Trade Master Company Report

This report provides a detailed analysis of a data warehouse designed for the Trade Master Company, a business that sells tools. The report begins with an introduction to data warehousing, defining its role in business intelligence and its structure based on the ETL approach. The core of the report is a deep dive into the database structure, including descriptions of the six key tables: CompanyDim, ProductDim, WarehouseDim, CustomerDim, DateDim, and SaleFacts. Each table's attributes and the significance of its primary key are explained. The report then elucidates the concepts of dimension and fact tables within a star schema, identifying SaleFacts as the central fact table and the other tables as dimension tables. Primary and foreign keys are meticulously identified and described, emphasizing their role in maintaining data integrity and relationships between tables. A database diagram visually represents the relationships between the tables. The report concludes with a series of SQL queries, each accompanied by an explanation of its function, demonstrating how data can be retrieved and analyzed from the warehouse. References are also provided.

Data and Information Management
1

Contents
INTRODUCTION...........................................................................................................................3
Part 1................................................................................................................................................3
a) Analyse and describe the tables...............................................................................................3
b) Description of dimension and fact tables................................................................................9
c) Identification and description of Primary and Foreign Key..................................................11
d) Draw database diagram.........................................................................................................13
e) Database queries and explain them.......................................................................................14
CONCLUSION..............................................................................................................................16
REFREENCES..............................................................................................................................17
2

INTRODUCTION
Datawarehouse can be defined as a type of relational database this design and used for query
and analysis rather than transaction processing. It is considered as a main and core component of
business intelligence (Moscoso-Zea and Luján-Mora, 2016). It can also be defined as a central
repository of integrated data from one or more desperate sources. It is used within large
organizations for storing both historical and current data in a single place for analysis so that
analytical reports can be created that can further be used for decision making. Datawarehouse is
built on the basis of ETL approach i.e. extract, transform and load. There are three main
approaches on the basis of which database is designed: bottom-up, top- down and hybrid design.
This assignment is based upon a case study of Trade Master company sells various tools who has
developed a data warehouse to store information about sales, customers and products. This
assignment will focus upon following task in detail: description and analysis of tables,
description of dimension and fact tables, description and identification of primary and foreign
key, drawing database diagram, explanation of database questions briefly.
Part 1
a) Analyse and describe the tables.
The database has been created by developer for trade master company that mainly used for
selling the product as well as service in marketplace (Li, Yang and Feng, 2019). The Trade
Master Company have developed the data warehouse to collect or store information regarding
consumers, sales and products. Database tables has been developed by using Structural query
language. This is considered as one of the most effective database table that help for performing
different commands and afterwards, it is running the process in step by step manner (Chen and
et. al., 2019). For creation of relational database, SQL is playing important role to use DDL,
DML for performing command. In order to generate accurate result or outcome.
Generally, it is mainly consists of six different database tables such as CompanyDim,
productDim, warehouseDim, customerDim, DateDim, SaleFacts.
CompanyDim Table
SELECT * FROM 'CompanyDim' LIMIT 0, 30
3

In this table, it is mainly representing the information about the company and also consists of
different attributes such as CompanyKey, BranchState, BranchAddress and branchEmail. These
are basically appropriate attributes to represent more information (Faqihuddin, Wahyuddin and
Nathasia, 2020). CompanyKey is a primary key and contain unique value. This will help for
establishing a unique relationship with another table. It can be possible when primary key behave
as foreign key into another table.
4

ProductDim Table
SELECT * FROM 'ProductDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as ProductKey, ProductName, Brand, UnitPrice. These are basically
appropriate attributes to represent more information (Chen and et. al., 2019). ProductKey is a
primary key and contain unique value. This will help for establishing a unique relationship with
another table. It can be possible when primary key behave as foreign key into another table.
5

WarehouseDim Table
SELECT * FROM 'WarehouseDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as warehousekey, warehousedivision and warehouse region. These are
basically appropriate attributes to represent more information (Xie, Yang and Yao, 2019).
Warehousekey is a primary key and contain unique value. This will help for establishing a
unique relationship with another table. It can be possible when primary key behave as foreign
key into another table.
6

CustomerDim Table
SELECT * FROM 'CustomerDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as customerkey, customername, customeraddress, customer state,
customerEmail. These are basically appropriate attributes to represent more information.
CustomerKey is a primary key and contain unique value (Chen and et. al., 2019). This will help
for establishing a unique relationship with another table. It can be possible when primary key
behave as foreign key into another table.
7

DateDim Table
SELECT * FROM 'DateDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as Datekey, saledate, year, Month, day, Quarter These are basically
appropriate attributes to represent more information (Chen and et. al., 2019). Datekey is a
primary key and contain unique value. This will help for establishing a unique relationship with
another table. It can be possible when primary key behave as foreign key into another table.
8

SaleFacts Table
SELECT * FROM 'SaleFacts' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as ID, Datekey, CompanyKey, Customerkey, productkey,
warehousekey, salesQuantity (Menon and Narwane, 2020). These are basically appropriate
attributes to represent more information. ID is a primary key and contain unique value. This will
help for establishing a unique relationship with another table. It can be possible when primary
key behave as foreign key into another table.
b) Description of dimension and fact tables
Data modelling is one of the most important and crucial steps of converting raw data into
an effective form which further helps in making data readily accessible (Walha, Ghozzi and
Gargouri, 2019). Database star schema is one of the simplest style of data mart schema or data
9

model. It is a common approach which is most widely used for development of data warehouse
and dimensional data marts. Star schema consist of one or more than one Fat table that can refer
to any number of dimension tables (Chen and et. al., 2019). It works as a base for snowflake
schema which further helps in effective handling of database queries. It is named as star schema
because in this fact table is at the centre and dimension tables surround tact table which makes it
look like a star. In this schema denormalization of data modelling is done for creation of simple
ad easy schema. Fact table consist of two main types of tables: First is Fact table and second is
dimension table.
Fact table: This table focuses upon recording matrix or measurement of specific events. It
mostly consists of numeric values, foreign keys to dimensional data in which descriptive
information or data for numeric data is stored (Menon and Narwane, 2020). It can help in storing
large number of records within fact table over time. Fact tables many times consist of surrogate
key for ensuring that each row can be uniquely identified. Surrogate key is a simple primary key
of this table.
In this database SaleFacts is a Fact table that consist of foreign keys to all the other
dimensional table that consist of only numeric data.
Dimension table: It is a kind of table that consist of small number of records as compared to fact
table. But this table can consist of large number of attributes as compared to fact table. This table
can be defined as a wide variety of characteristics (Brown, 2019). Each dimension table is linked
to Fact table and each of these tables consist of a surrogate primary key which is usually of
integer data type. This database star schema consists of five main dimension tables that are liked
to SaleFacts fact table.
 CompanyDim: This table helps in explaining details of company like BranchState,
BranchAddress, BranchEmail.
 CustomerDim: This table helps in explaining details of customers like CustomerName,
CustomerAddress, CustomerState and CustomerEmail.
 DateDim: This table helps in explaining details of sales date like SaleDate, Year, month,
Day, Quarter.
10

 ProductDim: This table helps in explaining details of products like productname, Brand,
UnitPrice.
 WarehouseDim: This table helps in explaining details of warehouse like WarehouseKey,
WarehouseDivision and WarehouseRegion.
Figure 1 Database star schema
c) Identification and description of Primary and Foreign Key
There are various kinds of keys that are used while creating a column within a table.
These keys help in explaining characteristics of data that can be stored with it as well as helps in
explaining relationship with other tables as well (Menon and Narwane, 2020). But out of all the
keys two most important keys are primary and foreign key. Primary Key is unique constraint that
identified each record in a table. Primary keys must be unique and cannot be null (Vassiliadis
and et. al., 2019). Each table consist of only one primary key. Whereas foreign key is a kind of
key used to link two tables. Primary key of one table used in another is known as foreign key. A
table can have more than one foreign key.
Primary and foreign keys of all tables have been explained below:
11

Table name Primary Key Foreign Key
SaleFacts ID  CompanyKey
 CustomerKey
 DateKey
 ProductKey
 WarehouseKey
CompanyDim CompanyKey
CustomerDim CustomerKey
DateDim DateKey
ProductDim ProductKey
WarehouseDim WarehouseKey
Database referential integrity: Referential integrity is a property of data stating that all of its
references are valid. Referential mostly describes actions of foreign key as it performs,
‘referring’ or a kind of link to another column (Menon and Narwane, 2020). It can also be
explained as that each of the values stored within foreign key must be stored a unique value in
primary key in referred table. Foreign key within a table helps in maintaining referential integrity
od a relational database.
12

d) Draw database diagram
Figure: 1
Database is consider as integral part of software system that can fully utilise ER model in
database engineering. It also providing the guarntees to produce high quality of database design
to use in creation, maintenace and management (Menon and Narwane, 2020). ER model will
give an effective way to establish a strong communication between one entity to another one.
Database diagram is also known as entity relationship diagram that mainly represent the
relation between one or more entities (Chen and et. al., 2019). In order to store large amount of
data or information within database system. An entity is base don the objects for collecting
similar entities.
Entity relationship diagram is mainly representing the structual way to use efficient
database design. Generally, it may contain different symbols and connectors that can easily
visualise important information or data (Menon and Narwane, 2020). The major entities within
system and also maintain inter relationship among different entities in proper manner.
13

e) Database queries and explain them.
Query: 1 display customerName, customerAdress, customerState, ProductName, brand,
Unitprice
SQL statement
SELECT customerDM.CustomerName, customerDM.CustomerAddress,
customerDM.CustomerState, ProductDM.ProductName, ProductDM.Brand,
ProductDM.UnitPrice
FROM ProductDM INNER JOIN customerDM ON ProductDM.[ProductKey] = customerDM.
[productkey];
Query:2 display customerkey and salesQuantity from salefact table
SQL statement
SELECT SaleFacts.[CustomerKey], SaleFacts.[SalesQuantity]
FROM SaleFacts;
Query: 3 List the customer who has select the product
SQL statement
SELECT customerDM.CustomerKey, customerDM.CustomerName, ProductDM.ProductName,
ProductDM.Brand
FROM ProductDM INNER JOIN customerDM ON ProductDM.[ProductKey] = customerDM.
[productkey];
Query: 4 listing product details
SELECT ProductDM.[ProductKey], ProductDM.[ProductName], ProductDM.[Brand],
ProductDM.[UnitPrice]
FROM ProductDM;
14

Query: 5 product detail that stored in warehouse
SQL statement
SELECT ProductDM.ProductKey, ProductDM.ProductName, ProductDM.Brand,
ProductDM.UnitPrice, Warehouse.WarehouseKey, Warehouse.WarehouseDivision,
Warehouse.WarehouseRegion
FROM Warehouse INNER JOIN ProductDM ON Warehouse.[WarehouseKey] = ProductDM.
[warehousekey];
Query: 6 For each sale, display a customer name and a purchase date
SQL statement
SELECT customerDM.CustomerName, SaleFacts.DateKey, ProductDM.ProductName
FROM (ProductDM INNER JOIN customerDM ON ProductDM.[ProductKey] = customerDM.
[productkey]) INNER JOIN SaleFacts ON customerDM.[CustomerKey] = SaleFacts.
[CustomerKey];
Query: 7 sales for each customer separately
SQL statement
SELECT customerDM.CustomerName, SaleFacts.SalesQuantity, SaleFacts.DateKey
FROM customerDM INNER JOIN SaleFacts ON customerDM.[CustomerKey] = SaleFacts.
[CustomerKey];
15

Query: 8 listing the product that stored in warehouse with specific brand.
SQL statement
SELECT Warehouse.WarehouseKey, Warehouse.WarehouseDivision,
Warehouse.WarehouseRegion, ProductDM.ProductName, ProductDM.Brand
FROM Warehouse INNER JOIN ProductDM ON Warehouse.[WarehouseKey] = ProductDM.
[warehousekey];
CONCLUSION
Above assignment helps in explaining and describing all the database tables in detail. Each
table has their own primary keys. All the tables are inter related to each other with the help of
primary keys where these keys are used as foreign key in other tables. In the above assignment
database star schema has also been explained in which all the dimension tables and fact table are
explained. Database diagram drawn further helps in explaining relationship between each table.
Lastly in this assignment many different kinds of queries have been run for achieving desired
results for analysis. In this both DDL and DML queries have been executed and run for desired
results for analysis.
16

REFREENCES
Book and Journals
Brown, J., 2019. Crossref grant IDs: a global, open database of funding information and
identifiers.
Chen, L., and et. al., 2019, April. Efficient and incremental clustering algorithms on star-schema
heterogeneous graphs. In 2019 IEEE 35th International Conference on Data
Engineering (ICDE) (pp. 256-267). IEEE.
Faqihuddin, A., Wahyuddin, I. and Nathasia, N.D., 2020. Mysql Database Processing
Information System Using The System Development Life Cycle (SDLC) Method At
Quality Guarantee Agency Working Unit At National University. Jurnal Mantik. 4(1,
May). pp.399-406.
Li, H., Yang, X. and Feng, S., 2019, April. Design and implementation of international civil
aviation security information database management system. In IOP Conference Series:
Earth and Environmental Science (Vol. 252, No. 5, p. 052101). IOP Publishing.
Menon, C.B. and Narwane, V., 2020. Relevance of Database and Information Retrieval System
in Mechanical Engineering Program: A Case Study. Journal of Engineering Education
Transformations. 33. pp.447-450.
Moscoso-Zea, O. and Luján-Mora, S., 2016, September. Datawarehouse design for educational
data mining. In 2016 15th International Conference on Information Technology Based
Higher Education and Training (ITHET) (pp. 1-6). IEEE.
Vassiliadis, P., and et. al., 2019. Schema evolution and foreign keys: a study on usage, heartbeat
of change and relationship of foreign keys to table activity. Computing. 101(10).
pp.1431-1456.
Walha, A., Ghozzi, F. and Gargouri, F., 2019. From user generated content to social data
warehouse: processes, operations and data modelling. International Journal of Web
Engineering and Technology. 14(3). pp.203-230.
Xie, Q., Yang, W. and Yao, L., 2019, December. A Database Optimization Strategy for Massive
Data Based Information System. In 2019 2nd International Conference on Mathematics,
Modeling and Simulation Technologies and Applications (MMSTA 2019). Atlantis
Press.
17