SQL LAB 2

/*DROP DATABASE trade_union_organization_of_events;
CREATE DATABASE trade_union_organization_of_events; */


CREATE TABLE ADRESSES(
    adress VARCHAR(255) NOT NULL PRIMARY KEY
);

CREATE TABLE POSTS(
    name VARCHAR(255) NOT NULL PRIMARY KEY
);

CREATE TABLE EVENT_TYPES (
    e_type VARCHAR(30) NOT NULL PRIMARY KEY
);

CREATE TABLE ROLES(
    role_name VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE COMPANIES(
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    adress VARCHAR(255) NOT NULL REFERENCES ADRESSES(adress)
);

CREATE TABLE DEPARTMENTS(
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    company INT NOT NULL REFERENCES COMPANIES(id)
);

CREATE TABLE PERSONS(
    id INT NOT NULL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    adress VARCHAR(255) NOT NULL REFERENCES ADRESSES(adress)
);

CREATE TABLE PERSONS_DEPARTMENTS(
    id INT NOT NULL PRIMARY KEY,
    person INT NOT NULL REFERENCES PERSONS(id),
    department INT NOT NULL REFERENCES DEPARTMENTS(id),
    post VARCHAR(255) NOT NULL REFERENCES POSTS(name)
);

CREATE TABLE PROBLEMS(
    id INT NOT NULL PRIMARY KEY,
    register_time DATE NOT NULL,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(255) NOT NULL
);

CREATE TABLE EVENTS (
    id INT NOT NULL PRIMARY KEY,
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    approval_date DATE NOT NULL,
    e_type VARCHAR(30) NOT NULL REFERENCES EVENT_TYPES(e_type),
    adress VARCHAR(255) NOT NULL REFERENCES ADRESSES(adress)
);

CREATE TABLE PERSONS_EVENTS(
    id INT NOT NULL PRIMARY KEY,
    role VARCHAR(50) NOT NULL REFERENCES ROLES(role_name),
    person INT NOT NULL REFERENCES PERSONS(id),
    event INT NOT NULL REFERENCES EVENTS(id)
);

CREATE TABLE EVENTS_PROBLEMS(
    id INT NOT NULL PRIMARY KEY,
    event INT NOT NULL REFERENCES EVENTS(id),
    problem INT NOT NULL REFERENCES PROBLEMS(id)
);