create_distribution_course_table.sql 3.19 KiB
Newer Older
--liquibase formatted sql

-- changeset galan:create_distribution_course_table

CREATE TABLE IF NOT EXISTS teachers.distribution_practice
(
    id                  serial8 PRIMARY KEY NOT NULL,
    contingent_budget   int4,
    contingent_contract int4,
    contingent_foreign  int4,
    is_base             bool,
    per_group           bool,
    practice_hours      double precision,
    total_contingent    int4,
    total_hours         double precision,
    distribution_id     int8,
    teacher_id int8,
    CONSTRAINT distribution_practice_fk0 FOREIGN KEY (distribution_id) REFERENCES teachers.distribution(id),
    CONSTRAINT distribution_practice_fk1 FOREIGN KEY (teacher_id) REFERENCES teachers.teacher(id)
);

CREATE TABLE IF NOT EXISTS teachers.group_course_wkr
(
    id serial8 PRIMARY KEY NOT NULL,
    name_group varchar(255) NULL
);

CREATE TABLE IF NOT EXISTS teachers.name_course_wkr
(
    id serial8 PRIMARY KEY NOT NULL,
    name varchar(255) NULL,
    group_course_wkr_id int8 NULL,
    CONSTRAINT name_course_wkr_fk0 FOREIGN KEY (group_course_wkr_id) REFERENCES teachers.group_course_wkr(id)
);

CREATE TABLE IF NOT EXISTS teachers.distribution_course_wkr
(
    id int8 PRIMARY KEY NOT NULL,
    contingent_budget int4 NULL,
    contingent_contract int4 NULL,
    contingent_foreign int4 NULL,
    distribution_id int8 NULL,
    practice_hours float8 NULL,
    teacher_id int8 NULL,
    total_contingent int4 NULL,
    total_hours float8 NULL,
    name_course_wkr_id int8 NULL,
    CONSTRAINT distribution_course_wkr_fk0 FOREIGN KEY (name_course_wkr_id) REFERENCES teachers.name_course_wkr(id),
    CONSTRAINT distribution_course_wkr_fk1 FOREIGN KEY (distribution_id) REFERENCES teachers.distribution(id),
    CONSTRAINT distribution_course_wkr_fk2 FOREIGN KEY (teacher_id) REFERENCES teachers.teacher(id)
);

INSERT INTO teachers.group_course_wkr
VALUES  (1,'Курс 3 Бакалавр, Курсовая'),
        (2,'Курс 4 Бакалавр, ВКР'),
        (3,'Курс 1 Магистратура, Курсовая'),
        (4,'Курс 2 Магистратура, ВКР');

INSERT INTO teachers.name_course_wkr
VALUES  (1,'Курсовая работа 1',1),
        (2,'Курсовая работа 2',1),
        (3,'Научно-исследовательская работа',2),
        (4,'Преддипломная практика',2),
        (5,'Руководство ВКР бакалавр',2),
        (6,'Организация допусков ВКР к защите (бакалавр и специалист)',2),
        (7,'Курсовая работа',3),
        (8,'Научно-исследовательская работа (получение первичных навыков научно-исследовательской работы)',3),
        (9,'Научно-исследовательская работа',4),
        (10,'Преддипломная практика',4),
        (11,'Руководство магистерской диссертацией',4);

/* liquibase rollback
DROP TABLE IF EXISTS teachers.distribution_practice;
DROP TABLE IF EXISTS teachers.distribution_course_wkr;
DROP TABLE IF EXISTS teachers.name_course_wkr;
DROP TABLE IF EXISTS teachers.group_course_wkr;
*/