The Relational Model as a Data Model

Concept

The relational model represents the entire database as a collection of relations — tables of rows and columns. There are no pointers, no paths and no navigation. Two rows are related when they share a value.

Phase 5 covers the model formally. This note places it beside the other models and explains why it won.

The three parts

Relationships by value

students                      enrolments
+---------+--------+          +---------+--------+-------+
| roll_no | name   |          | roll_no | code   | marks |
+---------+--------+          +---------+--------+-------+
|   21    | Meera  |<---------|   21    | CS201  |  87   |
|   22    | Ravi   |<---------|   21    | CS202  |  74   |
+---------+--------+          |   22    | CS201  |  91   |
                              +---------+--------+-------+

The link is the VALUE 21 appearing in both tables.
No address, no pointer, nothing physical. Move the tables to
another disk, reorder the rows, add an index - the
relationship is unaffected, because it was never physical.

Why the model succeeded

1. Declarative access. You state the result you want; the optimiser decides how to get it. The access path stops being your problem.
2. Physical data independence. Since nothing above the storage layer names a pointer or a file, storage can change freely.
3. Closure. Every operation returns a relation, so operations compose. The result of a query can be the input of another.
4. Formal foundation. Set theory and predicate logic give provable equivalences, which is exactly what a query optimiser needs in order to rewrite a query safely.
5. One structure for everything. Data, relationships and even the catalog are all tables. There is one thing to learn.
6. Design theory. Functional dependencies and normalisation give a repeatable method for judging whether a design is good, which the earlier models never had.

Honest limitations

• Impedance mismatch. Programs work with objects and nested structures; the model works with flat relations. Translating between them is constant work.
• Deep hierarchies are awkward. Recursive structures such as an organisation chart need repeated joins or special support.
• Highly connected data. Queries traversing many relationships repeatedly can be more naturally expressed in a graph model.
• Rigid schema. Every row of a table has the same columns. Sparse or rapidly changing structures fit poorly.
• Horizontal scaling is harder. Joins and strong consistency across many machines are expensive, which is a driver behind Phase 16.

Example

-- Illustration only. This note is about the MODEL,
-- not about the language.

-- Structure: everything is a relation
departments ( dept_code, dept_name )
students    ( roll_no, name, dept_code )

-- Integrity: declared, not coded into applications
--   roll_no   is the primary key      -> entity integrity
--   dept_code references departments  -> referential integrity
--   name      must not be null        -> domain constraint

-- Manipulation: the request states WHAT, never HOW
SELECT s.name, d.dept_name
FROM   students s
JOIN   departments d ON d.dept_code = s.dept_code;

Nothing in that request says which table to read first, whether to use an index, or how to perform the join. Those are decisions the optimiser makes, and it may make them differently tomorrow.

Common mistakes

• Saying the relational model has no relationships because it has no pointers. It has relationships expressed as shared values, which is stronger, not weaker.
• Equating the relational model with SQL. SQL is one language that implements the model, and it departs from it in places — duplicate rows and three valued logic are two.
• Claiming it removes all redundancy. A foreign key is a deliberate repetition of a value.
• Ignoring the limitations. Naming impedance mismatch and horizontal scaling shows real understanding.

Exam and interview questions

1. State the three parts of the relational model.
2. How are relationships represented, and why does that give data independence?
3. What does closure mean, and why does it matter?
4. Give four reasons the relational model displaced hierarchical and network models.
5. Name three limitations of the relational model.

Practice

1. Take the hospital design from the previous note and express it as relations connected by values.
2. Explain in three sentences why an optimiser can rewrite a relational query but could not rewrite a navigational program.
3. Give one real dataset that fits the relational model badly, and say why.

Conclusion

The relational model uses one structure, connects rows by value, defines a closed set of operations and declares its integrity rules. That combination bought declarative querying and data independence, and it is why almost every database course is built around it.