City Guilds Model Questions Vol1 2025 Information Technology Question 5

Practice practical question 5 from the 2025 City Guilds Model Questions Vol1 Information Technology examination. This question covers Database Design, Normalization, SQL Queries, Triggers .

City Guilds Model Questions Vol1 2025 Information Technology Practical
Topics: Database Design Normalization SQL Queries Triggers
Question 5 CITY_GUILDS_MODEL_QUESTIONS_VOL1 • 2025 • INFORMATION_TECHNOLOGY • practical

An online retail store maintains a relational database to record its customers, products, orders and the items contained in each order. The information required for each entity is listed below: Customers: CustomerID, FullName, Email, Phone, Address Products: ProductID, ProductName, Category, UnitPrice, UnitsInStock Orders: OrderID, CustomerID, OrderDate, TotalAmount OrderItems: OrderID, ProductID, Quantity, UnitPrice Using this information, answer the following questions.

Question Parts

( a )
Propose a set of relational tables for the above data. For each table give its name and clearly indicate the primary key.
( b )
State the highest normal form (1NF, 2NF, 3NF or BCNF) that each of the tables you proposed satisfies and give a brief justification.
( c )
Write an SQL query that lists the FullName of every customer who, in the last 30 days, has placed orders whose total amount exceeds ₦50,000.
( d )
The store wants to prevent overselling of products. Describe the logic of a BEFORE INSERT trigger (or stored procedure) on the OrderItems table that checks the UnitsInStock before an order item is recorded, and updates the stock if the insert is allowed. Include the key conditions that must be tested.
( e )
Identify two possible data anomalies that could arise from the proposed design and suggest a modification to eliminate each anomaly.

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 City Guilds Model Questions Vol1 2025 Information Technology Question 5. It is one of the practical questions from the 2025 City Guilds Model Questions Vol1 Information Technology examination.

The question covers Database Design, Normalization, SQL Queries, Triggers .

View More City Guilds Model Questions Vol1 2025 Information Technology Questions More City Guilds Model Questions Vol1 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