| Date | November 2020 | Marks available | 2 | Reference code | 20N.2.SL.TZ0.1 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Construct | Question number | 1 | Adapted from | N/A |
Question
The Driving Licensing Agency stores information about individuals who hold a driving license and/or own vehicles.
The following rules apply:
- Each individual may only hold one driving license.
- Each individual may own more than one vehicle.
- Each vehicle may be owned by one individual only.
When an individual applies for a driving license, they have to complete a license application form. The following is an extract from that form:
The data in the form shown above is stored in the Person table. The license application form also requires an individual’s medical information. This is stored in a table called PersonMedical.
The following extract is a sample of the medical questions that are asked.
Figure 2: A sample of the medical questions asked on the license application form
Construct the entity-relationship diagram (ERD) that shows the relationship between the individual, their driving license, and their vehicle(s).
Explain why Date of birth has been separated into three fields.
Explain two reasons why medical information should not be stored in the Person table.
Outline two issues caused by storing redundant data.
Outline two situations where data stored by the Driving Licensing Agency may need to be open to interrogation by other parties.
Markscheme
Award [2 max].
Accept either diagram. License, Individual, and Vehicle correctly positioned
Award [1] for 1 to 1.
Award [1] for 1 to m.
Award [3 max].
The order that a date is written varies by country, e.g. dd/mm/yyyy in UK, mm/dd/yyyy in US;
Some dates, such as 01 June 2018 (01/06/2018) could be confused with 06 January 2018 (06/01/2018);
leading to the incorrect information being stored and consequent decisions, e.g. license expired at the wrong time due to incorrect age;
The Date/String datatype will make analysing slower;
integers can be analysed more quickly than Date/String datatypes;
because functions will be used to split/extract the data;
The Date datatype will make validating data more difficult.
because it is easier to validate separate integer fields;
rather than one entire data that will need to be split by functions;
Award [6 max].
Normalisation
Medical information is only stored if the person has a medical condition / some people will have no medical information stored / some people may have more than one medical conditions;
this requires an additional table / normalisation requires a separate table / one to many relationship;
leaving in the person table will increase storage capacity / empty fields take up space;
Privacy
All employees will need access to the person table and that would include sensitive medical information;
Protects people’s privacy / data needs to comply with the Data Protection Act / private medical data seen by non-authorised personnel may cause harm to the licence applicant / potentially result in legal action;
having confidential data in a separate table allows that table to be only available to people with a certain permission level;
Updates
Person table is also likely to be less permanent and need updating more often;
medical information rarely gets updated so is more permanent;
the person table may be useful in other applications, as it offers a way of identifying citizens/the medical data is not likely to be used by other applications;
Mark as [3] + [3]
Award [4 max].
May lead to update/deletion anomalies;
Address changes may result in duplicate addresses;
so letters / fines may go to the wrong address;
Storing data multiple times wastes storage space;
and may slow down data retrieval / data entry;
Award [4 max].
Accept any suitable example
Police access
It is a legal requirement for the VDLA to give access to the police;
e.g. a speeding vehicle / an accident / linked to a police investigation;
so the police will need to look up the details of the person that owns the car;
facial scanning software may be cross-referenced with the car owner’s driving license photo;
so that police have a way to check the identity of the person driving the car;
Insurance company access
A person taking out car insurance signs a consent form to give VDLA access to their records;
this would allow the insurance company to build up a profile e.g., check for driving offences, see how long that person had owned a car;
thus, a quote could be created quickly / minimal effort;
provides proof of driver eligibility (i.e., not serving a driving ban);
Medical access
Doctors may require access to an accident victim’s records;
To check blood type etc.;
Mark as [2] + [2]
Examiners report
Most of the students clearly identified the main entities, their correct position and cardinality in the ERD diagram. Few failed to establish and correctly label the relationships.
This was not well answered with many students giving vague generic answers when explaining the separation of the Date of Birth into three fields. Very few students included reasons related to data type making easier analysis / validation / correct formatting.
This question was not answered well with most candidates struggling to explain reasons for having a separate table for medical information. However, the majority of students were able to explain privacy issues related to the question, but not able to explain other reasons such as normalization, updates or user access rights.
Candidates tended to comment on the waste of storage space and to mention update/deletion anomalies, but few were able to elaborate on these aspects or outline others.
This question explicitly asked for two different situations in which the database had to be open to interrogation by other parties. Successful candidates were able to combine knowledge and real-world context and provide situations with concrete examples of third parties and the reasons for interrogating the DB.
