Computer Science 186 - Fall 1998 - Hellerstein - Midterm 2

Computer Science 186 - Fall 1998 - Hellerstein - Midterm 2...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
CS186 file:///C:/Documents%20and%20Settings/Jason%20Raftery/Desktop/R. .. 1 of 4 5/12/2006 8:35 PM CS186 Fall 1998 Midterm 2 J.Hellerstein Midterm Exam: Introduction to Database Systems This exam has four problems, for a total of 70 points. There are also 10 points of extra credit available. 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! Cost Estimation 1. (primary keys in bold ) Clients(name, id , birthday) -5000 tuples, 50 bytes/tuple (2 tuples/page) Cases( caseid , clientid, lawyer, courtdate) -10,000 tuples, 2000 bytes/tuple, each attribute of the tupe is 500 bytes long. (2 tuples/page) Some additionaly notes on this schema: A page holds 4000 bytes caseid has values ranging from 0 to 10,000 clientid has values ranging from 50 to 5050, and is a foreign key to Clients. Unless stated otherwise, a B+Tree index is available on each of the primary keys. [2 points] Consider the following two queries: a. SELECT* FROM Clients C WHERE C.birthday=4/25/77 i. SELECT* FROM Cases S WHERE S.lawyer=’Georgia Thomas’ ii. Assume that the two queries return the same number of qualifying tuples. If you’re building indexes on the selected attributed to speed up the queries, for which is a clustered index( as opposed to unclustered) more important, and why? Be sure to state any assumptions that you make. [4 points] Estimate the number of tuples that would pass the selection Cases.casesid = Cases.clientid. What assumptions are you making in this approximation? a. [9 points] Assume that you have two indexes on Cases. The first is a clustered B+tree of height 2 on (lawyer, caseid), containing 201 pages (note that key compression makes this possible, even though the keys themselves are 500 bytes long!). The second is an unclustered B+tree of height 2 on (caseid) containing 101 pages. Consider the query: b. SELECT lawyer
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/17/2009 for the course CS 186 taught by Professor Staff during the Spring '08 term at University of California, Berkeley.

Page1 / 4

Computer Science 186 - Fall 1998 - Hellerstein - Midterm 2...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online