To get a good database design it is essential to identify the relations and attributes of relations. A designer must make sure that it is simple and stable to store data. Normalization is the process that coverts a complex data structure into a simpler one. It is done by decomposition of complex relations into simple but related relations. This is an essential step to create a good database design. Functional Dependency is the technique to understand the relationship of attributes.
Definition- Functional Dependency
Let there be a relation R. Relation R contains two attributes A and B. We can say the attribute B is functionally dependent on attribute if and only if each value of A determines precisely one value of attribute B
A->B iff each value of B is determined by each values of A. A can be simple or composite attribute. It is termed as A determines B or B is functionally dependent on A
For example attribute A is total score and attribute B is the CGPA of a student. It can be said that CGPA is determined by Score or CGPA is functionally dependent on Score
Functional Dependency-Types
To understand the types of functional dependency we will be using this relation
Student_Result
{StuID Student ID
CourseID Course ID of the course
CourseTitle Detailed Description of Course
ProfName Name of the professor who teaches this course
RoomNo Room Number where course is taught
Score Score attained by student in this course
CGPA CGPA
}
Primary Key of Relation
StuID+ CourseID
Dependencies
CourseID-> CourseDesc
CourseID-> ProfName
ProfName->RoomNo
StuID+ CourseID-> Score
Score-> CGPA
Full Functional Dependency
In a relation R attribute B is fully functionally dependent on A if A completely determine B. A subset of A does not determine B.
In the example relation Score is fully functionally dependent on StuID+ CourseID and not on subset of StuID and CourseID. We can understand it like this that score is not determined alone by student ID or Course ID since a student can score only in a course.
Considering other attribute CourseTitle, it is not fully functionally dependent on StuID+ CourseID because subset of StuID+ CourseID ie CourseID is sufficient to determines the CourseTitle.
Partial Functional Dependency
In a relation R attribute B is partially functionally dependent on A if it is determined by a subset of A.
In the example relation ProfName, CourseTitle and RoomNo are partially functionally dependent on StuID+ CourseID since the subset of StuID + CourseID, CourseID determine the ProfName, CourseTitle and RoomNo attribute as one course can be taught by only one professor, a coursed can have only one title and a course is allocated a room.
Transitive Functional Dependency
In a relation R attribute if attribute B is functionally dependent on attribute A and attribute C is functionally dependent on attribute B then we can say that Attribute C is functionally dependent on Attribute A.
A, B and C are three attributes.
A -> B and B-> C => A -> C
In the example relation RoomNo functionally depends on ProfName and ProfName is functionally dependent on CourseID. We can say that RoomNo is functionally Dependent on CourseID
CourseID-> ProfName and ProfName->RoomNo => CourseID->RoomNo