Schema, Instance and Data Models

DBMS Architecture · 25 min

Core CS · DBMS

The design holds still while the data moves

A schema is written once and edited rarely. An instance changes every second. Half the DBMS questions you will be asked live on one side of that line or the other.

Watch one schema survive seven writes →
Instance at t1 · 78 65 35
→
Instance at t4 · 78 55 · same schema

01 The idea

The design and the data are two different things

In C you write int marks; and then marks = 78;. The first line is a type: it fixes what values are legal and it does not change while the program runs. The second is a value: it changes as often as you like, and the compiler checks every assignment against the first line. A database works the same way, one size up. The type of the whole database is called the schema. One legal value of that type is called an instance.

Here is the schema this whole lesson uses. Read it once now, because every trace, table and console step below is written against it.

CREATE TABLE student (  roll   INT         PRIMARY KEY,  name   VARCHAR(20) NOT NULL,  marks  SMALLINT    CHECK (marks BETWEEN 0 AND 100));

That is five statements. There is a table called student. A row carries a roll, a name and marks. No two rows may share a roll. A name may never be missing. Marks run from 0 to 100. Five statements, and not one of them is about any particular student.

Now put rows in. (1, Asha, 78) arrives, then (2, Ravi, 65), then (3, Meera, 35). Meera’s mark is corrected to 55. Ravi transfers out and his row is deleted. Five changes, five different instances, and the schema was not touched once. That asymmetry is the whole idea. It is also why the two words are never interchangeable: ALTER TABLE changes the schema and UPDATE changes the instance. An interviewer who hears you mix them up will keep pulling on the thread.

One more warning before the vocabulary. A real database has more than one schema at a time, at the internal, conceptual and external levels, and separating them is a topic of its own in this module. Everything in this lesson is true of any of the three.

The schema is the part that does not change. The instance is the part that does. Every write is the DBMS reading the first to decide whether it is allowed to alter the second.
SchemaThe design of the database: table names, column names, types, keys and constraints. Stored in the catalog, written by DDL — CREATE, ALTER, DROP — and edited rarely.
InstanceEvery row in every table at one particular moment. Also called the database state, which is the clearer name. Changed by DML — INSERT, UPDATE, DELETE — and changed constantly.
Valid stateAn instance in which every constraint the schema states is true. The DBMS refuses any write that would leave an invalid state, so a correct system never lets you observe one.

02 Worked example

One write, checked against a design that does not move

Follow the marks column of the same three rows through four moments. Each line below is one instance, with only the marks shown: the rolls stay 1, 2 and 3 in that order and the names never change, so marks are the only thing left to watch.

t1 · 3 inserts786535instance right after the three rows go in
t2 · UPDATE786555roll 3 corrected from 35 to 55 · a new instance
t3 · DELETE78·55roll 2 removed · two rows left
t4 · refused78·55an insert of 350 marks was rejected, so t4 and t3 are the same state

Four moments, four lines, and the design was never mentioned. Read the same four lines in the other direction and the point flips: at every one of them the columns were still roll, name and marks, with the same types and the same constraints. Those are the schema.

The last line is the interesting one, so open it up. Somebody ran INSERT INTO student VALUES (4, 'Nikhil', 350); and the database said no. Nothing about that statement is malformed, and 350 fits comfortably inside a SMALLINT, which holds anything from −32768 to 32767. Here is the whole path it took.

CREATE TABLEthe design is written once, months ago
→
Catalogthe schema is stored: one copy, readable with a SELECT
→
INSERT 350one write arrives, aimed at the instance
→
CheckCHECK (marks BETWEEN 0 AND 100) is read from the catalog and applied
→
Refusedstate unchanged: no row added, no design altered

The highlighted node is where students think the decision happens. It is not. The write carries no rules of its own; the rule was written into the catalog long before and is read back on every attempt. So the type was fine and the constraint was not, and the DBMS chose refusal over an invalid state. That is the only choice it is allowed to make.

