例 - Advanced SQL Engine - Teradata Database

/**********************************************************************
*
* PURPOSE:  
* ============
* Suggested queries to validate a Temporal RI on a child table.
*
* BACKGROUND:
* ============
* Multiple variants of RI can be defined with temporal tables.
*
* When a parent table is temporal, there can be multiple rows in the
* parent that contain a given parent key value with non-overlapping
* temporal column value. Presence of a temporal column causes rows
* to be tracked, so when a non-parent key column is modified
* in the parent, the system tracks history. This occurs in TransactionTime
* dimension. The same occurs for ValidTime dimension when modifications
* are made using CURRENT or SEQUENCED VALIDTIME qualifiers.
*
* Note this example:
*
* create multiset table tpar (
*  c1 int, 
*  c2 int,
*  pk int not null sequenced validtime unique
*  vt period(timestamp with time zone) as validtime not null,
*  tt period(timestamp with time zone) not null as transactiontime
* )
* primary index(c1)
*
* Assume row in parent is 
*  (1, 1, 1, (t1-until_changed),(t1-until_closed))
* on t3, assume c2 got incremented by 1.
*
* Rows in the parent table then are :
*  (1, 1, 1, (t1-until_changed),(t1-t3))
*  (1, 1, 1, (t1-t3),(t3-until_closed))
*  (1, 2, 1, (t3-until_changed),(t3-until_closed))
*
* As noted, the pk value 1 is valid from t1-until_changed but this value
* is split in the two open rows above. Therefore, when validating whether a 
* given child row's time value is present in the parent's VT or TT value,
* the rows must be normalized before comparing.
*
* The queries below use the TD_NORMALIZE_MEET table function.
*
* DESCRIPTION:
* ============
*
* For each of the variant of RI, a separate query (or set of queries)
* is provided to validate the RI constraint. If query returns rows, then
* it implies that that the child table does not satisfy the constraints.
* Please follow the instructions below to clean up the child table
* FK refers to the columns (excluding the temporal columns or the
* TRC column if the child table does not support ValidTime) on which the
* RI constraint is defined. 
*
*              1) If FK is not present in the parent, then either update 
*                 the child to point to correct FK column value or delete 
*                 the row in the child
*              2) If FK is present in the parent, but the child's 
*                 date/time column (date/timestamp or VT or TT column)
*                 does not satisfy the corresponding temporal RI
*                 property in the parent's VT or TT, then fix the
*                 portions of the child column to satisfy the RI
*                 constraint or delete the child row
*               3) If parent and child table's VT column's data types are 
*                 different(ex Period(date), period(timestamp) etc.),
*                 Then use the cast function in the queries
*                 appropriately.
*                 For Ex: In the below queries the contains condition
*                 will be wriiten by using cast function as follows.
*                 (cast(tblf.vtp as period(date)) contains t2chld.vt)
*
*
*
*
*
* GLOSSARY
* =========
* NT  - Nontemporal
* VT  - Validtime
* TT  - Transactiontime
* BiT - BiTemporal
* TRC - Temporal Relationship Constraints
* CRI - Current Referential Integrity
* SRI - Sequenced Referential Integrity
* NRI - Nonsequenced Referential Integrity
*
*
**********************************************************************/
/**********************************************************************/
/************ NT child -- NT parent ***********************************/
/**********************************************************************/
/* RI definition.
   foreign key(fk) references with no check option tpar(pk)
*/
SELECT  fk 
FROM  tchld 
WHERE  fk NOT IN (
SELECT  pk 
FROM  tpar) 
  AND  
fk IS NOT NULL;
/**********************************************************************/
/************ NT child -- VT parent (TRC)******************************/
/* The parent's VT must contain the child dt to unitl_changed *********/
/* when pk-fk match                                                   */
/**********************************************************************/
/* RI definition.
   foreign key(fk,dt) references with no check option tpar(pk,vt)
*/   
 drop table nm_par;
 CREATE MULTISET TABLE nm_par ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      pk INTEGER,
      vtp PERIOD(DATE) as VALIDTIME)
PRIMARY INDEX ( pk );
INSERT INTO nm_par (pk,vtp) 
WITH  SUBTBL(x,d ) AS 
(NONSEQUENCED VALIDTIME 
SELECT  pk,vt  
FROM  tpar)
SELECT  *
FROM TABLE ( 
      TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )
      RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
      HASH BY x LOCAL 
      ORDER BY x,d ) AS TBLF(pk,vtp); 
