Consider the Following Relational Database for the Central Zoo ENCLOSURE Relation

Essay

Consider the following relational database for the Central Zoo. 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 and each enclosure has a unique enclosure number. 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. 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 zookeeper may perform a particular service on a particular animal more than once on a given day.
A sponsor, who has a unique sponsor number and a unique social security number, sponsors at least one and possibly several animals. An animal may have several sponsors or none. 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. A sponsor may have several dependents or none. A dependent is associated with exactly one sponsor.
$$\begin{array} { | l | l | l | l | l |} \hline \text { Enclosure } & & & & \text { Date } \\\text { Number } & \text { Type } & \text { Location } & \text { Size } & \text { Built }\\\hline\end{array}$$ ENCLOSURE Relation

$$\begin{array} { | l | l | l | l | l | l | l | } \hline \text { Animal } & & \text { Animal } & & \text { Country } &&\text { Enclosure }\\\text { Number } & \text { Species } & \text { Name } & \text { Gender } & \text { Of Birth }&\text { Weight }&\text { Number }\\\hline\end{array}$$ ANIMAL Relation

$$\begin{array} { | l | l | l | l | l | } \hline \text { Employee } & \text { Employee } & & \text { Year } & \text { Supervisor } \\\text { Number } & \text { Narne } & \text { Title } & \text { Hired } & \text { Number } \\\hline\end{array}$$ ZOOKEEPER Relation
$$\begin{array} { | l | l | l | l | l |} \hline\text { Animal } & \text { Employee } & \text { Service } & & \\\text { Number } & \text { Number } & \text { Type } & \text { Date } & \text { Time }\\\hline\end{array}$$ CARES FOR Relation


$\begin{array}{|l|l|l|l|l|}\hline \text { Sponsor } & \text { Social Security } & \text { Sponsor } & & \\\text { Number } & \text { Number } & \text { Name } & \text { Address } & \text { Telephone }\\\hline \end{array}$ SPONSOR Relation

$$\begin{array} { | l | l | l | l | } \hline \text { Animal } & \text { Sponsor } & \text { Annual } & \text { Renewal } \\\text { Number } & \text { Number } & \text { Contribution } & \text { Date }\\\hline\end{array}$$ CONTRIBUTION Relation

$$\begin{array} { | l | l | l | l | } \hline \text { Sponsor } & \text { Dependent } & & \text { Date of } \\\text { Number } & \text { Name } & \text { Relationship } & \text { Birth }\\\hline\end{array}$$ DEPENDENT Relation

a. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves as part of the primary key of the relation in which it is a foreign key. Why does each of those relations require a multi-attribute primary key?
f. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data.
g. Using the informal relational command language described in this chapter, write commands to:
i. Retrieve the record for animal number 58560.
ii. Retrieve the record for the tiger named Stripes.
iii. List all of the tigers born in India.
iv. List the name and animal number of every animal born in China that weighs less than 100 pounds .
v. List the name and number of every tiger.
vi. What is the country of birth of animal number 74371?
vii. What is the type and size of the enclosure used for animal number 74371?
viii. List the name and address of every sponsor of animal number 74371.