Gce Model Questions Vol1 2020 Computer Studies Question 9

Practice essay / theory question 9 from the 2020 Gce Model Questions Vol1 Computer Studies examination. This question covers Database Management Systems .

Gce Model Questions Vol1 2020 Computer Studies Essay / Theory
Topic: Database Management Systems
Question 9 GCE_MODEL_QUESTIONS_VOL1 • 2020 • COMPUTER_STUDIES • essay

A small retail business wants to computerise its inventory and sales records. The information collected is as follows: - Each product has a unique ProductID, a name, a unit price and a current stock quantity. - Each customer has a unique CustomerID, a name, an address and a phone number. - Sales are recorded with a unique SaleID, the date of sale, the CustomerID of the purchaser and one or more line items. Each line item records the ProductID sold, the quantity sold and the selling price per unit (which may differ from the standard unit price due to discounts). - The business wishes to keep a history of stock levels before and after each sale. Using this description, answer the following questions.

Question Parts

( a )
Identify the main entities and their attributes. For each entity, specify a suitable primary key.
( b )
Convert the identified entities into a relational schema. Show the tables with their fields, primary keys and foreign keys, and explain how you would normalise the design to at least Third Normal Form (3NF).
( c )
Write the SQL statements required to create each of the tables defined in part (b). Include appropriate data types, primary‑key constraints and foreign‑key constraints.
( d )
Write an SQL query that returns, for the most recent calendar month, each product’s name together with the total quantity sold and the total revenue (quantity × selling price). Order the result by total revenue in descending order.
( e )
Discuss two advantages of using a relational DBMS rather than a traditional file‑processing system for this retail business.

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 Gce Model Questions Vol1 2020 Computer Studies Question 9. It is one of the essay questions from the 2020 Gce Model Questions Vol1 Computer Studies examination.

The question covers Database Management Systems .

View More Gce Model Questions Vol1 2020 Computer Studies Questions More Gce 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