Basic Concepts of DBMS

A database management system (DBMS) is the software that stores your data, lets many users read and change it at the same time, and guarantees it stays correct when things go wrong. PostgreSQL, MySQL, SQL Server, Oracle Database and SQLite are all DBMSs. The handful of concepts below, tables, keys, relationships, normalization, transactions and indexes, are the same in every relational one.

This page builds one small order-tracking schema and uses it for every concept. The SQL runs unchanged on current PostgreSQL and MySQL (8.0.16 or later, where CHECK constraints are enforced).

Tables, rows and columns

A relational database stores data in tables. Each table describes one kind of thing. A row (also called a record or tuple) is one instance of that thing, and a column (attribute) is one fact about it, with a declared data type.

The table definitions together are the schema. The rows in the tables at a given moment are the database instance, or state. The schema changes rarely; the instance changes with every write.

CREATE TABLE customers (
    customer_id INT          PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id INT            PRIMARY KEY,
    name       VARCHAR(100)   NOT NULL,
    price      DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock      INT            NOT NULL CHECK (stock >= 0)
);

NOT NULL, UNIQUE and CHECK are constraints: rules the DBMS enforces on every write, no matter which application sent it. This is what "data integrity" means in practice. A rule that lives only in application code is broken by the first script that bypasses that code.

Keys

A primary key is the column, or set of columns, that identifies each row. It must be unique and can never be NULL. A table has exactly one.

A candidate key is any column set that could have served as the primary key. In customers, both customer_id and email are candidates. The one you did not pick is usually declared UNIQUE, as email is here.

A foreign key is a column whose values must match a key in another table. It is how one table points at another:

