An entity is in second normal form (2NF) if it is in first normal form and every non-key attribute of this entity is fully functionally dependent on its primary key.
The following entity is in 1NF but the non-key attributes are not fully functionally dependent on the primary key.
Supplier’s Inventory
K Supplier number
K Part code
Supplier name
Supplier status
Supplier status description
Part Quantity
|
SNumber |
PNumber |
Name |
Status |
Sstatus Desc |
Part Qty |
|---|---|---|---|---|---|
|
111 111 111 245 245 |
05 10 15 05 10 |
Computer Store Computer Store Computer Store Floppy Discount Floppy Discount |
105 105 105 100 100 |
Wholesale Wholesale Wholesale Wholesale Wholesale |
100 210 534 498 021 |
You can see that the Supplier Name and Status are functionally dependent on Supplier Number only and not the Part Number. Therefore, the Supplier Name and Status are not fully functionally dependent on the Supplier Number and Part Number. The entity is not in 2NF.
The Supplier Status is functionally dependent on Supplier Number. This dependency means that a value of Supplier Number requires a specific value of Supplier Status. If the Supplier Number is 111, the Supplier Status must be 105.
Make the following changes to get the entity into 2NF:
Supplier
K Supplier number
Supplier name
Supplier status
Supplier status description
Supplier’s Inventory
K Supplier number
K Part number
Part quantity
Both entities are now in at least 2NF. Supplier's Inventory is in 3NF.
|
Copyright © 2014 CA.
All rights reserved.
|
|