Core CS · DBMS

Three descriptions of the same data

The rows never move when you draw this diagram. What moves is who is allowed to know how they are stored, and the two mappings in the middle are the reason a storage change does not become a code change.

Change the storage, watch the query survive →

The internal level says how the bytes sit. The conceptual level says what tables exist. An external level says what one group of users may see. Two mappings sit between them, and their job is to stop a change at one level from reaching the level above.

01 The idea

Three descriptions, one set of bytes

Take the five marks from the first lesson and put them in a real table: RESULT(sid, name, marks) holding Asha 78, Ravi 65, Meera 35, Kabir 82 and Nikhil 90. Three different people now have to talk about that table, and none of them wants to hear what the others have to say.

The exam office wants a list of students who passed. It does not want to know that Meera exists. The database designer wants to declare that sid is the primary key and that marks must lie between 0 and 100. She does not want to know which disk the file is on. The DBA has measured the query, found it reads every block of the file, and wants to reorganise the file so the marks sit in a tree. He does not want to know what a pass mark is. Each of the three is describing the same five rows, and each one needs a description that leaves the other two out.

So the data gets described three times, at three levels, and the DBMS stores a translation between each pair of neighbouring levels. That translation is the whole invention. Because the description of the tables never mentions a disk block, the DBA can change the blocks and only the translation below has to be rewritten. Nobody above notices. That property is what the architecture exists to deliver, and it has a name from the first lesson: data independence.

Each level is described without mentioning the level below it. A stored mapping does the connecting, so a change at a lower level stops at that mapping instead of reaching the programs above.

SchemaThe description of the structure: names, types, keys, offsets, indexes. It is written once and changes rarely. The instance is the rows that currently satisfy it, and that changes every second.

MappingThe stored correspondence between two neighbouring levels. It is what lets the DBMS turn a request phrased at one level into a request phrased at the level below it. There are exactly two.

Data independenceBeing able to change a schema at one level without changing the schema above it or the programs written against that. One kind for each mapping, and they are not equally achievable.

02 Worked example

The same five marks, described three times

Fix the details so there is something concrete to argue about. On disk, result.dat is a heap file: rows appended in insertion order, no index, one 8 KB block holding all five records. Each record is 24 bytes, laid out as sid in bytes 0 to 1, name in bytes 2 to 21, and marks in bytes 22 to 23. Five records is 120 bytes, so today the whole table fits inside that one block. The layout is the same layout at five million rows, which is what makes the DBA’s cost measurement worth acting on. The exam office reads a view, pass_list, defined as the name and marks of every student at or above the pass mark of 40.

internal4E4123525Abyte 23 of each record, in insertion order · byte 22 is 00 for every mark

conceptual7865358290RESULT.marks · SMALLINT · five rows

external7865358290pass_list · four rows · 35 does not exist at this level

Three rows, one set of bytes. 4E is 78 the moment something says bytes 22 and 23 are a SMALLINT, and 78 becomes a passing mark the moment something says 40 is the line. Nothing was copied between the rows. What changed is which description you are reading through. Now put the two translations in where they belong.

External levelpass_list — name and marks of four students; the exam office never sees Meera

→

External / conceptual mappingthe view definition: select name and marks from result where marks is at least 40

→

Conceptual levelRESULT(sid CHAR(2), name CHAR(20), marks SMALLINT) — keys and constraints, declared once for everybody

→

Conceptual / internal mappingthe dictionary entry: marks is bytes 22 to 23 of a 24-byte record in result.dat, reached by scanning

→

Internal levelresult.dat — heap file, 8 KB blocks, insertion order, no index

Read the two mapping boxes, not the three level boxes. The levels are the part students memorise; the mappings are the part interviews ask about. A request travels left to right, getting more physical at every step, and the answer travels back right to left, getting more abstract. The highlighted box is the one nobody names: it is the only place in the whole system that knows bytes 22 to 23.

