Write a query to create the zoo database. Write queries to create the tables. Be sure to include primary keys for each table and the required foreign keys and referential ntegrity actions. Add approriate indexes. This database requires at least 2 indexes in addition to those automatically created by MySQL. Add at least 5 rows to each tabl

Note: Here, we can't able to share SQL script files to execute directly on the database. Bartleby is only allowed to share image files. Therefore I have explained/shared the SQL query here, you can run it directly on MySQL. Description: As mentioned in the assignment, created both tables, please check the following SQL query one by one. Write a query to create the zoo database. Query:- CREATE DATABASE `zoo` ;2. Write queries to create the tables. Be sure to include primary keys for each table and the required foreign keys and referential integrity actions. Query:- animal table:- CREATE TABLE `animal` ( `animalId` int(50) NOT NULL AUTO_INCREMENT, `animalName` varchar(50) DEFAULT NULL, `animalBirthdate` date DEFAULT NULL, `species` varchar(50) DEFAULT NULL, `habiat` int(50) DEFAULT NULL, PRIMARY KEY (`animalId`), KEY `habiat` (`habiat`), KEY `idx_animalName` (`animalName`), CONSTRAINT `animal_ibfk_1` FOREIGN KEY (`habiat`) REFERENCES `habitats` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; habitats table:- CREATE TABLE `habitats` ( `id` int(50) NOT NULL AUTO_INCREMENT, `habitatName` varchar(50) DEFAULT NULL, `habitatLocation` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_habitatName` (`habitatName`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; 3. Add approriate indexes. This database requires at least 2 indexes in addition to those automatically created by MySQL. Query:- index-1 CREATE INDEX idx_animalNameON `animal` (`animalName`); index-2 CREATE INDEX idx_habitatNameON `habitats` (`habitatName`); 4. Add at least 5 rows to each table. Query:- For animals: insert into `animal`(`animalId`,`animalName`,`animalBirthdate`,`species`,`habiat`) values (1,'dog','2022-10-11','catus',1),(2,'cat','2022-09-21','sapiens',2),(3,'rabit','2022-05-24','catus',3),(4,'rat','2022-07-21','sapiens',4),(5,'lion','2022-02-03','catus',5); For habitats: insert into `habitats`(`id`,`habitatName`,`habitatLocation`) values (1,'Grasslands','Florida'),(2,'Deserts','Mumbai'),(3,'Forests','New york'),(4,'Wetlands','Lucknow'),(5,'Grasslands','Kurla'); Output/Screenshot: ------------Description: 1- Create statement is used to create the database also user can create the table. 2- Insert statement is used to insert the records in the database.3- Select statement is used to display the records from the database.4- As mentioned in the assignment, create two indexes. 5- First index for animal table and second index for habitats table.

Answered: Write a query to create the zoo database. Write queries to create the tables. Be sure to include primary keys for each table and the required foreign keys and…

Database System Concepts

7th Edition

ISBN: 9780078022159

Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher: McGraw-Hill Education

See similar textbooks

Concept explainers

SQL Query

Structured Query Language is the full form of SQL. It is one of the standard languages to deal with the relational database. It is used to insert, update, delete, and search the records stored in a RDBMS. The relational databases are created and managed …

Question

You've been asked to create a database for a local zoo. The database consists of the following tables:

Animals

This table contains the following data about all animals in the zoo.

animal ID
animal name
animal birthdate
species
habitat

Habitats

This table contains data about the habitats in the zoo. Each animal lives in a specific habitat. The habitat table should include the following data

habitat name
habitat location

Exercises

Complete the following exercises to create an executable SQL script which is the physical design for this database. You should be able to execute this script to create and populate the database with test data.

Write a query to create the zoo database.
Write queries to create the tables. Be sure to include primary keys for each table and the required foreign keys and referential ntegrity actions.
Add approriate indexes. This database requires at least 2 indexes in addition to those automatically created by MySQL.
Add at least 5 rows to each table.

Expert Solution