|
2nd Normal Form
The updated tables below are now in second normal form. There are no longer any partial key dependencies. The plant manager has been moved into a second table with just the one plant manager for each plant name. But there is still a 3rd normal form problem.
3rd Normal Form
Definition
To be in 3rd normal form a table must meet the following conditions:
The table is in 2nd normal form.
No non-key fields determine other non-key fields. This is called a transitive dependency.
Example
Let us look at an order processing example. A sample orders table might be...
ORDERS(orderID, orderDate, orderAmount, customerID, CustomerName)
But this is not correct as customerName depends on customerID. Once you know the customerID you can determine the address. To correct this create two tables as was done for the 2nd normal form correction. The primary key of the Customer table serves as a foreign key in the order table. The customer table contains the customerName field which is dependent on the customerID primary key..
ORDERS(orderID, orderDate, orderAmount)
CUSTOMERS(customerID, customer_name)
3rd normal form violation
Chemical Plants Example
Our example still has a problem. As can be seen below the manufacturer's address is dependent on the manufacturer which is a non-key field. This is a transitive dependency which violates 3rd normal form.
Correcting the 3rd normal form violation
Again, we will have to use two tables to correct this.
Continue
to page 4 - database normalized to 3rd normal form |
