ER
b a c R F E d isa e G S H h f g If we translate this diagram to relations in the normal way for E/R diagrams described in the textbook, which relation schema would not be in the resulting database schema? (A) E(a,c,b) (B) S(a,c,f,g) (C) F(c,d) (D) R(a,c)
If we instead used the “object-oriented” approach to translating this E/R diagram to relations, how many relation schema would be different (when compared to the E/R-to-relations translation), either in their schema, or their set of tuples, or both? (A) 1 (B) 2 (C) 3 (D) 4
1 of 9
Convert the following E/R diagram (with details omitted) to a relational database schema using the object-oriented method. How many relation schemas will be produced? (Assume that each entity set has its own special attributes.)
A isa B isa D
(A) 1
isa C
(B) 4
(C) 5 2 of 9
(D) 6
Relational Theory
Given a relation R(A,B,C,D,E) and FDs AB?CE, B?D, and D?E, which of the following FD’s can not be inferred? (A) AD?CE (B) BC?D (C) AB?A (D) B?E
Given a relation R(A,B,C,D) with FD: A?BC, which of the following MVD’s can not be inferred? (A) A??BC (B) A??B (C) A??BD
(D) None of the above.
Suppose we have a relation R(A,B,C,D,E) and the FD’s A?DE, D?B, and E?C. If we project R (and therefore its FD’s) onto schema S(A,B,C), what is true about the key(s) for S? (A) Only ABC is a key (B) Only A is a key (C) Only DE is a key
(D) A, B, and C are each keys
Suppose we are given a relation schema R(A,B,C,D,E) with functional dependencies A?B, BC?D and E?C. If we project R onto S(B,C,D,E), which of the following functional
dependencies holds in S and is not a BCNF violation for S? (A) BC?D (B) BE?D (C) B?E (D) E?C
Suppose that R(A,B,C,D) satisfies MVD:A??B and it is known that R has tuples (a1,b1,c1,d1), (a1,b2,c2,d2), and (a2,b1,c1,d2). How many tuples must R have at least? (A) 3 (B) 4 (C) 5 (D) 6
3 of 9
Relational algebra and Datalog
In the following question, assume R and S are bags, and the operators are bag operators. Q1: (R∪ S) – (R∩S) Q2: (S–R) ∪ (R–S)
(A) Q1 and Q2 produce the same answer.
(B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) Q1 and Q2 produce different answers.
Given relations P(A,B,C) and Q(C,D), which of the following relational algebra expressions is equivalent to this Datalog rule:
Answer(x,w) ? P(x,y,z) AND Q(y,w) AND x (A) ?P.A,Q.D(?P.A (D) ?P.A,Q.D(?P.A 4 of 9 SQL Q1: SELECT DISTINCT a FROM R WHERE b > 10; Q2: SELECT a FROM R WHERE b > 10 GROUP BY a; (A) Q1 and Q2 produce the same answer. (B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) Q1 and Q2 produce different answers. Consider the following two SQL queries on relations R(a,b) and S(c): Q1: SELECT a FROM R WHERE b > ALL (SELECT c FROM S); Q2: SELECT a FROM R WHERE b > ANY (SELECT c FROM S); (A) Q1 and Q2 always produce the same answer. (B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) None of the above. Given R(A,B)={('a',10),('b',20),('c',30)}, and execute the following three statements. What is the result of the last statement? create view V as select A from R where B>10; insert into V values('d'); select count(*) from V; (A) 1 (B) 2 (C) 3 (D) 4 5 of 9 Given a relation R(x) and two queries as follows: Q1: SELECT x FROM R; Q2: (SELECT x FROM R) UNION (SELECT x FROM R); (A) Q1 and Q2 produce the same answer. (B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) None of the above. In the 3-valued logic used by SQL, suppose x has the value NULL and y is TRUE, the result for (x > 1) OR y is: (A) FALSE (B) UNKNOWN (C) TRUE (D) NULL Consider the following two SQL queries on relations R(a,b) and S(b,c): Q1: (R NATURAL FULL OUTER JOIN S) UNION (R NATURAL JOIN S); Q2: (R NATURAL LEFT OUTER JOIN S) UNION (R NATURAL RIGHT OUTER JOIN S); (A) Q1 and Q2 produce the same answer. (B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) Q1 and Q2 produce different answers. Suppose that relation R(A,B) has 100 tuples and S(B,C) has 50 tuples. The number of tuples in R NATURAL LEFT OUTER JOIN S is at most: (A) 50 (B) 100 (C) 150 (D) 5000 6 of 9 Assume that a relation R(a,b) has no NULL’s but may have duplicates. Compare the following two queries: Q1: SELECT count(*) FROM R GROUP BY a; Q2: SELECT count(b) FROM R GROUP BY a; (A) Q1 and Q2 produce the same answer. (B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) Q1 and Q2 produce different answers. In the following, the results of Q1 and Q2 should be taken to be the result of the final SELECT * FROM R. Assume that the schema of relation R is R(a,b). Q1: DELETE FROM R WHERE a=10; INSERT INTO R VALUES(10,5); SELECT * FROM R; Q2: UPDATE R SET b=5 WHERE a=10; SELECT * FROM R; (A) Q1 and Q2 produce the same answer. (B) The answer of Q1 is contained in the answer of Q2. (C) The answer of Q2 is contained in the answer of Q1. (D) Q1 and Q2 produce different answers. 7 of 9 Integrity Suppose we have the following table declarations: CREATE TABLE A (w INT PRIMARY KEY); CREATE TABLE B (x INT PRIMARY KEY REFERENCES A(w) ON DELETE SET NULL); CREATE TABLE C (y INT REFERENCES A(w)); CREATE TABLE D (z1 INT REFERENCES B(x) ON DELETE SET NULL, z2 INT REFERENCES A(w) ON UPDATE CASCADE); Consider the following scripts: (1) DELETE FROM C; DELETE FROM B; DELETE FROM A; DELETE FROM D; (2) DELETE FROM C; DELETE FROM D; DELETE FROM A; DELETE FROM B; (3) DELETE FROM B; DELETE FROM C; DELETE FROM D; DELETE FROM A; Which of the above scripts will empty all four tables, without error? (A) (3) only (B) (1) only (C) (2) and (3) only (D) (1) and (3) only Suppose R is declared as CREATE TABLE R (a INT REFERENCES S(b) ON UPDATE CASCADE); Which of the following modifications on S will never be rejected for violating the referential integrity constraint? (I) DELETE FROM S; (II) UPDATE S SET b = 10; (III) INSERT INTO S(b) VLAUES(10); (A) (I)(II) (B) (II)(III) (C) (I)(III) (D) None of (I),(II),(III) 8 of 9 Suppose we want to enforce the FD A?B on R(A,B,C) using triggers. Consider the following operations. For which of them do we need to create a trigger on R? (A) delete on R (B) update of C on R (C) insert on R (D) All of the above Suppose we want to enforce the MVD: A??B on R(A,B,C) using triggers. Consider the following operations. For how many of them should we create a trigger? (1) INSERT ON R (2) DELETE ON R (3) UPDATE OF A ON R (4) UPDATE OF B ON R (5) UPDATE OF C ON R (A) 2 (B) 3 (C) 4 (D) 5 9 of 9 Suppose you find the following statement in a program: ... FETCH FROM myCursor INTO myVar ... Then the program may be written in: (A) Embedded SQL only (B) SQL/PSM only (C) Both Embedded SQL and SQL/PSM (D) None of the above 10 of 9 Transaction Assume that we have a relation Employee(ID, salary) where ID is the key, and that Employee initially has two tuples (A,20) and (B,30). Consider the following two concurrent transactions: T1: BEGIN TRANSACTION; UPDATE Employee SET salary = 2*salary WHERE ID = ’A’; UPDATE Employee SET salary = salary+10 WHERE ID = ’A’; COMMIT; T2: BEGIN TRANSACTION; SELECT AVG(salary) AS sal1 FROM Employee; SELECT AVG(salary) AS sal2 FROM Employee; COMMIT; Suppose that T1 and T2 execute at isolation level SERIALIZABLE and READ COMMITTED respectively, which of the following is an impossible pair of values for sal1 and sal2 returned by T2? (A) (35,40) (B) (40,40) (C) (25,25) (D) (25,40) 11 of 9 Privilege Suppose user A is the owner of relation R(a,b). The following sequence of grants and revocation occurs: A: GRANT update on R to B A: GRANT update(a) on R to C WITH GRANT OPTION C: GRANT update(a) on R to B WITH GRANT OPTION A: REVOKE update(a) on R FROM C CASCADE Which of the following statement can user B do? (I) UPDATE R SET a=a+1; (II) GRANT update on R to C; (III) SELECT * FROM R; (A) only (I) (B) (I)(II) (C) (I)(II)(III) (D) None of (I)(II)(III) 12 of 9 OO/OR Consider the following ODL declarations: class X (key A,B){ class Y (key C) { attribute integer A; attribute integer C; attribute integer B; attribute integer D; relationship Y R1 relationship Set inverse Y::R2; inverse X::R1; }; }; Which of the following relation schemas are produced according to the standard translation in the textbook? (A) X(A,B) and Y(A,C,D) (B) X(A,B,C) and Y(C,D) (C) X(A,B) and Y(C,D) (D) X(A,B), Y(C,D) and R(A,B,C) 13 of 9 Datalog/Recursion The following queries are recursive Datalog, and as for all Datalog queries, the result is a set, not a bag. Q1: Path(x,y) <- Arc(x,y) Path(x,y) <- Path(x,a) AND Path(a,b) AND Path(b,y) Q2: Path(x,y) <- Arc(x,y) Path(x,y) <- Path(x,z) AND Path(z,y) (A) Q1 and Q2 produce the same answer. (B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) Q1 and Q2 produce different answers. Given a relation R(A) = {1,2,3}. Consider the following query that involves a recursively defined relation P(X,Y): WITH RECURSIVE P(X,Y) AS (SELECT A AS X, A+1 AS Y FROM R) UNION (SELECT COUNT(*) AS X, MAX(Y) AS Y FROM P) SELECT COUNT(*) FROM P; What value is in the result of this query? (A) 3 (B) 4 (C) 5 (D) None of the above 14 of 9 XML Consider the following XML DTD: ]> How many elements are there in the smallest XML document (i.e., a document containing the fewest possible elements) that conforms to the above DTD? Note that text values are not elements. (A) 3 (B) 4 (C) 5 (D) 6 Assume that the XML document in file “XYZ.xml” conforms to the following DTD: ]> Consider the following two queries in XQuery: Q1: for $x in doc(“XYZ.xml”)/A/B return $x Q2: let $x := doc(“XYZ.xml”), $y := $x/A/B return $y (A) Q1 and Q2 produce the same answer. (B) The answer to Q1 is always contained in the answer to Q2. (C) The answer to Q2 is always contained in the answer to Q1. (D) Q1 and Q2 produce different answers. 15 of 9