Question

Given the following tables: Aircraft (ano, amodel, mfgYr) Fleet (fno, src, dest, ano, pno) Pilot (pno,pname,gender). Write Relational Algebra Query for the following. (i) Find those female pnames who has not taken a fleet dest of ‘Alabama’ but taken a fleet src to ‘Bahama’ (ii) Retrieve the fno, amodel of the aircraft manufactured in 2015 that the male pilot has had a flight on

Answer 1

(i) Result_1 will contain the subset of the fleet having src equal to Bahama (src = 'Bahama') and dest not equal to Alabama (dest <> 'Alabama').

Result_2 will contain the subset of the table pilot such that their gender is female.

Finally, we will join the Result_1 and Result_2 such that pno of the Result_1 and Result_2 should be same. And, then we will extract the column pname.

(ii). Result_1 will contain the subset of the Aircraft table having mfgYr = 2015.

Result_2 will contain the subset of Pilot table such that the gender should be only male.

Result_3 will be equijoin of Result_2 and Fleet with condition that pno should be same.

Finally, we will join the Result_3 with Result_1 with condition that ano of both the Result_1 and Result_3 should be same. And, then we will extract the column fno and model into Result.