Relational algebra natural join of table q2 with rename of table q2 for Passenger-Flight-Trip

Question

• Passenger = {pid, pname, pnation} with key {pid}
• Flight = {fno, fdate, forig, fdest} with key {fno,fdate}
• Trip = {pid, fno, fdate, class} with key {pid,fno,fdate}
  and FKs
  [pid] ⊆ Passenger [pid]
  [fno,fdate] ⊆ Flight [fno,fdate]

The question I got given from my tutorial was:

Write in relational algebra: what is id of passengers who have flown First class from Narita more than once?

and the answer that was given to us was:

q1 = σ class=First (Trip) ⋈ σ forig=Narita (Flight)
q2 = π fno,fdate,pid (q1)
q3 = q2 ⋈ δ fno→fno',fdate→fdate' (q2)
q4 = q3 - σ fno=fno' (σ fdate=fdate' (q3))
q5 = π pid (q4)

I'm not quite sure how q3 accomplishes anything... I don't see how renaming the attribute and joining them to the original would help as you would just end up with a table like

fno' | fno  | fdate | fdate' | pid

and all values of fno' will equal all values of fno and same thing for fdate as you are natural joining two of the same table. (It will join on pid and since all values are equal wouldn't you just end up with empty set in q4?)

How does q3 work?

Answer 1

you would just end up with a table like

fno' | fno | fdate | fdate' | pid

and all values of fno' will equal all values of fno and same thing for fdate as you are natural joining two of the same table.

No, it is not the case that in every row fno = fno' and fdate = fdate', because those are four different column names and natural join returns all combinations of rows that union a left row and a right row where the same-named columns have the same value.

(It will join on pid and since all values [of fno & fno' in a row] are equal wouldn't you just end up with empty set in q4?)

Per above they aren't all equal.

In terms of table values The join holds the rows where (fno,fdate,pid) is in q2 AND (fno',fdate',pid) is in the renaming. Those rows are what you get from every combination of a q2 (fno, fdate) and a renaming (fno', fdate') that share the same pid.

In terms of table meanings In a database a table (base or query) holds the rows that make a true proposition (statement) from some predicate (statement template) parameterized by its columns. Eg Trip holds the rows where "pid took fno on fdate in class"; in shorthand, the rows where Trip(pid, fno, fdate, class). If q2 holds the rows where q2(fno,fdate,pid) then the nature of δ (rename) is that δ fno→fno',fdate→fdate'(q2) holds the rows where q2(fno',fdate',pid) and the nature of ⋈ (natural join) is that the natural join of q2 & the rename holds the rows where q2(fno,fdate,pid) AND q2(fno',fdate',pid).

---

(The fact that each operator transforms input predicates into an output predicate is how we form a relational algebra query from an English description:

Flight -- fno flew on fdate from forig to fdest
Trip -- pid took fno on fdate in class
σ class=First (Trip) -- pid took fno on fdate in class AND class=First
σ forig=Narita (Flight) -- fno flew on fdate from forig to fdest AND forig=Narita
q1 -- pid took fno on fdate in class AND class=First
    AND fno flew on fdate from forig to fdest AND forig=Narita
q2 -- FOR SOME class,forig & fdest,
            pid took fno on fdate in class AND class=First
        AND fno flew on fdate from forig to fdest AND forig=Narita
q3 --   FOR SOME class,forig & fdest,
                pid took fno on fdate in class AND class=First
            AND fno flew on fdate from forig to fdest AND forig=Narita
    AND FOR SOME class,forig & fdest,
                pid took fno' on fdate' in class AND class=First
            AND fno' flew on fdate' from forig to fdest AND forig=Narita

Ie:

q3 --   FOR SOME fdest,
                pid took fno on fdate in First
            AND fno flew on fdate from Narita to fdest
    AND FOR SOME fdest,
                pid took fno' on fdate' in First
            AND fno' flew on fdate' from Narita to fdest

)