Description
Question 1 25 Points
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:
1. Movie and Reviewer should have primary key constraints on the respective id columns. (5)
2. Place auto increment on the ’mID’ and ’rID’ columns in the Movie and Reviewer tables. (5)
3. Rating table columns ’rID’ and ’mID’ should refer to the respective columns in the parent tables
i.e. Movie and Reviewer. (5)
4. The default value of the ’ratingDate’ column in the Rating table should be the current date. (5)
5. The ’year’ column in the Movie table should not be greater than 2016. (5)
Course: CIND 110 Winter 2021
Assignment 2
2
Question 2 20 Points
Execute the following script first and then work on questions given below.
DROP DATABASE IF EXISTS cind110A2Script1;
CREATE SCHEMA cind110A2Script1;
USE cind110A2Script1;
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;
SET SQL SAFE UPDATES = 0;
UPDATE hiking
SET distance = 10.5, est time = 5.5
WHERE trail = ‘East Mesa Loop’;
USE cind110A2Script1;
DELETE FROM hiking WHERE trail = ‘Cedar Creek Falls’;
SELECT ∗ FROM hiking;
Assignment 2 Course: CIND 110 Winter 2021
3
1. Write the SQL statements to insert the following values into the hiking table: (3)
trail area distance est time
East Mesa Loop Cuyamaca Mountains 10.50 10.50
Oak Canyon NULL 3.00 NULL
2. Write the SQL statements 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: (5)
trail area distance est time
East Mesa Loop Cuyamaca Mountains 10.50 10.50
Oak Canyon Mission Trails Regional Park 3.00 2.00
3. Write the SQL statement to delete trails with a distance greater than 5 miles. (2)
4. Write 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). (3)
5. Write the command to add another column to the hiking table called ’trailID’ with Primary key
constraint. (2)
6. Add another column called ’trailID’ in the ’rating’ table, which should be the foreign key with the
table referring to the hiking table. (3)
7. What is the command to delete the rating table? (2)
Assignment 2 Course: CIND 110 Winter 2021
4
Question 3 40 Points
Consider the following tables for Customer, Salesman and Order entities.
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 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 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 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
Assignment 2 Course: CIND 110 Winter 2021
5
Instruction:
You are asked to provide the correct SQL scripts for the following questions. You may execute your
script using terminal or MySQL workbench to verify your answers. Provide screenshots of only the
outputs along with your SQL scripts.
1. Write an SQL statement to prepare a list with salesman name, customer name and their cities for
the salesmen and customer who belong to same city. (5)
2. Write an SQL statement to make a list with order no, purchase amount, customer name and their
cities for the orders where order amount is between 500 and 2000. (5)
3. Write an SQL statement to find out which salesmen are working for which customer. (5)
4. Write an SQL statement to find the list of customers who appointed a salesman for their jobs whose
commission is more than 12%. (6)
5. 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 above 12%. (6)
6. 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. (8)
7. Write an SQL statement to make a join within the tables salesman, customer and orders such that
the same column of each table will appear once and only the related rows will be returned. (5)
Question 4 15 Points
Consider the following Relations for a database that keeps track of student enrollment in courses and
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)
Having that a Relation can have zero or more Foreign keys and each Foreign key can refer to different
referenced Relations. Specify all possible Foreign keys for this schema.