Homework 3: The Relational Data Model

The goal of this homework is to practice interpreting relational model schema diagrams.

Instructions

Complete this assignment individually.

Problem 1: COMPANY Database

Suppose that each of the following Update operations is applied directly to the database schema shown below.

Identify all constraints violated by each operation, if any.

  1. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE
  2. Insert <‘123456789’, NULL, ‘40.0’> into WORKS_ON
  3. Delete the WORKS_ON tuples with Essn = ‘333445555’
  4. Delete the EMPLOYEE tuple with Ssn = ‘987654321’
  5. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively
  6. Insert <‘Facilities’, 1, ‘123456789’, ‘soon’> into DEPARTMENT
Figure 5.6

Problem 2: AIRLINE Database

Consider the AIRLINE relational database schema shown below, 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, and so on. Each FLIGHT_LEG has scheduled arrival and departure times, airports, and one or more LEG_INSTANCEs — one for each Date on which the flight travels. FAREs are kept for each FLIGHT. For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as are the AIRPLANE used on 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 at which they can land. An AIRPORT is identified by an Airport_code.

  1. Consider an update for the AIRLINE database to add a seat reservation:
    • Give the operation(s) for this update.
    • What constraints should you check? For each type of constraint, explain in detail how the database would check it (e.g., which attributes would be checked?).
  2. Annotate the diagram with arrows to specify all the referential integrity constraints that hold on the schema.
Figure 5.8

Submit

Submit your assignment on Gradescope.