All the examples will use this simple table for explanation.
Attribute is column, and tuple is row. In this table the attributes are from A to F.
Functional dependency
A is determinant, B is dependant.
B is functional dependent on A.
A and B are composite key which functionally determines C.
The determinant must only determine a unique value, the same determinant cannot have more than one unique value, the below example is not allowed. A is the determinant which cannot determine B and C, it must be either determining B or C not both.
Different determinants however can have the same values. Such as attribute A determines B, attribute C also determines B.
Partially functional dependency
A and B are candidate keys, but C is dependent only to A. This is partially functional dependent.
Transitive functional dependency
F is dependent on D, but D is not part of candidate key.
E and F dependent on D, but D is not part of candidate key.
Why normalisation?
Normalisation is to remove duplicates. Duplicates or redundant values in a database will create problems. These problems are:
Update anomaly, deletion anomaly, and insert anomaly.
- Update anomaly.
Consider below table, there supposed I need to change C5 to something else, I need to change all instances that has C5.
- Insert anomaly.
Suppose I want to insert a new instance in attribute B, I will need to update a new value in attribute A as well. Insert anomaly arises when inserting a new value requires to insert another attribute as well. - Deletion anomaly.
This anomaly happens when a deletion of a value in an attribute also deletes the values of other attribute(s). Consider if I delete A4, I inadvertently lost B4, C5, D4, E4 and F4.
1NF
To qualify for 1NF these are the requirements:
a. Attributes must be unique.
b. Values in tuple must be atomic i.e. no multiple values, only one value.
c. Values in tuple must be the same domain. Example if the attribute is date, the value must be date related.
2NF
2NF must have the qualification of 1NF and in addition must not have attributes that are partially functional dependent.
Example of 2NF
A and B determine C, A determine B, no partially dependent.
3NF
3NF must be qualified for 2NF, and must not have attribute(s) is/are dependent on another non-key attribute(s) i.e. no transitive dependency
