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
icon
Related questions
Question

 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.
 
 

 

Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps

Blurred answer
Knowledge Booster
Transaction Processing
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.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education