File System versus DBMS

Concept

Before databases, applications stored records in ordinary files and each program contained its own code for reading and writing them. That approach is called the file based system, and understanding exactly how it fails is the fastest way to understand why every DBMS feature exists.

The seven problems, one by one

1. Data redundancy

Each department keeps its own file. The accounts office stores a student address, the hostel office stores the same address, the library stores it again.

accounts.dat   roll 21, Meera, 14 Anna Salai, Chennai
hostel.dat     roll 21, Meera, 14 Anna Salai, Chennai
library.dat    roll 21, Meera, 14 Anna Salai, Chennai

Three copies. Three chances to be wrong.

2. Data inconsistency

Meera moves house. The hostel file is updated. The other two are not. The database now asserts two different addresses for one person, and no part of the system objects.

3. Difficulty of access

A new question — which hostel residents have unreturned library books? — needs a new program, because no general query facility exists. Every new question costs development time.

4. Data isolation

The three files are in different formats, written by different teams, possibly on different machines. Combining them is a project rather than a query.

5. Integrity problems

The rule marks must be between 0 and 100 lives inside whichever program happens to enforce it. A second program written later does not know the rule exists, and writes 150.

6. Atomicity problems

A transfer that updates two files crashes between them. There is no mechanism that knows the two writes belonged together, so the data is left half changed.

7. Concurrent access and security

Two clerks read the same balance of 10,000, each subtracts 2,000, each writes back 8,000. Two withdrawals happened and only one was recorded. Separately, file permissions are all or nothing — a user who can read the file can read every salary in it.

The comparison table

Where a file system is still the right choice

Answering this well separates a memorised answer from an understood one.

• A single user, single program, small dataset — a configuration file needs no DBMS.
• Large binary objects such as video, where the useful operations are read and stream, not query.
• Append only logs read sequentially by one tool.
• Environments where the overhead of installing and administering a DBMS exceeds the benefit.

Example

THE LOST UPDATE, in a file system

  clerk A                    clerk B
  read balance -> 10000
                             read balance -> 10000
  10000 - 2000 = 8000
                             10000 - 2000 = 8000
  write 8000
                             write 8000

  Two withdrawals of 2,000. Final balance 8,000.
  6,000 was expected. 2,000 has been created from nothing.

THE SAME SEQUENCE, in a DBMS

  clerk A takes an exclusive lock on the row.
  clerk B waits.
  A writes 8,000 and commits, releasing the lock.
  B now reads 8,000, writes 6,000 and commits.
  Final balance 6,000. Correct.

Common mistakes

• Saying a DBMS removes redundancy completely. It controls redundancy. Foreign keys repeat a value deliberately, and denormalisation repeats more on purpose.
• Listing only redundancy and security. The full list is seven problems; examiners award marks per point.
• Claiming a DBMS is always better. It costs money, memory and expertise. Name a case where a file is correct and the answer improves.
• Confusing data isolation with transaction isolation. In this list, isolation means files trapped in incompatible formats. In transactions, isolation is the I of ACID. Same word, different idea.

Exam and interview questions

1. List and explain the seven drawbacks of the file based approach.
2. Draw the table comparing file systems and a DBMS on at least eight aspects.
3. Explain the lost update problem with a schedule, and state how a DBMS prevents it.
4. Give two situations where a file system is still preferable to a DBMS.
5. Distinguish data isolation from transaction isolation.

Practice

1. Take a college with separate exam, fee and hostel offices. List every fact stored in more than one office and describe the inconsistency that follows a change of address.
2. Write the lost update schedule for two clerks depositing rather than withdrawing, and show the incorrect result.
3. For each of the seven problems, name the single DBMS feature that solves it.

Conclusion

Every feature of a DBMS is an answer to a specific failure of the file based approach. Learn the seven problems and their solutions as pairs, and most of the rest of this subject stops feeling like a list of unrelated topics.