Designing A Database For BigM: Conceptual Data Model And Relational Database Schema

(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.

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.

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
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)
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: 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

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

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

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.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Designing A Database For BigM: Conceptual Data Model And Relational Database Schema ”

Get high-quality paper

NEW! AI matching with writer