Covers operation symbols used for math, string manipulation, logic, and comparison expressions. Includes order of precedence and truth table.

Rationalizing Database Structures

The structure of a database must reflect the natural organization of the information it contains and it must do so independently of the software.

A primary key is the data element which uniquely identifies all the other elements with which it is associated. A primary key must have a one-to-one relationship to all of the elements with which it is associated.

The three types of data relationships

• One-to-One
• One-to-Many
• Many-to-Many

Four rules for creating a proper database structure

1. Each key must be assigned a unique value.
2. All the data elements of a table must have a one-to-one relationship to the value in their key field.
3. For two tables with a one-to-many relationship, place the primary key from the "one" table in the "many" table as a foreign key.
4. For two tables with a many-to-many relationship, create a third table (the lien) and place the primary keys from both many-to-many tables in it as foreign keys, as well as any data elements which are completely dependent upon both the keys.

Three tests for correct database structure

1) Data elements should not be repeated in a file

See how in the following example a DIRECTOR'S NAME would be repeated for all EMPLOYEES with an identical DEPARTMENT CODE.

                        +-----------+------------+
                        |           |            |
                 +--------------------------------------+
                 | DEPARTMENT | DIRECTOR'S | EMPLOYEE'S |
                 |    CODE    |    NAME    |    NAME    |
                 +--------------------------------------+
                              Repeating Field

To correct the problem, remove the repeating field(s) to a separate table:

           +-------------------------------+
           |  +-----------+                |  +------------+
           |  |           |                |  |            |
       +-------------------------+     +-------------------------+
       | DEPARTMENT | DIRECTOR'S |     | DEPARTMENT | EMPLOYEE'S |
       |     CODE   |    NAME    |     |    CODE    |     NAME   |
       +-------------------------+     +-------------------------+

2)All fields should be dependent upon both parts of a concatenated key

If not, it is partial dependence.

In the following table, see how CLIENT NAME is only dependent upon CLIENT ID and not ORDER NUMBER.

                  +---------+------------+-------------+
                  |         |            |             |
              +-----------------------------------------------+
              | ORDER  | CLIENT   |   CLIENT    |  DEPARTMENT |
              | NUMBER |   ID     |    NAME     |             |
              +-----------------------------------------------+
                  |         |    Partial Dependence
                  +---------+
                Concatenated Key

To correct partial independence, place the offending field in a separate database with the key upon which it is wholly dependent:

                +---------+---------+                +--------+
                |         |         |                |        |
           +-------------------------------+    +-----------------+
           |  ORDER  | CLIENT | DEPARTMENT |    | CLIENT | CLIENT |
           |  NUMBER |   ID   |            |    |   ID   |  NAME  |
           +-------------------------------+    +-----------------+
                          |                          |
                          +--------------------------+

3) All fields in a file should be related only to the primary key

                 +---------+-----------+------------+
                 |         |           |            |
             +--------------------------------------------+
             |  KEY  |  FIELD_1  |  FIELD_2   |  FIELD_3  |
             +--------------------------------------------+

Otherwise, it is transitive dependence:

                 +----------+----------+---------+
                 |          |          |         |
           +--------------------------------------------+
           | EMPLOYEE  | EMPLOYEE |  TASK  | COMPLETION |
           |    ID     |   NAME   |   ID   |    DATE    |
           +--------------------------------------------+
                                        |         |
                                        +---------+

To correct transitive dependence:

                +----------+----------+           +---------+
                |          |          |           |         |
           +------------------------------+  +---------------------+
           | EMPLOYEE | EMPLOYEE |  TASK  |  |  TASK  | COMPLETION |
           |    ID    |   NAME   |   ID   |  |   ID   |    DATE    |
           +------------------------------+  +---------------------+
                                      |           |
                                      +-----------+

Also see:
Intro to Database Concepts
and
Mail Order Database Design Case