Relational Database Concepts (2)

This is the first in a series of articles describing the use of Microsoft's SQL Server database management system (DBMS). The tutorial is aimed at software developers using SQL Server for data storage. Part one reviews database concepts and terminology.

Elements of a Relational Database Schema

There are several key elements to a relational database created using Microsoft SQL Server. Each of these forms a part of the database's schema. The schema is the logical data model that determines the information that may be stored in the database and how it is to be arranged. This section describes some of the key terminology that should be understood before continuing with the tutorial.

Tables

The table is the most important element of a relational database. Tables are where the information within a database is held. A table can be thought of as a grid of information where the columns in the grid define the elements of data required and each row is an individual record. In fact, the terms "columns" and "rows" are those used to describe the table's schema and its data respectively.

Employees Table
Payroll ID	Name	Vehicle Registration
1001	John Smith	ZX08 W4S
1002	Bill Jones	NX08 DS8
1003	Lisa Brown	NJ57 D1D
1005	Louise Green	NX08 DS8

The above data could be held in a table of employees. The table contains three columns for the payroll ID, name and vehicle registration number; these being defined in the table's schema. The table has four rows of data, one for each employee that has access to a vehicle. The vehicle's details will be held in a separate, related table linked by the unique vehicle registration number.

NB: The storage of data in a table is more complicated than that shown above. However, this is a useful metaphor for this article.

Indexes

One of the greatest benefits of holding information in a database is the ability to quickly retrieve it. When querying a database, it is possible to apply criteria to ask for a specific set of rows. For example, returning all employees that use a specific vehicle or returning employees within a particular range of payroll IDs.

If the DBMS needed to scan through all of the data within a table in order to retrieve the desired information, the process would be very slow, particularly for tables with millions of rows. To improve retrieval performance a table can have one or more indexes. Each index provides a fast "look-up" facility for rows, as an index in a book allows all references to a topic to be located without reading every page.

In the fleet database example, an index may be created for the Vehicle Registration column. This would facilitate fast queries that required data to be filtered using that column.

Keys

There are two types of key that are important for Microsoft SQL Server databases. The first is the primary key. A primary key is a single column, or group of several columns (compound key), that can be used to uniquely identify rows in a table. Each table in a database may have a single primary key. Once defined, no two rows in the table may contain matching data in the primary key columns. In the fleet database example, the unique Payroll ID value could be the primary key for the Employees table.

Foreign keys are used when defining relationships between tables. A foreign key is a single column, or group of columns, in a table that reference the primary key in another table. This creates a link between the two tables. In the fleet database, a table named "Vehicles" may be used to contain the details of each vehicle owned by the company. The Vehicles table could have a primary key based upon the vehicle registration number. By creating a foreign key in the matching column of the Employees table, the employee and vehicle data become linked.

The information in foreign key columns does not have to be unique. Matching information can appear in multiple rows, as in the example employees table where two people use the same car. For this reason, relationships are often called "one-to-many links", as one value in the primary key of a table may link to many rows in related tables.

NB: A foreign key does not always refer to a secondary table. By linking a foreign key in a table to the primary key of the same table, a self-referencing relationship is created. These can be used to create hierarchical data structures.