Database Design For BigM Business

Entity Relationship Diagram

Database design refers to a collection of techniques that facilitate design, development, the implementation and the maintenance of a business data management systems. In this assignment, we will be designing a database for BigM business. Our main tasks will be drawing and ERD, normalizing the database, and finally coming up with a relational schema from information gathered in the ERD.

Entity relationship diagram depicts entities, sets of information together with their relations maintained for an enterprise. It is an information modelling method that pictorially demonstrates an information system’s tables and the relations among these tables. Entities, relations and attributes are the components of an Entity Relationship Diagram. Entity Relationship Diagram is used by database designers to model the design of a database.

ERD

Conrad, Dozier & Veeramachaneni, (2017) defines assumption as an action that is accepted as a true as certain to take place without any proof.  In the above ERD, there exists a number of assumptions made. They include:

• In the real world, a customer can be served by more than one employee from point of entry into business premises to point of exit from business premises after delivery of goods. However, in our database design for BigM business enterprises we assume that a customer is served by one and only one employee.
• Many of the businesses store their products in more than one warehouses, in our database design, we assume that BigM has only one store where it store all of its products.
• We assume that every employee has one and only one payslip at the end of every month.
• We assume that a manager manages only one store, however, in real time scenario, there are occasions when a manager can step in and oversee more than store.
• In a normal life setting, an employee or customer may have one or more postal addresses, in our database design for BigM, we assume that both employees and customers may have one and only one postal address or they may not have one at all.

Noh, Bahari & Zakaria, (2018), describes normalization as the technique of arranging data in the database to avoid update, deletion, insertion and redundancy anomalies. To achieve our goal of database development for BigM enterprises without database anomalies we adhered to the following steps:

1. We choose our data origin from the provided business activities description and converted the data into un-normalized tables.

Example: employee table

Emp_ID	Emp_ Name	Salary Paid ($)	Job Type	Sales & Marketing	Productions	Procurement
1	Susan John	320	Permanent	Marketing 2
2	Scott Smith	230	Contractual	Sales 1	Productions 1
3	Matt Longson	120	Casual

This table is not normalized at all, a number of issues exist in the employees table as summarized below.

Insert anomaly: insert anomaly occur when we try to insert a row in our entity table without good understanding of data we are necessitated to know. A good example when are required to add an employee in the above table, without understanding their job types. The new row inserted will look like one below:

4

Mark Mazq

0

?

Update anomaly: this kind of anomaly takes place when an entity is updated while some of data updated is updated leaving some data not updated. For instance, if employee activity sales 1 was changed to economics 1, we will be forced to query each field that has this column item and change it to match the above. This is tedious and nearly to impossible especially when our database is huge.  

We converted our above table into first normal form by asking ourselves questions such as:

• Are our fields brought together make a unique row/record every single moment?
• Which field/column we are using to primarily identify a row?

Keenly observing our first question, the answer is a big NO, there exists similar combination of information that signifies a different row. The answer to the second question is also NO. Simply because there is a probability that more than one employees may have same name. We are going to address this by creating a fresh primary key.

For example,

Employee (Emp_ID, FName, LName, Salary, Job_Type, Sales&marketing, productions)

Our table above is now in third normal form

1. We transformed the un-normalized information to first normal form by the instruction of removing every repeated attributes to a fresh table.

An entity qualifies to be in second normal form when it is the first normal form, and each primary key does not depend on Primary Key. Observing our table above,

Employee (Emp_ID, FName, LName, Salary, Job_Type, Sales&marketing, productions), FName, LName, Salary, Job_Type, Sales&marketing and productions, are all fields dependent on primary key Emp_ID, our table therefore, is in second normal form.

• We then transformed data in first normal form to second normal form using the rule of removing each non-key field that is on dependent on part of entity table to a new table.

A database qualifies to be in third normal it is in the second normal form, and its fields does not have functional transitivity. Transitive dependency means that column 1 determines column 2which determines column 3. This feature has to be eliminated from our tables. Observing our table able, none of the columns has this feature. Hence, our table is in third normal form.

1. We finally transformed data in second normal form to third normal form by the instruction of removing any transitive dependencies.    

It should be noted that every other table in our BigM database, followed the above steps to have 3NF of every table.

According to Jukic, Vrbsky & Nestorov, (2016), a relational database schema denotes to a logical and visual architecture of a db build on a database management system.

