A-LevelComputer ScienceDatabases9618/12 · May/June 2023 · Q217 Marks

A horse riding school uses a database, Lessons, to store data about lesson bookings. This database is created and managed using a Database Management System (DBMS).

(a)The table contains names and descriptions of DBMS features and tools. Complete the table by writing down the missing names and descriptions. Name Description Data dictionary Query processor A model of a database that is not specific to one DBMS. A software tool that allows the user to create items such as tables, forms and reports.[4]
(b)Explain the reasons why referential integrity is important in a database.[3]
(c)(i)The database Lessons has the following tables: HORSE (HorseID, Name, Height, Age, HorseLevel) STUDENT(StudentID, FirstName, LastName, RiderLevel, PreferredHorseID) LESSON(LessonID, Date, Time, StudentID, HorseID, LessonContent) Dates in this database are stored in the format #DD/MM/YYYY#. The fields RiderLevel and HorseLevel can only have the values: Beginner, Intermediate or Advanced. Describe two methods of validating the field RiderLevel.[2]
(c)(ii)Write a Structured Query Language (SQL) script to return the names of all the horses that have the horse level intermediate or beginner.[4]
(c)(iii)The following SQL script should return the number of riders that have the rider level beginner and have a lesson booked on 09/09/2023. SELECT SUM(STUDENT.RiderLevel) AS NumberOfRiders FROM STUDENT, LESSON WHERE StudentID = StudentID OR Date = #09/09/2023# AND STUDENT.RiderLevel = Beginner; There are four errors in the script. Identify and correct each error.[4]

Mark scheme

Marking points from the official mark scheme (May/June 2023). The mark or mark type is shown in brackets.

[4] 1 mark for each correct feature or description Feature | Description Data dictionary | Data about the data in the database // data about the structure of the database // metadata for a database Query processor | Software that allows the user to enter criteria, then finds and returns the appropriate result // software that processes and executes queries written in SQL Logical schema | A model of a database that is not specific to one DBMS Developer interface | A software tool that allows the user to create items such as tables, forms and reports
[3] 1 mark each to max 3 • Referential Integrity makes sure data is consistent • Referential Integrity makes sure all data is up-to-date • Referential integrity ensures that every foreign key has a corresponding primary key • Referential Integrity prevents records from being added / deleted / modified incorrectly • Referential Integrity makes sure that if data is changed in one place the change is reflected in all related records • Referential Integrity makes sure any queries return accurate and complete results
[2] 1 mark each to max 2 • Presence check to make sure that the (rider level) is entered • Look-up / Existence check to make sure the rider level is only Beginner, Intermediate or Advanced • Length check to make sure the rider level entered is either 8 or 12 characters • Type check to make sure the rider level is alphanumeric
[4] 1 mark each • SELECT field Name • FROM table HORSE • WHERE with Intermediate / Beginner • OR with Beginner / Intermediate Example answer: SELECT Name FROM HORSE WHERE HorseLevel = "Intermediate" OR HorseLevel = "Beginner";
[4] 1 mark each • SUM should be COUNT // SELECT COUNT(STUDENT.RiderLevel) • The WHERE statement needs the table names before each field name // WHERE STUDENT.StudentID = LESSON.StudentID • The OR should be AND // AND Date = #09/09/2023# • Beginner is missing the speech marks // STUDENT.RiderLevel = "Beginner";