Notice what that costs. The schema is a promise the database keeps on your behalf, and it can only keep promises you actually wrote down. Nothing above stops Nikhil being recorded as 82 when he really scored 28. A valid state is a legal state, never a true one. And the notation all of this is written in — tables, columns, values matching values — has a name. It is the relational model, and it is one of seven worth knowing.

03 Mechanics

Seven data models, and the four things that separate them

A data model is the notation a schema may be written in: which structures you are allowed to build, which constraints you may state, and which operations you get for free. That is a third thing, one step more abstract than a schema, and interviewers test whether you can keep the three apart. The relational model is a data model. student(roll, name, marks) is a schema written in it. The two rows in the table today are an instance of that schema.

Seven models come up. Two are history you are still asked about, one is a drawing tool, one is a road not taken, and three are what you actually use. Ignore the adjectives people attach to them and compare the four columns that genuinely differ.

ModelHow data is structuredHow a relationship is representedQuery languageWhere you still meet it
Hierarchical Segments in a tree: one root segment type per database, everything else hanging beneath it. IBM, shipped with IMS in 1968 parent-to-child links; a child has exactly one parent Navigational: DL/I calls such as GET UNIQUE and GET NEXT, one segment at a time IBM IMS on mainframes, still running core banking and insurance. Also every tree you already use: DNS, LDAP, a filesystem, nested JSON
Network Records joined into owner-member sets: a graph, not a tree. Standardised by the CODASYL Data Base Task Group, 1971 — not by IBM, and not by ANSI set pointers; a record may be a member of many sets, so many-to-many is direct Navigational: CODASYL DML, FIND and GET, walking from one current record to the next IDMS on mainframes. The navigational style resurfaces in graph databases such as Neo4j, which are a separate model of their own
Relational Relations: unordered sets of tuples over named, typed attributes. Proposed by E. F. Codd at IBM Research in 1970 matching values; a foreign key holds the same value as some candidate key Declarative SQL, defined over relational algebra. You state what you want, not how to reach it PostgreSQL, MySQL, Oracle, SQL Server, SQLite. Most transactional data in the world
ER Entity sets, attributes and relationship sets, drawn rather than stored. Proposed by Peter Chen in 1976 a relationship is a first-class thing, with a cardinality ratio and a participation constraint None. An ER diagram is mapped to a relational schema before anything runs The design step before CREATE TABLE, and every modelling tool: MySQL Workbench, dbdiagram, ERwin
Object-oriented Objects with their own identity, grouped into classes, with inheritance and methods stored beside the data direct object references held as object identifiers OQL from the ODMG standard, plus ordinary method calls from the host language ObjectDB, db4o, GemStone. Niche. The idea won anyway, as ORMs sitting on top of relational databases
Object-relational Tables, but a column may hold a user-defined type, an array or a whole document still foreign keys, plus reference types SQL with the object extensions added in SQL:1999 PostgreSQL composite types, arrays, JSONB and table inheritance; Oracle object types. Most databases you call relational are really this
Document (NoSQL) Self-describing documents in a collection; two documents need not agree on their fields embed the child inside the parent, or store a reference and join in the application or with $lookup A query API rather than a language: MongoDB’s query documents and its aggregation pipeline MongoDB, Couchbase, Firestore, DocumentDB. Also JSONB columns living inside PostgreSQL
Hierarchical and network are navigational: your program says how to reach the record, so it is written against the physical layout and breaks when the layout moves. Relational is declarative: you say what you want and the planner picks the path. That single change is why the relational model won, and it is why you can add an index tomorrow without editing one query.
On “schema-less”. A document store does not check the shape when you write; it leaves the check to whoever reads. The schema did not disappear, it moved into the application code and became plural: one shape per document, plus whatever the reading code assumes. MongoDB has shipped $jsonSchema validators since 3.6, which is a schema put back exactly where a relational database keeps it.

05 Cheat sheet

The words they will make you define

