Solution to Exercise 2

Relational Database Design and Modeling for Software Engineers

Ask Author

Back to course home

0% completed

Vote For New Content

Solution to Exercise 2

Table of Contents

Contents are not accessible

Step 1: Check for First Normal Form (1NF)

To determine if the given table is in 1NF, we need to ensure:

Each column contains atomic values (no multivalued or composite attributes).
Each row is unique and identifiable by a primary key.

Given Table:

Emp_ID	Emp_Name	DOB	Area	City	State	Zip
101	Alice	1990-05-01	Downtown	New York	NY	10001
102	Bob	1988-08-12	Midtown	New York	NY	10002
103	Charlie	1992-11-23	Central	Los Angeles	CA	90001
104	David	1985-03-15	West End	Chicago	IL	60601

Conclusion:

The table satisfies the requirements for First Normal Form (1NF).

Step 2: Check for Second Normal Form (2NF)

To determine if the table is in 2NF, we need to:

Ensure the table is already in 1NF.
Ensure there are no partial dependencies, where a non-key attribute depends only on part of a composite primary key.

Primary Key:

The primary key is Emp_ID (single column, not composite).

Dependencies:

All non-key attributes (Emp_Name, DOB, Area, City, State, Zip) depend directly on Emp_ID.

Conclusion:

There are no partial dependencies, and the table satisfies the requirements for Second Normal Form (2NF).

Step 3: Check for Third Normal Form (3NF)

To determine if the table is in 3NF, we need to:

Ensure the table is already in 2NF.
Ensure there are no transitive dependencies, where a non-key attribute depends on another non-key attribute rather than directly on the primary key.

Dependencies:

Emp_Name, DOB, and Zip depend directly on Emp_ID.
However, Area, City, and State depend on Zip, which in turn depends on Emp_ID. This is a transitive dependency.

Conclusion:

The table is not in 3NF due to the transitive dependency between Zip and Area, City, and State.

Step 4: Convert to Third Normal Form (3NF)

To remove the transitive dependency, we need to:

Create a separate table for the location details (dependent on Zip).
Retain a reference to Zip in the original table.

Employee Table

This table will store employee-specific information, with Zip as a foreign key.

Emp_ID (PK)	Emp_Name	DOB	Zip
101	Alice	1990-05-01	10001
102	Bob	1988-08-12	10002
103	Charlie	1992-11-23	90001
104	David	1985-03-15	60601

Explanation:

Emp_ID is the primary key.
All attributes in this table depend directly on Emp_ID.

Location Table

This table will store the location details based on Zip.

Zip (PK)	Area	City	State
10001	Downtown	New York	NY
10002	Midtown	New York	NY
90001	Central	Los Angeles	CA
60601	West End	Chicago	IL

Explanation:

Zip is the primary key.
Area, City, and State depend directly on Zip.

Final Relational Model

After normalization:

Employee Table:
- Contains Emp_ID, Emp_Name, DOB, and Zip.
- No transitive dependencies exist in this table.
Location Table:
- Contains Zip, Area, City, and State.
- Location details are fully normalized and uniquely identified by Zip.

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible