| Lesson 6 | Limitations of 1NF |
| Objective | Describe the limitations of 1st normal form. |
Translating a table into 1NF alleviates much of the data redundancy and wasted space of a table that has not been normalized. First normal
form is only a first step to good design. It is still possible for a table in 1NF to be designed inefficiently. Consider the following
relation:
Order (CustID, CustName, Address, Phone, OrderDate, OrderNo, ItemNo, Title, Price, Shipped)
The relation has no repeating groups and is, therefore, in 1NF. But the relation's design is still inefficient. First, the relation describes four entities: Customers (CustID, CustName, Address, Phone), Orders (OrderDate, OrderNo), Item (ItemNo, Title, Price), and order Line Items (OrderNo, ItemNo, Shipped). In this relation, a customer's personal information will be repeated in every record referring to an order the customer placed. As noted earlier, repetition increases the amount of time it takes to update that information and makes it more likely that an update will be missed, causing inconsistencies in the database.
Second, the relation'sconcatenated primary key of OrderNo and ItemNo means that:
Order (CustID, CustName, Address, Phone, OrderDate, OrderNo, ItemNo, Title, Price, Shipped)
The relation has no repeating groups and is, therefore, in 1NF. But the relation's design is still inefficient. First, the relation describes four entities: Customers (CustID, CustName, Address, Phone), Orders (OrderDate, OrderNo), Item (ItemNo, Title, Price), and order Line Items (OrderNo, ItemNo, Shipped). In this relation, a customer's personal information will be repeated in every record referring to an order the customer placed. As noted earlier, repetition increases the amount of time it takes to update that information and makes it more likely that an update will be missed, causing inconsistencies in the database.
Second, the relation'sconcatenated primary key of OrderNo and ItemNo means that:
- You must know at least one item in a customer's order to enter information about that customer.
- You can not enter information about an item until that item has been ordered once.
- Deleting a customers only order deletes all information about that customer. (Remember, a customer's contact information is stored along with their orders, not separately. If you delete from your database the only order a customer has placed, you also delete that customer's contact information.
- Deleting from the database the only order for an item deletes all information about that item.
You can begin to address these problems by putting your relations into second normal form.
- second normal form (2NF) : A table is in second normal form when every attribute is functionally dependent on the entire primary key.
- concatenated primary key : A primary key made up of more than one field.