Chapter 02 of 41

File System vs DBMS

Before database systems became common, organisations stored their data in ordinary files: text files, CSV files or binary record files, each one read and written by a program written specially for it. This is called the file processing system or simply the file system approach.

This approach still exists (think of the Excel sheets that many offices run on), and it works fine for small jobs. But as data grows and more people use it, serious problems appear. In this chapter we will see those problems one by one with a single running example, and then see how a DBMS solves each of them.


What Is a File Processing System?

In a file processing system, data is stored in separate files, and each application program defines and manages its own files.

The operating system's file system only knows about files and bytes. It does not know that a line in a file is a "student" or that a column is "marks". All that meaning lives inside the application programs.

  Accounts program          Exam program           Library program
         |                       |                       |
         v                       v                       v
  accounts_students.csv    exam_students.csv     library_members.csv
  fees.csv                 marks.csv             issued_books.csv

Each department wrote its own program and created its own files. Nothing connects them.


A Running Example: A College Using Files

Imagine a college where the Accounts office and the Exam office keep student data separately.

accounts_students.csv (Accounts office):

roll_no,name,phone,fee_paid
101,Riya Sen,98450-11111,YES
102,Kabir Rao,98450-22222,NO

exam_students.csv (Exam office):

roll_no,name,phone,marks
101,Riya Sen,98450-11111,88
102,Kabir Rao,98450-22222,74

Already you can see that the name and phone number of every student are stored twice. Let us see what goes wrong.


Problems with the File System Approach

1. Data Redundancy

Redundancy means the same data is stored in more than one place. Here, each student's name and phone are in both files (and probably in the library's file too).

Why is this bad?

  • It wastes storage.

  • Every change must be repeated in every copy.

  • It leads directly to the next problem.

2. Data Inconsistency

Suppose Riya changes her phone number and informs only the Accounts office:

accounts_students.csv : 101,Riya Sen,99000-55555,YES   (updated)
exam_students.csv     : 101,Riya Sen,98450-11111,88    (old)

Now the college has two different phone numbers for the same student. Which one is correct? When the exam office sends her result by SMS, it goes to the old number. This situation, where copies of the same data disagree, is called inconsistency.

Redundancy causes inconsistency. If data is stored only once, it cannot disagree with itself.

3. Difficulty in Accessing Data

The principal asks: "Give me the list of students who have not paid fees but scored above 70."

No existing program answers this. The fee data is in one file and the marks are in another. A programmer has to write a new program that reads both files, matches roll numbers and filters them. Next week the question changes slightly, and another program is needed.

With a DBMS, if the college keeps this data together in one table students(roll_no, name, phone, fee_paid, marks), the question becomes a single query, written in a minute:

SELECT name
FROM students
WHERE fee_paid = 'NO' AND marks > 70;

4. Data Isolation

Data is scattered across many files, possibly in different formats (one office uses CSV, another uses fixed-width records, another uses Excel). Writing programs that combine them is hard. The data is isolated in separate silos.

5. Integrity Problems

Integrity constraints are rules that data must always satisfy, for example:

  • Marks must be between 0 and 100.

  • Roll numbers must be unique.

  • Every issued book must belong to an existing student.

In a file system, these rules are written as if conditions inside each program. If the exam program checks marks but a quickly written "bulk upload" script does not, a value like 105 silently enters the file. Adding a new rule means finding and changing every program.

6. Atomicity Problems

Some operations must happen completely or not at all. This is called atomicity.

Consider a fee refund that moves Rs 1,000 from the college account to a student's wallet:

Step 1: college_balance = college_balance - 1000   (written to file)
         ---- power cut / program crash here ----
Step 2: student_wallet  = student_wallet  + 1000   (never happens)

After restart, Rs 1,000 has simply disappeared. Plain files have no built-in way to "undo" step 1. The programmer would need to build their own logging and recovery logic, which is very hard to get right.

7. Concurrent Access Anomalies

When several users update the same file at the same time, their changes can interfere. Suppose the library has 1 copy of a book left, recorded in a file, and two librarians issue it at the same moment:

Time

Librarian A

Librarian B

copies in file

t1

reads copies = 1

1

t2

reads copies = 1

1

t3

1 > 0, so issue book; writes copies = 0

0

t4

1 > 0, so issue book; writes copies = 0

0

The same single copy has been issued to two students, and the file still says 0, so nobody notices. This is called a lost update: B's decision was based on a value that A had already changed. A file system offers basic file locking at most, not a full mechanism for safely running many such read-then-write operations in parallel.

8. Security Problems

Operating-system permissions work at the level of whole files: you can read the file or you cannot. But real needs are finer:

  • A class teacher should see marks of only their own section.

  • A clerk should see names and fee status but not phone numbers.

Such row-level and column-level rules are very hard to enforce with files alone.

9. Program-Data Dependence

Because each program knows the exact layout of its file, changing the file structure breaks programs. If the Exam office adds an email column in the middle of the CSV, every program that assumed "marks is the 4th field" must be changed and re-tested. This tight coupling is called program-data dependence.


How a DBMS Solves These Problems

In a DBMS, the college keeps one integrated database. Students are stored once, and all departments use the same data through the DBMS. The rules are written once, in the schema. Here is a simplified students table (only the columns needed for this demo):

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name    VARCHAR(50) NOT NULL,
  phone   VARCHAR(15) NOT NULL,
  marks   INT CHECK (marks BETWEEN 0 AND 100)
);

