Databases Reference
In-Depth Information
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
185
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, num-
ber 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 addi-
tion, 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.
What are the user views in the preceding example? In particular, how should the design proceed if you
are given requirements that are not specifically stated in the form of user views? Sometimes you might encoun-
ter a series of well thought-out user views in a form that you can easily merge into the design. Other times you
might be given only a set of requirements, such as those described in Example 1. In another situation, you
might be given a list of reports and updates that a system must support. In addition to the requirements, when
you are able to interview users and document their needs before beginning the design process, you can make
sure that you understand the specifics of their user views
prior
to starting the design process. On the other
hand, you may have to take information as you get it and in whatever format it is provided.
When the user views are not clearly defined, you should consider each stated requirement as a separate
user view. Thus, you can think of each report or update transaction that the system must support, as well as
any other requirement stated in the user views, as an individual user view. In fact, even when the require-
ments are presented as user views, you may want to split a complex user view into smaller pieces and con-
sider each piece as a separate user view for the design process.
To transform each user view into DBDL, examine the requirements and create the necessary entities, keys,
and relationships.
User View 1:
For a sales rep, store the sales rep's number, name, address, total commission, and commis-
sion rate. You'll need to create only one table to support this view:
Rep (
RepNum
, LastName, FirstName, Street, City, State,
Zip, Commission, Rate)
This table is in third normal form. Because there are no foreign, alternate, or secondary keys, the DBDL
representation of the table is the same as the relational model representation.
