Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 79 ยท Introduction to Databases

File Systems vs a DBMS

See why storing data in plain files causes redundancy, inconsistency, lost updates and weak security, and how a DBMS solves each problem, with live SQL examples.

Before database systems were common, programs kept their data in ordinary files: one file per application, in whatever layout the programmer chose. This still happens today whenever someone stores records in a CSV file, a spreadsheet or a JSON file and writes a script to read it. For small, single-user jobs that is fine. Once data grows, is shared, or must stay correct, the weaknesses of this approach appear one after another.

This lesson walks through the classic problems of file-based storage and shows how a DBMS answers each one. Understanding why a DBMS exists makes the rest of the tutorial easier to follow, because every later chapter is a solution to one of these problems.

Problems of file-based storage

What goes wrong with flat files

Redundancy The same fact is copied into many files.
Inconsistency The copies drift apart over time.
Hard access Every new question needs a new program.
Weak security Access is all or nothing per file.
1 / 2

Redundancy and inconsistency

Imagine the bookstore keeps one file of orders where every line repeats the customer's name and city. Ada Lovelace ordered five times, so her city appears in five lines. This is data redundancy: the same fact stored more than once. Redundancy wastes space, but the real damage is inconsistency. If Ada moves and only three lines are updated, the file now says she lives in two cities.

Difficulty in accessing data

With files, each question needs code that knows the exact layout. "List customers in Paris who bought more than two books" means writing a new program, parsing the lines and doing the grouping yourself. A DBMS offers a general query language, so most new questions are one new query, not a new program.

Data isolation and format lock-in

Data spread over many files in many formats is hard to combine. And because each program has the file layout built in, adding a column or changing a date format means editing, retesting and redeploying every program that reads the file. A DBMS keeps the structure in the database and hides the physical layout from programs; you will study this in Schemas, Instances and Data Independence.

Integrity problems

A rule such as "a quantity must be positive" or "every order refers to an existing customer" lives only in program code. Each program must remember to check it, and one forgetful program can write bad data that all the others then trust. A DBMS declares rules once, with the data, and applies them to every write.

Atomicity problems

A bank transfer subtracts from one account and adds to another. If the program crashes between the two steps, money has vanished. With files you would have to build your own "undo". A DBMS treats the whole transfer as one transaction that either completes entirely or has no effect. See What Is a Transaction?.

Concurrent access

Two clerks open the same stock file, both read "10 copies left", each sells one and writes back "9". One sale is lost. To prevent this with files you must invent locking, and getting it right is hard. A DBMS has a concurrency-control component built for exactly this (see Two-Phase Locking).

Security

Operating systems give permissions per file: you can read it or you cannot. A DBMS can allow one user to see names but not salaries, or to read a table but not change it (Authentication, Authorization and Access Control).

Seeing the difference: a flat table

Let us build the redundant "file" as a single table, the way a spreadsheet would hold it. There are no rules at all, so anything can be stored.

SQL A flat file as one table

Press Run (or Ctrl + Enter) to see the result here.

Ada now appears in two cities because of one typo, and the quantity column accepted the word three and a negative number. Every one of these rows was accepted without complaint, just as a text file would accept them. Adding up quantities would give nonsense.

The same data in a relational design

Now give the data structure. Customers live in one table, and orders refer to them by key. Constraints describe what a valid row is.

SQL Constraints reject bad data

Press Run (or Ctrl + Enter) to see the result here.

The city is stored once, so there is nothing to drift apart. Now try to break the rules on purpose. Each statement below is rejected and the run stops at the first one, so uncomment one at a time.

SQL Three rejected writes

Press Run (or Ctrl + Enter) to see the result here.

The first insert fails with a CHECK constraint error, so nothing is stored. Those messages are the DBMS doing the job that every file-reading program would otherwise have to do. Keys and Integrity Constraints covers the rules in depth.

SQLite is lenient with types. SQLite columns have a type affinity, not a strict type, so storing the text 'three' in an INTEGER column is allowed unless you add a CHECK or use a STRICT table. Most server DBMSs are stricter. The lesson here is that a DBMS lets you declare rules; which rules you declare is up to the designer.

When files are still fine

A DBMS is not always required. Configuration files, a small log, or a one-off export are happy as plain files. Reach for a DBMS when data is shared by several programs or users, must stay correct, will keep growing, or needs to answer flexible questions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Ada's city appears in five lines of a flat file, but only three were updated after she moved. Which problem is this?

    The same fact is stored several times and the copies disagree. Storing it once removes the risk.
  2. 2. Two clerks both read 10 in stock, each subtract one and write back 9. What is lost?

    Both writes started from the same old value, so one sale vanished. A DBMS uses concurrency control to prevent this lost update.
  3. 3. Where does a rule like quantity > 0 live in a DBMS-based design?

    Declarative constraints such as CHECK are stored with the table and apply no matter which program does the write.
  4. 4. Which situation best matches the atomicity problem of file storage?

    Atomicity means all steps of a unit of work happen or none do. A transaction gives the DBMS that guarantee.

Summary

  • File-based storage causes redundancy, inconsistency, hard access, format lock-in, weak integrity, no atomicity, unsafe concurrency and coarse security.
  • A DBMS stores each fact once, offers a general query language, enforces declared rules, and provides transactions, concurrency control, recovery and fine-grained permissions.
  • Constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL and CHECK reject bad data at write time.
  • Plain files remain fine for small, private, single-writer data.

Next: look inside the system to see which components provide these services, in DBMS Architecture and Components.