Schema, Instance and Metadata
A schema is the design of a database and rarely changes. An instance is the data in it at one moment and changes constantly. Metadata is the stored description of the schema itself.
-
DBMS Fundamentals
- Data, Information and Databases
- What a DBMS Is and Why It Exists
- File System versus DBMS
- Advantages and Limitations of a DBMS
- Database Users and the Role of the DBA
- Three Level Architecture and Data Abstraction
- Logical and Physical Data Independence
- Schema, Instance and Metadata
- Database Applications and the Database System Environment
- Database Architecture
- Data Models
-
ER Model
- Entities, Entity Sets and Entity Types
- Types of Attributes in the ER Model
- Keys in the ER Model
- Relationships, Relationship Sets and Degree
- Cardinality and Participation Constraints
- Strong and Weak Entities
- Drawing and Reading ER Diagrams
- Extended ER: Generalisation, Specialisation and Aggregation
- Converting an ER Diagram into Relational Tables
- ER Design Projects
- Relational Model
- Relational Algebra
- Functional Dependencies
-
Normalisation
- Why Normalisation Exists: Anomalies and Redundancy
- First Normal Form
- Second Normal Form and Partial Dependency
- Third Normal Form and Transitive Dependency
- BCNF and BCNF Decomposition
- 4NF, 5NF, Multivalued and Join Dependencies
- Lossless Decomposition and Dependency Preservation
- Complete Worked Normalisation: Unnormalised to BCNF
- Denormalisation and When to Use It
Concept
Two words are used almost interchangeably in conversation and mean very different things in this subject.
| Schema | Instance | |
|---|---|---|
| What it is | The design: structure, types, constraints, relationships | The actual data held at one particular moment |
| Also called | Intension | Extension, or database state |
| How often it changes | Rarely, and deliberately | Constantly, with every insert, update and delete |
| Defined by | DDL statements | DML statements |
| Analogy | The class in a program | An object created from that class |
Seeing the difference
SCHEMA (the design - one, stable)
students ( roll_no INT primary key,
name VARCHAR(50) not null,
marks INT check between 0 and 100 )
INSTANCE at 09:00 INSTANCE at 09:05
21 Meera 87 21 Meera 87
22 Ravi 91 22 Ravi 91
23 Anitha 78 <- inserted
Same schema. Different instance. Adding a row does not
change the design; adding a COLUMN does.Schemas at all three levels
Each level of the three level architecture has its own schema.
- External schema — also called a subschema. One per user group.
- Conceptual schema — one for the whole database.
- Internal schema — one, describing storage.
There is no such thing as an external instance. The data exists once; the views are windows on to it.
Metadata and the data dictionary
Metadata is data about the data. It is the stored form of the schema, and the DBMS reads it constantly.
| Metadata includes | Example |
|---|---|
| Table and column names | students.roll_no |
| Data types and sizes | VARCHAR(50) |
| Constraints | Primary key, not null, check |
| Relationships | Foreign key from enrolments to students |
| Indexes and storage detail | B+ tree on roll_no |
| Users, roles and privileges | Who may read the marks column |
| Statistics | Row counts and value distribution, used by the optimiser |
| Views and routines | Their definitions |
Metadata lives in the data dictionary, also called the system catalog. It is itself stored as tables inside the database, which is why a database is described as self describing.
-- Illustration only: the catalog is queryable like any table.
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
ORDER BY table_name, ordinal_position;Why the optimiser needs metadata
Statistics stored as metadata are what let the query optimiser choose a plan. If the catalog says a column holds 400,000 distinct values, an index lookup is attractive. If it says the column holds two values, a full scan may be cheaper. Stale statistics are therefore a real cause of sudden slowness, which is covered again in the query processing phase.
Common mistakes
- Saying the schema changes when a row is inserted. That changes the instance.
- Believing there is one instance per view. There is one database state. Views present it differently.
- Thinking metadata is optional documentation. The DBMS cannot execute a single query without it.
- Forgetting statistics are metadata. This link explains a whole class of performance problems.
Exam and interview questions
- Differentiate schema and instance with an example.
- What are intension and extension?
- How many schemas exist at each of the three levels?
- Define metadata and list six things a data dictionary stores.
- Why does a query optimiser depend on the system catalog?
Practice
- Write a three column schema for a library book table, then write two different instances of it.
- Classify each as a schema change or an instance change: adding a student, adding a column, changing a data type, correcting a mark, dropping an index.
- List four items of metadata a DBMS must read before it can execute a simple query.
Conclusion
The schema is the design and is stable, the instance is the current data and is not, and metadata is the schema stored as data so that the system, and anyone else, can discover the structure without reading application code.