logo Hurry, Grab up to 30% discount on the entire course
Order Now logo

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

expert
Puneet GuptaStatistics
(/5)

956 Answers

Hire Me
expert
Arapera BillingAccounting
(4/5)

676 Answers

Hire Me
expert
Vincent AlexanderEnglish
(5/5)

644 Answers

Hire Me
expert
Neha SharmaOthers
(5/5)

916 Answers

Hire Me
Database

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

 

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme