Normalizing Database Tables | CREATE TABLE | Teradata Vantage - Normalizing Database Tables - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

Normalizing 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 Vantage, 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 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 the 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.