Question $4.$

$[50$ marks$]$

A relational database schema consists of four relation schemas for a fast$-$food enterprise with many branches.

$-$ The STAFF relation stores the personal data of every staff member.

$-$ The BRANCH relation stores the address data of every branch.

$-$ Each staff works in one branch. The WORKIN relation stores the salary and the branch of every staff.

$-$ Each staff has at most one supervisor. The MANAGE relation stores every pair of subordinate and supervisor. Both MemberID and ManagerID are foreign keys referencing STAFF.StaffID.

In each sub$-$question $4($a$)-($f$),$ please convert each English statement into

$($i$)$ one relational algebra expression and

$($ii$)$ one SQL statement.

$4($a$)$

$[8$ marks$]$

Use Natural Join $/$ Inner Join to find the staff names, staff cities, and salaries of all employees who work in the branch named $\text{'}$B$1\text{'}$ and earn more than $\backslash (\backslash$$ $26,000\backslash ).$

$4($b$)$

$[8$ marks$]$

Use Cartesian Product to find the staff names, staff cities, and salaries of all employees who work in the branch named $\text{'}$B$1\text{'}$ and earn more than $\backslash (\backslash$$ $26,000\backslash ).$

$4($c$)$

$[8$ marks$]$

Find the staff names, branch streets, and branch cities for all employees who work in the same cities where they live.

$4($d$)$

$[8$ marks$]$

Find the names and salaries of all employees from the branch named $\text{'}$B$1\text{'},$ who earn more than the average salary of all employees in the enterprise.