Database Reference
In-Depth Information
CustomerNumber
CustomerSoldToName
CustomerSoldToAddressLine1
CustomerSoldToAddressLine2
CustomerSoldToCity
CustomerSoldToState
CustomerSoldToZip
CustomerRepNumber
CustomerRepLastName
CustomerRepFirstName
207
ItemNumber
ItemDescription
ItemPrice
InvoiceNumber
InvoiceDate
OrderNumber
ShipDate
Freight
InvoiceTotal
OrderNumber
OrderDate
CustomerPONumber
CustomerShipToName
CustomerShipToAddressLine1
CustomerShipToAddressLine2
CustomerShipToCity
CustomerShipToState
CustomerShipToZip
OrderNumber, ItemNumber
ItemQuantityOrderedƒ(added when order is entered)
ItemQuantityShippedƒ(added during invoicing)
ItemQuantityBackorderedƒ(added during invoicing)
ItemPriceƒ(added when order is entered)
FIGURE 6-17
Revised list of functional dependencies for the Holt Distributors invoice
After you have determined the preliminary functional dependencies, you can begin determining the
tables and assigning columns. You could create tables with the determinant (the column or columns to the
left of the arrow) as the primary key and with the columns to the right of the arrow as the remaining col-
umns. This would lead to the following initial collection of tables:
Customer (CustomerNumber, CustomerSoldToName,
CustomerSoldToAddressLine1, CustomerSoldToAddressLine2,
CustomerSoldToCity, CustomerSoldToState, CustomerSoldToZip,
CustomerRepNumber, CustomerRepLastName,
CustomerRepFirstName)
Part (ItemNumber, ItemDescription, ItemPrice)
Invoice (InvoiceNumber, InvoiceDate, OrderNumber, ShipDate,
Freight, InvoiceTotal)
Order (OrderNumber, OrderDate, CustomerPONumber,
CustomerShipToName, CustomerShipToAddressLine1,
CustomerShipToAddressLine2, CustomerShipToCity,
CustomerShipToState, CustomerShipToZip)
OrderLine (OrderNumber, ItemNumber, ItemQuantityOrdered,
ItemQuantityShipped, ItemQuantityBackordered, ItemPrice)
These tables would then need to be converted to third normal form and the result merged into the
cumulative design.