CREATE TABLE orders (
    order_id    INT       PRIMARY KEY,
    customer_id INT       NOT NULL,
    ordered_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

CREATE TABLE order_items (
    order_id   INT            NOT NULL,
    product_id INT            NOT NULL,
    quantity   INT            NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders (order_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

order_items has a composite primary key: neither column is unique alone, but the pair is.

Load some rows:

INSERT INTO customers (customer_id, name, email) VALUES
    (1, 'Asha', 'asha@example.com'),
    (2, 'Ben',  'ben@example.com');

INSERT INTO products (product_id, name, price, stock) VALUES
    (10, 'Keyboard', 45.00, 20),
    (11, 'Mouse',    15.00, 50);

INSERT INTO orders (order_id, customer_id) VALUES (100, 1), (101, 2);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (100, 10, 1, 45.00),
    (100, 11, 2, 15.00),
    (101, 11, 3, 15.00);

The foreign key now rejects an order for a customer who does not exist. On PostgreSQL:

INSERT INTO orders (order_id, customer_id) VALUES (103, 999);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

It also stops you from deleting customer 1 while their orders exist, unless you declare ON DELETE CASCADE or ON DELETE SET NULL on the key.

Relationships

Foreign keys express three kinds of relationship:

Relationship	Example here	How it is stored
One-to-many	One customer has many orders	Foreign key on the "many" side (orders.customer_id)
Many-to-many	An order has many products, a product is in many orders	A junction table with two foreign keys (order_items)
One-to-one	A customer and an optional profile row	Foreign key that is also UNIQUE or the primary key

You put related rows back together with a join:

SELECT o.order_id, c.name, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
GROUP BY o.order_id, c.name
ORDER BY o.order_id;

 order_id | name | total
----------+------+-------
      100 | Asha | 75.00
      101 | Ben  | 45.00

Normalization

Normalization is the process that leads to a design like the one above. Start from the spreadsheet most projects begin with:

order_id | customer_name | customer_email   | items
---------+---------------+------------------+----------------------
100      | Asha          | asha@example.com | Keyboard x1, Mouse x2
101      | Ben           | ben@example.com  | Mouse x3

Each normal form removes one kind of problem:

• First normal form (1NF): every column holds a single value. The items list breaks this, and you cannot index it, sum it or constrain it. Fix: one row per order line.
• Second normal form (2NF): every non-key column depends on the whole key. Once the key is (order_id, product_id), a product's name depends on only half of it. Fix: move product facts to products.
• Third normal form (3NF): non-key columns depend on nothing but the key. customer_email depends on the customer, not on the order. Fix: move customer facts to customers.

The payoff is that each fact is stored once. When Asha changes her email you update one row, and it cannot disagree with itself.

Normalization is not "never repeat a value". order_items.unit_price looks like a copy of products.price, but it records a different fact: the price at the time of sale. Without it, a price change would rewrite the totals of old orders.

Transactions and ACID

A transaction groups statements into one unit of work. Placing an order touches three tables, and a half-placed order is worse than no order:

BEGIN;

UPDATE products SET stock = stock - 2 WHERE product_id = 10;
INSERT INTO orders (order_id, customer_id) VALUES (102, 1);
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (102, 10, 2, 45.00);

COMMIT;

The guarantees a DBMS gives a transaction are abbreviated ACID:

Property	Guarantee	In the example
Atomicity	All statements take effect, or none do	If the server crashes after the UPDATE, stock is not reduced
Consistency	A committed transaction never violates a declared constraint	Stock cannot go below zero
Isolation	Concurrent transactions do not see each other's unfinished work	Another session sees stock 20 until the COMMIT, then 18
Durability	Committed changes survive a crash or power loss	After COMMIT returns, order 102 exists after a restart

To abandon a transaction, send ROLLBACK instead of COMMIT. Consistency is enforced for you: try to oversell and the statement fails.

BEGIN;
UPDATE products SET stock = stock - 500 WHERE product_id = 10;

ERROR:  new row for relation "products" violates check constraint "products_stock_check"

MySQL reports the same violation as error 3819. Send ROLLBACK afterwards. PostgreSQL refuses every further statement in a failed transaction until you do.

Isolation levels and locking

Isolation is adjustable, because full isolation costs concurrency. The SQL standard defines four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. The defaults differ: PostgreSQL and SQL Server use READ COMMITTED, and MySQL's InnoDB uses REPEATABLE READ. The same code can therefore behave differently on two systems.

The DBMS implements isolation with locks and with multiversion concurrency control (MVCC). Under MVCC, readers see a snapshot of the data and do not block writers. Two transactions that update the same row still queue behind a row lock. If each holds a lock the other needs, that is a deadlock: the DBMS aborts one of them, and the application should retry it.

Indexes

Without an index, finding one customer's orders means reading every row of orders. An index is a separate sorted structure, usually a B-tree, that maps column values to row locations.

CREATE INDEX idx_orders_customer ON orders (customer_id);

• Primary keys and UNIQUE constraints get an index automatically.
• Foreign key columns do not get one in PostgreSQL. MySQL's InnoDB creates one for you. Joins and parent-row deletes are slow without it.
• Every index slows down INSERT, UPDATE and DELETE and uses disk space. Index the columns you filter, join and sort on, not every column.
• Prefix a query with EXPLAIN to see whether an index is used.

Common mistakes

• No primary key. Duplicate rows creep in and you cannot update a single row reliably.
• Lists in one column. Comma-separated IDs break 1NF. Use a junction table.
• Relationships without foreign keys. Orphaned rows accumulate and nothing reports them.
• Assuming NULL is a value. NULL means unknown. WHERE email = NULL matches nothing; write IS NULL. A UNIQUE column accepts several NULLs in PostgreSQL and MySQL, but only one in SQL Server.
• Money in FLOAT. Binary floating point cannot store 0.10 exactly. Use DECIMAL.
• Relying on autocommit. Outside an explicit transaction each statement commits by itself, so a multi-step change can stop halfway.

Further reading

• PostgreSQL documentation: Constraints
• PostgreSQL documentation: Transaction Isolation
• MySQL Reference Manual: InnoDB Locking and Transaction Model