Neco 2024 Data Processing Question 5

Practice essay / theory question 5 from the 2024 Neco Data Processing examination. This question covers Database Design, Normalization, SQL Queries, Transaction Management .

Neco 2024 Data Processing Essay / Theory
Topics: Database Design Normalization SQL Queries Transaction Management
Question 5 NECO • 2024 • DATA_PROCESSING • essay

A small retail store maintains a relational database that records information about its products, suppliers and purchase orders. The tables are defined as follows: Products( PID, PName, UnitPrice, SupplierID ) Suppliers( SID, SName, City ) Orders( OID, PID, Qty, OrderDate ) Assume that: - PID uniquely identifies each product. - SID uniquely identifies each supplier. - OID uniquely identifies each order. - Each product is supplied by exactly one supplier (SupplierID references SID). - The store records the quantity (Qty) of a product ordered on a particular OrderDate. Answer the following questions:

Question Parts

( a )
State the primary key of each table and list any functional dependencies that violate the Third Normal Form (3NF) in the above design.
( b )
Decompose any table that is not in 3NF into relations that satisfy 3NF. Give the resulting relation schemas.
( c )
Write an SQL statement that lists the names of suppliers who supplied products whose total purchase amount (UnitPrice × Qty) exceeded ₦500,000 during March 2024. Explain briefly how the query works.
( d )
The store wishes to execute a transaction that (i) updates the UnitPrice of a product and (ii) inserts a new order for that product. Explain how the ACID properties ensure the transaction is safe, and state an isolation level that would prevent a lost‑update problem.

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 2024 Data Processing Question 5. It is one of the essay questions from the 2024 Neco Data Processing examination.

The question covers Database Design, Normalization, SQL Queries, Transaction Management .

View More Neco 2024 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