blem 1. 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, 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. (See entities below) \begin{tabular}{|l|l|l|l|l|} \hline ENCINUMINTEGER & TVPE & LOCATION & SIZE & DATEBUILT \\ VARCHAR(SO) & VARCHAR(SO) & INTEGER & DATE \\ \hline \end{tabular} ENCLOSURE Relation \begin{tabular}{|l|l|l|l|l|l|l|} \hline ANIMALNUM & SPECIES & ANIMALNAME & GENDER & COUNTRY & WEIGHT & ENCLNUM \\ INTEGER & VARCHAR(50) & VARCHAR(50) & CHAR(1) & VARCHAR(50) & INTEGER & VARCHAR(4) \\ \hline \end{tabular} ANIMAL Relation \begin{tabular}{|l|l|l|l|} \hline EMPNUM & EMPNAME & TITLE & YEARHIRED \\ INTEGER & VARCHAR(255) & VARCHAR(50) & CHAR(4) \\ \hline \end{tabular} ZOOKEEPER Relation \begin{tabular}{|l|l|l|l|l|} \hline ANIMALNUMINTEGER & EMPNUM & SERVICETYPE & DATE & TIME \\ INTEGER & VARCHAR(50) & DATE & TIME \\ \hline \end{tabular} CARESFOR Relation \begin{tabular}{|l|l|l|l|l|} \hline SPONSORNUMINTEGER & SOCSECNUMCHAR(8) & SPONSORNAME & ADDRESS & PHONE \\ VARCHAR(255) & VARCHAR(255) & CHAR(12) \\ \hline \end{tabular} SPONSOR Relation \begin{tabular}{|l|l|l|l|} \hline ANIMALNUM & SPONSORNUM & ANNUALCONT & RENEWDATE \\ INTEGER & INTEGER & FLOAT (8,2) & DATE \\ \hline \end{tabular} CONTRIBUTION Relation \begin{tabular}{|l|l|l|l|} \hline SPONSORNUM & DEPENNAME & RELATIONSHIP & BIRTHDATE \\ INTEGER & VARCHAR(255) & VARCHAR(50) & DATE \\ \hline \end{tabular} DEPENDENT Relation Execute the appropriate SQL statement(s). Provide a screenshot of both the SQL statement(s) and final output in your assignment submission for the following: a. Create a database called "centralzoo" in the MySQL database you installed. b. Create tables for the entities previously listed above using the data types provided (i.e., ENCLOLSURE Relation, ANIMAL Relation, etc.) c. Add the data provided from the files below into the appropriate tables using INSERT statements (NOTE: All enclosures are in Georgia and construction was completed on September 13, 1999.). d. Add the Enclosure Number from the file below to the ANIMAL table using UPDATE statements: e. Add the data provided in the attached Excel files (i.e., SPONSOR, ZOOKEEPER, and DEPENDENT) using either INSERT statements or the LOAD DATA statement. (HINT: Refer to the MySQL Tutorial for information on the LOAD DATA statement. All data will need to be saved from Microsoft Excel to a CSV file. For creating INSERT statements, in Microsoft Excel, you can create a formula that builds the INSERT statement and copy it to each row.)