Consider the SQL Query given below. Draw initial query tree and transform this initial query tree using heuristic query optimization

SELECT p. ticketno

FROM Flight as F, Passenger as P, Crew as C

WHERE F.Flightno=c.flightno AND

F.Date=’06-23-15’AND

F.to=’Mumbai’ AND

P.name=C.name

Mumbai University > Information Technology > Sem 3 > Database Management System

Marks: 10M

Year: May 2016

Step 1: Converting Expression to basic Relational Form

πp.ticketnoσf.flightn0 = p.flightno

AND f.flightno = c.flightno

AND F.Date=’06-23-15’

AND F.to=’Mumbai’

AND p.name=c.name

Step 2: Drawing initial query tree using SQL query

Step 3: Moving SELECT operators down the tree

Step 4: Apply more restrictive SELECT operation First

Step 5: Replacing Cartesian Product with JOIN Operation.

Step 6: Replacing Cartesian Product with JOIN Operation.