DATABASE DESIGN 2: DESIGN METHOD - Concepts of Database Management - page 189

NOTE
Certainly, the combination of OrderNum and PartNum functionally determines all attributes that OrderNum alone would determine.
In addition, the combination determines all attributes that PartNum alone would determine. Adding all these other attributes 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.

189

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; but CustomerNum
is not an alternate key. Converting the Orders table to third normal form and leaving the other tables as writ-
ten would produce the following design for this requirement:

Orders (OrderNum, OrderDate, CustomerNum)
Customer ( CustomerNum , CustomerName, Street, City, State,
Zip, RepNum)
Part ( PartNum , Description)
OrderLine (OrderNum, PartNum, NumOrdered, QuotedPrice)

You can represent this collection of tables in DBDL and then merge them into the cumulative design. Again,
however, you can look ahead and see that you can merge this Customer table with the existing Customer table
and this Part table with the existing Part table. In both cases, you won't need to add anything to the
Customer and Part tables already in the cumulative design; so the Customer and Part tables for this user view
will not affect the overall design. The DBDL representation for the Orders and OrderLine tables appears in
Figure 6-7.

Orders (OrderNum, OrderDate, CustomerNum)
FK CustomerNum

→

Customer

OrderLine (OrderNum, PartNum, NumOrdered, QuotedPrice)
FK OrderNum → Orders
FK PartNum → Part

FIGURE 6-7 DBDL for the Orders and OrderLine tables

At this point, you have completed the process for each user view. Now it's time to review the design to make
sure it will fulfill all the stated requirements. If the design contains problems or new information arises, you
must modify the design to meet the new user views. Based on the assumption that you do not have to modify
the design further, the final information-level design appears in Figure 6-8.