Each term has a short answer that holds up and a shorter one that invites the follow-up in the third column. The hollow answers are not wrong, which is exactly why they get you into trouble.

TermSay thisThe answer that invites a follow-up
Schemathe design: tables, columns, types, keys, constraints“the structure of the database” — then you are asked which statement changes it, and DDL is the word you left out.
Instanceevery row in every table at one moment“the data” — one row is also data. The three words that save you are at one moment.
Database statea synonym for instance: the contents right now“the state of the DBMS” — that is uptime, sessions and memory, not data.
Valid statean instance satisfying every constraint the schema states“correct data” — a valid state can be factually wrong. The DBMS enforces legality, not truth.
Schema evolutionchanging the design of a live database with ALTER“redesigning it” — evolution is incremental and every existing row has to stay legal.
Data modelthe notation a schema may be written in, plus its operations“the diagram” — a diagram is one schema drawn in one model.
Relational modelrelations of tuples, related by matching values, queried declaratively“tables” — tables are the picture. Values instead of pointers is the property.
ER modela design notation: entities, attributes, relationships, cardinality“a kind of database” — no ER database exists. You map the diagram to tables.
Object-relationalrelational plus user-defined types, arrays and inheritance, per SQL:1999“object-oriented” — a different model. Object-relational keeps tables and keeps SQL.
Document storeself-describing documents, shape checked on read“schema-less” — the schema moved into your code. It did not vanish.
Type and valueThe schema is a type declaration and the instance is one value of that type. One schema has an enormous number of possible instances, and exactly one of them is live at any moment. If you can only remember one sentence from this lesson, remember that one.
Intension and extensionThe textbook words. Intension is the schema, extension is the instance, and they come from relational theory where a relation schema names the attributes and the relation itself is the set of tuples currently in it. If an interviewer uses them, translate to design and data and answer normally.
Valid is not trueThe schema can only refuse what breaks a rule somebody wrote down. It cannot know that Meera really scored 55. Constraints buy you legality, and every remaining error is one that the design never claimed to catch.

06 Where & why

Where the schema actually lives, and what the word means to each vendor

Schema and instance sound like exam vocabulary until you notice that four databases you have used disagree about what the words mean. Answers that name a real system and a real command land differently from answers that recite a definition.

PostgreSQL
The schema is a table you can query

Column definitions live in pg_catalog and are exposed as information_schema.columns, so “what is the schema” is a SELECT and “what is the instance” is a COUNT. Since PostgreSQL 11, ADD COLUMN with a constant default is a catalog-only change, so a design change on a huge table can finish in milliseconds without touching a single row.

Oracle · MySQL
The same two words, three meanings

In the SQL standard and in PostgreSQL a schema is a namespace inside a database that holds tables. In MySQL, SCHEMA is a plain synonym for DATABASE. In Oracle a schema is the set of objects owned by one user, so creating the user creates the schema. Worse, Oracle uses instance for the memory and processes that run the database, not for the rows. Say which vocabulary you are in before you answer.

MongoDB
Checked on read instead of on write

Two documents in one collection may carry different fields, so nothing rejects a stray value at insert time and the reader meets it instead. The design is still there, spread across the documents themselves and the code that reads them. A collection can be given a $jsonSchema validator, available since 3.6, at which point it refuses a bad document much as a CHECK constraint does.

SQLite
The design stored as its own text

sqlite_master holds one row per table, index, view and trigger, and its sql column keeps the original CREATE statement verbatim. The schema is literally a string. SQLite is also dynamically typed: a column declared INTEGER will accept the text twenty unless the table is declared STRICT, which arrived in 3.37. A declared type is not automatically an enforced one.

Answer any schema-or-instance question in two halves: what the design says, and what the rows currently are. Then name where you would go to read each one in a database you have actually opened. That is the difference between having learned the definition and having used the thing.

07 Interview questions

What they actually ask

This pair of terms opens more DBMS interviews than any other, usually as a warm-up that the interviewer expects to take fifteen seconds. Answer each of these out loud, in about twenty.

