City Guilds Model Questions Vol1 2022 Information Technology Question 5

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

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

A small e‑commerce company wants to store information about its customers, orders and products in a relational database. The data collected during the first month of operation are shown in the table below. Table 1: Sample transaction data | CustomerID | CustomerName | CustomerPhone | OrderID | OrderDate | ProductID | ProductName | Quantity | UnitPrice | |------------|--------------|---------------|---------|------------|-----------|-------------|----------|-----------| | C001 | Alice | 0801111111 | O1001 | 2022-01-05 | P01 | Widget | 2 | 1500 | | C001 | Alice | 0801111111 | O1002 | 2022-01-12 | P02 | Gizmo | 1 | 2500 | | C002 | Bayo | 0802222222 | O1003 | 2022-01-08 | P01 | Widget | 1 | 1500 | | C003 | Chidi | 0803333333 | O1004 | 2022-01-15 | P03 | Doohickey | 3 | 500 | | C002 | Bayo | 0802222222 | O1005 | 2022-01-20 | P02 | Gizmo | 2 | 2500 | | C001 | Alice | 0801111111 | O1006 | 2022-01-25 | P03 | Doohickey | 1 | 500 | Using the information above, answer the following questions.

Question Parts

( a )
Identify all non‑trivial functional dependencies that hold in the relation represented by Table 1.
( b )
Based on the functional dependencies you identified, decompose the original relation into a set of tables that are in Third Normal Form (3NF). List each resulting table with its attributes.
( c )
Write the SQL statements required to create each of the 3NF tables you listed in part (b). Include primary‑key definitions and appropriate foreign‑key constraints.
( d )
Write an SQL query that returns the names of customers who have ordered more than one distinct product, together with the number of distinct products ordered by each such customer.
( e )
The database will be used to generate a monthly sales report. Identify two possible sources of error that could affect the accuracy of the report and suggest a practical method to minimise each error.

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

The question covers Database design, Normalization, SQL .

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