Reference no: EM131411802

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';

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.

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	5007

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	5001

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.

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.

- 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.

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.