Проектирование базы данных для магазина проката музыкальных инструментов «Музыкальный арсенал», страница 2

OBRAZOVANIE_ID INTEGER NOT NULL,

DOLJNOST_ID INTEGER NOT NULL);

CREATE TABLE STRANA (

ID INTEGER NOT NULL,

NAZVANIE VARCHAR(30));

CREATE TABLE YCHREJDENIE (

ID INTEGER NOT NULL,

NAZVANIE VARCHAR(30));

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

/***                              Primary keys                              ***/

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

ALTER TABLE DEFFECT ADD CONSTRAINT PK_DEFFECT PRIMARY KEY (ID);

ALTER TABLE DOGOVOR ADD CONSTRAINT PK_TABLE3 PRIMARY KEY (ID);

ALTER TABLE DOLJNOST ADD CONSTRAINT PK_DOLJNOST PRIMARY KEY (ID);

ALTER TABLE DOSTAVKA ADD CONSTRAINT PK_DOSTAVKA PRIMARY KEY (ID);

ALTER TABLE FIRMA ADD CONSTRAINT PK_FIRMA PRIMARY KEY (ID);

ALTER TABLE FORMA ADD CONSTRAINT PK_FORMA PRIMARY KEY (ID);

ALTER TABLE FORMA_OBRAZ ADD PRIMARY KEY (ID);

ALTER TABLE INSTRYMENT ADD CONSTRAINT PK_INSTRYMENT PRIMARY KEY (ID);

ALTER TABLE KLIENT ADD CONSTRAINT PK_KLIENT PRIMARY KEY (ID);

ALTER TABLE NAZVANIE_INSTR ADD CONSTRAINT PK_NAZVANIE_INSTR PRIMARY KEY (ID);

ALTER TABLE OBRAZOVANIE ADD CONSTRAINT PK_OBRAZOVANIE PRIMARY KEY (ID);

ALTER TABLE SOSTOAYNIE ADD CONSTRAINT PK_SOSTOAYNIE PRIMARY KEY (ID);

ALTER TABLE SOTRYDNIK ADD PRIMARY KEY (ID);

ALTER TABLE STRANA ADD CONSTRAINT PK_STRANA PRIMARY KEY (ID);

ALTER TABLE YCHREJDENIE ADD CONSTRAINT PK_YCHREJDENIE PRIMARY KEY (ID);

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

/***                              Foreign keys                              ***/

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

ALTER TABLE DOGOVOR ADD CONSTRAINT FK_DOGOVOR_1 FOREIGN KEY (SOTRYDNIK_ID) REFERENCES SOTRYDNIK (ID) ON UPDATE CASCADE;

ALTER TABLE DOGOVOR ADD CONSTRAINT FK_DOGOVOR_2 FOREIGN KEY (INSTRYMENT_ID) REFERENCES INSTRYMENT (ID) ON UPDATE CASCADE;

ALTER TABLE DOGOVOR ADD CONSTRAINT FK_DOGOVOR_3 FOREIGN KEY (KLIENT_ID) REFERENCES KLIENT (ID);

ALTER TABLE DOGOVOR ADD CONSTRAINT FK_DOGOVOR_4 FOREIGN KEY (DOSTAVKA_ID) REFERENCES DOSTAVKA (ID);

ALTER TABLE DOSTAVKA ADD CONSTRAINT FK_DOSTAVKA_1 FOREIGN KEY (FORMA_ID) REFERENCES FORMA (ID);

ALTER TABLE FIRMA ADD CONSTRAINT FK_FIRMA_1 FOREIGN KEY (STRANA_ID) REFERENCES STRANA (ID);

ALTER TABLE INSTRYMENT ADD CONSTRAINT FK_INSTRYMENT_1 FOREIGN KEY (SOSTOAYNIE_ID) REFERENCES SOSTOAYNIE (ID);

ALTER TABLE INSTRYMENT ADD CONSTRAINT FK_INSTRYMENT_2 FOREIGN KEY (FIRMA_ID) REFERENCES FIRMA (ID);

ALTER TABLE INSTRYMENT ADD CONSTRAINT FK_INSTRYMENT_3 FOREIGN KEY (NAZVANIE_INSTR_ID) REFERENCES NAZVANIE_INSTR (ID);

ALTER TABLE OBRAZOVANIE ADD CONSTRAINT FK_OBRAZOVANIE_1 FOREIGN KEY (FORMA_OBRAZ_ID) REFERENCES FORMA_OBRAZ (ID);

ALTER TABLE OBRAZOVANIE ADD CONSTRAINT FK_OBRAZOVANIE_2 FOREIGN KEY (YCHREJDENIE_ID) REFERENCES YCHREJDENIE (ID);

ALTER TABLE SOSTOAYNIE ADD CONSTRAINT FK_SOSTOAYNIE_1 FOREIGN KEY (DEFFECT_ID) REFERENCES DEFFECT (ID);

ALTER TABLE SOTRYDNIK ADD CONSTRAINT FK_SOTRYDNIK_1 FOREIGN KEY (OBRAZOVANIE_ID) REFERENCES OBRAZOVANIE (ID);

ALTER TABLE SOTRYDNIK ADD CONSTRAINT FK_SOTRYDNIK_2 FOREIGN KEY (DOLJNOST_ID) REFERENCES DOLJNOST (ID);

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

/***                                Triggers                                ***/

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

SET TERM ^ ;

SET TERM ; ^

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

/***                               Procedures                               ***/

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

SET TERM ^ ;

SET TERM ; ^