Database Management System: Case Study of BigM System

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.