Page 230 - Using MIS

198 Chapter 5 Database Processing

for the work; the DBMS will assign a unique value to 5-8. Figure 5-30c shows a third alternative data model
WorkID when a new row is added to the Work table. for the sheet-music-tracking problem. In this data
a. Select identifiers for ChoirMember, Work_Version2, model, use either Work_Version2 or Work_Version3,
Work_Version3, and Copy_Assignment. Justify your whichever you think is better.
selection. a. Select identifiers for each entity in your data
b. Does this design eliminate the potential for data model. Justify your selection.
integrity problems that occur in the spreadsheet? b. Summarize the differences between this data
Why or why not? model and that in Figure 5-30b. Which data model
c. Design a database for the data model that uses Work_ is better? Why?
Version2. Specify key and foreign key columns. c. Design a database for this data model. Specify key
d. Design a database for the data models that uses and foreign key columns.
Work_Version3. Specify key and foreign key columns. 5-9. Which of the three data models is the best? Justify
e. Is the design with Work_Version2 better than the your answer.
design for Work_Version3? Why or why not?

CaSe Study 5

Searching for Pianos . . . the late spring, summer, and fall, and during the rest of year,
he repairs and restores pianos.
Dean Petrich is a certified piano tuner and technician who Over the past 20 years, the demand for pianos has dramat-
has been repairing and restoring pianos since 1973. He also ically declined. When Grandma dies, or the kids move out, or
has a career as Deano the Clown, a clown entertainer who some other life change occurs, families have no further use
performs children’s parties in the Seattle, WA, metro area. for their piano, and when they find there is no market for it,
(See Figure 5-31, http://deanotheclown.com). The schedule of they call Dean, who picks up that piano for a modest fee. For
his two businesses balance each other: He’s busy as a clown in a number of years, Dean restored those pianos and either
resold or rented them. Since the turn of the century, however,
the decreasing demand for pianos has affected him as well,
and over time, he’s accumulated far too many pianos. Even
discarding the worst of them, he has, today, nearly 100.
As you can imagine, 100 pianos consume considerable
storage. At first, Dean stored them in his workshop. When
he ran out of room in his workshop, he built and stored
them in a large metal shed (Figure 5-32). When the shed
overflowed with pianos, he moved them to plastic tents
in a meadow on his property (Figure 5-33). Unfortunately,
the plastic tents are prone to rips and tears, and because
Dean lives in the Pacific Northwest, many pianos have been
ruined by rain, even when he covers them with plastic tarps
inside the plastic tents.
Two years ago, sinking in his steadily increasing piano in-
ventory, Dean began to offer pianos for free. Not the very best
pianos—those he hopes to sell—but he offers many quality
pianos for free. However, Dean has two problems. First, he
doesn’t know which pianos are best and where they are lo-
cated in the shop, shed, or tents. Second, few people are will-
Figure 5-31 ing to crawl over the tops of the pianos in the large shed and
Deano the Clown tents (through refuse of squirrels, rats, and mice) looking for
Source: Dean Petrich their perfect piano.