ASSIGNMENT-2 (Due by 4th March) Instructions: • Answers to questions 1,2 have to be executed in the MySQL terminal. Take a screenshot of the output and paste it in your assignment answer sheet. • Do not upload a word document to the shell. Your answers should be in a pdf document. • Make sure all your screenshots and answers are clear. Unclear answers will not be evaluated. 1. Execute the following commands in MySQL terminal CREATE TABLE hiking ( trail CHAR (50), area CHAR (50), distance FLOAT, est_time FLOAT); SHOW TABLES; SHOW COLUMNS FROM hiking: INSERT INTO hiking VALUES ('Cedar Creek Falls', 'Upper San Diego',4.5, 2.5); INSERT INTO hiking (trail, area) VALUES ('East Mesa Loop', 'Cuyamaca Mountains' ); SELECT * FROM hiking UPDATE hiking SET distance = 10.5, est_time = 5.5 WHERE trail = 'East Mesa Loop'; DELETE FROM hiking WHERE trail = 'Cedar Creek Falls';2 a) Give the SQL statements to insert the following values into the hiking table: trail area distance est_time East Mesa Loop Cuyamaca Mountains 10.50 5.50 Oak Canyon NULL 3.00 NULL b) Give the SQL statement(s) to update the entry for the 'Oak Canyon' trail. Set the area to 'Mission Trails Regional Park' and the estimated time (est_time) to 2 hours.Your table should then look like the following: trail area distance est_time East Mesa Loop Cuyamaca Mountains 10.50 5.50 Oak Canyon Mission Trails Regional Park 3.00 2.00 c) Give the SQL statement to delete trails with a distance greater than 5 miles. d) Give the SQL statement to create a table called 'rating'. This table rates the difficulty of a hiking trail. It will have two columns: the trail name, 'trail' and the difficulty, 'difficulty'. The tail name is a string of no more than 50 characters and the difficulty is an integer (INT).
 e) What is the command to delete the rating table? f) Give the command to add another column to the hiking table called 'trail id' with Primary key constraint. Add another column called 'trail id' in the 'rating' table, which should be the foreign key with the table referring to the hiking table.3 2. Create a database 'Hollywood' and create the below tables with the constraints listed below: Movie(mID int, title text, year int, director text); Reviewer(rID int, name text); Rating(rID int, mID int, stars int, ratingDate date); Enforce the following constraints on the above database: • Movie and Reviewer should have primary key constraints on the respective id columns. • Place auto increment on the mID and rID columns in the Movie and Reviewer tables • Rating table columns 'rID' and 'mID' should refer to the respective columns in the parent tables i.e. Movie and Reviewer. • The default value of the 'ratingDate' column in the Rating table should be the current date. • The 'year' column in the Movie table should not be greater than 2016. 3. Consider the sample tables below: Customer: customer_id cust_name city grade salesman_id 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 5005 3004 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moscow 200 50074 Salesman: salesman_id name city commission 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen 0.12 5007 Paul Adam Rome 0.13 Order: Order_No Purch_Amt Ord_Date Customer_id salesman_id 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 50015 Answer the following questions based on the above tables: a) Write an SQL statement to prepare a list with salesman name, customer name and their cities for the salesmen and customer who belongs to same city. b) Write an SQL statement to make a list with order no, purchase amount, customer name and their cities for those orders which order amount between 500 and 2000. c) Write an SQL statement to know which salesman are working for which customer. d) Write an SQL statement to find the list of customers who appointed a salesman for their jobs who gets a commission from the company is more than 12% e) Write an SQL statement to find the list of customers who appointed a salesman for their jobs who does not live in same city where the customer lives, and gets a commission is above 12% f) Write an SQL statement to find the details of an order i.e. order number, order date, amount of order, which customer gives the order and which salesman works for that customer and how much commission he gets for an order. g) Write an SQL statement to make a join within the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.6 4. Consider an AIRLINE relational database schema shown below, which describes a database for airline flight information. Consider an update for the AIRLINE database to enter a reservation on a particular flight or flight leg on a given date.7 • Each FLIGHT is identified by a Flight_number, and consists of one or more FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on. • Each FLIGHT_LEG has scheduled arrival and departure times, airports, and one or more LEG_INSTANCEs—one for each Date on which the flight travels. FAREs are kept for each FLIGHT. • For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as are the AIRPLANE used on the leg and the actual arrival and departure times and airports. • An AIRPLANE is identified by an Airplane_id and is of a particular AIRPLANE_TYPE. • CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs at which they can land. • An AIRPORT is identified by an Airport_code. a. Give the operations for this update. b. What types of constraints would you expect to check? c. Which of these constraints are key, entity integrity, and referential integrity constraints, and which are not? d. Specify all the referential integrity constraints that hold on the schema shown below.8 5. Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT(Ssn, Name, Major, Bdate) COURSE(Course#, Cname, Dept) ENROLL(Ssn, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_isbn) TEXT(Book_isbn, Book_title, Publisher, Author) Specify the foreign keys for this schema.