Database Relational Model, Relational Algebra, Relational Calculus

[qingquan-li]

The following tables form an instance of an Airport Database held in a relational DBMS: Airport (airportID, name, city, state, country) Carrier (carrierID, name, fleetSize, employees, revenue) Flight (carrierID, flightNo, date, time, airportID, destination, distance)

Airport Table: Assumption: Each airport name is unique in its country.

airportID	name	city	state	country
CDG	Charles de Gaulle	Paris	NULL	FRA
DXB	Dubai international	Dubai	NULL	UAE
JFK	John F Kennedy International	New York	NY	USA
LGA	LaGuardia	New York	NY	USA
MIA	Miami International	Miami	FL	USA
SFO	San Francisco International	San Francisco	CA	USA

Carrier Table: Assumption: Each carrier name is unique in the whole world.

carrierID	name	fleetSize	employees	revenue
AF	Air France	225	84,602	15,414
DL	Delta Air Lines	855	80,000	39,639
EK	Emirates Airline	239	84,153	18,300
US	US Airways	338	31,467	13,050

Flight Table: Assumption: Any (carrierID, flightNo) combination is unique for the whole day.

carrierID	flightNo	date	time	airportID	destination	distance
DL	426	10/16/2017	7:00 AM	JFK	SFO	2,586
DL	426	10/17/2017	7:00 AM	JFK	SFO	2,586
DL	479	10/16/2017	7:25 PM	JFK	SFO	2,586
EK	721	10/17/2017	11:25 AM	CDG	DXB	3,259
US	783	10/17/2017	3:20 AM	DXB	JFK	6,843
DL	1472	10/17/2017	7:30AM	LGA	MIA	1,099
AF	3577	10/17/2017	5:45 PM	JFK	CDG	3,629
US	3577	10/17/2017	9:40 AM	LGA	SFO	2,576
AF	3634	10/16/2017	1:20 PM	CDG	MIA	4,580

Part A - Relational Model Questions

1. What is the cardinality of the Flight table? 9

2. What is the degree of the Flight table? 7

3. List the candidate key(s) of the Airport table (each in a separate line).

   airportID (name, country)

4. List the candidate key(s) of the Carrier table (each in a separate line).

   carrierID

   name

5. List the foreign key(s) of the Flight table (each in a separate line).

   carrierID

   airportID

   destination (note: don't forget this one!)

6. If the airportID is the primary key of the Airport table, list the alternate key(s) of this table. (name, country)

7. What is the primary key of the Flight table? (carrierID, flightNo, date)

Part B - Relational Algebra Questions

Using the instance of the Airport database, formulate the following queries in the relational algebra:

1. List all airport names. π _name (Airport)

2. List full details of all airports located outside the United States. Note: You should use the not equal sign ≠ in this query. σ _{country ≠ 'USA'} (Airport)

3. List the name and the state of all United States airports. π _{name, state} (σ _{country = 'USA'} (Airport))

4. List the name and the fleet size of all carriers employing more than 50000 workers. π _{name, fleetSize} (σ _{employees > 50000} (Carrier))

5. List the carrier name, the carrierID, the flightNo and the date of all flights.

   π _{name, carrierID, flightNo, date} (Carrier ⨝ Flight)

6. Display the number of airports.

   COUNT airportID (Airport)

7. Display the number of flights with distances longer than 3000 miles. COUNT carrierID (σ _{distance > 3000} (Flight))

8. Display the number of all carriers and the average of their revenues.

   COUNT carrierID, AVERAGE revenue (Carrier)

9. Display the number of flights leaving from JFK and the total of their distances.

   COUNT carrierID, SUM distance (σ _{airportID = 'JFK'} (Flight))

10. Display the minimum, the average, and the maximum distances of the flights that are scheduled on October 17th, 2017?

    MIN distance, AVERAGE distance, MAX distance (σ _{date = '10/17/2017'} (Flight))

Part C - Relational Calculus Questions

Given the Airport Database, let's formulate the queries in Relational Calculus:

1. List all airport names.

   {A.name | Airport(A)}
   

2. List full details of all airports located in NY state.

   {A | Airport(A) A.state = 'NY'}
   

3. List the name and the state of all United States airports.

   {A.name, A.state | Airport(A) A.country = 'USA'}
   

4. List the name and the fleet size of all carriers employing more than 50000 workers.

   {C.name, C.fleetSize | Carrier(C) C.employees > 50000}
   

5. List all flights leaving from United States airports.

   {F | Flight(F) (∃A) (Airport(A) (A.airportID = F.airportID) (A.country = 'USA'))}
   

6. List the carrierID and the distance of all flights landing in NY state airports.

   {F.carrierID, F.distance | Flight(F) (∃A) (Airport(A) (A.airportID = F.destination) (A.state = 'NY'))}
   

7. List the carriers that do not leave from JFK.

   # Using a negative existential quantifier:
   { C | Carrier(C) ~(∃F) (Flight(F) (F.carrierID = C.carrierID) (F.airportID = 'JFK'))}
   

   # Or using the universal quantifier:
   {C | Carrier(C) (∀F) (Flight(F) (F.carrierID ≠ C.carrierID) (F.airportID ≠ 'JFK'))}
   

8. List the names of carriers that have flights landing in France airports.

   {C.name | Carrier(C) (∃F) (∃A) (Flight(F) Airport(A) (C.carrierID = F.carrierID) (F.destination = A.airportID) (A.country = 'FRA'))}