Relational Databases

Tables

Tables are simply lists of things, with details about those things . Databases are made up of one or more of these Tables. There should be a table for each kind of thing.
E.G. Employees, Client, Suppliers, Products.

They are organised into Rows and Columns.

The Columns, usually called Fields, define which details are stored about each item in the list. These Field definitions are hardly-ever changed.
E.G. Name, Address, Telephone, Salary.

The Rows are usually called Records. Users can change the data in the fields of these Records and vary the number of Records in the table. They can, for instance, add new employees, delete ex-employees, or change salaries of existing employees.

Unique IDs

Each table has one Field marked as the Primary Key. This is a unique code which is used to refer to the record.
E.G Employee ID, Model No.

We could use freeform text fields such as a full product description or an employee's full name to refer to the Record but in practice these could be mis-spelled. They are also less efficient than short codes.

When one of these codes is used as data in another table it is called a Foreign Key.
E.G. each Product might have a Supplier ID.

By using exact codes to refer to things we can look at the data in different ways. We could, for example, print a list of Products which is summarised by Supplier.

Relationships Between Tables

Tables can be related by linking Primary Keys and Foreign Keys. The Relationships show how the things in the tables are related. They save us from duplicating data such as employee addresses or product descriptions by allowing us to simply refer to them via the Relationship.
E.G. The CustomerID field in the Customers file is linked to the CustomerID field in the Orders file.

If we wish, we can allow the user to change the data in the related field as if it were a normal field. However this would change that data wherever else it was being used.

'One To One' Relationship: E.G. Linking One Customer to One Order.

'One To Many' Relationship: E.G. Linking a list of Many Products to One Order.

'Many To Many' Relationship: E.G. Linking Each Project to a list of Employees, and Linking each Employee back to a list of Projects.

Views

Views, often called Queries, are new Tables created by manipulating the existing Tables. We usually do this by defining the View using a simple notation such as SQL (Standard Query Language).

As far as the computer is concerned these Views are simply re-arrangements of the existing data so the data in these Views will continue to change as the base data changes.

True Relational Databases allow us to use these Views as if they were normal Tables, so we can create new Relationships between them to create more complicated structures.

Summary

This idea of a Database of Views of Tables of Things, and the Relationships between them, is simple yet powerful. It can be used to represent most situations and processes in our world.

Copyright © Murray Cumming, Openismus GmbH.

This work is licensed under a Creative Commons License.