Schema vs Instance

## The two levels Every relational database exists on two levels at once, and mixing them up is a reliable source of confused reasoning. The **schema**, also called the **intension**, is the description. For a single relation it consists of the relation's name, its heading - the set of attributes, each with a domain that fixes the values it may take - and the constraints declared over it, such as which attribute or combination of attributes is the key and which values are permitted. Written compactly: `EMPLOYEE(emp_id: INTEGER, name: TEXT, dept_id: INTEGER, hired_on: DATE)`, key `{emp_id}`. The **instance**, also called the **extension**, is the actual body of tuples that exists at one moment in time. It is one specific value of the type the schema describes. Right now `EMPLOYEE` may hold four tuples; after the payroll batch runs it holds four hundred. Those are two different instances of the same, unchanged schema. ## The relationship between them One schema admits many instances - in principle every set of tuples that conforms to the heading and satisfies every declared constraint. Such an instance is called a **legal** or **valid** instance of the schema. Instances that would break a constraint are not merely undesirable; they are excluded by definition, and the engine's job at runtime is to ensure the database never lands in one. The empty instance is worth calling out: a relation with zero tuples still has its full schema, and queries against it succeed and return nothing rather than failing. Emptiness is a property of the extension, never of the intension. ## Different rates and mechanisms of change This is the practical heart of the distinction. The schema changes rarely and by deliberate act. Changes to it are expressed as definition changes, reviewed like code, versioned in source control, and applied through migrations. A schema change alters the *type* of the data and can therefore invalidate queries, application objects, reports and integrations. The blast radius is large and the change is coordinated. The instance changes constantly and automatically. Every insert, update and delete moves the database from one instance to another. No review, no migration, no coordination - just transactions. What you protect here is durability and correctness, not compatibility. The two even live in different places: the schema lives in the system catalog (the database's own metadata) and, in a healthy project, in migration files under version control; the instance lives in data pages and in backups. ## Why interviewers ask this The distinction quietly underlies a lot of later reasoning, and answers that blur it go wrong in predictable ways: - **Constraints belong to the schema.** Saying 'this attribute has no duplicates' is a statement about today's extension; declaring a uniqueness constraint is a statement about every future instance. Only the second is a guarantee. - **Queries are written against the schema but evaluated against an instance.** A query that returns the right answer today has been checked against one extension, not against every extension the schema permits - which is why sample data proves much less than people assume. - **Testing and deployment split along the same line.** Schema changes need migration and compatibility thinking; data changes need volume, correctness and recovery thinking. - **The vocabulary recurs.** 'Intension versus extension' is the textbook phrasing you will meet in academic material, and the same idea reappears as type versus value, class versus object, description versus state. ## Saying it well One sentence of definition, one concrete example, one sentence of consequence: 'The schema is the definition - attributes, domains, constraints; the instance is the set of tuples present at a moment. `EMPLOYEE(emp_id, name, dept_id)` is the schema, the four rows in it right now are one instance. The schema changes by migration and is what code is written against; the instance changes with every transaction.'