Summary of Fundamental Database Principles - Teradata Database

Teradata Database Design
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

Summary of Fundamental Database Principles

This chapter and Chapter 5: “The Normalization Process” has presented a number of fundamental principles of relational database management. The following summary of those rules, including several introduced for the first time, is based in part on Date (2005, 2009):

 

Principle

Definition

Entity integrity rule

The attributes of the primary key of a relation cannot be null.

More accurately, this rule applies to any candidate key of a relation, not just to the candidate key chosen to be the primary key.

Note that because this rule is explicitly intended to apply to base relations only and not virtual relations (views), it violates the Principle of Interchangeability.

See “Rules for Primary Keys” on page 94.

Referential integrity rule

There cannot be any unmatched foreign key values.

Restated more formally, assume a primary key value PK in relvar T1 and a candidate key value FK in relvar T2 that references it.

The Referential Integrity Rule states that if FK references PK, then PK must exist.

Note that the Referential Integrity Rule permits the attributes of FK to be wholly or partly null, which violates the Entity Integrity Rule because FK must reference a candidate key in T1, and no attribute of a candidate key can be null.

See “The Referential Integrity Rule” on page 97.

Information principle

A relational database contains nothing but relation variables. In other words, the information content of a relational database at any given instant is represented as explicit values (recall that nulls are not values) in attribute positions in tuples in relations.

Closed world assumption

If a tuple t could appear in relation variable R at a given instant, but does not appear in that relvar, then the logical proposition p that corresponds to t must evaluate to FALSE at that time. See “The Closed World Assumption” on page 579 and “The Closed World Assumption Revisited” on page 621.

Principle of interchangeability

No arbitrary or unnecessary distinctions shall be made between base relations (base tables) and virtual relations (views).

Principles of normalization
  • A relation variable that is not in 5NF should be decomposed into a set of 5NF projections.
  • The decomposition must be non‑loss.
  • The decomposition must preserve dependencies.
  • Every projection on the non‑5NF relvar must be required to reconstruct the original relvar from those projections.
  • The decomposition should stop as soon as all of its relation variables are in 5NF (or, situation permitting, 6NF).
    		•

    Principle of orthogonal design

    Let T1 and T2 be distinct relation variables contained within the same database.

    There must not be a non‑loss decomposition of T1 and T2 such that the relation variable constraints for some projection of T1 and some projection of T2 in those decompositions are such as to permit the same tuple to appear in both of the projections.

    Assignment principle

    After value v is assigned to variable V, the comparison V=v must evaluate to TRUE.

    Golden rule

    No update operation can ever cause any database constraint to evaluate to FALSE.

    In other words, no statement can leave any relvar with a value that violates its relvar predicate.

    See “Relations, Relation Values, and Relation Variables” on page 577.

    Principle of the identity of indiscernibles

    Every entity has its own identity.

    In more formal terms, let E1 and E2 be any two entities. If there is no way to distinguish between E1 and E2, then E1 and E2 are identical: they are one thing, not two.