Defining Tables, Rows, and Columns

You are already familiar with the idea of a table that is composed of rows and columns. But you must respect the following rules when you define the tables of a formal data model:

• Rows must stand alone.

  Each row of a table is independent and does not depend on any other row of the same table. As a consequence, the order of the rows in a table is not significant in the model. The model should still be correct even if all the rows of a table are shuffled into random order.

  After the database is implemented, you can tell the database server to store rows in a certain order for the sake of efficiency, but that order does not affect the model.

• Rows must be unique.

  In every row, some column must contain a unique value. If no single column has this property, the values of some group of columns taken as a whole must be different in every row.

• Columns must stand alone.

  The order of columns within a table has no meaning in the model. The model should still be correct even if the columns are rearranged.

  After the database is implemented, programs and stored queries that use an asterisk to mean all columns are dependent on the final order of columns, but that order does not affect the model.

• Column values must be unitary.

  A column can contain only single values, never lists or repeating groups. Composite values must be separated into individual columns. For example, if you decide to treat a person's first and last names as separate values, as the examples in this chapter show, the names must be in separate columns, not in a single name column.

• Each column must have a unique name.

  Two columns within the same table cannot share the same name. However, you can have columns that contain similar information. For example, the name table in the telephone directory example contains columns for children's names. You can name each column, child1, child2, and so on.

• Each column must contain data of a single type.

  A column must contain information of the same data type. For example, a column that is identified as an integer must contain only numeric information, not characters from a name.

If your previous experience is only with data organized as arrays or sequential files, these rules might seem unnatural. However, relational database theory shows that you can represent all types of data with only tables, rows, and columns that follow these rules. With a little practice, these rules become automatic.

Placing Constraints on Columns

When you define your table and columns with the CREATE TABLE statement, you constrain each column. These constraints specify whether you want the column to contain characters or numbers, the form that you want dates to use, and other constraints. A domain describes the constraints when it identifies the set of valid values that attributes can assume.

Domain Characteristics

You define the domain characteristics of columns when you create a table. A column can contain the following domain characteristics:

• Data type (INTEGER, CHAR, DATE, and so on)
• Format (for example, yy/mm/dd)
• Range (for example, 1,000 to 5,400)
• Meaning (for example, serial number)
• Allowable values (for example, only grades S or U)
• Uniqueness
• Null support
• Default value
• Referential constraints

For information about how to define domains, see Choosing Data Types. For information about how to create your tables and database, see Implementing a Relational Data Model.