A-LevelComputer ScienceDatabases9618/12 · Oct/Nov 2025 · Q410 Marks

A relational database, SHIPPING, stores data about the ships in a company and the containers that are carried on the ships. The database has the following tables: CONTAINER(ContainerID, Type, Weight, OwnerName, ShipID) SHIP(ShipID, Type, Capacity, ShipName)

(a)Describe the relationship between the two tables. Refer to the primary and foreign keys in your answer.[2]
(b)The table CONTAINER needs an additional field to store the data for the last inspection date. Write a Structured Query Language (SQL) script to add one field to the table CONTAINER to store the date of last inspection of the container, for example 08/07/2019.[2]
(c)Write an SQL script to return the number of containers stored in the database for the ship with the name Caledonia.[4]
(d)Describe the purpose of a developer interface in a Database Management System (DBMS).[2]

Mark scheme

Marking points from the official mark scheme (Oct/Nov 2025). The mark or mark type is shown in brackets.

4(a) 1 mark per bullet point, max 2 marks
The relationship between SHIP and CONTAINER is one-to-many (1:M)
The primary key ShipID in the SHIP table is linked to the foreign key ShipID in the CONTAINER table
4(b) 1 mark per bullet point, max 2 marks
ALTER TABLE statement
Including the additional field with a suitable field name and suitable field type
Example answer: ALTER TABLE CONTAINER ADD InspectionDate DATE;
4(c) 1 mark per bullet point, max 4 marks
SELECT COUNT statement
Using the correct tables
Joining the tables
The condition for the name
Example Answer 1: SELECT COUNT(ContainerID) FROM CONTAINER, SHIP WHERE CONTAINER.ShipID = SHIP.ShipID AND ShipName = "Caledonia";
Example Answer 2: SELECT COUNT(ContainerID) FROM CONTAINER INNER JOIN SHIP ON CONTAINER.ShipID = SHIP.ShipID WHERE ShipName = "Caledonia";
4(d) 1 mark per bullet point, max 2 marks
To allow the user to create / modify / delete tables // maintain the database
To allow the user to set up / modify relationships
To allow the user to create a form for data input
To allow a user to add tools to a form // for example, drop-down boxes / buttons etc.
To allow a user to design a report (to show the output in an organised manner)
To allow a user to add a menu to enable a choice of different actions
To allow a user to inspect the database contents and metadata ... by creating and/or running different SQL queries

On Oracle Prep you can write or photograph your own answer and have it marked by AI against this mark scheme, with feedback on what you missed.