Before the Advent of Database

A college keeps records about its students. Three offices need those records.

The admissions office keeps a file of every student who joined. The library keeps its own file of borrowers. The examination office keeps a third file, for marks and results.

A student named Priya moves to a new address. She tells the admissions office, and a clerk updates the admissions file. Nobody tells the library. Nobody tells the examination office.

Her library fine notice goes to the old address. Her result card goes to the old address too. The college now holds three answers to one simple question, and two of them are wrong.

This is the problem databases were invented to solve. Before them, every application stored its own data in its own files. That arrangement is called a file processing system, and it is worth understanding, because every weakness in it became a feature of the database that replaced it.

What a file processing system looked like

Each application owned a set of files on disk. The files held plain text, comma separated values, or a binary layout invented by the programmer.

There was no shared layer between the program and the file. The program opened the file, read bytes, and worked out what those bytes meant.

That last point is the important one. The layout of the data lived inside the program code, not with the data. A file on its own did not describe itself.

The picture above shows the arrangement. Three applications, three sets of files, and the same student stored three times.

Records were usually read in one of two ways. Sequential access meant reading the file from the start until you found the record you wanted. Indexed access meant keeping a second file that mapped a key to a position, which was faster but had to be maintained by hand.

The problems this caused

The arrangement worked while systems were small. It broke down as the number of applications and the volume of data grew. Seven problems came up again and again.

Data redundancy and inconsistency

Redundancy means the same fact is stored in more than one place. Priya's address sits in three files, so the college pays for three copies.

Inconsistency is what redundancy turns into over time. An update reaches one copy and not the others, and the copies stop agreeing.

Nothing in the system detects this. There is no rule anywhere saying the three files must match, so the wrong address can survive for years.

Data isolation

The files were scattered across directories, machines, and formats. One office used a comma separated file, another used a fixed width binary record.

So a question that crossed two offices was hard to answer. Take this one: "Which students with unpaid library fines are also failing a subject?" Answering it needed a programmer to open two files, parse two formats, and match the records by hand.

Every new question needed a new program

There was no query language. A file processing system had no way to say "give me the rows where the fine is above 500".

Somebody had to write a program for each new question. A request that would take thirty seconds with SQL took days of programming, so most questions never got asked.

Integrity rules lived in application code

An integrity constraint is a rule the data must always obey. A marks column must hold a value between 0 and 100. Every borrower must be a real student.

In a file processing system these rules existed only as if statements inside programs. A rule was enforced only by the programs that remembered to check it.

Add a second program that writes the same file, and the rule is now in two places. Miss one, and bad data enters the file with nothing to stop it.

No safe access from two programs at once

Two clerks issue the last available copy of a book at the same moment. Both programs read the count as 1. Both subtract 1. Both write 0.

Two books have been issued and only one was subtracted. This is a lost update, and it happens whenever two programs read and write the same record without coordination.

File systems offered file level locking at best. Locking a whole file to change one record made the system unusable for many users.

No recovery from a failure in the middle

Moving a fee payment between two accounts means two writes: subtract from one record, add to another. Suppose the machine loses power after the first write.

The money has now left one account and arrived nowhere. The file is left in a state the business rules say is impossible.

A file system cannot undo a half finished change. Repair meant restoring an old backup and losing every change made since.

Security was all or nothing

File permissions apply to a whole file. A clerk who needs to read a student's address gets the whole student file, including marks and fee records.

There was no way to grant access to some columns and not others, or to some rows and not others. Fine grained control had to be written into each program, which brings back the problem of rules living in code.

What the database changed

A database management system, written DBMS, is software that sits between applications and the stored data. Every application goes through it. No application touches the files directly.