problem 2 design a relational database schema and implement the database 50 in this 5153410

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.

    1. Declare your relations using the SQL DDL.

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

    1. Populate the database using SQL DML.

    2. Use the provided tables as the data to insert in the database.

  1. Answer

    1. List the departure date, flight number and the number of booked customers.

    2. List the pilot names that can fly a A310 plane type.

    3. List the departure date and flight number that never got any personnel assigned.

  2. Create 5 more queries that will be useful for retrieving data from this database. Specify the queries and get a screenshot of the results.

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

the following queries a few queries using PostgreSQL (psql or pgAdmin).

format: “xyzassignment2” (xyz are your initials) using the canvas system. Embed any graph in the

with the name of the file using this

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 Rose Parker Bryan Brown Arthur Hooper Sergio Ryan Brandon Gordon Madison Parker Mark Williams Carol Phillips Justin Colano Claudia Stevens Julia Maverick John Peters

Departure_Date Flight_Num Oct-31-2018 100 Oct-31-2018 206 Oct-31-2018 334 Oct-31-2018 449

Nov-1-2018 991 Nov-1-2018 991 Nov-1-2018 100 Nov-1-2018 100

Oct-31-2018 449 Oct-31-2018 449 Nov-1-2018 991 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

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

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

Employee_no

Name

License_Date

Plane_model_ 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 100 SLC BOS

206 DFW STL

  1. 334 ORD MIA

  2. 335 MIA ORD

  3. 336 ORD MIA

  4. 337 MIA ORD

  1. 121 STL SLC

  2. 122 STL YYV

330 JFK YYV 991 BOS ORD

  1. 394 DFW MIA

  2. 395 MIA DFW

449 CDG DEN

  1. 930 YYV DCA

  2. 931 DCA YYV

  3. 932 DCA YYV

112 DCA DEN

dep_time arr_time 8:00 17:50 9:00 11:40 12:00 14:14 15:00 17:14 18:00 20:14 20:30 23:53 7:00 9:13 8:30 10:19 16:00 18:53 17:00 18:22 19:00 21:30 21:00 23:43 10:00 19:29 13:00 16:10 17:00 18:10 18:00 19:10 14:00 18:07

DEPARTURE

ASSIGNED_TO

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

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.

"Get 15% discount on your first 3 orders with us"
Use the following coupon
FIRST15

Order Now