Database Reference
In-Depth Information
Rep entity
Rep
RepNum
LastName
FirstName
Street
City
State
Zip
Commission
Rate
Customer
entity
Customer
CustomerNum
CustomerName
Street
City
State
Zip
Balance
CreditLimit
RepNum (FK)
193
Relationship
Foreign key
“Many”
part of the
relationship
FIGURE 6-5
Cumulative design after second user view
User View 3 Requirements: Like the first user view, this one poses no special problems. For a part,
store the part
s number, description, units on hand, item class, number of the warehouse in which the
part is located, and price. Only one table is required to support this user view:
'
Part (PartNum, Description, OnHand, Class, Warehouse, Price)
This table is in third normal form. The DBDL representation is identical to the relational model
representation.
Because PartNum is not the primary key of any table you have already encountered, merging this
table into the cumulative design produces the design shown in Figure 6-6, which contains the tables Rep,
Customer, and Part.
Part
entity
Rep
RepNum
LastName
FirstName
Street
City
State
Zip
Commission
Rate
Part
PartNum
Description
OnHand
Class
Warehouse
Price
Customer
CustomerNum
CustomerName
Street
City
State
Zip
Balance
CreditLimit
RepNum (FK)
No relationship
exists between the Part entity
and other entities at
this point
FIGURE 6-6
Cumulative design after third user view
User View 4 Requirements: This user view is more complicated, and you can approach it in several
ways. For an order, store the order number; order date; number, name, and address of the customer that
placed the order; and number of the sales rep who represents that customer. In addition, for each line item
within the order, store the part number and description, number of the part that was ordered, and quoted
price. Suppose that you decide you need to create only a single entity for orders. You might create the
following table:
Orders (
