The query that survived a new disk

A year later the college has a database. In April the servers move to a new machine with faster storage, and the tables are reorganised on disk to suit it.

The office reports keep working. Not one of them is edited. Not one line of SQL changes.

That is not luck. It is the oldest idea in the subject, and it was designed in 1975 for exactly this moment.

Three levels

A database is described at three separate levels, and each one is allowed to change without disturbing the one above.

Physical. How the bytes sit on disk: which file, which block, which index. Only the database and its administrator ever look here.

Logical. What tables exist, what columns they have, what depends on what. This is what you draw on a whiteboard, and what SELECT talks to.

View. What one person or one application sees. The accounts office gets a view containing fees and no marks, and as far as they are concerned that view is the whole database.

Why the reports survived

The disk change happened entirely at the physical level. The logical level, where the reports live, never knew it happened. That is physical data independence, and every database has it.

The harder one is logical data independence: changing the tables themselves without breaking what reads them. When students is later split into students and student_contact, a view presents the old shape and the old reports keep running.