Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 79 ยท Introduction to Databases

Schemas, Instances and Data Independence

Understand schema vs instance, the three-schema architecture, logical and physical data independence, and the DDL, DML, DCL and TCL sublanguages, with live SQL.

Two ideas make a database long-lived. First, a clear line between the description of the data and the data itself. Second, the promise that you can change how data is stored, or reorganize parts of the design, without rewriting every program. That promise is called data independence, and it is one of the main reasons database systems replaced hand-rolled files.

This lesson defines schema and instance, explains the classic three-schema architecture, and then proves data independence with two experiments you can run: a view that keeps old queries working after a table is reorganized, and an index that changes how a query runs without changing the query.

Schema and instance

The schema is the description of the database: table names, columns, types, keys and constraints. It changes rarely. The instance (or state) is the actual data stored at one moment: the rows. It changes constantly.

A good analogy is a blank form versus a pile of filled-in forms. The form is the schema; the filled-in forms are the instance. Inserting a row changes the instance; adding a column changes the schema.

The three-schema architecture

Database systems separate the description into three levels so that each can change independently.

Three levels of a database

External Views tailored for each user or application.
Conceptual The logical design for the whole community.
Internal Files, pages, indexes and record layout.

Mappings between the levels keep them separate.

1 / 2
  • The external level has many external schemas (user views). A sales clerk sees orders but not salaries.
  • The conceptual level has one conceptual schema: all entities, attributes, relationships and constraints for the whole organization. In a relational DBMS this is your set of CREATE TABLE statements.
  • The internal level has the internal schema: file structures, indexes, page layout, compression. DBAs and the engine decide it.
flowchart TB
    U1["External schema A<br/>(sales view)"] --> C["Conceptual schema<br/>(all tables)"]
    U2["External schema B<br/>(HR view)"] --> C
    C --> I["Internal schema<br/>(files and indexes)"]

Requests travel down through mappings; each level can change as long as its mapping is updated.

Data independence

Logical data independence is the ability to change the conceptual schema (add a column, split a table) without changing external schemas or application programs. Physical data independence is the ability to change the internal schema (add an index, change file organization, move to faster disks) without changing the conceptual schema or programs.

Physical independence is well supported by relational systems: you rarely change a query because you added an index. Logical independence is harder and partial, because some changes (dropping a column a program uses) genuinely break programs. Views are the main tool for achieving it.

Logical independence with a view

Suppose the application was written against a staff table with one name column. Later the design is improved by splitting the name into two columns. A view can keep the old shape alive.

SQL A view as an external schema

Press Run (or Ctrl + Enter) to see the result here.

Both SELECT statements are identical and return the same three rows, although the conceptual schema changed in between. The old program did not break; only the view definition changed.

Views also restrict what a user sees, which is the external level's other job:

SQL A view that hides columns and rows

Press Run (or Ctrl + Enter) to see the result here.

Physical independence with an index

Now keep the conceptual schema fixed and change only the internal level. EXPLAIN QUERY PLAN shows how SQLite will run a query. We do not change the query at all.

SQL An index changes the plan, not the query

Press Run (or Ctrl + Enter) to see the result here.

The first plan says SCAN readings (read every row). After the index, the plan says it will SEARCH readings USING COVERING INDEX idx_readings_sensor. The answer (20) is the same both times. The programmer wrote nothing about indexes. That is physical data independence in action, and it is why a DBA can tune a production system without asking developers to change code.

The database languages: DDL, DML, DCL, TCL

SQL is one language that plays four roles. Textbooks name the roles as sublanguages.

Sublanguage Purpose Examples
DDL (Data Definition) Define and change the schema CREATE, ALTER, DROP
DML (Data Manipulation) Query and change the instance SELECT, INSERT, UPDATE, DELETE
DCL (Data Control) Permissions GRANT, REVOKE
TCL (Transaction Control) Group work into transactions BEGIN, COMMIT, ROLLBACK, SAVEPOINT

Some books put SELECT into its own category, DQL. The grouping is for teaching; the DBMS has one parser for all of it.

DCL is the one that cannot run here: SQLite has no users or roles, so GRANT and REVOKE are only shown as text.

SQL
-- Read-only: this is server SQL (PostgreSQL syntax), not supported by SQLite
GRANT SELECT ON research_directory TO report_reader;
REVOKE SELECT ON research_directory FROM report_reader;

You will return to permissions in Authentication, Authorization and Access Control.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which of these is part of the schema rather than the instance?

    The schema is the structural description. Rows and their values are the instance.
  2. 2. Adding an index so a query runs faster, with no change to the query text, demonstrates:

    The internal schema changed while the conceptual schema and programs stayed the same.
  3. 3. At which level of the three-schema architecture do views belong?

    External schemas are the tailored views each user or application sees.
  4. 4. Which statement is DDL?

    ALTER changes the schema, so it is data definition language. INSERT and SELECT are DML; COMMIT is TCL.

Summary

  • The schema describes structure and changes rarely; the instance is the current data and changes constantly.
  • The three-schema architecture has external (views), conceptual (logical design) and internal (storage) levels.
  • Logical data independence lets the conceptual schema change without breaking external views and programs; views are the main tool.
  • Physical data independence lets storage change (indexes, files) without touching queries; the optimizer adapts the plan.
  • SQL covers DDL, DML, DCL and TCL; GRANT and REVOKE need a server DBMS and are read-only here.

You now have the foundations. Chapter two compares the ways databases model data: Data Models Compared.