SELECT, you have used it.
The relational model organizes data into relations, which most people call tables. Each row is one entity, such as a student. Each column is one property of that entity, such as a name or an age. Relations connect to each other through shared values called keys, and a set of constraints stops bad data from getting in.
The model is a theory, and SQL databases are practical implementations of it. They follow it closely but not perfectly; this post points out where they differ.
Anatomy of a relation
The model has its own vocabulary, but every term names something you can point at in a table. We will use one smallSTUDENT relation throughout.
A few rules follow from calling a table a relation, a word borrowed from set theory:
- Values are atomic. Each cell holds one value from its domain, not a list or a nested table.
- No two tuples are identical. A relation is a set, and a set has no duplicates.
- Order carries no meaning. Rows have no first or last, and attributes are found by name, not position.
ORDER BY exists because rows come back in no guaranteed order.
Textbooks write a schema as R(A1, A2, …, An), where n is the degree. STUDENT.NAME names an attribute of a relation, and t[NAME] is that attribute’s value in tuple t.
Schema vs. instance
The schema and the data change at very different speeds. The schema is decided when the table is designed and rarely changes after that. The data changes with every insert, update and delete. The set of tuples at one moment is called an instance (or relation state). The difference matters because rules are written against the schema and must hold for every instance, including ones that do not exist yet. That idea comes back when we choose keys.Keys: telling tuples apart
Since a relation has no duplicate tuples, some set of attributes always separates any two rows. Keys name that set. The terms nest inside each other:- A superkey is any set of attributes whose values are different for every tuple.
- A candidate key is a minimal superkey: remove any attribute and it stops being unique.
- The primary key is the one candidate key the designer picks as the main identifier. It can never be NULL.
- A key made of two or more attributes is called a composite key. That describes its size; it is not a separate kind of key.
NAME row is the trap. In these four rows every name is different, so NAME looks like a key. But the next student could also be called RAM. Data can prove that a set of attributes is not a key; it can never prove that one is. Keys are declared from what the data means, which is why the school assigns ROLL_NO.
Real tables often have more than one candidate key. An EMPLOYEE table might have both an employee number and a national ID. Both are unique, and the designer picks one as primary.
Constraints: rules on every write
Relations do not live alone. Give each student aBRANCH_CODE that refers to a row in a BRANCH relation, and you have a foreign key: an attribute whose values must match a key in another relation (or the same one). STUDENT is the referencing relation; BRANCH is the referenced one.
The DBMS checks every insert, update and delete against four kinds of constraint. If any check fails, the whole write is refused.
Two details are worth knowing.
1. Deleting a branch that students still use. RAM and RAMESH are both in branch
CS. What happens if you delete CS from BRANCH?
- By default, the database says no. RAM and RAMESH would point at a branch that no longer exists. In SQL this default is called
NO ACTIONorRESTRICT. - You can pick a different rule when you create the foreign key:
ON DELETE CASCADE: deletingCSalso deletes RAM and RAMESH.ON DELETE SET NULL: RAM and RAMESH stay, but theirBRANCH_CODEbecomes empty.
STUDENT to BRANCH, so removing a student leaves nothing pointing at nothing.
2. A foreign key does not have to point at the primary key. Say BRANCH_NAME is marked UNIQUE, so no two branches can share a name. Then STUDENT could store 'COMPUTER SCIENCE' instead of 'CS' and point at that column. A foreign key can point at any column whose values never repeat. In SQL, that means the primary key or any column marked UNIQUE.
The order in which the figure runs the checks is a teaching device. A real DBMS may check them in any order; what matters is that one failure rejects the write.
Some tutorials, including the source of this post, call these rejected writes insertion and deletion anomalies. In database design, “anomaly” means something else: a problem caused by storing the same fact in several places, which normalization fixes. A refused write is the constraint doing its job.
Operations return relations
Codd defined a small set of operations on relations, called relational algebra. SQL is built on them. The three you use daily:- Selection (σ) keeps the tuples that match a condition.
- Projection (π) keeps only some attributes.
- Join (⋈) combines tuples from two relations whose values match.
ADDRESS returns DELHI once, because a relation is a set. SQL returns it twice unless you write DISTINCT.
Codd’s rules
In 1985, Codd published a list of rules that a system must follow to call itself relational. People call them the “12 rules”, but they are numbered 0 to 12, so there are thirteen. A few show the spirit of the list:
No mainstream product meets every rule completely. The list is better read as a description of the goal than as a pass/fail test.
Strengths and limits
Most relational systems also offer ACID transactions. That comes from the DBMS implementation rather than from the model itself, but the two are rarely seen apart.
Check yourself
Glossary
For AI agents
Adapted from GeeksforGeeks, “Relational Model in DBMS”, with corrections to its definitions of candidate keys, foreign keys and anomalies. Figures are simplified teaching models; the order of constraint checks in Figure 4 is illustrative.