Summary of Fundamental Database Principles | Teradata Vantage - Summary of Fundamental Database Principles - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantageā„¢

This section and The Normalization Process has presented a number of fundamental principles of relational database management. The following table lists a summary of those rules:

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.

Referential integrity rule There cannot be any unmatched foreign key values.

Restated more formally, assume a primary key value PK in relvar T 1 and a candidate key value FK in relvar T 2 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.

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 A statement that is true is also known to be true. Conversely, what is not currently known to be true is false. See The Closed World Assumption and The Closed World Assumption Revisited.
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 No two relations in a relational database should be defined in such a way that they can represent the same facts.
Assignment principle After value x is assigned to variable V, the comparison V=x 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.

Principle of the identity of indiscernibles Every entity has its own identity.

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