Database Reference
In-Depth Information
User view
Step 1.
Represent the user view as a collection of tables (relations).
Collection of tables
Step 2.
Normalize these tables.
Collection of
normalized tables
190
Step 3.
Represent all keys.
Collection of
normalized tables with
keys represented
Step 4.
Merge the result of the previous steps into the
cumulative design.
New cumulative design
Old cumulative design
FIGURE 6-3
Information-level design method
DATABASE DESIGN EXAMPLES
Now that you understand how to represent a database in DBDL and in an E-R diagram, you can examine the
requirements of another database, the Premiere Products database. In the process, you will see how a set of
requirements led to the database with which you have been working throughout this text.
EXAMPLE 1
Complete an information-level design for a database that satisfies the following constraints and user view
requirements for a company that stores information about sales reps, customers, parts, and orders.
User View 1 Requirements: For a sales rep, store the sales rep's number, name, address, total commis-
sion, and commission rate.
User View 2 Requirements: For a customer, store the customer's number, name, address, balance, and
credit limit. In addition, store the number and name of the sales rep who represents this customer. A sales
rep can represent many customers, but a customer must have exactly one sales rep. (A customer must have
a sales rep and cannot have more than one sales rep.)
User View 3 Requirements: 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. All units of a particular part are stored in
the same warehouse.
User View 4 Requirements: 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. The user also has supplied the following constraints:
a. Each order must be placed by a customer that is already in the Customer table.
b. There is only one customer per order.
c. On a given order, there is, at most, one line item for a given part. For example, part DR93 cannot
appear in several lines within the same order.
d. The quoted price might not match the current price in the Part table, allowing the company to sell
the same parts to different customers at different prices. The user wants to be able to change the
price for a part without affecting orders that are currently on file.
