Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Write a SQL statement to list flight numbers and Airplane models for flights out of Philadelphia on Nov. 6, 2015. Grand Travel Airlines has to

Write a SQL statement to list flight numbers and Airplane models for flights out of Philadelphia on Nov. 6, 2015.

Grand Travel Airlines has to keep track of its flight and airplane history.

A flight is uniquely identified by the combination of a flight number and a date;

Every passenger who has flown on Grand Travel has a unique passenger number;

For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it;

Clearly, a passenger may have taken many flights and every flight has had many passengers on it;

A pilot is identified by a unique pilot number;

A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights;

Each airplane has a unique serial number. A flight on a particular date used one airplane.

The tables are as follows.

PILOT

Column Name

Key Type

Null/ unique

FK Table

FK Column

Data type

Max length

PilotNum

PK

NN, U

INT

9

FirstName

NN

Varchar

50

LastName

NN

Varchar

50

DateOfBirth

NN

Date

DateOfHire

NN

Date

FLIGHT

Column Name

Key Type

Null/ unique

FK Table

FK Column

Data type

Max length

FlightNum

PK

NN

INT

9

DepartureDate

PK

NN

Date

Origin

NN

Char

3

Destination

NN

Char

3

PilotNum

FK

NN

PILOT

PilotNum

INT

9

AirplaneNum

FK

NN

AIRPLANE

AirplaneNum

INT

9

PASSENGER

Column Name

Key Type

Null/ unique

FK Table

FK Column

Data type

Max length

PassengerNum

PK

NN, U

INT

9

FirstName

NN

Varchar

50

LastName

NN

Varchar

50

StreetAddress

NN

Varchar

100

City

NN

Varchar

50

State

NN

Char

2

PhoneNum

NN

Varchar

20

RESERVATION

Column Name

Key Type

Null/ unique

FK Table

FK Column

Data type

Max length

FlightNum

PK,FK

NN

FLIGHT

FlightNum

INT

9

Date

PK

NN

Date

PassengerNum

PK,FK

NN

PASSENGER

PassengerNum

Varchar

50

Fare

NN

Decimal

9,2

ReservationDate

NN

Date

AIRPLANE

Column Name

Key Type

Null/ unique

FK Table

FK Column

Data type

Max length

AirplaneNum

PK

NN,U

INT

9

Model

NN

Varchar

25

PassengerCapacity

NN

INT

4

YearBuilt

NN

Date

Manufacturer

NN

Varchar

0

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Databases questions