![]() |
Covers operation symbols used for math, string manipulation, logic, and comparison expressions. Includes order of precedence and truth table. Rationalizing Database StructuresThe 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
Four rules for creating a proper database structure
Three tests for correct database structure1) Data elements should not be repeated in a fileSee 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 keyIf 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:
Bruce Miller, 2004, 2014 All Articles Index ![]() |