Appendix B. Code generated by StP

Code generated by StP for SQL2 ER model (figs 9, 10, 11).


CREATE SCHEMA
AUTHORIZATION SQL2_ER

CREATE TABLE DOMAINS
     (
     DOMAIN_CATALOG   IDENTIFIER ,
     DOMAIN_NAME      IDENTIFIER ,
     DOMAIN_SCHEMA    IDENTIFIER ,
     CONSTRAINT_NAME  IDENTIFIER ,
     DOMAIN_DEFAULT   CHAR_DATA ,
     DOM_FK_CONSTRAINT_CATALOG      IDENTIFIER ,    Foreign Key from CHECK_CONSTRAINTS
     DOM_FK_CONSTRAINT_NAME         IDENTIFIER ,    Foreign Key from CHECK_CONSTRAINTS
     DOM_FK_CONSTRAINT_SCHEMA       IDENTIFIER ,    Foreign Key from CHECK_CONSTRAINTS
     DOMS_IN_CATALOG_CATALOG_NAME   IDENTIFIER ,    Foreign Key from SCHEMATA
     DOMS_IN_CATALOG_SCHEMA_NAME    IDENTIFIER ,    Foreign Key from SCHEMATA
     PRIMARY KEY (DOMAIN_CATALOG, DOMAIN_NAME, DOMAIN_SCHEMA)
     )

CREATE TABLE TRANSLATIONS
     (
     TRANSLATION_CATALOG   IDENTIFIER ,
     TRANSLATION_NAME      IDENTIFIER ,
     TRANSLATION_SCHEMA    IDENTIFIER ,
     SRC_CHARSET_CATALOG   IDENTIFIER ,
     SRC_CHARSET_NAME      IDENTIFIER ,
     SRC_CHARSET_SCHEMA    IDENTIFIER ,
     TGT_CHARSET_CATALOG   IDENTIFIER ,
     TGT_CHARSET_NAME      IDENTIFIER ,
     TGT_CHARSET_SCHEMA    IDENTIFIER ,
     SRC_CH_SET_CHARSET_CATALOG      IDENTIFIER ,    Foreign Key from CHARACTER_SETS
     SRC_CH_SET_CHARSET_NAME         IDENTIFIER ,    Foreign Key from CHARACTER_SETS
     SRC_CH_SET_CHARSET_SCHEMA       IDENTIFIER ,    Foreign Key from CHARACTER_SETS
     TGT_CH_SET_CHARSET_CATALOG      IDENTIFIER ,    Foreign Key from CHARACTER_SETS
     TGT_CH_SET_CHARSET_NAME         IDENTIFIER ,    Foreign Key from CHARACTER_SETS
     TGT_CH_SET_CHARSET_SCHEMA       IDENTIFIER ,    Foreign Key from CHARACTER_SETS
     TRANS_FK_CATALOG_NAME           IDENTIFIER ,    Foreign Key from SCHEMATA
     TRANS_FK_SCHEMA_NAME            IDENTIFIER ,    Foreign Key from SCHEMATA
     PRIMARY KEY (TRANSLATION_CATALOG, TRANSLATION_NAME, TRANSLATION_SCHEMA)
     )

CREATE TABLE CHARACTER_SETS
     (
     CHARSET_CATALOG       IDENTIFIER ,
     CHARSET_NAME          IDENTIFIER ,
     CHARSET_SCHEMA        IDENTIFIER ,
     DEF_COLLATE_CATALOG   IDENTIFIER ,
     DEF_COLLATE_NAME      IDENTIFIER ,
     DEF_COLLATE_SCHEMA    IDENTIFIER ,
     FORM_OF_USE           IDENTIFIER ,
     NUMBER_OF_CHARS       CARDINAL ,
     CHAR_SETS_CATALOG_NAME       IDENTIFIER ,      Foreign Key from SCHEMATA
     CHAR_SETS_SCHEMA_NAME        IDENTIFIER ,      Foreign Key from SCHEMATA
     DEF_COLLS_COLLATION_CATALOG  IDENTIFIER ,      Foreign Key from COLLATIONS
     DEF_COLLS_COLLATION_NAME     IDENTIFIER ,      Foreign Key from COLLATIONS
     DEF_COLLS_COLLATION_SCHEMA   IDENTIFIER ,      Foreign Key from COLLATIONS
     PRIMARY KEY (CHARSET_CATALOG, CHARSET_NAME, CHARSET_SCHEMA)
     )

