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.
Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rb, Bellaire, TX’, M, 58000, ‘888665555’, 1> into LABORER
Install <‘123456789’, NULL, ‘40.0’> into WORKS_ON
Delete the WORKS_ON tuples with Essn = ‘333445555’
Delete the EMPLOYEE tuple with Ssn = ‘987654321’
Customize the Mgr_ssn and Mgr_start_date to the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ the ‘2007-10-01’, respectively
Insert <‘Facilities’, 1, ‘123456789’, ‘soon’> under SECTION
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.
- 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?).
- Annotate the diagram with arrows to specify all the referential integrity constraints that hold on the schema.
Present
Submission your assignments on Gradescope.