What is a Functional Dependency?
• Functional Dependency (FD) determines the relation of one attribute
to another attribute in a database management system (DBMS)
system.
• X → Y ( if X is same then Y has to be same)
• Emp_id → Emp_name
Types of Functional Dependencies
• Trivial functional dependency
X -> Y is a trivial functional dependency if Y is a subset of X
For example X ->X, XY->X, XY ->Y
Qus L.H.S intersect R.H.S = ??
• Non-trivial functional dependency**
• X→ Y has a non-trivial functional dependency if B is not a subset of A.
Rules of Functional Dependencies X->Y
• Reflexive rule – If X is a set of attributes and Y is subset of X, then X
holds a value of Y.
• Augmentation rule: When X -> Y holds, and c is attribute set, then
Xc -> Yc also holds. That is adding attributes which do not change the
basic dependencies.
• Transitivity rule: This rule is very much similar to the transitive rule in
algebra if x -> y holds and y -> z holds, then x -> z also holds. X -> y is
called as functionally that determines y.
Rules of Functional Dependencies X->Y
• Union: If X → Y and X → Z then X → YZ
• Decomposition: If X → YZ then X → Y and X → Z
Qus. If XY->Z can I write X->Z & Y->Z
Normalization
• Normalization is the process of organizing the data in the database.
• Normalization is used to minimize the redundancy from a relation or
set of relations. It is also used to eliminate the undesirable
characteristics like Insertion, Update and Deletion Anomalies.
• Normalization divides the larger table into the smaller table and links
them using relationship.
• The normal form is used to reduce redundancy from the database
table.
First Normal Form (1NF)
• Each attribute of a relation contains only an atomic value.
• No Multivalued attribute only single valued
Student_id Name Subject
101 AK Computer Network, JAVA
102 VK DBMS, C++, JAVA
Software Engineering,
103 Amrita
Compiler Design
Second Normal Form (2NF)
• A relation must be in 1NF and
• No partial dependency should exist in the relation.
• Partial Dependency: If a non-prime attribute can be determined by
the part of the candidate key in a relation, it is known as a partial
dependency.
• if L.H.S is the proper subset of a candidate key and R.H.S is the non-
prime attribute, then it shows a partial dependency.
Partial Dependency &
Prime, Non-Prime attributes
• Partial Dependency: If a non-prime attribute can be determined by
the part of the candidate key in a relation, it is known as a partial
dependency.
• if L.H.S is the proper subset of a candidate key and R.H.S is the non-
prime attribute, then it shows a partial dependency.
• R(ABCD) && FD= AB->C, C->D, B->D && Candidate key=AB
Third Normal Form (3NF)
• A relation must be in second normal form (2NF) And there should be
no transitive functional dependency exists for non-prime attributes in
a relation.
Rollno State City
1 Punjab Chandigarh
2 Haryana Ambala
3 Punjab Chandigarh
4 Haryana Ambala
5 Uttar Pradesh Ghaziabad
Check whether a table in 3rd NF or Not
• In all FDs X->Y
• X is a super key or candidate key And Y is a prime attribute, i.e., Y is a
part of candidate key.
Boyce-Codd Normal Form (BCNF)
• A relation is in 3NF And for every functional dependency, X → Y, L.H.S
of the every functional dependency (X) be the super key of the table.
• R(ABCD) && FD: A->B, B->C, C->D, D->A
Fourth Normal Form
• A relation is in BCNF.
• And, there is no multivalued dependency exists in the relation.
• Multivalued dependency: For a dependency X → Y, if for a single
value of X, multiple values of Y exists, then the relation may have a
multi-valued dependency. It is represented by the double arrow
sign (→→).
Fourth Normal Form
STU_ID COURSE HOBBY
21 Computer Dancing
21 Math Singing
34 Chemistry Dancing
74 Biology Cricket
59 Physics Hockey
Fifth normal form (5NF)
• A relation is in 5NF if it is in 4NF and not contains any join
dependency and joining should be lossless.
• No spurious tuples
• 5NF is also known as Project-join normal form (PJ/NF).