The Veterinary Clinic's Six Spreadsheets
A veterinary clinic in Gozo keeps client and animal records in six spreadsheets: Owners, Animals, Visits, Vaccinations, Invoices and Staff. The Owners sheet stores the owner's name, address and phone number, and the Animals sheet repeats the owner's name and phone number for each animal. When an owner changes their phone number, staff edit it in several places, and some entries are missed. Two animals with the same name and owner have been entered twice. The clinic wants to introduce a symptom-checking tool that uses an external AI service, and must decide what data to send. It also needs a backup policy: currently a staff member copies the spreadsheets to a USB drive every Friday, and the drive is kept in the reception drawer. The clinic has no written data-protection policy and has never tested restoring a backup.
1. Identify the data redundancy and the update, insert and delete anomalies in the clinic's spreadsheets. Propose a normalised relational schema up to third normal form, with primary and foreign keys.
Read the worked answer
The redundancy is clear: the owner's name and phone number are stored once in Owners and repeated for every animal in Animals, and probably repeated again in Visits and Invoices. The same animal may appear twice because there is no reliable identifier.
- Update anomaly: a phone change must be made in every row where the owner appears. Missed rows produce conflicting values, so staff cannot know which number is correct.
- Insert anomaly: if the clinic wants to register a new owner who has no animal yet, the Animals-centred structure may force a dummy animal or leave the owner unrecorded.
- Delete anomaly: deleting the last animal for an owner may delete the only copy of the owner's contact details, or deleting an owner may lose visit history that should be retained.
A suitable 3NF schema is:
- Owners(owner_id PK, name, address, phone)
- Animals(animal_id PK, owner_id FK, name, species, date_of_birth)
- Staff(staff_id PK, name, role)
- Visits(visit_id PK, animal_id FK, staff_id FK, visit_date, notes)
- Vaccinations(vaccination_id PK, animal_id FK, staff_id FK, vaccine, date_given)
- Invoices(invoice_id PK, visit_id FK, amount, invoice_date)
Each non-key attribute depends on the key, the whole key and nothing but the key. A unique constraint on owner_id, name, species and date_of_birth would help prevent the duplicate-animal problem, and the clinic should reconcile existing duplicates before migrating.
2. The clinic wants to use an external AI symptom-checking service. Discuss the main data-protection and ethical issues and recommend what data should and should not be sent. Refer to principles such as data minimisation and consent.
Read the worked answer
Sending clinic data to an external AI service is a form of processing by a third party. The owner's name, address, phone number and invoice details are personal data, so the clinic needs a lawful basis, a written contract with the processor, transparency and a way for clients to object. Animal health data is not special-category personal data in the same way as human health data, but it can still identify the owner indirectly. The clinic also owes a duty of confidentiality and must consider clinical risk if the AI output is wrong.
Data minimisation means sending only what the tool needs. Appropriate fields are species, age, sex, weight and the symptom description. The clinic should strip owner name, address, phone, invoice numbers and staff names, and replace the animal's name with a pseudonymous ID if the name could identify the owner. It should not send full historical records unless the tool genuinely needs them. Consent should be obtained for external AI processing, with an opt-out and a human vet reviewing every output.
The ethical issues include accuracy and liability, bias in the model, vendor retention or reuse of data for training, and cross-border transfer. The clinic should check the vendor's retention policy, refuse training-on-clinic-data without consent, and document the decision. The AI should be an assistant, not a diagnosis.
3. Evaluate the current backup policy and recommend an improved policy. Include the 3-2-1 principle, encryption, access control and restore testing, and explain why each matters for this clinic.
Read the worked answer
The current policy has a single copy on a USB drive, written once a week and stored in an unlocked reception drawer. It fails on several counts. A single copy is vulnerable to theft, fire, ransomware and simple loss. The reception drawer is accessible to visitors and staff, and the drive is probably unencrypted. A weekly copy means up to seven days of visits and vaccinations can be lost. Nothing has ever been restored, so the clinic does not know whether the files are readable or complete.
A better policy follows 3-2-1: at least three copies, on two different media, with one offsite or in cloud storage. Backups should run daily and automatically, with versioning so that a corrupted or encrypted file does not overwrite good copies. Data should be encrypted at rest and in transit, and access restricted to named staff with individual accounts. At least one copy should be offline or immutable to resist ransomware.
Restore testing is essential: a quarterly test should restore a sample of records to a separate machine and record the result. For a clinic, lost vaccination history can affect animal care, and lost owner data raises data-protection obligations. The policy should be written down, with responsibilities and retention periods, rather than relying on one staff member's Friday routine.