INSERT INTO students VALUES (101, 'Riya', '98450-11111', 88);

Now let us try to break the rules (MySQL 8):

INSERT INTO students VALUES (102, 'Kabir', '98450-22222', 105);
ERROR 3819 (HY000): Check constraint 'students_chk_1' is violated.
INSERT INTO students VALUES (101, 'Dev', '98450-33333', 70);
ERROR 1062 (23000): Duplicate entry '101' for key 'students.PRIMARY'

Both bad rows are rejected, no matter which program or person sends them. The rule lives with the data, not inside each program. (Note: MySQL enforces CHECK constraints only from version 8.0.16 onwards; older versions parsed them but silently ignored them.)

For atomicity, the DBMS gives us transactions. Take a small accounts table where Asha (account 1) has Rs 5,000 and Bharat (account 2) has Rs 2,000:

CREATE TABLE accounts (
  acc_no  INT PRIMARY KEY,
  holder  VARCHAR(20),
  balance DECIMAL(10,2)
);
INSERT INTO accounts VALUES (1, 'Asha', 5000), (2, 'Bharat', 2000);

Now watch a transfer being started and then cancelled:

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 1;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
|      1 | Asha   | 4000.00 |
|      2 | Bharat | 2000.00 |
+--------+--------+---------+
ROLLBACK;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
|      1 | Asha   | 5000.00 |
|      2 | Bharat | 2000.00 |
+--------+--------+---------+

The half-done change was undone completely. If the server had crashed instead of us typing ROLLBACK, the DBMS would have undone it automatically during recovery. (Transactions are covered in detail in Transactions & ACID Properties.)

Here is how each problem maps to a DBMS feature:

File system problem

How a DBMS handles it

Redundancy

Integrated database; good design (normalization) stores each fact once

Inconsistency

Less redundancy, plus constraints and transactions keep data in a valid state

Difficult access

A declarative query language (SQL) answers new questions without new programs

Data isolation

All data in one system with a uniform format, linked by keys

Integrity problems

Constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK) defined once in the schema

Atomicity problems

Transactions with COMMIT/ROLLBACK and log-based recovery

Concurrent access anomalies

Concurrency control (locking, multiversioning) gives each transaction a safe view

Security

Users, roles, GRANT/REVOKE on tables and columns, views that hide rows/columns

Program-data dependence

Data independence through the three-schema architecture


File System vs DBMS: Comparison Table

Basis

File System

DBMS

Structure

Files whose meaning is known only to programs

Data described by a schema stored in a catalog

Redundancy

High; each application keeps its own copy

Controlled; data shared by all applications

Consistency

Hard to maintain

Maintained through constraints and transactions

Querying

Write a program for each new need

Ad hoc queries in SQL

Concurrency

Very limited (basic file locks)

Fine-grained (row-level) concurrency control

Crash recovery

Not provided; manual backups

Automatic, using logs

Security

File-level OS permissions

User, table, column and view-level privileges

Data independence

None; programs depend on file layout

Logical and physical data independence

Cost and setup

Cheap and simple

More software, memory and expertise needed

Best for

Small, simple, single-user data

Large, shared, multi-user, critical data


Is the File System Useless Then?

No. A DBMS itself stores its data in files on top of the operating system's file system. And for some jobs plain files are the better choice:

  • Small configuration files (for example .env or config.json).

  • Log files that are only appended and read in order.

  • Large media (videos, images), which are usually kept in file or object storage, with only their path or URL stored in the database.

  • One-time data exports and simple single-user scripts.

The real question is not "files or DBMS?" but "does this data need sharing, integrity, concurrency and recovery?" If yes, use a DBMS.


Common Mistakes Students Make

  • Saying a DBMS has "no redundancy". A DBMS controls redundancy; some deliberate redundancy (indexes, replicas, denormalised reporting tables) is common.

  • Mixing up redundancy and inconsistency. Redundancy is having multiple copies; inconsistency is when those copies disagree. Redundancy makes inconsistency possible.

  • Forgetting that a DBMS also uses files. The difference is who manages the files and their meaning: individual programs, or one central system.


Interview Questions

1. What are the main disadvantages of a file processing system?

Data redundancy and inconsistency, difficulty accessing data, data isolation, integrity problems, atomicity problems, concurrent-access anomalies, security problems and program-data dependence.

2. What is the difference between data redundancy and data inconsistency?

Redundancy means the same data is stored in several places. Inconsistency means those copies hold different values. Redundancy causes inconsistency when an update reaches only some copies.

3. Give an example of an atomicity problem.

A fund transfer that debits one account and then crashes before crediting the other. Without transactions, the money is lost. A DBMS rolls back the incomplete work during recovery.

4. What is a lost update?

Two users read the same value, both compute a new value from it and both write back; the second write overwrites the first, so one update is lost. DBMS concurrency control prevents this.

5. When would you prefer a file over a database?

For small, simple, single-user data such as configuration, sequential logs or large media files (with the database storing only their paths).


Summary

  • In a file processing system each program manages its own files, and the meaning of the data lives inside the programs.

  • This leads to redundancy, inconsistency, hard data access, isolation, integrity, atomicity, concurrency and security problems, plus program-data dependence.

  • A DBMS keeps one integrated database with a schema, enforces constraints centrally, offers SQL for ad hoc queries, and provides transactions, concurrency control, recovery and fine-grained security.

  • Files are still fine for small, simple, single-user data.

In the next chapter, DBMS Architecture, we will look inside a DBMS to see which components make all of this possible.