It grants the architectural view of the whole database structure providing means for displaying and logically grouping of database objects for instance fields, relations, functions and tables of a database.

Our BigM database has the following database schema:

Store (St_No, PostalA_ID, Name, Email, Fax, Phone)

Product (Pr_No, Name, Description, Brand, Size, Price, quantity, Cus_No, S_No)

Employee (Emp_ID, Fname, LName, Phone, DOB, Tax_File_No, StartDate, Job_Type, Salary, Pay_ID, PostalA_ID, Dep_ID)

Department (Dep_ID, Name, Phone, Email, S_No, Emp_ID)

Manager (Mgr_ID, St_ID, Emp_ID)

Postal Address (PostalA_ID, St_ID, Emp_ID, City, State, Postcode)

Order (Ord_ID,Cus_No, Pr_No, Name, Phone, DateOrdered, DateDelivered)

Supervisor (Sup_ID, Emp_ID, Dep_ID)

Customer (Cus_No, PostalA_ID, Ord_ID, Name, Phone)

Payslip (Pay_ID, Sup_ID, Emp_ID, Pay_Date, Hrs_Worked, Amount_Paind)

	St_No	PostalA_ID	Name	Email	Fax	Phone
Datatype	Varchar	Varchar	Char	Varchar	Dec	Int
Size	4	4	50	20	6	10
Primary/Foreign	PK	FK	NA	NA	NA	NA

	Pr_No	Description	Brand	Size	Price	quantity	Cus_No	S_No
Data type	vachar	char	Char	Dec	Dec	Dec	Varchar	Varchar
Size	4	50	20	5,2	6,2	4,2	4	4
PK/FK	PK	NA	NA	NA	NA	NA	FK	FK

Emp_ID

Fname

LName

Phone

DOB

Tax_File_No

StartDate

Job_Type

Salary

Pay_ID

PostalA_ID

Dep_ID

Datatyp

Varchar

Char

Char

Int

Date

Dec

Date

Char

Dec

Varchar

Varchar

Varchar

Size

4

50

50

10

NA

4,0

NA

20

6,2

4

4

4

PK/FK

PK

NA

NA

NA

NA

NA

NA

NA

NA

FK

FK

FK

	Dep_ID	Name	Phone	Email	S_No	Emp_ID
Datatype	Varchar	Char	Int	Varchar	Varchar	Varchar
Size	4	50	10	10	4	4
PK/FK	PK	NA	NA	NA	4	4

	Mgr_ID	St_ID	Emp_ID
Datatype	Varchar	Varchar	Varchar
Size	4	4	4
PK/FK	PK	FK	FK

	PostalA_ID	St_ID	Emp_ID	City	State	Postcode
Datatype	Varchar	Varchar	Varchar	Char	Char	Dec
Size	4	4	4	20	20	6
PK/FK	PK	FK	FK	NA	NA	NA

 

	Ord_ID	Cus_No	Pr_No	Name	Phone	DateOrdered	DateDelivered
Datatype	Varchar	Varchar	Varchar	Char	int	Date	Date
Size	4	4	4	50	10	NA	NA
FK/PK	PK	FK	FK	NA	NA	NA	NA

	Sup_ID	Emp_ID	Dep_ID
Datatype	Varchar	Varchar	Varchar
Size	4	4	4
FK/PK	PK	FK	FK

	Postal_ID	St_ID	Emp_ID	City	State	Postcode
Data type	vachar	Varchar	Varchar	Char	Char	Dec
Size	4	4	4	20	20	6
PK/FK	PK	FK	FK	NA	NA	NA

	Pay_ID	Sup_ID	Emp_ID	PayDate	Hrs_Worked	Amount_Paid
Data type	vachar	Varchar	Varchar	Date	Time	Dec
Size	4	4	4	NA	NA	5,2
PK/FK	PK	FK	FK	NA	NA	NA

 

References

Conrad, J. G., Dozier, C. C., & Veeramachaneni, S. (2017). U.S. Patent No. 9,600,509. Washington, DC: U.S. Patent and Trademark Office.

Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and data warehouses. Prospect Press.

Noh, H. N., Bahari, M., & Zakaria, N. H. (2018). A Conceptual Model of Database Normalization Courseware Using Gamification Approach. In PROCEEDINGS OF NEW ACADEMIA LEARNING INNOVATION (NALI) SYMPOSIUM 2018 (p. 23).