Database fundamentals
The Relational Model
The relational model is the most popular logical data model behind SQL databases, built on tables and the relations between them.
The Relational Model is the most popular of the logic data models, and it sits at the basis of SQL databases.
The Relational Model is based on two simple concepts:
- tables
- relations
The relational model dates back to 1969 and the work of Edgar F. Codd, an English computer scientist. As programmers we often chase new shiny things, but a technology that has stayed central to computing for 50 years is worth studying.
The fact that the model is based on tables makes it intuitive. We are used to organizing things in tables. Think about an Excel spreadsheet.
With SQL-based databases like PostgreSQL, Oracle, MySQL, SQLite, and MS SQL Server, data modeled with the ER model can be transformed into a SQL database format. That format is a real-world implementation of the relational model. We will talk about that in other posts.
Here I want to cover the theory and concepts the relational model is built on, not in mathematical terms but what they mean in practice.
If you are a student, what you read here may not match your textbook word for word. Maybe it is easier to read first, then go back to the formal definitions in your course material.
Tables
In a relational model, a table is a collection of items.
It is organized in rows and columns:
| Name | Age |
|---|---|
| Flavio | 36 |
| Roger | 7 |
| Syd | 6 |
Tuples
Each entry in the table is called a tuple. You can also use the terms record or row.
A tuple represents a row of the table, like this:
| Flavio | 36 |
Attributes
An attribute is one single item in the tuple.
In this example:
| Flavio | 36 |
“Flavio” is an attribute. 36 is another attribute.
Tuples are unique
Every tuple in the table is unique.
In the relational model, we cannot have duplicate data. Every row in the table must differ in at least one attribute.
The relation key
The thing that ensures a tuple is unique is the relation key.
The key is one attribute that must uniquely identify a tuple.
If the relation key is a set of attributes, it must be non-redundant. If we remove one attribute from the key, the key can no longer guarantee uniqueness.
If more than one key can be determined, one of those keys will be identified as the primary key.
Key integrity constraint
The key attribute or attributes of any tuple in the table must never be null, and must never repeat.
Given a key, we must be able to point to a specific tuple or row without ambiguity.
The domain constraints
Every attribute has rules about what value it can hold.
If we decide to store numbers, we cannot store strings. We might decide not to store strings longer than 10 characters for names.
We can also call this type.
The referential integrity constraint
If a table contains a reference to a secondary table, or to other tuples in the same table, we must follow rules that prevent the reference from breaking.
In particular, we must avoid breaking the reference by:
- deleting or editing the primary key of the record we point to in the other table
- inserting a new record with a non-existing key to point to in the other table
- changing the key of the record we point to without ensuring that the new key exists in the other table
A DBMS (Database Management System) implements measures to help us enforce referential integrity.
Lesson completed