City Guilds Model Questions Vol1 2021 Information Technology Question 5

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

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

XYZ Retail Ltd. operates an online store. All sales transactions are recorded in a single table called **SALES** with the attributes listed below. The table stores one row for each product sold in a transaction (i.e., a line‑item). **Attributes**: TransactionID, CustomerName, CustomerAddress, CustomerPhone, ProductCode, ProductDescription, UnitPrice, Quantity, TransactionDate, SalesPersonID, SalesPersonName, SalesPersonRegion. **Sample records**: | TransactionID | CustomerName | CustomerAddress | CustomerPhone | ProductCode | ProductDescription | UnitPrice (₦) | Quantity | TransactionDate | SalesPersonID | SalesPersonName | SalesPersonRegion | |---------------|--------------|-----------------|---------------|------------|--------------------|---------------|----------|-----------------|--------------|----------------|-------------------| | 1001 | Ada Okoro | 12 Lagos St | 0801111111 | P01 | USB Cable | 500 | 2 | 2023‑03‑05 | S01 | Emeka | North | | 1001 | Ada Okoro | 12 Lagos St | 0801111111 | P02 | Mouse | 1500 | 1 | 2023‑03‑05 | S01 | Emeka | North | | 1002 | Bola Ade | 34 Abuja Ave | 0802222222 | P01 | USB Cable | 500 | 3 | 2023‑03‑12 | S02 | Chidi | South | | 1003 | Ada Okoro | 12 Lagos St | 0801111111 | P03 | Keyboard | 2500 | 1 | 2023‑04‑02 | S01 | Emeka | North | Using the information above, answer the following:

Question Parts

( a )
Identify all functional dependencies that hold in the **SALES** table (based on the business rules implied by the attributes). Then decompose the table into a set of tables that are in Third Normal Form (3NF). For each resulting table list its attributes, primary key and any foreign keys.
( b )
Write an SQL statement that lists the total sales amount (UnitPrice × Quantity) for each product (ProductCode) for the month of March 2023. The result should show **ProductCode**, **ProductDescription**, and **TotalSales**, ordered by **TotalSales** in descending order.
( c )
Using the sample records given above, calculate the **TotalSales** for each product for March 2023 as would be returned by the query in part (b). Present the result as a table with the three columns required.
( d )
Discuss two possible data‑integrity problems that could arise in the original un‑normalised **SALES** table and propose appropriate integrity constraints or design changes to prevent 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 2021 Information Technology Question 5. It is one of the practical questions from the 2021 City Guilds Model Questions Vol1 Information Technology examination.

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

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