Relational Constructs   «Prev  Next»

Lesson 5Table Column Rules
ObjectiveList the rules for table columns.

Table Column Rules

When you create a table, you use the columns to store attributes of the entity represented by the table.
For example, a computer might have attributes of manufacturer, processor speed, amount of random access memory, hard-disk space, floppy-disk drive, speed of a CD-ROM drive, and monitor size. Each of those attributes is represented as a column in the Computer table.

Manufacturer Processor Harddrive RAM CD-ROM Monitor
DELL 3.2 GHZ 2 TB 32 GB Optional 15 inch
Apple 2.3 GHZ 1 TB 16 GB Optional 17 inch

Column obeys Two Rules

Each column in a table must adhere to two rules:
  1. The column must have a unique name within the table
  2. The values for the column must come from a single domain. [1]
    (You will learn about column domains in a later lesson.)
The first rule seems obvious: every column must represent a different attribute of the entity represented by the table. If the same attribute occurs in more than one table, a relationship[2] exists between those two tables. Imagine that your database contains two tables: one listing computer manufacturers, the other listing computers owned by your company.

ManufID field creates the relationship between these tables. ManufID is the primary key for the MANUFACTURING table and the foreign key for the COMPUTER table.

These two tables have the ManufId column in common.

Link Tables in Query

Establishing relationships between tables enables you to link tables in a query.
If you have a problem with a computer and need to contact the manufacturer, you can look up the computer's record in the Computer table and, with the ManufId value in the record, find the manufacturer's contact information.
Another benefit to establishing relationships between tables is that, if two tables are in a one-to-many relationship[3] , you can ensure that every record in the table on the many side has a related record in the table on the one side.
In the graphic above, it would make no sense to enter a manufacturer into a record describing a computer unless that manufacturer was listed in the Manufacturers table.
If you enforce referential integrity[4] between two tables, your (RDBMS) Relational Database Management System[5] will prevent you from making that mistake. The next lesson lists the rules for table rows.

[1]Domain: Determines the type of data values that are permitted for that attribute.
[2]relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.
[3] one-to-many relationship: In relational database design, a one-to-many (1:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; but for one instance of entity B, there exists zero or one instance of entity A.
[4]Referential integrity: The means of maintaining the integrity of data between one or more tables that relate to each other. In other words, a column of data in a table has a null or matching value in a corresponding table. Referential integrity is usually enforced with foreign keys.
[5](RDBMS)Relational Database Management System: A software package that manages and provides access to a database. These packages follow Codd’s 12 rules of relational databases and normally use SQL to access data.