1. [14 points) Define the following terms Relation Relational Database Schema Superkey Key Primary key Foreign key Referential integrity (or foreign key) constraint 2. 8 points) 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 3.8. You can write a referential integrity constraint as R.A -> Sor R.(X) ->T) whenever attribute A for the set of attributes X) of relation R form a foreign key that references the primary key of relation S (or T) 4.[4 points] Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT (SSN Name, Major, Bdate) COURSE (Course Cname, Dept) ENROLL (SSN Course Quarter Grade) BOOK ADOPTION (Counsod Quarter Book_ISBN) TEXT (Book ISBN. Book Title, Publisher, Author) Draw a relational schema diagram specifying the foreign keys for this schema. MacBook Pro 5. [10 points) Suppose each of the following Update operations is applied directly to the database of Figure 5.6. Discuss all integrity constraints violated by each operation: (c) Insert into DEPARTMENT. (d) Insert into WORKS_ON. (e) Insert into DEPENDENT. (h) Delete the PROJECT tuple with PNAME='ProductX. (1) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN='999887777' to '943775543'. Page of 64 0 - Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Frame Mini John B Franklin T J Lame Sen Bdate Address Se Salwy Super. Dno Smith 1234567891965-01-09 731 Fonden, Houston, TX 30000 3334455555 Wong 330445666 1965-12-08 638 Voss, Houston, TX TM 400000866666666 Zelaya 090607777 1968-01-10 3321 Castle, Spring, TXF 25000 0076543214 Wallace 9876543211941-06-20 291 Berry Belle TXF 43000 8665555 4 Narayan S6844441962-09-15 975 Fire Oak Humble TXM 30000 333445565 Enga 453453453 1972-07-31 5631 Rice Houston, TX 950003334455555 abb 070707 1060-0329 980 Date Houston, TX 95000 987654321 Borg 1937-11-10450 Stone, Houston, TXM 35000 NULL Ramesh K V E DEPARTMENT DEPT LOCATIONS Drumber Dlocation 1995-01-01 1981-05-19 Headquarters 5 5 Bellaire Sugarland WORKS ON PROJECT Completion 10 10100 DEPENDENT 20100 3000 M 10350 MOTOR 05.0 087654321 M 1956-04-05 Der 190310 18 Son 1050 05.09 1042-02-20 Spouse 1968-0104 Son 100 1300 10-05-08 30 ind Shamkant Naga 150 NULL 123456789 1934neteg E th F
