Chapter 4.2 : Steps in Normalisation
- First normal form (1NF). Any repeating groups have been removed, so that there is a single value at the intersection of each row and column of the table.
- Second normal form (2NF). Any partial functional dependencies have been removed.
- Third normal form (3NF). Any transitive dependencies have been removed.
Note: If a relation meets the criteria for 3NF, it also meets criteria for 2NF and 1NF. Most design problems can be avoided if the relations are in 3NF.
4.2.1 First Normal Form
Example:
4.2.2 Second Normal Form
A relation is in 2NF if:
- It is in INF, and
- all non-key attributes are fully functionally dependent on the primary key and not on only a portion of the primary key.
Steps to transform into 2NF:
- Identify all functional dependencies in INF.
- Make each determinant the primary key of a new relation.
- Place all attributes that depend on a given determinant in the relation with that determinant that depend on a given determinant in the relation with that determinant as non-key attributes.
All the functional dependencies in this case are:
ORDER-NO –> DATE, CUST-NO, CUSTNAME, CUST-ADDRESS
PART-NO –> PART-DESC
Note : In this case, we say that PART-NO is only partially functional dependent on the key.
(ORDER-NO, PART-NO) – - > QTY-ORDERED, QUOTE-PRICE
The partial functional dependency in
ITEM (ORDER-NO, PART-NO, QTY-ORDERES, QUOTE-PRICE)
creates redundancy in that relation, which results in anomalies when the table is updated.
- Insertion anomaly. To insert a row for the ITEM table, we must provide the part description information too.
- Deletion anomaly. If we delete a row for the ITEM table, we may lose some PART information.
- Modification anomaly. If a PART’s description changes, we must record the change in multiple rows in the ITEM table.
Example:
1 comment
wahhhh.. mao ni amo discussion run