Chapter 7 Logical Database Design
7-12
29. Converting from first to second normal form involves subdividing tables to remove
transitive dependencies.
30. In third normal form, all foreign keys appear where needed to logically tie together
related tables.
Continue to the next page for problems.
Chapter 7 Logical Database Design
7-13
Problems
1. Convert the following Central Zoo entity-relationship diagram into a relational database.
ANIMAL
PK Animal Number
Species
Animal Name
Gender
Country of Birth
Weight
CARE
PK Animal Number
PK Employee Number
PK Date
Service Type
Time
ENCLOSURE
PK Enclosure Number
Type
Location
Size
Date Built
SPONSORING
PK Animal Number
PK Sponsor Number
Annual Contribution
Renewal Date
SPONSOR
PK Sponsor Number
Sponsor Name
Address
Telephone
ZOOKEEPER
PK Employee Number
Employee Name
Title
Year Hired
DEPENDENT
PK Sponsor Number
PK Dependent Name
Relationship
Date of Birth
Lives in
Contains
Sponsored by
Sponsors
Sponsors
Sponsored by
Supports
Supported by
Cared
Cared by
Cared by
Cared
Supervises
Supervised by
Chapter 7 Logical Database Design
7-14
Answer
7-15
2. Convert the following Grand Travel Airline entity-relationship diagram into a relational
database.
FLIGHT
PK Flight Number
PK Date
Departure Time
Arrival Time
AIRPLANE
PK Serial Number
Model
Passenger Capacity
Year Built
Manufacturer
MAINTENANCE LOCATION
PK Location Name
Address
Telephone Number
Manager
PILOT
PK Pilot Number
Pilot Name
Date of Birth
Date of Hire
TRIP
PK Flight Number
PK Date
PK Passenger Number
Fare
Reservation Date
PASSENGER
PK Passenger Number
Passenger Name
Address
Telephone Number
Flew
Flown by
Used
Used by
Located
Located at
Used
Used by
Taken
Taken by
SERVICE
PK Serial Number
PK Procedure Number
PK Location Name
PK Date
Duration
MAINTENANCE PROCEDURE
PK Procedure Number
Procedure Name
Frequency
Used
Used by
Serviced
Serviced at
Chapter 7 Logical Database Design
7-16
3. Central Zoo wants to maintain information about its animals, the enclosures in
which they live, and its zookeepers and the services they perform for the animals. In
addition, Central Zoo has a program by which people can be sponsor of animals.
Central Zoo wants to track its sponsors, their dependents, and associated data.
Each animal has a unique animal number, species, name (Jumbo, Fred, etc.), gender,
country of birth, and weight. Enclosures have a unique enclosure number, type
Chapter 7 Logical Database Design
7-17
(cage, fenced field, etc.), location, size, and date built. An animal can live in only
one enclosure. An enclosure can have several animals in it or it can be currently
empty. A zookeeper has a unique employee number, employee name, title, and year
hired. Some zookeepers supervise other zookeepers. Every animal has been cared
for by at least one and generally many zookeepers; each zookeeper has cared for at
least one and generally many animals. Each time a zookeeper performs a specific,
significant service for an animal the service type, date, and time are recorded.
A sponsor sponsors at least one and possibly several animals. An animal may have
several sponsors or none. A sponsor has a unique sponsor number, a name, address,
and telephone number. For each animal that a particular sponsor sponsors, the zoo
wants to track the annual sponsorship contribution and renewal date. In addition,
Central Zoo wants to keep track of each sponsor’s dependents, keeping their name,
relationship to the sponsor, and date of birth. A sponsor may have several
dependents or none. A dependent is associated with exactly one sponsor.
Attributes
Enclosure Number
Type
Location
Size
Date Built
Animal Number
Species
Animal Name
Gender
Country of Birth
Weight
Sponsor Number
Sponsor Name
Address
Telephone
Annual Contribution
Renewal Date
Employee Number
Employee Name
Title
Year Hired
Service Type
Date
Time
Name
Relationship
Date of Birth
Chapter 7 Logical Database Design
7-18
Functional Dependencies
Enclosure Number Type
Enclosure Number Location
Enclosure Number Size
Enclosure Number Date Built
Animal Number Species
Animal Number Animal Name
Animal Number Gender
Animal Number Country of Birth
Animal Number Weight
Animal Number Enclosure Number
Sponsor Number Sponsor Name
Sponsor Number Address
Sponsor Number Telephone
Sponsor Number, Name Relationship
Sponsor Number, Name Date of Birth
Animal Number, Sponsor Number Annual Contribution
Animal Number, Sponsor Number Renewal Date
Employee Number Employee Name
Employee Number Title
Employee Number Year Hired
Employee Number Supervisor Number
Animal Number, Employee Number, Service Type, Date, Time Null
Design a well-structured relational database for this Super Baseball League
environment using the data normalization technique. Progress from first to
second normal form and then from second to third normal form justifying your
design decisions at each step based on the rules of data normalization.
Answer
Chapter 7 Logical Database Design
7-19
4. Grand Travel Airlines has to keep track of its flight and airplane history. A flight
is uniquely identified by the combination of a flight number and a date. In
addition, every flight has an actual departure time and an actual arrival time.
Every passenger who has flown on Grand Travel has a unique passenger number
plus their name, address, and telephone number. For a particular passenger who
has taken a particular flight, the company wants to keep track of the fare that she
paid for it and the date that she made the reservation for it. Clearly, a passenger
may have taken many flights (he must have taken at least one to be in the
database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number, a name, date of birth,
and date of hire. A flight on a particular date has exactly one pilot. Each pilot has
typically flown many flights but a pilot may be new to the company, is in training,
and has not flown any flights, yet.
Each airplane has a unique serial number, a model, manufacturer name, passenger
capacity, and year built. A flight on a particular date used one airplane. Each
airplane has flown on many flights and dates, but a new airplane may not have
been used at all, yet.
Grand Travel also wants to maintain data about its airplanes’ maintenance history.
A maintenance procedure has a unique procedure number, a procedure name, and
the frequency with which it is to be performed on every airplane. A maintenance
location has a unique location name, plus an address, telephone number, and
manager. Grand Travel wants to keep track of which airplane had which
maintenance procedure performed at which location. For each such event it wants
to know the date of the event and the duration.
Chapter 7 Logical Database Design
7-20
Attributes
Pilot Number
Pilot Name
Date of Birth
Date of Hire
Flight Number
Date (of flight)
Departure Time
Arrival Time
Passenger Number
Passenger Name
Address
Telephone Number (of passenger)
Fare
Reservation Date
Serial Number
Model
Passenger Capacity
Year Built
Manufacturer
Procedure Number
Procedure Name
Frequency
Location Name
Address
Telephone Number (of maintenance location)
Manager
Date (of maintenance)
Duration
Chapter 7 Logical Database Design
7-21
Functional Dependencies
Pilot Number Pilot Name
Pilot Number Date of Birth
Pilot Number Date of Hire
Flight Number, Date (of flight) Departure Time
Flight Number, Date (of flight) Arrival Time
Flight Number, Date (of flight) Pilot Number
Flight Number, Date (of flight) Serial Number
Passenger Number Passenger Name
Passenger Number Address (of passenger)
Passenger Number Telephone Number
Flight Number, Date, Passenger Number Fare
Flight Number, Date, Passenger Number Reservation Date
Serial Number Model
Serial Number Passenger Capacity
Serial Number Year Built
Serial Number Manufacturer
Procedure Number Procedure Name
Procedure Number Frequency
Location Name Address (of maintenance location)
Location Name Telephone Number
Location Name Manager
Serial Number, Procedure Number, Location Name, Date Duration
For each of the following tables, first write the table’s current normal form (as 1NF,
2NF, or 3NF.) Then, for those tables that are currently in 1NF or 2NF, reconstruct
them as well-structured 3NF tables. Primary key attributes are underlined. Do not
assume any functional dependencies other than those shown.
a. Flight Number, Date (of flight), Passenger Number, Departure Time, Arrival
Time, Passenger Name
b. Flight Number, Date (of flight), Passenger Number, Fare, Reservation Date
c. Flight Number, Date (of flight), Serial Number, Departure Time, Arrival
Time, Model, Passenger Capacity
d. Pilot Number, Pilot Name, Date of Birth, Date of Hire
e. Serial Number, Procedure Number, Location Name, Date (of maintenance),
Duration, Year Built, Manufacturer, Procedure Name, Frequency
