Overview of Relational Databases

An online store needs to answer one question: which products did Priya buy?

Priya's name and city sit in one place. Her orders sit somewhere else. Something has to connect them.

In a relational database that connection is not a pointer, a file path, or an address in memory. It is a plain value that both places happen to hold.

A relational database stores all its data in tables, and connects those tables using values they have in common. That one idea is the whole model. Everything else in this course is a consequence of it.

The vocabulary

Every part of a table has two names: a plain one and a formal one. Both appear in interviews, so learn the pairs.

The word relational comes from relation, the formal word for a table. It does not come from relationships between tables, which is the guess most people make.

A worked example

Here are two tables from that online store.

Customers

Orders

The customer_id column appears in both tables. That is the entire connection.

Orders 501 and 503 carry customer_id 1, and row 1 of Customers is Priya. So Priya bought a keyboard and a mouse. In SQL you ask for that like this.

SELECT o.product
FROM Customers c
JOIN Orders o ON o.customer_id = c.customer_id
WHERE c.name = 'Priya Sharma';

An operation that combines rows from two tables by matching values is called a join. It is the most used operation in relational databases.

Keys

A key is a column, or a group of columns, used to identify rows. Two kinds matter most.

A primary key identifies one row and never repeats. In Customers it is customer_id. No two customers may share a value, and it may never be empty.

A foreign key holds a primary key value that belongs to another table. In Orders it is customer_id, and it points at Customers.

The foreign key is the piece the connection depends on, and the database protects it. If you try to insert an order with customer_id 7 when no customer 7 exists, the DBMS rejects the insert. That rule is called referential integrity.

Now try to delete customer 1 while her orders still exist. The DBMS either refuses, or removes those orders with her. Which one happens depends on the rule you chose when you created the table. Either way, you cannot end up with an order belonging to nobody.

The rules a table obeys

These four rules are what make a table a relation. They look small and they decide a great deal later.

Every row has the same columns. There are no optional extra fields on some rows. A missing value is stored as NULL, which means unknown or not applicable.

Each cell holds one value from the column's domain. Not a list, not a nested record. You will meet this rule again by name in the lesson on First Normal Form.

Row order carries no meaning. The rows above could be stored in any order and the table would be identical. If order matters to you, put it in a column.

In the formal model, no two rows can be identical. A relation is a set of tuples, and a set cannot hold the same item twice. Real SQL tables are more relaxed and do allow duplicate rows, unless a primary key or a unique constraint forbids them. Good design forbids them.

Where the model came from

Edgar F. Codd published the relational model in 1970, while working at IBM. Before it, programs navigated data by following stored pointers from record to record, and moving data meant rewriting programs.

Codd's proposal was to describe data only as values in tables, and to let the system work out how to fetch them. That separation of what from how is the reason the model lasted.

What the model gives you

Data independence. You can add an index or move to a bigger disk without touching a query, because nothing in a query names a storage location.

Declarative queries. You describe the result you want. The DBMS picks the method, and it can pick a better one later without you editing anything.

Rules the database enforces. Primary keys, foreign keys, and column constraints hold for every application that connects, not only the ones that remember to check.

A precise theory of design. Because the model is mathematical, there are exact tests for whether a design carries a flaw. Those tests are the normal forms, and they are the subject of the Normalization chapter.

Systems that implement the model

MySQL is open source and very widely used behind websites.

PostgreSQL is open source, strict about correctness, and rich in features.

Oracle Database is a commercial system used by large enterprises.

Microsoft SQL Server is a commercial system common in organisations built on Microsoft software.

SQLite is a small library that stores a whole database in one file. It runs inside mobile apps, browsers, and desktop software rather than on a server.

They differ in speed, price, and extra features. The design work in this course applies to all of them.

Key Takeaways

• A relational database stores everything in tables and connects tables through values they share, not through pointers.
• Relation means table, tuple means row, attribute means column, and domain means the allowed set of values.
• A primary key identifies one row and never repeats. A foreign key holds a primary key value from another table.
• Referential integrity is the DBMS rule that a foreign key value must match a real row in the other table.
• A table's rules are: same columns on every row, one value per cell, no meaning in row order, and no duplicate rows in the formal model.
• Codd published the model in 1970, and its lasting idea is that you describe the result you want while the system decides how to get it.

You now have the pieces: tables, rows, columns, keys, and joins. Before we start designing with them, it is worth knowing when a table is the wrong shape. That is what the next lesson covers.