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

Explain the exec statement in sql, Explain the Exec statement in SQL An...

Explain the Exec statement in SQL Ans: Exec statement has the structure    EXEC SQL END-EXEC   Embeded SQl statements are of a form identical to non embedded SQL statem

Marketing interface, as a customer service employee explain five measures y...

as a customer service employee explain five measures you would recommend to make even a displeased students patronize the school canteen

Benefits of views-data control, Benefits: It limits data access. ...

Benefits: It limits data access. It makes difficult queries look easy. It permits data independence. It presents dissimilar views of the similar data.

Explain the components of database system in details., Explain the compone...

Explain the components of Database System in details.

Explain the term- periodic recomputation, Explain the term- Periodic recomp...

Explain the term- Periodic recomputation Periodic recomputation:  Base values are often updated in bunches. Sometimes, it is possible to just recompute all derived attributes p

A distributed transaction, A Distributed Transaction Let us demonstrate...

A Distributed Transaction Let us demonstrate the concept of a distributed transaction by considering a banking system having of three branches located in 3 different cities. Ev

Determine the strong and weak entity, Determine the Strong and Weak Entity ...

Determine the Strong and Weak Entity Based on the method of foreign key, there might be arise a situation when we have to relate an entity having a primary key of its own and a

What is data integrity, What is data integrity? Ans: It is the data con...

What is data integrity? Ans: It is the data consisted of in database that is accurate and consistent.  (Data integrity means that the data should be valid as per to the give

Describe log based recovery, Describe log based recovery The most usabl...

Describe log based recovery The most usably structure for recording data base modification is the LOG the log is a sequence of log records recording all the update activities i

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