-- Generated by Oracle SQL Developer Data Modeler 21.2.0.183.1957
--   at:        2021-11-19 16:12:47 MSK
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



-- predefined type, no DDL - MDSYS.SDO_GEOMETRY

-- predefined type, no DDL - XMLTYPE

CREATE TABLE department (
    idposition INTEGER NOT NULL,
    full_name  VARCHAR2(100) NOT NULL,
    short_name VARCHAR2(30)
);

ALTER TABLE department ADD CONSTRAINT department_pk PRIMARY KEY ( idposition );

CREATE TABLE employee (
    idemployee         INTEGER NOT NULL,
    last_name          VARCHAR2(50) NOT NULL,
    first_name         VARCHAR2(50) NOT NULL,
    parent_name        VARCHAR2(50) NOT NULL,
    birthday           DATE NOT NULL,
    sex                CHAR(1) NOT NULL,
    district_birthday  VARCHAR2(60),
    town_birthday      VARCHAR2(50),
    village_birthday   VARCHAR2(50),
    reservist          CHAR(1) NOT NULL,
    date_begin         DATE NOT NULL,
    "passport_ series" VARCHAR2(4) NOT NULL,
    passport_number    VARCHAR2(6) NOT NULL,
    passport_date      DATE NOT NULL,
    inn                VARCHAR2(12) NOT NULL,
    snils              VARCHAR2(11) NOT NULL,
    category           VARCHAR2(15) NOT NULL
);

ALTER TABLE employee
    ADD CHECK ( sex IN ( 'ж', 'м' ) );

ALTER TABLE employee
    ADD CHECK ( category IN ( 'совместитель', 'штатный' ) );

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

CREATE TABLE POSITION (
    idposition INTEGER NOT NULL,
    name       VARCHAR2(100) NOT NULL,
    min_salary NUMBER(8, 2),
    max_salary NUMBER(8, 2)
);

ALTER TABLE POSITION ADD CONSTRAINT position_pk PRIMARY KEY ( idposition );

CREATE TABLE positions_department (
    number_posts          NUMBER(5, 2) NOT NULL,
    position_idposition   INTEGER NOT NULL,
    department_idposition INTEGER NOT NULL
);

ALTER TABLE positions_department ADD CONSTRAINT positions_department_pk PRIMARY KEY ( department_idposition );

--  ERROR: FK name length exceeds maximum allowed length(30)
ALTER TABLE positions_department
    ADD CONSTRAINT positions_department_department_fk FOREIGN KEY ( department_idposition )
        REFERENCES department ( idposition );

--  ERROR: FK name length exceeds maximum allowed length(30)
ALTER TABLE positions_department
    ADD CONSTRAINT positions_department_position_fk FOREIGN KEY ( position_idposition )
        REFERENCES POSITION ( idposition );



-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                             4
-- CREATE INDEX                             0
-- ALTER TABLE                              8
-- 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                                   2
-- WARNINGS                                 0