Tuesday, 30 January 2018

Find the functional dependencies and normalize the table to 3nf

Find the functional dependencies, Find the candidate keys of a relation, How to find the candidate keys, Which is the key for the given table, concept of candidate key in dbms, candidate key examples, How many candidate keys are there for a table, Normalize the table to 3nf, Third Normal Form example

Question:
Consider a relation Student (StudentID, ModuleID, ModuleName, StudentName, StudentAddress, TutorId, TutorName). Each student is given a StudentID and each module given a ModuleID. A student can register more modules and a module can be registered by more students. TutorID is the ID of the student's personal tutor, it is not related to the modules that the student is taking. Each student has only one tutor, but a tutor can have many tutees. Different students can have the same name. Different students can be living at the same address.
Find all the functional dependencies holding in this relation and normalize the table to 3NF.

Solution:

Finding functional dependencies:

It is given that each student has unique ID and unique tutor. So,

  • StudentID → StudentName, StudentAddress, TutorId, TutorName

It is given that each module is uniquely identified by an ID. So,

  • ModuleID → ModuleName

Tutor is given a TutorID. Hence,

  • TutorID → TutorName

Finding candidate key(s):

Find closure for left hand side attributes of above functional dependencies.
(StudentID)+ = StudentID StudentName, StudentAddress, TutorID, TutorName
Closure of StudentID does not give complete Student table. Hence, StudentID is not a candidate key.
(ModuleID)+ = ModuleID, ModuleName

Closure of ModuleID does not give complete Student table. Hence, ModuleID is not a candidate key.
(TutorID)+ = TutorID, TutorName
Closure of TutorID does not give complete Student table. Hence, TutorID is not a candidate key.
(StudentID, ModuleID)+ = StudentID StudentName, StudentAddress, TutorID, TutorName, ModuleID, ModuleName = Student.

Hence, the combination (StudentID, ModuleID) is the only candidate key for Student relation.

Is Student in 2NF?

If there are partial dependencies in the relation, then the relation is not in 2NF.
In our question, the key is composite hence there are possibilities for partial dependencies.
  • StudentID alone identifies StudentID StudentName, StudentAddress, TutorID, TutorName attributes. This is one partial dependency.
  • ModuleID identifies ModuleID, ModuleName attributes uniquely. This is another partial dependency.
Due to these partial dependencies, Student relation is not in 2NF. So, we need to decompose Student further on individual partial dependencies. In that process Student becomes as follows;
Student (StudentID, StudentName, StudentAddress, TutorID, TutorName)
Module (ModuleID, ModuleName)

To establish a conection between Student and Module, we need to create a new relation (man-to-many relationship) as follows;
Stu_Module (StudentID, ModuleID)

All these three tables are in 2NF.

Are they in 3NF?

To be in 3NF, a relation should not have any transitive dependency (non-key functional dependency).
New Student relation has a functional dependency TutorID TutorName which is transitive. [How? StudentID → TutorID and TutorID TutorName]
Student relation is not in 3NF. To convert it to 3NF, decompose the relation using the violating functional dependency. In this process, we get the following relations;
Stu (StudentID, StudentName, StudentAddress) and
Tutor (TutorID, TutorName)
Both of these relations are in 3NF.

No transitive dependencies are found in the other two relations Module and Stu_Module. Hence they are also in 3NF.

Following are the final relation schemas in 3NF;
Stu (StudentID, StudentName, StudentAddress)  
Tutor (TutorID, TutorName)
Module (ModuleID, ModuleName)
Stu_Module (StudentID, ModuleID)

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


Go to - 1NF,    2NF,    3NF,    BCNF




 


SQL exercises for beginners one

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