That is worth saying twice, because it is the mechanism and not a slogan. The number 22 appears exactly once. It is not in the view definition, which names columns. It is not in the conceptual schema, which names types. It is not in the exam office’s query, which names pass_list. So when the DBA changes the file, there is exactly one thing to rewrite, and the DBMS owns it. Compare that with the pass mark of 40, which is written in the view definition by a person, and you can already feel which of the two mappings is going to be the awkward one.

03 Mechanics

What lives where, and what each mapping absorbs

Three tables, in the order an interviewer walks through them. The first is what is written down at each level. The fourth column is the one that matters: an abstraction is defined by what it hides, not by what it holds.

Level	What is written down	Who writes it	What it hides, and from whom
Internal also called physical	`file organisation, record layout and field offsets, block size, indexes, compression, file location`	DBA, with the storage engine	That a row has an address at all. Nothing above the internal level knows a block exists.
Conceptual also called logical	`tables, columns, types, primary and foreign keys, constraints, relationships`	Database designer	How many bytes a row takes, whether an index exists, how fast anything is. The external level above it is told none of that.
External also called view	`one named view per user group: chosen columns, chosen rows, derived columns`	Application designer, per group	Rows, columns and whole tables that this group has no business seeing. No level sits above it, so what it hides, it hides from the user.

Two neighbouring pairs means two mappings. Both are stored in the data dictionary, which is why both survive a restart and neither lives in your source code. Both also run in two directions: a request travels down through them and the answer travels back up, so a write issued at one level has to be translatable down to the level below as well.

Mapping	What it translates	Stored as	Written by
External / conceptual	`pass_list.name → a query over RESULT`	the view definition	`a person, by hand`
Conceptual / internal	`RESULT.marks → file, block, offset, access path`	dictionary entries the storage engine maintains	`the DBMS itself`

One kind of independence per mapping. The change column is what you actually do; the last column is why one of these is routine and the other is a project.

Independence	Change you make	Must not change	How hard, and why
Physical	`heap file → B+ tree file organisation keyed on marks; move the file; turn on compression; change block size`	the conceptual schema, every external schema, every application	`routine` — one mapping is rewritten and the DBMS rewrites it. Nothing above ever named a block, so there is nothing above to update.
Logical	`ADD COLUMN attempt; add a table; widen a type; split RESULT in two; drop a column`	every external schema and every application above it	`partial at best` — the mapping above is written by hand and names tables and columns explicitly. Additive changes are absorbed. Splits must be rewritten by a person. Removals cannot be absorbed at all.

Logical data independence is the harder of the two for three reasons, and they stack. The mapping that has to absorb the change is a view definition a person wrote, not a dictionary entry the DBMS maintains. That definition names conceptual tables and columns out loud, whereas nothing above the internal level ever named a byte offset. And a conceptual change can destroy information: drop marks and no rewrite of pass_list can produce it, because the fact is no longer recorded anywhere. A physical change only ever relocates data, so a mechanical rewrite of the lower mapping always exists.

The additive rule. Adding a column or a table is absorbed cleanly, because a view that names its columns explicitly cannot be disturbed by a column it does not name. That is the everyday case, and it is why logical independence looks easy until somebody renames something.

The SELECT * trap. A star defeats the mapping from both sides. PostgreSQL and MySQL expand * into a fixed column list when the view is created, so a later ADD COLUMN never shows up in the view and the exam office quietly keeps getting the old shape. An application that runs SELECT * against the base table instead does get the extra column, and code that unpacks a row into a fixed set of variables, or writes it into a fixed set of report columns, breaks on the day the column is added. Name your columns and both problems disappear.

Three levels, not three schemas. There is one internal schema, one conceptual schema, and as many external schemas as there are groups of users. Saying “a database has three schemas” is the single most common slip on this topic, and the follow-up is always to describe the schema for a second department.

05 Cheat sheet

The eight answers to get right

Every line here has a short answer that survives a follow-up and a shorter one that does not. The third column is the answer that sounds fine in the room and then costs you the next two minutes.

