Translate the logical data model in PostgreSQL. • Development of SQL code to create the entire database schema and reflect its constraints. • Development of the case study application for the given 10 sample queries. • Insertion of several sample tuples for each relation in your database. Submit your database using the pg dump command. Derived Relations: Airplane(Reg_Num PK, Model_Num FK, Capacity, Weight) Model(Model_Num PK, Manufacturer, Type, Max_Speed) Technician(SSN PK, Name, Address, Phone_Number, Salary) Expertise(SSN PK, Model_Num PK) Traffic_Ctrlr(SSN PK, Date_Most_Recent) Union(Membership_Num PK, SSN FK) Test(FAA_Num PK, Name, Max_Score) Event(Date PK, Hours, Score, SSN FK, Reg_Num FK, FAA_Num FK)   Relationships Airplane is of Model (1:M) Technician has Expertise in Model (M:M) Traffic Controller undergoes Medical Examination (1:1) Employee belongs to Union (1:1) Airplane is Tested using Test (M:M) Testing Event records Airplane, Technician, and Test (M:M:M)   Validation of Logical Model against Corresponding User Transactions: Adding a new airplane to the database with its registration number, model number, capacity, and weight. Assigning an expertise to a technician for a particular model. Recording a medical examination date for a traffic controller. Adding a new union member with their membership number and corresponding employee SSN. Recording a new test with its FAA test number, name, and maximum possible score. Creating a new testing event with the date, hours, score, and the corresponding airplane, technician, and test involved.   Primary key constraints: Airplane: Reg_Num Model: Model_Num Technician: SSN Expertise: (SSN, Model_Num) Traffic_Ctrlr: SSN Union: Membership_Num Test: FAA_Num Event: Date   Referential integrity (foreign key) constraints: Airplane: Model_Num references Model(Model_Num) Expertise: SSN references Technician(SSN) and Model_Num references Model(Model_Num) Traffic_Ctrlr: SSN references Technician(SSN) Union: SSN references Technician(SSN) Event: SSN references Technician(SSN), Reg_Num references Airplane(Reg_Num), and FAA_Num references Test(FAA_Num)   Entity integrity (NULL and default value) constraints: Airplane: Reg_Num cannot be NULL, Model_Num cannot be NULL, Capacity cannot be NULL, Weight cannot be NULL Model: Model_Num cannot be NULL, Manufacturer cannot be NULL, Type cannot be NULL, Max_Speed cannot be NULL Technician: SSN cannot be NULL, Name cannot be NULL, Address cannot be NULL, Phone_Number cannot be NULL, Salary cannot be NULL Expertise: SSN cannot be NULL, Model_Num cannot be NULL Traffic_Ctrlr: SSN cannot be NULL, Date_Most_Recent cannot be NULL Union: Membership_Num cannot be NULL, SSN cannot be NULL Test: FAA_Num cannot be NULL, Name cannot be NULL, Max_Score cannot be NULL Event: Date cannot be NULL, Hours cannot be NULL, Score cannot be NULL    Alternate key constraints: Airplane: Registration Number Technician: SSN Union: Membership Number Test: FAA Test Number   Sample Queries 1. Insert a new technician into the database. 2. Delete an existing airplane from the database. 3. Update the expertise of an existing technician. 4. List the details of the technician whose salary is greater than the average of the salary of all technicians. 5. List all the model numbers that a given technician has the expertise, along with their capacity and weight information. 6. List the total number of technicians who are experts in each model. 7. List the details (test number, test name, maximum score, etc.) of the FAA tests for a given airplane, sorted by the maximum scores. 8. List the most recent annual medical examination and his/her union membership number for each traffic controller. 9. List the total number of tests done by each technician for a given airplane. 10. List the name of the technician, the registration number of the airplane, and the FAA number of those tests done between September 2021 and December 2021, sorted by the FAA numbers.

 Translate the logical data model in PostgreSQL.
• Development of SQL code to create the entire database schema and reflect its constraints.
• Development of the case study application for the given 10 sample queries.
• Insertion of several sample tuples for each relation in your database.

Submit your database using the pg dump command.

Derived Relations:

• Airplane(Reg_Num PK, Model_Num FK, Capacity, Weight)
• Model(Model_Num PK, Manufacturer, Type, Max_Speed)
• Technician(SSN PK, Name, Address, Phone_Number, Salary)
• Expertise(SSN PK, Model_Num PK)
• Traffic_Ctrlr(SSN PK, Date_Most_Recent)
• Union(Membership_Num PK, SSN FK)
• Test(FAA_Num PK, Name, Max_Score)
• Event(Date PK, Hours, Score, SSN FK, Reg_Num FK, FAA_Num FK)

Relationships

• Airplane is of Model (1:M)
• Technician has Expertise in Model (M:M)
• Traffic Controller undergoes Medical Examination (1:1)
• Employee belongs to Union (1:1)
• Airplane is Tested using Test (M:M)
• Testing Event records Airplane, Technician, and Test (M:M:M)

Validation of Logical Model against Corresponding User Transactions:

• Adding a new airplane to the database with its registration number, model number, capacity, and weight.
• Assigning an expertise to a technician for a particular model.
• Recording a medical examination date for a traffic controller.
• Adding a new union member with their membership number and corresponding employee SSN.
• Recording a new test with its FAA test number, name, and maximum possible score.
• Creating a new testing event with the date, hours, score, and the corresponding airplane, technician, and test involved.

Primary key constraints:

• Airplane: Reg_Num
• Model: Model_Num
• Technician: SSN
• Expertise: (SSN, Model_Num)
• Traffic_Ctrlr: SSN
• Union: Membership_Num
• Test: FAA_Num
• Event: Date

Referential integrity (foreign key) constraints:

• Airplane: Model_Num references Model(Model_Num)
• Expertise: SSN references Technician(SSN) and Model_Num references Model(Model_Num)
• Traffic_Ctrlr: SSN references Technician(SSN)
• Union: SSN references Technician(SSN)
• Event: SSN references Technician(SSN), Reg_Num references Airplane(Reg_Num), and FAA_Num references Test(FAA_Num)

Entity integrity (NULL and default value) constraints:

• Airplane: Reg_Num cannot be NULL, Model_Num cannot be NULL, Capacity cannot be NULL, Weight cannot be NULL
• Model: Model_Num cannot be NULL, Manufacturer cannot be NULL, Type cannot be NULL, Max_Speed cannot be NULL
• Technician: SSN cannot be NULL, Name cannot be NULL, Address cannot be NULL, Phone_Number cannot be NULL, Salary cannot be NULL
• Expertise: SSN cannot be NULL, Model_Num cannot be NULL
• Traffic_Ctrlr: SSN cannot be NULL, Date_Most_Recent cannot be NULL
• Union: Membership_Num cannot be NULL, SSN cannot be NULL
• Test: FAA_Num cannot be NULL, Name cannot be NULL, Max_Score cannot be NULL
• Event: Date cannot be NULL, Hours cannot be NULL, Score cannot be NULL

 Alternate key constraints:

• Airplane: Registration Number
• Technician: SSN
• Union: Membership Number

Test: FAA Test Number