Assignment Solution for Data Warehouse

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

DATA WAREHOUSE1
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

DATA WAREHOUSE2
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).

DATA WAREHOUSE3
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