Asked about	Say this	The answer that costs you
Internal schema	`file organisation, record layout and offsets, block size, indexes, compression`	`“the physical level”` — a restatement of the question. Name three things in it.
Conceptual schema	`tables, columns, types, keys, constraints, relationships; one description for the whole database`	`“the logical design”` — then say what is declared there and what is declared nowhere else.
External schema	`one view per user group: chosen rows, chosen columns, derived columns`	`“what the user sees”` — describe a view you would actually write, for a named group.
How many schemas	`one internal, one conceptual, many external`	`“three”` — there are three levels. The follow-up is the second department’s schema.
Conceptual / internal mapping	`turns a conceptual request into a file, block, offset and access path`	`not mentioning it` — the most common gap, and the one that makes the rest sound memorised.
External / conceptual mapping	`turns a view request into a query over conceptual tables; it is the view definition`	`“the view”` — the mapping is the definition, not the rows it returns.
Physical data independence	`change the internal schema; conceptual schema and applications unchanged`	`“storage does not affect programs”` — true and hollow. Name the mapping that absorbed it.
Logical data independence	`change the conceptual schema; external schemas and applications unchanged`	`“the same, one level up”` — it is not the same. It is the harder one, and you will be asked why.

Ask which level firstEvery question on this topic is really “where is this change written down?”. A new index is internal. A new column is conceptual. A new report for the accounts office is external. Getting this step wrong is how candidates end up calling ADD COLUMN a physical change.

Then name the mappingAn independence claim with no mapping in it is a slogan. Physical changes stop at the conceptual / internal mapping, which the DBMS rewrites for you. Logical changes stop at the external / conceptual mapping, if that view still names everything it needs.

Views store no rowsA plain view is its definition and nothing else, so the external level holds descriptions and the rows are recomputed on every read. A materialised view is the exception and is worth naming as one: it stores its rows, so the external level stops being description-only and somebody has to decide when the copy is refreshed.

06 Where & why

Where the three levels are visible in real systems

This architecture is from an ANSI/SPARC report published in 1975, which makes students assume it is a diagram rather than a thing. In each system below you can point at a level or a mapping and say where it lives, and the last two are interesting because a level is missing or locked away. Naming one of these lands very differently from reciting the diagram.

PostgreSQL · MySQL

You can print the upper mapping as text

CREATE VIEW pass_list AS SELECT name, marks FROM result WHERE marks >= 40 is the external / conceptual mapping, stored not computed. PostgreSQL hands it back with pg_get_viewdef; MySQL keeps it in information_schema.views. Both expand * to a column list when the view is created, so a star in a view definition freezes the shape at that moment.

Oracle

Tablespaces are the internal level, out loud

Oracle names its storage layer separately from its tables: tablespaces, segments, extents and blocks. ALTER TABLE result MOVE TABLESPACE fast_ts relocates every row of the table and not one line of application SQL changes. What you do have to do afterwards is rebuild the indexes, which is the honest edge of physical independence: the mapping is rewritten for you, but rewriting it is not free.

SQLite

An internal level you are not allowed to touch

SQLite gives you almost no control over storage: PRAGMA page_size, an index, and that is close to all of it. There are no tablespaces and no file layout to choose. Views exist, so the external level does. The lesson is that the internal level can be entirely private and the system is still a DBMS; the three levels are a division of responsibility, not three files you can open.

MongoDB

What happens when the conceptual level is optional

You can insert documents with no declared schema, so unless you add a JSON Schema validator the conceptual description lives only in the application. Indexes are still an internal decision you make without editing a query, so physical independence survives. Logical independence does not: when a field changes shape, every program that reads that field is the mapping, and there is no single definition to rewrite.

Name the level, then name the mapping. “Adding an index did not change my SQL” is physical data independence, and the conceptual / internal mapping absorbed it. “Adding a column did not break the exam office report” is logical data independence, and the view definition absorbed it. An answer with no mapping in it is a diagram somebody else drew.

07 Interview questions

What they actually ask

