Terminology Used in the ATM Process - Advanced SQL Engine - Teradata Database

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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL External Routine Programming , B035-1147 for information about creating UDTs and their associated database objects.

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

For more information about the various constraints, see “CREATE TABLE” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

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

• 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.

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.

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.

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 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 Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

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.