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

Problems of concurrent transactions-lost updates, Lost Updates : Suppose th...

Lost Updates : Suppose the two transactions T3 and T4 run simultaneously and they happen to be interleaved in the following way (suppose the initial value of X as 10000):

Define the basic operations of the relational algebra, Define the basic ope...

Define the basic operations of the relational algebra?     Ans: Basic operators of relational algebra are: 1. Union (∪) - Selects tuples which are in either P or Q or in bot

Accounting and external reporting, List the internal controls that should b...

List the internal controls that should be in effect solely because a EDP system is employed, classifing them as (1) Those controls pertaining to input of information and (2) All ot

When it is better to use files than a dbms, When It is better to use files ...

When It is better to use files than a DBMS? It is better to use files than a DBMS when Multiple users wish to access the data.

Can a domain, Can a domain, assigned to a data element be changed? Yes....

Can a domain, assigned to a data element be changed? Yes.  We can do so by just overwriting the entry in the field domain.

Define tables students and teachers as sub tables of people, Write a query ...

Write a query to define tables students and teachers as sub tables of people? Create table students of student under people Create table teachers of teacher under people

Define a relation schema, A Relation Schema represented by R(A1, A2, .....

A Relation Schema represented by R(A1, A2, ..., An) is made up of the relation name the list of attributes and R Ai that it contains. A relation is described as a set of tuples

Database ass1, #quCreate a database design specification (Enhanced Entity R...

#quCreate a database design specification (Enhanced Entity Relationship Diagram (EERD) and Relational Data Model (RDM)) from the given business description. The RDM must be in 3rd

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