This topic is asked as a diagram question and marked as a reasoning question. Nobody is impressed that you can list three levels. What they are listening for is whether you can classify a change, name the mapping that absorbs it, and say honestly where the architecture stops working.

What is the three schema architecture?

Three descriptions of the same data at three distances from storage, plus two mappings between the neighbouring pairs. The internal schema says how the data is stored, the conceptual schema says what data exists and how it relates, and an external schema says what one group of users may see. It came out of the ANSI/SPARC report of 1975, and its purpose is data independence: a change at one level stops at a mapping instead of reaching the programs above.

What exactly lives at the internal level?

File organisation such as heap, clustered or hashed; the record layout and the byte offset of every field; the block size; which indexes exist and of what kind; compression, and where the file physically sits. What does not live there is the data. The internal schema is a description, exactly like the other two, and it is the only place that knows a row has an address.

How many schemas does a database have?

Three levels, but not three schemas. There is one internal schema and one conceptual schema, and as many external schemas as there are groups of users. Say “three schemas” and the natural follow-up is to describe the schema for a second department, which the answer has already ruled out.

What are the two mappings, and where are they stored?

The conceptual / internal mapping turns a conceptual request such as RESULT.marks into a file, a block, an offset and an access path; the DBMS maintains it in the data dictionary. The external / conceptual mapping turns a view request into a query over conceptual tables, and it is the view definition, also in the dictionary. Both are stored, which is why neither lives in your source code and neither is lost on restart.

Define physical data independence, and give me a change that tests it.

The ability to change the internal schema without changing the conceptual schema or any application. Building a B+ tree index, switching a heap file to a clustered organisation, hashing the file on sid, turning on compression. Only the conceptual / internal mapping is rewritten, and the DBMS rewrites it, because nothing above the internal level ever named a block or an offset.

Define logical data independence.

The ability to change the conceptual schema without changing the external schemas above it or the applications written against them. Adding a column, adding a table, adding a relationship. The external / conceptual mapping, which is the view definition, is supposed to absorb it, and for additive changes it does.

Why is logical data independence harder to achieve than physical?

Three reasons that stack. The mapping that must absorb the change is a view definition a person wrote, not a dictionary entry the DBMS maintains. That definition names conceptual tables and columns out loud, whereas nothing above the internal level ever named a byte offset. And a conceptual change can destroy information: drop a column a view selects and no rewrite of that view can produce it, because the fact is no longer recorded anywhere. A physical change only relocates data, so a mechanical rewrite of the lower mapping always exists.

Adding an index, adding a column, adding a report for the accounts office: which level is each?

Index is internal, column is conceptual, report is external. This is the question that separates people who attached the definitions to real operations from people who memorised them. Note that none of the three changes the data itself, and none of them is an instance change at all.

Three schemas or three tiers, are they the same thing?

No, and mixing them is a common trip. Tiers are a deployment split: how many machines the request crosses on its way to the database. Schemas are a description split: how many ways the same data is described. A single-tier setup on one laptop still has all three schemas, and a three-tier web application still has exactly one conceptual schema.

If the DBMS already gives me views, why do I need the conceptual level at all?

Because the conceptual level is the one description everybody agrees on. Keys, constraints and relationships are declared there once, so two views cannot disagree about what is true. Take it away and every view becomes its own definition of the data with nothing to reconcile them. It is also worth knowing that a plain view stores no rows, only its definition, so the external level holds descriptions and not data.

Give me a conceptual change that logical independence cannot absorb.

Hard

The college replaces the two-character sid with a national student ID, and both must work for one academic year. Using only the three levels and the two mappings, write a plan that lets the existing applications keep running unchanged for that year while new applications use the new ID, and name the one point in your plan where logical data independence genuinely fails and a program must be edited.

Follow-up

A view can hide a column and rename one, but it cannot make a value exist in two forms unless something records both, so say which level holds the extra fact. Account for writes as well as reads: a view a program inserts through is a different problem from one it only selects from.

Show the hint

Decide first whether the second identifier is a new column at the conceptual level or a second table, then walk every operation the old application performs against your view, not only its SELECT.