Consider the AIRLINE relational database schema shown in Figure 3.8, which describes a database for airline flight information. Each FLIGHT is identified by a flight NUMBER, and consists of one or more FLIGHT_LEGs with LEG_NUMBERs 1, 2, 3, etc. Each leg has scheduled arrival and departure times and airports, and has many LEG_INSTANCEs--one for each DATE on which the flight travels. FARES are kept for each flight. For each leg instance, SEAT_RESERVATIONs are kept, as is the AIRPLANE used in the leg, and the actual arrival and departure times and airports. An AIRPLANE is identified by an AIRPLANE_ID, and is of a particular AIRPLANE_TYPE. CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs in which they can land. An AIRPORT is identified by an AIRPORT_CODE. Consider an update for the AIRLINE data

(a) Give the operations for this update.

(b) What types of constraints would you expect to check?

(c) Which of these constraints are key, entity integrity, and referential integrity constraints and which are not?

(d) Specify all the referential integrity constraints on Figure 3.8.


(a) One possible set of operations for the following update is the following:
INSERT into SEAT_RESERVATION; MODIFY the LEG_INSTANCE tuple with the condition:
( FLIGHT_NUMBER=FNO AND LEG_NUMBER=LNO AND DATE=DT) by setting
NUMBER_OF_AVAILABLE_SEATS = NUMBER_OF_AVAILABLE_SEATS - 1; These operations should be repeated for each LEG of the flight on which a reservation is made. This assumes that the reservation has only one seat. More complex operations will be needed for a more realistic reservation that may reserve several seats at once.

(b) We would check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1 before doing any reservation (unless overbooking is permitted), and that the SEAT_NUMBER being reserved in SEAT_RESERVATION is available.

(c) The INSERT operation into SEAT_RESERVATION will check all the key, entity integrity, and referential integrity constraints for the relation. The check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1 does not fall into any of the above types of constraints (it is a general semantic integrity constraint).

(d) We will write a referential integrity constraint as R.A --> S (or R.(X) --> T) whenever attribute A (or the set of attributes X) of relation R form a foreign key that references the primary key of relation S (or T). FLIGHT_LEG.FLIGHT_NUMBER --> FLIGHT
FLIGHT_LEG.DEPARTURE_AIRPORT_CODE --> AIRPORT FLIGHT_LEG.ARRIVAL_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.(FLIGHT_NUMBER,LEG_NUMBER) --> FLIGHT_LEG
LEG_INSTANCE.DEPARTURE_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.ARRIVAL_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.AIRPLANE_ID --> AIRPLANE
FARES.FLIGHT_NUMBER --> FLIGHT
CAN_LAND.AIRPLANE_TYPE_NAME --> AIRPLANE_TYPE
CAN_LAND.AIRPORT_CODE --> AIRPORT
AIRPLANE.AIRPLANE_TYPE --> AIRPLANE_TYPE
SEAT_RESERVATION.(FLIGHT_NUMBER,LEG_NUMBER,DATE) --> LEG_INSTANCE

Computer Science & Information Technology

You might also like to view...

All of the following are true about the Hide method of a form except:

a. Using the Hide method removes the form from memory. b. The Hide method makes the form invisible. c. Once you have called the Hide method, the form can be redisplayed with the ShowDialog method. d. Once you have called the Hide method, the form can be redisplayed with the Show method.

Computer Science & Information Technology

After you customize an image in Photoshop, you can import the layered Photoshop ____ file into Flash.

A. JPG B. PSD C. PDF D. TIFF

Computer Science & Information Technology

What content control is used to insert a specific date into a document?

What will be an ideal response?

Computer Science & Information Technology

The ________ function calculates the payment for a loan

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology