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.

Go back to other Normalization Process Exercises