Earn Higher Grades With Instant Assignment Help.Ask Question!

Database
(5/5)

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 1st 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 1st 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 2nd 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. 5th 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 3rd Normal Form (3NF)

Convert to 1 NF: The existing ERD follows the rule of 1st 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:
(5/5)

Related Questions

CSI 1420 Introduction to C Programming & 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 majorconstructs of the C programming language – Fu

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

Ask This Assignment To Be Done By Our ExpertsGet A+ Grade Solution Guaranteed

expert
joyComputer science
(4/5)
12 Answers Hire Me
expert
Robert DLaw
(4.8/5)
979 Answers Hire Me
expert
Dr Samuel BarberaStatistics
(5/5)
939 Answers Hire Me
expert
Tutor For YouEconomics
(5/5)
594 Answers Hire Me