Database Reference
In-Depth Information
What are the user views in Example 1? 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 encounter 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 Requirements: For a sales rep, store the sales rep
191
s number, name, address, total
commission, and commission rate. You will 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.
Notice that you have assumed the sales rep
this is a
reasonable assumption. Because the user did not provide this information, however, you would need to verify
its accuracy with the user. In each of the following requirements, you can assume the obvious column (cus-
tomer number, part number, and order number) is the primary key. Because you are working on the first
user view, the
'
s number (RepNum) is the Rep table
'
s primary key
—
step of the design method will produce a cumulative design consisting of only the Rep
table, which is shown in Figure 6-4. This design is simple, so you do not need to represent it with an E-R
diagram.
“
merge
”
Rep (RepNum, LastName, FirstName, Street, City, State, Zip,
ƒƒƒƒ Commission, Rate)
FIGURE 6-4
Cumulative design after first user view
User View 2 Requirements: Because the first user view was simple, you were able to create the necessary
table without having to complete each step mentioned in the information-level design method section.
The second user view is more complicated, however, so you will use all the steps to determine the tables.
(If you have already determined what the tables should be, you have a natural feel for the process. If so,
please be patient and work through the process.)
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. You will take two different approaches
to this requirement, allowing you to see how they both can lead to the same result. The only difference
between the two approaches is the entities that you initially identify. In the first approach, suppose you iden-
tify two required entities for sales reps and customers. You would begin by listing the following two tables:
'
Rep (
Customer (
After determining the unique identifiers, you add the primary keys, which would give the following:
Rep (RepNum,
Customer (CustomerNum,
