May/June 2026 Paper 13

A database needs designing for a school library. The library stores data about books including the author, title, category and number of pages. The library will also store data about the students and the books they have borrowed. The data needs to include the student's name and student's class. Books are borrowed for 14 days. A book is late if it has not been returned within the 14 days. The database needs to store whether the book is late, or not late. Create a 3-table design for the library database normalised to Third Normal Form (3NF). Give your table design in the format: TableName(Field1, Field2, Field3, …) Underline a primary key with a solid line and a foreign key with a broken line.

Explain the reasons why the library database should enforce referential integrity.

The school is also creating a database to store data about the departments, the staff and classes in the school. The database is called SCHOOLDATA. The database has four tables:

01STAFF(StaffID, FirstName, FamilyName, DepartmentID)02DEPARTMENT(DepartmentID, DepartmentName, DepartmentLeader)03CLASS(ClassID, YearGroup, SubjectCode)04SUBJECT(SubjectCode, SubjectName, DepartmentID)

The table shows some sample data for SUBJECT.

The Structured Query Language (SQL) scripts to create the database and the table SUBJECT are shown.

0101  CREATE DATA BASE SCHOOLDATA;0202  CREATE TABLE SUBJECT(0303      SubjectCode VARCHAR NONULL,0404      SubjectName VARCHAR0505      DepartmentID VARCHAR,0606      PRIMARY KEY (SubjectCode),0707      FOREIGN KEY (DepartmentID) REFERENCES (DepartmentID)0808  );

The SQL scripts contain four errors. Give the line number of each error and the corrected line.

Write an SQL script to return each department's name and the number of subject codes for that department. The result needs to include a suitable field name for the number of subject codes. Order the results in ascending order of department name.