Базы данных SQL-DDL и SQL-DML. Изучение транзакций. Программирование на языке SQL. Консольное приложение (Цикл лабораторных работ). Вариант № 1, страница 2

);

CREATE TABLE REASON (

    REASON  VARCHAR(256),

    ID      SMALLINT NOT NULL

);

CREATE TABLE STATUS (

    ID           INTEGER NOT NULL,

    DESCRIPTION  VARCHAR(32) NOT NULL

);

CREATE TABLE STATUS_HISTORY (

    STATUS_ID  INTEGER NOT NULL,

    PERSON_ID  INTEGER NOT NULL,

    DT_CHANGE  TIMESTAMP NOT NULL

);

CREATE TABLE SUMMONS (

    ID         INTEGER NOT NULL,

    DT_CREATE  TIMESTAMP NOT NULL,

    DT_SUMMON  TIMESTAMP NOT NULL,

    PERSON_ID  INTEGER NOT NULL

);

/******************************************************************************/

/***                           Unique Constraints                           ***/

/******************************************************************************/

ALTER TABLE REASON ADD CONSTRAINT UNQ1_REASON UNIQUE (REASON);

/******************************************************************************/

/***                              Primary Keys                              ***/

/******************************************************************************/

ALTER TABLE ADDRESS ADD CONSTRAINT PK_ADDRESS PRIMARY KEY (ID);

ALTER TABLE ADDRESS_LINK ADD CONSTRAINT PK_ADDRESS_LINK PRIMARY KEY (PERSON_ID, ADDRESS_ID);

ALTER TABLE DELAYS ADD CONSTRAINT PK_DELAYS PRIMARY KEY (ID);

ALTER TABLE MEDICAL_NOTES ADD CONSTRAINT PK_MEDICAL_NOTES PRIMARY KEY (ID);

ALTER TABLE MEDICS ADD CONSTRAINT PK_MEDICS PRIMARY KEY (ID);

ALTER TABLE PEOPLE ADD CONSTRAINT PK_PEOPLE PRIMARY KEY (ID);

ALTER TABLE PROFESSIONS ADD CONSTRAINT PK_PROFESSIONS PRIMARY KEY (ID);

ALTER TABLE REASON ADD PRIMARY KEY (ID);

ALTER TABLE STATUS ADD CONSTRAINT PK_STATUS PRIMARY KEY (ID);

ALTER TABLE STATUS_HISTORY ADD CONSTRAINT PK_STATUS_HISTORY PRIMARY KEY (STATUS_ID, PERSON_ID, DT_CHANGE);

ALTER TABLE SUMMONS ADD CONSTRAINT PK_SUMMONS PRIMARY KEY (ID);

/******************************************************************************/

/***                              Foreign Keys                              ***/

/******************************************************************************/

ALTER TABLE ADDRESS_LINK ADD CONSTRAINT FK_ADDRESS_LINK_1 FOREIGN KEY (PERSON_ID) REFERENCES PEOPLE (ID) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE ADDRESS_LINK ADD CONSTRAINT FK_ADDRESS_LINK_2 FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS (ID) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE DELAYS ADD CONSTRAINT FK_DELAYS_1 FOREIGN KEY (PERSON_ID) REFERENCES PEOPLE (ID);

ALTER TABLE DELAYS ADD CONSTRAINT FK_DELAYS_2 FOREIGN KEY (REASON_ID) REFERENCES REASON (ID);

ALTER TABLE MEDICAL_NOTES ADD CONSTRAINT FK_MEDICAL_NOTES_1 FOREIGN KEY (AUTHOR_ID) REFERENCES MEDICS (ID);

ALTER TABLE MEDICAL_NOTES ADD CONSTRAINT FK_MEDICAL_NOTES_2 FOREIGN KEY (PERSON_ID) REFERENCES PEOPLE (ID);

ALTER TABLE MEDICS ADD CONSTRAINT FK_MEDICS_1 FOREIGN KEY (PROFESSION_ID) REFERENCES PROFESSIONS (ID);

ALTER TABLE STATUS_HISTORY ADD CONSTRAINT FK_STATUS_HISTORY_1 FOREIGN KEY (STATUS_ID) REFERENCES STATUS (ID);

ALTER TABLE STATUS_HISTORY ADD CONSTRAINT FK_STATUS_HISTORY_2 FOREIGN KEY (PERSON_ID) REFERENCES PEOPLE (ID);

ALTER TABLE SUMMONS ADD CONSTRAINT FK_SUMMONS_1 FOREIGN KEY (PERSON_ID) REFERENCES PEOPLE (ID);

Скрипт заполнения БД приведен в листинге 2.

Листинг 2:

INSERT INTO ADDRESS (ID, STR) VALUES (1, '115569, Москва, ул. Маршала Захарова, д. 8, корп. 1');

INSERT INTO ADDRESS (ID, STR) VALUES (2, '117623, Москва, улица Брусилова, дом 29, корп. 1');

INSERT INTO ADDRESS (ID, STR) VALUES (3, '119334, Москва, Ленинский проспект, дом 43 Б');

INSERT INTO ADDRESS (ID, STR) VALUES (4, '111674, Москва, ул. Лухмановская, дом 13Б');