Tuesday, 19 April 2016

Determination of Normal Forms

Determine whether the given table is in 1NF, 2NF, 3NF etc., how to determine the normal form of a table? Normalization YES/NO questions, Normalization questions answered


Normalization Exercises

Consider a relation Teacher with the attributes Teacher_Name, Gender, DOB, Subject_Handles, School_Name, and School_Location. Assume that Teacher is in 1NF. Answer the following questions;

a. Assume that there are set of functional dependencies in Teacher. If (Teacher_Name, DOB) is the key for Teacher, is Teacher in 2NF?

CANNOT DETERMINE.

A table is in 2NF, if there is no partial key dependency. Here, the key for Teacher is a composite primary key. So, there are possibilities for partial key dependencies.
We cannot determine whether this table is in 2NF or not as the given information is not enough.

b. If Teacher_Name is the key for Teacher, is Teacher in 2NF?

YES.

The key for Teacher is formed by a single column, ie., Teacher_Name. If all the attributes of Teacher can be determined uniquely by Teacher_Name, then only Teacher_Name attribute forms the key. And, there are no possibilities for partial key dependencies. Hence, Teacher is in 2NF.

c. (Teacher_Name, DOB) is the key for Teacher, all the other attributes depend on the whole key, and only other FD is "School_Name determines the School_Location uniquely". Is Teacher in 2NF?

YES.

As per the given information, there is no partial key dependency present. Hence, the table is in 2NF.

d. (Teacher_Name, DOB) combination is the key for Teacher, all the other attributes depend on the whole key, and only other FD is "School_Name determines the School_Location uniquely". Is Teacher in 3NF?

NO.

For a table in 3NF, it should be in 2NF and non-key attribute dependencies (transitive dependencies) must not present.
From the given information, it is clear that the table is in 2NF. Teacher is not in 3NF because of the non-key dependency School_Name School_Location.

e. Assume a set of FDs in Teacher, and it is found that Teacher_Name is the key. If Teacher_Name is the key for Teacher, is Teacher in 3NF?

CANNOT DETERMINE.

Only information given here is the key for Teacher. If we are given the set of functional dependencies, then only we can decide. In this case we cannot determine.

*********************








No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...