CREATE TABLE COLLATIONS
     (
     COLLATION_CATALOG      IDENTIFIER ,
     COLLATION_NAME         IDENTIFIER ,
     COLLATION_SCHEMA       IDENTIFIER ,
     CHARSET_CATALOG        IDENTIFIER ,
     CHARSET_NAME           IDENTIFIER ,
     CHARSET_SCHEMA         IDENTIFIER ,
     CHARSET_CSN_CHARSET_CATALOG      IDENTIFIER ,      Foreign Key from CHARACTER_SETS
     CHARSET_CSN_CHARSET_NAME         IDENTIFIER ,      Foreign Key from CHARACTER_SETS
     CHARSET_CSN_CHARSET_SCHEMA       IDENTIFIER ,      Foreign Key from CHARACTER_SETS
     COLLS_CATALOG_NAME      IDENTIFIER ,      Foreign Key from SCHEMATA
     COLLS_SCHEMA_NAME       IDENTIFIER ,      Foreign Key from SCHEMATA
     PRIMARY KEY (COLLATION_CATALOG, COLLATION_NAME, COLLATION_SCHEMA)
     )

CREATE TABLE DATA_TYPE_DESCRIPTOR
     (
     COLUMN_NAME     IDENTIFIER ,
     DTD_CATALOG     IDENTIFIER ,
     DTD_SCHEMA      IDENTIFIER ,
     TABLE_OR_DOMAIN_NAME       IDENTIFIER ,
     CHAR_MAX_LENGTH       CARDINAL ,
     CHAR_OCTET_LENGTH     CARDINAL ,
     COLLATION_CATALOG     IDENTIFIER ,
     COLLATION_NAME        IDENTIFIER ,
     COLLATION_SCHEMA      IDENTIFIER ,
     DATA_TYPE             CHAR_DATA ,
     DATETIME_PRECISION    CARDINAL ,
     NUMERIC_PRECISION     CARDINAL ,
     NUMERIC_PREC_RADIX    CARDINAL ,
     NUMERIC_SCALE         CARDINAL ,
     DTD_COLLATION_CATALOG      IDENTIFIER     Foreign Key from COLLATIONS
     DTD_COLLATION_NAME    IDENTIFIER     Foreign Key from COLLATIONS
     DTD_COLLATION_SCHEMA  IDENTIFIER     Foreign Key from COLLATIONS
     PRIMARY KEY (COLUMN_NAME, DTD_CATALOG, DTD_SCHEMA, TABLE_OR_DOMAIN_NAME)
     )

CREATE TABLE CHECK_CONSTRAINTS
     (
     CONSTRAINT_CATALOG    IDENTIFIER ,
     CONSTRAINT_NAME       IDENTIFIER ,
     CONSTRAINT_SCHEMA     IDENTIFIER ,
     CHECK_CLAUSE          CHAR_DATA ,
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA)
     )

CREATE TABLE_SCHEMATA
     (
     CATALOG_NAME          IDENTIFIER ,
     SCHEMA_NAME           IDENTIFIER ,
     DEF_TIME_DISP_TYPE    CHAR_DATA ,
     SCHEMA_OWNER          IDENTIFIER ,
     TIME_DISP_CATALOG     IDENTIFIER ,
     TIME_DISP_SCHEMA      IDENTIFIER ,
     TIME_DISP_TABLE       IDENTIFIER ,
     OWN_USER_NAME         IDENTIFIER ,    Foreign Key from USERS
     TIME_DISP_CSN_TABLE_CATALOG     IDENTIFIER ,      Foreign Key from TABLES
     TIME_DISP_CSN_TABLE_NAME        IDENTIFIER ,      Foreign Key from TABLES
     TIME_DISP_CSN_TABLE_SCHEMA      IDENTIFIER ,      Foreign Key from TABLES
     PRIMARY KEY (CATALOG_NAME, SCHEMA_NAME)
     )

CREATE TABLE VIEWS
     (
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     CHECK_OPTION     CHAR_DATA ,
     UPDATABLE        CHAR_DATA ,
     VIEW_DEFINITION       CHAR_DATA ,
     V_FK_CATALOG_NAME     IDENTIFIER ,    Foreign Key from SCHEMATA
     V_FK_SCHEMA_NAME      IDENTIFIER ,    Foreign Key from SCHEMATA
     PRIMARY KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA)
     )

CREATE TABLE USERS
     (
     USER_NAME         IDENTIFIER ,
     PRIMARY KEY (USER_NAME)
     )

