Database design
Normalization
Б.Наранчимэг
Мэдээлэл, компьютерийн ухааны тэнхим
ХШУИС, МУИС
[email protected]
Next Advanced normal forms
• BCNF
• 4NF
• 5NF
Boyce-Codd Normal Form (BCNF)
• based on functional dependencies that take into account all
candidate keys in a relation
• however, BCNF also has additional constraints compared with the
general definition of 3NF
• Boyce–Codd Normal Form (BCNF)
• A relation is in BCNF, if and only if, every determinant is a candidate
key.
Boyce-Codd Normal Form (BCNF)
• 3NF vs BCNF
• for a functional dependency A→B
• 3NF allows this dependency in a relation if B is a primary-key attribute and A
is not a candidate key
• BCNF insists that for this dependency to remain in a relation, A must be a
candidate key.
• Boyce–Codd Normal Form is a stronger form of 3NF
• every relation in BCNF is also in 3NF. However, a relation in 3NF is not
necessarily in BCNF.
Dream Home: Property inspection reports
Functional dependencies of
StaffPropertyInspection
(propertyNo, iDate)
(staffNo, iDate, iTime)
(carReg, iDate, iTime)
2NF
• Property (propertyNo, pAddress)
• PropertyInspection (propertyNo, iDate, iTime, comments, staffNo,
sName, carReg)
3NF
• PropertyInspection Relation
• fd1 propertyNo, iDate → iTime, comments, staffNo, sName, carReg
• fd3 staffNo → sName
• fd4 staffNo, iDate → carReg
• fd5′ carReg, iDate, iTime → propertyNo, comments, staffNo, sName
• fd6′ staffNo, iDate, iTime → propertyNo, comments
• Staff (staffNo, sName)
• PropertyInspect (propertyNo, iDate, iTime, comments, staffNo, carReg)
UNF-3NF
• Property (propertyNo, pAddress)
• Staff (staffNo, sName)
• PropertyInspect (propertyNo, iDate, iTime, comments, staffNo, carReg)
Boyce–Codd Normal Form (BCNF)
• Property Relation
Every determinant must be candidate key
• fd2 propertyNo → pAddress
• Staff Relation
• fd3 staffNo → sName
• PropertyInspect Relation
• fd1′ propertyNo, iDate → iTime, comments, staffNo, carReg
• fd4 staffNo, iDate → carReg
• fd5′ carReg, iDate, iTime → propertyNo, comments, staffNo
• fd6′ staffNo, iDate, iTime → propertyNo, comments
Boyce–Codd Normal Form (BCNF)
• Property Relation
Every determinant must be candidate key
• fd2 propertyNo → pAddress
• Staff Relation
• fd3 staffNo → sName
• PropertyInspect Relation
• fd1′ propertyNo, iDate → iTime, comments, staffNo, carReg
• fd4 staffNo, iDate → carReg
• fd5′ carReg, iDate, iTime → propertyNo, comments, staffNo
• fd6′ staffNo, iDate, iTime → propertyNo, comments
Boyce–Codd Normal Form (BCNF)
• StaffCar (staffNo, iDate, carReg)
• Inspection (propertyNo, iDate, iTime, comments, staffNo)
Review of Normalization
Boyce-Codd Normal Form (BCNF)
• Violation of BNCF is quite rare.
• Potential to violate BCNF may occur in a relation:
• Contains two (or more) composite candidate keys;
• The candidate keys overlap (i.e. have at least one attribute in common)
fd1 clientNo, propertyNo → rentStart, rentFinish
3NF fd5′ clientNo, rentStart → propertyNo, rentFinish
fd6′ propertyNo, rentStart → clientNo, rentFinish
Fourth Normal Form (4NF)
• Multivalued dependency (MVD)
• Represents a dependency between attributes (for example, A, B, and C) in a
relation, such that for each value of A there is a set of values for B and a set of
values for C. However, the set of values for B and C are independent of each
other.
• A ⎯>> B
• A ⎯>> C
Fourth Normal Form (4NF)
branchNo ⎯>> sName
branchNo ⎯>> oName
4NF: A relation that is in Boyce–Codd normal form and does not
contain nontrivial multi-valued dependencies.
Fifth Normal Form (5NF)
• Lossless-join dependency
• A property of decomposition, which ensures that no spurious tuples are
generated when relations are reunited through a natural join operation.
• Fifth Normal Form (5NF)
• A relation that has no join dependency.
• Normalization into 5NF is very rarely done in practice
Fifth Normal Form (5NF)
5NF
