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:

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:

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:

Salesman:

Order:

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.