CREATE TABLE DOMAIN_PRIVILEGES
     (
     DOMAIN_CATALOG    IDENTIFIER ,
     DOMAIN_NAME       IDENTIFIER ,
     DOMAIN_SCHEMA     IDENTIFIER ,
     GRANTEE     IDENTIFIER ,
     GRANTOR     IDENTIFIER ,
     PRIVILEGE   CHAR_DATA ,
     GRANTABLE   CHAR_DATA
     DOM_CSN_DOMAIN_CATALOG     IDENTIFIER ,   Foreign Key from DOMAINS
     DOM_CSN_DOMAIN_NAME        IDENTIFIER ,   Foreign Key from DOMAINS
     DOM_CSN_DOMAIN_SCHEMA      IDENTIFIER ,   Foreign Key from DOMAINS
     DP_GRANTEE_USER_NAME       IDENTIFIER ,   Foreign Key from USERS
     DP_GRANTOR_USER_NAME       IDENTIFIER ,   Foreign Key from USERS
     PRIMARY KEY (DOMAIN_CATALOG, DOMAIN_NAME, DOMAIN_SCHEMA, GRANTEE, 
                  GRANTOR, PRIVILEGE)
     )

CREATE TABLE ASSERTIONS
     (
     CONSTRAINT CATALOG      IDENTIFIER ,
     CONSTRAINT_NAME         IDENTIFIER ,
     CONSTRAINT_SCHEMA       IDENTIFIER ,
     ASS_CONSTRAINT_CATALOG  IDENTIFIER ,     Foreign Key from CHECK_CONSTRAINTS
     ASS_CONSTRAINT_NAME     IDENTIFIER ,     Foreign Key from CHECK_CONSTRAINTS
     ASS_CONSTRAINT_SCHEMA   IDENTIFIER ,     Foreign Key from CHECK_CONSTRAINTS
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA)
     )

CREATE TABLE CHECK_TABLE_USAGE
     (
     CONSTRAINT_CATALOG     IDENTIFIER ,
     CONSTRAINT_NAME        IDENTIFIER ,
     CONSTRAINT_SCHEMA      IDENTIFIER ,
     TABLE_CATALOG     IDENTIFIER ,
     TABLE_NAME        IDENTIFIER ,
     TABLE_SCHEMA      IDENTIFIER ,
     CTU_FK_CONSTRAINT_CATALOG    IDENTIFIER ,     Foreign Key from CHECK_CONSTRAINTS
     CTU_FK_CONSTRAINT_NAME       IDENTIFIER ,     Foreign Key from CHECK_CONSTRAINTS
     CTU_FK_CONSTRAINT_SCHEMA     IDENTIFIER ,     Foreign Key from CHECK_CONSTRAINTS
     CTU_T_FK_TABLE_CATALOG       IDENTIFIER ,     Foreign Key from TABLES
     CTU_T_FK_TABLE_NAME          IDENTIFIER ,     Foreign Key from TABLES
     CTU_T_FK_TABLE_SCHEMA        IDENTIFIER ,     Foreign Key from TABLES
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, 
                  TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA)
     )

CREATE TABLE CHECK_COLUMN_USAGE
     (
     COLUMN_NAME            IDENTIFIER ,
     CONSTRAINT_CATALOG     IDENTIFIER ,
     CONSTRAINT_NAME        IDENTIFIER ,
     CONSTRAINT_SCHEMA      IDENTIFIER ,
     TABLE_CATALOG     IDENTIFIER ,
     TABLE_NAME        IDENTIFIER ,
     TABLE_SCHEMA      IDENTIFIER ,
     CCU_C_FK_COLUMN_NAME       IDENTIFIER ,    Foreign Key from COLUMNS
     CCU_C_FK_TABLE_CATALOG     IDENTIFIER ,    Foreign Key from COLUMNS
     CCU_C_FK_TABLE_NAME        IDENTIFIER ,    Foreign Key from COLUMNS
     CCU_C_FK_TABLE_SCHEMA      IDENTIFIER ,    Foreign Key from COLUMNS
     CCU_FK_CONSTRAINT_CATALOG  IDENTIFIER ,    Foreign Key from CHECK_CONSTRAINTS
     CCU_FK_CONSTRAINT_NAME     IDENTIFIER ,    Foreign Key from CHECK_CONSTRAINTS
     CCU_FK_CONSTRAINT_SCHEMA   IDENTIFIER ,    Foreign Key from CHECK_CONSTRAINTS
     PRIMARY KEY (COLUMN_NAME, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, 
                  TABLE_CATALOG, TABLE TNAME, TABLE_SCHEMA)
     )

