Database Management System: Case Study of BigM System

This case study discusses the database management system of BigM System, including entity relationship diagram, normalization, and relational database schema.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System: Case Study of BigM System
Name of the Student
Name of the University
Author’s Note

1DATABASE MANAGEMENT SYSTEM
Table of Contents
Entity Relationship Diagram:..........................................................................................................2
Normalization:.................................................................................................................................3
Relational Database Schema:..........................................................................................................6
Bibliography:.................................................................................................................................11

2DATABASE MANAGEMENT SYSTEM
Entity Relationship Diagram:
Figure 1: Entity Relationship Diagram of BigM’s Business
(Source: Created by Author)
Assumptions: The customer will make order from the website. A bridge table has been
used for tracking the product quantity for each order. The postal and residential address will refer
to the same entity. An employee acting as the supervisor of the store will not work in any
department. The pay slip will be provided to the employees by supervising store under which
they work. The inventory will be used to track the stock the availability of the products. The
inventory entity also specifies which store has which products.

3DATABASE MANAGEMENT SYSTEM
Normalization:
1st Normal Form: In order to be in the first normal form, the attributes are required have
only atomic values and a primary key for each table. As shown the figure 1 ERD, all the tables
have at least one primary key and all the attributes are atomic. Therefore, the database tables are
perfectly in first normal form.
2nd Normal Form: The second normal form states that non-key attributes should be
depended on key attributes.
Table name: Customer
Entity Data Type Size Key Reference Table
customerNumber Int 8 Primary Key None
firstName Varchar 40 None None
lastName Varchar 40 None None
phone Int 10 None None
addressID Int 8 Primary Key None
street Varchar 40 None None
city Varchar 40 None None
state Varchar 40 None None
postCode Int 8 None None
In the above table, the street, city, state and postcode are depended on the addressID.
Therefore, the table has partial dependency. To remove the partial dependency, the table is
divided into two separate tables named customer and address.

4DATABASE MANAGEMENT SYSTEM
Table name: Customer
Entity Data Type Size Key Reference Table
customerNumber Int 8 Primary Key None
firstName Varchar 40 None None
lastName Varchar 40 None None
phone Int 10 None None
postalAddress Int 8 Foreign Address (addressID)
Table name: Address
Entity Data Type Size Key Reference Table
addressID Int 8 Primary Key None
street Varchar 40 None None
city Varchar 40 None None
state Varchar 40 None None
postCode Int 8 None None
3rd Normal Form: The database must be in second normal form and it does not have any
transitive dependency among the attributes.
Table name: Store
Entity Data Type Size Key Reference Table
storeNumber Int 8 Primary Key None
storeName Varchar 40 None None

5DATABASE MANAGEMENT SYSTEM
phone Varchar 40 None None
fax Int 15 None None
email Varchar 40 None None
postalAddress Int 9 Foreign Address (addressID)
manager Int 9 Foreign Employee (employeeID)
supervisinStore Int 8 Foreign Store (storeNumber)
departmentID Int 8 Primary Key None
supervisor Int 8 Foreign Employee (employeeID)
departmentName Varchar 40 None None
phone Int 10 None None
emailAddress Varchar 40 Foreign Address (addressID)
The storeID determines the departmentID detrrmines the departmentName. But the
departmentID does determines storeID. Therefore, the table has transitive dependency. To
remove the dependency, the table has been separated into two table named store and department.
Table name: Store
Entity Data Type Size Key Reference Table
storeNumber Int 8 Primary Key None
storeName Varchar 40 None None
phone Varchar 40 None None
fax Int 15 None None
email Varchar 40 None None
postalAddress Int 9 Foreign Address (addressID)

6DATABASE MANAGEMENT SYSTEM
manager Int 9 Foreign Employee (employeeID)
supervisinStore Int 8 Foreign Store (storeNumber)
Table name: Department
Entity Data Type Size Key Reference Table
departmentID Int 8 Primary Key None
store Int 8 Foreign Store (storeNumber)
supervisor Int 8 Foreign Employee (employeeID)
departmentName Varchar 40 None None
phone Int 10 None None
emailAddress Varchar 40 Foreign Address (addressID)
Relational Database Schema:
Table name: Customer
Entity Data Type Size Key Reference Table
customerNumber Int 8 Primary Key None
firstName Varchar 40 None None
lastName Varchar 40 None None
phone Int 10 None None
postalAddress Int 8 Foreign Address (addressID)

7DATABASE MANAGEMENT SYSTEM
Table name: Address
Entity Data Type Size Key Reference Table
addressID Int 8 Primary Key None
street Varchar 40 None None
city Varchar 40 None None
state Varchar 40 None None
postCode Int 8 None None
Table name: Employee
Entity Data Type Size Key Reference Table
employeeID Int 8 Primary Key None
firstName Varchar 40 None None
lastName Varchar 40 None None
phone Int 10 None None
dateOfBirth Int 8 None None
workStartDate Date None None
taxFileNumber Int 11 None None
postalAddress Int 8 Foreign Address (addressID)
residentialAddress Int 8 Foreign Address (addressID)
jobType Varchar 40 None None
annualSallary Varchar 40 None None
Table name: Store

8DATABASE MANAGEMENT SYSTEM
Entity Data Type Size Key Reference Table
storeNumber Int 8 Primary Key None
storeName Varchar 40 None None
phone Varchar 40 None None
fax Int 15 None None
email Varchar 40 None None
postalAddress Int 9 Foreign Address (addressID)
manager Int 9 Foreign Employee (employeeID)
supervisinStore Int 8 Foreign Store (storeNumber)
Table name: Department
Entity Data Type Size Key Reference Table
departmentID Int 8 Primary Key None
store Int 8 Foreign Store (storeNumber)
supervisor Int 8 Foreign Employee (employeeID)
departmentName Varchar 40 None None
phone Int 10 None None
emailAddress Varchar 40 Foreign Address (addressID)
Table name: PaySlip
Entity Data Type Size Key Reference Table
payID Int 8 Primary Key None

9DATABASE MANAGEMENT SYSTEM
supervisingStore Int 8 Foreign Store (storeNumber)
employee Int 8 Foreign Employee (employeeID)
payDate Date None None
numberOfHours Time None None
grossPayment Number 10,2 None None
Table name: Inventory
Entity Data Type Size Key Reference Table
store Int 8 Primary Key,
Foreign
Store (storeNumber)
product Int 8 Foreign Product (productNumber)
availableQuantity Int 8 None None
Table name: Product
Entity Data Type Size Key Reference Table
productNumber Int 8 Primary Key None
productDescription Varchar 200 None None
brand Varchar 40 None None
tyos Varchar 40 None None
productSize Int 8 None None
price Number 10,2 None None

10DATABASE MANAGEMENT SYSTEM
Table name: OrderProduct
Entity Data Type Size Key Reference Table
orderID Int 8 Primary Key,
Foreign
Order (orderID)
product Int 8 Primary Key,
Foreign
Product (productNumber)
quantity Int 8 None None
arrivalDate Date None None
pickupDate Date None None
Table name: OrderProduct
Entity Data Type Size Key Reference Table
orderID Int 8 Primary Key None
customer Int 8 Primary Key Customer
(customerNumber)
date Date None None
cost Number 10,2 None None

11DATABASE MANAGEMENT SYSTEM
Bibliography:
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology Research, 6,
1.