A-Level Computer Science / Unit 8: Relational Databases and SQL

8.1.1 From File-Based Storage to Databases

🔒 Lesson slides are available to signed-in users. Sign in

8.1.1 From File-Based Storage to Databases

Small applications can store data successfully in ordinary text files or spreadsheets. Problems appear when the system grows, several teams need the same information, and different programs depend on different copies of the files. This lesson examines those weaknesses and introduces the ways a relational database can address them.

By the end of this section, you should be able to:

  • Describe how a file-based system stores and retrieves data.
  • Explain how duplicated, missing or conflicting values can reduce data integrity.
  • Explain how separate departmental files can create redundancy, inconsistency, isolation and privacy problems.
  • Explain data dependency and why changes to a file structure can force programs to be rewritten.
  • Compare these limitations with the controls provided by a relational database.

When a Simple Storage System Grows

A file-based system stores data in separate files that are accessed directly by application programs. Each program must know the format of the file it uses, including the order, data type and meaning of each item.

File-based approach: data is kept in separate files, and programs are written to read and update those files directly.

Consider a community makerspace that lends equipment such as cameras, soldering stations and 3D scanners. At first, one member of staff creates a file called loans.csv:

Member ID Member name Equipment code Due date Deposit status
M184 Lina Park CAM-14 18/10/2027 Paid
M271 Amir Saleh SCAN-03 20/10/2027 Waived

This is manageable while one program and one team use the file. Later, the reception, finance, workshop and outreach teams each create their own files. Information that once had one location now exists in several formats and several copies.

Important: Using files is not automatically wrong. The limitation is that separate files become difficult to coordinate when a system contains related data, many users and changing requirements.

Problem 1: The Stored Data Becomes Unreliable

File-based applications often rely on each program to check the data it receives. If the checks are incomplete, the same real-world item may be missing, duplicated or represented differently.

Entry in a member file What has gone wrong? Possible consequence
M184 · Lina Park · [email protected] A second file records the same member as “Lina Pak”. A search may treat one person as two different members.
M271 · Amir Saleh · training expiry blank A required value is missing. Staff cannot confirm whether the member may use restricted equipment.
M322 · Hana Ito · two different email addresses Two copies disagree. An overdue notice may be sent to the wrong address.
Data integrity: the data remains accurate, complete, consistent and suitable for its intended use.

Validation can reject values that break a rule, such as a blank member ID or an impossible date. It cannot prove that a plausible value is true. For example, both “Lina Park” and “Lina Pak” could pass a format check.

Common misconception: Validation supports integrity, but it does not guarantee accuracy. A value can satisfy every validation rule and still describe the wrong person or item.

Problem 2: Separate Files Create New Difficulties

Giving each team its own file may appear to protect private information, but it creates a fragmented system. The same facts are copied into several places, and related data become harder to combine.

Team file Example data stored Resulting risk
Reception Member names, contact details, current loans Member and loan data are duplicated elsewhere.
Finance Member names, deposit status, unpaid charges A changed name or contact detail may not be updated here.
Workshop Member names, training records, equipment condition Staff may use an old training status.
Outreach Member names, email addresses, interests Users may receive private data that is not needed for outreach.
Data redundancy: the same fact is stored more than once.
Data inconsistency: two or more stored copies of the same fact contain different values.

How redundancy becomes inconsistency

  1. A member changes their email address.
  2. Reception updates its file.
  3. Finance and outreach keep the previous address.
  4. Different programs now return different answers to the same question.

Data isolation

Related data may be separated by file format, location or department. Producing a report such as “members with overdue equipment whose safety training expires this month” may require several files to be found, interpreted and combined manually.

Data isolation: related data are kept in separate places or incompatible formats, making them difficult to retrieve and combine.

Privacy and access

A large file may reveal more information than a user needs. For example, workshop staff require training records but do not need to see deposit payment details. Copying data into smaller files can reduce this exposure, but it increases redundancy and makes updates harder to coordinate.

Exam tip: Explain the trade-off. One large file can expose too much data; several smaller files can reduce access but increase duplication, inconsistency and isolation.

Problem 3: Programs Become Tied to File Layouts

A file-processing program may assume that each line follows an exact layout:

Original layout:
MemberID | Name | Email | EquipmentCode | DueDate

The makerspace then adds a safety-training expiry date before the equipment code:

New layout:
MemberID | Name | Email | TrainingExpiry | EquipmentCode | DueDate

An older program still reads the fourth item as the equipment code. It may now interpret a date as an equipment identifier, fail during execution or produce incorrect output.

