Database Reference
In-Depth Information
USER VIEWS
Regardless of which approach an organization adopts to implement its database design, a complete database
design that will satisfy all the organization
s requirements is rarely a one-step process. Unless the require-
ments are simple, an organization will usually divide the overall job of database design into many smaller
tasks by identifying the individual pieces of the design problem, called user views. A user view is the set of
requirements that is necessary to support the operations of a particular database user. For example, at Pre-
miere Products, the database must be capable of storing each part
'
s number, description, units on hand, item
class, number of the warehouse in which the part is located, and price. It is critical to analyze and determine
these user views carefully before beginning the design process.
For each user view, designers must design the database structure to support the view and then merge it
into a cumulative design that supports all the user views encountered during the design process. Each user
view is generally much simpler than the total collection of requirements, so working on individual user views
is usually more manageable than attempting to turn the design of the entire database into one large task.
'
184
INFORMATION-LEVEL DESIGN METHOD
The information-level design method in this text involves representing individual user views, refining them to
eliminate any problems, and then merging them into a cumulative design. After you have represented and
merged all user views, you can complete the cumulative design for the entire database.
When creating user views, a
can be a person or a group that will use the system, a report that the
system must produce, or a type of transaction that the system must support. In the last two instances, you
might think of the user as the person who will use the report or enter the transaction. In fact, if the same
user requires three separate reports, for example, it is more efficient to consider each report as a separate
user view, even though only one
“
user
”
is involved, because smaller user views are easier to construct.
For each user view, the information-level design method requires you to complete the following steps:
“
user
”
1. Represent the user view as a collection of tables.
2. Normalize these tables.
3. Identify all keys in these tables.
4. Merge the result of Steps 1 through 3 into the cumulative design.
In the following sections, you will examine each of these steps in detail.
Represent the User View as a Collection of Tables
When provided with a user view or some sort of stated requirement, you must develop a collection of tables
that will support it. In some cases, the collection of tables may be obvious to you. For example, suppose a
requested user view involves departments and employees, each department can hire many employees, and
each employee can work in only one department (a typical restriction). A design similar to the following may
have naturally occurred to you. It is an appropriate design.
Department (DepartmentNum, Name, Location)
Employee (EmployeeNum, LastName, FirstName, Street, City,
State, Zip, WageRate, SocSecNum, DepartmentNum)
You will undoubtedly find that the more designs you complete, the easier it will be for you to develop
such a collection without resorting to any special procedure. The real question is this: What procedure should
you follow when the correct design is not so obvious? In this case, you can complete the following four steps.
Step 1. Determine the entities involved and create a separate table for each type of entity. At this point,
you do not need to do anything more than name the tables. For example, if a user view involves departments
and employees, you can create a Department table and an Employee table. At this point, you will write some-
thing like this:
Department (
Employee (
That is, you will write the name of a table and an opening parenthesis, and that is all. You will assign
columns to these tables in later steps.
