City Guilds Model Questions Vol1 2019 Information Technology Question 5

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

City Guilds Model Questions Vol1 2019 Information Technology Practical
Topics: Database Design Normalization SQL Query Writing Data Analysis
Question 5 CITY_GUILDS_MODEL_QUESTIONS_VOL1 • 2019 • INFORMATION_TECHNOLOGY • practical

A small retail store maintains a relational database to record its sales activities. The following tables and sample data are stored in the system. Customers | customer_id | name | phone | address | |------------|-----------|-------------|-----------------------| | C001 | Alice | 0801111111 | 12 Lagos St | | C002 | Bayo | 0802222222 | 45 Abuja Rd | | C003 | Chidinma | 0803333333 | 78 Port Harcourt Ave | Suppliers | supplier_id | name | contact_email | |------------|---------------|--------------------| | S01 | Alpha Supplies| alpha@sup.com | | S02 | Beta Traders | beta@sup.com | Products | product_id | name | unit_price (₦) | supplier_id | |-----------|------------|----------------|------------| | P100 | Notebook | 500 | S01 | | P101 | Pen | 100 | S01 | | P102 | Calculator | 2500 | S02 | Orders | order_id | order_date | customer_id | |----------|------------|-------------| | O500 | 2023-03-05 | C001 | | O501 | 2023-03-15 | C002 | | O502 | 2023-04-02 | C001 | OrderItems | order_id | product_id | quantity | |----------|------------|----------| | O500 | P100 | 2 | | O500 | P101 | 5 | | O501 | P102 | 1 | | O501 | P100 | 1 | | O502 | P101 | 3 | Based on the above information, answer the following:

Question Parts

( a )
State the functional dependencies for each of the five relations (Customers, Suppliers, Products, Orders, OrderItems). For each relation, indicate the highest normal form (1NF, 2NF, 3NF) it satisfies and justify your answer.
( b )
Write an SQL query that lists the order_id and the number of distinct products purchased in that order for every order where the customer bought more than two different products in a single order. The result should be ordered by order_id ascending.
( c )
Using the sample data provided, calculate the total revenue (in Naira) generated by all orders placed in March 2023. Show the intermediate calculations for each order item used in the total.
( d )
If the attribute contact_email of a supplier were stored in the Products table instead of the Suppliers table, describe one type of data anomaly that could arise and suggest a design change to eliminate the 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 City Guilds Model Questions Vol1 2019 Information Technology Question 5. It is one of the practical questions from the 2019 City Guilds Model Questions Vol1 Information Technology examination.

The question covers Database Design, Normalization, SQL Query Writing, Data Analysis .

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