Gce Model Questions Vol1 2019 Computer Studies Question 9

Practice essay / theory question 9 from the 2019 Gce Model Questions Vol1 Computer Studies examination. This question covers Relational database design, SQL, Transaction control, Indexing, Data integrity .

Gce Model Questions Vol1 2019 Computer Studies Essay / Theory
Topics: Relational database design SQL Transaction control Indexing Data integrity
Question 9 GCE_MODEL_QUESTIONS_VOL1 • 2019 • COMPUTER_STUDIES • essay

A small online bookstore maintains its inventory and sales data in a relational database.

Question Parts

( a )
The database contains two tables: Books(BookID, Title, Author, Price, Stock) and Sales(SaleID, BookID, Quantity, SaleDate). Explain how to enforce referential integrity between the two tables and provide the exact SQL statements required.
( b )
The manager requires that a transaction must never reduce the Stock value below zero. Describe a suitable transaction‑control mechanism, stating the isolation level to be used and any additional checks or constraints that should be implemented.
( c )
Prepare a SQL query that lists, for each author, the total sales revenue generated in the last quarter (October–December). Explain how GROUP BY and aggregate functions are used in the query.
( d )
The Sales table is becoming large and query performance is degrading. Recommend two indexing strategies that would improve the speed of the query in part (c). Justify each choice and discuss any possible trade‑offs.

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

The question covers Relational database design, SQL, Transaction control, Indexing, Data integrity .

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