University of California, Santa Cruz CMPS Database Systems I CSE 180, Final Exam, Winter 2020, Final CSE180 W20 LongAnswers

Document Content and Description Below

CSE 180, Final Exam, Winter 2020, Shel Finkelstein Final Points The first Section (Part I) of the Winter 2020 CSE 180 Final is multiple choice and is double-sided. Answer all multiple choice questio... ns on your Scantron sheet. You do not have to hand in the first Section of the Exam, but you must hand in the Scantron sheet, with your Name and Student ID filled in (including marking bubbles below) on that Scantron sheet. Please be sure to use a #2 pencil to mark your choices on that Section of the Final. This separate second Section (Parts II and III) of the Final is not multiple choice and is single-sided, so that you have extra space to write your answers. If you use that extra space, please be sure to write the number of the problem that you’re solving next to your answer. Please write your Name, Email and Student ID on this second Section of the Exam, which you must hand in. You may use any writing implement on this Section of the Exam. At the end of the Final, please be sure to hand in both your Scantron sheet for the first Section of the Exam and also this second Section of the Exam. You must also show your UCSC id when you hand them in. Part II: (24 points, 6 points each) Question 22: Here are statements creating two tables R and S: CREATE TABLE R ( a INT, b INT, PRIMARY KEY(a), FOREIGN KEY(b) REFERENCES S ON DELETE CASCADE, ON UPDATE CASCADE ); CREATE TABLE S ( b INT, c INT, PRIMARY KEY(b) ); Table R(a,b) currently contains the four tuples, (0,4), (1,5), (2,4), and (3,5). Table S(b,c) currently contains the four tuples, (2,10), (3,11), (4,12), and (5,14). Indicate all changes that happen to both R and S when the following SQL statements are executed upon the R and S instances that are shown above. That is, you should ignore changes made by earlier parts of this question when you answer later parts. If a statement changes existing tuples, show both old and new values of those tuples. 22a): UPDATE S SET b = 9 WHERE c = 14; Answer 22a): Changes made to the original 22b): UPDATE R set b = 7 WHERE a = 0; Answer 22b): Changes made to the original R: Changes made to the original S: 22c): DELETE FROM S WHERE b = 5; Answer 22c): Changes made to the original R: (1,5) and (3,5) are deleted Changes made to the original S: (5,14) is deleted Question 23: For the relation Employees(name, age, salary), write a Serializable SQL transaction that executes the following three SQL statements and then commits. 1. The transaction should insert an employee whose salary is 9000, whose age is 21, and whose name is John Smith. 2. After that, the transaction should update the salary of every employee that has the lowest salary value, adding 1000 to their salary. 3. Then the transaction should delete all employees who make more than the average salary and whose age is more than 70. Answer 23: Question 24: Codd’s relational algebra for sets included only 5 operators ( ,  , x , U , and -). Given the following relations: Sailors(sid, sname, rating, age) // sailor id, sailor name, rating, age Boats(bid, bname, color) Reserves(sid, bid, day) // boat id, boat name, color of boat// sailor id, boat id, date that sailor sid // made a reservation for boat bid Write the following query using Relational Algebra (not SQL). Find the names of sailors who reserved a red boat or reserved a green boat (or both). To simplify notation, you may write SIGMA for  and PI for . You may also use <-- for Assignment and RHO for  (Rename). Also, although you may use subscripts, you may also use square brackets, for example writing: PI[Sailors.name] ( SIGMA[Sailors.age > 18 ] ( Sailors ) ) instead of  Sailors.name (  Sailors.age > 18 ( Sailors ) ) Question 25: Suppose that you have a relation: Products(ProductID, ProductName, Component, Quantity) that has the following non-trivial Functional Dependencies: ProductID, Component → Quantity ProductName, Component → Quantity ProductName → ProductID ProductID → ProductName 25a) Is the Products relation in Boyce-Codd Normal Form? Give a clear detailed proof of your answer. Answer 25a): 25b) Is the Products relation in Third Normal Form? Give a clear detailed proof of your answer. Answer 25b): Part III: (36 points, 9 points each) Some familiar tables appear below, with Primary Keys underlined. These tables also appear on the last page of the Final, which you can tear off to help you do questions in Part III of the Final. If you tear off the page, you don’t have to turn it at the end of the Exam. Movies(movieID, name, year, rating, length, totalEarned) Theaters(theaterID, address, numSeats) TheaterSeats(theaterID, seatNum, brokenSeat) Showings(theaterID, showingDate, startTime, movieID, priceCode) Customers(customerID, name, address, joinDate, status) Tickets(theaterID, seatNum, showingDate, startTime, customerID, ticketPrice) Assume that no attributes can be NULL, and that there are no UNIQUE constraints. You may assume Referential Integrity constraints as follows: • Each theaterID in TheaterSeats appears as a Primary Key in Theaters. • Each theaterID in Showings appears as a Primary Key in Theaters. • Each movieID in Showings appears as a Primary Key in Movies. • Each customerID in Tickets appears as a Primary Key in Customers. • Each (theaterID, seatNum) in Tickets appears as a Primary Key in TheaterSeats. • Each (theaterID, showingDate, startTime) in Tickets appears as a Primary Key in Showings. Write legal SQL queries for Questions 26-29. If you want to create and then use views to answer these questions, that’s okay, but views are not required unless the question asks for them. Don’t use DISTINCT in your queries unless it’s necessary, 1 point will be deducted if you use DISTINCT when you don’t have to do so. (Of course, points will also be deducted if DISTINCT is needed and you don’t use it.) And some points may be deducted for queries that are very complicated, even if they are correct. Question 26: In the TheaterSeats table, brokenSeat is true if a theater seat is broken; otherwise it’s false. Write a SQL query that finds the theaterID and seatNum for all theater seats that aren’t broken, and for which no tickets have been sold. Result tuples which have a larger theaterID value should appear before result tuple that have a smaller theaterID value. If two result tuples have the same theaterID value, tuples with a smaller seatNum should appear before tuples with a larger seatNum. No duplicates should appear in your result. Answer 26: Question 27: Find the customerID, name and address of each customer whose address has the string ‘Cruz’ (with that exact capitalization) appearing anywhere in it, and who bought tickets to at least 8 different movies. Your result should have attributes cID, cName and cAddress. No duplicates should appear in your result. Answer 27: Question 28: Showings has an attribute startTime, the time at which a showing starts. The endTime (that is, the time at which a showing ends) is its startTime plus the length of that showing’s movie. Two showings S1 and S2 conflict if they are in the same theater on the same showingDate, S1 starts before S2 starts, but S2 starts before S1 ends. For each pair of showings S1 and S2 that conflict, your SQL query should output theaterID, showingDate, the startTime of S1, the endTime of S1, and the startTime of S2. The attributes in your result should appear as theaterID, showingDate, firstStart, firstEnd and secondStart. No duplicates should appear in your result. Question 29: This question has two parts; be sure to answer both. 29a) Define a view SingleTheaterMovies that finds the movies that had showings in exactly one theater. The attributes of the view should be movieID and theaterID. No duplicates should appear in the SingleTheaterMovies view. 29b) Using the SingleTheaterMovies view, define another view, GreatSingleTheaterMovies. A GreatSingleTheaterMovies is a movie that had showings in exactly one theater, is rated 'G', and earned more than 2000.00. GreatSingleTheaterMovies should have attributes movieID, name and totalEarnings. Be sure to use the SingleTheaterMovies view to do this; you may also use the original tables if necessary. No duplicates should appear in the GreatSingleTheaterMovies view. [Show More]

Last updated: 1 year ago

Preview 1 out of 13 pages