Advanced Database Management System - Tutorials and Notes: Find the functional dependenccies of the given relation

## How to check whether a functional dependency hold in a relation R, Check the functional dependencies of a relation, Functional dependencies satisfied or not?, Find all the functional dependencies or a table

Exercise
For the following Student table instance, find all the possible functional dependencies that are held. Do not include trivial FDs.
[ Schema - Student (Regno, Name, DOB, Phone, Gender, Course_ID, Course_Name, Instructor_ID, Instructor_Name, Instructor_Office)]

 Regno Name DOB Phone Gender CID CName Ins_ID Ins_Name Ins_Office 14M01 Kumar 12-Jan-1996 12345 M C1 DBMS I1 Kesav G123 14M05 Mary 10-Jun-1995 12367 F C1 DBMS I1 Kesav G123 14M07 Ram 10-May-1996 12898 M C1 DBMS I2 Ragav G127 14M01 Kumar 12-Jan-1996 12345 M C3 DS I5 Mani G125 14B01 Revathi 10-Dec-1995 23456 F C3 DS I5 Mani G125 14M09 Steve 23-Oct-1995 34567 M C4 OS I5 Mani G125 14B03 Ramya 20-Jul-1996 23456 F C4 OS I5 Mani G125

Solution:
Functional Dependency:
X → Y
-         X may be one or more attributes, and Y may be one or more attributes.
-         In this FD, X is the determinant set and Y is the dependent set.
-         We would read this as, “Y is dependent on X”, or “X determines the value of Y uniquely”

The following functional dependencies are held in Student;
Regno → Name
– Names are uniquely identified by a regno. In other words, for a given register number, there is exactly one name.
Regno → DOB
-         For any given register number in Student, there is exactly one DOB value.
Regno → Phone
Regno → Gender
You can write the above FDs collectively as follows;
Regno → Name, DOB, Phone, Gender
-         For any given register number of Student table, you would get unique name, DOB, phone, and gender values.
CID → CName
CName → CID
-         For the given instance, this FD is true. This FD may not be correct in some cases. For example, if suppose DBMS is offered for two different programs, say B.Tech and M.Tech with two different course IDs C1 and C10, then this FD does not hold in Student. You need to verify the semantics and the permitted values for a column carefully.
Ins_ID → Ins_Name, Ins_Office
Regno CID → Name, DOB, Phone, Gender, CName
-         Regno and CID together, i.e., any combination of these two values can uniquely identify the values of name, dob, phone, gender, and cname.
Regno CID Ins_ID → Name, DOB, Phone, Gender, CName, Ins_Name, Ins_Office
-         The combination of regno, cid and ins_id values can uniquely identify all the other attributes of the table Student. Hence, the combination (Regno, CID, Ins_ID) is the key for Student.

Example of the functional dependencies (Not all) that are not holding on Student:
Phone → Regno
-         As per the given instance of Student, for a given phone value 23456, there are two students with register numbers 14B01, and 14B03. Hence, this FD does not hold on Student.
CID → Ins_ID
-         CID C1 is taught by two instructors I1 and I2. Hence, this FD does not hold.
DOB → Regno, Name, Phone, Gender
-         Our Student instance does not contain duplicate values for the column DOB. But in reality, there may be more than one student with the same DOB. Hence, we cannot use DOB as the determiner.
The other set of FDs (if any), you are requested to try.