University of California, Santa Cruz CSE 180, Final Exam, Multiple Choice Questions (Part I) Answered on Scantron Sheet. Score 20/20

CSE 180, Final Exam, Winter 2020, Shel Finkelstein Multiple Choice Questions (Part I) Answered on Scantron Sheet Test Form letter: A This first Section (Part I) of the Winter 2020 CSE 180 Final is Multiple Choice and is double-sided. Answer all multiple choice questions on your Scantron sheet. You do not have to hand in this 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 this Section of the Final. Name and Student ID must also be filled in by shading letters/numbers on the form. You must also mark the version (“Test Form letter A”) of the Multiple Choice section that you took. The box for Test Form letter is at the top of the Scantron sheet, just to the left of the Multiple Choice questions. The 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 the 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 this first Section of the Exam and also the separate second Section of the Exam. You must also show your UCSC id when you hand them in. You do not have to hand in this Multiple Choice section of the Final, but you must show us the Test Form letter that’s printed on your exam.Part I: (42 points, 2 points each) Answer the questions in Part I on your Scantron sheets, which should have your name, email and UCSC id on them. Select the best answer for each of the following. For some questions, a choice is “All of the answers are correct”, or "None of the other answers is correct", so read answer choices carefully. Question 1: If an instance of the relation R1(A,B,C) has 100 different tuples in it, and an instance of the relation R2(D,E) has 8 different tuples in it, then how many tuples are there in the result of the following SQL query? SELECT * FROM R1, R2 WHERE R1.C = R2.D; a) Exactly 108 b) Exactly 800 c) Must be between 0 and 108; could be 0 and could be 108 d) Must be between 108 and 800; could be 108, and could be 800 e) Must be between 0 and 800; could be 0 and could be 800 Question 2: We discussed ACID properties of transactions. What of the following best corresponds to Consistency (the “C” in ACID)? a) Transactions must be executed one at a time. b) A transaction that rolls back has no effect on the database. c) If a transaction commits, its changes are permanent, even if there are failures. d) Constraints are always maintained by the database system. e) Uncommitted (dirty) values from one transaction are never read by any other transaction. Question 3: R(A,B,C,D) is a relation. A’s domain has 3 values, B’s domain has 5 values, C’s domain has 2 values and D’s domain has 8 values. (A, B,C) is the Primary Key of R. D is Unique and can’t be NULL. What is the maximum number of different tuples that can be in an instance of R? a) 3 b) 8 c) 12 d) 30 e) 240Question 4: In a database, an instance of relation R(A,B) has 40 tuples in it, all exactly the same, and an instance of relation S(A,B) has 2 tuples in it, all of which are exactly the same as the tuples in R(A,B). If R and S are Union-Compatible, then how many tuples are there in the result of the following query? ( SELECT * FROM R ) EXCEPT ( SELECT * FROM S ); a) 0 b) 1 c) 38 d) 39 e) 42 Question 5: For an instance of the relation Employees(name, age, salary), suppose that there are no employees whose salary is less than 8000. However, there are employees whose salary is 8000 or more. Which employee names will appear in the result of the following query? SELECT FROM Employees e1 WHERE ALL ( SELECT FROM Employees e2 WHERE y 8000 ); a) There will be no employee names in the result. b) All of the employee names will be in the result. c) The result will be NULL. d) The result will be UNKNOWN. e) The query will cause a runtime error. Question 6: Which statement is true for Relational Algebra Operations? a) If C1 and C2 are conditions on the attributes of r, then: C1 (C2 (r)) = C2 (C1 (r)) b) A sequence of projections corresponds to the result of the innermost projection: L1 ( L2 ( L3 ( r) ) ) = L3 ( r) c) Difference is Commutative (for union-compatible relations): r - s = s - r d) Difference is Associative (for union-compatible relations): (r - s) - t = r - (s -t) e) All of the answers are correct.Question 7: Employees(name, age, salary) is a table, and the salary for an employee named Singh is 6000. Two different transactions T1 and T2 are executed by different people at approximately the same time, both with Isolation Level Serializable. T1 and T2 are the only transactions executing. Both transactions commit successfully. What is Singh’s salary afterwards? T1: BEGIN TRANSACTION; UPDATE Employees SET salary = salary + 1000 WHERE name = ’Singh’; COMMIT TRANSACTION; T2: BEGIN TRANSACTION; UPDATE Employees SET salary = salary - 500 WHERE name = ’Singh’; COMMIT TRANSACTION; a) Must be 5500 b) Must be 6500 c) Must be 7000 d) Must be either 5500 or 7000 e) Could be a value other than 5500, 6500 or 7000 Question 8: The relational model has physical independence. Which of the following illustrates “physical independence” for the relational model? a) You don’t have to rewrite your SQL queries when the set of indexes on your tables changes. b) You don’t have to rewrite your SQL queries when you create a new view. c) You can port a Relational Database System onto different processors and operating systems, and it will still run. d) Transactions have ACID properties. e) None of the other answers is correct.Question 9: Suppose that our database schema contains a table Movies, whose Primary Key is (title, year). Movies(title, year, length, studioName) Our database schema also has a view PixarMovies defined by: CREATE VIEW PixarMovies AS SELECT title , year FROM Movies WHERE studioName = ‘Pixar '; Assuming that there isn’t already a movie named ‘Onward’ in the Movies table, when will execution of the following statement result in an error? INSERT INTO PixarMovies VALUES (‘Onward’, 2020); a) It will result in an error whenever there are no Movies that have studioName ‘Pixar’ because PixarMovies will be empty. b) It will result in an error whenever at least one of the attributes length and studioName doesn’t have a default value. c) It will result in an error whenever at least one of the attributes length and studioName can’t be NULL. d) It will result in an error whenever at least one of the attributes length and studioName doesn’t have default value, and that attribute also can’t be NULL. e) It will never result in an error. Question 10: For the relations Customers(cid, cname, level, type, age) and Activities(cid, slopeid, date), what does the following Relational Algebra query do? ΠC ( σC = A ( Customers X Activities ) ) a) Finds all the cid values that appear in the Customer relation. b) Finds all the cid values that appear in the Activities relation. c) Finds the cid values that appear in the Customers relation for which there was at least one activity in Activities with the same cid. d) Finds the cid values that appear in the Activities relation for which there was at least one customer in Customers with the same cid. e) Finds the cid values that appear in the Customers relation for which there was more than one activity in Activities with the same cid.Question 11: For a very large table: Students(ID, name, dept_name, tot_cred) which index will probably help the most when executing the following query? SELECT ID, tot_cred FROM Students WHERE tot_cred 100 AND dept_name = ‘CSE'; a) An index on dept_name b) An index on tot_cred c) An index on (ID, tot_cred) in that order d) An index on (dept_name, tot_cred) in that order e) An index on (tot_cred, dept_name) in that order Question 12: For the table Employees(name, age, salary) which of these CHECK conditions says that if age is not NULL, then salary must also not be NULL? a) CHECK ( age NULL AND salary NULL) b) CHECK ( age IS NOT NULL AND salary IS NOT NULL) c) CHECK ( age NULL OR salary NULL) d) CHECK ( age IS NOT NULL OR salary IS NOT NULL) e) CHECK ( age IS NULL OR salary IS NOT NULL)Question 13: Customers(cid, cname, level, type, age) is a table in your database. What happens when the following statement is executed? DELETE FROM Customers; a) The Customers table and all the tuples in the Customers table are deleted. b) All the tuples in the Customers tables are deleted, but the Customers table still exists. c) The Customers table is deleted, but all the tuples in the Customers tables still exist. d) The statement causes a runtime error if there are any tuples in the Customers table. e) The statement causes a runtime error if there are no tuples in the Customers table. Question 14: Which is/are advantages of Stored Procedures (and Functions)? a) After a Stored Procedure has been written, it can be used and re-used by anyone who has the authority to execute it. b) A Stored Procedure can encapsulate complex processing whose inner workings might be hidden from users of that Stored Procedure. c) A user may be authorized to execute a Stored Procedure that accesses certain data, even though the user is not authorized to access that data directly. d) With a Stored Procedure, processing can be performed inside the database system, instead of moving data back and forth between the client and the database. e) All of the answers are correct. Question 15: Relations R(A,B,C,D) and S(E,F,G,H) have no attributes in common. For this R and S, which of the following always equals R ⋈ S, the Natural Join of R(A,B,C,D) and S(E,F,G,H)? a) R ∪ S b) R ∩ S c) R x S d) R – S e) None of the other answers is correct.Question 16: In JDBC, what’s the difference between a Statement and a PreparedStatement? a) You use ExecuteUpdate on a PreparedStatement, and ExecuteQuery on a Statement. b) You use ExecuteQuery on a PreparedStatement, and ExecuteUpdate on a Statement. c) You supply the query or update when you create a PreparedStatement, but not when you create a Statement. d) You supply the query or update when you create a Statement, but not when you create a PreparedStatement. e) There’s no such thing as a PreparedStatement in JDBC. Question 17: For the following addressbook DTD: !DOCTYPE addressbook [ !ELEMENT addressbook (person*) !ELEMENT person (name, address, homephone?, ( workphone | mobile )+, email*) !ELEMENT name (#PCDATA) !ELEMENT address (#PCDATA) !ELEMENT homephone (#PCDATA) !ELEMENT workphone (#PCDATA) !ELEMENT workphone (#PCDATA) !ELEMENT email (#PCDATA) ] Does the following data correspond to that DTD? addressbook person name Thomas Edison /name address Menlo Park, NJ 08820 /address workphone (732) 555 1234 /workphone workphone (848) 555 3557 /workphone /person /addressbook a) Yes, the data corresponds to the DTD. b) No, because there’s no homephone. c) No, because there’s no mobile. d) No, because there are 2 workphones. e) No, because there’s no email.Question 18: Which statement about On-Line Analytical Processing (OLAP) is correct? a) A Fact table must have a row for every combination of its Dimension table values. b) There can be more than one row in a Fact table that corresponds to a particular combination of its Dimension table values. c) Aggregations of Dependent attributes in a Fact table are automatically calculated and stored in the database. d) Roll-up of a Fact table corresponds to performing SQL ORDER BY and aggregation. e) None of the other answers is correct.Question 19: R(A,B,C,D) is a relation, and the Functional Dependency AB → CD holds for that relation. Determine which of the following 4 Functional Dependencies must also hold for R(A,B,C,D), and answer based on choices below. i. A → CD ii. B → CD iii. AB → C iv. AB → D a) All of them must hold. b) Only i and ii must hold. c) Only iii and iv must hold. d) Only i and iii must hold. e) Only ii and iv must hold. Question 20: Suppose that R(A, B ,C, D) is a relation, Rx is an instance of R, and F is a non-trivial Functional Dependency on the attributes of R. Here are two statements: i. If instance Rx satisfies F, then F must hold for R. ii. If instance Rx does not satisfy F, then F does not hold for R. Supply the correct answer about these two statements. a) Both statements are True. b) The first statement is True, and the second statement is False. c) The first statement is False, and the second statement is True. d) Both statements are False. e) None of the other answers is correct.