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. 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.
News flash!!! Central Zoo now has branches in four cities: Miami (also the Zoo's headquarters), Tucson, Detroit, and Cleveland. The database structure remains the same except that the Location attribute of the ENCLOSURE relation now indicates in which of the four cities the enclosure is located.
Central Zoo has decided to reconfigure its database as a distributed database among its four locations. Distributed and replicated among these four locations, the tables have the following characteristics:
ENCLOSURE consists of 4,000 records and is used most heavily in the cities in which the individual enclosures are located.
ANIMAL consists of 50,000 records. The entire table is used in the Miami headquarters, in addition to which the records concerning individual animals are used frequently in the locations at which the animals are housed.
ZOOKEEPER consists of 1,500 records with a situation similar to the ANIMAL table.
CARES FOR consists of consists of 5,000 records with a situation similar to the ANIMAL table.
SPONSOR consists of 10,000 records and is used mostly at the Miami headquarters.
CONTRIBUTION consists of 30,000 records with a situation similar to the SPONSOR table.
DEPENDENT consists of 90,000 records with a situation similar to the SPONSOR table.
Design a distributed relational database for Central Zoo. Justify your placement, replication, and partitioning of the tables.
$$\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 | } \hline \text { Employee } & \text { Employee } & & \text { Year } \\\text { Number } & \text { Narne } & \text { Title } & \text { Hired } \\\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