CREATE TABLE TABLE_PRIVILEGES
     (
     GRANTEE    IDENTIFIER ,
     GRANTOR    IDENTIFIER ,
     PRIVILEGE  CHAR_DATA ,
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     GRANTABLE        CHAR_DATA ,
     TP_FK_TABLE_CATALOG   IDENTIFIER ,    Foreign Key from TABLES
     TP_FK_TABLE_NAME      IDENTIFIER ,    Foreign Key from TABLES
     TP_FK_TABLE_SCHEMA    IDENTIFIER ,    Foreign Key from TABLES
     TP_GRANTEE_USER_NAME      IDENTIFIER ,     Foreign Key from USERS
     TP_GRANTOR_USER_NAME      IDENTIFIER ,     Foreign Key from USERS
     PRIMARY KEY (GRANTEE, GRANTOR, PRIVILEGE, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA)
     )

CREATE TABLE TABLES
     (
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     TABLE_TYPE       CHAR_DATA ,
     TABS_FK_CATALOG_NAME       IDENTIFIER ,    Foreign Key from SCHEMATA
     TABS_FK_SCHEMA_NAME        IDENTIFIER ,    Foreign Key from SCHEMATA
     PRIMARY KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA)
     )

CREATE TABLE VIEW_TABLE_USAGE
     (
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     VIEW_CATALOG     IDENTIFIER ,
     VIEW_NAME        IDENTIFIER ,
     VIEW_SCHEMA      IDENTIFIER ,
     VTU_S_FK_CATALOG_NAME       IDENTIFIER ,     Foreign Key from SCHEMATA
     VTU_S_FK SCHEMA_NAME        IDENTIFIER ,     Foreign Key from SCHEMATA
     VTU_T_FK_TABLE_CATALOG      IDENTIFIER ,     Foreign Key from TABLES
     VTU_T_FK_TABLE_NAME         IDENTIFIER ,     Foreign Key from TABLES
     VTU_T_FK_TABLE_SCHEMA       IDENTIFIER ,     Foreign Key from TABLES
     PRIMARY KEY (TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, VIEW_CATALOG, 
                  VIEW_NAME, VIEW_SCHEMA)
     )

CREATE TABLE TABLE_CONSTRAINTS
     (
     CONSTRAINT_CATALOG   IDENTIFIER ,
     CONSTRAINT_NAME      IDENTIFIER ,
     CONSTRAINT_SCHEMA    IDENTIFIER ,
     CONSTRAINT_TYPE      CHAR_DATA ,
     TABLE_NAME           IDENTIFIER ,
     TAB_CONST_TABLE_CATALOG      IDENTIFIER ,     Foreign Key from TABLES
     TAB_CONST_TABLE_NAME         IDENTIFIER ,     Foreign Key from TABLES
     TAB_CONST_TABLE_SCHEMA       IDENTIFIER ,     Foreign Key from TABLES
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA)
     )

