Database Reference
In-Depth Information
Because order numbers uniquely identify orders, you would add the OrderNum column as the primary
key, giving this table:
Orders (OrderNum,
Examining the various properties of an order, such as the date, customer number, and so on, as listed in
the requirements, you would add the appropriate columns, giving the following:
Orders (OrderNum, OrderDate, CustomerNum, CustomerName,
Street, City, State, Zip, RepNum,
194
What about the fact that you are supposed to store the part number, description, number ordered, and
quoted price for each order line in this order? One way of doing this would be to include all these columns
within the Orders table as a repeating group (because an order can contain many order lines). This would
yield the following:
Orders (OrderNum, OrderDate, CustomerNum, CustomerName,
Street, City, State, Zip, RepNum, (PartNum, Description,
NumOrdered, QuotedPrice) )
At this point, you have a table that contains all the necessary columns. Now you must convert this table
to an equivalent collection of tables that are in third normal form. Because this table is not in first normal
form, you would remove the repeating group and expand the primary key to produce the following:
Orders (OrderNum, OrderDate, CustomerNum, CustomerName, Street,
City, State, Zip, RepNum, PartNum, Description, NumOrdered,
QuotedPrice)
In the new Orders table, you have the following functional dependencies:
OrderNum fi OrderDate, CustomerNum, CustomerName, Street,
City, State, Zip, RepNum
CustomerNum fi CustomerName, Street, City, State, Zip, RepNum
PartNum fi Description
OrderNum, PartNum fi NumOrdered, QuotedPrice
NOTE
Certainly, the combination of OrderNum and PartNum functionally determines all attributes that OrderNum alone would deter-
mine. In addition, the combination determines all attributes that PartNum alone would determine. Adding all these other attri-
butes after the combination of OrderNum and PartNum, while technically correct, would only clutter the list of dependencies. In
general, you should list an attribute after the smallest possible combination that determines it. Because you can determine
Description by PartNum alone, for example, you
should
list Description after PartNum, but you
should not
list Description after
the combination of OrderNum and PartNum.
From the discussion of the quoted price in the requirement, you should note that a quoted price depends
on both the order number and the part number, not on the part number alone. Because some columns
depend on only a portion of the primary key, the Orders table is not in second normal form. Converting to
second normal form would yield the following:
Orders (OrderNum, OrderDate, CustomerNum, CustomerName,
Street, City, State, Zip, RepNum)
Part (PartNum, Description)
OrderLine (OrderNum, PartNum, NumOrdered, QuotedPrice)
The Part and OrderLine tables are in third normal form. The Orders table is not in third normal form
because CustomerNum determines CustomerName, Street, City, State, Zip, and RepNum; CustomerNum is
Search WWH ::




Custom Search