CS 3335Final ExamMay 5, 2010100 points Name:1. Write an SQL query based on the chess tables used in class all semester. A copy isincluded at the end of the exam.(a) (10)Create a view consisting of the names of all players and each opening they haveused.(b) (10)Using your view, find all players who have used all openings used by players namedJohn Smith.2. Consider a chess tournament in which every player plays every o t her player. A playerreceives 1 point for a win, one half point for a draw, and no points for a loss. The winneris the player with the most points at the end of the tournament. Players are given uniqueIDs, but they also need a name and a nationality in the database. Tournaments are heldat one location over several days, and all have a unique name.(a) (10)Create an ER diagram for recording chess tournaments.Page 2(b) (10)Use SQL to create the database ta bles required by your ER diagram.Page 33. (5)List each of Armstrong’s Axioms a nd its definition.4. (10)Provide a schedule that consists of a t least three transaction such that the schedule is(a) serializable; (b) cascadeless and (c) not strict. No transaction may perform the sameoperation on the same data item more than once (i.e., a transaction can only do R(X)one; it may read any other data item and it may also write X, but it cannot R(X) again).Page 45. (10)Assume a B+−tree has 4 pointers. Insert the following values into the tree in this order:2, 3, 5, 7, 9, 11, 13, 17, 19, 23. Show the B+−tree after every node split.Page 56. (10)Use k-means to cluster the following 1 dimensional points into 3 clusters: 2, 3, 5, 7, 9,11, 13, 17, 19, 23.Page 67. For each set functional dependencies, assume R = (A, B, C, D, E) is the relation andthe domain of each attribute is integer. In the blank, put the highest normal form forR given the rules presented in class.(a) (3)A → BCDE, BC → A(b) (3)AB → CDE, BC → A(c) (3)AD → BCE, BC → A(d) (3)A → BCDE, BC → A, B → C(e) (3)AB → CDE, BC → D8. Fill in the blank.(a) (1)requires that if a transaction executes on a databasewith all constraints satisfied, then it will terminate with all constraints satisfied(b) (1)requires that either all of a transactions updates areperformed on the database, o r none of them are(c) (1)JSP stands for(d) (1)Isolation allows to behave as if they are the only onesexecuting(e) (1)Writing theto stable storage is the com-mit point for a transaction(f) (1)Thealgorithm is used to find the large item sets inassociation problems(g) (1)The WHERE clause in an SQL statement is translated to thesymbolin relational algebra(h) (1)The primary cost of a data base query is measured in(i) (1)ORM stands for(j) (1)requires that once a transaction has committed, all ofits updates are permanentPage