16.10 - Terminology Used in the ATM Process - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)
Term Definition
Domain A well-defined, closed set of values from which column data can be drawn. Domain is really just another term for data type.

Predefined data types generally do not provide sufficiently distinct domains, and you should consider using distinct user-defined types whenever domain integrity is important to your databases or applications. Note that you cannot specify any type of constraint for a UDT column. You can otherwise use various constraints, particularly check constraints, to restrict the range of values a column will accept, so if your application requires any kind of check, uniqueness, or referential constraints, you cannot define a domain for the column using a distinct type.

See SQL Data Definition Language and SQL External Routine Programming for information about creating UDTs and their associated database objects.

Note that Teradata Database does not support the physical concept of domains.

Constraint A well-defined physical restriction that can be defined for a column or table.
Constraints include the following:
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK expression
  • REFERENCES
The following rules and recommended practices apply to constraints.
  • Always name constraints.
  • Constraint names must be 30 or fewer characters.
  • A constraint name must be unique among all other constraint names defined for a table.
  • You can specify constraints both at the column and table levels.
  • The system does not assign names to constraints you do not name.
  • You cannot define any type of constraint on an XML, BLOB, or CLOB column.

For detailed information about the various constraints, see “CREATE TABLE” in SQL Data Definition Language Detailed Topics.

For design-related information about column and table constraints, see Designing for Database Integrity.

Table A multidimensional, abstract representation of an entity constructed from the following components:
  • Rows, representing tuples
  • Columns, representing attributes

Tables are sometimes referred to as relations, though the correspondence between relations and tables is not always direct.

Example: The Location Entity Before It Is Fully Attributed shows the structure of the Location entity before it is fully attributed.

Row An instance of an object in a relational table. Rows in relational tables are not ordered. The expression is a synonym for record, a term not used for relational database systems.

Rows are sometimes referred to as tuples, though the formal term for the corresponding concept is n-tuple.

The number of rows in a table is referred to as its cardinality.

Example: A Randomly Selected Row From the Location Entity indicates a randomly selected row from the Location entity.

Column A unique, atomic attribute of a relational entity. Columns in relational tables are not ordered logically, though they are ordered physically and can be referred to by their column number.

Columns are sometimes referred to as attributes.

The number of columns in a table is referred to as its degree or arity.

Example: CustNum Column From the Location Entity indicates the CustNum column from the Location entity.

Primary Key The primary key is a column set that uniquely identifies a tuple within a relation. Every relation must have one and only one primary key defined during logical design, but a primary key need not be formally defined for a table corresponding to a logically defined relation.

A table can have multiple candidate primary keys, but only one defined primary key. A candidate primary key not selected as the primary key for a table is referred to as an alternate key.

A primary key cannot be null and must be unique.

No component of a primary key can have the XML, BLOB, or CLOB data types.

Example: The Primary Key Column for the Location Entity indicates the primary key column for the Location entity.

Identity Column A column for which the values are unique and system-generated. Values for identity columns can be generated by the system in all cases or only in those cases for which users do not provide a value.

Identity columns are frequently used to generate surrogate keys.

You cannot define an identity column having the XML, BLOB, or CLOB data types.

For more information about identity columns, see System-Derived and System-Generated Column Data Types the detailed description of identity columns and their use documented by “CREATE TABLE” in SQL Data Definition Language Detailed Topics.

Foreign Key The foreign key is a column set that identifies a relationship between the table for which it is a foreign key and one or more other tables in the database.

Foreign keys are used both as join conditions and to maintain referential integrity between tables.

A foreign key must be the primary key for the table it references and it can be null unless you define the foreign key column set for the table to exclude nulls.

No component of a foreign key can have the XML, BLOB, or CLOB data types.

Example: The Three Foreign Keys For the Location Entity indicates the three foreign key for the Location entity. Notice that the CustNum column, which is constrained to be not null, relates Location to Customer, the State column relates Location to State, and the Country column, which is also constrained to be not null, relates Location table to Country.

Normalization A method for segregating the attributes of a database into individual tables in such a way that those attributes uniquely modify (or depend upon) the primary key for that table.

Somewhat more formally, a relation is said to be fully normalized if all its nonkey attributes are functionally dependent on its primary key.