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 Normalization - Solved Exercises page
Go to How to find closure? page