Exercise 2 (2 pts) Count the costs of operations and find an optimal selection o

Question

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]

Explanation / Answer

This question is not complete as it is also mentioned about slides but I am trying to answer it on the basis of given information:-

it is clearly showing that we have one table name product and it is having below attributes :

Model
Maker and
Type

so table can be created using simple sql create command

create Product(

varchar(30) model,
varchar(30) maker,
varchar(30) type
);

it is saying that model attribute is a key for product so it will be like :-

ALTER TABLE Product ADD PRIMARY KEY (model);

There are total 10 blocks have been used for data processing

lets analyze cases 1 by 1:

case 1.) no indexes for product

indexes helps in processing fast and speeds up the result while retriving data

since we are not using any index so in worst case select operation may take max time and will be the most costly opeartion.

so in first operation

Q1 : Select * from Product where maker =X and type =y

this operation has two conditions and without indexes select statement has to traverse through all the blocks so it will take the max cost

(information is not given complete so I am assuming it will take whole 20% cost)

cost of 1st operation will be : 20%


Q2: Select * from Product where model = Z

it is also having same reason and will cost max without indexing
  
cost of 2nd operation will be : 10%

Q3 : Insert into Product Values(A,B,C)

well this will happen in constant time since if indexing were there then we must need to update index block and it makes insertion operation slow but as per this case indexing
is not there so it will simply insert new row in free available block in constant time.

cost of 3rd operation will be : 1%;

case 2.) index on model only

using CREATE INDEX command we can create index on a table

so in this case model which is a key attribute is being used for indexing it will minimize the cost of select operation and will speed up the processing

Q1.) Select * from Product where maker =X and type =y

since index is only on model attribute and it is searching by maker and type attribute so it will cost as it is

cost of 1st operation : 20%

Q2.) Select * from Product where model = Z

index is on model so it will not take time and will show result in constant time only
  
cost of 2nd opeartion : 1%
Q3.) Insert into Product Values(A,B,C)

since index is there so insert operation will be slow and may take more time

so cost of 3rd opeartion can be around : 30%

case 3.) index on (Maker,Type) only

in this case indexing is happening on two columns together so it will speed up the process and

Query Q1 will take constant time and so I am assuming it will cost only 1% here

but in Query2 it has to go through the whole list since index is not on model so Q2 may take its max time which is 10%

and Query3 is insert operation and will be more slow as compare to last case lets assume it will be around : 40-50%

case 4.) Index on Model and Index on (Maker,Type)
since here now indexing is happening on all the columns so Q1 and Q2 queries will execute in constant time and will be the most fast operations
  
Q1 and Q2 may cost only : 1%
  
but Q3 which is an insert operation will be the most slow operation and may take long time as compare to other operatation so
it will cost max : 70%
  
this last case having indexes on all 3 columns so they will take 3 blocks in memory as question have mentioned 1 block for each index and same for rest cases
  
Thanks

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.