## 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