ISM 6217 Assignment 1
Entity-Relationship Modeling (10 points)
Draw entity-relationship diagrams (ERD) to best represent the following two business scenarios. The ERDs
can be drawn by hand or done using a software program such as Microsoft Visio or Powerpoint.
1. 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 (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.
2. 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.
