Ordnance Survey – Great Britain's national mapping agency
![Ordnance Survey home page [logo]](/images/gui/logo-os.gif){kind=logo}
6.3: Spatial databases (2)
Relational databases
The storage of information in a relational database is fairly simple. The records of information are organised into rows and columns in a table, with a separate row for each entity and a column for each property stored about that set of entities. For example, a table could contain a list of cars with each entry containing values such as a registration number, colour, mileage, make or mode. This is essentially the same as a spreadsheet. Each column is formatted to store a particular type of data, be that text, numbers, dates or boolean. These tables can be queried just like the earlier examples of selecting data in a GIS from chapter 3.3. A structured query language (SQL) that is used by almost all RDBMS to allow interrogation of the data has been developed. For example, the SQL statement Select * from CARS where MAKE = "Ford" will generate a list of all the Fords in the original table.
For the computer to understand the data correctly there can be no duplicate records in a table. In a relational database each record in a table must be uniquely identified for queries to be meaningful. This usually means that one of the columns should contain unique values – the primary key. In our example you could assume that car registration numbers will never be duplicated so this column could be used as the primary key. Often the data does not inherently contain a primary key, so the database will generate its own set of unique ID numbers for that table.
The term relational derives from the fact that such databases use multiple tables to store the information, with data linked together by relationships between these tables. These tables contain information about like entities and make storage more efficient by avoiding duplication. For example, in the car table there is no point in storing both the make and model of each car. You know that every record having Vectra as the model will also have Vauxhall as the make. You can store the relationship between model and make in a small separate table and only record the model in the main table. This is a key concept of database design and is known as normalisation. Database normalisation saves storage space and makes the data easier to index and analyse. Querying highly normalised relational databases can become quite complex since a large number of tables may need to be linked together.
Crucial to the efficient querying of a database is the way in which the tables are indexed. You can create indexes on tables that enable the computer to sort through and answer a query quickly. One way in which this can work is for a column containing textual data to generate an index in which the values are sorted alphabetically. The index stores ranges of values so that to respond to a query the software only has to search through a small subset of the whole table to find the required records.