For each of the following relations identify the Normal Form

For each of the following relations, identify the Normal Form(s) each relation satisfies, and transform it into 3NF.

Consider the relation STUDENT, where a student can have only one major:

RELATION = STUDENT (StuID, StuName, Major), Primary Key = {StuID}.

Consider the relation EMPLOYEE, where an employee can have more than one specialization:

RELATION = EMPLOYEE (EmpID, Name, Specialization), Primary Key = {EmpID}.

Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate:

RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key – {PersonID}.

Solution

1. For the relation:

RELATION = STUDENT (StuID, StuName, Major), Primary Key = {StuID}

The relation is in 3NF as StuName and Major are dependent on StuID. Considering that a single student can have only one Major, we won\'t be getting any redundant entries for the table with StuID as the Primary key for the same. For example:

Here, every StuID has a particular StuName which in turn has a particular Major. Hence the relation is said to be in 3NF.

2. For the relation:

RELATION = EMPLOYEE (EmpID, Name, Specialization), Primary Key = {EmpID}

The relation is not in a normalized form. Here, a single employee can have more than one specialization. Hence we need to normalize the relation For example:

Here, Brian has 2 specializations. Hence after normalizing the relation to 1NF, it becomes:

If we follow closely, Name is derived from EmpID. Hence we can separate it to convert to 3NF. The final structure becomes:

3. For the relation:

RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key – {PersonID}

The relation is in 2NF. The reason is that multiple persons can live in the same building. Now, the rent for the building depends on the BuildingID and not on the Primary key PersonID. Hence to convert it to 3NF, we need to remove the Rent column and store in separate table.

We can clearly see the redundancy here. Now lets convert it to 3NF :