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

Define clause in sql to specify query result must be sorted, The clause in ...

The clause in SQL which specifies that the query result should be sorted in ascending or descending order based upon the values of one or much more columns is Ans : Order by

What are the different integrity constraints in rdbms, What are the differe...

What are the different integrity constraints in RDBMS? The various integrity constraints in RDBMS are as: Primary Key: primary key is a grouping of one or more attributes

Create a database for their customers, The ABC Bank authority now wants to ...

The ABC Bank authority now wants to create a database for their customers. So you are requested to modify your program of Assignment A3 as follows. The main program first displa

Define deadlock detection, Define Deadlock detection If a system does ...

Define Deadlock detection If a system does not employ a few protocols that make sure deadlock freedom, after that a detection and recovery scheme should be used. An algorithm

What is substitutability, What is substitutability? Any method of a cla...

What is substitutability? Any method of a class-say A can equally well be adjured with any object belonging to any subclasses B of A. This characteristic leads to code reuse, a

Partial relation emp-get the details people with min salary, Let consider t...

Let consider the given partial relation EMP. Let us make some sub-queries for them EMPNO ENAME JOB SAL DEPTNO 7566

Describe the relational model, a) Distinguish between ‘database' and ‘Data...

a) Distinguish between ‘database' and ‘Database Management Systems (DBMS)'. b) List and briefly describe five potential benefits of the database approach compared to conventio

Appointment systems with sms and voice call feedback, Automated system for ...

Automated system for appointment with sms and call confirmation with service providers and user has to be initiate automatically. After selecting time and date to use service, s

What is a state chart diagram, What is a state chart diagram? State dia...

What is a state chart diagram? State diagrams (State Chart Diagram) explain all possible states that a particular object can get into, and how object's state changes as result

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