What is 1NF?
No multivalued columns
What is 2NF?
1NF & no partial dependencies
What is 3NF?
2NF & no transitive dependencies
What is BCNF?
3NF & every dependency has the form X -> Y where, Y ⊆ X or X is a super key
What are the 3 Armstrong Axioms?
Reflexivity
Augmentation
Transitivity
What is Reflexivity?
If X ⊆ Y
then Y -> X
If you have A, B, C -> A, B, C
What is Augmentation?
If X -> Y
then X, Z -> Y, Z
If we have A, B -> C then we can say A, B, Z -> C, Z
What is Transitivity?
If X -> Y and Y -> Z
then X -> Z
What are the 6 steps in ‘Table Decompositions’?
- Determine FDs
- Determine all CKs
- Identify PD & TDs
- Gather all PDs & TDs that have the same determinant into a single FD statemen
- Decompose table T with respect to the largest PD statement
- Repeat steps 1-5 until no tables have PDs or TDs
When decomposing tables. What does T1 contain?
- All columns from T except the dependent columns of the largest PD
- A single FK in T1 on the determinant columns of the largest PD referencing the primary key of T2
- Bring down all the constraints that were in T
When decomposing tables. What does T2 contain?
Contain all columns from the largest PD
The determinant of the largest PD becomes the PK of T2
What is ‘Determinant’?
Left side of all functional dependencies
What are ‘Dependent Columns’
Right side of all functional dependencies
What are ‘Partial Dependencies’?
A FD where 1 or more non-key attributes are dependent on only part of a candidate key
What are ‘Transitive Dependency’?
A FD where 1 or more non-key attributes are dependent on a determinant that includes a non-key attribute
Decompose T into a normalized set of tables (BCNF)
T(A, B, C, D, E, F)
A, B -> C
A, B -> D, E
C -> D, E
E -> F
Non-trivial FDs:
C -> F
A, B -> F
CK: (A,B)
T1(#A, #B, <u>C</u>)
T2(#C, D, E)
T3(#E,F)
Decompose T into a normalized set of tables (BCNF)
T(A, B, C, D)
A, B -> C
A, B -> D
B, C -> D
A -> D
Non-trivial FDs:
A -> D
B, C -> D
CK: (A,B)
T1(<u>#A</u>, #B, C)
T2(#A, D)
What is 4NF?
BCNF &
Table has no multi-valued dependencies
What are the anomalies that lead to issues due to redundancy?
- Insertion Anomaly
- Deletion Anomaly
- Updation Anomaly
What is an ‘Insertion Anomaly’?
To insert redundant data for every new row
