Question $3$

$(25$ marks$)$

Consider the following relational database schema consisting of the four relation schemas: passenger $($ pid, pname, pgender, pcity$)$ and answer the following questions using relational algebra queries.

agency $($ aid, aname, acity$)$

flight $($fid$,$ fdate, time, src$,$ dest$)$

booking $($pid$,$ aid, fid, fdate$)$

$3\mathrm{.}1$ Retrieve the complete details of all flights to Johannesburg.

$(3$ marks$)$

$3\mathrm{.}2$ Retrieve the details about all flights from Cape Town to Johannesburg.

$(4$ marks$)$

$3\mathrm{.}3$ Use a join to retrieve only the flight numbers for the passenger with pid $9877755$ for flights to Cape Town before $06/11/2023.$

$(5$ marks$)$

Storey, V$.$C$.$ and Goldstein, R$.$C$.,1993.$ Knowledge$-$based approachea io datahase design. Ma Quarterly, pp$\mathrm{.}25-46.$

$2$ Coronel, C$.$ and Marris, s$.,2016.$ Database syatems dasign implamentation. $8$ managamant. Cengage Laarring

$2$

HDBP$200-1-$Jul$-$Dec$2024-$FA$1-$V$3-1906202421062024$

$3\mathrm{.}4$ Use a join to retrieve the agency names for all agencies located in the same city as the passenger with pid $9877755.$

$(4$ marks$)$

$3\mathrm{.}5$ Retrieve the details of all flights that are scheduled on $01/12/2024$ and $02/12/2024$ at $16$:$00$ hours.

$(4$ marks$)$

$3\mathrm{.}6$ Retrieve the details of all flights that are scheduled on either of the dates $01/12/2024$ or $\frac{02}{12}\frac{?}{2024}$ or both at $16$:$00$ hours.

$(5$ marks$)$