For each of the following relations, identify the Normal Form(s) each relation s

Question

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}.

Explanation / Answer

Condition needs to satisfy 3NF:

1. Contains only atomic values(1 NF)

2. All the non key attributes are functionally dependant on primary key.(2NF)

3. No transitive functional dependancy (3NF)

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

1. All the values are atomic here, description clearly says one major per student - 1NF satisfied

2. Only one primary key in the relation and already satisfies 1NF - 2NF satisfied

3. No transitive functional dependancy among the columns in the relation - 3NF satisfied.

The relation is already in 3NF.

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

1.Since the column specialization can have more than one value, it is not satisfying the 1NF condition. So the table is further splited into

Employee(EmpID, Name)

Emp_specialization(EmpID, Specialization)

Now it satisfies the 1NF property.

2.Since there is only one Primarykey in the table and it already satisfies 1NF - 2NF satisfied

3. No transitive dependancy among the columns in this relation - 3NF satisfied.

Employee(EmpID, Name)

Emp_specialization(EmpID, Specialization)

Now the relation in 3NF

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

1. All the values are atomic here, description clearly says one rental rate, one building - 1NF satisfied

2. Since the Rent can retrieved only from BuildingID not from the PersonID directly, hence it forms composite key. So the relation is now not in 2NF. To proceed further split the relation further

Lease_person(PersonID, BuildingID)

Lease_Building(BuildingID, Rent)

Now it satisfies the 2NF

3. Now there is no transitive functional dependancy among the columns in the relation - 3NF satisfied.

Lease_person(PersonID, BuildingID)

Lease_Building(BuildingID, Rent)

Now the relation is in 3NF

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.