Normalization¶
Normalize¶
EER Diagram always produces the databases that are normalized.
What is all about?¶
The Rules¶
No redundancy of facts.
No cluttering of facts.
Must preserve information.
Must preserve functional dependencies.
Not a relation¶
Multi-value attributes.
Values of attributes are pull from set of atomic values.
NF2 = Non First Normal Form
Relation with Problems¶
Relation with Problems - Redundancy¶
Relation with Problems - Insertion Anamoly¶
Relation with Problems = Deletion Anamoly¶
Relation with Problems - Update Anamoly¶
Information loss¶
Dependency Loss¶
Perfect Decomposition¶
Functional Dependencies¶
Full Functional Dependency¶
Functional Dependencies and Keys¶
How to enforce functional dependency.
We use keys to enforce functional dependencies X->Y
Overview of Normal Forms¶
Non First Normal Forms Datastructures
Normal Form - Definitions¶
NF^2: Non First Normal Form
1NF: R is in 1NF iff all domain values are atomic.
2NF: R is in 2NF iff R is in 1NF and every non-key attribute is fully dependent on the key.
3NF: R is in 3NF iff R is in 2NF and every non-key attribute is non-transitively dependent on the key.
BCNF (Boyce-Codd Normal Form): R is in BCNF iff every determinant is a candidate key.
Determinant: A set of attributes on which some other attribute is fully functionally dependent.
Kent and Diehr Quote¶
All attributes must dependent on the key (1NF), the whole key (2NF), and nothing but the key (3NF), so help me codd!
1NF BCNF flow chart¶
Compute with Functional Dependencies with Armstrongs Rules¶
How to guarantee lossless joins¶
How to guarantee preservation of FDs¶
Email Interest - Good Decomposition¶
3NF and BCNF¶
There does exist relations which can only be decomposed to 3NF, but not to BCNF, while being lossless and dependency preserving.
It can only happen when the relation has overlapping keys.
It Never Happens in Practice¶
There does exists relations that exists in 3NF and not in BCNF. (Only in theory)