Product

maker	model	type
A	1001	pc
A	1002	pc
A	1003	pc
B	1004	pc
B	1005	pc
B	1006	pc
C	1007	pc
D	1008	pc
D	1009	pc
D	1010	pc
E	1011	pc
E	1012	pc
E	1013	pc
E	2001	laptop
E	2002	laptop
E	2003	laptop
A	2004	laptop
A	2005	laptop
A	2006	laptop
B	2007	laptop
F	2008	laptop
F	2009	laptop
G	2010	laptop
E	3001	printer
E	3002	printer
E	3003	printer
D	3004	printer
D	3005	printer
H	3006	printer
H	3007	printer
C	4001	flash

Exercise 2 (2 pts) Count the costs of operations and find an optimal selection of indexes for the Product table (Hardware Database) under the following assumptions: Operations Query Q1 (20%): SELECT * FROM Product WHERE maker-X AND type-Y Query Q2 (10%): SELECT * FROM Product WHERE model-Z insertion l (70%): INSERT INTO Product VALUES (A,B,C) Assumption about data (the same as on the slides) Data for the Product table is stored in 10 blocks. The Model attribute is a key for Product. On the average, a maker produces 3 models of each type. Records are spread uniformly over the 10 blocks: On the average, 3 records with the same maker are stored in 3 blocks . Any index for Product is stored in 1 block. Four cases to analyze (the same as on the slides) 1. 2. 3. 4. No indexes for Product. [2 pts] Index on Model only. [2 pts] Index on (Maker,Type) only. [2 pts] Index on Model and index on (Maker,Type). [2 pts] Exercise 2 (2 pts) Count the costs of operations and find an optimal selection of indexes for the Product table (Hardware Database) under the following assumptions: Operations Query Q1 (20%): SELECT * FROM Product WHERE maker-X AND type-Y Query Q2 (10%): SELECT * FROM Product WHERE model-Z insertion l (70%): INSERT INTO Product VALUES (A,B,C) Assumption about data (the same as on the slides) Data for the Product table is stored in 10 blocks. The Model attribute is a key for Product. On the average, a maker produces 3 models of each type. Records are spread uniformly over the 10 blocks: On the average, 3 records with the same maker are stored in 3 blocks . Any index for Product is stored in 1 block. Four cases to analyze (the same as on the slides) 1. 2. 3. 4. No indexes for Product. [2 pts] Index on Model only. [2 pts] Index on (Maker,Type) only. [2 pts] Index on Model and index on (Maker,Type). [2 pts]