Database Reference
In-Depth Information
4. Determine and list a primary key for each table. In this example, you might have the following:
Rep (RepNum,
Customer (CustomerNum,
Orders (OrderNum,
Part (PartNum,
5. For each one-to-many relationship you can identify among these entities, optionally create and
document an appropriate foreign key. For example, if there is a one-to-many relationship
between the Rep and Customer tables, add the foreign key RepNum to the Customer table. If
you omit this step or fail to list any foreign keys, you will usually find the foreign keys when you
examine the individual user views later.
After completing the steps for a top-down approach, you can then apply the bottom-up method for
examining individual user views. As you design each user view, keep in mind the tables you have created in
the initial top-down approach and their keys. When you need to determine the primary key for a table, look
for a primary key in your cumulative design. When it is time to determine a foreign key, check the entity
203
'
s
primary key to see if a match exists in the cumulative design. In either case, if the primary key already
exists, use the existing name as a foreign key to ensure that you can merge the tables properly. At the end of
the design process, you can consider removing any tables that do not contain columns and that have no for-
eign keys matching them.
Adding these steps to the process brings the benefits of the top-down approach to the approach you have
been using. As you proceed through the design process for the individual user views, you will have a general
idea of the overall picture.
SURVEY FORM
When designing a database, you might find it helpful to design a survey form to obtain the required informa-
tion from users. You can ask users to complete the form, or you may want to complete the form yourself
during an interview with the user. Before beginning the interview, you can identify all existing data by view-
ing various reports, documents, and so on. In any case, it is imperative that the completed survey form con-
tain all the information necessary for the design process.
To be truly valuable to the design process, the survey form must contain the following information:
Entity information. For each entity (reps, customers, parts, and so on), record a name and
description and identify any synonyms for the entity. For example, at Premiere Products, your
survey might reveal that what one user calls
In addition,
record any general information about the entity, such as its use within the organization.
“
parts
”
another user calls
“
products.
”
Attribute (column) information. For each attribute of an entity, list its name, description,
synonyms, and physical characteristics (such as being 20 characters long and alphanumeric or a
number with five digits), along with general information concerning its use. In addition, list any
restrictions on values and the place from which the values for the item originate. (For example,
the values might originate from time cards or from orders placed by customers or be computed
from values from other attributes, such as when subtracting the balance from the credit limit to
obtain available credit). Finally, list any security restrictions that apply to the attribute.
Relationships. For any relationship, the survey form should include the entities involved, the
type of relationship (one-to-one, one-to-many, or many-to-many), the significance of the rela-
tionship (that is, what determines when two objects are related), and any restrictions on the
relationship.
Functional dependencies. The survey form should include information concerning the func-
tional dependencies that exist among the columns. To obtain this information, you might ask the
user a question such as this: If you know a particular employee number, can you establish other
information, such as the name? If so, you can determine that the name is functionally depen-
dent on the employee number. Another question you might ask is this: Do you know the number
of the department to which the employee is assigned? If so, you can determine that the depart-
ment number is functionally dependent on the employee number. If a given employee can be
assigned to more than one department, you would not know the department number and the
department number would not be dependent on the employee number. Users probably will not
