Databases Reference
In-Depth Information
→
CustomerSoldToName
CustomerSoldToAddressLine1
CustomerSoldToAddressLine2
CustomerSoldToCity
CustomerSoldToState
CustomerSoldToZip
CustomerRepNumber
CustomerRepLastName
CustomerRepFirstName
CustomerNumber
202
ItemNumber
ItemDescription
ItemPrice
InvoiceNumber
→
InvoiceDate
OrderNumber
ShipDate
Freight
InvoiceTotal
→
OrderDate
CustomerPONumber
CustomerShipToName
CustomerShipToAddressLine1
CustomerShipToAddressLine2
CustomerShipToCity
CustomerShipToState
CustomerShipToZip
OrderNumber
→
ItemQuantityOrderedƒ(added when order is entered)
ItemQuantityShippedƒ(added during invoicing)
ItemQuantityBackorderedƒ(added during invoicing)
ItemPriceƒ(added when order is entered)
OrderNumber, ItemNumber
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 columns. 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 cumu-
lative design.
