The concept of domain as used in relational database theory derives directly from the more formal definition given in set theory and function theory. A variable is defined as a set of points having both a domain and a range, where the domain defines the specific type of data represented by the set and the range defines the bounds on that type.
A more concise way of expressing this is to say that domains are data types. It is frequently a good idea to extend this definition to include constraints defined for a column. Extended domains of this type are frequently referred to as business rules. A simple example would be to define employee_number as an INTEGER type and extend the definition with a constraint to disallow negative integer numbers as valid employee numbers. Such an example conflates the mathematical notions of range and domain. Strictly speaking, a domain is only a data type, and range constraints are not part of its definition.
Domains have been a fundamental concept supporting the theory of relational databases since the inception of that theory. The variables described by a domain in a relational database are the valid values an attribute, or column, can have.
Unfortunately, the ANSI/ISO SQL standard does not support a rigorous atomic definition of domain, so it is up to the database designer to define the domains for a database and their ranges rigorously by creating various constraints on table columns, by creating appropriate user-defined data types (the behaviors of distinct UDTs are based on the behaviors of the predefined data types from which they are derived. Distinct UDTs can optionally also have user-defined behaviors.
The behaviors of the other category of user-defined data types, structured UDTs, are exclusively user-defined. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for more information about creating distinct and structured UDTs and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL External Routine Programming , B035-1147 for details about user-defined data types and their associated database objects), and by ensuring that applications do not make nonsensical cross-domain data manipulations such as subtracting an integer part number from an integer inventory count (see Column Comparisons).
Properties of Domains In Teradata Database
- A domain defines the set of possible values that can be written to a table column.
- The values of a domain cannot be decomposed to component values; they are atomic.This is not true for domains having INTERVAL, TIMESTAMP, or structured UDT data types, which are not atomic, but the concept generally holds true otherwise.
- A domain must have a name.
- A domain must have an assigned data type.
Domains and Keys
- All primary key values for a table are drawn from the domain that defines all possible values for that column.
That domain cannot be defined with an XML, BLOB, or CLOB data type.
- All foreign key values that reference a primary key value in another table are drawn from the same domain as the values for that primary key.
That domain cannot be defined with an XML, BLOB, or CLOB data type.
Teradata Database Data Types
When you applications require a strict domain type to ensure domain integrity, particularly for column comparisons and arithmetic operations, you should consider defining user-defined distinct data types for those domains.
See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for information about creating user-defined data types and their associated database objects.
See Teradata Vantage™ - SQL External Routine Programming , B035-1147 for information about writing external code for the methods you define to work with user-defined types.
The following table lists the valid Teradata Database predefined data types you can use to define a domain or to create new user-defined data types.
| Data Type | Definition |
|---|---|
|
Not valid. You cannot create a distinct UDT using a ARRAY/VARRAY data types, so you cannot create a domain using an ARRAY/VARRAY predefined type as its base. |
|
A binary integer used to store digital images. |
| BINARY LARGE OBJECT (BLOB) | A large binary string used to store binary objects such as musical recordings, videos, and other multimedia. |
|
Any glyph from a supported language. For the English language, this type is often referred to as alphanumeric. The CLOB type is typically used to define large character objects whose length exceeds 64KB. |
| CHARACTER LARGE OBJECT (CLOB) | A large character string used to store documents, possibly encoded using tag languages such as XML. |
| XML | A large binary string used to store XML documents in XML format. |
| DATE | A valid, named 24-hour epoch from the Gregorian calendar. |
| DECIMAL | Any base-10 real number, including those with a fractional part. |
| NUMBER (Exact form) | Any base-10 real number, including those with a fractional part. |
|
A rational number expressed in exponential format which, depending on the value, might be exact or might be an approximation to a real number. |
| NUMBER (Approximate form) | Any base-10 rational number expressed in exponential format. |
|
Any natural number. |
| INTERVAL | A time duration with optional fractional precision. Interval data is not implemented atomically, though it is generally treated logically as if it were atomic. |
| TIME | A valid time expressed using 24-hour notation with optional fractional precision. The TIME type can also be defined with a TIME ZONE. |
| TIMESTAMP | A valid date and time expressed using 24-hour notation with optional fractional precision. The TIMESTAMP type can also be defined with a TIME ZONE. |
| Period | Not valid. You cannot create a distinct UDT using the Period data type, so you cannot create a domain using the Period predefined type as its base. |
See Teradata Vantage™ - Data Types and Literals, B035-1143 for complete listings and descriptions of the data types available for Teradata Database table columns.
You can also use these predefined data types to create your own user-defined data types that may be more suitable for your particular application workloads. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL External Routine Programming , B035-1147 for details.
Domains in the Logical Data Model
You should always define and assign domains to your table attributes during the logical design phase of your database design.
Domains can be bounded specifically, nonspecifically, or both simultaneously. For example, you can define the employee number domain to be both not null (a nonspecific boundary) and constrained within a restricted range such as greater than 100 and less than 1 000 000. Unfortunately, you cannot specify CHECK or any other types of constraints on UDT columns, which lessens their usefulness for defining domains.
Column Comparisons
The necessity for naming your domains becomes clear when you examine the topic of column comparisons.
- They must share the same set of values.
- The same value represents the same real world object in all cases.
- The values can be compared, added, subtracted, and joined.
Note that you cannot operate on XML, BLOB, or CLOB data types
- Columns from the same domain always have the same defined domain name, which can be based on a user-defined data type.
- Columns from the same domain always have the same defined constraints.
- Columns from the same domain always have the same data type.
Particularly in the case of user-defined data types, the domain and the data type are often isomorphic.
Commercial relational database management systems relax these comparison restrictions to a greater or lesser degree. For example, you can compare INTEGER and DECIMAL values in commercially available systems because, the reasoning goes, both are numeric types.
Database management systems usually provide internal data type conversion routines to ensure such comparisons can be made. Programming languages generally refer to this as weak typing. The more strict domain comparison rules of the relational model, which are more strongly typed, do not permit these types of comparison to be made. UDTs are strongly typed and do not permit careless comparisons unless you write casts specifically to permit them. See "CREATE CAST" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for more information about creating cast functionality for UDTs.