-- Generated by Oracle SQL Developer Data Modeler 18.2.0.179.0756
--   at:        2018-09-24 14:03:17 MSK
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE TABLE codex_type (
    id            INTEGER NOT NULL,
    c_type_name   VARCHAR2(20) NOT NULL,
    priority      INTEGER NOT NULL
);

ALTER TABLE codex_type ADD CONSTRAINT codex_type_pk PRIMARY KEY ( id );

CREATE TABLE comp_unit (
    id              INTEGER NOT NULL,
    unit_name       VARCHAR2(50) NOT NULL,
    max_employees   INTEGER NOT NULL
);

ALTER TABLE comp_unit ADD CONSTRAINT comp_unit_pk PRIMARY KEY ( id );

CREATE TABLE crime_type (
    id        INTEGER NOT NULL,
    cr_name   VARCHAR2(30) NOT NULL
);

ALTER TABLE crime_type ADD CONSTRAINT crime_type_pk PRIMARY KEY ( id );

CREATE TABLE employee (
    id             INTEGER NOT NULL,
    first_name     VARCHAR2(50) NOT NULL,
    second_name    VARCHAR2(150) NOT NULL,
    birth_date     DATE NOT NULL,
    email          VARCHAR2(50) NOT NULL,
    comp_unit_id   INTEGER NOT NULL
);

ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY ( id );

CREATE TABLE offence (
    id                INTEGER NOT NULL,
    commit_date       DATE NOT NULL CHECK (2018 = EXTRACT(YEAR FROM commit_date)),
    expiration_date   DATE,
    CONSTRAINT offence_duration CHECK (DATEDIFF("m", expiration_date, commit_date) <= 1),
    crime_type_id     INTEGER NOT NULL,
    description       VARCHAR2(250)
);

ALTER TABLE offence ADD CONSTRAINT offence_pk PRIMARY KEY ( id );

CREATE TABLE punishment (
    id                INTEGER NOT NULL,
    punishment_name   VARCHAR2(20) NOT NULL,
    offence_id        INTEGER NOT NULL,
    is_max            CHAR(1) NOT NULL,
    is_min            CHAR(1) NOT NULL DEFAULT "1",
    employee_id       INTEGER NOT NULL,
    fee               NUMBER
);

ALTER TABLE punishment ADD CONSTRAINT punishment_pk PRIMARY KEY ( id );

CREATE TABLE rule (
    num             NUMBER(3,2) NOT NULL,
    info            VARCHAR2(250) NOT NULL,
    crime_type_id   INTEGER NOT NULL,
    punishment_id   INTEGER NOT NULL,
    codex_type_id   INTEGER NOT NULL
);

ALTER TABLE rule ADD CONSTRAINT rule_pk PRIMARY KEY ( num );

ALTER TABLE employee
    ADD CONSTRAINT employee_comp_unit_fk FOREIGN KEY ( comp_unit_id )
        REFERENCES comp_unit ( id );

ALTER TABLE offence
    ADD CONSTRAINT offence_crime_type_fk FOREIGN KEY ( crime_type_id )
        REFERENCES crime_type ( id );

ALTER TABLE punishment
    ADD CONSTRAINT punishment_employee_fk FOREIGN KEY ( employee_id )
        REFERENCES employee ( id );

ALTER TABLE punishment
    ADD CONSTRAINT punishment_offence_fk FOREIGN KEY ( offence_id )
        REFERENCES offence ( id );

ALTER TABLE rule
    ADD CONSTRAINT rule_codex_type_fk FOREIGN KEY ( codex_type_id )
        REFERENCES codex_type ( id );

ALTER TABLE rule
    ADD CONSTRAINT rule_crime_type_fk FOREIGN KEY ( crime_type_id )
        REFERENCES crime_type ( id );

ALTER TABLE rule
    ADD CONSTRAINT rule_punishment_fk FOREIGN KEY ( punishment_id )
        REFERENCES punishment ( id );



-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                             7
-- CREATE INDEX                             0
-- ALTER TABLE                             14
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
--
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
--
-- REDACTION POLICY                         0
--
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
--
-- ERRORS                                   0
-- WARNINGS                                 0