Download Buffer Manager -Introduction to Database Systems - Exams and more Exams Introduction to Database Management Systems in PDF only on Docsity!
UNIVERISTY OF CALIFORNIA, BERKELEY
College of Engineering Department of EECS, Computer Science Division CS186 J. Hellerstein Spring 2003 Final Exam
Final Exam: Introduction to Database Systems
This exam has seven problems, each worth a different amount of points. Each problem is made up of multiple questions. You should read through the exam quickly and plan your time-management accordingly. Before beginning to answer a question, be sure to read it carefully and to answer all parts of every question! Please do not spend time explaining your answers unless we explicitly ask that you do so. We will not be giving extra or partial credit for explanations unless we ask for them. Good luck! You must write your answers on the exam. You also must write your name at the top of every page , and you must turn in all the pages of the exam. Do not remove pages from the stapled exam! Extra answer space is provided in case you run out of space while answering. If you run out of space, be sure to make a “forward reference” to the page number where your answer continues. Do not write in this space Class Account ____________________________
- Recovery [20 points] In this question we explore the interplay between buffer management and recovery. We present four scenarios below. For each scenario, you must follow the following guidelines: — Choose a maximally efficient protocol! — Among all protocols with the same level of efficiency, choose the simplest. Each scenario corresponds to exactly one of the four quadrants in the chart at the bottom of the page. Place the letter of each scenario (a, b, c, d) in the appropriate quadrant. Note that while each letter goes in only one quadrant, it is possible that some quadrants will have more or less than one letter in them! [ points each] a. Scenario: Traditional ARIES. You have been told to implement the ARIES recovery protocol as discussed in class. b. Scenario: Buffer Pool Bigger than Database. Modern servers ship with as much as 32GB of RAM. Hence for many customers, their databases will never be larger than available RAM. You have a startup company that is designing a special-purpose DBMS for these customers. c. Scenario: Before-Image Logging. In order to keep your logs more compact, you store only the “before-image” of the data in update log records, and not the “after-image”. (Recall that the before-image has the state of the page before the update; the after-image would have also shown the state of the page after the update.) d. Scenario: Untrusted Buffer Manager. No matter how many times you explain it, the engineers implementing the buffer manager cannot understand the log manager. So you’re going to have to choose a protocol that can handle any mix of page replacement decisions. No Steal Steal Force No Force
- Functional Dependencies and Normalization [20 points] Consider the relation schema R = (A, B, C, D, E, F) and the set of functional dependencies F: A->B, A->C, BC->E, BC->D, E->F, BC->F Note that in all the following, you will never have to compute F+, the closure of F! a. List the minimal candidate key(s) for R. Write ‘none’ if you think there are no candidate keys. [2 points] b. List the FDs in F that violate BCNF. (Hint: There are four) [4 points] c. Is R in 3NF (yes or no)? [2 points] d. Is F a minimal cover? [2 points] (continued)
e. Suppose we decompose R into the following tables: R1 = (B, C, E) R2 = (B, C, F) R3 = (B, C, D) and R4 = (A, B, C). This decomposed schema is indeed in BCNF (you can trust us on this!) Unfortunately, this decomposition is not dependency-preserving; in particular, the dependency E->F cannot be checked on a single table. A CHECK ASSERTION can be used to enforce E->F. Complete the following SQL statement for this particular CHECK ASSERTION needed to guarantee E->F. [8 points] CREATE ASSERTION checkDep CHECK ( NOT EXISTS ( SELECT * FROM R1, R WHERE _______________________ GROUP BY _____________________ HAVING COUNT(__________________________)__________)) f. Why might the ASSERTION in (e) be expensive? [2 points] i. Updates to R1 and R2 are frequent ii. Inserts to R1 and R2 are frequent iii. Insertions to R2 are frequent; R1 rarely changes. iv. Reads to R1 and R2 are frequent v. (i) and (ii) vi. (i), (ii), (iii) vii. All of the above Answer (choose one ): ___________
b. The database is still running very slowly even after you recommended the right indexes in part (a)! A careful workload study reveals that in practice, the following two transactions are extremely frequent (far more than any other queries or updates):
- T1: Find the average age of all graduate students group by aid
- T2: Update the GPA of graduate student g (the value of g may be different each time T2 is run) Traces show that the above two queries resulted in concurrency control bottlenecks, due to lock contention on the GraduateStudent table. Tuple granularity locks are obtained. The following solutions have been proposed to alleviate the problem. Choose the single letter that gives the most correct options; if no correct options are listed, choose (ix). [5 points] i. Vertically partition (i.e. decompose) the GraduateStudent table into two separate tables such that (SID,age,aid) is in one table, and (SID, sex, dept, GPA) is in another. ii. Horizontally partition the GraduateStudent table according to aid field. Graduate Students who stay in even numbered apartments are stored in a different table from those that are odd numbered. iii. Make use of page granularity locks iv. Use non-strict two-phase locking (2PL) v. Create an unclustered B+Tree index for GraduateStudent on <aid, age> vi. Either (i) or (ii) would work vii. Either (i) or (v) would work viii. Either (iii) or (iv) would work ix. None of the above. Answer (choose one ): ___________
5. Query Optimization [15 points] Consider the following relational schema and SQL query: Student (SID, DID, Enroll_Year, Nationality) Department (DID, Name, Building_Num, Telephone, FID) Finance (FID, Budget, Expenses, …) SELECT D.Name, F.Budget FROM Student S, Department D, Finance F WHERE S.DID = D.DID and D.FID = F.FID AND D.Building_Num > 5 AND D.Building_Num <=10 AND S.Enroll_Year = 2000 OR S.Enroll_Year = 2001; Here are some statistics: — Building Numbers range from 1 to 20 inclusive (i.e. 1 and 20 are both valid numbers). — Each building has the same number of departments — Students’ enrollment year (Student.Enroll_Year) ranges from 1997 to 2002, and is distributed according to the following table: 1997 1998 1999 2000 2001 2002 5000 1400 2000 3000 7000 1600 — Number of Tuples (pages) per relation: - Student: 20000 (2000 pages) - Department: 100 (10 pages) - Finance: 100 (10 pages) (continued)
6. Concurrency control [16 points] a) Consider the following modified definition of serializability: A schedule S is serializable iff it produces the same database state as a serial schedule T, where the transactions in T are exactly those in S, and are ordered in T according to their first appearance in S. Is this definition: [3 points] i. Correct? ii. Overly restrictive: i.e. there are some serializable schedules not covered by this definition? iii. Overly permissive: i.e. there are some unserializable schedules that are covered by this definition? iv. (ii) and (iii) Answer (choose one ): ___________ b) Bob and Anne share a bank account for their business. Today they went to the bank at the same time. Draw the dependency graph for the schedule below. You do not need to label any edges in the graph. [3 points] T_Bob T_Anne Description Action Description Action Looks at checking balance
R(C)
Looks at savings balance
R(S)
Looks at checking balance
R(C)
Transfer $ from checking to savings
W(C)
W(S)
Withdraw $ from checking
W(C)
commit commit
c) Is the schedule in (b) conflict serializable? If so, give an equivalent serial schedule. If not, enumerate all the serial schedules, and explain how Bob and Anne’s experience would be changed in each. [5 points] d) Suppose that the database system at the bank implemented strict 2-phase locking as we studied in class. Assume that Bob and Anne’s requests for actions arrive in the same order as in (b), but if either of them is blocked while waiting for a lock, their actions stop arriving until they acquire the lock (after which time they continue as fast as they can). Describe what happens in that scenario: a few words should suffice. [5 points]
SCRATCH
SCRATCH