Data dependency: application programs rely on the physical organisation and format of the files that store their data.
Required change Effect in a file-based system
Add a new field Existing file layouts and every program that reads them may need modification.
Change the date format Programs may reject or misinterpret existing records.
Create a new cross-department report New code must understand and combine several separate file structures.
Exam tip: A complete explanation of data dependency should connect both parts: the program relies on the file structure, so changing the file can require changes to the program.

How a Relational Database Addresses These Limitations

A relational database does more than place the same files in one folder. It stores related data in a controlled structure and uses a Database Management System (DBMS) to manage access, rules and retrieval. The detailed relational model is developed in the next lessons.

File-based limitation Relational database response Likely benefit
Repeated copies of member details Store each fact in an appropriate related table instead of copying it into every departmental file. Less redundancy and fewer conflicting updates.
Missing or invalid references Apply constraints and relationships when data are inserted or changed. Stronger data integrity.
Users see entire files Assign access rights to users or groups and provide controlled views of the data. Improved privacy and security.
Related data are isolated Use queries to combine related data from the database. Faster retrieval and more flexible reports.
Programs depend on field positions Programs request named data through the DBMS rather than interpreting raw file positions directly. Changes can be made with less disruption to applications.
Do not overclaim: A database does not automatically make every value correct, remove every duplicate or prevent every unauthorised action. It provides mechanisms that can be designed and configured to reduce these risks.

Interactive: File-Based System Risk Explorer

Select a risk to compare what may happen in the makerspace’s separate files with the control that a relational database can provide.

File-based approach

Conflicting member records

M184 · Lina Park · [email protected]

M184 · Lina Pak · [email protected]

M271 · Amir Saleh · training date missing

Different files can describe the same member differently or omit required information.

Database approach

One controlled record

A database can apply required fields, validation rules and controlled relationships to reduce conflicting entries.

Risk level: High
Integrity is weakened when records are missing, duplicated or inconsistent. Database controls reduce the risk, but they still depend on a sound design and accurate input.

Building a Strong Explanation

Questions about file-based systems usually require a chain of reasoning rather than a list of vocabulary. Use the following structure:

Step What to include Example sentence starter
1. Name the limitation Use the precise technical term. “The separate files create data redundancy because…”
2. Explain how it arises Connect the problem to the storage method. “The member’s email address is copied into several departmental files…”
3. State the consequence Describe the impact on users or data. “If only one copy is updated, the files become inconsistent…”
4. Link to the database response Identify the relevant control or feature. “A relational database can store the fact once and allow authorised users to retrieve it…”
Exam tip: “A database is better” is not enough. State the limitation, explain its cause, describe a consequence and then link it to a specific database feature.

Common Mistakes and Misconceptions

  • Redundancy is not the same as useless data. It means storing the same fact more than once.
  • Redundancy and inconsistency are connected but different. Redundancy creates multiple copies; inconsistency occurs when those copies disagree.
  • Privacy is not only about passwords. It includes limiting which users can view or change particular data.
  • Validation does not prove accuracy. A realistic but incorrect value may still pass every check.
  • Data dependency is about programs and file structure. It is not another name for duplicated data.
  • A database is not automatically perfect. Its benefits depend on suitable design, constraints, permissions and maintenance.

Practice

Part A: Check your understanding

  1. Define data redundancy.
  2. Distinguish between data redundancy and data inconsistency.
  3. Explain why validation cannot guarantee that stored data are accurate.
  4. Describe what is meant by data isolation.
  5. Explain data dependency using both a file and an application program.

Part B: Apply the ideas

A wildlife rescue centre keeps separate files for animal admissions, volunteer rotas, medicine stocks and payments. Animal identifiers, contact details and treatment dates appear in more than one file.

  1. Explain how the system could produce inconsistent data.
  2. Explain one privacy problem that could arise.
  3. Describe one report that would be difficult to produce because of data isolation.
  4. The centre adds a new “release location” field to its admission file. Explain why existing programs may need modification.
  5. Explain two ways a relational database could improve the system.

Part C: Extended response

A small company has grown from one office to six branches. Each branch keeps its own customer and order files. Evaluate the decision to replace the separate files with a relational database.

Your answer should consider integrity, redundancy, inconsistency, privacy, retrieval and the effect on existing programs.

Review

Issue What it means Database response
Data redundancy The same fact is stored in several places. Store related data once in an appropriate structure.
Data inconsistency Different copies of the same fact disagree. Update centrally managed data rather than several independent copies.
Weak integrity Data may be incomplete, inaccurate or invalid. Use validation, constraints and controlled relationships.
Data isolation Related data are difficult to combine. Use queries over related tables.
Privacy problem Users can see or change data outside their role. Apply access rights and controlled views.
Data dependency Programs rely on the physical file layout. Let programs request named data through the DBMS.
Final exam tip: Use paired explanations: identify the file-based limitation and then explain the specific relational-database feature that addresses it.