Gce Model Questions Vol1 2018 Computer Studies Question 9

Practice essay / theory question 9 from the 2018 Gce Model Questions Vol1 Computer Studies examination. This question covers Database design, Entity‑relationship modelling, Normalization, SQL query formulation, Referential integrity .

Gce Model Questions Vol1 2018 Computer Studies Essay / Theory
Topics: Database design Entity‑relationship modelling Normalization SQL query formulation Referential integrity
Question 9 GCE_MODEL_QUESTIONS_VOL1 • 2018 • COMPUTER_STUDIES • essay

A secondary school intends to computerise its library management system. The system must keep records of books, authors, categories, students and the borrowing transactions. Design a relational database that satisfies the requirements described below.

Question Parts

( a )
Draw a complete ER diagram for the library system. Include the entities Book, Author, Category, Student and Borrow. For each entity list appropriate attributes and indicate the primary key. State the cardinality of every relationship (e.g., one‑to‑many, many‑to‑many).
( b )
Transform the ER diagram into a set of relational tables. For each table list its fields, primary key and any foreign keys. Then explain how the tables satisfy the requirements of the third normal form (3NF), citing any functional dependencies that were eliminated.
( c )
Write an SQL statement that returns the titles of all books that are currently borrowed by students in class "SS2" and whose due date is earlier than the current date (i.e., overdue). Assume appropriate table and column names based on your design.
( d )
Discuss two advantages of enforcing referential integrity (foreign‑key constraints) in this library database, and two possible drawbacks that might be encountered in a high‑transaction environment.

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

The question covers Database design, Entity‑relationship modelling, Normalization, SQL query formulation, Referential integrity .

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