Studyledge

Databases and SQL: Question 1

Syllabus 8.1

Multiple choice AS 1 mark

A small dental clinic stores every appointment in a single flat-file table. Each row holds the appointment date, the dentist's name, the dentist's phone extension, the patient's name and the patient's home address. Because the clinic has no separate table for patients, a patient's name and address are repeated on every row for every appointment they have ever had.

Which of the following is a genuine limitation of this single flat-file approach that splitting the data into separate, related tables (a relational database) is designed to solve?

Choose an answer to check it, then compare with the worked solution below.
Show worked solution Hide worked solution

Worked solution

Why B is correct

Storing every appointment as one row, with the patient’s name and address repeated on each row, is a flat-file design. The patient’s address is not stored once in one place. It is duplicated across every appointment row for that patient.

This duplication is the real problem: if the patient moves house, the address has to be updated in every row that mentions them. If the clinic’s receptionist updates some rows but misses others (which is easy to do across many appointment records), the table ends up with inconsistent data. Different addresses for the same patient depending on which row is read. This kind of problem, caused directly by unnecessary data redundancy, is called an update anomaly.

A relational database solves this by storing each patient’s details once, in a separate Patient table, and linking to it from the appointments table using a key, so the address only ever needs to be updated in one place.

Why the other options are wrong

  • A is false. A flat file can store any field type, including text fields such as a name or address. It is not restricted to numeric data.
  • C is false. A flat file can still be sorted or searched by any field, including patient name; sorting/searching ability is not what a relational database specifically fixes.
  • D is false. There is no inherent fixed row limit on a flat file of this kind. The real limitation is data redundancy and the risk of inconsistency it causes, not a capacity limit.

Final answer

B. Repeating a patient’s details on every row risks the same patient ending up with inconsistent data (an update anomaly) if not every row is changed; this redundancy is exactly what a relational database, splitting data into separate related tables, is designed to remove.