CSE444 Introduction to Database Systems Fall 1998Homework 4 - Due 12/9/981. Consider a database schema with the following relations:Student (ssn, name)Prof (ssn, name)Course (number, instructor-ssn, title, credits, room#)Enroll (student-ssn, course#)Room (number, capacity, bldg#)Building (number, name)Write an OQL query (with a single SELECT) that lists the names of all buildings which house any course either taught by "Smith" or enrolled in by "Jones." Use at least 1 subquery in the FROM clause of your SELECT statement.2. Consider a relation R(A,B,C) containing 1,000,000 tuples, where each page on disk can hold 10 tuples. The relation R is stored as a sorted file on the attribute A, which is also a key of R. R also has secondary indexes: a B+-tree index on the attribute A, and a hash index on A. (Recall that on average, the time to retrieve a tuple using a hash index is 1.2 I/Os.) The values of R.A range uniformly from 0 to 50,000. For each of the following relational algebra queries, compute the expected number of I/Os required to answer the query. State any additional assumptions you are making in the computation. -- Access the sorted file for R directly -- Use a clustered B+-tree index on R.A -- Use a hashed index on attribute R.Aa. select{A < 3000} R$b. select{A = 2000} R$c. select{A != 2000} R$d. select{A > 20,000 AND A < 20,200} R$3. Consider the following schema:Subscriber (ssn, name, city-name)City (city-name, route-code, region-id)Region (region-id, name, delivery-code, report)Assume that each Subscriber record is 30 bytes long, each City record is 50 bytes long, and each Region record is 1000 bytes long on average. There are 15,000 tuples in Subscriber, 5000 tuples in City, and 1500 tuples in Region. Page size is 2000 bytes, and there are 20 pages available in the buffer pool. You can assume uniform distribution of values whenever necessary.Consider the query:SELECT *FROM Subscriber S, City CWHERE S.city-name = C.city-nameSuppose that there is a non-clustered hash index on city-name of Subscriber. Also, note that city-name is NOT a key of City. List all the plans that are considered and identify the plan with the lowest cost.Note:- You need to consider the following join algorithms: block nested-loop join, hash join, and sort-merge join.- The cost metric of plans is the number of I/O operations. Ignore the cost of writing out the final result.- Remember to make explicit any other assumptions you are making.4. Suppose you have the following two relations:Attends(student-name, student-age, classNo)Teaches(prof-name, prof-age, classNo)describing the enrollment and teaching assignments in a particular quarter. In each of the following queries, the query will be evaluated by first scanning all relations in the FROM clause. Using predicate-pushdown, which predicates can the query processor apply to the relations immediately after the scan?a. SELECT student-name, prof-name FROM Attends A, Teaches T WHERE A.classNo = T.classNo and T.classNo < 400 and A.student-age > T.prof-age and A.student-age < 45b. SELECT A.classNo, Max(prof-age) FROM Teaches T GROUPBY classNo HAVING Max(prof-age) > 50c. SELECT A.classNo, Max(prof-age), Min(prof-age) FROM Teaches T GROUPBY classNo HAVING Max(prof-age) > 50 and Min(prof-age) >
View Full Document
Unlocking...