Normalization is a process of arranging the records within a database to prevent records insertion anomaly, redundancy, deletion as well as update anomaly.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Normalization, Converting the ERD

 Database Design and Management

Introduction

Normalization is a process of arranging the records within a database to prevent records insertion anomaly, redundancy, deletion as well as update anomaly (Coronel & Morris, 2018). According to the rule of 1^st normalization form, a column (attribute) of a database table can’t contain numerous values. It must comprise just atomic or separate data values. For example, a firm needs to keep the contact details and names of its workers: The initial table would be :

 

Emp_id	Emp-name	Emp-add	Emp-phone
001	Conner	Usa	7570000000
002	marie	canada	9190000000

 

 

The table itself is not in first normal form based on the provided definitions of normalization rules of 1^st NF. The emp_mobile data values interrupt/infringe upon the 1 NF rule. To normalize we must separate the data values. A database table is alleged to be in 2 NF if  both the mentioned conditions hold true:

• A table should be in first normal form.
• Not any non-prime attribute is reliant on the accurate subset of any candidate key of the database table. A non-prime attribute is not portion of any candidate key(Singh, 2015).

For instance, with in the ERD created in week 2 we utilized Employee, Manager, Workstation, Software, Employee Location and Configuration, this could apply to the managers within the organization.

Manger-ID	Department	Employee-Count
101	Sales	15
102	Operations	20

 

Non-prime attribute is would be the employee count and candidate keys would be the manager_id.  The database table is present in first normal form since every attribute has single values. However, is not considered to be in 2 NF form since non-prime attributes of employee-count is reliant on manager_id is unaccompanied. Therefore, infringes on the 2 NF rule. To make this table in line with second normal form  it needs to be separated;

 

Manager_ID	Employee_Count
101	15
102	20

 

Manager_ID	Department
101	Sales
102	Operations

 

Third normalization form means to structure 3 NF with both the succeeding situations:

• Table should be present in 2^nd normal form.
• Transitive functional dependency of a non-prime data attribute on super key must be eliminated(Petersen, 2002).

With the ERD created in week 2 a variety of data can be compiled for the employees which can include details of the employees that they will need to keep.

Emp_id	Emp_name	Emp_zip	Emp_state	Emp_city
0001	Conner	23181	VA	Williamsburg
0002	Marie	23233	NE	Omaha

 

We can see the above table violates the 3 NF rule of normalization as there is transitive functional dependency of non-prime attribute on super key i.e. {emp_id, emp_name}, {emp_id}, and {emp_id, emp_name, emp_zip}. The candidate key should only contain {emp_id}. In this case, emp_city, emp_state are reliant on emp_zip as well as, reliant on the emp_id. It creates a non-prime column attributed that is transitively reliant upon the emp_id (super key), this can be corrected by:

Emp_id	Emp_name	Emp_zip
0001	Conner	23181
0002	Marie	23233

 

Emp_zip	Emp_state	Emp_city
23181	VA	Williamsburg
23233	NE	Omaha

 

4 NF must fulfill all the needs of 1 NF, 2 NF and 3 NF and not have any multi-valued dependencies, with an exception of having an additional candidate key. 5^th normal form or project join normalization form is existing in 4 NF and can’t have a lossless breakdown into several possible tables. It manages joint-dependencies that is a generalization of the multiple value dependency.

Translating the ERD Model into 3^rd Normal Form (3NF)

Convert to 1 NF: The existing ERD follows the rule of 1^st Normal Form as every attribute of all tables (entities) e.g. Employee, Manager, Workstation, Software, Employee Location and Configuration have single or atomic data values in it.

Convert to 2 NF: The employees table is in 1 NF but non-prime attributes e.g. DofH and Salary are dependent on candidate key for example, {EMPID, FirstName, LastName}. Therefore it violates 2 NF rule as no non-prime data attribute is reliant on proper subset of candidate key. Therefore, EMPLOYEES table can be broken into the following tables:

EMPLOYEE -> EMPID (Primary key), FirstName, LastName, Title, DEPT.

EMPLOYEES_DETAILS table -> EMPID, Dofh, Salary.

The rest of the ERD tables is following the rule of 1 NF and 2 NF.

Convert to 3 NF:

In Employee Location table of the ERD Model, attributes for example, City and State are dependent on Location ID that sorts non-prime attributes e.g. City and State transitively reliant on Location ID (super key) in the table. It violates the 3 NF rule. Therefore, to sort this database model table agrees with 3 NF one need to split the table Location into 2 tables to eliminate transitive dependency. For example, we now remove functional transitive dependency from table Employee Location to new table created Employee Address.

Employee Location -> Location ID, Name, Address

Employee Address -> Address, State, City

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of