Neco 2023 Data Processing Question 5

Practice essay / theory question 5 from the 2023 Neco Data Processing examination. This question covers Relational Algebra, SQL, Normalization, Transaction Management .

Neco 2023 Data Processing Essay / Theory
Topics: Relational Algebra SQL Normalization Transaction Management
Question 5 NECO • 2023 • DATA_PROCESSING • essay

A company maintains a relational database with the following three tables: Employees(EmpID, Name, DeptID, Salary) Departments(DeptID, DeptName, ManagerID) Projects(ProjID, ProjName, DeptID, Budget) Assume that EmpID and DeptID are primary keys of their respective tables and that each project is managed by the department indicated by DeptID. Answer the following questions:

Question Parts

( a )
Write a relational algebra expression that retrieves the names of all employees who work in a department that manages at least one project whose budget exceeds ₦500,000.
( b )
Translate the relational algebra expression from part (a) into an equivalent SQL query.
( c )
The Salary attribute in the Employees table is determined solely by the department to which an employee belongs (i.e., all employees in the same department receive the same salary). Identify any functional dependencies that violate the Third Normal Form (3NF) in the Employees table and propose a decomposition that brings the schema to 3NF.
( d )
Discuss two ACID properties and explain how they are ensured when a transaction transfers an employee from one department to another. The transaction must (i) change the employee’s DeptID in the Employees table and (ii) update the ManagerID of the old and new departments in the Departments table.

Detailed Solution & Marking Scheme

Get the step-by-step solution, key points required by examiners and detailed marking guidance through the Edcrib AI Tutor.

Reveal Answer

About This Question

This is Neco 2023 Data Processing Question 5. It is one of the essay questions from the 2023 Neco Data Processing examination.

The question covers Relational Algebra, SQL, Normalization, Transaction Management .

View More Neco 2023 Data Processing Questions More Neco Practice Questions Back to Questions

Master the Exam!

You've seen a preview, but there are thousands more questions plus AI tutor to break down complex solutions.

Unlock Full Access Available for Android & Windows
Chat with Support