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