As in Quiz10, assume we have this database: CREATE TABLE 'country' ( 'Code' CHAR (3) NOT...

 

 

Transcribed Image Text:

As in Quiz10, assume we have this database: CREATE TABLE 'country' ( 'Code' CHAR (3) NOT NULL DEFAULT" 'Name' CHAR (52) NOT NULL DEFAULT", CECS 535: Introduction to Database Fall 2023, Homework 5 'Continent' enum ('Asia', 'Europe', 'North America', 'Africa', 'Oceania', 'Antarctica', 'South America') NOT NULL DEFAULT 'Asia', 'Region' CHAR (26) NOT NULL DEFAULT", 'SurfaceArea' DECIMAL NOT NULL DEFAULT '0.00', 'GNPOld' DECIMAL DEFAULT NULL, 'LocalName' CHAR (45) NOT NULL DEFAULT", 'GovernmentForm' CHAR (45) NOT NULL DEFAULT"> 'HeadOf State' CHAR (60) DEFAULT NULL, 'Capital INT DEFAULT NULL, 'Code2' CHAR (2) NOT NULL DEFAULT", PRIMARY KEY ('Code')); ); 'IndepYear' SMALLINT DEFAULT NULL, 'Population' INT NOT NULL DEFAULT '0', 'LifeExpectancy' DECIMAL DEFAULT NULL, 'GNP DECIMAL DEFAULT NULL, CREATE TABLE 'city' ( 'ID' INT NOT NULL AUTO_INCREMENT, 'Name' CHAR (35) NOT NULL DEFAULT", 'CountryCode" CHAR (3) NOT NULL DEFAULT'' 'District' CHAR (20) NOT NULL DEFAULT"' 'Population' INT NOT NULL DEFAULT '0', PRIMARY KEY ('ID'), KEY 'CountryCode' ('CountryCode'), FOREIGN KEY ('CountryCode') REFERENCES 'country' ('Code') ); " CREATE TABLE 'countrylanguage ( 'CountryCode' CHAR (3) NOT NULL DEFAULT", 'Language CHAR (30) NOT NULL DEFAULT '' 'IsOfficial' enum ('T', 'F') NOT NULL DEFAULT 'F', 'Percentage' DECIMAL NOT NULL DEFAULT'0.0', PRIMARY KEY ('CountryCode', 'Language), FOREIGN KEY ('CountryCode ) REFERENCES 'country' ('Code') 1 Recall that table city has 10,000 tuples, country has 800 and countrlanguage has 2,500 tuples. Assume you have 20 pages of memory buffer. Pages are 1K (1024 bytes). Assume the query SELECT c. Name, sum (c. Population), avg (co.GNP) FROM city c, country co, countrylanguage cl WHERE c. CountryCode = co. Code and cl.CountryCode=co. Code and Language = 'English' and Continent = 'Asia' and c. Population > 100,000 GROUP BY c. Name; In all the following, explain how you obtain your numbers in order to receive credit. Numbers without explanation will not get credit. 1. Give an initial query tree for this query. 2. Give an optimized query tree for this query. All you have to do is push down projections and selections. 3. To answer some of the following, you will need to calculate the size of country language. Give the size of countrylanguage in blocks/pages. 4. Estimate the cost of selection Population>100,000 (city) assuming selectivity factor of 5%, if (a) the file for table city is a heap, no indices. (b) the file for table city file is sorted on Population. (c) the file for table city has a clustering index on Population with height 4 (each pointer is 8 bytes). (d) the file for table city has a secondary index on Population, with height 6 (each pointer is 8 bytes). 5. Estimate the size and cost of implementing the join country country language. (a) first, give join size in tuples and number of pages/blocks. (b) cost if done by nested loop. (c) cost if done by sort-merge join. (d) cost if done by hash-join. 6. For this expression T country.Code, GNP,city. Name,city. Population (Continent='Asia' (country Code=CountryCode City)) and assuming a selectivity factor the selection of 1 in 7 tuples, (a) Give an estimate of the final size of this expression. Assume the selection has a 10% selectivity factor. (b) Give a query that uses pipeline and pushes down the selection and cost estimate. (c) Give a query plan that does not use pipeline and does not push down the selection, and a cost estimate. (d) Give a query plan that does not use pipeline and does push down the selection, and a cost estimate. 2 As in Quiz10, assume we have this database: CREATE TABLE 'country' ( 'Code' CHAR (3) NOT NULL DEFAULT" 'Name' CHAR (52) NOT NULL DEFAULT", CECS 535: Introduction to Database Fall 2023, Homework 5 'Continent' enum ('Asia', 'Europe', 'North America', 'Africa', 'Oceania', 'Antarctica', 'South America') NOT NULL DEFAULT 'Asia', 'Region' CHAR (26) NOT NULL DEFAULT", 'SurfaceArea' DECIMAL NOT NULL DEFAULT '0.00', 'GNPOld' DECIMAL DEFAULT NULL, 'LocalName' CHAR (45) NOT NULL DEFAULT", 'GovernmentForm' CHAR (45) NOT NULL DEFAULT"> 'HeadOf State' CHAR (60) DEFAULT NULL, 'Capital INT DEFAULT NULL, 'Code2' CHAR (2) NOT NULL DEFAULT", PRIMARY KEY ('Code')); ); 'IndepYear' SMALLINT DEFAULT NULL, 'Population' INT NOT NULL DEFAULT '0', 'LifeExpectancy' DECIMAL DEFAULT NULL, 'GNP DECIMAL DEFAULT NULL, CREATE TABLE 'city' ( 'ID' INT NOT NULL AUTO_INCREMENT, 'Name' CHAR (35) NOT NULL DEFAULT", 'CountryCode" CHAR (3) NOT NULL DEFAULT'' 'District' CHAR (20) NOT NULL DEFAULT"' 'Population' INT NOT NULL DEFAULT '0', PRIMARY KEY ('ID'), KEY 'CountryCode' ('CountryCode'), FOREIGN KEY ('CountryCode') REFERENCES 'country' ('Code') ); " CREATE TABLE 'countrylanguage ( 'CountryCode' CHAR (3) NOT NULL DEFAULT", 'Language CHAR (30) NOT NULL DEFAULT '' 'IsOfficial' enum ('T', 'F') NOT NULL DEFAULT 'F', 'Percentage' DECIMAL NOT NULL DEFAULT'0.0', PRIMARY KEY ('CountryCode', 'Language), FOREIGN KEY ('CountryCode ) REFERENCES 'country' ('Code') 1 Recall that table city has 10,000 tuples, country has 800 and countrlanguage has 2,500 tuples. Assume you have 20 pages of memory buffer. Pages are 1K (1024 bytes). Assume the query SELECT c. Name, sum (c. Population), avg (co.GNP) FROM city c, country co, countrylanguage cl WHERE c. CountryCode = co. Code and cl.CountryCode=co. Code and Language = 'English' and Continent = 'Asia' and c. Population > 100,000 GROUP BY c. Name; In all the following, explain how you obtain your numbers in order to receive credit. Numbers without explanation will not get credit. 1. Give an initial query tree for this query. 2. Give an optimized query tree for this query. All you have to do is push down projections and selections. 3. To answer some of the following, you will need to calculate the size of country language. Give the size of countrylanguage in blocks/pages. 4. Estimate the cost of selection Population>100,000 (city) assuming selectivity factor of 5%, if (a) the file for table city is a heap, no indices. (b) the file for table city file is sorted on Population. (c) the file for table city has a clustering index on Population with height 4 (each pointer is 8 bytes). (d) the file for table city has a secondary index on Population, with height 6 (each pointer is 8 bytes). 5. Estimate the size and cost of implementing the join country country language. (a) first, give join size in tuples and number of pages/blocks. (b) cost if done by nested loop. (c) cost if done by sort-merge join. (d) cost if done by hash-join. 6. For this expression T country.Code, GNP,city. Name,city. Population (Continent='Asia' (country Code=CountryCode City)) and assuming a selectivity factor the selection of 1 in 7 tuples, (a) Give an estimate of the final size of this expression. Assume the selection has a 10% selectivity factor. (b) Give a query that uses pipeline and pushes down the selection and cost estimate. (c) Give a query plan that does not use pipeline and does not push down the selection, and a cost estimate. (d) Give a query plan that does not use pipeline and does push down the selection, and a cost estimate. 2

Answer rating: 100% (QA)

1 Initial Query Tree PROJECT AGGREGATE SELECT SELECT SELECTION CROSS PRODUCT JOIN CITY COUNTRY COUNTRYLANGUAGE 2 Optimized Query Tree Pushing down pro... View the full answer