City Guilds Model Questions Vol1 2025 Information Technology Question 34

In a relational database the following tables are defined:\n\n- **Employees**(\(EmpID\), \(DeptID\), \(Salary\)) - 10 000 rows.\n- **Departments**(\(DeptID\), \(ManagerID\), \(Budget\)) - 200 rows.\n- **Projects**(\(ProjID\), \(DeptID\), \(Cost\)) - 5 000 rows.\n\nEach department has on average 50 employees and 25 projects. The average project cost is \$200 000 and the average department budget is \$4 000 000.\n\nA query asks for the total salary of employees that belong to departments whose total project cost exceeds the department's budget.\n\nAssuming the database engine can hold at most 1 000 rows in main memory, compare the number of row‑level operations required by a naïve nested‑loop join versus a hash‑join implementation of this query, and state which join method is more efficient under the given memory constraint.