CREATE TABLE body_table (
    C                                INTEGER NOT NULL,
    HEADER_ID                        INTEGER NOT NULL,
    YEAR_ID                          INTEGER NOT NULL,
    ORG_ID                           INTEGER NOT NULL,
    FLAG                             FLAGDOM NOT NULL,
    FAKE                             SMALLINT DEFAULT 0 NOT NULL,
    DELETED                          DOM_DELETED
);


CREATE TABLE SPECS (
    C                     INTEGER NOT NULL,
    YEAR_ID               INTEGER NOT NULL,
    body_table_ID         INTEGER NOT NULL,
    ORG_ID                INTEGER NOT NULL,
    FLAG                  FLAGDOM NOT NULL,
    FAKE                  SMALLINT NOT NULL,
    DELETED               DOM_DELETED
);


CREATE TABLE STARTP2 (
    C                     INTEGER NOT NULL,
    YEAR_ID               INTEGER NOT NULL,
    SPECS_ID              INTEGER NOT NULL,
    ORG_ID                INTEGER NOT NULL,
    FLAG                  FLAGDOM NOT NULL,
    FAKE                  SMALLINT NOT NULL,
    body_table_ID         INTEGER NOT NULL,
    DELETED               DOM_DELETED
);




/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/

ALTER TABLE body_table ADD CONSTRAINT UNQ1_body_table UNIQUE (C);
ALTER TABLE SPECS ADD CONSTRAINT UNQ1_SPECS UNIQUE (YEAR_ID, body_table_ID, ORG_ID);
ALTER TABLE STARTP2 ADD CONSTRAINT UNQ2_STARTP2 UNIQUE (C, SPECS_ID);


/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE body_table ADD CONSTRAINT PK_body_table PRIMARY KEY (C, HEADER_ID, YEAR_ID, ORG_ID, FLAG, FAKE);
ALTER TABLE SPECS ADD CONSTRAINT PK_SPECS PRIMARY KEY (C, body_table_ID, YEAR_ID, ORG_ID, FAKE, FLAG);
ALTER TABLE STARTP2 ADD CONSTRAINT PK_STARTP2 PRIMARY KEY (C, body_table_ID, HEADER_ID, YEAR_ID, ORG_ID, FLAG);


/******************************************************************************/
/***                              Foreign keys                              ***/
/******************************************************************************/

ALTER TABLE SPECS ADD CONSTRAINT FK_SPECS_2 FOREIGN KEY (body_table_ID, HEADER_ID, YEAR_ID, ORG_ID, FLAG, FAKE) REFERENCES body_table (C, HEADER_ID, YEAR_ID, ORG_ID, FLAG, FAKE) ON UPDATE CASCADE;
ALTER TABLE STARTP2 ADD CONSTRAINT FK_STARTP2_3 FOREIGN KEY (body_table_ID, HEADER_ID, YEAR_ID, ORG_ID, FLAG, FAKE) REFERENCES body_table (C, HEADER_ID, YEAR_ID, ORG_ID, FLAG, FAKE) ON UPDATE CASCADE;
ALTER TABLE STARTP2 ADD CONSTRAINT FK_STARTP2_5 FOREIGN KEY (SPECS_ID, body_table_ID, YEAR_ID, ORG_ID, FAKE, FLAG) REFERENCES SPECS (C, body_table_ID, YEAR_ID, ORG_ID, FAKE, FLAG) ON UPDATE CASCADE;

CREATE TABLE Z1 (
    C     INTEGER NOT NULL,
    NAME  CHAR(10)
);


CREATE TABLE Z1_1 (
    C      INTEGER NOT NULL,
    Z1_ID  INTEGER NOT NULL,
    NAME   CHAR(10)
);


CREATE TABLE Z2 (
    C        INTEGER NOT NULL,
    NAME     CHAR(10),
    BASE_ID  INTEGER NOT NULL
);


CREATE TABLE Z2_1 (
    C        INTEGER NOT NULL,
    Z2_ID    INTEGER NOT NULL,
    Z1_1_ID  INTEGER NOT NULL,
    NAME     CHAR(10)
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE Z1 ADD CONSTRAINT PK_Z1 PRIMARY KEY (C);
ALTER TABLE Z1_1 ADD CONSTRAINT PK_Z1_1 PRIMARY KEY (C);
ALTER TABLE Z2 ADD CONSTRAINT PK_Z2 PRIMARY KEY (C);
ALTER TABLE Z2_1 ADD CONSTRAINT PK_Z2_1 PRIMARY KEY (C);


/******************************************************************************/
/***                              Foreign keys                              ***/
/******************************************************************************/

ALTER TABLE Z1_1 ADD CONSTRAINT FK_Z1_1_1 FOREIGN KEY (Z1_ID) REFERENCES Z1 (C);
ALTER TABLE Z2 ADD CONSTRAINT FK_Z2_1 FOREIGN KEY (BASE_ID) REFERENCES Z1 (C);
ALTER TABLE Z2_1 ADD CONSTRAINT FK_Z2_1_1 FOREIGN KEY (Z2_ID) REFERENCES Z2 (C);