Databases Reference
In-Depth Information
table, but this table is now much smaller than the sales table and will take a lot
less time to scan.
2.
Updates, such as changing the address of the customer Galler, would require
changing multiple rows. In the two-table equivalent, the customer address
appears only once in the customer table, and any update to that address is con-
fined to a single row in the customer table.
3.
Deleting an order by a valued customer, such as Fry (who bought an expensive
computer), if that is his only outstanding order, deletes the only copy of his
name, address, and credit rating as a side effect. In the two-table equivalent,
Fry's order can still be deleted, but his name, address, and credit rating are all
still maintained in the customer table.
In fact, the two-table equivalent is in third normal form (3NF), and actually a
stronger form of 3NF, called Boyce-Codd normal form (BCNF). It has the property
that only the key of the table uniquely defines the values of all the other attributes of the
table. For example, the key value for 45 for custNo in the customer table determines
that the customer name, Galler, is the only value that can occur in the same row as the
customer number 45. The custNo value also uniquely determines the customer address
and the credit rating. This property of uniqueness is useful in keeping tables small and
nonredundant. These unique properties can be expressed in terms of functional depen-
dencies (FDs):
sales table
orderNo -> productName, custNo, custName, custAddress,
creditRat, date
custNo -> custName, custAddress, creditRat
productSales table
orderNo -> productName, custNo, date
customer table
custNo -> custName, custAddress, creditRat
In the customer and productSales tables, only the key uniquely determines the val-
ues of all the nonkeys in each table, which is the condition necessary for 3NF. In the
sales table, you have a nonkey, custNo, which uniquely determines several attributes in
addition to the key, orderNo. Thus, the sales table does not satisfy 3NF, and this depen-
dency on nonkeys is the source of the loss of integrity and multiple updates in a non-
normalized database.
