Schema and Instances

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

Schema and Instances

The schema is the design; the instance is the data currently in it — a distinction exactly like class versus object.

🌍 Where this lives: every migration tool you will ever use — Django's makemigrations, Rails' ActiveRecord::Migration, Flyway, Liquibase — exists to version the schema while leaving the instance alone. That is why a deploy can add a column to a table holding ten million rows without deleting any of them. It is also why "schema migration" is a scary phrase in production: the schema change is instantaneous in the catalog but may rewrite every row on disk, locking the table meanwhile. Search "zero downtime schema migration" — the techniques all come down to making schema changes that no existing instance violates.

The distinction

SCHEMA — the STRUCTURE. Defined once, changes rarely. · table names, column names, data types · keys, constraints, relationships · stored in the DBMS's system catalog · analogous to a CLASS declaration, or a struct definition INSTANCE (also called a "database state" or "extension") — the DATA. Changes with every INSERT, UPDATE, DELETE. · the actual set of rows present right now · analogous to an OBJECT, or a struct variable's contents SCHEMA INSTANCE at time T ───────────────────────────────────────────────────────── student(roll INTEGER, {(101,'Ram',87.5), name VARCHAR(60), (102,'Sita',91.0), marks NUMERIC(5,2)) (103,'Hari',76.5)} One schema. A DIFFERENT instance every second. THE PROGRAMMING PARALLEL, worth stating in an exam: struct Student { int roll; char name[60]; float marks; }; ↑ the SCHEMA — a type, no data Student s = {101, "Ram", 87.5}; ↑ an INSTANCE — actual values THREE SCHEMAS, ONE PER LEVEL (from the previous topic): external schema — a view definition conceptual schema — the whole logical design internal schema — the physical storage design And correspondingly, the INSTANCE at each level is the data as seen through that level. VALID INSTANCE: an instance that satisfies every constraint in the schema. The DBMS's job is to reject any operation that would produce an invalid instance — that is what constraint enforcement means.

schema_vs_instance.sql

-- ======== THE SCHEMA — written once ========
CREATE TABLE department (
    code   CHAR(6)     PRIMARY KEY,
    name   VARCHAR(60) NOT NULL UNIQUE,
    budget NUMERIC(12,2) CHECK (budget >= 0)
);

CREATE TABLE student (
    roll        INTEGER      PRIMARY KEY,
    name        VARCHAR(60)  NOT NULL,
    dept_code   CHAR(6)      NOT NULL REFERENCES department(code),
    marks       NUMERIC(5,2) CHECK (marks BETWEEN 0 AND 100),
    admitted_on DATE         NOT NULL DEFAULT CURRENT_DATE
);

-- ======== INSTANCE at time T1 ========
INSERT INTO department VALUES
    ('ACtE07', 'Computer Engineering', 5000000),
    ('AExE01', 'Electrical Engineering', 4200000);

INSERT INTO student (roll, name, dept_code, marks) VALUES
    (101, 'Ram Bahadur',  'ACtE07', 87.5),
    (102, 'Sita Devi',    'ACtE07', 91.0),
    (103, 'Hari Prasad',  'AExE01', 76.5);
-- instance: 2 departments, 3 students

-- ======== INSTANCE at time T2 — same schema ========
INSERT INTO student (roll, name, dept_code, marks) VALUES
    (104, 'Gita Kumari',  'ACtE07', 68.0);
UPDATE student SET marks = 89.0 WHERE roll = 101;
DELETE FROM student WHERE roll = 103;
-- instance: 2 departments, 3 students (different ones)
-- THE SCHEMA DID NOT CHANGE. Only the data did.

-- ======== OPERATIONS THE SCHEMA REJECTS ========
-- Each of these would create an INVALID instance.

INSERT INTO student (roll, name, dept_code, marks)
    VALUES (101, 'Duplicate', 'ACtE07', 50);
-- ERROR: duplicate key value violates unique constraint
--        "student_pkey"
-- DETAIL: Key (roll)=(101) already exists.

INSERT INTO student (roll, name, dept_code, marks)
    VALUES (105, 'Bad Marks', 'ACtE07', 150);
-- ERROR: new row violates check constraint
--        "student_marks_check"

INSERT INTO student (roll, name, dept_code, marks)
    VALUES (106, 'Ghost Dept', 'XXXXXX', 70);
-- ERROR: insert or update on table "student" violates
--        foreign key constraint "student_dept_code_fkey"

INSERT INTO student (roll, name, dept_code, marks)
    VALUES (107, NULL, 'ACtE07', 70);
-- ERROR: null value in column "name" violates
--        not-null constraint

DELETE FROM department WHERE code = 'ACtE07';
-- ERROR: update or delete on table "department" violates
--        foreign key constraint on table "student"
--        (students still reference it)

🌍 A detail worth knowing: constraints are not checked in the order you might expect. Writing that insert as VALUES (101, 'Duplicate', 'ACtE07', 50, NULL) — supplying NULL for admitted_on rather than letting the DEFAULT apply — produces a not-null violation on admitted_on, not the duplicate-key error, because row-level checks run before the index is consulted. When a statement violates several constraints, which error you see depends on the engine's evaluation order. That is why "it failed with error X" is weaker evidence than "it failed" — always fix the constraint the message names, then re-run.

Five rejected operations, five different constraint types — and every one of them protects an INSTANCE-level property declared in the SCHEMA: PRIMARY KEY → no two students share a roll number CHECK → marks stay within 0..100 FOREIGN KEY → every dept_code refers to a real department NOT NULL → every student has a name FK on DELETE → a department with students cannot vanish Notice what this means: the schema is not merely a description, it is an ENFORCED CONTRACT. The DBMS refuses any transition that would move the database from a valid instance to an invalid one. That is the deep reason to put constraints in the schema rather than in application code. Application checks can be bypassed — by a second application, by a manual SQL session, by a bug. A schema constraint cannot.

