Question

Consider the database consisting of the following relations:

Flights(Fl#: integer, From: string, To: string, Distance: integer, departs: Time, Arrives: time)

Flight Instance(Fl#: integer, Day: date, Aid: Integer)

Aircraft(Aid: integer, Make: string, Model: string, CrusingRange: string)

Certified(Eid: integer, Make: string, Model: string) Employee(Eid: integer, Ename: string, Salary: integer)

Fight Attendant(Fl#: integer, Day: date, Eid:integer, Role: string)

The Flight table contains general information about the flights whereas the Flight Instance is about a flight number on a specific date. Aircraft list the aircrafts in the company, the make is the company that made the aircraft (e.g. Boing, Airbus) and the model is for example 747 for Boing or 321 for Airbus. The table Certified tells which employees can y which airplanes, the tables Fight Attendant assigns employees to specific flights with a role (e.g. captain). Finally, the table Employee lists the employees of the airline company. Express the following queries in Algebra and SQL:

List the crew members of the flight 123 on May 5, 2006

List the flight attendants who ew to Chicago at list once since January 1st 2006.

Find the employees who ew only to Chicago.

List the employees who have been to all the cities

List pairs of employees who worked together on at least one flight.

Which employee has own the largest number of flights (SQL only)

Answer 1

1.select e.Eid,e.ename from employee e join flight attendant f on e.Eid=f.eid join on flightinstance i on f.fi=i.fi where fi=123 and date='25-05-2006'

2.select e.eid,e.ename frome employee e join flightattendant f on e.eid=f.eid join flights l on f.fi=l.fi join flight instance i on f.fl=i.fl where l.from='EW' and l.to='chicago' and i.date='01-01-2006';

3. select e.eid,e.ename frome employee e join flightattendant f on e.eid=f.eid join flights l on f.fi=l.fi join flight instance i on f.fl=i.fl where l.to='chicago';

4.select e.eid,e.employees from employees e join flightattendants f on e.eid=f.eid join flights i on i.fi=f.fi where i.fi=f.fi;

Note: for any quires please comment and we cant understand the question properly, we are supposed to answer only 3 questions