The Basics (II) – Database Tables

As mentioned in the previous post, the data in a database are organized into tables. A table (also referred to as a relation) is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. The data in a table is usually all related to some entity type. Each column in the table represents an attribute of that entity, while each row (tuple) represents an instance of that entity. A table has a specified number of columns but can have any number of rows.

Columns (attributes) can be of different types. Possible types include text, numeric, date & time, binary objects, etc. For example, in Figure 1, the “Last name” column usually contains textual data while the “salary” column contains numeric data.

Figure 1 – Employee Table

Each table usually has one of its columns defined as the primary key (or simply the key) of the table. The primary key can be defined as follows: Given a particular value of that column, one can always identify a single row in that table. For example, assume that the table in figure 1 contains an additional column “ID”, and assume that each employee has a unique ID. Now if the user looks for any ID value, the system can find the single row corresponding to that ID (since no 2 employees can have the same ID). Any column that satisfies this condition is called a candidate key. One of these candidate keys is selected to be the primary key of the table. On he other hand, the column “Salary” is not a candidate key; given a salary value, one cannot identify a single employee with that salary. As a result, the primary key column cannot contain duplicate values. In some cases, however, the primary key is set as a group of columns, instead of a single column. Each of these columns might contain duplicates. However, each combination of values across the whole group of columns is unique. In this case, this primary key is called a compound key or a composite key.