University of California, Santa Cruz CMPS: CMPS 180, Final Exam, Winter 2019, Shel Finkelstein Multiple Choice Questions (Part I) Answered on Scantron Sheet. Score 100%

Document Content and Description Below

CMPS 180, Final Exam, Winter 2019, Shel Finkelstein Multiple Choice Questions (Part I) Answered on Scantron Sheet Test Form letter: A This first Section (Part I) of the Winter 2019 CMPS 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, email and Student ID 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 Final 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, and show your UCSC id when you hand them in. https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com 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 may be “All of the Above” or “None of the Above”, so read answer choices carefully. Question 1: The relational model has logical independence. What does “logical independence” mean for the relational model? a) When you write queries, you don’t have to know how the tables used are stored, or what indexes exist. b) If you change what’s a table and what’s a view, then your queries will still work. c) You can port a Relational Database System on different hardware and operating systems and it will still run. d) You can also write your queries using a Hierarchical Model, with trees. e) None of the Above. Question 2: Let R(A,B,C,D,E) be a relation, where (A, B) is the Primary Key for R, a UNIQUE constraint is specified on (C, D), and attributes C, D and E cannot be NULL. Attribute A’s domain has 5 different values, B’s domain has 8 different values, C’s domain has 2 different values, D’s domain has 12 different values and E’s domain has 30 different values. What is the maximum number of different tuples that can be in an instance of R? a) 2 b) 13 c) 24 d) 30 e) 40 Question 3: The Relational Algebra operation Semi-Join is most closely relating to which of the following in SQL? a) EXISTS b) Aggregation c) GROUP BY d) HAVING e) ORDER BY https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Question 4: What does the Isolation Level READ COMMITTED mean for transactions? Give the best answer. a) Values written by an uncommitted transaction may be read by another transaction. b) Uncommitted values from one transaction are never read by any other transaction. c) When a transaction reads the same data multiple times, it sees the same values all times, but it also sees its own updates. d) A transaction read all data as of when the transaction started, but it also read its own updates. e) Transaction execution is serializable, meaning that execution is “as if” transactions were executed one-by-one. Question 5: Assume that R is a relation, and that c1 and c2 are conditions on the attributes of R. Here are some statements. Which of them is/are always correct? Statement 1: c1 ( c2 (R) ) = c1 AND c2 (R) Statement 2: c2 ( c1 (R) ) = c1 AND c2 (R) Statement 3: c1 ( c2 (R) ) = c2 ( c1 (R) ) a) Just Statement 1 b) Just Statement 1 and Statement 2 c) Just Statement 3 d) Statement 1, Statement 2 and Statement 3 e) None of the Above. Question 6: An instance of relation R(A,B,C) has m tuples, all exactly the same. An instance of relation S(A,B,C) has n tuples, all exactly the same as all the tuples in R(A,B,C). m is greater than or equal to n. If R and S are Union-Compatible, then how many tuples are there in the result of the following query? ( SELECT * FROM R ) EXCEPT ALL ( SELECT * FROM S ); a) 1 b) m c) m – n d) m + n e) min(m, n) https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Question 7: If an instance of relation S(A,B,C) has 6 different tuples in it, and an instance of relation T(C,D,E) has 20 different tuples in it, then how many tuples will there be in the result if the following SQL query is executed on those instances? SELECT * FROM S, T WHERE S.C = T.C; a) Exactly 26. b) Exactly 120. c) Could be between 0 and 26, but can’t be anything else. d) Could be between 26 and 120, but can’t be anything else. e) Could be between 0 and 120, but can’t be anything else. Question 8: In JDBC, after you obtain a ResultSet rs from executing a query, which construct would you use as part of a loop to iterate through rs? a) while ( fetch(rs) ) b) while ( rs.fetch() ) c) while ( rs.fetch() = 0 ) d) while ( rs.fetch() != NOTFOUND ) e) while ( rs.next() ) Question 9: The tables Slopes(slopeid, sname, color) and Activities(cid, slopeid, date) are in your schema. Why might there be a runtime error when the following statement is executed? SELECT s.slopeid, s.color FROM Slopes s WHERE s.slopeid = ANY ( SELECT a.slopeid FROM Activities a); a) There might be a slopeid in Slopes for which there are no activities in Activities. b) There might be a slopeid in Slopes for which there is exactly one activity in Activities. c) There might be a slopeid in Slopes for which there is more than one activity in Activities. d) There might be an activity in Activities for which there are no matching slopeid in Slopes. e) There wouldn’t be a runtime error for this statement. https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Question 10: What statement about the three-valued logic used by SQL is correct? a) The negation of FALSE is TRUE, but the negation of UNKNOWN is UNKNOWN. b) UNKNOWN is the same as NULL, but FALSE isn’t. c) FALSE AND UNKNOWN equals UNKNOWN. d) When you execute a SQL query, if the result for some tuples is FALSE, those tuples don’t contribute to the answer, but if the result is UNKNOWN, then they do contribute to the answer. e) All of the Above. Question 11: Suppose that our database schema contains a table Movies and there is a view ParamountMovies defined by: CREATE VIEW ParamountMovies AS SELECT title , year FROM Movies WHERE studioName = ‘Paramount '; When happens when we execute the following? DROP View ParamountMovies; a) It will result in an error if there are any tuples in ParamountMovies. b) Queries can still be executed on Movies but not on ParamountMovies, and all the Paramount movies will be deleted from the Movies table. c) Queries can still be executed on Movies but not on ParamountMovies, and all the Paramount movies will still be in the Movies table. d) Queries can still be executed on both Movies and on ParamountMovies, but there will be no movies in ParamountMovies. e) Queries can no longer be executed on either Movies or on ParamountMovies. https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Question 12: Students(student_id, name, address, age, major) is a table where student_id is the Primary Key. If (12345, ‘Alexander Hamilton’, ‘Miranda Place’, 21, 'HIST') is a tuple in that table, and the following is executed, with no other work going on: BEGIN TRANSACTION; UPDATE Students SET age = age + 1 WHERE name = ‘Alexander Hamilton’; UPDATE Students SET major = 'POLI' WHERE age = 21; ROLLBACK TRANSACTION; then afterwards, what will be in the tuple that has student_id 12345? a) (12345, ‘Alexander Hamilton’, ‘Miranda Place’, 21, 'HIST') b) (12345, ‘Alexander Hamilton’, ‘Miranda Place’, 22, 'HIST') c) (12345, ‘Alexander Hamilton’, ‘Miranda Place’, 21, ' POLI') d) (12345, ‘Alexander Hamilton’, ‘Miranda Place’, 22, ' POLI') e) None of the Above Question 13: Employees(name, age, salary) is a relation whose Primary Key is name. Which CHECK constraint correctly says that if the value of salary is more than 9000, then the value of age must be NULL? a) CHECK (salary > 9000 AND age IS NULL) b) CHECK (salary <= 9000 OR age IS NULL) c) CHECK (salary > 9000 OR age IS NOT NULL) d) CHECK (salary > 9000 OR age <> NULL) e) CHECK (salary <= 9000 OR age = NULL) https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Question 14: R is a relation, and F is a Functional Dependency X->A, where X is a set of attributes of R and A is an attribute of R. You inspect 10 million instances of R, and FD F holds for all of them. But there are other instances of R. Which statement is correct? a) There cannot be a relation R and a Functional Dependency F that holds for 10 million instances of R. b) FD F must hold for all instances of R. c) There must be an instance of R for which F does not hold. d) It’s possible that FD F holds for all instances of R, but it’s also possible that there is an instance of R for which FD F does not hold. e) None of the Above. Question 15: The OLAP operation Roll-Up is most similar to: a) GROUP BY and aggregation b) ORDER BY and aggregation. c) OUTER JOIN d) Cartesian Product e) UNION Question 16: For the relation: Students(student_id, name, address, age, major) for which student_id is the primary key, what is the result of the following query if there are students, but there are no students whose major is ‘CMPS’ SELECT s.student_id FROM Students s WHERE s.age <= ALL ( SELECT s2.age FROM Students s2 WHERE s2.major = ‘CMPS’ ); a) The result will be the empty set. b) The result will be the student_id for all students. c) The result will be the student_id for all students who have the youngest age. d) The result will be a runtime error. e) This is not a legal SQL statement. https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Question 17: Which is the best index for the following query on the table: Movies(title, year, length, genre, studioName , producerC#)? SELECT producerC#, year FROM Movies WHERE studioName LIKE ‘D%’ AND year = 1942; a) An index on studioName b) An index on year c) An index on (studioName, year) d) An index on (year, studioName) e) An index on (producerC#, year) Question 18: The Relational Model is schema-first. Some other models support a data-first approach. What’s the major difference between “schema-first” and “datafirst” approaches? a) A schema-first approach allows data to be stored and accessed efficiently. “Datafirst” allows the data to describe itself, which is a more flexible, less efficient model. b) With a schema-first approach used by Relational databases, the schema can never be changed, and that simplifies query execution. c) A data-first approach supports greater efficiency than with a schema-first model. d) A data-first approach limits the number of queries that can be executed, which makes database administration simpler than in the Relational Model. e) All of the Above. Question 19: Assume that Employees(ename, salary, age, department) is a table in which ename is the primary key. Which statement about the following query is correct? SELECT department, MIN(salary), MAX(salary) FROM Employees GROUP BY department HAVING ANY (salary > 8000); a) The query is a legal SQL query. b) The query is not a legal SQL query because of the aggregates on salary that appear in the SELECT clause. c) The query is not a legal query because salary appears in the HAVING clause, but salary does not appear in the GROUP BY clause. d) The query is not a legal SQL query because ANY appears in the HAVING clause. e) The query is not a legal SQL query because department appears in the SELECT clause, and department also appears in the GROUP BY clause. https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Question 20: When you write a C application using embedded SQL: a) The C programmer must write the application code using CLI library calls for SQL calls, similar to what JDBC does for Java. b) The C programmer must write the application code using a stored procedure language such as PSM, PL/SQL or PL/pgSQL for SQL calls. c) The application code can be compiled as is by the C compiler as written. d) The application code can mix C and SQL statements, with no special syntax needed for SQL statements. But the code must go through a pre-processor before it can be compiled by the C compiler. e) The application code can mix C and SQL statements, with EXEC SQL before each SQL statement and shared variables starting with a colon when used in SQL. But the code must go through a pre-processor before it can be compiled by the C compiler. Question 21: The Union Rule for Functional Dependencies says that: If X → Y and X → Z, then X → YZ And the Decomposition Rule for Functional Dependencies says that: If X → YZ then both X → Y and X → Z. Which of the following do these rules together allow us to do? a) Assume that every FD has only one attribute on its left-hand side. b) Assume that every FD has only one attribute on its right-hand side. c) Prove that if a set of FDs implies another FD F, then F can be proved from the set of FDs using Armstrong Axioms. d) Prove that if an FD F can be proved from a set of FDs using Armstrong Axioms, then that set of FDs implies F. e) Keep cats, dogs and horses out of the classroom. https://www.coursehero.com/file/51969455/Final-CMPS180-Winter2019-MultipleChoice-Answers-Apdf/ This study resource was shared via CourseHero.com Powered by TCPDF (www.tcpdf.org) CMPS 180, Final Exam, Winter 2017, Shel Finkelstein Student Name: ____________________________________________________ Student ID: ____________________________________________________ UCSC Email: ____________________________________________________ Final Points Part Max Points Points I 44 II 30 III 32 Total 106 The first Section (Part I) of the Winter 2017 CMPS 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 that first Section of the Exam, but you must hand in the Scantron sheet, with your name, email and student id on that Scantron sheet. Please be sure to use a #2 pencil to mark your choices on this Section of the Final. 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 the first Section of the Exam and also the second Section of the Exam, and show your UCSC id when you hand them in. Part I: (44 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 Above”, so read answer choices carefully. Question 1: If an instance of relation R(A,B) has 10 different tuples in it, and an instance of relation S(B,C,D) has 6 different tuples in it, then how many tuples are there in the result if the following SQL query is executed on those instances? SELECT * FROM R, S WHERE R.B = S.B; a) 0 b) Exactly 16 c) Exactly 60 d) Between 0 and 16 e) Between 0 and 60 Question 2: We discussed ACID properties of transactions. What does Atomicity (the “A” in ACID) refer to for transactions? a) Transaction execution is as if they were executed one at a time. b) Transactions happen completely or not-at-all. c) If a transaction commits, its changes are permanent, even if there are failures. d) Business rules are always maintained by the database system. e) Uncommitted (dirty) values from one transaction are never read by any other transaction. Question 3: An instance of relation R(A,B) has m tuples in it, all the same, and an instance of relation S(A,B) has n tuples in it, which are all the same as the tuples in R(A,B). If R and S are Union-Compatible, and m and n are both at least 1, then how many tuples are there in the result of the following query? ( SELECT * FROM R ) INTERSECT ( SELECT * FROM S ); a) 1 b) m + n c) m – n (but not less than zero) d) min(m, n) e) max(m, n) Question 4: Why might there be a runtime error when the following statement is executed on relations Customers(cname, age) and Activities(cname, slopeid, date)? SELECT Customers.cname FROM Customers WHERE Customers.cname = ( SELECT Activities.cname FROM Activities ); a) Some customer may have participated in no activities. b) Some customer may have participated in exactly one activity. c) Some customer may have participated in more than one activity. d) For some cname in Activities, there might be no customer with that cname in Customers. e) For some cname in Activities, there might be more than one customer with that cname in Customers. Question 5: For the relation Employees(name, age, salary), suppose that no employees who are 65 or older, but there are employees who are under 65. Which employee names will appear in the result to the following query: SELECT e1.name FROM Employees e1 WHERE e1.salary < ALL ( SELECT e2.salary FROM Employees e2 WHERE e2.age >= 65 ); 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, where R is a relation and C1 and C2 are conditions on the attributes of R? a)  is Commutative: C1 (C2 (R)) = C2 (C1 (R)) b) C1 (C2 (R)) = C1 AND C2(R) c) Union is Commutative (for union-compatible relations): R ∪ S = S ∪ R d) Union is Associative (for union-compatible relations): (R ∪ S) ∪ T = R ∪ (S ∪ T) e) All of the Above Question 7: Employees(name, age, salary) is a relation, and the salary for an employee named Smith is 5000. What is Smith’s salary after the following, assuming that this is the only transaction executing? BEGIN TRANSACTION; UPDATE Employees SET salary = salary + 1000 WHERE name=’Smith’; UPDATE Employees SET salary = 2 * salary WHERE name=’Smith’; ROLLBACK TRANSACTION; a) 5000 b) 6000 c) 12000 d) Could be 5000, 6000 or 12000 e) None of the Above Question 8: Employees(name, age, salary) is a relation, and the salary for an employee named Smith is 5000. Two different transactions T1 and T2 are executed by different people at approximately the same time, with Isolation Level Serializable. Both transactions commit. T1 and T2 are the only transactions executing. What is Smith’s salary afterwards? T1: BEGIN TRANSACTION; UPDATE Employees SET salary = salary + 1000 WHERE name=’Smith’; COMMIT TRANSACTION; T2: BEGIN TRANSACTION; UPDATE Employees SET salary = 2 * salary WHERE name=’Smith’; COMMIT TRANSACTION; a) Must be 5000 b) Must be 11000 c) Must be 12000 d) Must be either 11000 or 12000 e) Could be something other than 11000 or 12000 Question 9: The Employees(name, age, salary) table has been created with name as the primary key, salary having a default value of 9000, and with age and salary both allowed to be NULL. What is in the tuple with name ‘Chou’ after the following statement is executed, assuming that there was no tuple for ‘Chou’ before the statement was executed? INSERT INTO EMPLOYEES(name, age) VALUES (‘Chou’, 25); a) ‘Chou’, 25 b) ‘Chou’, 25, NULL c) ‘Chou’, 25, 9000 d) ‘Chou’, NULL, 9000 e) There will be no tuple for Chou because salary was not supplied in the INSERT. Question 10: For the relations Customers(cname, age) and Activities(cname, slopeid, date), what does the following Relational Algebra query do? ΠCustomers.cname, Customers.age(σCustomers.cname=Activities.cname(Customer X Activities)) a) Finds cname and age for the customers who participated in at least one activity. b) Finds cname and age for the customers who didn’t participate in any activity. c) Finds cname and age for each customer. d) For each customer, gives cname, age and counts the number of activities that the customer participated in. e) None of the Above Question 11: For a table Movies(title, year, length, studio), which index will probably help the most for executing the following query? SELECT title, year, length FROM Movies WHERE year > 2000 AND studio = ‘Disney”; a) An index on year b) An index on studio c) An index on (year, studio) in that order d) An index on (studio, year) in that order e) Indexes on (year, studio) and (studio, year) are both the most helpful, and they’re equally good. Question 12: Assume that name is the primary key in the Beers table. This use of a CHECK constraint is legal in many SQL systems (but not in PostgreSQL): CREATE TABLE Sells ( bar beer CHAR(20),CHAR(20) CHECK ( beer IN(SELECT name FROM Beers) ), price REAL ); For SQL systems in which this use of CHECK is legal, which is the best answer? a) If you INSERT a row into the Sells table whose beer attribute value doesn’t appear as a name in the Beers table, then there will be an error. b) If you DELETE a row from the Beers table, and there is a row in the Sells table whose beer attribute value equals the beer name in the deleted row, then there will be an error. c) If you UPDATE a row in the Sells table, changing its beer attribute value to a different beer whose name appears in the Beers table, then there will be an error. d) Answer a) and b) are both correct, but answer c) is not correct. e) Answer a), b) and c) are all correct. Question 13: Which operation can an ALTER statement do? a) Create a table b) Drop a table c) Delete all rows from a table d) Add additional columns to a table e) None of the Above Question 14: Which of these is/are advantages of Stored Procedures? a) Can make performance better by enabling operations to be performed on data without moving that data from the database to the client. b) Can make programming easier by allowing code to be written once in a procedure, and then reused by anyone authorized to execute the procedure. c) Can improve security by allowing people to be authorized to execute a procedure without authorizing them to access all the data used by the procedure. d) All of the Above e) None of the Above Question 15: Sells(bar, beer, price) is a table, and RipoffBars(bar) is another table. What does this Trigger do, assuming that price represents a dollar amount? CREATE TRIGGER PriceTrig AFTER UPDATE OF price ON Sells REFERENCING OLD ROW AS old_row NEW ROW AS new_row FOR EACH ROW WHEN(new_row.price – old_row.price > 8.00) INSERT INTO RipoffBars VALUES(new_row.bar); a) If any row in Sells has a beer that costs more than 8 dollars, then the bar in that row is inserted into RipoffBars. b) If a row is inserted into Sells that has a beer that costs more than 8 dollars, then the bar in that inserted row is inserted into RipoffBars. c) If price is updated for a row in Sells, and the new price is more than 8 dollars higher than the old price, then the bar in that updated row is inserted into RipoffBars. d) If price is updated for a row in Sells, and the old price is more than 8 dollars higher than the new price, then the bar in that updated row is inserted into RipoffBars. e) If price is updated for a row in Sells, and the new price is more than 8 dollars higher than the old price, then the update returns an error. Question 16: Sells(bar, beer, price) is a table, where bar and beer are CHAR(20) and price is FLOAT. Assuming that myCon is a connection, what error appears in the following JDBC, which is supposed to print the beers and prices for Joe''s Bar? Statement stmt = myCon.createStatement(); ResultSet Menu = stmt.executeQuery(“ SELECT beer, price FROM Sells WHERE bar = 'Joe''s Bar' ”); while (Menu.fetch()) { // For each value in result, get values of beer and price, and print them System.out.println(Menu.getString(1), Menu.getFloat(2)); a) Should use PreparedStatement, not Statement b) Should use Menu.next, not Menu.fetch c) Should use System.out.println(ResultSet.Float(1), ResultSet.getString(2)); not System.out.println(Menu.getString(1), Menu.getFloat(2)); d) Should use stmt.updateQuery, not stmt.executeQuery e) In the SELECT statement, 'Joe''s Bar' has the single quote symbol appearing twice (between Joe and s) in 'Joe''s Bar', but it should only appear once, replaced by 'Joe's Bar' Question 17: R(A,B) and S(A,B) are union-compatible tables which may have duplicates. Here are two queries, Q1 (on the left) and Q2 (on the right) on those tables. Which statement is always correct for the results of those queries? a) The results of Q1 and Q2 must always be the same. b) Everything in the result of Q1 must always be in the result of Q2, but the results aren’t always the same. c) Everything in the result of Q2 must always be in the result of Q1, but the results aren’t always the same. d) The result of Q1 can never have duplicates. e) None of the Above Question 18: In On-Line Analytic Processing (OLAP), what does the term “roll-up” mean? a) Separating attributes in a Fact table into Dimension attributes and Dependent attributes. b) Joining a Fact table with its underlying Dimension tables. c) Doing an Outer Join to obtain facts that are not in the Fact Table because values for those facts are 0 or NULL. d) Aggregating values along one or more dimensions, e.g., taking a sum. e) Disaggregating a value into its constituent parts, e.g., breaking a sum down into the values that led to the sum. Question 19: For the following addressbook DTD: <!DOCTYPE addressbook [ <!ELEMENT addressbook (person*)> <!ELEMENT person (name, address+, ( homephone | ( workphone, mobile )* ), email?)> <!ELEMENT name <!ELEMENT address (#PCDATA)>(#PCDATA)> <!ELEMENT homephone (#PCDATA)> <!ELEMENT workphone (#PCDATA)> <!ELEMENT mobile <!ELEMENT email ]> (#PCDATA)>(#PCDATA)> Does the following data correspond to that DTD? <addressbook> <person> <name> Abraham Lincoln </name> <address> Washington D.C. 20500 </address> <workphone> (312) 555 9876 </workphone> <mobile> (773) 555 2543 </mobile> </person> </addressbook> a) Yes, the data corresponds to the DTD. b) No, because there’s only one person. c) No, because there’s no homephone. d) No, because there’s both a workphone and a mobile. e) No, because there’s no email. Question 20: For the table R(A, B, C), which is a lossless join decomposition? R1(A, B) is the projection of R on attributes A, B, and R2(B,C) is the projection of R on attributes B, C. a) R1(A, B) and R2(B, C), if there are no functional dependencies b) R1(A, B) and R2(B, C), if we know that A → B c) R1(A, B) and R2(B, C), if we know that B → A d) R1(A, B) and R2(B, C), if we know that A → C e) R1(A, B) and R2(B, C), if we know that C → A Question 21: If you want to check whether attributes salary1 and salary2 in a relation are both NULL, which way (or ways) can you write a condition in a SQL WHERE clause to test for that? a) salary1 = NULL AND salary2 = NULL b) salary1 IS NULL AND salary1 = salary2 c) salary1 IS NULL AND salary2 IS NULL d) All of the Above e) None of the Above Question 22: Suppose that R(A, B ,C, D) is a relation, r is an instance of R, and F is a non-trivial Functional Dependency on the attributes of R. Here are two statements: i. ii. If r satisfies F, then F must hold for R.If r does not satisfy F, then F does not hold for R. You’re asked to 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) All of the Above Part II: (30 points, 6 points each) Question 23: Assume we have the following tables: • Stores table, with Primary Key store_id • Customers table, with Primary Key customer_id • Products table, with Primary Key product_id These tables have other columns that aren’t important for this question. Here’s the Create Statement for another table, the Sales Table, which gives information about products sold to customers in stores: CREATE TABLE Sales ( store_id INT, customer_id INT, product_id INT, quantity INT, PRIMARY KEY(store_id, customer_id, product_id) ); Rewrite this CREATE statement (don’t do an ALTER) so that customer_id and product_id are Foreign Keys that correspond to the Primary keys of Customers and Products, respectively. Policies for Referential Integrity should be: • A row in Customers can’t be deleted if there are any Sales rows mentioning that customer. • Deleting a row from Products deletes all Sales rows mentioning that product. Also, the quantity for any row in Sales must be at least 1, but can’t be more than 25. Answer 23: at the end of the CREATE. Question 24a): Why have any indexes in a database? Give two different clear reasons. Answer 24a): Any two of the following reasons are good. 1. Query execution may be faster using indexes, rather than scanning entire tables to find Question 24b): Why not have indexes on every column in a database? Give two different reasons. Answer 24b): Any two of the following reasons are good. Question 25a): For the relation Employees(name, age, salary), write a SQL UPDATE statement that finds every employee whose name begins with ‘SHEL’ and increases that employee’s salary by 35. Answer 25a): UPDATE Employees SET salary = salary + 35 WHERE name LIKE ‘SHEL%’; 25b): For the relation Employees(name, age, salary), write a SQL DELETE statement that deletes all employees who receive the highest salary. Answer 25b): Here are 3 solutions that work in standard SQL. DELETE FROM Employees WHERE salary = Question 26a): Codd’s relational algebra for sets included only the 5 operators Selection, Projection, Product, Union and Difference. But other operators can be defined using those operators. Show that Intersection can be defined using these 5 operators. Answer 26a): For two Union-Compatible Relations R and S, either of the following works. Only Difference is needed. R ⋂ S = R – (R – S) R ⋂ S = S – (S – R) 26b): Armstrong’s Axioms for Functional Dependencies are Reflexivity, Augmentation and Transitivity. Using those axioms, we proved other Rules for Functional Dependencies, including the Union Rule. The Union Rule is: If X → Y and X → Z, then X → YZ. Here’s the proof of the Union Rule, but with explanations missing for 3 steps in that proof. Write one of Reflexivity, Augmentation or Transitivity on each of the 3 empty underlines (starting with ?) below to supply the missing explanations in the proof. It’s okay to write the same explanation more than once, if that’s correct. Proof Step Explanation ------------------------------- ----------------------------- X → Z Given So we get XY → YZ ? Augmentation X → Y Given So we get X → XY ? Augmentation Therefore X → YZ ? Transitivity Question 27: Suppose that you have a relation Inventory(StoreID, StoreName, Part, Quantity) with the following functional dependencies: StoreID, Part  Quantity StoreName, Part  Quantity StoreName  StoreID StoreID  StoreName 27a) Is Inventory in BCNF? Justify your answer fully and clearly. Answer 27a): 27b) Is Inventory in 3NF? Justify your answer fully and clearly. Answer 27b): Part III: (32 points, 8 points each) The familiar relations Persons, Houses and Tenants are shown below, with Primary Keys underlined. Assume Referential Integrity as follows: • Every HouseID that appears in Persons, Ownerships or Tenants also appears as a HouseID in Houses. • Every SSN (OwnerSSN in Landlords, LeaseTenantSSN in Tenants) also appears as an SSN in Persons. • Every LandlordID that appears in Ownerships also appears as a LandlordID in Landlords. Persons (SSN, Name, HouseID, ApartmentNumber, Salary) Houses (HouseID, HouseAddress, ApartmentCount, Color) Landlords (LandlordID, OwnerSSN, LandlordAddress) Ownerships (LandlordID, HouseID, PurchaseDate, PropertyTax) Tenants (HouseID, ApartmentNumber, LeaseTenantSSN, LeaseStartDate, LeaseExpirationDate, Rent, LastRentPaidDate, RentOverdue) Assume that no attributes can be NULL, and that there are no UNIQUE constraints. Write SQL queries for each of the following questions. If you want to create and then use views to answer these questions, that’s okay, but views are not required unless a question asks you for them. Don’t use DISTINCT in your queries unless it’s necessary. Question 28: Find the names (with no duplicates) of all persons who live in houses whose color is ‘Red’. Names should appear in the result in reverse alphabetical order, so that names starting with ‘Z’ come before names starting with ‘Y’, etc. Answer 28: Question 29: If a landlord owns a house, then there will be a row for that LandlordID and HouseID in the Ownership relation. Find the LandlordID (with no duplicates) of all landlords in the Landlord relation who don’t own any houses. Answer 29: Here are two correct answers. Question 30: There can be multiple rows in the Tenants table that have the same LeaseTenant. For each person who is a LeaseTenant, find the total of the Rent for all the Tenant rows in which that person is the LeaseTenant, but only include rows in your result if the total Rent is more than 2000. Your result should include the LeaseTenant’s SSN, his name, his personal address, and the total Rent for all the rows in Tenants in which he is LeaseTenant. In your result, call the total Rent TotalRent. And, as we emphasized above, only include rows in your result if TotalRent is more than 2000. Answer 30: Here are three correct answers; you only needed to provide one. Answer #1 to Question 30: Question 31: This question has two parts; be sure to answer both. 31a) Create a SQL view called Rented. For each house in the Houses relation, the Rented view should give the HouseID and the number of apartments in that house that have rows in Tenants. In the result of your view, the second attribute should be called RentedCount. (Hint: Doing this correctly requires an Outer Join, since there could be Houses that have no Tenants. You will still get partial credit if you do a Join instead of an Outer Join.] 31b) Write a SQL query that finds all the houses that appear in the Rented view whose RentedCount in the Rented view does not equal the ApartmentCount in Houses for that house. Your result should include the HouseID, the RentedCount and the ApartmentCount for each house in Rented whose RentedCount doesn’t equal its ApartmentCount. Answers 31a) and 31b): 31a) CSE 180, Final Exam, Fall 2019, Shel Finkelstein Multiple Choice Questions (Part I) Answered on Scantron Sheet Answers Test Form letter: A This first Section (Part I) of the Fall 2019 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, email and Student ID 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. Part I: (40 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 Above” or “None of the Above”, so be sure to read answer choices carefully. Question 1: Which operation can be performed using an ALTER statement? a) Create a table b) Drop a table c) Delete all rows from a table d) Add additional columns to a table e) None of the Above Question 2: If an instance of relation R1(A,B,C) has 10 different tuples in it, and an instance of relation R2(B,C,D) has 8 different tuples in it, then how many tuples will there be in the result if the following SQL query is executed on those instances? SELECT * FROM R1, R2 WHERE R1.B = R2.B AND R1.C = R2.C; a) 0 b) Exactly 18 c) Exactly 80 d) Between 0 and 18. Could be 0 or 18, but could be values other than 0 and 18. e) Between 0 and 80. Could be 0 or 80, but could be values other than 0 and 80. Question 3: We discussed the ACID properties of transactions. What does Atomicity (the “A” in ACID) mean for transactions? a) Transaction execution is as if transactions were executed one at a time. b) Transactions happen completely or not-at-all. c) If a transaction commits, its changes are permanent, even if there are failures, although they can be changed by later transactions. d) Business rules are always maintained by the database system. e) Uncommitted (dirty) values from one transaction are never read by any other transaction. Question 4: Assume that r is a relation, attribs is some attributes of r, and cond is a condition on r. When will the following equation be true? scond ( pattribs (r) ) = pattribs( scond (r) ) a) It will always be true. b) It will be true only when r is a binary relation, with two attributes. c) It will be true only when there are two attributes in attribs. d) It will be true only when the conditions in cond refer just to attributes that are in attribs. e) None of the Above. Question 5: An instance of relation r(A,B) has m tuples in it, all exactly the same, and an instance of relation s(A,B) has n tuples in it, and all of those tuples 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 ) INTERSECT ALL ( SELECT * FROM s ); a) 1 b) m + n c) m – n, as long as m ≥ n, otherwise 0 d) min(m, n) e) max(m, n) Question 6: Let R(A,B,C,D) be a relation, where (A, B) is the Primary Key, C can be NULL, and D is UNIQUE, and D also can’t be NULL. Assume that A’s domain has 4 different values, B’s domain has 5 different values, C’s domain has 7 different values, and D’s domain has 11 different values. What is the maximum number of different tuples that can be in any instance of R? a) 7 b) 8 c) 11 d) 12 e) 20 Question 7: Which statement is FALSE for Relational Algebra Operations, where R is a relation and C1 and C2 are conditions on the attributes of R? a) sC1( sC2 (R) ) = sC1 AND C2(R) b) Intersection is Commutative (for union-compatible relations): R ⋂ S = S ⋂ R c) Union is Associative (for union-compatible relations): (R ∪ S) ∪ T = R ∪ (S ∪ T) d) Minus is Associative (for union-compatible relations): (R - S) - T = R - (S - T) e) Product distributes over union, assuming that S and T are union-compatible relations: R x (S ∪ T) = (R x S) ∪ (R x T) Question 8: Which of these is/are advantages of Stored Procedures? a) Stored Procedures can make performance better by enabling operations to be performed on data without moving that data from the database to the client. b) Stored Procedures can make programming easier by allowing code to be written once in a procedure, and then reused by anyone authorized to execute the procedure. c) Stored Procedures can improve security by allowing people to be authorized to execute a procedure without authorizing them to access all the data used by the procedure. d) All of the Above e) None of the Above Question 9: What difference between FALSE and UNKNOWN in the three-valued logic used by SQL is the major justification for the need for UNKNOWN? (The correct answer must both be correct, and also be the major justification for the need for UNKNOWN.) a) FALSE is the same as NULL, but UNKNOWN isn’t. b) When you execute a SQL query, if the result for some tuples is FALSE, those tuples don’t contribute to the answer, but if the result is UNKNOWN, then they do contribute to the answer. c) The negation of FALSE is TRUE, but the negation of UNKNOWN is UNKNOWN. d) TRUE OR UNKNOWN is UNKNOWN, but TRUE or FALSE is TRUE. e) FALSE AND UNKNOWN is FALSE, but FALSE OR UNKNOWN is UNKNOWN. Question 10: Here’s a query Q on the relation Employees(name, age, salary): Q: SELECTE1.name FROM EmployeesE1 WHERE EXISTS ( SELECT E2.salary FROM Employees E2 WHERE E1.salary = E2.salary AND E2.age >= 65 ); And here are 3 other queries, which are different only on the line shown in bold: Q1: SELECTE1.nameFROMemployee E1 WHERE E1.salary = ( SELECT E2.salary FROM employee E2 WHERE E2.age >= 65 ); Q2: SELECTE1.nameFROMemployee E1 WHERE E1.salary IN ( SELECT E2.salary FROM employee E2 WHERE E2.age >= 65 ); Q3: SELECTE1.nameFROMemployee E1 WHERE E1.salary = ANY ( SELECT E2.salary FROM employee E2 WHERE E2.age >= 65 ); Which query (or queries) are equivalent to Q? a) Just Q1 b) Just Q2 c) Just Q3 d) Just Q2 and Q3 e) Q1, Q2 and Q3 are all equivalent to Q Question 11: student(student_id, name, address, age, major) is a table where student_id is the Primary Key. If (5678, 'Kylo Ren', ‘Solo Place', 21, 'CMPS') is a tuple in that table, and the following is executed, with no other work going on: BEGIN TRANSACTION; UPDATE Students SET age = age + 1 WHERE name = 'Kylo Ren'; UPDATE Students SET major = ‘CSE’ WHERE age = 21; ROLLBACK TRANSACTION; then afterwards, what will be in the tuple that has student_id 5678? a) (5678, 'Kylo Ren', ‘Solo Place', 21, 'CMPS') b) (5678, 'Kylo Ren', ‘Solo Place', 22, 'CMPS') c) (5678, 'Kylo Ren', ‘Solo Place', 21, 'CSE') d) (5678, 'Kylo Ren', ‘Solo Place', 22, ‘CSE') e) None of the Above Question 12: For a very large table student(ID, name, dept_name, tot_cred), which index will probably help the most for executing the following query? SELECT ID, name, tot_cred FROM student WHERE dept_name = ‘BIO’ AND tot_cred > 60 a) An index on dept_name b) An index on tot_cred c) An index on (dept_name, tot_cred) in that order d) An index on (tot_cred, dept_name) in that order e) Indexes on (dept_name, tot_cred) and (tot_cred, dept_name) are both the most helpful, and they’re equally good. Question 13: Assume that the employee table has been created as follow: CREATE TABLE employee ( name CHAR(20) PRIMARY KEY, age INTEGER NOT NULL DEFAULT 18, salary INTEGER NOT NULL, CHECK (age < 21 OR salary >= 8000) ); Which INSERT statement (or statements) will result in an error? Be sure to give the best answer. a) INSERT INTO employee(name, salary) VALUES (‘Alpha’, 9000); b) INSERT INTO employee(name) VALUES (‘Beta’); c) INSERT INTO employee VALUES (‘Gamma’, 30, 6500); d) Answers a), b) and c) will all result in errors. e) Answers b) and c) will result in errors, but answer a) will not. Question 14: sells(bar, beer, price) and beers(name, manufacturer) are tables in a database schema, with Primary Key underlined. You look at a million different instances of the database. Here are two statements: i. sells.beer must be a Foreign Key corresponding tobeers.name if (for all million instances) every beer insells.beer alsoappears inbeers.namebeers.name must be a Foreign Key corresponding tosells.beer if (for all million instances) every beer inbeers.name alsoappears insells.beer ii. 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) All of the Above. Question 15: Identify the statement about OLAP that is FALSE. [Choose e if choices a, b, c and d are all correct.] a) There must be a row in the Fact table for every combination of Dimension table values. b) Fact tables may have multiple Measure attributes. c) In a Fact table, Dimension attributes are Foreign Keys referring to the Dimension tables. d) Roll-up of a Fact table corresponds to performing SQL GROUP BY and aggregation. e) None of the above statements are False. Question 16: What is a difference between JSON and the Relational Model? a) JSON documents are not in First Normal Form, because JSON allows arrays and nesting, but the Relational Model requires First Normal Form. b) Relational tables are not in First Normal Form, because relational tables allow arbitrarily many appearances of elements in a document, but JSON requires First Normal Form. c) Relational requires that data be in Boyce-Codd Normal Form, but JSON does not. d) Relational requires that data be in Third Normal Form, but JSON does not. e) None of the Above. Question 17: Why is a reason (or reasons) that having a relational schema in Boyce-Codd Normal Form (BCNF) may be better than not having a schema in BCNF? a) BCNF schemas reduce redundancy. b) BCNF schemas avoid the Update Anomaly. c) BCNF schemas avoid the Insert Anomaly. d) BCNF schemas avoid the Delete Anomaly. e) All of the Above. Question 18: r(A,B) and s(A,B) are Union-Compatible tables which may have duplicates. Here are two SQL queries, Q1 (on the left) and Q2 (on the right) on those tables. Which statement is always correct for the results of those queries? ( SELECT DISTINCT * FROM r WHERE A > 17 ) UNION ( SELECT DISTINCT * FROM s WHERE B < 500 ); ( SELECT * FROM r WHERE A > 17 ) UNION ALL ( SELECT * FROM s WHERE B < 500 ); a) The same tuples will appear in the results of Q1 and Q2, but Q1 may have duplicates. b) The same tuples will appear in the results of Q1 and Q2, but Q2 may have duplicates. c) There may be some tuples in the result of Q1 that do not appear even once in the result of Q2. d) There may be some tuples in the result of Q2 that do not appear even once in the result of Q1. e) None of the Above. Question 19: What are Armstrong’s Axioms? a) Transformations which relational systems use to optimize a query. b) The 5 relational operators that Ted Codd used to define Relational Algebra. c) Rules for generating functional dependencies from other functional dependencies. d) An algorithm to determine whether a decomposition is a Lossless Join decomposition. e) None of the Above. Question 20: sells(bar, beer, price) is a table, where bar and beer are CHAR(20) and price is FLOAT. Assume that myCon is a JDBC connection. What’s the error in the following JDBC, which is supposed to print the beers and prices for Joe's Bar? Statement stmt = myCon.createStatement(); ResultSet Menu = stmt.executeQuery(“ SELECT beer, price FROM sells WHERE bar = 'Joe''s Bar' ”); while (Menu.fetch()) { // For each value in result, get values of beer and price, and print them System.out.println(Menu.getString(1), Menu.getFloat(2)); a) Should use PreparedStatement, not Statement b) Should use Menu.next, not Menu.fetch c) Should use System.out.println(ResultSet.Float(1), ResultSet.getString(2)); not System.out.println(Menu.getString(1), Menu.getFloat(2)); d) Should use stmt.updateQuery, not stmt.executeQuery e) In the SELECT statement, 'Joe''s Bar' has the single quote symbol appearing twice (between Joe and s) in 'Joe''s Bar', but the quote symbol should only appear once, replaced by 'Joe's Bar'. Question 21: sells(bar, beer, price) is a table, and ripoffBars(bar) is another table. What does this Trigger do, assuming that price represents a dollar amount? CREATE TRIGGER PriceTrig AFTER UPDATE OF price ON sells REFERENCING OLD ROW AS old_row NEW ROW AS new_row FOR EACH ROW WHEN(new_row.price – old_row.price > 6.00) INSERT INTO ripoffBars VALUES(new_row.bar); a) If any row in sells has a beer whose price is more than 6 dollars, then the bar in that row is inserted into ripoffBars. b) If a row is inserted into sells that has a beer whose price is more than 6 dollars, then the bar in that inserted row is inserted into ripoffBars. c) If price is updated for a row in sells, and the new price is more than 6 dollars higher than the old price, then the bar in that updated row is inserted into ripoffBars. d) If price is updated for a row in sells, and the old price is more than 6 dollars higher than the new price, then the bar in that updated row is inserted into ripoffBars. e) If price is updated for a row in sells, and the new price is more than 6 dollars higher than the old price, then the update returns an error. CMPS 180, Final Exam, Fall 2017, Shel Finkelstein Multiple Choice Questions (Part I) Answered on Scantron Sheet Test Form letter: A This first Section (Part I) of the Winter 2017 CMPS 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, email and Student ID 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 Final 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, and show your UCSC id when you hand them in. Part I: (40 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 Above”, so read answer choices carefully. Question 1: The relational model has physical independence. What does “physical independence” mean for the relational model? a) When you write queries, you don’t have to know how the tables used are stored, or what indexes exist. b) If you change what’s a table and what’s a view, then your queries will still work. c) You can port a Relational Database System on different hardware and operating systems and it will still run. d) You can also write your queries using a Navigational Model, with links. e) None of the Above. Question 2: If an instance of relation R1(A,B,C) has 11 different tuples in it, and an instance of relation R2(B,C,D) has 7 different tuples in it, then how many tuples will there be in the result if the following SQL query is executed on those instances? SELECT * FROM R1, R2 WHERE R1.B = R2.B AND R1.C = R2.C; a) 0 b) Exactly 18 c) Exactly 77 d) Between 0 and 18 e) Between 0 and 77 Question 3: We discussed ACID properties of transactions. What does Durability (the “D” in ACID) mean for transactions? a) Transaction execution is as if they were executed one at a time. b) Transactions happen completely or not-at-all. c) If a transaction commits, its changes are permanent, even if there are failures, although they can be changed by later transactions. d) Business rules are always maintained by the database system. e) Uncommitted (dirty) values from one transaction are never read by any other transaction. Question 4: Assume that R is a relation, attribs is some attributes of R, and cond is a condition on R. When will the following equation be true? cond ( attribs (R) ) = attribs ( cond (R) ) a) It will always be true. b) It will be true only when R is a binary relation, with two attributes. c) It will be true only when there are two attributes in attribs. d) It will be true only when the conditions in cond refer to just the attributes in attribs. e) None of the Above. Question 5: An instance of relation R(A,B) has m tuples in it, all exactly the same, and an instance of relation S(A,B) has n 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 ) UNION ALL ( SELECT * FROM S ); a) 1 b) m + n c) m * n d) min(m, n) e) max(m, n) Question 6: Let R(A,B,C,D) be a relation, where (A, B) is the Primary Key, C can be NULL, and D is Unique and also can’t be NULL. Assume that A’s domain has 3 different values, B’s domain has 5 different values, C’s domain has 9 different values, and D’s domain has 12 different values. What is the maximum number of different tuples that can be in any instance of R? a) 8 b) 9 c) 10 d) 12 e) 15 Question 7: Which statement is false for Relational Algebra Operations, where R is a relation and C1 and C2 are conditions on the attributes of R? a) C1( C2 (R) ) = C1 AND C2(R) b) Intersection is Commutative (for union-compatible relations): R ⋂ S = S ⋂ R c) Union is Associative (for union-compatible relations): (R ∪ S) ∪ T = R ∪ (S ∪ T) d) Minus is Associative (for union-compatible relations): (R - S) - T = R - (S - T) e) Product distributes over union, assuming that S and T are union-compatible relations: R x (S ∪ T) = (R x S) ∪ (R x T) Question 8: In JDBC, what’s the difference between a Statement and a PreparedStatement? a) You supply the query or update when you create a PreparedStatement, but not when you create a Statement. b) You supply the query or update when you create a Statement, but not when you create a PreparedStatement. c) You use ExecuteUpdate on a PreparedStatement, and ExecuteQuery on a Statement. d) You use ExecuteQuery on a PreparedStatement, and ExecuteUpdate on a Statement. e) There’s no such thing as a PreparedStatement in JDBC. Question 9: What difference between FALSE and UNKNOWN in the three-valued logic used by SQL is the major justification for the need for UNKNOWN? (The correct answer must both be true and be the major justification for the need for UNKNOWN.) a) UNKNOWN is the same as NULL, but FALSE isn’t. b) When you execute a SQL query, if the result for some tuples is FALSE, those tuples don’t contribute to the answer, but if the result is UNKNOWN, then they do contribute to the answer. c) The negation of FALSE is TRUE, but the negation of UNKNOWN is UNKNOWN. d) TRUE OR UNKNOWN is UNKNOWN, but TRUE or FALSE is TRUE. e) FALSE AND UNKNOWN is FALSE, but FALSE OR UNKNOWN is UNKNOWN. Question 10: Here’s a query Q on the relation Employees(name, age, salary): Q: SELECT e1.name FROM Employees e1 WHERE EXISTS ( SELECT e2.salary FROM Employees e2 WHERE e1.salary > e2.salary AND e2.age >= 65 ); And here are 3 other queries, which are different only on the line shown in bold: Q1: SELECT e1.name FROM Employees e1 WHERE e1.salary > ( SELECT e2.salary FROM Employees e2 WHERE e2.age >= 65 ); Q2: SELECT e1.name FROM Employees e1 WHERE e1.salary > ANY ( SELECT e2.salary FROM Employees e2 WHERE e2.age >= 65 ); Q3: SELECT e1.name FROM Employees e1 WHERE e1.salary > ALL ( SELECT e2.salary FROM Employees e2 WHERE e2.age >= 65 ); Which query (or queries) are equivalent to Q? a) Just Q1 b) Just Q2 c) Just Q3 d) Just Q1 and Q2 e) Just Q1 and Q3 Question 11: Students(student_id, name, address, age, major) is a table where student_id is the Primary Key. If (98765, 'Eliza Doolittle', 'Higgins Place', 18, 'ENGL') is a tuple in that table, and the following is executed, with no other work going on: BEGIN TRANSACTION; UPDATE Students SET age = age + 1 WHERE name = 'Eliza Doolittle'; UPDATE Students SET major = 'CMPS' WHERE age = 19; ROLLBACK TRANSACTION; then afterwards, what will be in the tuple that has student_id 98765? a) (98765, 'Eliza Doolittle', 'Higgins Place', 18, 'ENGL') b) (98765, 'Eliza Doolittle', 'Higgins Place', 19, 'ENGL') c) (98765, 'Eliza Doolittle', 'Higgins Place', 18, 'CMPS') d) (98765, 'Eliza Doolittle', 'Higgins Place', 19, 'CMPS') e) None of the Above Question 12: Suppose that Customers(cust_id, name, spentvalue) is a table and GoodCustomers is a View defined on Customers that selects only the Customers whose spentvalue is more than 1000. What happens when the statement: DROP VIEW GoodCustomers is executed? a) The view GoodCustomers goes away, but the Customers table is unchanged. b) The view GoodCustomers goes away, and all the tuples in Customers whose spentvalue is more than 1000 are deleted. c) Both the view GoodCustomers and the Customers table go away. d) The statement does nothing if there are any tuples in the Customers table. e) The statement does nothing if there are any tuples in the GoodCustomers view. Question 13: Employees(name, age, salary) is a relation, and the salary for an employee named Smith is 5000. Two different transactions T1 and T2 are executed by different people at approximately the same time, both with Isolation Level READ UNCOMMITTED. T1 and T2 are the only transactions executing. T2 commits, but T1 rolls back. What is Smith’s salary after that happens? T1: BEGIN TRANSACTION; UPDATE Employees SET salary = salary + 1000 WHERE name=’Smith’; ROLLBACK TRANSACTION; T2: BEGIN TRANSACTION; UPDATE Employees SET salary = 2 * salary WHERE name=’Smith’; COMMIT TRANSACTION; a) Must be 10000 b) Must be 11000 c) Must be 12000 d) Must be either 10000 or 12000 e) Could be something other than 10000, 11000 or 12000 Question 14: Sells(bar, beer, price) and Beers(name, manufacturer) are tables in a database schema, with Primary Key underlined. You look at a million different instances of the database. Here are two statements: i. Sells.beer must be a Foreign Key corresponding to Beers.name if (for all million instances) every beer in Sells.beer also appears in Beers.nameBeers.name must be a Foreign Key corresponding to Sells.beer if (for all million instances) every beer in Beers.name also appears in Sells.beer ii. 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) All of the Above. Question 15: In OLAP, what is a dependent attribute? a) A key in a Dimension table. b) An attribute in a Fact table that is not in a key of one of the Dimension tables. c) The result of doing Roll-Up on a table. d) The result of doing Drill-Down on a table. e) None of the Above. Question 16: What is a difference between XML and the Relational Model? a) XML documents are not in First Normal Form, because XML allows arbitrarily many appearances of elements in a document, but the Relational Model requires First Normal Form. b) Relational tables are not in First Normal Form, because relational tables allow arbitrarily many appearances of elements in a document, but XML requires First Normal Form. c) Relational requires that data be in Boyce-Codd Normal Form, but XML does not. d) Relational requires that data be in Third Normal Form, but XML does not. e) None of the Above. Question 17: Why is a reason (or reasons) that having a relational schema in Boyce-Codd Normal Form (BCNF) may be better than not having a schema in BCNF? a) BCNF schemas reduce redundancy. b) BCNF schemas avoid the Update Anomaly. c) BCNF schemas avoid the Insert Anomaly. d) BCNF schemas avoid the Delete Anomaly. e) All of the Above. Question 18: What are Armstrong’s Axioms? a) Transformations which relational systems use to optimize a query. b) The 5 relational operators that Ted Codd used to define Relational Algebra. c) Rules for generating functional dependencies from other functional dependencies. d) An algorithm to determine whether a decomposition is a Lossless Join decomposition. e) None of the Above. Question 19: Employees(name, age, salary) is a table in which name is the Primary Key. How could a tuple-based CHECK require that if someone’s age is NULL then their salary must be less than 5000? a) CHECK ( age = NULL OR salary < 5000 ) b) CHECK ( age IS NULL OR salary < 5000 ) c) CHECK ( age <> NULL OR salary < 5000 ) d) CHECK ( age IS NOT NULL OR salary < 5000 ) e) CHECK ( age IS NOT NULL AND salary < 5000 ) Question 20: In this question, Employees(name, age, salary) is a table in which name is the Primary Key and age has a default value of 21. What will happen when we execute the following statement, assuming that there was no tuple with name ‘Shaw’ before the statement was executed? INSERT INTO EMPLOYEES(name) VALUES (‘Shaw’); a) There will always be an error because values for age and salary weren’t supplied. b) There will always be an error because the value for salary wasn’t supplied. Not supplying a value for age won’t be a problem because it has a default value. c) There might be an error because the value for salary wasn’t supplied, but that will happen only if salary is not allowed to be NULL. d) There will never be an error. e) None of the Above. [Show More]

Last updated: 1 year ago

Preview 1 out of 52 pages