Query optimization in database system, Database Management System

Assignment Help:

Problem :

AirwaysCentre, an international airline booking company currently uses a relational database system for their operations, which includes the following relations:

PASSENGER(p_no, p_name, p_address, p_phone)
FLIGHT(flight_code, airline_name, from_airport, to_airport, depart_time, arrive_time)
BOOKING(booking_no, p_no, invoice_total, payment_made)
FLIGHT_BOOKING(booking_no, flight_code, flight_date, price)

The following assumptions are made:

  • The flight code is unique, which includes information on an airline and its flight number, for example „BA300?
  • For each flight code, there is only one associated flight on any particular day
  • A passenger may purchase tickets for several different flights under one booking number
  • However, a passenger may not purchase more than one ticket for the same flight under the same booking number
  • The PASSENGER relation contains 2000 records
  • The FLIGHT relation contains 500 records
  • The BOOKING relation contains 5000 records
  • The FLIGHT-BOOKING relation contains 10000 records 
  • Each flight has a maximum of 200 bookable tickets (seats)

(a) Query optimization in database system aims to improve efficiency in query evaluation. One of the stages involved in the query optimization is to convert some internal form of the query representation into its equivalent but more efficient canonical form using the transformation rules.

Demonstrate, by using two query examples based on the above given relations, how appropriate transformation rules can be applied to carry out such a conversion.

(b) Consider the following query based on the above relations and formulate it using correct SQL syntax:

"Find the names and numbers of all passenger who have booked the Air Mauritius flight MK356 for 2nd December 2010".

(c) Specify two possible options to evaluate the query in (b). Express each of these options firstly in relational algebra and secondly in a query tree. Clearly show all the steps required in the evaluation of the query.

(d) For each option identified above, calculate the size (number of records) for each of the immediate relations to be produced at each steps. Recommend the best option based on the results of your calculations.


Related Discussions:- Query optimization in database system

Relational algebra and calculus, Q.  What is relational algebra and calcul...

Q.  What is relational algebra and calculus? Explain the relational algebra operators :- Selection and predicate, set operation, joins and division. Sol. Relational Algebra

Case-based online phone repair, please i request for your assistance in de...

please i request for your assistance in developing a case-based online phone repair

Complexity- drawbacks of data distribution, Complexity : A distributed DBMS...

Complexity : A distributed DBMS that is available, reliable, and secure is inherently more difficult than a centralised DBMS. Though, adequate data replication is essential to have

Column constraints-on delete cascade, CREATE TABLE prodtrans ( pno ...

CREATE TABLE prodtrans ( pno number (4) ptype char (1) CHECK (ptype in ('I','R','S')), qty number (5) FOREIGN KEY pno REFERENCES product (pno) ON DELETE CASCADE);

Illustrate the definition of the classes and operation, The definition of t...

The definition of the classes and operation can often be adjusted to increase the amount of inheritance". Justify the above statement.   Adjustment of Inheritance The def

Erds, What are the various symbols used to draw an E-R diagram (any 3)?

What are the various symbols used to draw an E-R diagram (any 3)?

Type of views-data control, Type of views :  Simple views and Difficult ...

Type of views :  Simple views and Difficult Views Feature Simple Views Complex Views Number of tables One

Objectives of data management, The traffic data mgmt. systems infrastructur...

The traffic data mgmt. systems infrastructure plan describes the software, hardware, data network, and other elements that will support the TDMS. The infrastructure plan is based o

Why we use delegation to share implementation, Use Delegation to Share Impl...

Use Delegation to Share Implementation The delegation " Delegation comprises of catching an operation on one object and sending it to other object which is part, or related to

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd