Question

Consider a database containing information about all the car accidents between 1997 and 2005, including the cars involved and their owners. The database has the following tables:

Car (license, year, make, model);

Accident (license, accident date, damage_amount zipcode);

Owner (SSN, license, name, address, zipcode);

// zipcode in Accident is the place where accident took place

// assume that the same car does not get into an accident twice in a day

// assume each owner has only one licensed car

Consider the following query:

SELECT O.name, A.damage_amount

FROM Car C, Accident A, Owner O

WHERE C.license = A.license AND C.licese = O. license AND A.zipcode = O.zipcode AND C.make = ‘Volvo’ AND

A.accident_date < ’07/01/2000’ AND A.damage_amount > 10000;

•Draw the initial tree for this query, and generate a range of query-evaluation plans from the given query by using the equivalence rules, and choose the one with the least cost.

Answer 1

Given Query:-

SELECT O.name, A.damage_amount

FROM Car C, Accident A, Owner O

WHERE C.license = A.license AND C.licese = O. license AND A.zipcode = O.zipcode AND C.make = ‘Volvo’ AND

A.accident_date < ’07/01/2000’ AND A.damage_amount > 10000;

The initial tree for this query:-

After applying Cost-based optimization, The best Query with least cost is as follows:-