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 check clause, Explain the Check Clause The CHECK clause is ...

Explain the Check Clause The CHECK clause is employed to at the end of a CREATE TABLE statement specify table constraints. This is known as table-based constraint because it ap

What is horizontal fragmentation, What is horizontal fragmentation? Hor...

What is horizontal fragmentation? Horizontal fragmentation divides the relation by assuming every tuple of r to one or more fragments

Er schema can be represented by relation schemas , Q. Describe how an ER sc...

Q. Describe how an ER schema can be represented by relation schemas and constraints arising from ER design can be mapped to constraints on a relation schema.     The entity rela

Define timestamp ordering, Define Timestamp ordering Ans In timestamp-b...

Define Timestamp ordering Ans In timestamp-based method, a serial order is made among the concurrent transaction by assigning to every transaction a unique nondecreasing number

Normalization database, I need some help with my normalization database hom...

I need some help with my normalization database homework.

Explain dml with some examples, Explain DML with some examples? DML - D...

Explain DML with some examples? DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples: • SELECT - retrieve data from the a da

Define database management system, Define database management system? D...

Define database management system? Database management system (DBMS) is a set of interrelated data and a combination of programs to access those data.

Query optimization in database system, Problem : AirwaysCentre, an inte...

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

Database, How many physical thing?

How many physical thing?

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