Normal Forms
- These were defined by Codd and others
- Designed to eliminate anomalies.
- Based on primary and candidate keys and functional dependencies.
- Done in a series of decompositions.
- Developed as follows: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, others
- Unnormalized Form
- A table contains one or more repeating groups
- Or order is important.
-
| ClientNo | ClientName | PropertyNo | Owner |
CR 76 | John Kay | PG4 | Tina Murphay
| PG16 | Tony Shaw
| CR 55 | Aline Stewart | PG4 | Tina Murphay
| PG16 | Tony Shaw
| PG42 | Tony Shaw
|
- First Normal Form (1NF)
- Any table that meets the definition of a relation is in 1NF.
- UNF to 1NF
- Fill in the blanks by duplicating the data for shared rows.
- This is called "Flattening" the table.
- This introduces redundancy into the table, but it is removed later.
-
| ClientNo | ClientName | PropertyNo | Owner |
CR 76 | John Kay | PG4 | Tina Murphay
| CR 76 | John Kay | PG16 | Tony Shaw
| CR 55 | Aline Stewart | PG4 | Tina Murphay
| CR 55 | Aline Stewart | PG16 | Tony Shaw
| CR 55 | Aline Stewart | PG42 | Tony Shaw
|
- Here client information was duplicated to each row.
- Build a separate relation for repeating data.
- Need to identify a primary key, which will also be a foreign key.
- This may need to be repeated if there are nested repeating groups.
-
| ClientNo | ClientName |
|---|
| CR 76 | John Kay |
| CR 55 | Aline Stewart |
-
| ClientNo | PropertyNo | Owner |
CR 76 | PG4 | Tina Murphay
| CR 76 | PG16 | Tony Shaw
| CR 55 | PG4 | Tina Murphay
| CR 55 | PG16 | Tony Shaw
| CR 55 | PG42 | Tony Shaw
|
- Here the duplicate information (Owner Name) has been placed in a new table,
- Convert this table to 1NF
| StudentID | Activity | Fee |
|---|
| 100 | Golf | 65 |
| Skiing | 200 |
| 200 | Skiing | 200 |
| Swimming | 50 |
| 300 | Skiing | 200 |
| Swimming | 50 |
| 400 | Golf | 65 |
| Swimming | 50 |
- Second Normal Form (2NF)
- Full Functional Dependency:
- If A and B are attributes of a relation, B is fully functionally dependent of A if B is functionally dependent on A but not a proper subset of A.
- If (A,C,D) → B, but (A,C) → B, then (A,C,D) → B is a partial dependency
- In other words, the determinant is minimal.
- A relation is in 2NF if it is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key.
- Or R(A,B, C,D,E) is in 2NF if C,D,and E can not be determined by A or B alone. (So none of these exist A → C, A→ D, A→ E, B→ C, B→D, B→ E).
- Relations with a single attribute primary key are automatically in 2NF.
- This eliminates update anomalies.
-
| ClientNo | ClientName | PropertyNo | Address | Start | Finish | Price | OwnerNo | Owner |
|---|
| CR76 | John Kay | PG4 | 6 Lawrence St, Glasgow | 1-Jul-00 | 31-Aug-01 | 350 | CO40 | Tina Murphy |
| CR76 | John Kay | PG16 | 5 Novar Dr, Glasgow | 1-Sep-01 | 1-Sep-02 | 450 | CO93 | Tony Shaw |
| CR56 | Aline Stewart | PG4 | 6 Lawrence St, Glasgow | 1-Sep-99 | 10-Jun-00 | 350 | CO40 | Tina Murphy |
| CR56 | Aline Stewart | PG36 | 5 Manor Rd, Glasgow | 10-Oct-00 | 1-Dec-01 | 375 | CO93 | Tony Shaw |
| CR56 | Aline Stewart | PG16 | 5 Novar Dr, Glasgow | 1-Nov-02 | 10-Aug-03 | 450 | CO93 | Tony Shaw |
- We assume that a a client rents a given property only once and cannot rent more than one property at a time.
- (ClientNo, PropertyNo ) is the primary key.
- Some Dependencies to note:
- (ClientNo,PropertyNo) is a candidate key
- ClientNo → ClientName
- PropertyNo → Address, OwnerNo, Owner
- There are other candidate keys, (ClientNo, Start), (ProperyNo, Start)
- But what we are most interested in for 2NF is
- ClientNo → ClientName
- PropertyNo → Address, OwnerNo, Owner
- Let us focus on the first.
- If John Kay were to rent another property
- Both the ClientNo and ClientName would need to be inserted into the table again.
- If ClientName were misspelled, there would be a problem.
- But the relation is a partial dependency.
- IE ClientNo determines ClientName.
- So the ClientNO/ClientName relation needs to be extracted and placed in a different table.
- The same is true for PropertyNo → (Address, OwnerNo, Owner)
- New Schema
- CLIENT(ClientNo, ClientName)
-
| ClientNo | ClientName |
|---|
| CR76 | John Kay |
| CR56 | Aline Stewart |
- PROPERTY_OWNER(PropertyNo, Address,Rent, OwnerNo, Owner)
| PropertyNo | Address | Rent | OwnerNo | Owner |
|---|
| PG4 | 6 Lawrence St, Glasgow | 350 | CO40 | Tina Murphy |
| PG16 | 5 Novar Dr, Glasgow | 450 | CO93 | Tony Shaw |
| PG36 | 2 Manor Rd St, Glasgow | 375 | CO93 | Tony Shaw |
- RENTAL(ClientNo, PropertyNo, Start, Finish)
| ClientNo | PropertyNo | Start | Finish |
|---|
| CR76 | PG4 | 1-Jul-00 | 31-Aug-01 |
| CR76 | PG16 | 1-Sep-01 | 1-Sep-02 |
| CR56 | PG4 | 1-Sep-99 | 10-Jun-00 |
| CR56 | PG36 | 10-Oct-00 | 1-Dec-01 |
| CR56 | PG16 | 1-Nov-02 | 10-Aug-03 |
- Notice, there still is a Dependency in PROPERTY_OWNER
- OwnerNo → Owner
- Owner → OwnerNo.
- Does this violate 2NF?
- Does this still present a possibility of an insertion anomaly?
- Does this still present a possibility of an deletion anomaly?
- Does this still present a possibility of an modification anomaly?
- Convert this table to 2NF
| StudentID | Activity | Fee |
|---|
| 100 | Golf | 65 |
| 100 | Skiing | 200 |
| 200 | Skiing | 200 |
| 200 | Swimming | 50 |
| 300 | Skiing | 200 |
| 300 | Swimming | 50 |
| 400 | Golf | 65 |
| 400 | Swimming | 50 |