Assignment – 6 (Query Processing and Query Optimization)

Atmiya Institute of Technology & Science

Department of M.C.A.

M.C.A. Semester - II

620006 – Databse Management System - II

Assignment – 6 (Query Processing and Query Optimization)

-----------------------------------------------------------------------------------------------------------------

Q-1. Explain the Following Questions in detail.

1).What do you mean by the term query processing? What are its objectives? Draw a neat sketch of high-level query processing?

2).Discuss the reason for converting SQL query into relation algebra queries before query optimization.

3).What is syntax analyzer? Explain with an example.

4).Explain the query decomposer with different phases in detail?

5).What is query optimization? Why it is needed.

6).Describe the query optimization with detail block diagram.

7).Explain how heuristic query optimization is performed with an example.

8).Describe the Transformation rules for query optimizer.

9).What is relational algebra query tree? Explain with example.

10).What is heuristic optimization algorithm? Discuss various steps in heuristic optimization algorithm.

11).Discuss the main components for a cost function that is used to estimate query execution cost.

12).What are the cost components are used most often as the basis for cost function.

13).List the cost function for the SELECT operation.

14).List the cost function for the JOIN operation.

15).What are basic query optimization strategies? List out the different technique.

-----------------------------------------------------------------------------------------------------------------

Q-2. Do as Directed

1). Let us consider the fallowing relations (tables) that form part of a database of a relational DBMS

HOTEL     (HOTEL-NO, HOTEL-NAME, CITY)

ROOM      (ROOM-NO, HOTEL-NO, TYPE, PRICE)

BOOKING (HOTEL-NO, GUEST-NO, DATE-FROM, DATE-TO, ROOM-NO)

GUEST      (GUEST-NO, GUEST-NAME, GUEST-ADDRESS)

draw a relational algebra tree for each of the following queries. Use the heuristic rules to transform the queries into a more efficient form.

(A). SELECT R.ROOM-NO, R.TYPE, R.PRICE

       FROM ROOM R, HOTEL H, BOOKING B

       WHERE R.ROOM-NO=B.ROOM-NO AND

       B.HOTEL-NO=H.HOTEL-NO AND

       H.HOTEL-NAME=’EMPERIAL’ AND

       R.PRICE>100

(B). SELECT G.GUEST-NO, G.GUEST-NAME

       FROM GUEST AS G, BOOKING AS B. HOTEL AS H, ROOM AS R

       WHERE H.HOTEL-NO = B.HOTEL-NO AND

       G.GUEST-NO = B.GUEST-NO AND

       H.HOTEL-NO = R.HOTEL-NO AND

       H.HOTEL-NAME = ‘Shiv International’ AND

       B.DATE-FROM >= '1-Jan-05' AND

       B.DATE-TO<='31 Dec-05

2). Write and justify an efficient relational algebra expression that is equivalent to the following given query

SELECT B1.BANK-NAME

FROM BANK1 AS B1, BANK2 AS B2

WHERE B1.ASSESTS>B2.ASSESTS AND

B2.BANK-LOCATION=’Rajkot’

3). Using the above HOTEL schema, determine whether the following queries are semantically correct and justify your answer.

(A). SELECT R.TYPE, R.PRICE

      FROM ROOM AS R, HOTEL AS H

      WHERE R.HOTEL-NUM = H.HOTEL-NUM AND

      H.HOTEL-NAME = 'Taj Residency' AND

      R..TYPE > l00

(B).   SELECT G.GUEST-NO, G.GUEST-NAME

        FROM GUEST AS G, BOOKING AS B, HOTEL AS H

        WHERE R.HOTEL-NO=B.HOTEL-NO AND

        H.HOTEL-NAME=’TAJ’

(C).  SELECT R.ROOM-NO, H.HOTEL-NO

       FROM  ROOM AS R, HOTEL AS H, BOOKING AS B

       WHERE H.HOTEL-NO=B.HOTEL-NO AND

       H.HOTEL-NO=’H40’ AND

       B.ROOM-NO=R.ROOM-NO AND

       R.TYPE>’S’ AND B.HOTEL-NO=’H50’
-----------------------------------------------------------------------------------------------------------------