Section 20.2. Relational Databases

20.2. Relational Databases

A relational database is a logical representation of data that allows the data to be accessed independently of its physical structure. A relational database organizes data in tables. Figure 20.1 illustrates a sample Employees table that might be used in a personnel system. The table stores the attributes of employees. Tables are composed of rows and columns in which values are stored. This table consists of six rows and five columns. The Number column of each row in this table is the table's primary keya column (or group of columns) in a table that requires a unique value that cannot be duplicated in other rows. This guarantees that a primary key value can be used to uniquely identify a row. A primary key that is composed of two or more columns is known as a composite key. Good examples of primary key columns in other applications are an employee ID number in a payroll system and a part number in an inventory systemvalues in each of these columns are guaranteed to be unique. The rows in Fig. 20.1 are displayed in order by primary key. In this case, the rows are listed in increasing (ascending) order, but they could also be listed in decreasing (descending) order or in no particular order at all. As we will demonstrate in an upcoming example, programs can specify ordering criteria when requesting data from a database.

Figure 20.1. Employees table sample data.

Each column represents a different data attribute. Rows are normally unique (by primary key) within a table, but some column values may be duplicated between rows. For example, three different rows in the Employees table's Department column contain the number 413, indicating that these employees work in the same department.

Different database users are often interested in different data and different relationships among the data. Most users require only subsets of the rows and columns. To obtain these subsets, programs use SQL to define queries that select subsets of the data from a table. For example, a program might select data from the Employees table to create a query result that shows where each department is located, in increasing order by Department number (Fig. 20.2). SQL queries are discussed in Section 20.4. In Section 20.7, you will learn how to use the IDE's Query Builder to create SQL queries.

Figure 20.2. Result of selecting distinct Department and Location data from the Employees table.