TOPICS (Click to Navigate)
Tuesday, 19 April 2016
Determination of Normal Forms
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?
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?
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?
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?
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?
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.
Go to Normalization Solved Exercises and Questions page
SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...
Advanced concepts in DBMS Advanced Database Topics (Click on the links to navigate) Advanced Concepts in D...
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
Query Processing in DBMS / Steps involved in Query Processing in DBMS / How is a query gets processed in a Database Management System? / Q...