City Guilds Model Questions Vol1 2018 Information Technology Question 5

Practice practical question 5 from the 2018 City Guilds Model Questions Vol1 Information Technology examination. This question covers Database Design, SQL Query Writing, Normalization, Data Migration, Concurrency Control .

City Guilds Model Questions Vol1 2018 Information Technology Practical
Topics: Database Design SQL Query Writing Normalization Data Migration Concurrency Control
Question 5 CITY_GUILDS_MODEL_QUESTIONS_VOL1 • 2018 • INFORMATION_TECHNOLOGY • practical

TechMart Ltd. maintains a relational database for its e‑commerce operations. The database currently contains the following tables (primary keys are underlined): Customers(**CustomerID**, Name, Email, Phone, City) Products(**ProductID**, ProductName, Category, UnitCost, UnitPrice, StockQty) Orders(**OrderID**, CustomerID, OrderDate, TotalAmount) OrderItems(**OrderItemID**, OrderID, ProductID, Quantity, UnitPrice) All foreign‑key relationships are correctly defined. The store wishes to perform several tasks related to reporting, normalisation, data migration and concurrency control. Answer the following sub‑questions.

Question Parts

( a )
Write an SQL statement that lists the three customers who have spent the greatest total amount on orders placed in the last six months. Display CustomerID, Name and the total amount spent, ordered from highest to lowest spender.
( b )
The Products table is intended to be in Third Normal Form (3NF). Identify any functional dependencies that breach 3NF and propose a revised schema (list new tables and their attributes) that satisfies 3NF.
( c )
A CSV file containing new product data is to be imported. The file has columns: ProductName, Category, UnitCost, UnitPrice, StockQty. Some product names appear more than once with different Category values. Outline a systematic procedure, including the necessary SQL statements, to detect such duplicate product names, resolve the conflict, and insert only valid, non‑duplicate rows into the database.
( d )
During peak periods two users sometimes experience deadlocks when updating StockQty in the Products table. Explain the likely cause of these deadlocks and recommend two practical measures that the database administrator can implement to minimise them.

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

The question covers Database Design, SQL Query Writing, Normalization, Data Migration, Concurrency Control .

View More City Guilds Model Questions Vol1 2018 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