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 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.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

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.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.