Overview
Recap: Base Operators
Week 7: Worksheet
CSC 343 Winter 2021 University of Toronto Mississauga
Recap: Extended Operators
March 4/5, 2021
1
Let¡¯s do an example together!
Given the following relations:
Flights(fno:integer, from:string, to:string, distance:integer, departs:time, arrives:time)
Aircraft(aid:integer, aname:string, cruisingrange:string) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer)
Note: the Employees relation describes pilots and other kinds of employees as well; every pilot is certified for some aircraft (otherwise, they would not qualify as a pilot), and only pilots are certified to fly.
Write the following queries in Relational Algebra (RA):
1. Find the eids of pilots certified for some Boeing aircraft.
2. Find the aids of all aircraft that can be used on non-stop flights from Toronto to Miami.
3. Find the names of pilots who can operate planes with a range greater than 3, 000 miles but are not certified on any Boeing aircraft.
2
Recall!
Given the following relations:
Suppliers(sid:integer, sname:string, address:string)
Parts(pid:integer, pname:string, colour:string)
Catalog(sid:integer, pid:integer, cost:real)
Please note: keys for each relation are denoted with an underlined. Additionally, the Catalog relation lists the prices charged for parts by Suppliers (i.e. cost).
Last Time:
Find the sids of suppliers who supply some red or green part.
Sample Solution:
SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE (P.colour = ¡®red¡¯ OR P.colour = ¡®green¡¯) AND P.pid = C.pid;
Task!
Utilizing the same relations and sample SQL query above, convert them into Relational Algebra. Note: your SQL query may not translate directly, so you may need to alter the query above.
Find the sids of suppliers who supply some red or green part.
3
Last Time:
Find the pids of parts supplied by at least two different suppliers.
Sample Solution:
SELECT DISTINCT C.pid
FROM Catalog C
WHERE EXISTS (SELECT C1.sid
FROM Catalog C1
WHERE C1.pid = C.pid AND C1.sid <> C.sid);
Task!
Utilizing the same relations and sample SQL query above, convert them into Relational Algebra. Note: your SQL query may not translate directly, so you may need to alter the query above.
Find the pids of parts supplied by at least two different suppliers.
4