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.
$$\begin{array} { | l | l | l | l | l | }
\hline \text { ENCLNUM } & \text { TYPE } & \text { LOCATION } & \text { SIZE } & \text { DATEBULT } \
\hline
\end{array}$$ ENCLOSURE Relation
$$\begin{array} { | l | l | l | l | l | }
\hline \text { ANIMALNUM } & \text { SPECES } & \text { ANIMALNAME } & \text { GENDER } & \text { COUNTRY } \
\hline
\end{array}$$ $$\begin{array} { | l | l | }
\hline \text { WEIGHT}&\text { ENCLNUM } \
\hline
\end{array}$$ ANIMAL Relation
$$\begin{array} { | l | l | l | l | }
\hline \text { EMPNUM } & \text { EMPNAME } & \text { TITLE } & \text { YERHIRED } \
\hline
\end{array}$$ ZOOKEEPER Relation
$$\begin{array} { | l | l | l | l | l | }
\hline \text { ANIMALNUM } & \text { EMPNUM } & \text { SERVICETYPE } & \text { DATE } & \text { TIME }\
\hline
\end{array}$$ CARESFOR Relation
$$\begin{array} { | l | l | l | l | l | }
\hline \text { SPONSORNUM } & \text { SOCSECNUM } & \text { SPONSORNAME } & \text { ADDRESS } & \text { PHONE }\
\hline
\end{array}$$ SPONSOR Relation
$$\begin{array} { | l | l | l | l | }
\hline \text { ANIMALNUM } & \text { SPONSORNUM } & \text { ANNUALCONT } & \text { RENEWDATE } \
\hline
\end{array}$$ CONTRIBUTION Relation
$$\begin{array} { | l | l | l | l| }
\hline \text { SPONSORNUM } & \text { DEPENNAME } & \text { RELATIONSHIP } & \text { BIRTHDATE }\
\hline
\end{array}$$ DEPENDENT Relation
Write SQL SELECT commands to answer the following queries.
a. Find the size of enclosure number 2582.
b. List the names and numbers of the female tigers who live in enclosure number 4627.
c. List the animal number, species, gender, and weight of all of the lions, tigers, and leopards that weigh between 700 and 900 pounds, from heaviest to lightest.
d. Find the average weight of all of the male tigers.
e. Find the average weight of the males of each species.
f. List the names, addresses, and phone numbers of all of the sponsors of tigers that were born in India.
g. How many monkeys live in cages that are less than 400 square feet in size?
h. Find the average weight of each species of animal that that live in cages that are less than 400 square feet in size. Only include those species for which the average weight is at least 200 pounds.
i. Find the name and number of the heaviest tiger.
j. Find the name, number, and species of the animals from China that are at least as heavy as the heaviest female tiger that was born in India.

Question

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.
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { ENCLNUM } & \text { TYPE } & \text { LOCATION } & \text { SIZE } & \text { DATEBULT } \
\hline
\end{array}$$ ENCLOSURE Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { ANIMALNUM } & \text { SPECES } & \text { ANIMALNAME } & \text { GENDER } & \text { COUNTRY } \
\hline
\end{array}$$ $$\begin{array} { | l | l | } 
\hline \text { WEIGHT}&\text { ENCLNUM } \
\hline
\end{array}$$ ANIMAL Relation
 $$\begin{array} { | l | l | l | l | } 
\hline \text { EMPNUM } & \text { EMPNAME } & \text { TITLE } & \text { YERHIRED } \
\hline
\end{array}$$ ZOOKEEPER Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { ANIMALNUM } & \text { EMPNUM } & \text { SERVICETYPE } & \text { DATE } & \text { TIME }\
\hline
\end{array}$$ CARESFOR Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { SPONSORNUM } & \text { SOCSECNUM } & \text { SPONSORNAME } & \text { ADDRESS } & \text { PHONE }\
\hline
\end{array}$$ SPONSOR Relation
 $$\begin{array} { | l | l | l | l | } 
\hline \text { ANIMALNUM } & \text { SPONSORNUM } & \text { ANNUALCONT } & \text { RENEWDATE } \
\hline
\end{array}$$ CONTRIBUTION Relation
 $$\begin{array} { | l | l | l | l| } 
\hline \text { SPONSORNUM } & \text { DEPENNAME } & \text { RELATIONSHIP } & \text { BIRTHDATE }\
\hline
\end{array}$$ DEPENDENT Relation
Write SQL SELECT commands to answer the following queries. 
a. Find the size of enclosure number 2582.
b. List the names and numbers of the female tigers who live in enclosure number 4627.
c. List the animal number, species, gender, and weight of all of the lions, tigers, and leopards that weigh between 700 and 900 pounds, from heaviest to lightest.
d. Find the average weight of all of the male tigers.
e. Find the average weight of the males of each species.
f. List the names, addresses, and phone numbers of all of the sponsors of tigers that were born in India.
g. How many monkeys live in cages that are less than 400 square feet in size?
h. Find the average weight of each species of animal that that live in cages that are less than 400 square feet in size. Only include those species for which the average weight is at least 200 pounds.
i. Find the name and number of the heaviest tiger.
j. Find the name, number, and species of the animals from China that are at least as heavy as the heaviest female tiger that was born in India.

Quizplus · Accepted Answer

The Answer of 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.
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { ENCLNUM } & \text { TYPE } & \text { LOCATION } & \text { SIZE } & \text { DATEBULT } \
\hline
\end{array}$$ ENCLOSURE Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { ANIMALNUM } & \text { SPECES } & \text { ANIMALNAME } & \text { GENDER } & \text { COUNTRY } \
\hline
\end{array}$$ $$\begin{array} { | l | l | } 
\hline \text { WEIGHT}&\text { ENCLNUM } \
\hline
\end{array}$$ ANIMAL Relation
 $$\begin{array} { | l | l | l | l | } 
\hline \text { EMPNUM } & \text { EMPNAME } & \text { TITLE } & \text { YERHIRED } \
\hline
\end{array}$$ ZOOKEEPER Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { ANIMALNUM } & \text { EMPNUM } & \text { SERVICETYPE } & \text { DATE } & \text { TIME }\
\hline
\end{array}$$ CARESFOR Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { SPONSORNUM } & \text { SOCSECNUM } & \text { SPONSORNAME } & \text { ADDRESS } & \text { PHONE }\
\hline
\end{array}$$ SPONSOR Relation
 $$\begin{array} { | l | l | l | l | } 
\hline \text { ANIMALNUM } & \text { SPONSORNUM } & \text { ANNUALCONT } & \text { RENEWDATE } \
\hline
\end{array}$$ CONTRIBUTION Relation
 $$\begin{array} { | l | l | l | l| } 
\hline \text { SPONSORNUM } & \text { DEPENNAME } & \text { RELATIONSHIP } & \text { BIRTHDATE }\
\hline
\end{array}$$ DEPENDENT Relation
Write SQL SELECT commands to answer the following queries. 
a. Find the size of enclosure number 2582.
b. List the names and numbers of the female tigers who live in enclosure number 4627.
c. List the animal number, species, gender, and weight of all of the lions, tigers, and leopards that weigh between 700 and 900 pounds, from heaviest to lightest.
d. Find the average weight of all of the male tigers.
e. Find the average weight of the males of each species.
f. List the names, addresses, and phone numbers of all of the sponsors of tigers that were born in India.
g. How many monkeys live in cages that are less than 400 square feet in size?
h. Find the average weight of each species of animal that that live in cages that are less than 400 square feet in size. Only include those species for which the average weight is at least 200 pounds.
i. Find the name and number of the heaviest tiger.
j. Find the name, number, and species of the animals from China that are at least as heavy as the heaviest female tiger that was born in India.

Consider the Following Relational Database for the Central Zoo ENCLOSURE Relation