SELECT  * 
FROM  (
CURRENT VALIDTIME SELECT  DISTINCT fk,dt 
FROM  nm_par LEFT OUTER JOIN tchld 
ON  fk=pk WHERE
  NOT( vtp CONTAINS period(dt, UNTIL_CHANGED))
  AND vtp IS NOT NULL
UNION
 
SELECT * FROM (NONSEQUENCED VALIDTIME 
SELECT  fk,tchld.dt 
FROM  tchld 
WHERE  fk NOT IN (
SELECT  pk 
FROM  tpar) 
  AND  
fk IS NOT NULL)DT)dt(x,y);
/**********************************************************************/
/************ NT child -- TT parent ***********************************/
/******* child row must exist in the open rows of parent **************/
/**********************************************************************/
/* RI definition.
   foreign key(fk) references with no check option tpar(pk)
*/
CURRENT TRANSACTIONTIME 
SELECT  fk 
FROM  tchld 
WHERE  fk NOT IN 
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL ;
/**********************************************************************/
/************ NT child -- BiT parent (TRC) ****************************/
/* The parent's VT must contain the child dt to unitl_changed         */
/* when pk-fk match                                                   */
/**********************************************************************/
/* RI definition.
   foreign key(fk,dt) references with no check option tpar(pk,vt)
*/
drop table nm_par;
 CREATE MULTISET TABLE nm_par ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      pk INTEGER,
      vtp PERIOD(DATE) as VALIDTIME)
PRIMARY INDEX ( pk );
INSERT INTO nm_par (pk,vtp) 
WITH  SUBTBL(x,d ) AS 
(NONSEQUENCED VALIDTIME
AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt  
FROM  tpar)
SELECT  *
FROM TABLE ( 
      TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )
      RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
      HASH BY x LOCAL 
      ORDER BY x,d ) AS TBLF(pk,vtp); 
SELECT  * 
FROM  (
CURRENT VALIDTIME  SELECT  DISTINCT fk,dt 
FROM  nm_par LEFT OUTER JOIN tchld 
ON  fk=pk WHERE
  NOT( vtp CONTAINS period(dt, UNTIL_CHANGED))
  AND vtp IS NOT NULL
UNION
 
SELECT * FROM (NONSEQUENCED VALIDTIME 
AND  CURRENT TRANSACTIONTIME
SELECT  fk,tchld.dt 
FROM  tchld 
WHERE  fk NOT IN (
SELECT  pk 
FROM  tpar) 
  AND  
fk IS NOT NULL)DT)dt(x,y);
/**********************************************************************/
/************ VT child -- NT parent ***********************************/
/************ NRI in VT dimension *************************************/
/**********************************************************************/
/* RI definition.
   nonsequenced validtime foreign key(fk) references with no check
   option tpar(pk)
*/
NONSEQUENCED VALIDTIME 
SELECT  fk,vt 
FROM  tchld 
WHERE  fk NOT IN 
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL ;
/**********************************************************************/
/************ VT child -- VT parent ***********************************/
/**********************************************************************/
/* CRI in VT dimension */
/* RI definition.
   current validtime foreign key(fk) references with no check option
   tpar(pk)
*/
 
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
SELECT  pk,vt  
FROM  tpar 
WHERE  END( tpar.vt ) >= temporal_date ) 
SELECT  x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' )  
FROM  (
NONSEQUENCED VALIDTIME 
SELECT  fk ( title 'Foreign Key Column'),vt ( title ' ValidTime Column' ) 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )
    
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS period(
CASE  WHEN 
begin  (tchld.vt) > temporal_date THEN 
begin  (tchld.vt) 
ELSE  temporal_date 
END  , 
END  (tchld.vt))) 
  AND  END( tchld.vt ) >= temporal_date
  AND  END( tblf.vtp ) >= temporal_date 
  AND  tchld.vt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN 
(   
SELECT  pk 
FROM  tpar 
WHERE    
END  ( tpar.vt ) >= temporal_date
) 
  AND  fk IS NOT NULL
 
  AND  END( tchld.vt ) >= temporal_date)DT)dt(x,y); 
 
 /* SRI in VT dimension */
 /* RI definition.
    sequenced validtime foreign key(fk) references with no check option
    tpar(pk)
*/
 
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
SELECT  pk,vt  
FROM  tpar ) 
SELECT  x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' )  
FROM  (
NONSEQUENCED VALIDTIME 
SELECT  fk,tchld.vt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )    
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS tchld.vt) 
  AND  tchld.vt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt(x,y);
/**********************************************************************/
/************ VT child -- BiT parent **********************************/
/**********************************************************************/
/* CRI in VT dimension */
/* RI definition.
   current validtime foreign key(fk) references with no check option
   tpar(pk)
*/
 
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt  
FROM  tpar 
WHERE  END( tpar.vt ) >= temporal_date ) 
SELECT  x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' ) 
FROM  (
NONSEQUENCED VALIDTIME  
SELECT  fk, tchld.vt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )   
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS period(
CASE  WHEN 
begin  (tchld.vt) > temporal_date THEN 
begin  (tchld.vt) 
ELSE  temporal_date 
END  , 
END  (tchld.vt))) 
  AND  END( tchld.vt ) >= temporal_date
  AND  END( tblf.vtp ) >= temporal_date 
  AND  tchld.vt IS NOT NULL 
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN 
(    
SELECT  pk 
FROM  tpar 
WHERE   
END  ( tpar.vt ) >= temporal_date
) 
  AND  fk IS NOT NULL
  AND  END( tchld.vt ) >= temporal_date)DT)dt(x,y);  
 
 /* SRI in VT dimension */
 /* RI definition.
    sequenced validtime foreign key(fk) references with no check option
    tpar(pk)
*/
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt  
FROM  tpar ) 
SELECT  x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' ) 
FROM  (
NONSEQUENCED VALIDTIME 
SELECT  fk,tchld.vt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )  
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS tchld.vt) 
  AND  tchld.vt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt(x,y);
/**********************************************************************/
/************ TT child -- NT parent ***********************************/
/************ NRI in TT dimension *************************************/
/**********************************************************************/
/* RI definition.
   nonsequenced transactiontime foreign key(fk) references 
   with no check option tpar(pk)
*/
NONSEQUENCED TRANSACTIONTIME
SELECT fk ( title 'Foreign Key Column'),tt ( title 'TransactionTime Column' )
FROM  tchld 
WHERE  fk NOT IN 
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL;
/**********************************************************************/
/************ TT child -- VT parent (TRC) *****************************/
/**** NONSEQUENCED TRANSACTIONTIME RI with TRC on parent't VT *********/
/* The parent's VT must contain the child dt to until_changed         */
/* when pk-fk match                                                   */
/**********************************************************************/
/* RI definition.
   nonsequenced transactiontime foreign key(fk,dt) references 
   with no check option tpar(pk,vt)
*/
 
 drop table nm_par;
 CREATE MULTISET TABLE nm_par ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      pk INTEGER,
      vtp PERIOD(DATE) as VALIDTIME)
PRIMARY INDEX ( pk );
INSERT INTO nm_par (pk,vtp) 
WITH  SUBTBL(x,d ) AS 
(NONSEQUENCED VALIDTIME 
SELECT  pk,vt  
FROM  tpar)
SELECT  *
FROM TABLE ( 
      TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )
      RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
      HASH BY x LOCAL 
      ORDER BY x,d ) AS TBLF(pk,vtp); 
SELECT  * 
FROM  (
CURRENT VALIDTIME SELECT  DISTINCT fk,dt 
FROM  nm_par LEFT OUTER JOIN tchld 
ON  fk=pk WHERE
  NOT( vtp CONTAINS period(dt, UNTIL_CHANGED))
  AND vtp IS NOT NULL
UNION
 
SELECT * FROM (NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,tchld.dt 
FROM  tchld 
WHERE  fk NOT IN (
SELECT  pk 
FROM  tpar) 
  AND  
fk IS NOT NULL)DT)dt(x,y);
/**********************************************************************/
/************ TT child -- TT parent ***********************************/
/**********************************************************************/
/* CRI in TT dimension */
/* RI definition.
   current transactiontime foreign key(fk) references 
   with no check option tpar(pk)
*/
/* CURRENT rows in child should have equivalent PK in parent's open rows
   for each matching fk-pk rows.
*/   
CURRENT TRANSACTIONTIME 
SELECT fk ( title 'Foreign Key Column'),tt ( title 'TransactionTime Column' )
FROM  tchld 
WHERE  fk NOT IN (
SELECT  pk 
FROM  tpar) 
  AND  
fk IS NOT NULL;
 
 /* SRI in TT dimension */
 /* RI definition.
    sequenced transactiontime foreign key(fk) references 
    with no check option tpar(pk)
*/
 /* child row's TT must be contained in the normalized TT of the parent
    for each of the matching fk-pk rows.
 */
 
