Database Management Systems (DBMS)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
- Each key must be assigned a unique value.
- All the data elements of a table must have a one-to-one relationship to the value in their key field.
- 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.
- 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
Bruce Miller, 2004, 2014
All Articles Index
Hermit Homepage