Splitting tables the right way

A relational database is one that represents data as two-dimensional tables. There are rows and columns, and tables relate to each other through the values in their columns.

Behind those tables sits a piece of mathematics called the relational model, which uses different words. The exam often asks how they line up.

• Relation … a table
• Tuple … a row
• Attribute … a column
• Domain … the range of values that column may take

What to watch is that words lining up does not mean the properties do too. Attributes and tuples in the relational model have no order, whereas the columns of an implemented table run left to right and the rows have an order as well. A domain can also be numeric or a date, not only text.

Before you put any data in, you have to decide and declare what columns there are and what types they hold. This collection of data definitions is the schema. What you were writing with CREATE TABLE was exactly a schema declaration.

Hold on to the point that a schema is about shape, not about operations and not about constraints. Operations such as INSERT and SELECT are something else, and constraints such as "this value must not be empty" are something else again.

On top of that a DBMS keeps its schema in three layers. The purpose is to keep a change in a lower layer from reaching the ones above. That you can change how things are laid out on disk without touching the application is thanks to this split.

You need a column that can point reliably at one particular row in a table. That is the primary key.

Being a primary key takes two conditions.

• Unique … no two rows may hold the same value
• Not empty … NULL may not be put in

That being unique is not enough is a favourite thing to ask. A blank leaves you unable to say "which row", so NULL is forbidden as well.

Sometimes several columns could serve. In an employee table, either the employee number or the email address settles a row. Each of these eligible candidates is a candidate key, and you choose one of them as the primary key.

A primary key need not be a single column. Several columns taken together, such as "order number plus product number", can be the primary key.

The customer_no in the orders table points at the primary key of the customers table. A column that points at the primary key of another table is a foreign key. Being able to write JOIN ... ON orders.customer_no = customers.customer_no came from that correspondence.

Declare a foreign key and the DBMS refuses values with nothing to point at. Adding an order with a customer number that is not in the customers table will not go through, and neither will deleting a customer row while orders remain. This state, where what is pointed at always exists, is called referential integrity.

The purpose of a foreign key is to impose that constraint and nothing else. It is not there to make searches faster, nor to recover broken data.

To learn the procedure for splitting tables, you first have to be able to say "what settles what". When fixing the value of X settles the value of Y to exactly one, Y is functionally dependent on X, written X -> Y.

Fix an employee number and the name is settled, so emp_no -> name. The reverse does not hold, since two people can share a name, so note that it has a direction.

Two shapes have names of their own, so learn these.

• Partial functional dependency … where the primary key is several columns, something is settled by only part of it
• Transitive functional dependency … X -> Y and Y -> Z join up, so X settles Z

These two are the targets of second and third normal form, coming next.

Normalisation is the procedure for splitting tables, guided by functional dependencies. The stages are numbered, and you work through first, second and third.

The state where nothing has been done is called unnormalised. It is a table copied straight off a paper slip, with repetition inside a single row — "one order slip with three product lines hanging off it".

First normal form is the state where that repetition has been split into separate rows so that one cell holds exactly one value. Where one slip was one row, you open it out so that one product line is one row.

Get that far and it is finally in a shape SELECT can work with. If one cell were packed with several values, WHERE could not narrow it down.

Second normal form is the state with partial functional dependencies driven out. If the primary key is "order number plus product number" and yet there is a column settled by only part of the key, such as product_no -> product_name, you move it into another table. The product name goes off to a products table.

Third normal form is the state with transitive functional dependencies driven out. Among the columns settled by the primary key, if any is settled by way of a non-key column, as in order_no -> customer_no -> customer_name, that goes into another table too. The customer name moves to a customers table.

The way to remember it: only the thing being driven out differs. Second is "columns settled by part of the key", third is "columns settled by a non-key column". Either way it becomes the primary key where it lands, and only a foreign key stays behind in the original table.

This is what the exam asks about most. The purpose of normalisation is to avoid writing the same fact in several places — in other words eliminating redundancy.

And what does removing redundancy prevent? The odd situations that arise on update, known as update anomalies.

• You want to correct a customer name, but unless you correct every row that holds it, the data disagrees with itself
• You want to register a customer with no orders yet, but with no order row there is nowhere to put them
• You delete the last order and the customer information vanishes along with it

What trips people up here is that it is not for improving storage efficiency. Less duplication does save space, but that is a by-product, not the aim. The aim is to keep the data from disagreeing with itself.

You want to sort out how the tables will be split before you write any CREATE TABLE. The picture for that is the E-R diagram (entity-relationship diagram). It represents the world of interest with just two ideas: entities and relationships.

• Entity … a kind of thing you want to keep track of, such as "customer" or "product"
• Relationship … a connection between entities, such as "places an order"
• Attribute … a property an entity holds, such as "customer name"

A relationship carries a multiplicity showing which of one-to-one, one-to-many and many-to-many it is. If one customer has many orders, it is one-to-many.

Many-to-many cannot become a table as it stands. When both sides can be several, as with students and subjects, you put one table in between. Create an enrolment table and you have two one-to-many relationships instead. Situations where a JOIN links three tables come from this.

In the relational model, operations that make a new table out of tables are called relational operations. Learn three names and the SELECT statements you have been writing read straight off.

• Projection … take particular columns out of a table → the columns listed after SELECT
• Selection … take the rows matching a condition out of a table → WHERE
• Join … put two or more tables together into one → JOIN, or two tables in FROM

Projection is columns, selection is rows. The names feel like the wrong way round, so learn this one deliberately.

There is one more: the Cartesian product, which pairs up the rows of two tables exhaustively. In SQL it is simply listing tables as FROM R, S. Add a WHERE condition to that and you have a join, which is why FROM A, B WHERE A.x = B.x gives the same result as a JOIN.

Once normalisation has multiplied your tables, writing the same JOIN every time gets tiresome. So you give the result of an operation a name and present it like a table. That is a view.

Sort out the words. A table that actually holds data is a base table; a table that comes out as the result of an operation is a derived table. A view is a derived table with a name, and it holds no data of its own. The operation you defined runs every time it is used.

There are two gains: calling a frequently used JOIN by a single word, and being able to hand over only the columns you want seen.

Note the rules about order and number too. A view cannot be defined without a base table, so the base table comes first. You can create as many views as you like on one base table, and you can even define a view on top of a view.