Gce Model Questions Vol1 2024 Computer Studies Question 9

Practice essay / theory question 9 from the 2024 Gce Model Questions Vol1 Computer Studies examination. This question covers Database Management Systems, Entity‑Relationship modelling, Normalization, SQL query writing, Indexing .

Gce Model Questions Vol1 2024 Computer Studies Essay / Theory

Topics: Database Management Systems Entity‑Relationship modelling Normalization SQL query writing Indexing

Question 9 GCE_MODEL_QUESTIONS_VOL1 • 2024 • COMPUTER_STUDIES • essay

A small online bookstore maintains information about its customers, books, and purchases. The following facts are recorded: - Each customer has a unique CustomerID, a name, an email address and a residential city. - Each book has a unique ISBN, a title, an author, a price and a stock quantity. - A purchase records the CustomerID, the ISBN of the book bought, the quantity purchased, the date of purchase and the total amount (price × quantity). - A customer may purchase many books and a book may be bought by many customers. - The store wants to keep a record of the last purchase date for each customer. Using this scenario, answer the following questions:

Question Parts

( a )

Draw (describe) a complete ER diagram for the scenario. Identify all entities, their key attributes, any non‑key attributes, and the relationships with appropriate cardinalities.

( b )

Convert the ER diagram into a set of relational tables. For each table list its primary key and any foreign keys.

( c )

Normalise the tables obtained in part (b) to Third Normal Form (3NF). State any functional dependencies that required decomposition and describe the anomalies eliminated.

( d )

Write an SQL query that lists the CustomerName, BookTitle, PurchaseDate and TotalAmount for all purchases made in the last 30 days by customers whose city is Lagos. The result should be ordered by PurchaseDate descending.

( e )

Discuss two advantages of creating an index on the PurchaseDate column of the Purchases table. Include the effect on query performance and any possible drawbacks.

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

The question covers Database Management Systems, Entity‑Relationship modelling, Normalization, SQL query writing, Indexing .

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