Download SEMESTER-II Functional Dependencies and more Summaries Database Management Systems (DBMS) in PDF only on Docsity!
For
SEMESTER-II
Paper II : Database Management Systems(CODE: CSC-RC-2016)
Topics: Functional dependencies, Normalization and Normal forms up to 3NF
Functional Dependencies
A functional dependency (FD) is a relationship between two attributes, typically between the primary key and other non-key attributes within a table. A functional dependency denoted by
X Y , is an association between two sets of attribute X and Y. Here, X is called the determinant , and Y is called the dependent.
For example, SIN ———-> Name, Address, Birthdate
Here, SIN determines Name, Address and Birthdate.So, SIN is the determinant and Name, Address and Birthdate are the dependents.
Rules of Functional Dependencies
- Reflexive rule : If Y is a subset of X, then X determines Y.
- Augmentation rule: It is also known as a partial dependency, says if X determines Y, then XZ determines YZ for any Z
- Transitivity rule: Transitivity says if X determines Y, and Y determines Z, then X must also determine Z
Types of functional dependency
The following are types functional dependency in DBMS:
- Fully-Functional Dependency
- Partial Dependency
- Transitive Dependency
- Trivial Dependency
- Multivalued Dependency
Full functional Dependency
A functional dependency XY is said to be a full functional dependency, if removal of any attribute A from X, the dependency does not hold any more. i.e. Y is fully functional dependent on X, if it is Functionally Dependent on X and not on any of the proper subset of X. For example,
{Emp_num,Proj_num} Hour Is a full functional dependency. Here, Hour is the working time by an employee in a project.
Partial functional Dependency
A functional dependency XY is said to be a partial functional dependency, if after removal of
any attribute A from X, the dependency does not holds. i.e. Y is dependent on a proper subset of X. So X is partially dependent on X.
For example,
If {Emp_num,Proj_num} → Emp_name but also Emp_num → Emp_name then Emp_name is
partially functionally dependent on {Empl_num,Proj_num}.
Transitive dependency
A functional dependency is X Z is said to be a transitive functional dependency if there exists the functional dependencies X Y and Y Z. i.e. it is an indirect relationship.
For example,
EMP_NUM JOB_CLASS is a transitive dependency which comes from EMP_NUM JOB_CLASS and JOB_CLASS CHG_HOUR
Trivial functional dependency
A functional dependency X Y is said to be a trivial functional dependency if Y is a subset of X.
For example,
{Emp_num,Emp_name} Emp_num
is a trivial functional dependency since Emp_num is a subset of {Emp_num,Emp_name}.
Multivalued dependency
Multivalued dependency occurs in the situation where there are multiple independent
multivalued attributes in a single table. A multivalued dependency is a complete constraint
between two sets of attributes in a relation. It requires that certain tuples be present in a relation.
Here, if we try to insert a record in STUDENT_COURSE with STUD_NO =7, it will not allow.
Deletion and Updation anomaly
If a tuple is deleted or updated from referenced relation and referenced attribute value is used by referencing attribute in referencing relation, it will not allow deleting the tuple from referenced relation. For Example,
Here, if we try to delete a record from STUDENT with STUD_NO =1, it will not allow.
The normal forms used for normalization are:
- First normal form(1NF)
- Second normal form(2NF)
- Third normal form(3NF)
- Boyce & Codd normal form (BCNF)
- Fourth normal form (4NF)
- Fifth normal form (5NF)
First normal form (1NF)
The first normal form is based on the simple or atomic attribute and single valued attribute. A relation is said to be in 1NF if all the attributes of the relation are atomic and single valued.
Example-
Suppose a company wants to store the names and contact details of its employees. It creates a table that looks like this:
Employee
Emp_id Emp_name Emp_address Emp_mobile
101 Herschel New Delhi 8912312390
102 Jon Kanpur
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123 8123450987 Here, the Relation employee is not in 1NF, because employees with employee id 102 and 104 are having two phone numbers. i.e. the Emp_mobile attribute is a multi valued attribute.
After normalization to 1NF the relation will be like this:
Emp_id Emp_name Emp_address Emp_mobile
101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
102 Jon Kanpur 9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
104 Lester Bangalore 8123450987
Example-
Consider the following table:
In the above table, Book ID determines Genre ID, and Genre ID determines Genre Type. Therefore, Book ID determines Genre Type via Genre ID and we have transitive functional dependency, and this structure does not satisfy third normal form.
To bring this table to 3NF, we split the table into two as follows:
Now all non-key attributes are fully functional dependent only on the primary key. In TABLE_BOOK, both Genre ID and Price are only dependent on Book ID. In TABLE_GENRE, Genre Type is only dependent on Genre ID.
