hw3SolFinal

hw3SolFinal - Homework 3 Due on Monday October 26: Exercise...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Homework 3 Due on Monday October 26: Exercise 4.4, 5.12 in QBE only (ignore the Datalog part) 12.3, 12.4, 12.15, 13.3a, 13.3b, 13.10 4.4 SQL allows a foreign-key dependency to refer to the same relation, as in the following example: create table manager (employee-name char(20) not null, manager-name char(20) not null, primary key (employee-name), foreign key (manager-name) references manager on delete cascade ) Here, employee-name is a key to the table manager, meaning that each employee has at most one manager. The foreign-key clause requires that every manager also be an employee [who is managed, i.e., a subordinate]. Explain exactly what happens when a tuple in the relation manager is deleted. Answer: Whenever rows in the master (referenced) table are deleted, the respective rows of the child (referencing) table with a matching foreign key column will get deleted as well. For example a tuple in relation manager is deleted, all referencing tuples that match the referenced tuples are deleted as well (i.e. tuples where manager_name = d_employee_name are deleted, where d_employee_name refers to the employee_name in relation manager that was deleted). 5.12 Given a database schema: employee(person_name, street, city) works(person_name, company_name, salary) company(company_name, city) manages(person_name, manager_name) Express the following queries in QBE: ration. g. Find all employees who earn more than every employee of Small Bank Corporation. h. Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located. Answer: a. Find the names of all employees who work for First Bank Corporation. i. person-name company-name P. x First Bank Corporation ii. query(X) :- works(X, "First Bank Corporation", Y) works salary b. Find the names and cities of residence of all employees who work for First Bank Corporation. i. works person-name company-name x First Bank Corporation salary employee person-name street city P. x P. y ii. query (X, Y ) :- employee (X, Z, Y ), works (X, "First Bank Corporation", W ) c. Find the names, street addresses, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per annum. If people may work for several companies, the following solutions will only list those who earn more than $10,000 per annum from "First Bank Corporation" alone. i. employee person-name street city P. x P. y P. z Exercises 61 works ii. person-name x company-name First Bank Co salary > 10000 query (X, Y, Z) :- lives (X, Y, Z), works (X, "First Bank Corporation", W ), W > 10000 d. Find all employees who live in the city where the company for which they work is located. i. employee person-name street city P. x y works person-name company-name salary x c company company-name city c y ii. query (X) :- employee (X, Y, Z), works (X, V, W ), company (V, Z) e. Find all employees who live in the same city and on the same street as their managers. i. employee person-name street city P. x s c y s c manages person-name manager - name x y ii. query (X) :- lives (X, Y, Z), manages (X, V ), lives (V, Y, Z) f. Find all employees in the database who do not work for First Bank Corporation. The following solutions assume that all people work for exactly one company. i. person-name company-name salary P. x First Bank Co ii. query (X) :- works (X, Y, Z), Y = "First Bank Corporation" works If one allows people to appear in the database (e.g. in employee) but not appear in works, or if people may have jobs with more than one company, the solutions are slightly more complicated. They are given below :- 62 Chapter 5 Other Relational Languages i. employee person-name street city P. x works person-name company-name x First Bank Corporation salary ii. query (X) :- employee (X, Y, Z), p1 (X) p1 (X) :- works(X, "First Bank Corporation", W ) g. Find all employees who earn more than every employee of Small Bank Corporation. The following solutions assume that all people work for at most one company. i. works person-name P. x works company-name Small Bank Co or person-name P. x company-name salary y Small Bank Co > y salary y > MAX.ALL. y ii. query (X) :- works (X, Y, Z), p (X) p (X) :- works(X, C, Y 1), works (V, "Small Bank Corporation", Y ), Y > Y 1 h. Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located. i. Note: Small Bank Corporation will be included in each answer. located-in company-name Small Bank Corporation P. c Small Bank Corporation conditions CNT.ALL. y = CNT.ALL. x city x y y ii. 12.15 For each B+-tree of Practice Exercise 12.3, show the steps involved in the following queries: a. Find records with a search-key value of 11. b. Find records with a search-key value between 7 and 17, inclusive. Answer: With the structure provided by the solution to Practice Exercise 12.3a: a. Find records with a value of 11 i. Search the first level index; follow the first pointer. ii. Search next level; follow the third pointer. iii. Search leaf node; follow first pointer to records with key value 11. b. Find records with value between 7 and 17 (inclusive) i. Search top index; follow first pointer. ii. Search next level; follow second pointer. iii. Search third level; follow second pointer to records with key value 7, and after accessing them, return to leaf node. iv. Follow fourth pointer to next leaf block in the chain. v. Follow first pointer to records with key value 11, then return. vi. Follow second pointer to records with with key value 17. With the structure provided by the solution to Practice Exercise 12.3b: a. Find records with a value of 11 i. Search top level; follow second pointer. ii. Search next level; follow second pointer to records with key value 11. b. Find records with value between 7 and 17 (inclusive) i. Search top level; follow second pointer. ii. Search next level; follow first pointer to records with key value 7, then return. iii. Follow second pointer to records with key value 11, then return. iv. Follow third pointer to records with key value 17. With the structure provided by the solution to Practice Exercise 12.3c: a. Find records with a value of 11 i. Search top level; follow second pointer. ii. Search next level; follow first pointer to records with key value 11. b. Find records with value between 7 and 17 (inclusive) i. Search top level; follow first pointer. ii. Search next level; follow fourth pointer to records with key value 7, then return. iii. Follow eighth pointer to next leaf block in chain. iv. Follow first pointer to records with key value 11, then return. v. Follow second pointer to records with key value 17. 13.3a, number of record of r1 = 20000 number of records of r2 = 45000 number of blocks of r1 = 20000/25 = 800 number of blocks of r2 = 45000/30 = 1500 Worst case: number of transfers: nr1*br2+br1 = 20000*1500+ 800 = 30,000,800 number of seeks: nr1+br1 = 20000 + 800 = 20800 Best case: number of transfers: br1 + br2 = 800+ 1500= 2300 number of seeks: 2 13.3b, Worst case: number of transfers: br1*br2+br1 = 800*1500+ 800 = 1,200,800 number of seeks: 2*br1 =2* 800 = 1600 Best case: number of transfers: br1+br2 = 800+1500=2300 number of seeks: 2 13.10 (a) Users don't have all the information about internal costs of different strategies. Nave users may choose an inefficient strategy because of their lack of full information. They would not know how a relation is stored, or what indices are available to speed queries; as a result they might make poor choices of query-processing methods. Ease of use is a major objective of database query languages, so we cannot (and do not want to) force users to be aware of these details. (b) In theory it is possible that users who are aware of the internal implementation details could write very efficient queries, helping performance. In practice this would only occur in the case where experts were using the system. ...
View Full Document

This note was uploaded on 04/28/2010 for the course CS 143 taught by Professor Zaniolo during the Fall '01 term at UCLA.

Ask a homework question - tutors are online