2. Question 6.4E Part A (25 points). Given the below EER model for the airline business...

Transcribed Image Text:

2. Question 6.4E Part A (25 points). Given the below EER model for the airline business (also on Page 145), map the EER model to a relational model representation. Clearly indicate the primary and foreign key relationships. For the foreign key, indicate whether it is not NULL or NULL allowed. Name Location Belongs to Year of prod- uction Airport Type 10.n). Plane (1.1) 3 Letter Abbreviation Number of seats 1.0 (1.1) Employs Pilot (1. n) (D.A) assigned to is served by ID (0.n) (0.n Destination Employee Steward(ess) Flight (1.8) Home Address Is assigned to Departure (1.) Date of Birth ID Is valid for Name Flight Planner Customer Destination (1m) (1.1) Buys (0) Ticket Number of AirMiles Departure Price 3. DDL and DML SQL Statements (25 points) a) (15 points) Write DDL SQL statements to create the 12 relations from point 2, specifying the PK and FK constraints in your SQL commands, including not NULL/NULL allowed. You should execute your SQL statements in PostgreSQL to verify that the tables are created correctly, and paste your commands below. Type your answer here. b) (10 points) Write DML SQL statements to insert at least one row in each of the relations above. You should execute your SQL statements in PostgreSQL to verify that the rows are inserted successfully, and paste your commands below. Type your answer here. 2. Question 6.4E Part A (25 points). Given the below EER model for the airline business (also on Page 145), map the EER model to a relational model representation. Clearly indicate the primary and foreign key relationships. For the foreign key, indicate whether it is not NULL or NULL allowed. Name Location Belongs to Year of prod- uction Airport Type 10.n). Plane (1.1) 3 Letter Abbreviation Number of seats 1.0 (1.1) Employs Pilot (1. n) (D.A) assigned to is served by ID (0.n) (0.n Destination Employee Steward(ess) Flight (1.8) Home Address Is assigned to Departure (1.) Date of Birth ID Is valid for Name Flight Planner Customer Destination (1m) (1.1) Buys (0) Ticket Number of AirMiles Departure Price 3. DDL and DML SQL Statements (25 points) a) (15 points) Write DDL SQL statements to create the 12 relations from point 2, specifying the PK and FK constraints in your SQL commands, including not NULL/NULL allowed. You should execute your SQL statements in PostgreSQL to verify that the tables are created correctly, and paste your commands below. Type your answer here. b) (10 points) Write DML SQL statements to insert at least one row in each of the relations above. You should execute your SQL statements in PostgreSQL to verify that the rows are inserted successfully, and paste your commands below. Type your answer here.

Answer rating: 100% (QA)

Here is the relational model representation of the EER model for the airline business Airport AirportID PK Name 3LetterAbbreviation Location Employee ... View the full answer