O-LevelComputer ScienceDatabases2210/22 · Oct/Nov 2017 · Q610 Marks

A database table, TRAIN, is to be set up for a railway company to keep a record of the engines available for use. Each engine has a unique number made up of 5 digits, nnnnn. The engines are classified as freight (F) or passenger (P) together with a power classification that is a whole number between 0 and 9, for example F8. The railway company keeps a record of the date of the last service for each engine.

(a)Identify the three fields required for the database. Give each field a suitable name and data type. Provide a sample of data that you could expect to see in the field. Field 1 Name Data type Data sample Field 2 Name Data type Data sample Field 3 Name Data type Data sample[6]
(b)State the field that you should choose as the primary key.[1]
(c)Using the query-by-example grid below, write a query to identify all passenger engines that have not been serviced in the past 12 months. Only display the engine numbers. Field: Table: Sort: Show: Criteria: or:[3]

Mark scheme

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

1 mark for each field suitable name, 1 mark for appropriate data type and appropriate data sample
The following are examples there are many different correct answers.
Engine Number, text, 21012
Class, text, P6
Service Date, date, 4/3/2017
Engine Number // Correct field number
Field: Engine Number, Class, Service Date
Table: TRAIN, TRAIN, TRAIN
Sort:
Show: [✓]
Criteria: Like 'P*' // Like 'P?'
<10/11/2016
(1 mark)

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.