16.20 - Normalizing Tables in Teradata Database - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

Normalizing Teradata Database tables is different than normalizing a relational database. In this context, to normalize a table is to coalesce 2 Period or derived Period values that meet or overlap. The result of a normalization operation on 2 input Period or derived Period values that meet or overlap is a third Period value that is the relational union of the 2 input Period values.

In Teradata Database, any reference to Period columns or a Period data type with respect to normalization operations applies equally to Period data type columns and derived Period columns. A derived Period identifies 2 DateTime columns in a table that constitute a period. A derived Period is not an actual data type.

Any Teradata Database table can be created as a normalized table. All you need to do to normalize a table is to identify a Period column in the table as the column on which to normalize. This Period column is known as the normalize column for a table. A normalized table does not have 2 rows with the same data values whose normalize column values meet or overlap. This means that if a new row is inserted or an existing row is updated into a normalized table, it is coalesced with all of the existing table rows whose values are the same as those of the new row and whose normalize column values either overlap or meet. All DML updates on a table that is defined as a normalized table normalize the new row with rows that previously existed in the table.

You can optionally specify a set of table columns that Teradata Database should ignore when determining the value equivalence of rows for normalization purposes.

For example, the following CREATE TABLE statement specifies the NORMALIZE option where the data values to be considered for normalization do not include values in the dept_id column.

     CREATE TABLE project (
       emp_id	       	INTEGER,
       project_name	 VARCHAR(20),
       dept_id	   	   INTEGER,
       duration	   	  PERIOD(DATE),
       NORMALIZE ALL BUT (dept_id) ON duration ON OVERLAPS);

For information about using DML statements to process and update normalized tables, see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .