», A Productive-skepticism warning: users do not always

see a template's usefulness as clearly as they do its threat.

familiarity can alleviate fear. In this situa-
tion, our wisest course is to include tem-
plates in the design but temporarily leave
them out of the processing. Thus, a typical
seguence of change reguests reads:

- January (system newly installed):
"Delivery dates must be entered by hand.
They are too important to be left to the
computer."

April: "We need a list of standard
lead times we can refer to (while manually
Computing and entering all those delivery
dates)."

July: "The resupply report should
automatically compute each ofder's esti-
mated delivery date. But this must not go
directly into the database. Instead, we shall
transcribe the dates from report to data-
base, correcting each as needed."

October: "Computed delivery dates
should go automatically into the database,

0: separate Executive Review Report must

be provided, however. It should list each
day's computed delivery dates so we can
carefully review them and correct those in
error."

January: "Executive Review Re-
port? Never heard of it. Oh yes, now I re-
member—-that's the one Joe binds and files,
Nobody knows what it's for."

THE SELF. In previous issues we ad-
RELATED dressed two of the three
RECORD goals in the scope state-

ment for a maintenance
eguipment spare parts inventory system.
We've determined that the hub of our data-
base is a part-item record with one occur-
rence for each different kind of part we
stock. The record holds the part's ID num-
ber, description, on-hand guantity, reorder
point, and delivery lead time.
Now, in part 13, we examine the
third goal, that is, to "'identify, for each
art, those pieces of eguipment in which it
is used." The statement also implies the

Converse: to identify, for each piece of
eguipment, the spare parts it uses.

One approach is to add an eguip-
ment box, that is, a file containing a record
for each piece of eguipment.

The arrow is two-headed because,
while any part (e.g., lubricant) could be
used in many different kinds of eguipment,
a given piece of eguipment could reguire
many different types of parts. Recall that a
two-headed arrow warns of a missing inter-
section entity. Replacing it with the inter-
section, we have:

68 DATAMATION

This approach is satisfactory and
might do the job. Its main flaw lies in the
need to discriminate between a spare part
and a piece of eguipment. Terminology, it
turns out, often depends on context, and
something that's called a spare part one
moment might be termed a piece of eguip-
ment the next.

Looking at an automobile, we might
consider the entire engine assembly a spare
part. But if we consider the engine as eguip-
ment, its ignition cabling group (i.e., coil,
distributor, wires) might be termed a spare.
And, while a faulty ignition system (eguip-
ment) is being repaired, a single spark plug
cable (spare part) could be replaced.

If such context-dependent terminol-
OBy were the case in our application, then
treating part-item and eguipment as two
different entities would be a mistake. A less
redundant solution is to have just one
entity and call it eguipment/item as a
compromise.

The situation regarding this two-
headed arrow is precisely the same as in the
prior one. It means we need an intersection
entity. What makes it confusing is that the
same record lies at both ends of the arrow.
But, though the relationships are harder to
visualize, the same rules apply. The result-
ing pattern is so widely used that it has a
name: bill of material.

IN
NUMBERS, It is dangerously easy to
think a conceptual data-
KEYFIELDS, ;
ETC base is designed when, in

fact, major issues are still
unresolved. The topics keyfields, numbers,
and real things comprise a form of checklist
we find useful in deciding whether we are
really finished. They are not new. We have
mentioned all three before and in part l4
We review our prior discussions.
Keyfields—unigue, unambiguous,
unchanging, and dataless. Every entity
should have a keyfield—an identifier that
will tell a person or program which occur-

rence is at hand. Keyfields should be:

" Unigue. Each real-world object or event
should be represented by only one occur-
rence of its entity. Don't have two item rec-
ords for the same part.

" Unambiguous. Each occurrence of an en-
tity should model only one real-world ob-
ject or event. Don't mix light bulbs and
gaskets in a single time record.

» Unchanging. Once they are assigned, an
entity occurrence's keyfields should remain
unchanged.

e Dataless. Keyfields identify. Data fields
describe entity attributes. Don't mix the
two functions.

Numbers— population and volatili-
ty. An easily avoided error in database de-
sign is to neglect numerical analysis. To an
intern, it may seem that the experienced
surgeon takes risks. To an apprentice, the
seasoned engineer may appear to guess at
pressure vessel stress. Similarly, to novice
database designers, veterans can seem to
shortcut numerical analysis of the data. In
all three cases, appearances are deceptive.

A database designer working on his
or her tenth materials management System
might give the illusion of being unaware
that the bill of material is a complete tem-
plate for supply reguisitions, or that main-
tenance work-order volatility is between
50% and 100%. But, like the swan's effort-
less glide, it is an illusion that conceals
frantic paddling beneath the surface. Be-
fore signing off a conceptual design, we
must know every entity's population and
volatility. Until we do, our design is
unfinished.

Real things—objects and events.
Application systems analysis usually begins
by studying the existing system, automated
or not. This is the easiest way to find out
what it's all about. But our database design
would be less than professional if it simply
modeled the existing system. Our goal, af-
ter all, is to model underlying physical
reality.

Every box in our design should rep-
resent an identifiable entity in the real
world. Nonvolatile boxes simulate objects
(or intersection data about pairs of objects).
Volatile boxes model events or happenings
that actually take place. No box should
simply model a record in another data pro-
cessing system, automated or manual.

Concluding a conceptual database
design, we ask ourselves three guestions:

Does every box have a unigue, un-
ambiguous, unchanging dataless keyfield?

Can we produce reasonable popula-
tion and volatility estimates for every box?

Does every box represent either a
real-world object or a real-world event?

Yes? Then we're done. e