CREATE TABLE COLUMN_PRIVILEGES
     (
     COLUMN_NAME       IDENTIFIER ,
     GRANTEE           IDENTIFIER ,
     GRANTOR           IDENTIFIER ,
     PRIVILEGE         CHAR_DATA ,
     TABLE_CATALOG     IDENTIFIER ,
     TABLE_NAME        IDENTIFIER ,
     TABLE_SCHEMA      IDENTIFIER ,
     GRANTABLE         CHAR_DATA ,
     COL_PRIV_COLUMN_NAME      IDENTIFIER ,    Foreign Key from COLUMNS
     COL_PRIV_TABLE_CATALOG    IDENTIFIER ,    Foreign Key from COLUMNS
     COL_PRIV_TABLE_NAME       IDENTIFIER ,    Foreign Key from COLUMNS
     COL_PRIV_TABLE_SCHEMA     IDENTIFIER ,    Foreign Key from COLUMNS
     CP_GRANTEE_USER_NAME      IDENTIFIER ,    Foreign Key from USERS
     CP_GRANTOR_USER_NAME      IDENTIFIER ,    Foreign Key from USERS
     PRIMARY KEY (COLUMN NAME, GRANTEE, GRANTOR, PRIVILEGE, TABLE_CATALOG, 
                  TABLE_NAME, TABLE_SCHEMA)

CREATE TABLE COLUMNS
     (
     COLUMN_NAME      IDENTIFIER ,
     TABLE_CATALOG    IDENTIFIER ,
     TABLE_NAME       IDENTIFIER ,
     TABLE_SCHEMA     IDENTIFIER ,
     COLUMN_DEFAULT   CHAR_DATA ,
     DOMAIN_CATALOG   IDENTIFIER ,
     DOMAIN_NAME      IDENTIFIER ,
     DOMAIN_SCHEMA    IDENTIFIER ,
     POSITION         CARDINAL ,
     C_FK_TABLE_CATALOG    IDENTIFIER ,    Foreign Key from TABLES
     C_FK_TABLE_NAME       IDENTIFIER ,    Foreign Key from TABLES
     C_FK_TABLE_SCHEMA     IDENTIFIER ,    Foreign Key from TABLES
     DOM_COLS_DOMAIN_CATALOG     IDENTIFIER ,    Foreign Key from DOMAINS
     DOM_COLS_DOMAIN_NAME        IDENTIFIER ,    Foreign Key from DOMAINS
     DOM_COLS_DOMAIN_SCHEMA      IDENTIFIER ,    Foreign Key from DOMAINS
     PRIMARY KEY (COLUMN_NAME, TABLE_CATALOG, TABLE_NAME, TABLE SCHEMA)
     )

CREATE TABLE KEY_COLUMN_USAGE
     COLUMN_NAME           IDENTIFIER ,
     CONSTRAINT_CATALOG    IDENTIFIER ,
     CONSTRAINT_NAME       IDENTIFIER ,
     CONSTRAINT_SCHEMA     IDENTIFIER ,
     TABLE_NAME            IDENTIFIER ,
     POSITION              CARDINAL ,
     KCU_FK_COLUMN_NAME    IDENTIFIER ,    Foreign Key from COLUMNS
     KCU_FK TABLE_CATALOG  IDENTIFIER ,    Foreign Key from COLUMNS
     KCU_FK_TABLE_NAME     IDENTIFIER ,    Foreign Key from COLUMNS
     KCU_FK_TABLE_SCHEMA   IDENTIFIER ,    Foreign Key from COLUMNS
     PRIMARY KEY (COLUMN_NAME, CONSTRAINT_CATALOG, CONSTRAINT_NAME, 
                  CONSTRAINT_SCHEMA, TABLE_NAME)

CREATE TABLE VIEW_COLUMN_USAGE
     (
     COLUMN_NAME       IDENTIFIER ,
     TABLE_CATALOG     IDENTIFIER ,
     TABLE_NAME        IDENTIFIER ,
     TABLE_SCHEMA      IDENTIFIER ,
     VIEW_CATALOG      IDENTIFIER ,
     VIEW_NAME         IDENTIFIER ,
     VIEW_SCHEMA       IDENTIFIER ,
     VCU_COLUMN_NAME        IDENTIFIER ,     Foreign Key from COLUMNS
     VCU_TABLE_CATALOG      IDENTIFIER ,     Foreign Key from COLUMNS
     VCU_TABLE_NAME         IDENTIFIER ,     Foreign Key from COLUMNS
     VCU_TABLE_SCHEMA       IDENTIFIER ,     Foreign Key from COLUMNS
     PRIMARY KEY (COLUMN_NAME, TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA,
                  VIEW_CATALOG, VIEW_NAME, VIEW_SCHEMA)
     )

CREATE TABLE REF_CONSTRAINTS
     (
     CONSTRAINT_CATALOG     IDENTIFIER ,
     CONSTRAINT_NAME        IDENTIFIER ,
     CONSTRAINT_SCHEMA      IDENTIFIER ,
     CONST_CATALOG_UK       IDENTIFIER ,
     CONST_NAME_UK          IDENTIFIER ,
     CONST_SCHEMA_UK        IDENTIFIER ,
     DELETE_RULE       CHAR_DATA ,
     MATCH_OPTION      CHAR_DATA ,
     UPDATE_RULE       CHAR_DATA ,
     FK_CONSTRAINT_CATALOG  IDENTIFIER ,    Foreign Key from TABLE_CONSTRAINTS
     FK_CONSTRAINT_NAME     IDENTIFIER ,    Foreign Key from TABLE_CONSTRAINTS
     FK_CONSTRAINT_SCHEMA   IDENTIFIER ,    Foreign Key from TABLE_CONSTRAINTS
     UK_CONSTRAINT_CATALOG  IDENTIFIER ,    Foreign Key from TABLE_CONSTRAINTS
     UK_CONSTRAINT_NAME     IDENTIFIER ,    Foreign Key from TABLE_CONSTRAINTS
     UK_CONSTRAINT_SCHEMA   IDENTIFIER ,    Foreign Key from TABLE_CONSTRAINTS
     PRIMARY KEY (CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA)
     )

Contents | 1 Introduction | 2 Review | 3 Research aims | 4 Program | 5 Work | References | Appendix A | Appendix B | Appendix C