Question 1
19 marks
Driving Licensing Agency database schema, atomic date fields, and data privacy
- Databases
(a)
2 marks
- Identify the three core entities and their cardinality rules:
- 1) An Individual holds at most one Driving License (1-to-1 relationship).
- 2) An Individual can own multiple Vehicles, while each Vehicle belongs to a single Individual (1-to-many relationship).
- Construct the Entity-Relationship Diagram (ERD):
- LICENSE (1) ---- (holds / belongs to) ---- (1) INDIVIDUAL (1) ----< (owns) >---- (M) VEHICLE
Mentor tip
Clearly indicate the 1:1 link between License and Individual and the 1:M link between Individual and Vehicle.
(b)
3 marks
- Explain why decomposing Date of Birth into separate Day, Month, and Year fields is advantageous:
- 1) Eliminates regional date format ambiguity (such as DD/MM/YYYY vs MM/DD/YYYY where 01/06/2018 could be interpreted as 1 June or 6 January), preventing erroneous age or expiration calculations.
- 2) Simplifies input validation and range checking on individual integer components (e.g. verifying Month is between 1 and 12) without invoking string parsing functions.
- 3) Accelerates queries that filter or group records by birth year or birth month using direct integer comparisons rather than date extraction functions.
Mentor tip
Link the field separation to avoiding international format confusion and speeding up validation or querying.
(c)
6 marks
- Reason 1 — Access Control and Data Privacy (3 points):
- 1) General agency staff routinely query the Person table for standard administrative tasks.
- 2) Medical records contain highly confidential health information protected by data privacy laws.
- 3) Isolating medical attributes in the PersonMedical table allows the DBMS to enforce stricter role-based access permissions so only authorized medical evaluators can view health conditions.
- Reason 2 — Normalization and Storage Efficiency (3 points):
- 1) Most drivers have no declared medical conditions, while some individuals may report multiple conditions.
- 2) Storing medical fields inside the Person table would generate numerous null/empty fields or repeating groups.
- 3) Separating PersonMedical reduces wasted storage capacity and prevents update anomalies across unrelated personal identity attributes.
Mentor tip
Provide two distinct, fully developed explanations (such as security/privacy permissions and normalization/storage space) to secure all 6 marks.
(d)
4 marks
- Issue 1 — Update and Modification Anomalies:
- 1) Duplicating attributes across multiple records means an address or surname update might only be applied to some rows, leaving inconsistent data and causing correspondence or fines to be sent to outdated addresses.
- Issue 2 — Unnecessary Storage and Performance Overhead:
- 2) Storing identical values repeatedly consumes excessive disk space and increases I/O time during table scans and backups.
Mentor tip
Pair each identified issue (inconsistency/anomalies and wasted storage) with its practical consequence.
(e)
4 marks
- Situation 1 — Law Enforcement Investigations:
- 1) Police officers investigating traffic violations, collisions, or stolen vehicles query the agency database by registration plate or license photo to verify the registered owner's identity and legal driving status.
- Situation 2 — Motor Insurance Verification:
- 2) With the applicant's consent, insurance providers interrogate driving records to verify license validity, check penalty points or endorsements, and calculate accurate risk premiums.
Mentor tip
Name a specific external party (e.g. police, insurance companies, emergency medical staff) and explain why they need read access.
