Introduction to DBMS

A database is an organised collection of related data, for example the students, courses and marks of a college. A database management system (DBMS) is the software that stores this data and lets many users read and change it safely: MySQL, PostgreSQL, Oracle and SQLite are DBMSs.

Why not just use files?

Before DBMSs, every program kept its own files. That caused problems a DBMS is designed to solve:

The three-schema architecture

A DBMS separates how users see the data from how it is stored, using three levels:

• The external level holds views: a fees clerk sees names and fees, an examiner sees names and marks.
• The conceptual level describes the whole database logically: which tables exist, their columns, data types, keys and constraints.
• The internal level describes physical storage: file organisation, record layout, indexes and compression.

A schema is the description of the database (its design), which rarely changes. An instance (or state) is the data stored at one moment, which changes with every insert, update and delete.

Data independence

Because of the levels, a change at one level need not affect the level above it:

• Physical data independence: the internal schema can change (for example, adding an index or moving to new disks) without changing the conceptual schema or the programs.
• Logical data independence: the conceptual schema can change (for example, adding a column or splitting a table) without changing the external views that do not use the change.

Logical data independence is harder to achieve, because programs and views refer directly to tables and columns.

Database languages

Here is a complete small example: DDL creates a table with constraints, DML adds rows and asks a question. Every SQL example in this track is run on a real SQLite database when the site is built, and the output shown is the actual result.

CREATE TABLE student (
  roll  INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  marks INTEGER CHECK (marks BETWEEN 0 AND 100)
);
INSERT INTO student VALUES (1, 'Asha', 82), (2, 'Ravi', 67), (3, 'Meena', 91);
SELECT name, marks FROM student WHERE marks > 70;

name   marks
-----  -----
Asha   82
Meena  91

The CHECK constraint is enforced by the DBMS: an insert with marks = 120 would be rejected, whatever program tries it.

Data models

A data model is the set of concepts used to describe data:

• Relational model: data in tables (relations). Used by almost all databases today and by the rest of this track.
• Entity–Relationship (ER) model: a diagram-based model used to design a database before creating tables (next lesson).
• Older models: hierarchical (a tree of records) and network (a graph of records).
• NoSQL models (key–value, document, column-family, graph) trade some relational features for scale or flexibility.

Practice questions