joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · introduction...
TRANSCRIPT
![Page 1: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/1.jpg)
Databases
Jörg Endrullis
VU University Amsterdam
2015
![Page 2: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/2.jpg)
Relational Normal Forms
Overview
1. Functional Dependencies (FDs)
2. Anomalies, FD-based Normal Forms
3. Multivalued Dependencies (MVDs) and 4NF
4. Normal Forms and ER Design
5. Denormalization
![Page 3: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/3.jpg)
Introduction
Functional Dependencies (FDs)are a generalization of keyscentral part of relational database design theory
This theory defines when a relation is in normal form.(with respect to a given set of functional dependencies)
It is usually a sign of bad database design if a schemacontains relations that violate the normal form.If a normal form is violated
data is stored redundantly andinformation about different concepts is intermixed
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
The phone number for each instructor is stored multiple times!
![Page 4: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/4.jpg)
Introduction
Functional Dependencies (FDs)are a generalization of keyscentral part of relational database design theory
This theory defines when a relation is in normal form.(with respect to a given set of functional dependencies)
It is usually a sign of bad database design if a schemacontains relations that violate the normal form.If a normal form is violated
data is stored redundantly andinformation about different concepts is intermixed
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
The phone number for each instructor is stored multiple times!
![Page 5: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/5.jpg)
Introduction
Functional Dependencies (FDs)are a generalization of keyscentral part of relational database design theory
This theory defines when a relation is in normal form.(with respect to a given set of functional dependencies)
It is usually a sign of bad database design if a schemacontains relations that violate the normal form.
If a normal form is violateddata is stored redundantly andinformation about different concepts is intermixed
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
The phone number for each instructor is stored multiple times!
![Page 6: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/6.jpg)
Introduction
Functional Dependencies (FDs)are a generalization of keyscentral part of relational database design theory
This theory defines when a relation is in normal form.(with respect to a given set of functional dependencies)
It is usually a sign of bad database design if a schemacontains relations that violate the normal form.If a normal form is violated
data is stored redundantly andinformation about different concepts is intermixed
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
The phone number for each instructor is stored multiple times!
![Page 7: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/7.jpg)
Introduction
Functional Dependencies (FDs)are a generalization of keyscentral part of relational database design theory
This theory defines when a relation is in normal form.(with respect to a given set of functional dependencies)
It is usually a sign of bad database design if a schemacontains relations that violate the normal form.If a normal form is violated
data is stored redundantly andinformation about different concepts is intermixed
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
The phone number for each instructor is stored multiple times!
![Page 8: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/8.jpg)
Introduction
Functional Dependencies (FDs)are a generalization of keyscentral part of relational database design theory
This theory defines when a relation is in normal form.(with respect to a given set of functional dependencies)
It is usually a sign of bad database design if a schemacontains relations that violate the normal form.If a normal form is violated
data is stored redundantly andinformation about different concepts is intermixed
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
The phone number for each instructor is stored multiple times!
![Page 9: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/9.jpg)
Introduction
There are different normal forms. The main ones are:
Third Normal Form (3NF): the standard relational normalform used in practice (and education).
Boyce-Codd Normal Form (BCNF):a bit more restrictiveeasier to definebetter for our intuition of good database design
Roughly speaking, BNCF requires that all FDs are keys.
In rare circumstances, a relation might not have anequivalent BCNF form while preserving all its FDs.The 3NF normal form always exists (and preserves the FDs).
![Page 10: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/10.jpg)
Introduction
There are different normal forms. The main ones are:
Third Normal Form (3NF): the standard relational normalform used in practice (and education).
Boyce-Codd Normal Form (BCNF):a bit more restrictiveeasier to definebetter for our intuition of good database design
Roughly speaking, BNCF requires that all FDs are keys.
In rare circumstances, a relation might not have anequivalent BCNF form while preserving all its FDs.The 3NF normal form always exists (and preserves the FDs).
![Page 11: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/11.jpg)
Introduction
There are different normal forms. The main ones are:
Third Normal Form (3NF): the standard relational normalform used in practice (and education).
Boyce-Codd Normal Form (BCNF):a bit more restrictiveeasier to definebetter for our intuition of good database design
Roughly speaking, BNCF requires that all FDs are keys.
In rare circumstances, a relation might not have anequivalent BCNF form while preserving all its FDs.The 3NF normal form always exists (and preserves the FDs).
![Page 12: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/12.jpg)
Introduction
There are different normal forms. The main ones are:
Third Normal Form (3NF): the standard relational normalform used in practice (and education).
Boyce-Codd Normal Form (BCNF):a bit more restrictiveeasier to definebetter for our intuition of good database design
Roughly speaking, BNCF requires that all FDs are keys.
In rare circumstances, a relation might not have anequivalent BCNF form while preserving all its FDs.The 3NF normal form always exists (and preserves the FDs).
![Page 13: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/13.jpg)
Introduction
There are different normal forms. The main ones are:
Third Normal Form (3NF): the standard relational normalform used in practice (and education).
Boyce-Codd Normal Form (BCNF):a bit more restrictiveeasier to definebetter for our intuition of good database design
Roughly speaking, BNCF requires that all FDs are keys.
In rare circumstances, a relation might not have anequivalent BCNF form while preserving all its FDs.The 3NF normal form always exists (and preserves the FDs).
![Page 14: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/14.jpg)
Introduction
Normalization algorithms can construct good relationschemas from a set of attributes and a set of functionaldependencies.
In practice:relations are derived from ER modelsnormalization is used as an additional check only
When an ER model is well designed, the resulting derivedrelational tables will automatically be in BCNF.
Awareness of normal forms can help to detect designerrors already in the conceptual design phase.
![Page 15: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/15.jpg)
Introduction
Normalization algorithms can construct good relationschemas from a set of attributes and a set of functionaldependencies.
In practice:relations are derived from ER modelsnormalization is used as an additional check only
When an ER model is well designed, the resulting derivedrelational tables will automatically be in BCNF.
Awareness of normal forms can help to detect designerrors already in the conceptual design phase.
![Page 16: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/16.jpg)
Introduction
Normalization algorithms can construct good relationschemas from a set of attributes and a set of functionaldependencies.
In practice:relations are derived from ER modelsnormalization is used as an additional check only
When an ER model is well designed, the resulting derivedrelational tables will automatically be in BCNF.
Awareness of normal forms can help to detect designerrors already in the conceptual design phase.
![Page 17: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/17.jpg)
Introduction
Normalization algorithms can construct good relationschemas from a set of attributes and a set of functionaldependencies.
In practice:relations are derived from ER modelsnormalization is used as an additional check only
When an ER model is well designed, the resulting derivedrelational tables will automatically be in BCNF.
Awareness of normal forms can help to detect designerrors already in the conceptual design phase.
![Page 18: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/18.jpg)
First Normal Form
The First Normal Form (1NF) requires that all table entriesare atomic (not lists, sets, records, relations).
The relational model all table entries are already atomic.All further normal forms assume that tables are in 1NF.
The following are not violations of 1NF:A table entry contains values with internal structure.
e.g. a CHAR(100) containing a comma separated listList represented by several columns.
e.g. columns value1, value2, value3
Nevertheless, these are bad design.
![Page 19: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/19.jpg)
First Normal Form
The First Normal Form (1NF) requires that all table entriesare atomic (not lists, sets, records, relations).
The relational model all table entries are already atomic.
All further normal forms assume that tables are in 1NF.
The following are not violations of 1NF:A table entry contains values with internal structure.
e.g. a CHAR(100) containing a comma separated listList represented by several columns.
e.g. columns value1, value2, value3
Nevertheless, these are bad design.
![Page 20: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/20.jpg)
First Normal Form
The First Normal Form (1NF) requires that all table entriesare atomic (not lists, sets, records, relations).
The relational model all table entries are already atomic.All further normal forms assume that tables are in 1NF.
The following are not violations of 1NF:A table entry contains values with internal structure.
e.g. a CHAR(100) containing a comma separated listList represented by several columns.
e.g. columns value1, value2, value3
Nevertheless, these are bad design.
![Page 21: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/21.jpg)
First Normal Form
The First Normal Form (1NF) requires that all table entriesare atomic (not lists, sets, records, relations).
The relational model all table entries are already atomic.All further normal forms assume that tables are in 1NF.
The following are not violations of 1NF:
A table entry contains values with internal structure.e.g. a CHAR(100) containing a comma separated list
List represented by several columns.e.g. columns value1, value2, value3
Nevertheless, these are bad design.
![Page 22: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/22.jpg)
First Normal Form
The First Normal Form (1NF) requires that all table entriesare atomic (not lists, sets, records, relations).
The relational model all table entries are already atomic.All further normal forms assume that tables are in 1NF.
The following are not violations of 1NF:A table entry contains values with internal structure.
e.g. a CHAR(100) containing a comma separated list
List represented by several columns.e.g. columns value1, value2, value3
Nevertheless, these are bad design.
![Page 23: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/23.jpg)
First Normal Form
The First Normal Form (1NF) requires that all table entriesare atomic (not lists, sets, records, relations).
The relational model all table entries are already atomic.All further normal forms assume that tables are in 1NF.
The following are not violations of 1NF:A table entry contains values with internal structure.
e.g. a CHAR(100) containing a comma separated listList represented by several columns.
e.g. columns value1, value2, value3
Nevertheless, these are bad design.
![Page 24: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/24.jpg)
First Normal Form
The First Normal Form (1NF) requires that all table entriesare atomic (not lists, sets, records, relations).
The relational model all table entries are already atomic.All further normal forms assume that tables are in 1NF.
The following are not violations of 1NF:A table entry contains values with internal structure.
e.g. a CHAR(100) containing a comma separated listList represented by several columns.
e.g. columns value1, value2, value3
Nevertheless, these are bad design.
![Page 25: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/25.jpg)
Functional Dependencies
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
A functional dependency (FD) in this table is
INAME→ PHONE
Whenever two rows of a relation agree in the instructor nameINAME, they must also agree in the PHONE column values!
![Page 26: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/26.jpg)
Functional Dependencies
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
A functional dependency (FD) in this table is
INAME→ PHONE
Whenever two rows of a relation agree in the instructor nameINAME, they must also agree in the PHONE column values!
![Page 27: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/27.jpg)
Functional Dependencies
Intuitively, there is a functional dependencyINAME→ PHONE
since the phone number only depends on the instructor, noton other course data.
This functional dependency read asINAME {functionally, uniquely} determines PHONE
Also INAME is a determinant for PHONE .
A determinant is a ‘minimal’ functional dependency.
A determinant is like a partial key:uniquely determines some attributes, but not all in general
E.g. INAME→ TITLE is not satisfied.
![Page 28: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/28.jpg)
Functional Dependencies
Intuitively, there is a functional dependencyINAME→ PHONE
since the phone number only depends on the instructor, noton other course data.
This functional dependency read asINAME {functionally, uniquely} determines PHONE
Also INAME is a determinant for PHONE .
A determinant is a ‘minimal’ functional dependency.
A determinant is like a partial key:uniquely determines some attributes, but not all in general
E.g. INAME→ TITLE is not satisfied.
![Page 29: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/29.jpg)
Functional Dependencies
Intuitively, there is a functional dependencyINAME→ PHONE
since the phone number only depends on the instructor, noton other course data.
This functional dependency read asINAME {functionally, uniquely} determines PHONE
Also INAME is a determinant for PHONE .
A determinant is a ‘minimal’ functional dependency.
A determinant is like a partial key:uniquely determines some attributes, but not all in general
E.g. INAME→ TITLE is not satisfied.
![Page 30: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/30.jpg)
Functional Dependencies
Intuitively, there is a functional dependencyINAME→ PHONE
since the phone number only depends on the instructor, noton other course data.
This functional dependency read asINAME {functionally, uniquely} determines PHONE
Also INAME is a determinant for PHONE .
A determinant is a ‘minimal’ functional dependency.
A determinant is like a partial key:uniquely determines some attributes, but not all in general
E.g. INAME→ TITLE is not satisfied.
![Page 31: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/31.jpg)
Functional Dependencies
Intuitively, there is a functional dependencyINAME→ PHONE
since the phone number only depends on the instructor, noton other course data.
This functional dependency read asINAME {functionally, uniquely} determines PHONE
Also INAME is a determinant for PHONE .
A determinant is a ‘minimal’ functional dependency.
A determinant is like a partial key:uniquely determines some attributes, but not all in general
E.g. INAME→ TITLE is not satisfied.
![Page 32: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/32.jpg)
Functional Dependencies
Intuitively, there is a functional dependencyINAME→ PHONE
since the phone number only depends on the instructor, noton other course data.
This functional dependency read asINAME {functionally, uniquely} determines PHONE
Also INAME is a determinant for PHONE .
A determinant is a ‘minimal’ functional dependency.
A determinant is like a partial key:uniquely determines some attributes, but not all in general
E.g. INAME→ TITLE is not satisfied.
![Page 33: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/33.jpg)
Functional Dependencies
In general, an functional dependencies take the form
A1, . . . ,An → B1, . . . ,Bm
sequence of attributes is unimportantboth sides formally are sets of attributes
{A1, . . . ,An}→ {B1, . . . ,Bm}
The functional dependency (FD)
A1, . . .An → B1, . . .Bm
holds for a relation R in a database state I if and only if for alltuples t ,u ∈ I(R):
t .A1 = u.A1 ∧ · · ·∧ t .An = u.An⇒ t .B1 = u.B1 ∧ · · ·∧ t .Bm = u.Bm
![Page 34: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/34.jpg)
Functional Dependencies
In general, an functional dependencies take the form
A1, . . . ,An → B1, . . . ,Bm
sequence of attributes is unimportant
both sides formally are sets of attributes
{A1, . . . ,An}→ {B1, . . . ,Bm}
The functional dependency (FD)
A1, . . .An → B1, . . .Bm
holds for a relation R in a database state I if and only if for alltuples t ,u ∈ I(R):
t .A1 = u.A1 ∧ · · ·∧ t .An = u.An⇒ t .B1 = u.B1 ∧ · · ·∧ t .Bm = u.Bm
![Page 35: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/35.jpg)
Functional Dependencies
In general, an functional dependencies take the form
A1, . . . ,An → B1, . . . ,Bm
sequence of attributes is unimportantboth sides formally are sets of attributes
{A1, . . . ,An}→ {B1, . . . ,Bm}
The functional dependency (FD)
A1, . . .An → B1, . . .Bm
holds for a relation R in a database state I if and only if for alltuples t ,u ∈ I(R):
t .A1 = u.A1 ∧ · · ·∧ t .An = u.An⇒ t .B1 = u.B1 ∧ · · ·∧ t .Bm = u.Bm
![Page 36: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/36.jpg)
Functional Dependencies
In general, an functional dependencies take the form
A1, . . . ,An → B1, . . . ,Bm
sequence of attributes is unimportantboth sides formally are sets of attributes
{A1, . . . ,An}→ {B1, . . . ,Bm}
The functional dependency (FD)
A1, . . .An → B1, . . .Bm
holds for a relation R in a database state I if and only if for alltuples t ,u ∈ I(R):
t .A1 = u.A1 ∧ · · ·∧ t .An = u.An⇒ t .B1 = u.B1 ∧ · · ·∧ t .Bm = u.Bm
![Page 37: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/37.jpg)
Functional Dependencies
A key uniquely determines all attributes of its relation.There are never two distinct rows with the same key, so thefunctional dependency condition is trivially satisfied.
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
We have the following functional dependencies:CRN→ TITLE, INAME, PHONE
or equivalently:CRN→ TITLE
CRN→ INAME
CRN→ PHONE
![Page 38: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/38.jpg)
Functional Dependencies
A key uniquely determines all attributes of its relation.There are never two distinct rows with the same key, so thefunctional dependency condition is trivially satisfied.
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
We have the following functional dependencies:CRN→ TITLE, INAME, PHONE
or equivalently:CRN→ TITLE
CRN→ INAME
CRN→ PHONE
![Page 39: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/39.jpg)
Functional Dependencies
A key uniquely determines all attributes of its relation.There are never two distinct rows with the same key, so thefunctional dependency condition is trivially satisfied.
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
We have the following functional dependencies:CRN→ TITLE, INAME, PHONE
or equivalently:CRN→ TITLE
CRN→ INAME
CRN→ PHONE
![Page 40: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/40.jpg)
Functional Dependencies
An functional dependency with m attributes on the right
A1, . . .An → B1, . . .Bm
is equivalent to the m FDs:
A1, . . . ,An → B1...
...A1, . . . ,An → Bm
Thus, in the following it suffices to consider FDs with a singlecolumn name on the right-hand side.
![Page 41: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/41.jpg)
Functional Dependencies
An functional dependency with m attributes on the right
A1, . . .An → B1, . . .Bm
is equivalent to the m FDs:
A1, . . . ,An → B1...
...A1, . . . ,An → Bm
Thus, in the following it suffices to consider FDs with a singlecolumn name on the right-hand side.
![Page 42: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/42.jpg)
Functional Dependencies are Keys
Functional dependencies are constraints (like keys).
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
In this example state, the functional dependencyTITLE→ CRN
holds. But this is probably not true in general!It is a task of DB design to verify if this is mere coincidence.
For the database design process, the only interesting functionaldependencies are those that hold for all possible states.
![Page 43: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/43.jpg)
Functional Dependencies are Keys
Functional dependencies are constraints (like keys).
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
In this example state, the functional dependencyTITLE→ CRN
holds.
But this is probably not true in general!It is a task of DB design to verify if this is mere coincidence.
For the database design process, the only interesting functionaldependencies are those that hold for all possible states.
![Page 44: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/44.jpg)
Functional Dependencies are Keys
Functional dependencies are constraints (like keys).
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
In this example state, the functional dependencyTITLE→ CRN
holds. But this is probably not true in general!It is a task of DB design to verify if this is mere coincidence.
For the database design process, the only interesting functionaldependencies are those that hold for all possible states.
![Page 45: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/45.jpg)
Functional Dependencies are Keys
Functional dependencies are constraints (like keys).
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
In this example state, the functional dependencyTITLE→ CRN
holds. But this is probably not true in general!It is a task of DB design to verify if this is mere coincidence.
For the database design process, the only interesting functionaldependencies are those that hold for all possible states.
![Page 46: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/46.jpg)
Functional Dependencies are Keys
Functional dependencies are a generalisation of keys.
A1, . . . ,An is a key of relation R(A1, . . . ,An,B1, . . . ,Bm)⇐⇒the functional dependency A1, . . . ,An → B1, . . .Bm holds.
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
Here CRN→ TITLE, INAME, PHONE.
![Page 47: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/47.jpg)
Functional Dependencies are Keys
Functional dependencies are a generalisation of keys.
A1, . . . ,An is a key of relation R(A1, . . . ,An,B1, . . . ,Bm)⇐⇒the functional dependency A1, . . . ,An → B1, . . .Bm holds.
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
Here CRN→ TITLE, INAME, PHONE.
![Page 48: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/48.jpg)
Functional Dependencies are Keys
Functional dependencies are partial keys.
The functional dependency
A1, . . .An → B1, . . .Bm
holds for a relation R if { A1, . . .An } is a key for the relationobtained by restricting R to the columns {A1, . . .An,B1, . . .Bm}.
The restriction of the table COURSES to { INAME, PHONE } is:
COURSES
INAME PHONEGrust 7111Klotz 2418
The attribute INAME is a key of this table.
The goal of database normalization is to turn FDs into keys.The DBMS is then able to enforce the FDs for the user.
![Page 49: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/49.jpg)
Functional Dependencies are Keys
Functional dependencies are partial keys.
The functional dependency
A1, . . .An → B1, . . .Bm
holds for a relation R if { A1, . . .An } is a key for the relationobtained by restricting R to the columns {A1, . . .An,B1, . . .Bm}.
The restriction of the table COURSES to { INAME, PHONE } is:
COURSES
INAME PHONEGrust 7111Klotz 2418
The attribute INAME is a key of this table.
The goal of database normalization is to turn FDs into keys.The DBMS is then able to enforce the FDs for the user.
![Page 50: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/50.jpg)
Functional Dependencies are Keys
Functional dependencies are partial keys.
The functional dependency
A1, . . .An → B1, . . .Bm
holds for a relation R if { A1, . . .An } is a key for the relationobtained by restricting R to the columns {A1, . . .An,B1, . . .Bm}.
The restriction of the table COURSES to { INAME, PHONE } is:
COURSES
INAME PHONEGrust 7111Klotz 2418
The attribute INAME is a key of this table.
The goal of database normalization is to turn FDs into keys.The DBMS is then able to enforce the FDs for the user.
![Page 51: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/51.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
![Page 52: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/52.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
The ISBN uniquely identifies a book. Thus
ISBN→ TITLE, PUBLISHER
EquivalentlyISBN→ TITLE, andISBN→ PUBLISHER
![Page 53: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/53.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
A book may have many authors. Thus
ISBN→ AUTHOR
does not hold!
![Page 54: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/54.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
One author can write many books, thus
AUTHOR→ TITLE
does not hold in general.
Although it happens to hold in the above database state.
![Page 55: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/55.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
There may be books with the same title but differentauthors and different publishers. So
TITLE
determines no other attributes.
![Page 56: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/56.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
Every book has only one first (second, third, . . . ) author.Thus
ISBN, NO→ AUTHOR
![Page 57: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/57.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
At first glance, the author of any given book is alsouniquely assigned a position in the authorship sequence.
ISBN, AUTHOR→ NO ? questionable
However, violated by an author list like Smith & Smith.
![Page 58: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/58.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
What about the functional dependency
PUBLISHER, TITLE, NO→ AUTHOR ? questionable
Authorship sequence might change in a new edition of abook!
![Page 59: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/59.jpg)
Example
The following tables contains books and their authors:BOOKS
AUTHOR NO TITLE PUBLISHER ISBNElmasri 1 Fund. of DBS Addison-W. 0805317554Navathe 2 Fund. of DBS Addison-W. 0805317554
Silberschatz 1 DBS Concepts Mc-Graw H. 0471365084Korth 2 DBS Concepts Mc-Graw H. 0471365084
Sudarshan 3 DBS Concepts Mc-Graw H. 0471365084
a book may have multiple authors, one author per rowattribute NO is used to indicate the order of the authors
During database design, only unquestionable conditionsshould be used as functional dependencies.
Database normalization alters the table structuredepending on the specified functional dependencies.Later hard to change: needs creation/deletion of tables!
![Page 60: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/60.jpg)
Quiz
A table with homework grades:
HOMEWORK_RESULTS
STUD_ID FIRST LAST EX_NO POINTS MAX_POINTS100 Andrew Smith 1 9 10101 Dave Jones 1 8 10102 Maria Brown 1 10 10101 Dave Jones 2 11 12102 Maria Brown 2 10 12
Which FDs should hold for this table in general?Identify FDs that hold in this table but not in general.
![Page 61: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/61.jpg)
Implication of Functional Dependencies
Whenever A→ B and B → C hold, then A→ C is automaticallysatisfied.
Note that CRN→ PHONE is a consequence of
CRN→ INAME and INAME→ PHONE
FDs of the form A→ A always hold.
PHONE→ PHONE holds, but is not interesting
Implication of Functional DependenciesA set of FDs {α1 → β1, . . . , αn → βn} implies an FD α→ β ifand only if every DB state which satisfies all αi → βi ,1 6 i 6 n,also satisfies α→ β.
![Page 62: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/62.jpg)
Implication of Functional Dependencies
Whenever A→ B and B → C hold, then A→ C is automaticallysatisfied.
Note that CRN→ PHONE is a consequence of
CRN→ INAME and INAME→ PHONE
FDs of the form A→ A always hold.
PHONE→ PHONE holds, but is not interesting
Implication of Functional DependenciesA set of FDs {α1 → β1, . . . , αn → βn} implies an FD α→ β ifand only if every DB state which satisfies all αi → βi ,1 6 i 6 n,also satisfies α→ β.
![Page 63: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/63.jpg)
Implication of Functional Dependencies
Whenever A→ B and B → C hold, then A→ C is automaticallysatisfied.
Note that CRN→ PHONE is a consequence of
CRN→ INAME and INAME→ PHONE
FDs of the form A→ A always hold.
PHONE→ PHONE holds, but is not interesting
Implication of Functional DependenciesA set of FDs {α1 → β1, . . . , αn → βn} implies an FD α→ β ifand only if every DB state which satisfies all αi → βi ,1 6 i 6 n,also satisfies α→ β.
![Page 64: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/64.jpg)
Implication of Functional Dependencies
The DB designer is normally not interested in all FDs, but onlyin a representative FD set that implies all other FDs.
Armstrong AxiomsReflexivity:If β ⊆ α, then α→ β.Augmentation:If α→ β, then α ∪ γ→ β ∪ γ.Transitivity:If α→ β and β→ γ, then α→ γ.
Use the Amstrong axioms to show that
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
implies ISBN→ PUB_URL.
![Page 65: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/65.jpg)
Implication of Functional Dependencies
The DB designer is normally not interested in all FDs, but onlyin a representative FD set that implies all other FDs.
Armstrong Axioms
Reflexivity:If β ⊆ α, then α→ β.Augmentation:If α→ β, then α ∪ γ→ β ∪ γ.Transitivity:If α→ β and β→ γ, then α→ γ.
Use the Amstrong axioms to show that
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
implies ISBN→ PUB_URL.
![Page 66: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/66.jpg)
Implication of Functional Dependencies
The DB designer is normally not interested in all FDs, but onlyin a representative FD set that implies all other FDs.
Armstrong AxiomsReflexivity:If β ⊆ α, then α→ β.
Augmentation:If α→ β, then α ∪ γ→ β ∪ γ.Transitivity:If α→ β and β→ γ, then α→ γ.
Use the Amstrong axioms to show that
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
implies ISBN→ PUB_URL.
![Page 67: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/67.jpg)
Implication of Functional Dependencies
The DB designer is normally not interested in all FDs, but onlyin a representative FD set that implies all other FDs.
Armstrong AxiomsReflexivity:If β ⊆ α, then α→ β.Augmentation:If α→ β, then α ∪ γ→ β ∪ γ.
Transitivity:If α→ β and β→ γ, then α→ γ.
Use the Amstrong axioms to show that
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
implies ISBN→ PUB_URL.
![Page 68: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/68.jpg)
Implication of Functional Dependencies
The DB designer is normally not interested in all FDs, but onlyin a representative FD set that implies all other FDs.
Armstrong AxiomsReflexivity:If β ⊆ α, then α→ β.Augmentation:If α→ β, then α ∪ γ→ β ∪ γ.Transitivity:If α→ β and β→ γ, then α→ γ.
Use the Amstrong axioms to show that
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
implies ISBN→ PUB_URL.
![Page 69: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/69.jpg)
Implication of Functional Dependencies
The DB designer is normally not interested in all FDs, but onlyin a representative FD set that implies all other FDs.
Armstrong AxiomsReflexivity:If β ⊆ α, then α→ β.Augmentation:If α→ β, then α ∪ γ→ β ∪ γ.Transitivity:If α→ β and β→ γ, then α→ γ.
Use the Amstrong axioms to show that
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
implies ISBN→ PUB_URL.
![Page 70: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/70.jpg)
Implication of Functional Dependencies
Simpler way to check whether a→ β is implied by an FD set:compute the cover α+ of α, andthen check if β ⊆ α+.
CoverThe cover α+
F ofa set of attributes αwith respect to an FD set F
is the set of all attributes B that are uniquely determined by α:
α+F := { B | F implies α→ B }
Implication CheckA set of FDs F implies an FD α→ β if and only if β ⊆ α+
F .
![Page 71: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/71.jpg)
Implication of Functional Dependencies
Simpler way to check whether a→ β is implied by an FD set:compute the cover α+ of α, andthen check if β ⊆ α+.
CoverThe cover α+
F ofa set of attributes αwith respect to an FD set F
is the set of all attributes B that are uniquely determined by α:
α+F := { B | F implies α→ B }
Implication CheckA set of FDs F implies an FD α→ β if and only if β ⊆ α+
F .
![Page 72: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/72.jpg)
Implication of Functional Dependencies
Simpler way to check whether a→ β is implied by an FD set:compute the cover α+ of α, andthen check if β ⊆ α+.
CoverThe cover α+
F ofa set of attributes αwith respect to an FD set F
is the set of all attributes B that are uniquely determined by α:
α+F := { B | F implies α→ B }
Implication CheckA set of FDs F implies an FD α→ β if and only if β ⊆ α+
F .
![Page 73: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/73.jpg)
Implication of Functional Dependencies
Cover computationInput: α (set of attributes)
α1 → β1, . . . , αn → βn (set of FDs F)Output: α+ (the cover of α)
x = α;while x did change do
for all given FD αi → βi doif αi ⊆ x then
x = x ∪ βi ; (add attributes in βi to x)end if
end forend whilereturn x ;
![Page 74: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/74.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.2. The FD ISBN→ TITLE, PUBLISHER is applicable since the
left-hand side of is completely contained in x .We get x = {ISBN, TITLE, PUBLISHER}.
3. Now the FD PUBLISHER→ PUB_URL is applicable.We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.
4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 75: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/75.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.
2. The FD ISBN→ TITLE, PUBLISHER is applicable since theleft-hand side of is completely contained in x .We get x = {ISBN, TITLE, PUBLISHER}.
3. Now the FD PUBLISHER→ PUB_URL is applicable.We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.
4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 76: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/76.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.2. The FD ISBN→ TITLE, PUBLISHER is applicable since the
left-hand side of is completely contained in x .
We get x = {ISBN, TITLE, PUBLISHER}.3. Now the FD PUBLISHER→ PUB_URL is applicable.
We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 77: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/77.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.2. The FD ISBN→ TITLE, PUBLISHER is applicable since the
left-hand side of is completely contained in x .We get x = {ISBN, TITLE, PUBLISHER}.
3. Now the FD PUBLISHER→ PUB_URL is applicable.We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.
4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 78: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/78.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.2. The FD ISBN→ TITLE, PUBLISHER is applicable since the
left-hand side of is completely contained in x .We get x = {ISBN, TITLE, PUBLISHER}.
3. Now the FD PUBLISHER→ PUB_URL is applicable.
We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 79: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/79.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.2. The FD ISBN→ TITLE, PUBLISHER is applicable since the
left-hand side of is completely contained in x .We get x = {ISBN, TITLE, PUBLISHER}.
3. Now the FD PUBLISHER→ PUB_URL is applicable.We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.
4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 80: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/80.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.2. The FD ISBN→ TITLE, PUBLISHER is applicable since the
left-hand side of is completely contained in x .We get x = {ISBN, TITLE, PUBLISHER}.
3. Now the FD PUBLISHER→ PUB_URL is applicable.We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.
4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 81: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/81.jpg)
Implication of Functional Dependencies
Compute the cover {ISBN}+ for the following FDs:
ISBN→ TITLE, PUBLISHER
ISBN, NO→ AUTHOR
PUBLISHER→ PUB_URL
1. We start with x = {ISBN}.2. The FD ISBN→ TITLE, PUBLISHER is applicable since the
left-hand side of is completely contained in x .We get x = {ISBN, TITLE, PUBLISHER}.
3. Now the FD PUBLISHER→ PUB_URL is applicable.We get x = {ISBN, TITLE, PUBLISHER, PUB_URL}.
4. No further way to extend set x , the algorithm returns
{ISBN}+ = {ISBN, TITLE, PUBLISHER, PUB_URL}
5. We may now conclude, e.g., ISBN→ PUB_URL.
![Page 82: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/82.jpg)
How to Determine Keys
Given a set of FDs and the set of all attributes A of a relation R:
α ⊆ A is key of R ⇐⇒ α+ = A
That is α is a key if the cover α+ contains all attributes.
We can use FDs to determine all possible keys of R.
Remember: normally, we are interested in minimal keys only.
A key α is minimal if every A ∈ α is vital, that is
(α− {A})+ 6= A
![Page 83: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/83.jpg)
How to Determine Keys
Given a set of FDs and the set of all attributes A of a relation R:
α ⊆ A is key of R ⇐⇒ α+ = A
That is α is a key if the cover α+ contains all attributes.
We can use FDs to determine all possible keys of R.
Remember: normally, we are interested in minimal keys only.
A key α is minimal if every A ∈ α is vital, that is
(α− {A})+ 6= A
![Page 84: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/84.jpg)
How to Determine Keys
Given a set of FDs and the set of all attributes A of a relation R:
α ⊆ A is key of R ⇐⇒ α+ = A
That is α is a key if the cover α+ contains all attributes.
We can use FDs to determine all possible keys of R.
Remember: normally, we are interested in minimal keys only.
A key α is minimal if every A ∈ α is vital, that is
(α− {A})+ 6= A
![Page 85: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/85.jpg)
How to Determine Keys
Given a set of FDs and the set of all attributes A of a relation R:
α ⊆ A is key of R ⇐⇒ α+ = A
That is α is a key if the cover α+ contains all attributes.
We can use FDs to determine all possible keys of R.
Remember: normally, we are interested in minimal keys only.
A key α is minimal if every A ∈ α is vital, that is
(α− {A})+ 6= A
![Page 86: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/86.jpg)
How to Determine Keys
Finding a Minimal Key
Input: A (set of all attributes of R)α1 → β1, . . . , αn → βn (set of FDs F)
Output: α (a minimal key of R)
x = A;for all attributes A ∈ X do
if A ∈ {x − A}+F thenx = x − A; (remove A from x)
end ifend forreturn x ;
We might get different keys depending on the order in for all.
![Page 87: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/87.jpg)
How to Determine Keys
Finding a Minimal Key
Input: A (set of all attributes of R)α1 → β1, . . . , αn → βn (set of FDs F)
Output: α (a minimal key of R)
x = A;for all attributes A ∈ X do
if A ∈ {x − A}+F thenx = x − A; (remove A from x)
end ifend forreturn x ;
We might get different keys depending on the order in for all.
![Page 88: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/88.jpg)
How to Determine Keys
RESULTS
STUD_ID EX_NO POINTS MAX_POINTS100 1 9 10101 1 8 10101 2 11 12
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}
We determine a minimal key for the relation RESULTS:1. x = { STUD_ID, EX_NO, POINTS, MAX_POINTS }
2. We remove POINTS since { STUD_ID, EX_NO } ⊆ x :x = { STUD_ID, EX_NO, MAX_POINTS }
3. We remove MAX_POINTS since { EX_NO } ⊆ x :x = { STUD_ID, EX_NO }
4. Nothing else can be removed. We have a minimal key:{ STUD_ID, EX_NO }
![Page 89: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/89.jpg)
How to Determine Keys
RESULTS
STUD_ID EX_NO POINTS MAX_POINTS100 1 9 10101 1 8 10101 2 11 12
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}We determine a minimal key for the relation RESULTS:
1. x = { STUD_ID, EX_NO, POINTS, MAX_POINTS }
2. We remove POINTS since { STUD_ID, EX_NO } ⊆ x :x = { STUD_ID, EX_NO, MAX_POINTS }
3. We remove MAX_POINTS since { EX_NO } ⊆ x :x = { STUD_ID, EX_NO }
4. Nothing else can be removed. We have a minimal key:{ STUD_ID, EX_NO }
![Page 90: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/90.jpg)
How to Determine Keys
RESULTS
STUD_ID EX_NO POINTS MAX_POINTS100 1 9 10101 1 8 10101 2 11 12
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}We determine a minimal key for the relation RESULTS:
1. x = { STUD_ID, EX_NO, POINTS, MAX_POINTS }
2. We remove POINTS since { STUD_ID, EX_NO } ⊆ x :x = { STUD_ID, EX_NO, MAX_POINTS }
3. We remove MAX_POINTS since { EX_NO } ⊆ x :x = { STUD_ID, EX_NO }
4. Nothing else can be removed. We have a minimal key:{ STUD_ID, EX_NO }
![Page 91: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/91.jpg)
How to Determine Keys
RESULTS
STUD_ID EX_NO POINTS MAX_POINTS100 1 9 10101 1 8 10101 2 11 12
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}We determine a minimal key for the relation RESULTS:
1. x = { STUD_ID, EX_NO, POINTS, MAX_POINTS }
2. We remove POINTS since { STUD_ID, EX_NO } ⊆ x :x = { STUD_ID, EX_NO, MAX_POINTS }
3. We remove MAX_POINTS since { EX_NO } ⊆ x :x = { STUD_ID, EX_NO }
4. Nothing else can be removed. We have a minimal key:{ STUD_ID, EX_NO }
![Page 92: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/92.jpg)
How to Determine Keys
RESULTS
STUD_ID EX_NO POINTS MAX_POINTS100 1 9 10101 1 8 10101 2 11 12
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}We determine a minimal key for the relation RESULTS:
1. x = { STUD_ID, EX_NO, POINTS, MAX_POINTS }
2. We remove POINTS since { STUD_ID, EX_NO } ⊆ x :x = { STUD_ID, EX_NO, MAX_POINTS }
3. We remove MAX_POINTS since { EX_NO } ⊆ x :x = { STUD_ID, EX_NO }
4. Nothing else can be removed. We have a minimal key:{ STUD_ID, EX_NO }
![Page 93: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/93.jpg)
How to Determine Keys
RESULTS
STUD_ID EX_NO POINTS MAX_POINTS100 1 9 10101 1 8 10101 2 11 12
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}We determine a minimal key for the relation RESULTS:
1. x = { STUD_ID, EX_NO, POINTS, MAX_POINTS }
2. We remove POINTS since { STUD_ID, EX_NO } ⊆ x :x = { STUD_ID, EX_NO, MAX_POINTS }
3. We remove MAX_POINTS since { EX_NO } ⊆ x :x = { STUD_ID, EX_NO }
4. Nothing else can be removed. We have a minimal key:{ STUD_ID, EX_NO }
![Page 94: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/94.jpg)
How to Determine Keys
Finding all Minimal Keys
Input: A1,A2, . . . ,An (all attributes of R) and F (set of FDs)
Results = ∅;Candidates = {{A1}, {A2}, . . . , {An}};while Candidates 6= ∅ do
choose and remove a smallest κ ∈ Candidates;if κ+F = {A1,A2, . . . ,An} then
if κ contains no key in Results thenResults = Results ∪ {κ};
end ifelse
for all Ai 6∈ κ+F doκi = κ ∪ {Ai };Candidates = Candidates ∪ {κi };
end forend if
end whilereturn Results;
![Page 95: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/95.jpg)
How to Determine Keys
Finding all minimal keysFind all minimal keys the relation R
R
A B C D E
with the functional dependencies
A, D→ B, D
B, D→ C
A→ E
C, D, E→ A
using the algorithm on the previous slide.
![Page 96: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/96.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 97: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/97.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, and
the lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 98: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/98.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, and
the lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 99: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/99.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 100: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/100.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 101: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/101.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ?
NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 102: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/102.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? No
EX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 103: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/103.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ?
NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 104: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/104.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? No
STUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 105: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/105.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ?
YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 106: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/106.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? Yes
EX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 107: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/107.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ?
Yes
![Page 108: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/108.jpg)
Determinants
Determinants (Non-trivial, minimal FDs)The attribute set A1, . . . ,An is called a determinant for attributeset B1, . . . ,Bm if and only if
the FD A1, . . . ,An → B1, . . .Bm holds, andthe lhs is minimal, i.e., whenever any Ai is removed thenA1, . . . ,Ai−1,Ai+1,An → B1, . . .Bm does not hold, andthe lhs and rhs are distinct, i.e., {A1, . . . ,An} 6= {B1, . . . ,Bm}.
F =
{STUD_ID, EX_NO → POINTS
EX_NO → MAX_POINTS
}Are the following determinants?
POINTS, MAX_POINTS for POINTS, MAX_POINTS ? NoEX_NO for POINTS, MAX_POINTS ? NoSTUD_ID, EX_NO for POINTS, MAX_POINTS ? YesEX_NO, POINTS for POINTS, MAX_POINTS ? Yes
![Page 109: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/109.jpg)
Relational Normal Forms
Overview
1. Functional Dependencies (FDs)
2. Anomalies, FD-based Normal Forms
3. Multivalued Dependencies (MVDs) and 4NF
4. Normal Forms and ER Design
5. Denormalization
![Page 110: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/110.jpg)
Consequences of Bad DB Design
Usually a severe sign of bad DB design if a table contains anFD (encodes a partial function) that is not implied by a key.
INAME→ PHONE
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
This leads toredundant storage of certains facts(here, phone numbers)
insert, update, deletion anomalies
![Page 111: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/111.jpg)
Consequences of Bad DB Design
Usually a severe sign of bad DB design if a table contains anFD (encodes a partial function) that is not implied by a key.
INAME→ PHONE
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
This leads toredundant storage of certains facts(here, phone numbers)
insert, update, deletion anomalies
![Page 112: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/112.jpg)
Consequences of Bad DB Design
Usually a severe sign of bad DB design if a table contains anFD (encodes a partial function) that is not implied by a key.
INAME→ PHONE
COURSES
CRN TITLE INAME PHONE22268 Databases I Grust 711142232 Functional Programming Grust 711131822 Graph Theory Klotz 2418
This leads toredundant storage of certains facts(here, phone numbers)
insert, update, deletion anomalies
![Page 113: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/113.jpg)
Consequences of Bad DB Design
Redundant storage is bad for several reasons:
it wastes storage space
difficult to ensure integrity when updating the databaseall redundant copies need to be updatedwastes time, inefficient
need for additional constraints to guarantee integrityensure that the redundant copies indeed agreee.g. the constraint INAME→ PHONE
ProblemGeneral FDs are not supported by relational databases.
The solution is to transform FDs into key constraints.This is what DB normalization tries to do.
![Page 114: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/114.jpg)
Consequences of Bad DB Design
Redundant storage is bad for several reasons:
it wastes storage space
difficult to ensure integrity when updating the databaseall redundant copies need to be updatedwastes time, inefficient
need for additional constraints to guarantee integrityensure that the redundant copies indeed agreee.g. the constraint INAME→ PHONE
ProblemGeneral FDs are not supported by relational databases.
The solution is to transform FDs into key constraints.This is what DB normalization tries to do.
![Page 115: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/115.jpg)
Consequences of Bad DB Design
Redundant storage is bad for several reasons:
it wastes storage space
difficult to ensure integrity when updating the databaseall redundant copies need to be updatedwastes time, inefficient
need for additional constraints to guarantee integrityensure that the redundant copies indeed agreee.g. the constraint INAME→ PHONE
ProblemGeneral FDs are not supported by relational databases.
The solution is to transform FDs into key constraints.This is what DB normalization tries to do.
![Page 116: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/116.jpg)
Consequences of Bad DB Design
Redundant storage is bad for several reasons:
it wastes storage space
difficult to ensure integrity when updating the databaseall redundant copies need to be updatedwastes time, inefficient
need for additional constraints to guarantee integrityensure that the redundant copies indeed agreee.g. the constraint INAME→ PHONE
ProblemGeneral FDs are not supported by relational databases.
The solution is to transform FDs into key constraints.This is what DB normalization tries to do.
![Page 117: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/117.jpg)
Consequences of Bad DB Design
Redundant storage is bad for several reasons:
it wastes storage space
difficult to ensure integrity when updating the databaseall redundant copies need to be updatedwastes time, inefficient
need for additional constraints to guarantee integrityensure that the redundant copies indeed agreee.g. the constraint INAME→ PHONE
ProblemGeneral FDs are not supported by relational databases.
The solution is to transform FDs into key constraints.This is what DB normalization tries to do.
![Page 118: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/118.jpg)
Consequences of Bad DB Design
Redundant storage is bad for several reasons:
it wastes storage space
difficult to ensure integrity when updating the databaseall redundant copies need to be updatedwastes time, inefficient
need for additional constraints to guarantee integrityensure that the redundant copies indeed agreee.g. the constraint INAME→ PHONE
ProblemGeneral FDs are not supported by relational databases.
The solution is to transform FDs into key constraints.This is what DB normalization tries to do.
![Page 119: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/119.jpg)
Consequences of Bad DB Design
Update anomaliesWhen a single value needs to be changed (e.g., a phonenumber), multiple tuples must be updated. Thiscomplicates programs and updates takes longer.
Redundant copies potentially get out of sync and it isimpossible/hard to identify the correct information.
Insertion anomaliesThe phone number of a new instructor cannot be insertedinto the DB until it is known what course she/he will teach.Insertion anomalies arise when unrelated concepts arestored together in a single table.
Deletion anomaliesWhen the last course of an instructor is deleted, his/herphone number is lost.
![Page 120: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/120.jpg)
Consequences of Bad DB Design
Update anomaliesWhen a single value needs to be changed (e.g., a phonenumber), multiple tuples must be updated. Thiscomplicates programs and updates takes longer.Redundant copies potentially get out of sync and it isimpossible/hard to identify the correct information.
Insertion anomaliesThe phone number of a new instructor cannot be insertedinto the DB until it is known what course she/he will teach.Insertion anomalies arise when unrelated concepts arestored together in a single table.
Deletion anomaliesWhen the last course of an instructor is deleted, his/herphone number is lost.
![Page 121: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/121.jpg)
Consequences of Bad DB Design
Update anomaliesWhen a single value needs to be changed (e.g., a phonenumber), multiple tuples must be updated. Thiscomplicates programs and updates takes longer.Redundant copies potentially get out of sync and it isimpossible/hard to identify the correct information.
Insertion anomaliesThe phone number of a new instructor cannot be insertedinto the DB until it is known what course she/he will teach.
Insertion anomalies arise when unrelated concepts arestored together in a single table.
Deletion anomaliesWhen the last course of an instructor is deleted, his/herphone number is lost.
![Page 122: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/122.jpg)
Consequences of Bad DB Design
Update anomaliesWhen a single value needs to be changed (e.g., a phonenumber), multiple tuples must be updated. Thiscomplicates programs and updates takes longer.Redundant copies potentially get out of sync and it isimpossible/hard to identify the correct information.
Insertion anomaliesThe phone number of a new instructor cannot be insertedinto the DB until it is known what course she/he will teach.Insertion anomalies arise when unrelated concepts arestored together in a single table.
Deletion anomaliesWhen the last course of an instructor is deleted, his/herphone number is lost.
![Page 123: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/123.jpg)
Consequences of Bad DB Design
Update anomaliesWhen a single value needs to be changed (e.g., a phonenumber), multiple tuples must be updated. Thiscomplicates programs and updates takes longer.Redundant copies potentially get out of sync and it isimpossible/hard to identify the correct information.
Insertion anomaliesThe phone number of a new instructor cannot be insertedinto the DB until it is known what course she/he will teach.Insertion anomalies arise when unrelated concepts arestored together in a single table.
Deletion anomaliesWhen the last course of an instructor is deleted, his/herphone number is lost.
![Page 124: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/124.jpg)
Boyce-Codd Normal Form
A relation R is in Boyce-Codd Normal Form (BCNF) if andonly if all its FDs are implied by its key constraints.
That is, for any FD A1, . . . ,An → B1, . . . ,Bm of R we have:{B1, . . . ,Bm} ⊆ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R.
The relationCOURSES (CRN, TITLE, INAME, PHONE)
with the FDsCRN → TITLE, INAME, PHONE
INAME → PHONE
is not in BCNF because of the FD INAME→ PHONE:the FD is not trivial, andINAME is not a key
However, the relation COURSES (CRN, TITLE, INAME) withoutthe attribute PHONE is in BCNF.
![Page 125: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/125.jpg)
Boyce-Codd Normal Form
A relation R is in Boyce-Codd Normal Form (BCNF) if andonly if all its FDs are implied by its key constraints.That is, for any FD A1, . . . ,An → B1, . . . ,Bm of R we have:
{B1, . . . ,Bm} ⊆ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R.
The relationCOURSES (CRN, TITLE, INAME, PHONE)
with the FDsCRN → TITLE, INAME, PHONE
INAME → PHONE
is not in BCNF because of the FD INAME→ PHONE:the FD is not trivial, andINAME is not a key
However, the relation COURSES (CRN, TITLE, INAME) withoutthe attribute PHONE is in BCNF.
![Page 126: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/126.jpg)
Boyce-Codd Normal Form
A relation R is in Boyce-Codd Normal Form (BCNF) if andonly if all its FDs are implied by its key constraints.That is, for any FD A1, . . . ,An → B1, . . . ,Bm of R we have:
{B1, . . . ,Bm} ⊆ {A1, . . . ,An} (the FD is trivial), or
{A1, . . . ,An} contains a key of R.
The relationCOURSES (CRN, TITLE, INAME, PHONE)
with the FDsCRN → TITLE, INAME, PHONE
INAME → PHONE
is not in BCNF because of the FD INAME→ PHONE:the FD is not trivial, andINAME is not a key
However, the relation COURSES (CRN, TITLE, INAME) withoutthe attribute PHONE is in BCNF.
![Page 127: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/127.jpg)
Boyce-Codd Normal Form
A relation R is in Boyce-Codd Normal Form (BCNF) if andonly if all its FDs are implied by its key constraints.That is, for any FD A1, . . . ,An → B1, . . . ,Bm of R we have:
{B1, . . . ,Bm} ⊆ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R.
The relationCOURSES (CRN, TITLE, INAME, PHONE)
with the FDsCRN → TITLE, INAME, PHONE
INAME → PHONE
is not in BCNF because of the FD INAME→ PHONE:the FD is not trivial, andINAME is not a key
However, the relation COURSES (CRN, TITLE, INAME) withoutthe attribute PHONE is in BCNF.
![Page 128: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/128.jpg)
Boyce-Codd Normal Form
A relation R is in Boyce-Codd Normal Form (BCNF) if andonly if all its FDs are implied by its key constraints.That is, for any FD A1, . . . ,An → B1, . . . ,Bm of R we have:
{B1, . . . ,Bm} ⊆ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R.
The relationCOURSES (CRN, TITLE, INAME, PHONE)
with the FDsCRN → TITLE, INAME, PHONE
INAME → PHONE
is not in BCNF because of the FD INAME→ PHONE:the FD is not trivial, andINAME is not a key
However, the relation COURSES (CRN, TITLE, INAME) withoutthe attribute PHONE is in BCNF.
![Page 129: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/129.jpg)
Boyce-Codd Normal Form
A relation R is in Boyce-Codd Normal Form (BCNF) if andonly if all its FDs are implied by its key constraints.That is, for any FD A1, . . . ,An → B1, . . . ,Bm of R we have:
{B1, . . . ,Bm} ⊆ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R.
The relationCOURSES (CRN, TITLE, INAME, PHONE)
with the FDsCRN → TITLE, INAME, PHONE
INAME → PHONE
is not in BCNF because of the FD INAME→ PHONE:the FD is not trivial, andINAME is not a key
However, the relation COURSES (CRN, TITLE, INAME) withoutthe attribute PHONE is in BCNF.
![Page 130: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/130.jpg)
Boyce-Codd Normal Form
A relation R is in Boyce-Codd Normal Form (BCNF) if andonly if all its FDs are implied by its key constraints.That is, for any FD A1, . . . ,An → B1, . . . ,Bm of R we have:
{B1, . . . ,Bm} ⊆ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R.
The relationCOURSES (CRN, TITLE, INAME, PHONE)
with the FDsCRN → TITLE, INAME, PHONE
INAME → PHONE
is not in BCNF because of the FD INAME→ PHONE:the FD is not trivial, andINAME is not a key
However, the relation COURSES (CRN, TITLE, INAME) withoutthe attribute PHONE is in BCNF.
![Page 131: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/131.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 132: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/132.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 133: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/133.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 134: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/134.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are
{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 135: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/135.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 136: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/136.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 137: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/137.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?
both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 138: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/138.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 139: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/139.jpg)
Boyce-Codd Normal Form: Examples
Each course meets once per week in a dedicated room:
CLASS (CRN, TITLE, WEEKDAY, TIME, ROOM)
The relation thus satisfies the following FDs (plus implied ones):
CRN → TITLE, WEEKDAY, TIME, ROOMWEEKDAY, TIME, ROOM → CRN
The keys of CLASS are{ CRN }
{ WEEKDAY, TIME, ROOM }
Is the relation in BCNF?both FDs are implied by keys(their left-hand sides even coincide with the keys)
Thus CLASS is in BCNF.
![Page 140: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/140.jpg)
Boyce-Codd Normal Form: Examples
Consider the relation
PRODUCT (NO, NAME, PRICE)
and the following FDs:
NO → NAMENO → PRICE
PRICE, NAME → NAMENO, PRICE → NAME
Is this relation in BCNF?The two left FDs indicate that NO is a key.Both FDs are thus implied by a key.The third FD is trivial (and may be ignored).The left-hand side of the last FD contains a key.
Thus the relation PRODUCT is in BCNF.
![Page 141: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/141.jpg)
Boyce-Codd Normal Form: Examples
Consider the relation
PRODUCT (NO, NAME, PRICE)
and the following FDs:
NO → NAMENO → PRICE
PRICE, NAME → NAMENO, PRICE → NAME
Is this relation in BCNF?
The two left FDs indicate that NO is a key.Both FDs are thus implied by a key.The third FD is trivial (and may be ignored).The left-hand side of the last FD contains a key.
Thus the relation PRODUCT is in BCNF.
![Page 142: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/142.jpg)
Boyce-Codd Normal Form: Examples
Consider the relation
PRODUCT (NO, NAME, PRICE)
and the following FDs:
NO → NAMENO → PRICE
PRICE, NAME → NAMENO, PRICE → NAME
Is this relation in BCNF?The two left FDs indicate that NO is a key.Both FDs are thus implied by a key.
The third FD is trivial (and may be ignored).The left-hand side of the last FD contains a key.
Thus the relation PRODUCT is in BCNF.
![Page 143: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/143.jpg)
Boyce-Codd Normal Form: Examples
Consider the relation
PRODUCT (NO, NAME, PRICE)
and the following FDs:
NO → NAMENO → PRICE
PRICE, NAME → NAMENO, PRICE → NAME
Is this relation in BCNF?The two left FDs indicate that NO is a key.Both FDs are thus implied by a key.The third FD is trivial (and may be ignored).
The left-hand side of the last FD contains a key.Thus the relation PRODUCT is in BCNF.
![Page 144: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/144.jpg)
Boyce-Codd Normal Form: Examples
Consider the relation
PRODUCT (NO, NAME, PRICE)
and the following FDs:
NO → NAMENO → PRICE
PRICE, NAME → NAMENO, PRICE → NAME
Is this relation in BCNF?The two left FDs indicate that NO is a key.Both FDs are thus implied by a key.The third FD is trivial (and may be ignored).The left-hand side of the last FD contains a key.
Thus the relation PRODUCT is in BCNF.
![Page 145: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/145.jpg)
Boyce-Codd Normal Form: Examples
Consider the relation
PRODUCT (NO, NAME, PRICE)
and the following FDs:
NO → NAMENO → PRICE
PRICE, NAME → NAMENO, PRICE → NAME
Is this relation in BCNF?The two left FDs indicate that NO is a key.Both FDs are thus implied by a key.The third FD is trivial (and may be ignored).The left-hand side of the last FD contains a key.
Thus the relation PRODUCT is in BCNF.
![Page 146: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/146.jpg)
Boyce-Codd Normal Form
Alternative characterisation of Boyce-Codd Normal Form:
BCNF⇐⇒ every determinant is key
Advantages of Boyce-Codd Normal Form
If a relation R is in BCNF, then. . .Ensuring its key constraints automatically satisfies all FDs.Hence, no additional constraints are needed!
The anomalies (udpate/insertion/deletion) do not occur.
![Page 147: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/147.jpg)
Boyce-Codd Normal Form
Alternative characterisation of Boyce-Codd Normal Form:
BCNF⇐⇒ every determinant is key
Advantages of Boyce-Codd Normal Form
If a relation R is in BCNF, then. . .
Ensuring its key constraints automatically satisfies all FDs.Hence, no additional constraints are needed!
The anomalies (udpate/insertion/deletion) do not occur.
![Page 148: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/148.jpg)
Boyce-Codd Normal Form
Alternative characterisation of Boyce-Codd Normal Form:
BCNF⇐⇒ every determinant is key
Advantages of Boyce-Codd Normal Form
If a relation R is in BCNF, then. . .Ensuring its key constraints automatically satisfies all FDs.
Hence, no additional constraints are needed!
The anomalies (udpate/insertion/deletion) do not occur.
![Page 149: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/149.jpg)
Boyce-Codd Normal Form
Alternative characterisation of Boyce-Codd Normal Form:
BCNF⇐⇒ every determinant is key
Advantages of Boyce-Codd Normal Form
If a relation R is in BCNF, then. . .Ensuring its key constraints automatically satisfies all FDs.Hence, no additional constraints are needed!
The anomalies (udpate/insertion/deletion) do not occur.
![Page 150: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/150.jpg)
Boyce-Codd Normal Form
Alternative characterisation of Boyce-Codd Normal Form:
BCNF⇐⇒ every determinant is key
Advantages of Boyce-Codd Normal Form
If a relation R is in BCNF, then. . .Ensuring its key constraints automatically satisfies all FDs.Hence, no additional constraints are needed!
The anomalies (udpate/insertion/deletion) do not occur.
![Page 151: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/151.jpg)
Boyce-Codd Normal Form: Quiz
BCNF Quiz
1. Is the relation
RESULTS (STUD_ID, EX_NO, POINTS, MAX_POINTS)
with the following FDs in BCNF?
STUD_ID, EX_NO → POINTSEX_NO → MAX_POINTS
2. Is the relation
INVOICE (INV_NO, DATE, AMOUNT, CUST_NO, CUST_NAME)
with the following FDs in BCNF?
INV_NO → DATE, AMOUNT, CUST_NOINV_NO, DATE → CUST_NAME
CUST_NO → CUST_NAMEDATE, AMOUNT → DATE
![Page 152: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/152.jpg)
Boyce-Codd Normal Form: Quiz
BCNF Quiz
1. Is the relation
RESULTS (STUD_ID, EX_NO, POINTS, MAX_POINTS)
with the following FDs in BCNF?
STUD_ID, EX_NO → POINTSEX_NO → MAX_POINTS
2. Is the relation
INVOICE (INV_NO, DATE, AMOUNT, CUST_NO, CUST_NAME)
with the following FDs in BCNF?
INV_NO → DATE, AMOUNT, CUST_NOINV_NO, DATE → CUST_NAME
CUST_NO → CUST_NAMEDATE, AMOUNT → DATE
![Page 153: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/153.jpg)
Third Normal Form
A key attribute is an attribute that appears in a minimal key.Minimality is important, otherwise all attributes are key attributes.
Assume that FDs with multiple attributes on rhs have been expanded.That is, every FD has a single attribute on the right-hand side.
Third Normal Form (3NF)A relation R is in Third Normal Form (3NF) if and only if everyFD A1, . . . ,An → B satisfies at least one of the conditions:
B ∈ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R, orB is a key attribute of R.
The only difference with BCNF is the last condition.
Third Normal Form (3NF) is slightly weaker than BCNF:If a relation is in BCNF, it is automatically in 3NF.
![Page 154: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/154.jpg)
Third Normal Form
A key attribute is an attribute that appears in a minimal key.Minimality is important, otherwise all attributes are key attributes.
Assume that FDs with multiple attributes on rhs have been expanded.That is, every FD has a single attribute on the right-hand side.
Third Normal Form (3NF)A relation R is in Third Normal Form (3NF) if and only if everyFD A1, . . . ,An → B satisfies at least one of the conditions:
B ∈ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R, orB is a key attribute of R.
The only difference with BCNF is the last condition.
Third Normal Form (3NF) is slightly weaker than BCNF:If a relation is in BCNF, it is automatically in 3NF.
![Page 155: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/155.jpg)
Third Normal Form
A key attribute is an attribute that appears in a minimal key.Minimality is important, otherwise all attributes are key attributes.
Assume that FDs with multiple attributes on rhs have been expanded.That is, every FD has a single attribute on the right-hand side.
Third Normal Form (3NF)A relation R is in Third Normal Form (3NF) if and only if everyFD A1, . . . ,An → B satisfies at least one of the conditions:
B ∈ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R, orB is a key attribute of R.
The only difference with BCNF is the last condition.
Third Normal Form (3NF) is slightly weaker than BCNF:If a relation is in BCNF, it is automatically in 3NF.
![Page 156: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/156.jpg)
Third Normal Form
A key attribute is an attribute that appears in a minimal key.Minimality is important, otherwise all attributes are key attributes.
Assume that FDs with multiple attributes on rhs have been expanded.That is, every FD has a single attribute on the right-hand side.
Third Normal Form (3NF)A relation R is in Third Normal Form (3NF) if and only if everyFD A1, . . . ,An → B satisfies at least one of the conditions:
B ∈ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R, or
B is a key attribute of R.
The only difference with BCNF is the last condition.
Third Normal Form (3NF) is slightly weaker than BCNF:If a relation is in BCNF, it is automatically in 3NF.
![Page 157: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/157.jpg)
Third Normal Form
A key attribute is an attribute that appears in a minimal key.Minimality is important, otherwise all attributes are key attributes.
Assume that FDs with multiple attributes on rhs have been expanded.That is, every FD has a single attribute on the right-hand side.
Third Normal Form (3NF)A relation R is in Third Normal Form (3NF) if and only if everyFD A1, . . . ,An → B satisfies at least one of the conditions:
B ∈ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R, orB is a key attribute of R.
The only difference with BCNF is the last condition.
Third Normal Form (3NF) is slightly weaker than BCNF:If a relation is in BCNF, it is automatically in 3NF.
![Page 158: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/158.jpg)
Third Normal Form
A key attribute is an attribute that appears in a minimal key.Minimality is important, otherwise all attributes are key attributes.
Assume that FDs with multiple attributes on rhs have been expanded.That is, every FD has a single attribute on the right-hand side.
Third Normal Form (3NF)A relation R is in Third Normal Form (3NF) if and only if everyFD A1, . . . ,An → B satisfies at least one of the conditions:
B ∈ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R, orB is a key attribute of R.
The only difference with BCNF is the last condition.
Third Normal Form (3NF) is slightly weaker than BCNF:If a relation is in BCNF, it is automatically in 3NF.
![Page 159: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/159.jpg)
Third Normal Form
A key attribute is an attribute that appears in a minimal key.Minimality is important, otherwise all attributes are key attributes.
Assume that FDs with multiple attributes on rhs have been expanded.That is, every FD has a single attribute on the right-hand side.
Third Normal Form (3NF)A relation R is in Third Normal Form (3NF) if and only if everyFD A1, . . . ,An → B satisfies at least one of the conditions:
B ∈ {A1, . . . ,An} (the FD is trivial), or{A1, . . . ,An} contains a key of R, orB is a key attribute of R.
The only difference with BCNF is the last condition.
Third Normal Form (3NF) is slightly weaker than BCNF:If a relation is in BCNF, it is automatically in 3NF.
![Page 160: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/160.jpg)
Third Normal Form
In short, we can say:
BCNF ⇐⇒ for every non-trivial FD:the left-hand side contains a key
3NF ⇐⇒ for every non-trivial FD:the left-hand side contains a key, orthe right-hand side is an attribute of a minimal key
Alternative characterisation of 3NF:
3NF ⇐⇒ every determinant of a non-key attribute is a key
![Page 161: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/161.jpg)
Third Normal Form
In short, we can say:
BCNF ⇐⇒ for every non-trivial FD:the left-hand side contains a key
3NF ⇐⇒ for every non-trivial FD:the left-hand side contains a key, orthe right-hand side is an attribute of a minimal key
Alternative characterisation of 3NF:
3NF ⇐⇒ every determinant of a non-key attribute is a key
![Page 162: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/162.jpg)
Third Normal Form
In short, we can say:
BCNF ⇐⇒ for every non-trivial FD:the left-hand side contains a key
3NF ⇐⇒ for every non-trivial FD:the left-hand side contains a key, orthe right-hand side is an attribute of a minimal key
Alternative characterisation of 3NF:
3NF ⇐⇒ every determinant of a non-key attribute is a key
![Page 163: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/163.jpg)
Third Normal Form Quiz
3NF vs BCNFBOOKINGS
COURT START_TIME END_TIME RATE1 9:30 11:00 SAVER2 9:30 12:00 PREMIMUM-A1 12:00 14:00 STANDARD
The table contains bookings for one day at a tennis club:there are courts 1 (hard court) and 2 (grass court)the rates are
SAVER for member bookings of court 1STANDARD for non-member bookings of court 1PREMIMUM-A for member bookings of court 2PREMIMUM-B for non-member bookings of court 2
Quiz:Find a representative FDs set.Is the table in BCNF? Is the table in 3NF?
![Page 164: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/164.jpg)
Third Normal Form Quiz
3NF vs BCNFBOOKINGS
COURT START_TIME END_TIME RATE1 9:30 11:00 SAVER2 9:30 12:00 PREMIMUM-A1 12:00 14:00 STANDARD
The table contains bookings for one day at a tennis club:there are courts 1 (hard court) and 2 (grass court)the rates are
SAVER for member bookings of court 1STANDARD for non-member bookings of court 1PREMIMUM-A for member bookings of court 2PREMIMUM-B for non-member bookings of court 2
Quiz:Find a representative FDs set.Is the table in BCNF? Is the table in 3NF?
![Page 165: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/165.jpg)
Splitting Relations
If a table R is not in BCNF, we can split it into two tables.the violating FD determines how to split
Table DecompositionIf the FD A1, . . . ,An → B1, . . .Bm violates BCNF:
create a new relation S(A1, . . . ,An,B1, . . . ,Bm) andremove B1, . . . ,Bm from the original relation R.
Splitting “along an FD”The FD INAME→ PHONE is the reason why table
COURSES (CRN, TITLE, INAME, PHONE)
violates BCNF because of INAME→ PHONE. We split into:
INSTRUCTORS (CRN, TITLE, INAME)PHONEBOOK (INAME, PHONE)
![Page 166: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/166.jpg)
Splitting Relations
If a table R is not in BCNF, we can split it into two tables.the violating FD determines how to split
Table DecompositionIf the FD A1, . . . ,An → B1, . . .Bm violates BCNF:
create a new relation S(A1, . . . ,An,B1, . . . ,Bm) andremove B1, . . . ,Bm from the original relation R.
Splitting “along an FD”The FD INAME→ PHONE is the reason why table
COURSES (CRN, TITLE, INAME, PHONE)
violates BCNF because of INAME→ PHONE. We split into:
INSTRUCTORS (CRN, TITLE, INAME)PHONEBOOK (INAME, PHONE)
![Page 167: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/167.jpg)
Splitting Relations
If a table R is not in BCNF, we can split it into two tables.the violating FD determines how to split
Table DecompositionIf the FD A1, . . . ,An → B1, . . .Bm violates BCNF:
create a new relation S(A1, . . . ,An,B1, . . . ,Bm) andremove B1, . . . ,Bm from the original relation R.
Splitting “along an FD”The FD INAME→ PHONE is the reason why table
COURSES (CRN, TITLE, INAME, PHONE)
violates BCNF because of INAME→ PHONE.
We split into:
INSTRUCTORS (CRN, TITLE, INAME)PHONEBOOK (INAME, PHONE)
![Page 168: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/168.jpg)
Splitting Relations
If a table R is not in BCNF, we can split it into two tables.the violating FD determines how to split
Table DecompositionIf the FD A1, . . . ,An → B1, . . .Bm violates BCNF:
create a new relation S(A1, . . . ,An,B1, . . . ,Bm) andremove B1, . . . ,Bm from the original relation R.
Splitting “along an FD”The FD INAME→ PHONE is the reason why table
COURSES (CRN, TITLE, INAME, PHONE)
violates BCNF because of INAME→ PHONE. We split into:
INSTRUCTORS (CRN, TITLE, INAME)PHONEBOOK (INAME, PHONE)
![Page 169: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/169.jpg)
Splitting Relations
If a table R is not in BCNF, we can split it into two tables.the violating FD determines how to split
Table DecompositionIf the FD A1, . . . ,An → B1, . . .Bm violates BCNF:
create a new relation S(A1, . . . ,An,B1, . . . ,Bm) andremove B1, . . . ,Bm from the original relation R.
Splitting “along an FD”The FD INAME→ PHONE is the reason why table
COURSES (CRN, TITLE, INAME, PHONE)
violates BCNF because of INAME→ PHONE. We split into:
INSTRUCTORS (CRN, TITLE, INAME)PHONEBOOK (INAME, PHONE)
![Page 170: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/170.jpg)
Splitting Relations
If a table R is not in BCNF, we can split it into two tables.the violating FD determines how to split
Table DecompositionIf the FD A1, . . . ,An → B1, . . .Bm violates BCNF:
create a new relation S(A1, . . . ,An,B1, . . . ,Bm) andremove B1, . . . ,Bm from the original relation R.
Splitting “along an FD”The FD INAME→ PHONE is the reason why table
COURSES (CRN, TITLE, INAME, PHONE)
violates BCNF because of INAME→ PHONE. We split into:
INSTRUCTORS (CRN, TITLE, INAME)PHONEBOOK (INAME, PHONE)
![Page 171: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/171.jpg)
Splitting Relations
It is important that this splitting transformation is lossless, i.e.,that the original relation can be reconstructed by a join.
Reconstruction after splitRecall that we have split
COURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We can reconstruct the original table as follows:CREATE VIEW COURSES (CRN, TITLE, INAME, PHONE)
ASSELECT I.CRN, I.TITLE, I.INAME, P.PHONEFROM INTSTRUCTORS I, PHONEBOOK P
WHERE I.INAME = P.INAME
![Page 172: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/172.jpg)
Splitting Relations
It is important that this splitting transformation is lossless, i.e.,that the original relation can be reconstructed by a join.
Reconstruction after splitRecall that we have split
COURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We can reconstruct the original table as follows:CREATE VIEW COURSES (CRN, TITLE, INAME, PHONE)
ASSELECT I.CRN, I.TITLE, I.INAME, P.PHONEFROM INTSTRUCTORS I, PHONEBOOK P
WHERE I.INAME = P.INAME
![Page 173: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/173.jpg)
Splitting Relations
It is important that this splitting transformation is lossless, i.e.,that the original relation can be reconstructed by a join.
Reconstruction after splitRecall that we have split
COURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We can reconstruct the original table as follows:CREATE VIEW COURSES (CRN, TITLE, INAME, PHONE)
ASSELECT I.CRN, I.TITLE, I.INAME, P.PHONEFROM INTSTRUCTORS I, PHONEBOOK P
WHERE I.INAME = P.INAME
![Page 174: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/174.jpg)
Splitting Relations
When is a split lossless?
Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The join 1 connects tuples depending on the attribute (values) in theintersection. If these values uniquely identify tuples in the other relation wedo not lose information.
“Lossy” decompositionOriginal table Decomposition “Reconstruction”(key A,B,C) R1 R2 R1 1 R2
A B Ca11 b11 c11a11 b11 c12a11 b12 c11
A Ba11 b11a11 b12
A Ca11 c11a11 c12
A B Ca11 b11 c11a11 b11 c12a11 b12 c11a11 b12 c12
![Page 175: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/175.jpg)
Splitting Relations
When is a split lossless?
Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The join 1 connects tuples depending on the attribute (values) in theintersection. If these values uniquely identify tuples in the other relation wedo not lose information.
“Lossy” decompositionOriginal table Decomposition “Reconstruction”(key A,B,C) R1 R2 R1 1 R2
A B Ca11 b11 c11a11 b11 c12a11 b12 c11
A Ba11 b11a11 b12
A Ca11 c11a11 c12
A B Ca11 b11 c11a11 b11 c12a11 b12 c11a11 b12 c12
![Page 176: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/176.jpg)
Splitting Relations
When is a split lossless?
Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The join 1 connects tuples depending on the attribute (values) in theintersection. If these values uniquely identify tuples in the other relation wedo not lose information.
“Lossy” decompositionOriginal table Decomposition “Reconstruction”(key A,B,C) R1 R2 R1 1 R2
A B Ca11 b11 c11a11 b11 c12a11 b12 c11
A Ba11 b11a11 b12
A Ca11 c11a11 c12
A B Ca11 b11 c11a11 b11 c12a11 b12 c11a11 b12 c12
![Page 177: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/177.jpg)
Splitting Relations
When is a split lossless?
Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The join 1 connects tuples depending on the attribute (values) in theintersection. If these values uniquely identify tuples in the other relation wedo not lose information.
“Lossy” decompositionOriginal table Decomposition “Reconstruction”(key A,B,C) R1 R2 R1 1 R2
A B Ca11 b11 c11a11 b11 c12a11 b12 c11
A Ba11 b11a11 b12
A Ca11 c11a11 c12
A B Ca11 b11 c11a11 b11 c12a11 b12 c11a11 b12 c12
![Page 178: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/178.jpg)
Splitting Relations
Lossless split condition satisfiedRecall that we have split
COURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
The lossless split condition is satisfied since{CRN, TITLE, INAME} ∩ {INAME, PHONE} = {INAME}
and INAME is a key of the table PHONEBOOK.
All splits initiated by the table decomposition method fortransforming relations into BCNF satisfy the condition of thedecomposition theorem.
It is always possible to transform a relation into BCNF bylossless splitting (if necessary, split repeatedly).
![Page 179: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/179.jpg)
Splitting Relations
Lossless split condition satisfiedRecall that we have split
COURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
The lossless split condition is satisfied since{CRN, TITLE, INAME} ∩ {INAME, PHONE} = {INAME}
and INAME is a key of the table PHONEBOOK.
All splits initiated by the table decomposition method fortransforming relations into BCNF satisfy the condition of thedecomposition theorem.
It is always possible to transform a relation into BCNF bylossless splitting (if necessary, split repeatedly).
![Page 180: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/180.jpg)
Splitting Relations
Lossless split condition satisfiedRecall that we have split
COURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
The lossless split condition is satisfied since{CRN, TITLE, INAME} ∩ {INAME, PHONE} = {INAME}
and INAME is a key of the table PHONEBOOK.
All splits initiated by the table decomposition method fortransforming relations into BCNF satisfy the condition of thedecomposition theorem.
It is always possible to transform a relation into BCNF bylossless splitting (if necessary, split repeatedly).
![Page 181: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/181.jpg)
Splitting Relations
Lossless split condition satisfiedRecall that we have split
COURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
The lossless split condition is satisfied since{CRN, TITLE, INAME} ∩ {INAME, PHONE} = {INAME}
and INAME is a key of the table PHONEBOOK.
All splits initiated by the table decomposition method fortransforming relations into BCNF satisfy the condition of thedecomposition theorem.
It is always possible to transform a relation into BCNF bylossless splitting (if necessary, split repeatedly).
![Page 182: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/182.jpg)
Splitting Relations
Not every lossless split is reasonable!
STUDENTS
SSN FIRST_NAME LAST_NAME111-22-3333 John Smith123-45-6789 Maria Brown
Splitting STUDENTS into
STUD_FIRST (SSN, FIRST_NAME)STUD_LAST (SSN, LAST_NAME)
is lossless, butthe split is not necessary to enforce a normal form,only requires costly joins in subsequent queries
![Page 183: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/183.jpg)
Splitting Relations
Not every lossless split is reasonable!
STUDENTS
SSN FIRST_NAME LAST_NAME111-22-3333 John Smith123-45-6789 Maria Brown
Splitting STUDENTS into
STUD_FIRST (SSN, FIRST_NAME)STUD_LAST (SSN, LAST_NAME)
is lossless, but
the split is not necessary to enforce a normal form,only requires costly joins in subsequent queries
![Page 184: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/184.jpg)
Splitting Relations
Not every lossless split is reasonable!
STUDENTS
SSN FIRST_NAME LAST_NAME111-22-3333 John Smith123-45-6789 Maria Brown
Splitting STUDENTS into
STUD_FIRST (SSN, FIRST_NAME)STUD_LAST (SSN, LAST_NAME)
is lossless, butthe split is not necessary to enforce a normal form,
only requires costly joins in subsequent queries
![Page 185: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/185.jpg)
Splitting Relations
Not every lossless split is reasonable!
STUDENTS
SSN FIRST_NAME LAST_NAME111-22-3333 John Smith123-45-6789 Maria Brown
Splitting STUDENTS into
STUD_FIRST (SSN, FIRST_NAME)STUD_LAST (SSN, LAST_NAME)
is lossless, butthe split is not necessary to enforce a normal form,only requires costly joins in subsequent queries
![Page 186: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/186.jpg)
Splitting Relations
Lossless split guarantees that the resulting schema (aftersplitting) can represent all DB states that were possible before.
we can translate states from the old into the new schemawe may “simulate” the old schema via views
Lossless splits can lead to more general schemas!the new schema allows states which do not correspond tothe state in the old schema
Recall that we have splitCOURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We may now store instructors and phone numbers without anyaffiliation to courses.
![Page 187: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/187.jpg)
Splitting Relations
Lossless split guarantees that the resulting schema (aftersplitting) can represent all DB states that were possible before.
we can translate states from the old into the new schema
we may “simulate” the old schema via views
Lossless splits can lead to more general schemas!the new schema allows states which do not correspond tothe state in the old schema
Recall that we have splitCOURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We may now store instructors and phone numbers without anyaffiliation to courses.
![Page 188: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/188.jpg)
Splitting Relations
Lossless split guarantees that the resulting schema (aftersplitting) can represent all DB states that were possible before.
we can translate states from the old into the new schemawe may “simulate” the old schema via views
Lossless splits can lead to more general schemas!the new schema allows states which do not correspond tothe state in the old schema
Recall that we have splitCOURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We may now store instructors and phone numbers without anyaffiliation to courses.
![Page 189: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/189.jpg)
Splitting Relations
Lossless split guarantees that the resulting schema (aftersplitting) can represent all DB states that were possible before.
we can translate states from the old into the new schemawe may “simulate” the old schema via views
Lossless splits can lead to more general schemas!the new schema allows states which do not correspond tothe state in the old schema
Recall that we have splitCOURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We may now store instructors and phone numbers without anyaffiliation to courses.
![Page 190: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/190.jpg)
Splitting Relations
Lossless split guarantees that the resulting schema (aftersplitting) can represent all DB states that were possible before.
we can translate states from the old into the new schemawe may “simulate” the old schema via views
Lossless splits can lead to more general schemas!the new schema allows states which do not correspond tothe state in the old schema
Recall that we have splitCOURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We may now store instructors and phone numbers without anyaffiliation to courses.
![Page 191: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/191.jpg)
Splitting Relations
Lossless split guarantees that the resulting schema (aftersplitting) can represent all DB states that were possible before.
we can translate states from the old into the new schemawe may “simulate” the old schema via views
Lossless splits can lead to more general schemas!the new schema allows states which do not correspond tothe state in the old schema
Recall that we have splitCOURSES (CRN, TITLE, INAME, PHONE)
into tablesINSTRUCTORS (CRN, TITLE, INAME)
PHONEBOOK (INAME, PHONE)
We may now store instructors and phone numbers without anyaffiliation to courses.
![Page 192: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/192.jpg)
Splitting Relations: Computable Columns
Although computable columns lead to violations of BCNF,splitting the relation is not the right solution.
E.g. AGE which is derivable from BIRTDATE.
As a consequence we have a functional dependency:
BIRTDATE→ AGE
A split would yield a relation:
R(BIRTHDAY, AGE)
which would try to materialise the computable function.
The correct solution is to eliminate AGE from the table and todefine a view which contains all columns plus the computedcolumn AGE (invoking a SQL stored procedure).
![Page 193: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/193.jpg)
Splitting Relations: Computable Columns
Although computable columns lead to violations of BCNF,splitting the relation is not the right solution.
E.g. AGE which is derivable from BIRTDATE.
As a consequence we have a functional dependency:
BIRTDATE→ AGE
A split would yield a relation:
R(BIRTHDAY, AGE)
which would try to materialise the computable function.
The correct solution is to eliminate AGE from the table and todefine a view which contains all columns plus the computedcolumn AGE (invoking a SQL stored procedure).
![Page 194: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/194.jpg)
Splitting Relations: Computable Columns
Although computable columns lead to violations of BCNF,splitting the relation is not the right solution.
E.g. AGE which is derivable from BIRTDATE.
As a consequence we have a functional dependency:
BIRTDATE→ AGE
A split would yield a relation:
R(BIRTHDAY, AGE)
which would try to materialise the computable function.
The correct solution is to eliminate AGE from the table and todefine a view which contains all columns plus the computedcolumn AGE (invoking a SQL stored procedure).
![Page 195: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/195.jpg)
Splitting Relations: Computable Columns
Although computable columns lead to violations of BCNF,splitting the relation is not the right solution.
E.g. AGE which is derivable from BIRTDATE.
As a consequence we have a functional dependency:
BIRTDATE→ AGE
A split would yield a relation:
R(BIRTHDAY, AGE)
which would try to materialise the computable function.
The correct solution is to eliminate AGE from the table and todefine a view which contains all columns plus the computedcolumn AGE (invoking a SQL stored procedure).
![Page 196: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/196.jpg)
Splitting Relations: Computable Columns
Although computable columns lead to violations of BCNF,splitting the relation is not the right solution.
E.g. AGE which is derivable from BIRTDATE.
As a consequence we have a functional dependency:
BIRTDATE→ AGE
A split would yield a relation:
R(BIRTHDAY, AGE)
which would try to materialise the computable function.
The correct solution is to eliminate AGE from the table and todefine a view which contains all columns plus the computedcolumn AGE (invoking a SQL stored procedure).
![Page 197: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/197.jpg)
Preservation of Functional Dependencies
Besides losslessness, a property which a good decompositionof a relation should guarantee is the preservation of FDs:
The problem is that an FD can refer only to attributes of asingle relation.When you split a relation into two, there might be FDs thatcan no longer be expressed (these FDs are not preserved).
FD gets lost during decompositionADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
The second FD violates BCNF and would lead to the split:ADDRESSES1 (STREET_ADDR, CITY, ZIP) andADDRESSES2 (ZIP, STATE).
But now the first FD can no longer be expressed.�
![Page 198: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/198.jpg)
Preservation of Functional Dependencies
Besides losslessness, a property which a good decompositionof a relation should guarantee is the preservation of FDs:
The problem is that an FD can refer only to attributes of asingle relation.
When you split a relation into two, there might be FDs thatcan no longer be expressed (these FDs are not preserved).
FD gets lost during decompositionADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
The second FD violates BCNF and would lead to the split:ADDRESSES1 (STREET_ADDR, CITY, ZIP) andADDRESSES2 (ZIP, STATE).
But now the first FD can no longer be expressed.�
![Page 199: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/199.jpg)
Preservation of Functional Dependencies
Besides losslessness, a property which a good decompositionof a relation should guarantee is the preservation of FDs:
The problem is that an FD can refer only to attributes of asingle relation.When you split a relation into two, there might be FDs thatcan no longer be expressed (these FDs are not preserved).
FD gets lost during decompositionADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
The second FD violates BCNF and would lead to the split:ADDRESSES1 (STREET_ADDR, CITY, ZIP) andADDRESSES2 (ZIP, STATE).
But now the first FD can no longer be expressed.�
![Page 200: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/200.jpg)
Preservation of Functional Dependencies
Besides losslessness, a property which a good decompositionof a relation should guarantee is the preservation of FDs:
The problem is that an FD can refer only to attributes of asingle relation.When you split a relation into two, there might be FDs thatcan no longer be expressed (these FDs are not preserved).
FD gets lost during decompositionADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
The second FD violates BCNF and would lead to the split:ADDRESSES1 (STREET_ADDR, CITY, ZIP) andADDRESSES2 (ZIP, STATE).
But now the first FD can no longer be expressed.�
![Page 201: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/201.jpg)
Preservation of Functional Dependencies
Besides losslessness, a property which a good decompositionof a relation should guarantee is the preservation of FDs:
The problem is that an FD can refer only to attributes of asingle relation.When you split a relation into two, there might be FDs thatcan no longer be expressed (these FDs are not preserved).
FD gets lost during decompositionADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
The second FD violates BCNF and would lead to the split:ADDRESSES1 (STREET_ADDR, CITY, ZIP) andADDRESSES2 (ZIP, STATE).
But now the first FD can no longer be expressed.�
![Page 202: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/202.jpg)
Preservation of Functional Dependencies
Besides losslessness, a property which a good decompositionof a relation should guarantee is the preservation of FDs:
The problem is that an FD can refer only to attributes of asingle relation.When you split a relation into two, there might be FDs thatcan no longer be expressed (these FDs are not preserved).
FD gets lost during decompositionADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
The second FD violates BCNF and would lead to the split:ADDRESSES1 (STREET_ADDR, CITY, ZIP) andADDRESSES2 (ZIP, STATE).
But now the first FD can no longer be expressed.�
![Page 203: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/203.jpg)
Preservation of Functional Dependencies
ADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
Is the table in 3NF? YesMost designers would not split the table since it is in 3NF.Pro split: if there are many addresses with the same ZIPcode, there will be significant redundancy.Contra split: queries will involve more joins.
Whether or not to split depends on the intended application:A table of ZIP codes might be of interest on its own.E.g. it this were a database for a mailing company.
![Page 204: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/204.jpg)
Preservation of Functional Dependencies
ADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
Is the table in 3NF?
YesMost designers would not split the table since it is in 3NF.Pro split: if there are many addresses with the same ZIPcode, there will be significant redundancy.Contra split: queries will involve more joins.
Whether or not to split depends on the intended application:A table of ZIP codes might be of interest on its own.E.g. it this were a database for a mailing company.
![Page 205: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/205.jpg)
Preservation of Functional Dependencies
ADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
Is the table in 3NF? Yes
Most designers would not split the table since it is in 3NF.Pro split: if there are many addresses with the same ZIPcode, there will be significant redundancy.Contra split: queries will involve more joins.
Whether or not to split depends on the intended application:A table of ZIP codes might be of interest on its own.E.g. it this were a database for a mailing company.
![Page 206: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/206.jpg)
Preservation of Functional Dependencies
ADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
Is the table in 3NF? YesMost designers would not split the table since it is in 3NF.
Pro split: if there are many addresses with the same ZIPcode, there will be significant redundancy.Contra split: queries will involve more joins.
Whether or not to split depends on the intended application:A table of ZIP codes might be of interest on its own.E.g. it this were a database for a mailing company.
![Page 207: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/207.jpg)
Preservation of Functional Dependencies
ADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
Is the table in 3NF? YesMost designers would not split the table since it is in 3NF.Pro split: if there are many addresses with the same ZIPcode, there will be significant redundancy.
Contra split: queries will involve more joins.
Whether or not to split depends on the intended application:A table of ZIP codes might be of interest on its own.E.g. it this were a database for a mailing company.
![Page 208: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/208.jpg)
Preservation of Functional Dependencies
ADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
Is the table in 3NF? YesMost designers would not split the table since it is in 3NF.Pro split: if there are many addresses with the same ZIPcode, there will be significant redundancy.Contra split: queries will involve more joins.
Whether or not to split depends on the intended application:A table of ZIP codes might be of interest on its own.E.g. it this were a database for a mailing company.
![Page 209: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/209.jpg)
Preservation of Functional Dependencies
ADRESSES (STREET_ADDR, CITY, STATE, ZIP)
with functional dependenciesSTREE_ADDR, CITY, STATE → ZIP
ZIP → STATE
Is the table in 3NF? YesMost designers would not split the table since it is in 3NF.Pro split: if there are many addresses with the same ZIPcode, there will be significant redundancy.Contra split: queries will involve more joins.
Whether or not to split depends on the intended application:A table of ZIP codes might be of interest on its own.E.g. it this were a database for a mailing company.
![Page 210: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/210.jpg)
3NF Synthesis Algorithm
The following algorithm, the synthesis algorithm, produces alossless decomposition of a given relation into 3NF relationsthat preserve the FDs.
Determine a minimal (canonical) set of FDs that is equivalentto the given FDs F as follows:
1. Replace every FD α→ B1, . . . ,Bm by α→ Bi , 1 6 i 6 m.2. Minimise: For each A1, . . . ,An → B and each i = 1, . . . ,n
Compute the cover {A1, . . . ,Ai−1,Ai+1, . . . ,An}+F .
If the result contains B, replace F by
F ′ = (F − {A1, . . . ,An → B})
∪ {A1, . . . ,Ai−1,Ai+1, . . . ,An → B}
Keep repeating until all left-hand sides are minimal.3. Remove implied FDs: For each FD α→ B
Compute the cover α+F ′ where F ′ = F − {α→ B}.
If the cover contains B continue with F ← F ′.
![Page 211: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/211.jpg)
3NF Synthesis Algorithm
The following algorithm, the synthesis algorithm, produces alossless decomposition of a given relation into 3NF relationsthat preserve the FDs.
Determine a minimal (canonical) set of FDs that is equivalentto the given FDs F as follows:
1. Replace every FD α→ B1, . . . ,Bm by α→ Bi , 1 6 i 6 m.2. Minimise: For each A1, . . . ,An → B and each i = 1, . . . ,n
Compute the cover {A1, . . . ,Ai−1,Ai+1, . . . ,An}+F .
If the result contains B, replace F by
F ′ = (F − {A1, . . . ,An → B})
∪ {A1, . . . ,Ai−1,Ai+1, . . . ,An → B}
Keep repeating until all left-hand sides are minimal.3. Remove implied FDs: For each FD α→ B
Compute the cover α+F ′ where F ′ = F − {α→ B}.
If the cover contains B continue with F ← F ′.
![Page 212: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/212.jpg)
3NF Synthesis Algorithm
The following algorithm, the synthesis algorithm, produces alossless decomposition of a given relation into 3NF relationsthat preserve the FDs.
Determine a minimal (canonical) set of FDs that is equivalentto the given FDs F as follows:
1. Replace every FD α→ B1, . . . ,Bm by α→ Bi , 1 6 i 6 m.
2. Minimise: For each A1, . . . ,An → B and each i = 1, . . . ,nCompute the cover {A1, . . . ,Ai−1,Ai+1, . . . ,An}
+F .
If the result contains B, replace F by
F ′ = (F − {A1, . . . ,An → B})
∪ {A1, . . . ,Ai−1,Ai+1, . . . ,An → B}
Keep repeating until all left-hand sides are minimal.3. Remove implied FDs: For each FD α→ B
Compute the cover α+F ′ where F ′ = F − {α→ B}.
If the cover contains B continue with F ← F ′.
![Page 213: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/213.jpg)
3NF Synthesis Algorithm
The following algorithm, the synthesis algorithm, produces alossless decomposition of a given relation into 3NF relationsthat preserve the FDs.
Determine a minimal (canonical) set of FDs that is equivalentto the given FDs F as follows:
1. Replace every FD α→ B1, . . . ,Bm by α→ Bi , 1 6 i 6 m.2. Minimise: For each A1, . . . ,An → B and each i = 1, . . . ,n
Compute the cover {A1, . . . ,Ai−1,Ai+1, . . . ,An}+F .
If the result contains B, replace F by
F ′ = (F − {A1, . . . ,An → B})
∪ {A1, . . . ,Ai−1,Ai+1, . . . ,An → B}
Keep repeating until all left-hand sides are minimal.
3. Remove implied FDs: For each FD α→ BCompute the cover α+
F ′ where F ′ = F − {α→ B}.If the cover contains B continue with F ← F ′.
![Page 214: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/214.jpg)
3NF Synthesis Algorithm
The following algorithm, the synthesis algorithm, produces alossless decomposition of a given relation into 3NF relationsthat preserve the FDs.
Determine a minimal (canonical) set of FDs that is equivalentto the given FDs F as follows:
1. Replace every FD α→ B1, . . . ,Bm by α→ Bi , 1 6 i 6 m.2. Minimise: For each A1, . . . ,An → B and each i = 1, . . . ,n
Compute the cover {A1, . . . ,Ai−1,Ai+1, . . . ,An}+F .
If the result contains B, replace F by
F ′ = (F − {A1, . . . ,An → B})
∪ {A1, . . . ,Ai−1,Ai+1, . . . ,An → B}
Keep repeating until all left-hand sides are minimal.3. Remove implied FDs: For each FD α→ B
Compute the cover α+F ′ where F ′ = F − {α→ B}.
If the cover contains B continue with F ← F ′.
![Page 215: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/215.jpg)
3NF Synthesis Algorithm
Compute the canonical set of FDs for
A, B, C→ D, E
B→ C
B→ E
C→ E
C, D→ D, F
![Page 216: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/216.jpg)
3NF Synthesis Algorithm
3NF Synthesis AlgorithmInput: relation R and a set of FDs for R.
1. Compute a canonical (minimal) set of FDs F .
2. For each left-hand side α of an FD in F create a relationwith attributes A = α ∪ {B |α→ B ∈ F }.
3. If none of the relations constructed in step 2 contains a keyof the original relation R, add one relation containing theattributes of a minimal key of R.
4. For any two relations R1, R2 constructed in steps 2,3, if theschema of R1 is contained in the schema R2, discard R1.
![Page 217: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/217.jpg)
3NF Synthesis Algorithm
3NF Synthesis AlgorithmInput: relation R and a set of FDs for R.
1. Compute a canonical (minimal) set of FDs F .
2. For each left-hand side α of an FD in F create a relationwith attributes A = α ∪ {B |α→ B ∈ F }.
3. If none of the relations constructed in step 2 contains a keyof the original relation R, add one relation containing theattributes of a minimal key of R.
4. For any two relations R1, R2 constructed in steps 2,3, if theschema of R1 is contained in the schema R2, discard R1.
![Page 218: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/218.jpg)
3NF Synthesis Algorithm
3NF Synthesis AlgorithmInput: relation R and a set of FDs for R.
1. Compute a canonical (minimal) set of FDs F .
2. For each left-hand side α of an FD in F create a relationwith attributes A = α ∪ {B |α→ B ∈ F }.
3. If none of the relations constructed in step 2 contains a keyof the original relation R, add one relation containing theattributes of a minimal key of R.
4. For any two relations R1, R2 constructed in steps 2,3, if theschema of R1 is contained in the schema R2, discard R1.
![Page 219: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/219.jpg)
3NF Synthesis Algorithm
3NF Synthesis AlgorithmInput: relation R and a set of FDs for R.
1. Compute a canonical (minimal) set of FDs F .
2. For each left-hand side α of an FD in F create a relationwith attributes A = α ∪ {B |α→ B ∈ F }.
3. If none of the relations constructed in step 2 contains a keyof the original relation R, add one relation containing theattributes of a minimal key of R.
4. For any two relations R1, R2 constructed in steps 2,3, if theschema of R1 is contained in the schema R2, discard R1.
![Page 220: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/220.jpg)
3NF Synthesis Algorithm: Example
Use the 3NF synthesis algorithm to normalise the relation
R (A,B,C,D,E,F)
with the following canonical functional dependencies:
A→ D
B→ C
B→ D
D→ E
![Page 221: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/221.jpg)
Efficiency Considerations: BCNF vs 3NF
BCNF does not retain all FDs, therefore 3NF is popular.
Database systems are good at checking key constraints,because they create an index on the key columns.
If we leave a table in 3NF (and not BCNF), we have non-keyconstraints. Namely those FDs that are not implied by keys.
Sometimes we can enforce non-key constraints as follows:create a materialised view that contains the non-key FD(a selection of the columns of the FD)
define the key constraint on the materialised viewupdates to the table will cause updates to the view throughconstraint checking is index-based, hence efficient
![Page 222: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/222.jpg)
Efficiency Considerations: BCNF vs 3NF
BCNF does not retain all FDs, therefore 3NF is popular.
Database systems are good at checking key constraints,because they create an index on the key columns.
If we leave a table in 3NF (and not BCNF), we have non-keyconstraints. Namely those FDs that are not implied by keys.
Sometimes we can enforce non-key constraints as follows:create a materialised view that contains the non-key FD(a selection of the columns of the FD)
define the key constraint on the materialised viewupdates to the table will cause updates to the view throughconstraint checking is index-based, hence efficient
![Page 223: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/223.jpg)
Efficiency Considerations: BCNF vs 3NF
BCNF does not retain all FDs, therefore 3NF is popular.
Database systems are good at checking key constraints,because they create an index on the key columns.
If we leave a table in 3NF (and not BCNF), we have non-keyconstraints. Namely those FDs that are not implied by keys.
Sometimes we can enforce non-key constraints as follows:create a materialised view that contains the non-key FD(a selection of the columns of the FD)
define the key constraint on the materialised viewupdates to the table will cause updates to the view throughconstraint checking is index-based, hence efficient
![Page 224: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/224.jpg)
Efficiency Considerations: BCNF vs 3NF
BCNF does not retain all FDs, therefore 3NF is popular.
Database systems are good at checking key constraints,because they create an index on the key columns.
If we leave a table in 3NF (and not BCNF), we have non-keyconstraints. Namely those FDs that are not implied by keys.
Sometimes we can enforce non-key constraints as follows:
create a materialised view that contains the non-key FD(a selection of the columns of the FD)
define the key constraint on the materialised viewupdates to the table will cause updates to the view throughconstraint checking is index-based, hence efficient
![Page 225: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/225.jpg)
Efficiency Considerations: BCNF vs 3NF
BCNF does not retain all FDs, therefore 3NF is popular.
Database systems are good at checking key constraints,because they create an index on the key columns.
If we leave a table in 3NF (and not BCNF), we have non-keyconstraints. Namely those FDs that are not implied by keys.
Sometimes we can enforce non-key constraints as follows:create a materialised view that contains the non-key FD(a selection of the columns of the FD)
define the key constraint on the materialised viewupdates to the table will cause updates to the view throughconstraint checking is index-based, hence efficient
![Page 226: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/226.jpg)
Efficiency Considerations: BCNF vs 3NF
BCNF does not retain all FDs, therefore 3NF is popular.
Database systems are good at checking key constraints,because they create an index on the key columns.
If we leave a table in 3NF (and not BCNF), we have non-keyconstraints. Namely those FDs that are not implied by keys.
Sometimes we can enforce non-key constraints as follows:create a materialised view that contains the non-key FD(a selection of the columns of the FD)
define the key constraint on the materialised viewupdates to the table will cause updates to the view throughconstraint checking is index-based, hence efficient
![Page 227: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/227.jpg)
Summary
Tables should not contain FDs other than those implied bythe keys (i.e., all tables should be in BCNF).Such violating FDs indicate the combination of pieces of informationwhich should be stored separately (presence of an embedded function).This leads to redundancy.
A relation may be normalized by splitting it.Normalization to BCNF might not preserve FDs.Normalization to 3NF preserevs FDs.
Sometimes it may make sense to avoid a split(and thus to violate BCNF).The DB designer has to carefully resolve such scenarios, incorporatingapplication or domain knowledge.
![Page 228: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/228.jpg)
Summary
Tables should not contain FDs other than those implied bythe keys (i.e., all tables should be in BCNF).Such violating FDs indicate the combination of pieces of informationwhich should be stored separately (presence of an embedded function).This leads to redundancy.
A relation may be normalized by splitting it.Normalization to BCNF might not preserve FDs.Normalization to 3NF preserevs FDs.
Sometimes it may make sense to avoid a split(and thus to violate BCNF).The DB designer has to carefully resolve such scenarios, incorporatingapplication or domain knowledge.
![Page 229: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/229.jpg)
Summary
Tables should not contain FDs other than those implied bythe keys (i.e., all tables should be in BCNF).Such violating FDs indicate the combination of pieces of informationwhich should be stored separately (presence of an embedded function).This leads to redundancy.
A relation may be normalized by splitting it.Normalization to BCNF might not preserve FDs.Normalization to 3NF preserevs FDs.
Sometimes it may make sense to avoid a split(and thus to violate BCNF).The DB designer has to carefully resolve such scenarios, incorporatingapplication or domain knowledge.
![Page 230: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/230.jpg)
Relational Normal Forms
Overview
1. Functional Dependencies (FDs)
2. Anomalies, FD-based Normal Forms
3. Multivalued Dependencies (MVDs) and 4NF
4. Normal Forms and ER Design
5. Denormalization
![Page 231: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/231.jpg)
Introduction
The development of BCNF/3NF has been guided by aparticular type of constraint: functional dependencies.
The goal of normalization into BCNF/3NF is toelimminate the redundant storage of data that follows fromthese constraints, and totransform tables such that the constraints are automaticallyenforced by means of keys
However, there are further types of constraints which arealso useful to during DB design.
![Page 232: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/232.jpg)
Introduction
The development of BCNF/3NF has been guided by aparticular type of constraint: functional dependencies.
The goal of normalization into BCNF/3NF is to
elimminate the redundant storage of data that follows fromthese constraints, and totransform tables such that the constraints are automaticallyenforced by means of keys
However, there are further types of constraints which arealso useful to during DB design.
![Page 233: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/233.jpg)
Introduction
The development of BCNF/3NF has been guided by aparticular type of constraint: functional dependencies.
The goal of normalization into BCNF/3NF is toelimminate the redundant storage of data that follows fromthese constraints, and to
transform tables such that the constraints are automaticallyenforced by means of keys
However, there are further types of constraints which arealso useful to during DB design.
![Page 234: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/234.jpg)
Introduction
The development of BCNF/3NF has been guided by aparticular type of constraint: functional dependencies.
The goal of normalization into BCNF/3NF is toelimminate the redundant storage of data that follows fromthese constraints, and totransform tables such that the constraints are automaticallyenforced by means of keys
However, there are further types of constraints which arealso useful to during DB design.
![Page 235: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/235.jpg)
Introduction
The development of BCNF/3NF has been guided by aparticular type of constraint: functional dependencies.
The goal of normalization into BCNF/3NF is toelimminate the redundant storage of data that follows fromthese constraints, and totransform tables such that the constraints are automaticallyenforced by means of keys
However, there are further types of constraints which arealso useful to during DB design.
![Page 236: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/236.jpg)
Introduction
Recall the Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The condition in the decomposition theorem is onlysufficient (it guarantees losslessness),but not necessary (a decomposition might be losslesseven if the condition is not satisfied).
Multivalued dependencies (MVDs) are constraints that give anecessary and sufficient condition for lossless decomposition
MVDs lead to the Fourth Normal Form (4NF).
![Page 237: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/237.jpg)
Introduction
Recall the Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The condition in the decomposition theorem is onlysufficient (it guarantees losslessness),but not necessary (a decomposition might be losslesseven if the condition is not satisfied).
Multivalued dependencies (MVDs) are constraints that give anecessary and sufficient condition for lossless decomposition
MVDs lead to the Fourth Normal Form (4NF).
![Page 238: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/238.jpg)
Introduction
Recall the Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The condition in the decomposition theorem is onlysufficient (it guarantees losslessness),but not necessary (a decomposition might be losslesseven if the condition is not satisfied).
Multivalued dependencies (MVDs) are constraints that give anecessary and sufficient condition for lossless decomposition
MVDs lead to the Fourth Normal Form (4NF).
![Page 239: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/239.jpg)
Introduction
Recall the Decomposition TheoremThe split of relations is guaranteed to be lossless if theintersection (the shared set attributes) of the attributes of thenew tables is a key of at least one of them.
The condition in the decomposition theorem is onlysufficient (it guarantees losslessness),but not necessary (a decomposition might be losslesseven if the condition is not satisfied).
Multivalued dependencies (MVDs) are constraints that give anecessary and sufficient condition for lossless decomposition
MVDs lead to the Fourth Normal Form (4NF).
![Page 240: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/240.jpg)
Multivalued Dependencies
The following table shows for each employee:knowledge of programming languagesknowledge of programming DBMSs
EMP_KNOWLEDGE
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C DB2John Smith C++ OracleJohn Smith C++ DB2Maria Brown Prolog PostgreSQLMaria Brown Java PostgreSQL
There are no non-trivial functional dependencies.The table is in BCNF.
Nevertheless, there is redundant information.
![Page 241: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/241.jpg)
Multivalued Dependencies
The following table shows for each employee:knowledge of programming languagesknowledge of programming DBMSs
EMP_KNOWLEDGE
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C DB2John Smith C++ OracleJohn Smith C++ DB2Maria Brown Prolog PostgreSQLMaria Brown Java PostgreSQL
There are no non-trivial functional dependencies.
The table is in BCNF.
Nevertheless, there is redundant information.
![Page 242: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/242.jpg)
Multivalued Dependencies
The following table shows for each employee:knowledge of programming languagesknowledge of programming DBMSs
EMP_KNOWLEDGE
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C DB2John Smith C++ OracleJohn Smith C++ DB2Maria Brown Prolog PostgreSQLMaria Brown Java PostgreSQL
There are no non-trivial functional dependencies.The table is in BCNF.
Nevertheless, there is redundant information.
![Page 243: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/243.jpg)
Multivalued Dependencies
The following table shows for each employee:knowledge of programming languagesknowledge of programming DBMSs
EMP_KNOWLEDGE
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C DB2John Smith C++ OracleJohn Smith C++ DB2Maria Brown Prolog PostgreSQLMaria Brown Java PostgreSQL
There are no non-trivial functional dependencies.The table is in BCNF.
Nevertheless, there is redundant information.
![Page 244: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/244.jpg)
Multivalued Dependencies
The table contains redundant data & must be split.
EMP_LANG
ENAME PROG_LANGJohn Smith CJohn Smith C++Maria Brown PrologMaria Brown Java
EMP_DBMS
ENAME DBMSJohn Smith OracleJohn Smith DB2Maria Brown PostgreSQL
Note: table may only be decomposed if PROG_LANG and DBMS areindependent; otherwise loss of information.
E.g. it may not be decomposed if the semantics of the table is that theemployee knows the interface between the language and the database.
![Page 245: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/245.jpg)
Multivalued Dependencies
The table contains redundant data & must be split.
EMP_LANG
ENAME PROG_LANGJohn Smith CJohn Smith C++Maria Brown PrologMaria Brown Java
EMP_DBMS
ENAME DBMSJohn Smith OracleJohn Smith DB2Maria Brown PostgreSQL
Note: table may only be decomposed if PROG_LANG and DBMS areindependent; otherwise loss of information.
E.g. it may not be decomposed if the semantics of the table is that theemployee knows the interface between the language and the database.
![Page 246: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/246.jpg)
Multivalued Dependencies
The table contains redundant data & must be split.
EMP_LANG
ENAME PROG_LANGJohn Smith CJohn Smith C++Maria Brown PrologMaria Brown Java
EMP_DBMS
ENAME DBMSJohn Smith OracleJohn Smith DB2Maria Brown PostgreSQL
Note: table may only be decomposed if PROG_LANG and DBMS areindependent; otherwise loss of information.
E.g. it may not be decomposed if the semantics of the table is that theemployee knows the interface between the language and the database.
![Page 247: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/247.jpg)
Multivalued Dependencies
The table contains redundant data & must be split.
EMP_LANG
ENAME PROG_LANGJohn Smith CJohn Smith C++Maria Brown PrologMaria Brown Java
EMP_DBMS
ENAME DBMSJohn Smith OracleJohn Smith DB2Maria Brown PostgreSQL
Note: table may only be decomposed if PROG_LANG and DBMS areindependent; otherwise loss of information.
E.g. it may not be decomposed if the semantics of the table is that theemployee knows the interface between the language and the database.
![Page 248: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/248.jpg)
Multivalued Dependencies
The multivalued dependency (MVD)
ENAME� PROG_LANG
means that the set of values in column PROG_LANG associatedwith every ENAME is independent of all other columns.
EMP_KNOWLEDGE
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C DB2John Smith C++ OracleJohn Smith C++ DB2Maria Brown Prolog PostgreSQLMaria Brown Java PostgreSQL
That is, the table contains an
embedded function from ENAME to sets of PROG_LANG
![Page 249: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/249.jpg)
Multivalued Dependencies
The multivalued dependency (MVD)
ENAME� PROG_LANG
means that the set of values in column PROG_LANG associatedwith every ENAME is independent of all other columns.
EMP_KNOWLEDGE
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C DB2John Smith C++ OracleJohn Smith C++ DB2Maria Brown Prolog PostgreSQLMaria Brown Java PostgreSQL
That is, the table contains an
embedded function from ENAME to sets of PROG_LANG
![Page 250: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/250.jpg)
Multivalued Dependencies
The multivalued dependency (MVD)
ENAME� PROG_LANG
means that the set of values in column PROG_LANG associatedwith every ENAME is independent of all other columns.
EMP_KNOWLEDGE
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C DB2John Smith C++ OracleJohn Smith C++ DB2Maria Brown Prolog PostgreSQLMaria Brown Java PostgreSQL
That is, the table contains an
embedded function from ENAME to sets of PROG_LANG
![Page 251: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/251.jpg)
Multivalued Dependencies
Formally, ENAME� PROG_LANG holds if: whenever two tuplesagree on ENAME, one can exchange their PROG_LANG values andthe resulting tupes are in the same table.
From the two table rows
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C++ DB2
and the MVD ENAME� PROG_LANG, we can conclude that the tablemust also contain the following rows:
ENAME PROG_LANG DBMSJohn Smith C++ OracleJohn Smith C DB2
This expresses the independence of PROG_LANG for a givenENAME from the rest of the table columns.
![Page 252: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/252.jpg)
Multivalued Dependencies
Formally, ENAME� PROG_LANG holds if: whenever two tuplesagree on ENAME, one can exchange their PROG_LANG values andthe resulting tupes are in the same table.
From the two table rows
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C++ DB2
and the MVD ENAME� PROG_LANG, we can conclude that the tablemust also contain the following rows:
ENAME PROG_LANG DBMSJohn Smith C++ OracleJohn Smith C DB2
This expresses the independence of PROG_LANG for a givenENAME from the rest of the table columns.
![Page 253: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/253.jpg)
Multivalued Dependencies
Formally, ENAME� PROG_LANG holds if: whenever two tuplesagree on ENAME, one can exchange their PROG_LANG values andthe resulting tupes are in the same table.
From the two table rows
ENAME PROG_LANG DBMSJohn Smith C OracleJohn Smith C++ DB2
and the MVD ENAME� PROG_LANG, we can conclude that the tablemust also contain the following rows:
ENAME PROG_LANG DBMSJohn Smith C++ OracleJohn Smith C DB2
This expresses the independence of PROG_LANG for a givenENAME from the rest of the table columns.
![Page 254: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/254.jpg)
Multivalued Dependencies
Multivalued DependencyA multivalued dependency (MVD)
A1, . . . ,An � B1, . . . ,Bm
is satisfied in a DB state I if and only iffor all tuples t ,u in I(R) with t .Ai = u.Ai ,1 6 i 6 n,
there are two further tuples t ′,u ′ in I(R) such that1. t ′ agrees with t except that t ′.Bi = u.Bi ,1 6 i 6 m, and2. u ′ agrees with u except that u ′.Bi = t .Bi ,1 6 i 6 m.
The condition means that the values of the Bi are swapped:
a1, . . . ,an, b1, . . . ,bm, c1, . . . , ck
a1, . . . ,an, b ′1, . . . ,b
′m, c ′
1, . . . , c′k
t
u
a1, . . . ,an, b ′1, . . . ,b
′m, c1, . . . , ck
a1, . . . ,an, b1, . . . ,bm, c ′1, . . . , c
′k
t ′
u ′
![Page 255: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/255.jpg)
Multivalued Dependencies
Multivalued DependencyA multivalued dependency (MVD)
A1, . . . ,An � B1, . . . ,Bm
is satisfied in a DB state I if and only iffor all tuples t ,u in I(R) with t .Ai = u.Ai ,1 6 i 6 n,
there are two further tuples t ′,u ′ in I(R) such that1. t ′ agrees with t except that t ′.Bi = u.Bi ,1 6 i 6 m, and2. u ′ agrees with u except that u ′.Bi = t .Bi ,1 6 i 6 m.
The condition means that the values of the Bi are swapped:
a1, . . . ,an, b1, . . . ,bm, c1, . . . , ck
a1, . . . ,an, b ′1, . . . ,b
′m, c ′
1, . . . , c′k
t
u
a1, . . . ,an, b ′1, . . . ,b
′m, c1, . . . , ck
a1, . . . ,an, b1, . . . ,bm, c ′1, . . . , c
′k
t ′
u ′
![Page 256: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/256.jpg)
Multivalued Dependencies
Multivalued dependencies always come in pairs!
If ENAME� PROG_LANG holds, then ENAME� DBMS is automaticallysatisfied.
More general:
For a relation R(A1, . . . ,An,B1, . . . ,Bm,C1, . . . ,Ck ),the following multivalued dependencies are equivalent
A1, . . . ,An � B1, . . . ,Bm
A1 . . . ,An � C1, . . . ,Ck
Swapping the Bj values in two tuples is the same as swapping the values forall other columns (the Ai values are identical, so swapping them has noeffect).
![Page 257: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/257.jpg)
Multivalued Dependencies
Multivalued dependencies always come in pairs!
If ENAME� PROG_LANG holds, then ENAME� DBMS is automaticallysatisfied.
More general:
For a relation R(A1, . . . ,An,B1, . . . ,Bm,C1, . . . ,Ck ),the following multivalued dependencies are equivalent
A1, . . . ,An � B1, . . . ,Bm
A1 . . . ,An � C1, . . . ,Ck
Swapping the Bj values in two tuples is the same as swapping the values forall other columns (the Ai values are identical, so swapping them has noeffect).
![Page 258: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/258.jpg)
Multivalued Dependencies
Multivalued dependencies always come in pairs!
If ENAME� PROG_LANG holds, then ENAME� DBMS is automaticallysatisfied.
More general:
For a relation R(A1, . . . ,An,B1, . . . ,Bm,C1, . . . ,Ck ),the following multivalued dependencies are equivalent
A1, . . . ,An � B1, . . . ,Bm
A1 . . . ,An � C1, . . . ,Ck
Swapping the Bj values in two tuples is the same as swapping the values forall other columns (the Ai values are identical, so swapping them has noeffect).
![Page 259: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/259.jpg)
Multivalued Dependencies
If the FD A1, . . .An → B1, . . .Bm holds, the corresponding MVD
A1, . . . ,An � B1, . . . ,Bm
is trivially satisfied.
The FD means that if tuples t , u agree on the Ai then also on the Bj .Swapping thus has no effect (yields t , u again).
Deduction rules to derive all implied FDs/MVDsThe three Armstrong Axioms for FDs.If α� β then α� γ, where γ are all remaining columns.If α1 � β1 and α2 ⊇ β2 then α1 ∪ α2 � β1 ∪ β2.If α� β and β� γ then α� (γ− β).If α→ β, then α� β.If α� β and β ′ ⊆ β and there is γ with γ ∩ β = ∅ andγ→ β ′, then α→ β ′.
![Page 260: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/260.jpg)
Multivalued Dependencies
If the FD A1, . . .An → B1, . . .Bm holds, the corresponding MVD
A1, . . . ,An � B1, . . . ,Bm
is trivially satisfied.
The FD means that if tuples t , u agree on the Ai then also on the Bj .Swapping thus has no effect (yields t , u again).
Deduction rules to derive all implied FDs/MVDsThe three Armstrong Axioms for FDs.If α� β then α� γ, where γ are all remaining columns.If α1 � β1 and α2 ⊇ β2 then α1 ∪ α2 � β1 ∪ β2.If α� β and β� γ then α� (γ− β).If α→ β, then α� β.If α� β and β ′ ⊆ β and there is γ with γ ∩ β = ∅ andγ→ β ′, then α→ β ′.
![Page 261: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/261.jpg)
Fourth Normal Form
Fourth Normal Form (4NF)A relation is in Fourth Normal Form (4NF) if every MVD
A1, . . . ,An � B1, . . . ,Bm
iseither trivial, orimplied by a key.
Note: this definition of 4NF is very similar to BCNF but with afocus on implied MVDs (not FDs).
Since every FD is also an MVD, 4NF is stronger than BCNF.That is, if a relation is in 4NF, it is automatically in BCNF.
However, it is not very common that 4NF is violated, but BCNF is not.
![Page 262: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/262.jpg)
Fourth Normal Form
Fourth Normal Form (4NF)A relation is in Fourth Normal Form (4NF) if every MVD
A1, . . . ,An � B1, . . . ,Bm
iseither trivial, orimplied by a key.
Note: this definition of 4NF is very similar to BCNF but with afocus on implied MVDs (not FDs).
Since every FD is also an MVD, 4NF is stronger than BCNF.That is, if a relation is in 4NF, it is automatically in BCNF.
However, it is not very common that 4NF is violated, but BCNF is not.
![Page 263: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/263.jpg)
Fourth Normal Form
Fourth Normal Form (4NF)A relation is in Fourth Normal Form (4NF) if every MVD
A1, . . . ,An � B1, . . . ,Bm
iseither trivial, orimplied by a key.
Note: this definition of 4NF is very similar to BCNF but with afocus on implied MVDs (not FDs).
Since every FD is also an MVD, 4NF is stronger than BCNF.That is, if a relation is in 4NF, it is automatically in BCNF.
However, it is not very common that 4NF is violated, but BCNF is not.
![Page 264: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/264.jpg)
Fourth Normal Form
Fourth Normal Form (4NF)A relation is in Fourth Normal Form (4NF) if every MVD
A1, . . . ,An � B1, . . . ,Bm
iseither trivial, orimplied by a key.
Note: this definition of 4NF is very similar to BCNF but with afocus on implied MVDs (not FDs).
Since every FD is also an MVD, 4NF is stronger than BCNF.That is, if a relation is in 4NF, it is automatically in BCNF.
However, it is not very common that 4NF is violated, but BCNF is not.
![Page 265: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/265.jpg)
Fourth Normal Form
The relation
EMP_KNOWLEDGE (ENAME, PROG_LANG, DBMS)
is an example of a relation that is in BCNF, but not in 4NF.
The relation has no non-trivial FDs.
![Page 266: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/266.jpg)
Other Constraints
Multiple choice testThe following relation encodes the correct solution to a typicalmultiple choice test:
ANSWERS
QUESTION ANSWER TEXT CORRECT1 A ... Y1 B ... N1 C ... N2 A ... N2 B ... Y2 C ... N
Using keys to enforce other constraintsThe constraint is not an FD, MVD, or JD:
“Each question can only have one correct answer.”
Can you suggest a transformation of table ANSWERS suchthat the above constraint is already implied by a key?
![Page 267: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/267.jpg)
Relational Normal Forms
Overview
1. Functional Dependencies (FDs)
2. Anomalies, FD-based Normal Forms
3. Multivalued Dependencies (MVDs) and 4NF
4. Normal Forms and ER Design
5. Denormalization
![Page 268: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/268.jpg)
Introduction
If a “good” ER schema is transformed into the relational model,the result will satisfy all normal forms (4NF, BCNF, 3NF).
A normal form violation detected in the generatedrelational schema indicates a flaw in the input ER schema.
This needs to be corrected on the ER level.
FDs in the ER modelThe ER equivalent of the very first example in this chapter:
courses
crntitle
iname
phone
Obviously, the FD iname→ phoneleads to a violation of BCNF in theresulting table for entity Course.Also in the ER model, FDs betweenattributes of an entity should be impliedby a key constraint.
![Page 269: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/269.jpg)
Introduction
If a “good” ER schema is transformed into the relational model,the result will satisfy all normal forms (4NF, BCNF, 3NF).
A normal form violation detected in the generatedrelational schema indicates a flaw in the input ER schema.
This needs to be corrected on the ER level.
FDs in the ER modelThe ER equivalent of the very first example in this chapter:
courses
crntitle
iname
phone
Obviously, the FD iname→ phoneleads to a violation of BCNF in theresulting table for entity Course.Also in the ER model, FDs betweenattributes of an entity should be impliedby a key constraint.
![Page 270: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/270.jpg)
Introduction
If a “good” ER schema is transformed into the relational model,the result will satisfy all normal forms (4NF, BCNF, 3NF).
A normal form violation detected in the generatedrelational schema indicates a flaw in the input ER schema.
This needs to be corrected on the ER level.
FDs in the ER modelThe ER equivalent of the very first example in this chapter:
courses
crntitle
iname
phone
Obviously, the FD iname→ phoneleads to a violation of BCNF in theresulting table for entity Course.Also in the ER model, FDs betweenattributes of an entity should be impliedby a key constraint.
![Page 271: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/271.jpg)
Introduction
If a “good” ER schema is transformed into the relational model,the result will satisfy all normal forms (4NF, BCNF, 3NF).
A normal form violation detected in the generatedrelational schema indicates a flaw in the input ER schema.
This needs to be corrected on the ER level.
FDs in the ER modelThe ER equivalent of the very first example in this chapter:
courses
crntitle
iname
phone
Obviously, the FD iname→ phoneleads to a violation of BCNF in theresulting table for entity Course.
Also in the ER model, FDs betweenattributes of an entity should be impliedby a key constraint.
![Page 272: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/272.jpg)
Introduction
If a “good” ER schema is transformed into the relational model,the result will satisfy all normal forms (4NF, BCNF, 3NF).
A normal form violation detected in the generatedrelational schema indicates a flaw in the input ER schema.
This needs to be corrected on the ER level.
FDs in the ER modelThe ER equivalent of the very first example in this chapter:
courses
crntitle
iname
phone
Obviously, the FD iname→ phoneleads to a violation of BCNF in theresulting table for entity Course.Also in the ER model, FDs betweenattributes of an entity should be impliedby a key constraint.
![Page 273: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/273.jpg)
Examples
In the ER model, the solution is the “same” as in the relationalmodel: we have to split the entity.
ER entity splitIn this case, the instructor is an independent entity:
courses
crntitle
instructor
iname
phone
given_by 1..10..∗
![Page 274: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/274.jpg)
Examples
In the ER model, the solution is the “same” as in the relationalmodel: we have to split the entity.
ER entity splitIn this case, the instructor is an independent entity:
courses
crntitle
instructor
iname
phone
given_by 1..10..∗
![Page 275: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/275.jpg)
Examples
Functional dependencies between attributes of a relationshipalways violate BCNF.
Violation of BCNF on the ER level
customer product
custNo prodNo
orders0..∗ 0..∗
orderNo
date
The FD orderNo→ date violates BCNF.The key of the table corresponding to the relationship“orders” consists of the attributes CustNo, ProdNo.
This shows that the concept “order” is an independent entity.
![Page 276: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/276.jpg)
Examples
Functional dependencies between attributes of a relationshipalways violate BCNF.
Violation of BCNF on the ER level
customer product
custNo prodNo
orders0..∗ 0..∗
orderNo
date
The FD orderNo→ date violates BCNF.The key of the table corresponding to the relationship“orders” consists of the attributes CustNo, ProdNo.
This shows that the concept “order” is an independent entity.
![Page 277: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/277.jpg)
Examples
Functional dependencies between attributes of a relationshipalways violate BCNF.
Violation of BCNF on the ER level
customer product
custNo prodNo
orders0..∗ 0..∗
orderNo
date
The FD orderNo→ date violates BCNF.The key of the table corresponding to the relationship“orders” consists of the attributes CustNo, ProdNo.
This shows that the concept “order” is an independent entity.
![Page 278: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/278.jpg)
Examples
Violations of BCNF might also be due to the wrong placementof an attribute.
Questionable attribute placement
student course
studId crn
takes0..∗ 0..∗
The relationship is translated intoTAKES (STUD_ID, CRN, EMAIL)
Then the FD STUD_ID→ EMAIL violates BCNF.Obviously, email should be an attribute of Student.
![Page 279: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/279.jpg)
Examples
Violations of BCNF might also be due to the wrong placementof an attribute.
Questionable attribute placement
student course
studId crn
takes0..∗ 0..∗
The relationship is translated intoTAKES (STUD_ID, CRN, EMAIL)
Then the FD STUD_ID→ EMAIL violates BCNF.Obviously, email should be an attribute of Student.
![Page 280: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/280.jpg)
Examples
Violations of BCNF might also be due to the wrong placementof an attribute.
Questionable attribute placement
student course
studId crn
takes0..∗ 0..∗
The relationship is translated intoTAKES (STUD_ID, CRN, EMAIL)
Then the FD STUD_ID→ EMAIL violates BCNF.
Obviously, email should be an attribute of Student.
![Page 281: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/281.jpg)
Examples
Violations of BCNF might also be due to the wrong placementof an attribute.
Questionable attribute placement
student course
studId crn
takes0..∗ 0..∗
The relationship is translated intoTAKES (STUD_ID, CRN, EMAIL)
Then the FD STUD_ID→ EMAIL violates BCNF.Obviously, email should be an attribute of Student.
![Page 282: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/282.jpg)
Examples
If an attribute of a ternary relationship depends only on two ofthe entities, this violates BCNF.
Ternary relationship
instructor course
term
taught
room
If every course is taught only once per term, then attribute roomdepends only on term and course (but not instructor).
Then the FD TERM, COURSE→ ROOM violates BCNF.
![Page 283: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/283.jpg)
Examples
If an attribute of a ternary relationship depends only on two ofthe entities, this violates BCNF.
Ternary relationship
instructor course
term
taught
room
If every course is taught only once per term, then attribute roomdepends only on term and course (but not instructor).
Then the FD TERM, COURSE→ ROOM violates BCNF.
![Page 284: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/284.jpg)
Examples
If an attribute of a ternary relationship depends only on two ofthe entities, this violates BCNF.
Ternary relationship
instructor course
term
taught
room
If every course is taught only once per term, then attribute roomdepends only on term and course (but not instructor).
Then the FD TERM, COURSE→ ROOM violates BCNF.
![Page 285: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/285.jpg)
Normalization: Summary
Relational normalization is about:Avoiding redundancy.Storing separate facts (functions) separately.Transforming general integrity constraints intoconstraints that are supported by the DBMS: keys.
Relational normalization theory is mainly based on FDs,but there are other types of constraints (e.g., MVDs).
![Page 286: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/286.jpg)
Relational Normal Forms
Overview
1. Functional Dependencies (FDs)
2. Anomalies, FD-based Normal Forms
3. Multivalued Dependencies (MVDs) and 4NF
4. Normal Forms and ER Design
5. Denormalization
![Page 287: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/287.jpg)
Denormalization
Denormalization is the process of adding redundantcolumns to the database in order to improve performance.
Redundant data storageFor example, if an application extensively access the phonenumber of instructors, performance-wise it may make sense toadd column PHONE to table COURSES.
COURSES
CRN TITLE INAME PHONE
This avoids the otherwise required joins (on attribute INAME)between tables COURSES and PHONEBOOK.
![Page 288: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/288.jpg)
Denormalization
Denormalization is the process of adding redundantcolumns to the database in order to improve performance.
Redundant data storageFor example, if an application extensively access the phonenumber of instructors, performance-wise it may make sense toadd column PHONE to table COURSES.
COURSES
CRN TITLE INAME PHONE
This avoids the otherwise required joins (on attribute INAME)between tables COURSES and PHONEBOOK.
![Page 289: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/289.jpg)
Denormalization
Denormalization is the process of adding redundantcolumns to the database in order to improve performance.
Redundant data storageFor example, if an application extensively access the phonenumber of instructors, performance-wise it may make sense toadd column PHONE to table COURSES.
COURSES
CRN TITLE INAME PHONE
This avoids the otherwise required joins (on attribute INAME)between tables COURSES and PHONEBOOK.
![Page 290: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/290.jpg)
Denormalization
Since there is still the separate table PHONEBOOK, insertionand deletion anomalies are avoided.
But there will be update anomalies (changing a singlephone number requires the update of many rows).
The performance gain is thus paid for witha more complicated application logic(e.g., the need for triggers)and the risk that a faulty application will turn the DBinconsistent
Denormalization may not only be used to avoid joins:Complete separate, redundant tables may be created(increasing the potential for parallel operations).Columns may be added which aggregate information inother columns/rows.
![Page 291: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/291.jpg)
Denormalization
Since there is still the separate table PHONEBOOK, insertionand deletion anomalies are avoided.
But there will be update anomalies (changing a singlephone number requires the update of many rows).
The performance gain is thus paid for witha more complicated application logic(e.g., the need for triggers)and the risk that a faulty application will turn the DBinconsistent
Denormalization may not only be used to avoid joins:Complete separate, redundant tables may be created(increasing the potential for parallel operations).Columns may be added which aggregate information inother columns/rows.
![Page 292: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/292.jpg)
Denormalization
Since there is still the separate table PHONEBOOK, insertionand deletion anomalies are avoided.
But there will be update anomalies (changing a singlephone number requires the update of many rows).
The performance gain is thus paid for witha more complicated application logic(e.g., the need for triggers)and the risk that a faulty application will turn the DBinconsistent
Denormalization may not only be used to avoid joins:Complete separate, redundant tables may be created(increasing the potential for parallel operations).Columns may be added which aggregate information inother columns/rows.
![Page 293: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/293.jpg)
Denormalization
Since there is still the separate table PHONEBOOK, insertionand deletion anomalies are avoided.
But there will be update anomalies (changing a singlephone number requires the update of many rows).
The performance gain is thus paid for witha more complicated application logic(e.g., the need for triggers)and the risk that a faulty application will turn the DBinconsistent
Denormalization may not only be used to avoid joins:
Complete separate, redundant tables may be created(increasing the potential for parallel operations).Columns may be added which aggregate information inother columns/rows.
![Page 294: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/294.jpg)
Denormalization
Since there is still the separate table PHONEBOOK, insertionand deletion anomalies are avoided.
But there will be update anomalies (changing a singlephone number requires the update of many rows).
The performance gain is thus paid for witha more complicated application logic(e.g., the need for triggers)and the risk that a faulty application will turn the DBinconsistent
Denormalization may not only be used to avoid joins:Complete separate, redundant tables may be created(increasing the potential for parallel operations).
Columns may be added which aggregate information inother columns/rows.
![Page 295: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/295.jpg)
Denormalization
Since there is still the separate table PHONEBOOK, insertionand deletion anomalies are avoided.
But there will be update anomalies (changing a singlephone number requires the update of many rows).
The performance gain is thus paid for witha more complicated application logic(e.g., the need for triggers)and the risk that a faulty application will turn the DBinconsistent
Denormalization may not only be used to avoid joins:Complete separate, redundant tables may be created(increasing the potential for parallel operations).Columns may be added which aggregate information inother columns/rows.
![Page 296: joerg.endrullis.dejoerg.endrullis.de/databases/04_functional_dependencies.pdf/04... · Introduction Functional Dependencies (FDs) are a generalization of keys central part of relational](https://reader031.vdocuments.us/reader031/viewer/2022022005/5ab0784a7f8b9a1d168b5b6d/html5/thumbnails/296.jpg)
Relational Normal Forms: Objectives
After completing this chapter, you should be able towork with functional dependencies (FDs),
define what they aredetect them in database schemasdecide implication, determine keys
explain insert, update, and delete anomalies,understand, explain and use BCNF
test a given relation for BCNF, andtransform a relation into BCNF
understand, explain and use 3NFtest a given relation for 3NF, andtransform a relation into 3NF
understand, explain MVDs and 4NFdetect normal form violations on the level of ER,explain when and how to denormalize a DB schema