For the relational algebra queries, you can cut and paste the following as needed into your answer: p subscript (A) σ subscript (A) A ⋈ subscript B ρ (A(1→stuff), B) A ∩ B A U B symbol subscript1 ≤ subscript2 ˄ subscript3 ≥ subscript4 ˅ subscript5 < > subscript6 Consider the following database schema: Flights(flno, from, to, distance) Aircraft(aid, aname, cruisingrange) Certified(eid, aid) Employees(eid, ename, salary) In the above schema, Employees describes all employees, while Certified represents the fact that a given employee is certified to fly a given aircraft. Thus, all employees who are pilots will be listed in Certified (and no other employees will). An aircraft is able to fly a given flight if the aircraft’s cruisingrange is at least as great as the flight’s distance. a) Express the following query in both (i) Relational Algebra and (ii) SQL: Find the departure airport (from) and arrival airport (to) of flights that are able to be flown by the aircraft with aid 777. b) Express the following query in Relational Algebra only: Find the eids of pilots who are certified to fly every aircraft. c) Express the following query in both (i) Relational Algebra and (ii) SQL: Find the aid and aname of the aircraft with the highest cruisingrange. d) Express the following query in Relational Algebra only: Find the aid and aname of aircraft for which no pilot is certified. e) Express the following query in SQL only: For each pilot (identified by eid), find the number of aircraft they are certified to fly.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Consider the following database schema:

Flights(flno, from, to, distance)

Aircraft(aid, aname, cruisingrange)

Certified(eid, aid)

Employees(eid, ename, salary)

In the above schema, Employees describes all employees, while Certified represents the fact that a given employee is certified to fly a given aircraft. Thus, all employees who are pilots will be listed in Certified (and no other employees will). An aircraft is able to fly a given flight if the aircraft’s cruisingrange is at least as great as the flight’s distance.

a) Express the following query in both (i) Relational Algebra and (ii) SQL:

Find the departure airport (from) and arrival airport (to) of flights that are able to be flown by the aircraft with aid 777.

b) Express the following query in Relational Algebra only:

Find the eids of pilots who are certified to fly every aircraft.

c) Express the following query in both (i) Relational Algebra and (ii) SQL:

Find the aid and aname of the aircraft with the highest cruisingrange.

d) Express the following query in Relational Algebra only:

Find the aid and aname of aircraft for which no pilot is certified.

e) Express the following query in SQL only:

For each pilot (identified by eid), find the number of aircraft they are certified to fly.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions