RELATIONAL ALGEBRA: EXAMPLES
CS 564- Fall 2021
ACKs: , Jignesh Patel, An
EXAMPLE DB: SAILORS
Sailors (sid, sname, rating, age) Reserves (sid, bid, day)
Boats (bid, bname, color)
CS 564 [Fall 2021] – Paris Koutris 2
EXAMPLE DB: SAILORS
Q1: What are the names of the sailors who have reserved boat with name “100” ?
𝜋)”#$% ⋈
Sailors
⋈
Reserves
Boats
CS 564 [Fall 2021] – Paris Koutris
3
𝜎!”#$%&'((
EXAMPLE DB: SAILORS
Q2: What are the names of the sailors who have reserved a red boat ?
𝜋)”#$% ⋈
Sailors
⋈
Reserves
𝜎*+,+-&-%. Boats
CS 564 [Fall 2021] – Paris Koutris
4
EXAMPLE DB: SAILORS
Q3: What are the names of the sailors who have reserved a green or red boat ?
𝜋)”#$% ⋈
Sailors
⋈
Reserves
𝜎*+,+-&-%. ∨*+,+-&0-%%”
Boats
CS 564 [Fall 2021] – Paris Koutris 5
EXAMPLE DB: SAILORS
Q4: What are the names of the sailors who have reserved a green and red boat ? 𝜋)”#$%
𝜋)1.,)”#$% ∩ 𝜋)1.,)”#$% ⋈⋈
Sailors ⋈ Sailors Reserves 𝜎*+,+-&0-%%”
Boats
⋈
CS 564 [Fall 2021] – Paris Koutris
6
Reserves
𝜎*+,+-&-%. Boats
EXAMPLE DB: SAILORS
Q5: Find the names of the sailors who have reserved all boats with name “470”.
𝜋)”#$%
⋈
\
Sailors
𝜋)1.,!1.
𝜋!1. 𝜎”#$%&34(
Reserves
Boats
CS 564 [Fall 2021] – Paris Koutris
7
EXAMPLE DB: PRODUCTS
Product (pid,name,price,category,maker-cid) Purchase (buyer-ssn, seller-ssn, store, pid) Company (cid, name, country)
Person (ssn,name, phone, city)
CS 564 [Fall 2021] – Paris Koutris 8
EXAMPLE DB: PRODUCTS
Q6: Find the phone numbers of the people who have bought iPads from Fred (the salesman).
⋈
𝜋!:;%-8))”,61. ⋈))”&)%,,%-8))”
𝜎”#$%&9-%.
𝜋67+”% ⋈))”&!:;%-8))”
Person
Product Purchase
𝜎”#$%&15#.
Person CS 564 [Fall 2021] – Paris Koutris
9
EXAMPLE DB: PRODUCTS
Q7: Find the names and phone numbers of the people who have bought products from the USA.
𝜋”#$%,67+”% ⋈))”&!:;%-8))”
Person
⋈
𝜋61. ⋈*1.&$#<%-8*1.
𝜎*+:"=-;&>?@ Product
Company CS 564 [Fall 2021] – Paris Koutris
Purchase
10
EXAMPLE DB: PRODUCTS
Q8: Find the names of the people who have bought products only from the USA.
𝜋”#$% ⋈))”&!:;%-8))”
− 𝜋!:;%-8))”
𝜋!:;%-8))”
Person
⋈
Purchase Purchase ⋈*1.&$#<%-8*1.
𝜎*+:"=-;AB>?@ Product
Company
CS 564 [Fall 2021] – Paris Koutris 11
EXAMPLE DB: PRODUCTS
Q9: Find the names of the people who have bought
products from the USA but not from Greece.
𝜋”#$%
−
𝜋!:;%-8))”
𝜋⋈
61. Purchase
⋈*1.&$#<%-8*1. 𝜎*+:"=-;&>?@ Product
⋈))”&!:;%-8))”
𝜋!:;%-8))” 𝜋⋈
Person
61. Purchase ⋈*1.&$#<%-8*1.
Company
𝜎*+:"=-;&C-%%*%
Company
Product
CS 564 [Fall 2021] - Paris Koutris
12
EXAMPLE DB: PRODUCTS
Q10: Find the names of the people who have bought products from the USA and live in Madison.
𝜋"#$%
𝜋!:;%-8))"
𝜋⋈
61. Purchase
⋈*1.&$#<%-8*1.
𝜎*+:"=-;&>?@ Product Company
⋈))”&!:;%-8))”
𝜎*1=;&D#.1)+”
Person
CS 564 [Fall 2021] – Paris Koutris
13
EXAMPLE DB: FLIGHTS
City (cid, name, population)
Flight (fid, length,start-city,end-city,aid) Airline (aid, name, profit)
CS 564 [Fall 2021] – Paris Koutris 14
EXAMPLE DB: FLIGHTS
Q11: Find the flight ids for flights that start in a city with id “MSN” and end in a city with id “LON”.
𝜋F1. ⋈*1.&%”.8*1=;
𝜎*1.&GHE
City
⋈*1.&)=#-=8*1=; 𝜎*1.&D?E
Flight
City
CS 564 [Fall 2021] – Paris Koutris
15
EXAMPLE DB: FLIGHTS
Q12: Find the names of the cities that have a flight for
every airline with profit more than 0.
⋈*1.&)=#-=8*1=;
\ 𝜋)=#-=8*1=;,#1. 𝜋#1.
Flight
𝜋”#$%
City
CS 564 [Fall 2021] – Paris Koutris
Airline
16
𝜎6-+F1=B(