Создание первой БД (Страховая компания)

SQL> CREATE DATABASE 'C:\Users\Abibas\Desktop\db\myDB.fbd'
CON> USER 'SYSDBA' password 'masterkey';
Server version:
WI-V2.5.3.26780 Firebird 2.5
WI-V2.5.3.26780 Firebird 2.5/XNet (PHILIPS)/P12
WI-V2.5.3.26780 Firebird 2.5/XNet (PHILIPS)/P12
SQL> CREATE DOMAIN id AS INTEGER NOT NULL;
SQL> CREATE DOMAIN name AS VARCHAR(30);
SQL> CREATE DOMAIN adress AS VARCHAR(50);
SQL> CREATE DOMAIN mydate AS DATE;
SQL> CREATE DOMAIN cash AS DECIMAL(18,4);
SQL> CREATE DOMAIN phone AS VARCHAR(10);
SQL> SHOW DOMAIN;
       ADRESS                                 CASH
       ID                                     MYDATE
       NAME                                   PHONE

 CREATE TABLE employee(
        emp_id id,
        emp_name name,
        emp_adress adress,
        emp_phone phone,
        emp_cash cash);
 
 CREATE TABLE branch (
       br_id id,
       br_name name,
       br_adress adress,
       br_phone phone);
 
 CREATE TABLE contract(
        ctr_id id,
        ctr_date DATE,
        ctr_insurance cash,
        ctr_rate rate);
 
CREATE TABLE tins(
        ti_id id,
        ti_name name);

 ALTER TABLE employee ADD CONSTRAINT pk_emp_id PRIMARY KEY(emp_id);
 ALTER TABLE branch ADD CONSTRAINT pk_br_id PRIMARY KEY(br_id);
 ALTER TABLE contract ADD CONSTRAINT pk_ctr_id PRIMARY KEY(ctr_id);
 ALTER TABLE tins ADD CONSTRAINT pk_ti_id PRIMARY KEY(ti_id);

CREATE generator g_emp;
CREATE generator g_br;
CREATE generator g_ctr;
CREATE generator g_ti;

SQL> SET term ^;
SQL> CREATE TRIGGER t_emp FOR employee active
CON> BEFORE INSERT POSITION 1
CON> AS BEGIN
CON> IF(NEW.emp_id IS NULL)THEN
CON> NEW.emp_id=gen_id(g_emp,1);
CON> END ^
SQL> SET term ; ^
SQL> SHOW TRIGGER;
TABLE name                       TRIGGER name                     Invalid
================================ ================================ =======
EMPLOYEE                         T_EMP
SQL> SET term ^;
SQL> CREATE TRIGGER t_br FOR branch active
CON> BEFORE INSERT POSITION 1
CON> AS BEGIN
CON> IF(NEW.br_id IS NULL)THEN
CON> NEW.br_id=gen_id(g_br,1);
CON> END ^
SQL> SET term ; ^
SQL> SHOW TRIGGER;
TABLE name                       TRIGGER name                     Invalid
================================ ================================ =======
BRANCH                           T_BR
EMPLOYEE                         T_EMP
SQL> SET term ^;
SQL> CREATE TRIGGER t_ctr FOR contract active
CON> BEFORE INSERT POSITION 1
CON> AS BEGIN
CON> IF(NEW.ctr_id IS NULL)THEN
CON> NEW.ctr_id=gen_id(g_ctr,1);
CON> END ^
SQL> SET term ; ^
SQL> SHOW TRIGGER;
TABLE name                       TRIGGER name                     Invalid
================================ ================================ =======
BRANCH                           T_BR
CONTRACT                         T_CTR
EMPLOYEE                         T_EMP
SQL> SET term ^;
SQL> CREATE TRIGGER t_ti FOR tins active
CON> BEFORE INSERT POSITION 1
CON> AS BEGIN
CON> IF(NEW.ti_id IS NULL)THEN
CON> NEW.ti_id=gen_id(g_ti,1);
CON> END ^
SQL> SET term ; ^
SQL> SHOW TRIGGER;
TABLE name                       TRIGGER name                     Invalid
================================ ================================ =======
BRANCH                           T_BR
CONTRACT                         T_CTR
EMPLOYEE                         T_EMP
TINS                             T_TI
SQL>

SQL> ALTER TABLE employee ADD emp_br id;
SQL> ALTER TABLE employee ADD CONSTRAINT fk_emp_br FOREIGN KEY (emp_br) REFERENCES branch;

ALTER TABLE contract ADD ctr_br id;
ALTER TABLE contract ADD ctr_ti id;
 
ALTER TABLE contract ADD CONSTRAINT fk_ctr_br FOREIGN KEY (ctr_br) REFERENCES branch;
ALTER TABLE contract ADD CONSTRAINT fk_ctr_ti FOREIGN KEY (ctr_ti) REFERENCES tins;