Comp 231 Database Management Systems

1
Comp 231 Database Management Systems
3. Relational Algebra Exercises
2
Find ids of sailors whove reserved boat with
bid 103

• We use the following database schema
• Sailors (sid, sname),
• Reserves (sid, bid, date),
• Boats (bid,bname,color)
• Question Give an ER diagram for the above
  tables.
• ?sid(?bid103Reserves)

3
Find names of sailors whove reserved boat with
bid 103
Sailors(sid, sname), Reserves(sid,bid,date),
Boats(bid,bname,color)

• Solution 1
• ?sname(?Reserves.sidSailors.sid ?
  bid103(Reserves x Sailors)))

Solution 2 ?sname(?Reserves.sidSailors.sid
((?bid103Reserves) x Sailors))
Solution 3 ?sname((?bid103Reserves)
JOINsidSailors)
4
Find names of sailors whove reserved a red boat
Sailors(sid, sname), Reserves(sid,bid,date),
Boats(bid,bname,color)

• Solution 1
• ?sname((?colorredBoats) JOINbid Reserves
  JOINsidSailors)

Can you give a more efficient solution?
?sname((?bid(?colorredBoats)) JOINbid Reserves
JOINsidSailors)
query optimization real systems do such
optimizations based on algebra
5
Find the names of sailors whove reserved a red
or a green boat
Sailors(sid, sname), Reserves(sid,bid,date),
Boats(bid,bname,color)

• Can identify all red or green boats, then find
  sailors whove reserved one of these boats
• ?sname((?colorred ? colorgreen Boats) JOINbid
  Reserves JOINsidSailors)

What happens if ? is replaced by ? in this
query?
6
Find the names of sailors whove reserved a red
and a green boat
Sailors(sid, sname), Reserves(sid,bid,date),
Boats(bid,bname,color)

• Previous approach wont work! Must identify
  sailors whove reserved red boats, sailors whove
  reserved green boats, then find the intersection

Is this solution correct? ?sname((?colorredBoats
) JOINbid Reserves JOINsidSailors)
? ?sname((?colorgreenBoats) JOINbid Reserves
JOINsidSailors)
Correct solution ?sname ?sid,sname((?colorredB
oats) JOINbid Reserves JOINsidSailors) ?
?sid,sname((?colorgreenBoats) JOINbid Reserves
JOINsidSailors)
7
Find ids of sailors who have made at least two
reservations on the same date
Sailors(sid, sname), Reserves(sid,bid,date),
Boats(bid,bname,color)

• We have to use rename p(R1,Reserves),
  p(R2,Reserves)
• ?R1.sid(?R1.sidR2.sid ? R1.dateR2.date ? R1.bid
  ? R2.bid (R1 x R2))
• Or equivalently
• ?R1.sid(?R1.sidR2.sid ? R1.dateR2.date ? R1.bid
  ? R2.bid (p(R1,Reserves) x p(R2,Reserves)))
• ?R1.sid (p(R1,Reserves) JOINR1.sidR2.sid ?
  R1.dateR2.date ? R1.bid ? R2.bid p(R2,Reserves))

What happens if we omit R1.dateR2.date? What
happens if we omit R1.bid ? R2.bid?
8
Find the ids of sailors whove reserved all boats
Sailors(sid, sname), Reserves(sid,bid,date),
Boats(bid,bname,color)

• Uses division schemas of the input relations
  must be carefully chosen
• (?sid,bid Reserves) / ?bid Boats

What about the query find the ids of sailors who
have reserved all red boats (?sid,bid Reserves) /
?bid (?colorredBoats)
What about the query find the names of sailors
who have reserved all red boats ?sname(Sailors
JOINsid (?sid,bid Reserves) / ?bid
(?colorredBoats))