-- Employees' dossier.
create table employees (
    id            number not null,
    surname       varchar2(30),
    name          varchar2(30),
    second_name   varchar2(30),
    birth_date    date,
    gender        char(1),
    birth_place   varchar2(30),
    address       varchar2(30),
    phone_number  number
);

-- Departments' properties.
create table departments (
    id                number not null,
    name              varchar2(30),
    abbreviation      varchar2(30),
    department_number number
);

-- States' grade.
create table states(
    id           number not null,
    name         varchar2(40),
    abbreviation varchar2(30)
);
-- Positions' list.
create table positions(
    id             number not null,
    name           varchar2(30),
    abbreviation   varchar2(30)
);
-- Relation between employee and their department.
create table emp_departments(
    id                      number not null,
    emp_id                  number not null,
    dep_id                  number not null,
    change_date             date
);
-- Relation between employee and their positions.
create table emp_positions(
    id                     number not null,
    emp_id                 number not null,
    pos_id                 number not null,
    change_date            date
);
-- Realation between employee and their state.
create table emp_states(
    id                     number not null,
    emp_id                 number not null,
    state_id               number not null,
    change_status_date     date,
    where_transferred_name varchar2(40),
    commentary             varchar2(100)
);
-- Employees' job experience for the company.
create table emp_work_ranges(
    id                      number not null,
    emp_id                  number not null,
    employment_date         date not null,
    dismissal_date          date
);