What is the difference between a schema and an instance?
The schema is the design: table names, column names, types, keys and constraints. The instance is the set of rows sitting in those tables at one particular moment, which is why it is also called the database state. The schema is written once and edited rarely with DDL; the instance changes with every INSERT, UPDATE and DELETE. One schema, and over the life of the database an enormous number of instances.
Explain that with a programming analogy.
The schema is a type declaration and the instance is one value of that type. struct Student { int roll; char name[20]; } is the schema; one filled-in struct is the instance. The analogy carries the right consequence too: the type decides which values are legal and the compiler rejects the rest, which is exactly what a constraint does on every write.
Interviewers sometimes say intension and extension. What do those mean?
Intension is the schema and extension is the instance. They are the words relational theory uses, where a relation schema names the attributes and the relation itself is the set of tuples currently in it. If you hear them, translate to design and data and answer normally. Some books also say snapshot or occurrence for the instance.
What is a database state, and when is it invalid?
The database state is the instance: the contents at a moment in time. It is valid when every constraint the schema states holds, so types, NOT NULL, keys, CHECKs and foreign keys all pass. The DBMS refuses any write that would leave an invalid state, so in a correct system you never observe one. Add the honest half: valid is not the same as true, because no constraint can know what mark the student really scored.
Which statements change the schema and which change the instance?
DDL changes the schema: CREATE, ALTER, DROP. DML changes the instance: INSERT, UPDATE, DELETE. SELECT changes neither; it reads the instance using the schema. TRUNCATE is the trick question, because it is classified as DDL and yet its entire visible effect is on the instance.
Does an ALTER TABLE change the instance as well?
Usually yes, even though it is a schema change. ADD COLUMN gives every existing row a new cell, DROP COLUMN takes data away from every row, and a type change rewrites values. What varies is whether the engine has to touch the rows to do it: PostgreSQL 11 and later can add a column with a constant default as a catalog-only change, and MySQL 8.0.12 added ALGORITHM=INSTANT for the same reason. State the effect first and the cost second.
What is a data model, and how is it different from a schema?
A data model is the notation: which structures you may build, which constraints you may state, and which operations you get. A schema is one design expressed in that notation, and an instance is one set of data conforming to that schema. Three different things, and you can name all three from one example: the relational model, then student(roll, name, marks), then the two rows in it today.
Why did the relational model replace the hierarchical and network models?
Because those two are navigational: the program states the access path, record by record, so it is written against the physical layout and breaks when the layout changes. The relational model is declarative. You say what you want, the planner decides how to fetch it, and the same query survives a new index or a reorganised file. Codd also gave it a mathematical basis in relational algebra, which is what turned query optimisation into a solvable problem.
ER model versus relational model. Why do we need both?
ER is for design and relational is for implementation. ER gives you entities, attributes and relationships with cardinality and participation, so you can argue on paper about whether a student may have zero courses. The relational model has only relations and values, so a relationship is not a separate object; it survives as foreign-key values in the tables the diagram maps to. You draw in ER, then map the drawing to a relational schema, and no database stores the diagram.
Is MongoDB schema-less?
No. Every document has a shape; what the collection does not do is force all documents to share one. The real difference is when the shape gets checked: a relational database checks on write and rejects the row, while a document store defers the check to whoever reads the document. So the schema became plural rather than absent, one shape per document plus whatever the reading code assumes. Nothing was removed, the checking moved.
Is PostgreSQL relational or object-relational?
Object-relational, and so is almost every database people casually call relational. The core is relations queried with SQL, and on top of that sit user-defined and composite types and arrays, which are the object extensions SQL:1999 added, plus table inheritance and JSONB columns, which are PostgreSQL’s own rather than standard SQL. The right answer in an interview is that the relational model is the foundation and the object-relational features are additions to it, not a replacement for it.
In a real project, how often does a schema actually change?
Less often than the data by many orders of magnitude, and more often than students expect: a few times a release, and always the change that gets reviewed twice. The reason is not the ALTER statement itself. During a deploy the old code and the new code both run against the same instance, so the design has to be legal for both at once. That is why teams add columns freely and almost never rename or drop one in the same release.

08 Practice problems

Six to work through

For each one, write your answer in two columns before you check anything: what the design says, and what the data says. Most of the mistakes people make on this topic are answers that quietly swap the two.

Three instances, one schema

Easy
Using the lesson schema student(roll INT PRIMARY KEY, name VARCHAR(20) NOT NULL, marks SMALLINT CHECK (marks BETWEEN 0 AND 100)), write out two instances that are both valid and one that is not, and for the invalid one name the exact constraint it breaks.
Follow-up
One of your two valid instances must contain two rows that a careless reader would call duplicates but that the schema is perfectly happy with. Finding it means reading the design for what it does not say.
Show the hint
Go through the schema clause by clause and list which columns it actually forces to be unique.

Which number moved

Easy
Two screenshots of one database, a week apart. In the first, a query against information_schema.columns for student returns 3 rows and SELECT COUNT(*) FROM student returns 40. In the second, the column query still returns 3 and the count returns 512. Say which of the two numbers is reporting the schema and which is reporting the instance, and name the kind of statement that would have had to run for the number that stayed at 3 to move.
Follow-up
Both numbers came out of the same database on the same two days, and nothing in the screenshots says which one is allowed to move on an ordinary Tuesday and which one moving at all would have got somebody paged. Deciding that is the whole exercise.
Show the hint
For each of the two numbers, ask whether it is a property of the design or a property of the day.

Same fact, three models

Medium
A student may take many courses and a course may have many students. Show how that one fact is represented in the hierarchical model, in the relational model and in a document store, and for each of the three say what has to happen when a student drops a course.
Follow-up
One of the three cannot hold the fact once without duplicating it somewhere, and in one of them the drop touches a single place while in another it touches several. The drop is what exposes the difference, not the diagram.
Show the hint
Before you draw anything, ask how many parents a child record is allowed in each model.

Two ALTERs the data refuses

Medium
Take student holding the three rows from section 02: roll 1 Asha 78, roll 2 Ravi 65, roll 3 Meera 35. Write two different ALTER TABLE statements the database would refuse, each for a different reason, and for each one name the rows that block it and the single DML statement that would let it through.
Follow-up
The console showed you one statement the rows refused, and it needed a column that does not exist here. These three rows have to do the refusing on their own, using only the columns they already have, and your second example must fail for a reason unrelated to your first.
Show the hint
For each candidate ALTER, ask which of the three rows would stop being legal the instant it took effect.

Where did the schema go?

Medium
A team moves a users table out of PostgreSQL into a MongoDB collection and calls the new system schema-less. Six months later a nightly report crashes on a document whose age field holds the string twenty. Say where the schema was in each system, what enforced it in each, name two places the team could put it back without leaving MongoDB, and then say which of those two still refuses the bad document when a one-off migration script writes to the collection directly.
Follow-up
Schema-less is false in a precise way: the design did not disappear, it moved, and the crash happened at the moment it was finally checked. Both of the places you can move it back to are named in this lesson; which of them survives a writer that never runs the team’s code is not.
Show the hint
For each system, ask which piece of code would have to be edited before a bad value could no longer be written.

Add NOT NULL to 40 million rows

Hard
A live table with 40 million rows, written to continuously, needs a new email column that is NOT NULL, with no downtime. Give the ordered sequence of changes that gets you there, label each database step DDL or DML, and name the point in the sequence at which the currently deployed application would start failing if you ran the steps in the wrong order.
Follow-up
The console ran this sequence on a table nobody else was writing to, so filling the column once was enough. Here the application keeps inserting all the way through, which means a backfill that finishes still leaves you exposed: rows created after it started can arrive with the column empty. Closing that hole costs you a step that is not a database statement at all.
Show the hint
Write the sequence out, then after each step ask which of the two versions of the application would now crash.