CSE 444 Midterm, July 28, 2010 Page 1 of 8 CSE 444 Midterm Exam July 28, 2010 Name ___________________________________ Question 1 / 28 Question 2 / 20 Question 3 / 16 Question 4 / 20 Question 5 / 16 Total / 100 The exam is open textbook and open lecture notes, including any marginal or other notes you have made during lecture. Please put away all other materials including projects, homework, old exams, and sample solutions. No computers, electronics, communications, or other devices are permitted. Please wait to turn the page until everyone has their exam and you are told to begin. CSE 444 Midterm, July 28, 2010 Page 2 of 8 Question 1. SQL (28 points, 7 each part) We have a small database with three tables to keep track of mountains and the people who climb them. The tables and their attributes are as follows: Mountain(name, height, country, state) Climber(cid, name, sex) Ascent(cid, mountain, month, year) • Mountain: name (string) is assumed to be unique for this problem; height in feet (integer); country and state (or province, district, etc.) where the mountain is located (strings) • Climber: cid (unique integer), name (string), sex (string, either ‘M’ or ‘F’, and never null). Different climbers may have the same name, but they will have different cid nu mbers. • Ascent: record of a single climb by a single climber. cid is a foreign key referencing Climber, mountain is a foreign key referencing a name in Mountain, month and year are integers. We assume that no climber climbs a single mountain more than once in a given month and year. (a) Write a SQL query that gives the names of all climbers who have climbed the tallest mountain. You may assume that there is only one mountain that is taller than all of the others. If two or more climbers have the same name, it is your choice whether that name appears more than once. (b) Write a SQL query that returns the percentage of women among the climbers that ascended the mountain named ‘Rainier’ in July 2007. You may assume there are at least one man and one woman in the list of climbers that ascended Rainier during that month. (continued next page) CSE 444 Midterm, July 28, 2010 Page 3 of 8 Question 1. (cont) Schemas repeated for convenience: Mountain(name, height, country, state) Climber(cid, name, sex) Ascent(cid, mountain, month, year) (c) Write a SQL query that returns a list of every mountain in the database and the number of different climbers who have ascended each mountain, sorted in descending order by number of climbers. Climbers may have climbed the same mountain many times, but each unique climber should only be counted once in the total for each mountain. If several mountains have the same number of climbers, those mountains with the same number may be listed in any order. (d) Write a SQL query that determines the state in the country ‘USA’ that has the most mountains at least 12,000 feet high, and returns the name of that state and a list of those mountains, sorted by mountain name. You may assume there is only one state with this “largest number of tall mountains”. You may repeat the name of the state along with each mountain name in the output if that is more convenient. CSE 444 Midterm, July 28, 2010 Page 4 of 8 Question 2. Conceptual design (20 points) We would like to design a database to keep track of a zoo. (a) Give an E/R diagram that captures the following entities and relationships: • The zoo employees are known as zookeepers. Each zookeeper has a name, a unique employee ID number, and a salary. He or she may work in any number of exhibits, or none at all (administrative staff do not work in exhibits, for example). A zookeeper does not have to work in an exhibit to head it. • The zoo has several exhibits. Each one has an exhibit name and a head zookeeper who is in charge of it. A zookeeper may head many exhibits, but each exhibit has at most one head. • Each zookeeper has at most one manager, who is another zookeeper (some zookeepers, such as the director of the zoo, have no manager). A zookeeper can manage any number of employees, including none. • The zoo has many animals. Each has a name, species, unique ID number, and a weekly budget for its care. Each animal is tended by at least one zookeeper, and may appear in at most one exhibit. Some animals, such as those housed in the veterinary clinic, are not in any exhibit. (continued next page) CSE 444 Midterm, July 28, 2010 Page 5 of 8 Question 2 (cont.) (b) Give SQL CREATE TABLE statements for tables that implement your E/R diagram from part (a). You should give the attribute names and types for each table, and clearly indicate which attributes are keys, which others are unique, and which are foreign keys referencing other tables. CSE 444 Midterm, July 28, 2010 Page 6 of 8 Question 3. BCNF (16 points) Suppose we have a relational schema R(A,B,C,D,E) with the following functional dependencies: • A ‐> E • C ‐> D • A,B ‐> C,D Decompose this relation, if needed, into collections of relations that are in BCNF. At each step, show your work and explain which dependency violation(s) you are corr ecting. Be sure the steps in the decomposition are clear and that it is clear which tables are the final ones. Also, identify the keys of each table by underlying the attribute(s) that make up the key. Hint: there may be more than one correct solution to the problem. CSE 444 Midterm, July 28, 2010 Page 7 of 8 Question 4. Serialization (20 points). For each of the following schedules, i. Draw the precedence graph for the schedule. ii. If the schedule is conflict‐serializable, give the equivalent serial schedule. If the schedule is not conflict‐serializable, explain why not. iii. If the schedule is not conflict‐serializable, but there still is an equivalent serial schedule, give that schedule and explain why it is equivalent. (a) r2(X) r1(X) r2(Z) w2(Z) w2(X) r1(Z) w1(X) (b) r2(X) r3(Y) w3(X) r1(Y) w2(X) w1(Y) w1(X) CSE 444 Midterm, July 28, 2010 Page 8 of 8 Question 5. (16 points) Assorted short questions. (a) In a system with a simple undo log, when a transaction wants to commit it must first wait until all of its data pages have been written to disk. True or false? _____________ (b) In a system with a simple redo log, changes to the
View Full Document
Unlocking...