AUniversity of CaliforniaDepartment of Information and Computer ScienceICS 184 – Database SystemsFinal ExamFall 2000Max. Points: 125(Please read the instructions carefully)Instructions:- Total time for the exam is 2 hours. No extra time will be awarded so budget your timeaccordingly. - The exam is closed books and closed notes.- The exam is divided into 2 sections. - 15 multiple choice questions – a correct answer will get you 3 points, an incorrect answer will cost you 1 point and left blank answer will get you 0 points. - 6 short answer questions of which you can choose 3 to answer. Each question is worth 10 points for a total of 30 points. There will be no negative grading for short answer questions.- If you find ambiguities in a question or in the schema, write down the interpretation you are taking, and then answer the question based on your interpretation.- This exam contains 19 pages. You can use pages 18-19 as scratch paper.NAME: ALIAS:Part Points ScoreQuestionsabout the course2 2I 68II 30Total 100 ICS 184 Winter 2000 Final ExamPage 1 of 21Question 1: Consider a relation R(A,B,C,D,E) with the following functional dependencies: ABC  DE andD  AB. The number of superkeys of R is: (a) 2(b) 7(c) 10 *****(d) 12Question 2: Consider the following E/R diagram:Below are three possible relationship sets for this E/R diagram:A B C DI. a1b1c1d1a1b1c1d2A B C DII. a1b1c1d1a1b1c2d2A B C D ICS 184 Winter 2000 Final ExamPage 2 of 21ACBDRIII. a1b1c1d1a1b2c1d1You may assume that different symbols stand for different values, e.g., d1 is definitely not equal to d2. Which of the above could not be the relationship set for the E/R diagram?(a) I only *****(b) I and II only(c) II only(d) I, II and III.Question 3: One of the following four expressions of relational algebra is not equivalent to the other three. They are all based on the relations R(A,B) and S(B,C). Indicate which isnot equivalent to the others.(a) AB (R ⋈ S)(b) R ⋈ B(S)(c) R  (A(R) x B(S))(d) A,R.B(R x S) *******Question 4: Of the following three equivalence’s between expressions of relational algebra, each involving relations R(A,B) and S(C,D) (note the schema of S is different from that of the question above), which is true?(a) A,B(R x S) = R(b) R - T(A,B)(S) = T(A,B)(S - U(C,D)(R))(c) A,B,D(R S) = R ⋈ T(B,D) (S) ********(d) none of the above (i.e., they are all false) ICS 184 Winter 2000 Final ExamPage 3 of 21⋈ B=CQuestion 5: Below is an E/R Diagram:Indicate which of the ODL specifications best mimics the intent of this E/R diagram. In both E/R and ODL, we have omitted mention of all attributes, which you may thus ignore.(a) Interface A {relation Set<B> R inverse B::R;};Interface B {relation Set<A> R inverse A::R;}; relation Set<C> S inverse C::S;};Interface C {relation Set<B> R inverse B::S;};(b) Interface A {relation B R inverse B::R;};Interface B {relation Set<A> R inverse A::R;}; relation C S inverse C::S;};Interface C {relation Set<B> S inverse B::S;}; ************(c) Interface A {relation set <B> R inverse B::R;};Interface B {relation A R inverse A::R;}; relation Set<C> S inverse C::S;};Interface C {relation B S inverse B::S;};(d) Interface A {relation B R inverse B::R;};Interface B {relation A R inverse A::R;}; relation C S inverse C::S;};Interface C {relation B S inverse B::S;}; ICS 184 Winter 2000 Final ExamPage 4 of 21ACBRSThe following 4 questions are based on a relationEmps(empID, ssNo, name, mgrID)giving for a set of employees their employee ID (assumed unique), their social-security number (also unique), the name of the employee (not necessarily unique, and the employee ID of the manager of the employee. Assume that the president is his/her own manager, so every employee has a unique manager. You may assume there are no duplicate tuples in this relation.Question 6: Here are two possible ways to declare the relation Emps.I. CREATE TABLE Emps ( empID INT, ssNo INT, name CHAR(50), mgrID INT, UNIQUE (empID), PRIMARY KEY (ssNo), FOREIGN KEY mgrID REFERENECES Emps (empID));II. CREATE TABLE Emps ( empID INT PRIMARY KEY, ssNo INT UNIQUE, name CHAR(50), mgrID INT REFERENECES Emps (empID));Which, if any, of the two declarations above will correctly (in SQL2) declare the relation Emps?(a) Both I and II(b) I only(c) II only ******************(d) Neither I nor IIQuestion 7: Suppose we wish to find the ID’s of the employees that are managed by people who are managed by the employee with ID 123. Here are two possible queries:I. SELECT ee.empID FROM Emps ee, Emps f WHERE ee.mgrID = f.empID AND f.mgrID = 123;II. SELECT empID FROM Emps ICS 184 Winter 2000 Final ExamPage 5 of 21WHERE mgrID IN (SELECT empID FROM Emps WHERE mgrID = 123);Which, if any, of the two queries above will correctly (in SQL2) get the desired set of employee ID’s?(a) Both I and II ***********(b) I only(c) II only(d) Neither I nor IIQuestion 8: Suppose we wish to find the ID’s of the employees who do not manage any employee named “Sally.” Here are two possible queries:I. SELECT mgrIDFROM EmpsWHERE NOT EXISTS(SELECT * FROM Emps WHERE NAME = ‘Sally’);II. SELECT mgrIDFROM EmpsWHERE NOT (empID = ANY(SELECT EmpID FROM Emps WHERE name = ‘Sally’));Which, if any, of the two queries above will correctly (in SQL2) get the desired set of employee ID’s?(a) Both I and II(b) I only(c) II only(d) Neither I nor II **********Question 9: We wish to assert that no one can manage more than 10 employees. Here aretwo possible SQL2 assertions:I. CREATE ASSERTION I CHECK(NOT EXISTS( SELECT mgrID FROM Emps GROUP BY mgrID HAVING COUNT(*) > 10));II. CREATE VIEW mgrCounts AS SELECT mgrID, COUNT(*) AS cnt FROM Emps GROUP BY mgrID; ICS 184 Winter