Monday, 29 February 2016

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.






No comments:

Post a Comment

SQL exercises for beginners one

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