| Date | November 2019 | Marks available | 2 | Reference code | 19N.2.SL.TZ0.3 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Outline | Question number | 3 | Adapted from | N/A |
Question
Trucking On, a logistics and haulage company based in Marseille, France, keeps track of its lorries and operations in a database.
Lorries are made up of the truck and a trailer. Trucks are usually coupled with the same trailer but sometimes trailers are moved to a different truck for part of the year.
Each driver only uses one truck.
The following LORRY table shows information about the lorries.
Trailer space is measured in cubic metres.
The table can also be represented as:
LORRY (TruckID, Truckmake, Energysource, Driver, Tel, TrailerID,
Trailerspace, Coupled_from, Coupled_to)
The table has been split into two tables following the rules of normalization. The resulting two tables are shown with the primary key underlined:
LORRY (TruckID, Truckmake, Energysource, Driver, Tel)
TRAILER (TrailerID, Trailerspace, Coupled_from, Coupled_to, TruckID)
Identify two characteristics that make a database unnormalized.
Outline why data redundancy may be a problem in the LORRY table.
Identify the steps to create a query to find the names and telephone numbers of the drivers who drive lorries that have more than 60 cubic metres of trailer space.
Normalize the database to 3NF. Use the same format as shown, ensuring that primary keys are clearly indicated by underlining them.
Markscheme
Award [2 max].
Note: Allow answers that refer to any stage of normalisation.
Each data item hasn’t be broken down any further / contains repeating data;
Each row is not unique / does not contain a primary key;
Each field/column does not have a unique name / does not have atomic values;
Award [2 max].
This would mean that data is duplicated within a table / allow an example of duplicated data in this table;
However, when it is updated, it may not be updated in all cases;
Which could lead to anomalies within the data and the incorrect information being used;
Award [4 max].
Award [1] for all relevant tables selected (Lorry and Trailer);
Award [1] for all relevant fields selected (Driver and Tel);
Award [1] for correct condition;
Award [1] for correct link between tables;
SELECT Driver, Tel
FROM Lorry INNER JOIN Trailer
ON Trailer.TrailerID = Lorry.TrailerID
WHERE TRAILERSPACE > 60;
Accept logically equivalent answers written in English or a Data Manipulation Language.
Award [7 max].
Award marks as follows:
Separate, correct DRIVER table;
FK links Driver to TRUCK table (driver always drives same truck);
New “journey” table created — called JOURNEY in examples below;
JOURNEY PK either a new JourneyID field, or a composite key as shown below;
Correct FK links to TRUCK and TRAILER tables in JOURNEY table;
Coupled.to and Coupled_from in correct table;
TRAILER table + PK correct;
DRIVER (Driver, Tel)
TRUCK (TruckID, Truckmake, Energysource, Driver)
JOURNEY (TruckID, TrailerID, Coupled_from, Coupled_to)
TRAILER (TrailerID, Trailerspace)
DRIVER (Driver, Tel)
TRUCK (TruckID, Truckmake, Energysource, Driver)
JOURNEY Journey ID, TruckID, TrailerID, Coupled_from, Coupled_to)
TRAILER (TrailerID, Trailerspace)
Examiners report
This was a memory recall question, where many candidates were able to identify at least one characteristic of unnormalized databases. However, many others were not prepared for this question.
The question was connected to a given scenario. Successful candidates were able to provide examples of redundant data affecting the given table, but just few candidates were able to outline consequences.
Most candidates were able to get the basic structure of the query though some of them could not set the two tables and their relationship properly while writing the query. Also, many candidates provided generic answers failing to provide the necessary details (fields, tables, conditions) to answer the query.
Candidates seemed unprepared for this question, most of them were not able to correctly split the table, identify the relationships and in many cases did not identify primary keys (this being explicitly required in the wording of the question).