WITH  SUBTBL(x,d ) AS
  (NONSEQUENCED TRANSACTIONTIME 
SELECT  pk,tt  
FROM  tpar ) 
SELECT  * 
FROM  (
NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,tchld.tt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )
    
RETURNS  (pk1 INTEGER, ttp1 PERIOD(timestamp 
with  time zone))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,ttp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.ttp CONTAINS tchld.tt) 
  AND  tchld.tt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED TRANSACTIONTIME
SELECT  fk,tchld.tt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt(x,y);
/**********************************************************************/
/************ TT child -- BiT parent(TRC) *****************************/
/**********************************************************************/
 /* CURRENT TRANSACTIONTIME RI AND TRC on parent */
 /* RI definition.
    current transactiontime foreign key(fk,dt) references 
    with no check option tpar(pk,vt)
*/
 drop table nm_par;
 CREATE MULTISET TABLE nm_par ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      pk INTEGER,
      vtp PERIOD(DATE) as VALIDTIME)
PRIMARY INDEX ( pk );
INSERT INTO nm_par (pk,vtp) 
WITH  SUBTBL(x,d ) AS 
(NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt  
FROM  tpar)
SELECT  *
FROM TABLE ( 
      TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )
      RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
      HASH BY x LOCAL 
      ORDER BY x,d ) AS TBLF(pk,vtp); 
SELECT  * 
FROM  (
CURRENT VALIDTIME SELECT  DISTINCT fk,dt 
FROM  nm_par LEFT OUTER JOIN tchld 
ON  fk=pk WHERE
  NOT( vtp CONTAINS period(dt, UNTIL_CHANGED))
  AND vtp IS NOT NULL
UNION
 
SELECT * FROM(NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  fk,tchld.dt 
FROM  tchld 
WHERE  fk NOT IN (
SELECT  pk 
FROM  tpar) 
  AND  
fk IS NOT NULL)DT)dt(x,y);
/* SEQUENCED TRANSACTIONTIME RI AND TRC on parent */
/* RI definition.
   sequenced transactiontime foreign key(fk,dt) references 
   with no check option tpar(pk,vt)
*/
/* child row's TT must be contained in the normalized TT of the parent
   for each of the matching fk-pk rows */
/* normalize the VT on open rows in TT AND PK column - 
   with this result, normalize in TT diemntion on matching PK AND VT 
*/
drop table tmptbl;
CREATE MULTISET TABLE tmptbl
AS (
WITH  SUBTBL(x,d,t) AS 
  (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt, tt  
FROM  tpar )
SELECT  * 
FROM  ( TABLE (
 TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.t),
    SUBTBL.d )   
RETURNS  (pk1 INTEGER, ttp1 PERIOD(timestamp 
with  time zone), vtp1 period(date))
     HASH BY x, t LOCAL 
ORDER  BY x,t,d)AS TBLF(pk,tt, vt))
) with data ;
drop table tmptbl1;
CREATE MULTISET TABLE tmptbl1
AS (
NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  pk, vt, tt 
FROM  tpar   
WHERE  END(tt) is not until_closed   
UNION    
SELECT   pk, vt, tt 
FROM  tmptbl 
) with data ;
WITH  SUBTBL(x,v, d) AS 
(
 SEL * FROM tmptbl1
)
SELECT  * 
FROM  (
NONSEQUENCED TRANSACTIONTIME 
SELECT  fk, tchld.dt, tchld.tt 
FROM   TABLE (
 TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.v),
    SUBTBL.d )    
RETURNS  (pk1 INTEGER,  vtp1 period(date), ttp1 PERIOD(timestamp 
with  time zone))
     HASH BY x, v LOCAL 
ORDER  BY x,v,d)AS TBLF(pk,vtp, ttp) 
, tchld 
WHERE  pk=fk 
  AND  