The last error is the most instructive: deleting a department failed because students referenced it. The constraint is about the instance (which departments have students right now), but it is declared in the schema. That is the relationship between the two — the schema states invariants, the instance either satisfies them or is rejected.

Worked example — reading the system catalog

The schema itself is stored as DATA, in system tables. So you can query the schema with ordinary SQL — a genuinely elegant piece of design called a "self-describing" system. PostgreSQL / standard SQL uses INFORMATION_SCHEMA: -- what tables exist? SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; -- what columns does student have? SELECT column_name, data_type, is_nullable, character_maximum_length, column_default FROM information_schema.columns WHERE table_name = 'student' ORDER BY ordinal_position; -- what constraints are declared? SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_name = 'student'; TYPICAL RESULT for the columns query: column_name | data_type | is_nullable | max_len | default -------------+-----------+-------------+---------+--------- roll | integer | NO | | name | varchar | NO | 60 | dept_code | character | NO | 6 | marks | numeric | YES | | admitted_on | date | NO | | CURRENT_DATE AND THE INSTANCE, queried the ordinary way: SELECT COUNT(*) FROM student; → 3 SELECT * FROM student; → the actual rows Two queries, two different things: the first describes the SCHEMA, the second returns the INSTANCE. MySQL: SHOW CREATE TABLE student; / DESCRIBE student; Oracle: SELECT * FROM user_tab_columns WHERE table_name='STUDENT'; SQLite: .schema student / PRAGMA table_info(student);

Schema evolution — changing the design under live data

migration.sql

-- The hard part of schema change is that an INSTANCE already
-- exists and must remain valid after the change.

-- ===== SAFE: adding a nullable column =====
-- Existing 10,000,000 rows get NULL. No rewrite needed in
-- PostgreSQL 11+, so this is instant.
ALTER TABLE student ADD COLUMN email VARCHAR(120);

-- ===== DANGEROUS: adding a NOT NULL column with no default
-- Every existing row would violate it immediately. =====
ALTER TABLE student ADD COLUMN phone VARCHAR(15) NOT NULL;
-- ERROR: column "phone" of relation "student" contains
--        null values

-- ===== THE THREE-STEP SAFE PATTERN =====
-- 1. add it nullable
ALTER TABLE student ADD COLUMN phone VARCHAR(15);
-- 2. backfill the existing instance
UPDATE student SET phone = 'unknown' WHERE phone IS NULL;
-- 3. NOW the constraint can be added, because no row
--    violates it
ALTER TABLE student ALTER COLUMN phone SET NOT NULL;

-- ===== TIGHTENING A CONSTRAINT =====
-- Suppose marks were CHECK(0..100) and we now want 0..100
-- with at most 2 decimals AND a pass floor of 32 recorded.
-- Existing rows must be inspected FIRST.
SELECT roll, marks FROM student WHERE marks < 32;
-- decide what to do with those rows, THEN:
ALTER TABLE student
    ADD CONSTRAINT chk_marks_pass CHECK (marks >= 32);
-- fails if ANY existing row breaks it — the DBMS validates
-- the whole instance against the new schema.

-- ===== WIDENING vs NARROWING =====
ALTER TABLE student ALTER COLUMN name TYPE VARCHAR(120);
-- SAFE: every existing 60-char value fits in 120

ALTER TABLE student ALTER COLUMN name TYPE VARCHAR(20);
-- ERROR if any name exceeds 20 characters.
-- Narrowing must be validated against the instance.

-- ===== RENAMING: schema change, instance untouched =====
ALTER TABLE student RENAME COLUMN marks TO total_marks;
-- The DATA does not move at all — only the catalog entry
-- changes. But every view, query and application naming
-- "marks" now breaks. Cheap on disk, expensive on
-- dependencies.

The pattern across all of these: A schema change is ACCEPTED only if the existing instance remains VALID under the new schema. · widening / adding nullable → always valid, instant · adding NOT NULL → needs a backfill first · tightening a CHECK → the DBMS validates every existing row · narrowing a type → validated, may fail · renaming → instance untouched, but dependencies break THE COST DIMENSION most courses omit: some of these rewrite the table. ADD COLUMN nullable → catalog only, O(1) ADD COLUMN with a default → PostgreSQL 11+ catalog only; older versions rewrote every row, O(n) ALTER TYPE (widening varchar)→ often catalog only ALTER TYPE int → bigint → full rewrite, O(n), and the table is LOCKED throughout For a 10-million-row table on a live system, an O(n) rewrite under an exclusive lock means minutes of downtime. That is why production migrations use the add-backfill-swap pattern rather than a single ALTER.

🌍 Go further: the schema/instance split is exactly what "schemaless" NoSQL databases give up — MongoDB lets every document have different fields, so there is no schema to violate and therefore no schema to protect you. In practice teams rediscover the need and add validation back (MongoDB now has $jsonSchema validators). The interesting middle ground is schema-on-read, where files are stored raw and a schema is applied at query time — how Parquet, Avro and data lakes work, with schema evolution rules that formalise exactly the safe/unsafe distinctions above. Search "schema on read vs schema on write" and "Avro schema evolution compatibility".

💡 Exam angle: define schema as structure and instance as current data, with the class/object analogy — that comparison earns marks. State that a schema changes rarely while an instance changes with every DML statement, and that the schema is stored in the system catalog and queryable via INFORMATION_SCHEMA. Know the term valid instance (one satisfying every schema constraint) and be ready to give constraint violations as examples of rejected instances. Mention that the three architecture levels each have their own schema.

Syllabus points

Schema vs instance

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.