THE DATABASE MANAGEMENT SYSTEM CONCEPT - Fundamentals of Database Management Systems - page 65

c. Do any fields have to be added to one or the
other of the two files to record the relationship
between customers and orders? Explain.

d. Merge these two files into one, in the style of
Figure 3.6. Does this create any problems with
the data? Explain.

MINICASES

1. Answer the following questions based on the following
Happy Cruise Lines' data.

b. Assume that the records of the Crew Memberfile
are physically stored in the order shown.
i. Retrieve all of the records of the file physically
sequentially.
ii. Retrieve all of the records of the file logically
sequentially based on the Sailor Name field.
iii. Retrieve all of the records of the file logi-
cally sequentially based on the Sailor Number
field.
iv. Retrieve all of the records of the file logi-
cally sequentially based on the Ship Number
field.
v. Perform a direct retrieval of the records with a
Sailor Number field value of 27467.
vi. Perform a direct retrieval of the records with a
Ship Number field value of 020.
vii. Perform a direct retrieval of the records with a
Job Title field value of Captain.
c. The value 009 appears as a ship number once in the
Ship file and four times in the Crew Member file.
Does this constitute data redundancy? Explain.
d. Merge the Ship and Crew Member files based on
the common ship number field (in a manner similar
to Figure 3.8 for the General Hardware database).
Is the merged file an improvement over the two
separate files in terms of:
i. Data redundancy? Explain.
ii. Data integration? Explain.
e. Explain why the Ship Number field is in the Crew
Member file.
f. Explain why ship number 012 appears three times
in the Crew Member file.
g. How many files must be accessed to find:
i. The year that ship number 012 was built?
ii. The home country of sailor number 27941?
iii. The name of the ship on which sailor number
18535 is employed?
h. Describe the procedure for finding the weight of the
ship on which sailor number 00536 is employed.
i. What is the mechanism for recording the one-to-
many relationship between crew members and ships
in the Happy Cruise Lines database above?

(a) Ship table

Ship Ship Year Weight

Number Name Built (Tons)

005 Sea Joy 1999 80,000

009 Ocean IV 2003 75,000

012 Prince Al 2004 90,000

020 Queen Shirley 1999 80,000

(b) Crew Member table

Sailor Sailor Ship Home Job

Number Name Number Country Title

00536 John Smith 009 USA Purser

00732 Ling Chang 012 China Engineer

06988 Maria Gonzalez 020 Mexico Purser

16490 Prashant Kumar 005 India Navigator

18535 Alan Jones 009 UK Cruise Director

20254 Jane Adams 012 USA Captain

23981 Rene Lopez 020 Philippines Captain

27467 Fred Jones 020 UK Waiter

27941 Alain DuMont 009 France Captain

28184 Susan Moore 009 Canada Wine Steward

31775 James Collins 012 USA Waiter

32856 Sarah McLachlan 012 Ireland Cabin Steward

a. Regarding the Happy Cruise Lines Crew Member
file.
i. Describe the file's record type.
ii. Show a record occurrence.
iii. Describe the set or range of values that the Ship
Number field can take.
iv. Describe the set or range of values that the
Home Country field can take.