Overview of the Relational Model

This section discusses the basic concepts -- data structures, relationships, and data integrity -- that are the basis of the relational model.

Data Structure and Terminology

In the relational model, a database is a collection of relational tables. A relational table is a flat file composed of a set of named columns and an arbitrary number of unnamed rows. The columns of the tables contain information about the table. The rows of the table represent occurrences of the "thing" represented by the table. A data value is stored in the intersection of a row and column. Each named column has a domain, which is the set of values that may appear in that column. Figure 1.1 shows the relational tables for a simple bibliographic database which stores information about book title, authors, and publishers.

There are alternate names used to describe relational tables. Some manuals use the terms tables, fields, and records to describe relational tables, columns, and rows, respectively. The formal literature tends to use the mathematical terms, relations, attributes, and tuples. Figure 1.2 summarizes these naming conventions.

Figure 1.2 Terminology
This Document Formal Terms Many Database Manuals
Relational Table Relation Table
Column Attribute Field
Row Tuple Record

Notation

Relational tables can be expressed concisely by eliminating the sample data and showing just the table name and the column names. For example,

AUTHOR (au_id, au_lname, au_fname, address, city, state, zip)

TITLE (title_id, title, type, price, pub_id)

PUBLISHER (pub_id, pub_name, city)

AUTHOR_TITLE (au_id, pub_id)

Properties of Relational Tables

Relational tables have six properties:
  1. Each row-column entry in the table must have a single atomic value.
  2. Data values in columns are of the same kind. In relational terms, this property states that all values in a given column must come from the same domain, a set of values that the column can have.
  3. Each row is unique. No two rows may have identical values.

    4.  
  4. Each column must have an unique name.
  5. The sequence of columns (left to right) is insignificant.

    6.  
  6. The sequence of rows (top to bottom) is insignificant.

    7.  

Values are atomic

This property implies that columns in a relational table are not repeating group or arrays. Such tables are referred to as being in the "first normal form" (1NF). The atomic value property of relational tables is important because it is one of the cornerstones of the relational model.

The key benefit of the one value property is that it simplifies data manipulation logic.

Column Values Are of the Same Kind

In relational terms this means that all values in a column come from the same domain. A domain is a set of values which a column may have. For example, a Monthly_Salary column contains only specific monthly salaries. It never contains other information such as comments, status flags, or even weekly salary.

This property simplifies data access because developers and users can be certain of the type of data contained in a given column. It also simplifies data validation. Because all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL) of the database software.

Each Row is Unique

This property ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of which uniquely identify each row in the table. Such columns are called primary keys and are discussed in more detail below.

This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the primary key value.

The Sequence of Columns is Insignificant

This property states that the ordering of the columns in the relational table has no meaning. Columns can be retrieved in any order and in various sequences. The benefit of this property is that it enables many users to share the same table without concern of how the table is organized. It also permits the physical structure of the database to change without affecting the relational tables.

The Sequence of Rows is Insignificant

This property is analogous the one above but applies to rows instead of columns. The main benefit is that the rows of a relational table can be retrieved in different order and sequences. Adding information to a relational table is simplified and does not affect existing queries.

Each Column has A Unique Name

Because the sequence of columns is insignificant, columns must be referenced by name and not by position. In general, a column name need not be unique within an entire database but only within the table to which it belongs.

Relationships and Keys

A relationship is an association between two or more tables. Relationships are expressed in the data values of the primary and foreign keys.

A primary key is a column or columns in a table whose values uniquely identify each row in a table. A foreign key is a column or columns whose values are the same as the primary key of another table. You can think of a foreign key as a copy of primary key from another relational table. The relationship is made between two relational tables by matching the values of the foreign key in one table with the values of the primary key in another.

Keys are fundamental to the concept of relational databases because they enable tables in the database to be related with each other. Navigation around a relational database depends on the ability of the primary key to unambiguously identify specific rows of a table. Navigating between tables requires that the foreign key is able to correctly and consistently reference the values of the primary keys of a related table. For example, Figure 1.3 shows how the keys in the relational tables are used to navigate from AUTHOR to TITLE to PUBLISHER. AUTHOR_TITLE is an all key table used to link AUTHOR and TITLE. This relational table is required because AUTHOR and TITLE have a many-to-many relationship.

Data Integrity

Data integrity means, in part, that you can correctly and consistently navigate and manipulate the tables in the database. There are two basic rules to ensure data integrity: entity integrity and referential integrity.

The entity integrity rule states that the value of the primary key, can never be a null value (A null value is one that has no value. It is not the same as a blank.) Because a primary key is used to identify a unique row in a relational table, its value must always be specified and should never be unknown. The integrity rule requires that inset, update, and delete operations maintain the uniqueness and existence of all primary keys.

The referential integrity rule states that if a relational table has a foreign key, then every value of the foreign key must either be null or match the values in the relational table in which that foreign key is a primary key.

Related Topics

  • Relational Data Structures
  • Relational Data Manipulation
  • Normalization
  • Advanced Normalization



    • 23 August 1996 - The University of Texas at Austin Computation Center