City Guilds Model Questions Vol1 2023 Information Technology Question 5

Practice practical question 5 from the 2023 City Guilds Model Questions Vol1 Information Technology examination. This question covers Database design, Normalization, SQL queries, Data storage .

City Guilds Model Questions Vol1 2023 Information Technology Practical
Topics: Database design Normalization SQL queries Data storage
Question 5 CITY_GUILDS_MODEL_QUESTIONS_VOL1 • 2023 • INFORMATION_TECHNOLOGY • practical

An e‑commerce start‑up, ShopEase, intends to develop a relational database to record its product catalogue, customer details, order processing and payments. The initial un‑normalised record that the system currently stores for each transaction is shown below: OrderID | OrderDate | CustomerName | CustomerEmail | CustomerPhone | ProductID | ProductName | ProductCategory | UnitPrice | Quantity | PaymentMethod | PaymentDate | PaymentAmount --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- (Example row) 1023 | 2023‑09‑15 | Ada Okafor | ada.ok@shop.com | 0803123456 | P045 | "Wireless Mouse" | "Accessories" | 2500.00 | 2 | "Card" | 2023‑09‑15 | 5000.00 Based on this information, answer the following questions.

Question Parts

( a )
List all functional dependencies that can be inferred from the un‑normalised record. Using these dependencies, propose a set of relational tables (including table names) that would store the same information without redundancy. For each table, specify its attributes and identify the primary key.
( b )
Show, step‑by‑step, how the proposed design is normalised to the Third Normal Form (3NF). Indicate any tables that need to be split and give the final set of tables with their primary and foreign keys.
( c )
Write an SQL statement that lists the names and email addresses of customers who, in the month of September 2023, placed orders containing more than three distinct products. The result should be ordered alphabetically by customer name.

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

The question covers Database design, Normalization, SQL queries, Data storage .

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