Limited Offer Get 25% off — use code BESTW25
No AI No Plagiarism On-Time Delivery Free Revisions
Claim Now

You need to use the COMPANY. The script for the creation and data population for this database (pg_companydb.sql) was already provided in module 4. For that database, answer the following queries. Create the files for all the queries using PostgreSQL (psql or pgAdmin). When a query asks for “name” of employees, please include both, the first and the last names. 1. Retrieve the name and address of each employee that works in the Administration department. 2. Retrieve the name and SSN of each employee that either works in department 4 or has a salary lower or equal to $30,000. You are allowed to use only one condition term in any WHERE clause; i.e., don’t use AND/OR Boolean operations. 3. List the name and

Uncategorized

GET THIS ANSWER AT ESSAYLINK.NET

Florida International University
School of Computer Science

COP- 4710 Database Management

Assignment 2
Value: 100 points (10% weight of the course)

Due: June 19, 2022, 11:59 PM

PROBLEM 1: WRITE SQL QUERIES
[50]

In this problem, you are required to write the SQL statements to answer the following queries using PostgreSQL system.

You need to use the COMPANY. The script for the creation and data population for this database (pg_companydb.sql) was already provided in module 4. For that database, answer the following queries. Create the files for all the queries using PostgreSQL (psql or pgAdmin). When a query asks for “name” of employees, please include both, the first and the last names.

1. Retrieve the name and address of each employee that works in the Administration department.

2. Retrieve the name and SSN of each employee that either works in department 4 or has a salary lower or equal to $30,000. You are allowed to use only one condition term in any WHERE clause; i.e., don’t use AND/OR Boolean operations.

3. List the name and SSN of each employee that works on at least one of the projects 1, 2, and 3. You can use only one condition term in any WHERE clause.

4. Retrieve the names of all employees of the Research department who work more than 10 hours per week on the ProductX project.

5. Retrieve the names of all employees who work on all projects [every project] located in either Bellaire or Sugarland. That is, if p1, p2, and p3 are in Bellaire; and p4 and p5 are located in Sugarland; then I want an employee who works on p1, p2, p3, p4, and p5.

6. Find the names of all employees who work on at least one project located in Houston but whose department has no location in Houston.

7. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of male employees working for that department.

8. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.

9. [9A] Create a View ProjectInfo that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it. [9B] Show the content of this view.

10. [10A] Create a View DepartmentInfo that has the department name, manager name for each department. [10B] Show the content of this view.

Submit a document that contains each query and a screenshot of the result of the queries. Also send the sql files (one query per file (name each file Assig2_P1_Q.sql)– zip them all) using the Canvas system.

PROBLEM 2: DESIGN A RELATIONAL DATABASE SCHEMA AND IMPLEMENT THE DATABASE.
[50]

In this problem, you are required to create a relational database schema and to create a database using DDL and DML. Write all the SQL statements using PostgreSQL system.

1. Design the relational database schema for the ER Diagram of the airline database (problem 2 of assignment 1). You can use Figure 9.2 as an example of a relational database schema. Use the ER-to-Relational Mapping Algorithm for creating this schema.

2. Create the airline database.
a. Declare your relations using the SQL DDL.

b. Include your constraints in the DDL: primary keys, foreign keys, unique values, Default, null, not null, etc.

3. Include any necessary column that is missing from the original ER Diagram, for example customer’s ID or customer’s number.

4. Populate the airline database
a. Populate the database using SQL DML.
b. Use the provided tables as the data to insert in the database.
5. Answer the following queries a few queries using PostgreSQL (psql or pgAdmin).
a. List the departure date, flight number and the number of booked customers.
b. List the pilot names that can fly a A310 plane type.
c. List the departure date and flight number that never got any personnel assigned.

6. Create 5 more queries that will be useful for retrieving data from this database. Specify the queries and get a screenshot of the results.
7. Implement your database using PostgreSQL.

Create a single sql for the creation and population of the airline database. For example:

create_airline_db.sql

Submit a document that contains each query and a screenshot of the result of the queries that you create. Also send the sql files (one query per file (name each file Assig2_P2Q.sql)– zip them all) using the Canvas system.

You can submit all the files from this assignment in one single zip file with the name of the file using this format: “xyzassignment2” (xyz are your initials) using the canvas system. Embed any graph in the document.
Use this information to insert data in the airline database:

CUSTOMER
Name
Last_Name
Street
City
State
Zip_Code
phone
Rose
Parker
123 Thomas St.
Toledo
OH
57556
801-556-2239
Jessica
Jones
234 Pines St.
Los Angeles
CA
34898
801-552-2943
John
Peters
345 Star St.
Raleigh
NC
79999
801-393-2230
Bryan
Brown
435 Palm St.
Miami
FL
30533
801-933-2320
Mark
Williams
348 Andrew St.
Fort Lauderdale
FL
33318
801-343-2320
Carol
Phillips
395 Pine St.
Omaha
NE
88899
801-323-2320
Madison
Parker
285 Diamond St.
San Diego
CA
99977
801-493-2203
Justin
Colano
223 Easy St.
Tampa
FL
66798
801-193-2320
Claudia
Stevens
775 Main St.
Saint Louis
MO
99878
801-303-2222
Arthur
Hooper
456 Rose St.
Las Vegas
NV
17878
313-912-2101
Sergio
Ryan
567 Spruce St.
Lincoln
NE
87898
801-228-6729
Julia
Maverick
678 Tulip St.
Raleigh
NC
79999
NULL
Brandon
Gordon
789 First St.
Miami
FL
30533
NULL

BOOKED_ON
Customer Name
Departure_Date
Flight_Num
Rose Parker
Oct-31-2018
100
Bryan Brown
Oct-31-2018
206
Arthur Hooper
Oct-31-2018
334
Sergio Ryan
Oct-31-2018
449
Brandon Gordon
Nov-1-2018
991
Madison Parker
Nov-1-2018
991
Mark Williams
Nov-1-2018
100
Carol Phillips
Nov-1-2018
100
Justin Colano
Oct-31-2018
449
Claudia Stevens
Oct-31-2018
449
Julia Maverick
Nov-1-2018
991
John Peters
Nov-1-2018
100

PLANE TYPE
Model_No
Manufacturer
B727
Boeing
B747
Boeing
B757
Boeing
DC9
MD
DC10
MD
A310
Airbus
A320
Airbus
A330
Airbus
A340
Airbus
EMPLOYEE

Employee_no

Name

Salary

1001

John Jones

90000

1002

Albert Peters

85000

1003

Carl Rowe

115000

1004

Phillips

25000

1005

William Gates

50000

1006

Robert Clark

45000

1007

Joseph Warnock

50000

1008

Lisa Stuart

75000

PILOT

Plane_model_

Employee_no

Name

License_Date

can_fly

1001

John Jones

JUNE-1-1999

B727

1001

John Jones

AUG-14-2000

B747

1001

John Jones

APRIL-20-2005

DC10

1002

Albert Peters

SEP-25-2010

DC9

1002

Albert Peters

APRIL-20-2005

A310

1002

Albert Peters

JULY-17-2003

B757

1002

Albert Peters

MAY-28-2012

A320

1002

Albert Peters

DEC-01-2015

B727

1003

Carl Rowe

JAN-25-2013

A310

1003

Carl Rowe

JUNE-05-2015

DC9

FLIGHT

Flight_num

origin
dest

dep_time

arr_time

100

SLC
BOS

8:00

17:50

206

DFW
STL

9:00

11:40

334

ORD
MIA

12:00

14:14

335

MIA
ORD

15:00

17:14

336

ORD
MIA

18:00

20:14

337

MIA
ORD

20:30

23:53

121

STL
SLC

7:00

9:13

122

STL
YYV

8:30

10:19

330

JFK
YYV

16:00

18:53

991

BOS
ORD

17:00

18:22

394

DFW
MIA

19:00

21:30

395

MIA
DFW

21:00

23:43

449

CDG
DEN

10:00

19:29

930

YYV
DCA

13:00

16:10

931

DCA
YYV

17:00

18:10

932

DCA
YYV

18:00

19:10

112

DCA
DEN

14:00

18:07

AIRCRAFT

Serial_no
Model_no
B1110
B727
B1325
B727
B1088
B747
B1419
B747
B2244
B757
B9377
B757
D2101
DC9
D2216
DC9
D2333
DC9
D2429
DC9
D2530
DC10
A7099
A310
A8055
A320

DEPARTURE
Departure_Date
Flight_Number
Aircraft_serial_no
Oct-31-2018
100
B1110
Oct-31-2018
112
NULL
Oct-31-2018
206
D2530
Oct-31-2018
334
NULL
Oct-31-2018
335
NULL
Oct-31-2018
337
D2216
Oct-31-2018
449
NULL
Nov-1-2018
100
D2530
Nov-1-2018
112
A8055
Nov-1-2018
206
NULL
Nov-1-2018
334
NULL
Nov-1-2018
395
NULL
Nov-1-2018
991
B1325

ASSIGNED_TO

Employee_no
dep_date
flight_num
1001
Oct-31-2018
100
1003
Oct-31-2018
100
1004
Oct-31-2018
100
1002
Oct-31-2018
206
1007
Oct-31-2018
206
1003
Oct-31-2018
337
1004
Oct-31-2018
337
1005
Oct-31-2018
337
1006
Oct-31-2018
337
1001
Nov-1-2018
100
1005
Nov-1-2018
100
1007
Nov-1-2018
100
1003
Nov-1-2018
991
1006
Nov-1-2018
991
1007
Nov-1-2018
991
1002
Nov-1-2018
112
1004
Nov-1-2018
112
1008
Nov-1-2018
112

Notes:

• Employee table contains all the personnel of the airline that can be assigned to a departure.

• This data is not necessarily a database state. It is just data to insert in the database.

• If you are planning to use auto increment columns for some tables columns like for example a possible customer_id for the customer table, you can read a quick tutorial on how use serial to create auto-increment columns here.

• Try to eliminate as much data duplication as possible.

Good luck!

GET ORIGINAL SOLUTION AT ESSAYLINK.NET

Plagiarism Free Assignment Help

Expert Help With This Assignment — On Your Terms

✓ Native UK, USA & Australia writers ✓ Deadline from 3 hours ✓ 100% Plagiarism-Free — Turnitin included ✓ Unlimited free revisions ✓ Free to submit — compare quotes

Related posts:

  1. HSC4500 Lesson 01 12 Your health care organization has adopted a policy
  2. 1. Identify the aims, objectives and the unique selling point of the proposed business [5 marks] 2. Clearly identify the strengths of the proposed business. [5 marks] 3. Identify and
  3. Tots to Teens is a catalog retailer specializing in children’s clothing. A proje
  4. 175 words reply 1