( (NOT (tblf.ttp CONTAINS tchld.tt) )
  OR  (NOT ( tblf.vtp CONTAINS period(tchld.dt, UNTIL_CHANGED) ) )
)
UNION  
SELECT * FROM (NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,tchld.dt, tchld.tt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt order by 1, 2 ;
/**********************************************************************/
/************ BiT child -- NT parent **********************************/
/**********************************************************************/
/* NRI in both the dimensions */
/* RI definition.
   nonsequenced validtime and nonsequenced transactiontime foreign
   key(fk) references with no check option tpar(pk)
*/ 
 
NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,vt,tt 
FROM  tchld 
WHERE  fk NOT IN 
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL ;
/**********************************************************************/
/************ BiT child -- VT parent **********************************/
/**********************************************************************/
/* CRI in VT dimension NRI in TT dimension */ 
/* RI definition.
   current validtime and nonsequenced transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/ 
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
SELECT  pk,vt  
FROM  tpar 
WHERE  END( tpar.vt ) >= temporal_date ) 
SELECT  * 
FROM  (
NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  fk, tchld.vt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )    
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS period(
CASE  WHEN 
begin  (tchld.vt) > temporal_date THEN 
begin  (tchld.vt) 
ELSE  temporal_date 
END  , 
END  (tchld.vt))) 
  AND  END( tchld.vt ) >= temporal_date
  AND  END( tblf.vtp ) >= temporal_date 
  AND  tchld.vt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN 
(  
SELECT  pk 
FROM  tpar 
WHERE  
END  ( tpar.vt ) >= temporal_date
) 
  AND  fk IS NOT NULL
  AND  END( tchld.vt ) >= temporal_date)DT)dt(x,y);                 
/* SRI in VT dimension NRI in TT dimension */
/* RI definition.
   sequenced validtime and nonsequenced transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/
  
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
SELECT  pk,vt  
FROM  tpar ) 
SELECT  * 
FROM  (
NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,tchld.vt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )
    
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS tchld.vt) 
  AND  tchld.vt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt(x,y) order by 1, 2 ;
/**********************************************************************/
/************ BiT child -- TT parent **********************************/
/**********************************************************************/
/* NRI in VT dimension CRI in TT dimension */
/* RI definition.
   nonsequenced validtime and current transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/  
/* CURRENT rows in child should have equivalent PK in parent's open rows
   for each matching fk-pk rows */
NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  fk,vt,tt 
FROM  tchld 
WHERE  fk NOT IN (
SELECT  pk 
FROM  tpar) 
  AND  
fk IS NOT NULL;  
   
/* NRI in VT dimension SRI in TT dimension */
/* RI definition.
   nonsequenced validtime and sequenced transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/ 
/* child row's TT must be contained in the normalized TT of the parent
   for each of the matching fk-pk rows */
WITH  SUBTBL(x,d ) AS
  (NONSEQUENCED TRANSACTIONTIME 
SELECT  pk,tt 
FROM  tpar ) 
SELECT  * 
FROM  (
NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  DISTINCT fk,tchld.tt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )   
RETURNS  (pk1 INTEGER, ttp1 PERIOD(timestamp 
with  time zone))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,ttp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.ttp CONTAINS tchld.tt) 
  AND  tchld.tt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME
SELECT  fk,tchld.tt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt(x,y);
/**********************************************************************/
/************ BiT child -- BiT parent *********************************/
/**********************************************************************/
/* CRI in VT dimension, CRI  TT dimension */
/* RI definition.
   current validtime and current transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/
 
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt  
FROM  tpar 
WHERE  END( tpar.vt ) >= temporal_date ) 
SELECT  * 
FROM  (
NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  fk, tchld.vt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )   
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS period(
CASE  WHEN 
begin  (tchld.vt) > temporal_date THEN 
begin  (tchld.vt) 
ELSE  temporal_date 
END  , 
END  (tchld.vt))) 
  AND  END( tchld.vt ) >= temporal_date
  AND  END( tblf.vtp ) >= temporal_date 
  AND  tchld.vt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN 
(   
SELECT  pk 
FROM  tpar 
WHERE    
END  ( tpar.vt ) >= temporal_date
) 
  AND  fk IS NOT NULL
  AND  END( tchld.vt ) >= temporal_date)DT)dt(x,y); 
 
 /* SRI in VT dimension, CRI  TT dimension */
 /* RI definition.
   sequenced validtime and current transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/
WITH  SUBTBL(x,d ) AS 
  (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME
SELECT  pk,vt  
FROM  tpar ) 
SELECT  * 
FROM  (
NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  fk,tchld.vt 
FROM   TABLE (
   TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d )    
RETURNS  (pk1 INTEGER, vtp1 PERIOD(DATE))
     HASH BY x LOCAL 
ORDER  BY x,d) AS TBLF(pk,vtp) 
, tchld 
WHERE  pk=fk 
  AND  
NOT (tblf.vtp CONTAINS tchld.vt) 
  AND  tchld.vt IS NOT NULL
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME
SELECT  fk,tchld.vt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt(x,y);
/*******************************************
 * CRI in VT dimension, SRI  TT dimension 
********************************************/
/* RI definition.
   current validtime and sequenced transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/
drop table tmptbl;
CREATE MULTISET TABLE tmptbl
AS (
WITH  SUBTBL(x,d,t) AS 
  (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt, tt  
FROM  tpar )
SELECT  * 
FROM  ( TABLE (
 TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.t),
    SUBTBL.d )   
RETURNS  (pk1 INTEGER, ttp1 PERIOD(timestamp 
with  time zone), vtp1 period(date))
     HASH BY x, t LOCAL 
ORDER  BY x,t,d)AS TBLF(pk,tt, vt))
) with data ;
drop table tmptbl1;
CREATE MULTISET TABLE tmptbl1
AS (
NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  pk, vt, tt 
FROM  tpar   
WHERE  END(tt) is not until_closed   
UNION    
SELECT   pk, vt, tt 
FROM  tmptbl 
) with data ;
WITH  SUBTBL(x,v, d) AS 
(
 SEL * FROM tmptbl1
)
SELECT  * 
FROM  (
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME 
SELECT  DISTINCT fk, tchld.vt, tchld.tt 
FROM   TABLE (
 TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.v),
    SUBTBL.d )    
RETURNS  (pk1 INTEGER,  vtp1 period(date), ttp1 PERIOD(timestamp 
with  time zone))
     HASH BY x, v LOCAL 
ORDER  BY x,v,d)AS TBLF(pk,vtp, ttp) 
, tchld 
WHERE  pk=fk 
  AND  
( (NOT (tblf.ttp CONTAINS tchld.tt) )
  OR  (NOT ( tblf.vtp CONTAINS PERIOD(
CASE  WHEN 
begin  (tchld.vt) > temporal_date THEN 
begin  (tchld.vt) 
ELSE  temporal_date 
END  , 
END  (tchld.vt)) ) )
)
AND END(tchld.vt) >= TEMPORAL_DATE
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,tchld.vt, tchld.tt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt order by 1, 2 ;
  
/*******************************************
 * SRI in VT dimension, SRI  TT dimension 
********************************************/
/* RI definition.
   sequenced validtime and sequenced transactiontime foreign key(fk) 
   references with no check option tpar(pk)
*/
drop table tmptbl;
CREATE MULTISET TABLE tmptbl
AS (
WITH  SUBTBL(x,d,t) AS 
  (NONSEQUENCED VALIDTIME 
  AND  CURRENT TRANSACTIONTIME 
SELECT  pk,vt, tt  
FROM  tpar )
SELECT  * 
FROM  ( TABLE (
 TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.t),
    SUBTBL.d )   
RETURNS  (pk1 INTEGER, ttp1 PERIOD(timestamp 
with  time zone), vtp1 period(date))
     HASH BY x, t LOCAL 
ORDER  BY x,t,d)AS TBLF(pk,tt, vt))
) with data ;
drop table tmptbl1;
CREATE MULTISET TABLE tmptbl1
AS (
NONSEQUENCED VALIDTIME 
  AND  NONSEQUENCED TRANSACTIONTIME 
SELECT  pk, vt, tt 
FROM  tpar   
WHERE  END(tt) is not until_closed   
UNION    
SELECT   pk, vt, tt 
FROM  tmptbl 
) with data ;
WITH  SUBTBL(x,v, d) AS 
(
 SEL * FROM tmptbl1
)
SELECT  * 
FROM  (
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME 
SELECT  DISTINCT fk, tchld.vt, tchld.tt 
FROM   TABLE (
 TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.v),
    SUBTBL.d )    
RETURNS  (pk1 INTEGER,  vtp1 period(date), ttp1 PERIOD(timestamp 
with  time zone))
     HASH BY x, v LOCAL 
ORDER  BY x,v,d)AS TBLF(pk,vtp, ttp) 
, tchld 
WHERE  pk=fk 
  AND  
( (NOT (tblf.ttp CONTAINS tchld.tt) )
  OR  (NOT ( tblf.vtp CONTAINS tchld.vt ) )
)
UNION  
SELECT * FROM (NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME 
SELECT  fk,tchld.vt, tchld.tt 
FROM  tchld 
WHERE  fk NOT IN
( 
SELECT  pk 
FROM  tpar ) 
  AND  fk IS NOT NULL)DT)dt order by 1, 2 ;