Database Reference
In-Depth Information
not an alternate key, however. Converting the Orders table to third normal form and leaving the other tables
as written 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 will not 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.
195
Orders (OrderNum, OrderDate, CustomerNum)
FK CustomerNum Customer
OrderLine (OrderNum, PartNum, NumOrdered, QuotedPrice)
FK OrderNum Orders
FK PartNum Part
FIGURE 6-7
DBDL for Orders and OrderLine tables
At this point, you have completed the process for each user view. Now it is 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.
Rep
RepNum
LastName
FirstName
Street
City
State
Zip
Commission
Rate
Customer
CustomerNum
CustomerName
Street
City
State
Zip
Balance
CreditLimit
RepNum (FK)
Columns in this
primary key are also
foreign keys
Orders
entity
OrderLine
entity
Part
PartNum
Description
OnHand
Class
Warehouse
Price
Dashed line
indicates nonidentifying
relationship
Orders
OrderNum
OrderDate
CustomerNum (FK)
OrderLine
OrderNum (FK)
PartNum (FK)
NumOrdered
QuotedPrice
Solid line
indicates identifying
relationship
Shape indicates
that order lines can be identiied
only through relationships
with other entities
FIGURE 6-8
Final information-level design
