{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

E no constraint violations f no constraint violations

Info iconThis preview shows pages 2–4. Sign up to view the full content.

View Full Document Right Arrow Icon
(e) No constraint violations. (f) No constraint violations. (g) Violates referential integrity because several tuples exist in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference the tuple being deleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or (ii) deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equal to'987654321'. (h) Violates referential integrity because two tuples exist in the WORKS_ON relations that reference the tuple being deleted from PROJECT. We may enforce the constraint by: (i) rejecting the deletion, or (ii) deleting the tuples in the WORKS_ON relation whose value for PNO=1 (the value for the primary key PNUMBER for the tuple being deleted from PROJECT). (i) No constraint violations. (j) Violates referential integrity because the new value of SUPERSSN='943775543' and there is no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce the constraint by: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple with SSN='943775543'. (k) No constraint violations. 5.11 Consider the AIRLINE relational database schema shown in Figure 5.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 database to enter a reservation on a particular flight or flight leg on a given date. (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 5.8.
Background image of page 2

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Answers: (a) One possible set of operations for the following update is the following: INSERT <FNO,LNO,DT,SEAT_NO,CUST_NAME,CUST_PHONE> 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.
Background image of page 3
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}