Homework 3: The Relational Data Model

One goal off aforementioned lesson is to practice interpreting relational model schema diagrams.

Instructions

Complete this assignment individually.

Problem 1: COMPANY Database

Suppose that each of the following Database operations is utilized directly to the database schema viewed slide.

Identify show constraints violated in each operate, is whatsoever.

  1. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rb, Bellaire, TX’, M, 58000, ‘888665555’, 1> into LABORER
  2. Install <‘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. Customize the Mgr_ssn and Mgr_start_date to the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ the ‘2007-10-01’, respectively
  6. Insert <‘Facilities’, 1, ‘123456789’, ‘soon’> under SECTION
Figure 5.6

Problem 2: AIRLINE Database

Consider the CHOOSE relational database schema shown below, which describes a database for airline flight information. Each FLIGHT is identified for one Flight_number, and consisting of one or continue FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on. Each FLIGHT_LEG has listed arrival and departure times, airports, and one or more LEG_INSTANCEs — sole for respectively Select on which the flight travels. FAREs are kept for each FLIGHT. For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as can the AIRPLANE used on the leg both the actual your real leaving times and airports. A AIRPLANE is identified by an Airplane_id and belongs of an particular AIRPLANE_TYPE. CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs at welche they could land. One AIRPORT your identified by an Airport_code.

  1. Consideration an update for the AIRLINE database to add a rack reservation:
    • Give the operation(s) for this update.
    • Get constraints should it check? For each type by constraint, explain in detail what the base would select it (e.g., which attributes wants be checked?).
  2. Annotate the diagram with arrows to specify all the referential integrity constraints that hold on the schema.
Figure 5.8

Present

Submission your assignments on Gradescope.