Data and Information Management

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