backend/data/lab10/task2.sql - master - Centermant/web-nodejs

backend/data/lab10/task2.sql
520 строк13 KB
Centermant
feat(lab11)
16 дек 2025, 00:57
16 дек 2025, 00:5701b83c7
-- Adminer 5.4.1 PostgreSQL 16.11 dump

DROP TABLE IF EXISTS "groups";
DROP SEQUENCE IF EXISTS groups_id_seq;
CREATE SEQUENCE groups_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."groups" (
    "id" integer DEFAULT nextval('groups_id_seq') NOT NULL,
    "name" character varying(50) NOT NULL,
    "course" integer,
    CONSTRAINT "groups_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "groups_course_check" CHECK (((course >= 1) AND (course <= 6)))
)
WITH (oids = false);

CREATE UNIQUE INDEX groups_name_key ON lab10.groups USING btree (name);

INSERT INTO "groups" ("id", "name", "course") VALUES
(1,	'ИТ2201',	4),
(2,	'ИТ2202',	4),
(3,	'ИТ2203',	4),
(4,	'ИТ2204',	4);

DROP TABLE IF EXISTS "people";
DROP SEQUENCE IF EXISTS people_id_seq;
CREATE SEQUENCE people_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."people" (
    "id" integer DEFAULT nextval('people_id_seq') NOT NULL,
    "last_name" character varying(100) NOT NULL,
    "first_name" character varying(100) NOT NULL,
    "middle_name" character varying(100),
    "email" character varying(255),
    CONSTRAINT "people_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "people" ("id", "last_name", "first_name", "middle_name", "email") VALUES
(1,	'Вострокнутов',	'Александр',	'Евгеньевич',	'-'),
(2,	'Кузьмина',	'Эвелина',	'Вячеславовна',	'-'),
(3,	'Минина',	'Евгения',	'Александровна',	'-'),
(4,	'Лытнев',	'Николай',	'Николаевич',	'-'),
(5,	'Чемарина',	'Анна',	'Валерьевна',	'-'),
(6,	'Барановская',	'Татьяна',	'Петровна',	'-'),
(7,	'Лаптев',	'Сергей',	'Владимирович',	'-'),
(8,	'Креймер',	'Алексей',	'Семёнович',	'-'),
(9,	'Крамаренко',	'Татьяна',	'Анатольевна',	'-'),
(10,	'Самойлюков',	'Юрий',	'Николаевич',	'-');

DROP TABLE IF EXISTS "schedule_groups";
CREATE TABLE "lab10"."schedule_groups" (
    "schedule_item_id" integer NOT NULL,
    "group_id" integer NOT NULL,
    CONSTRAINT "schedule_groups_pkey" PRIMARY KEY ("schedule_item_id", "group_id")
)
WITH (oids = false);

INSERT INTO "schedule_groups" ("schedule_item_id", "group_id") VALUES
(14,	1),
(15,	2),
(16,	3),
(17,	4),
(18,	1),
(19,	2),
(20,	3),
(21,	4),
(23,	3),
(24,	3),
(25,	1),
(26,	2),
(27,	3),
(28,	4),
(29,	3),
(30,	1),
(31,	2),
(32,	3),
(33,	4),
(34,	1),
(35,	2),
(36,	3),
(37,	4),
(38,	3),
(39,	3),
(40,	3),
(41,	3),
(42,	3),
(43,	3),
(44,	1),
(45,	2),
(46,	3),
(47,	4),
(48,	3),
(49,	3),
(50,	3),
(51,	3),
(52,	3),
(53,	1),
(54,	2),
(55,	3),
(56,	4),
(57,	1),
(58,	2),
(59,	3),
(60,	4),
(61,	3),
(62,	1),
(63,	2),
(64,	3),
(65,	4),
(66,	1),
(67,	2),
(68,	3),
(69,	4),
(70,	3),
(71,	1),
(72,	1),
(73,	1),
(74,	1),
(75,	1),
(76,	1),
(77,	1),
(78,	1),
(79,	1),
(80,	1),
(81,	1),
(82,	1),
(84,	1),
(85,	1),
(86,	1),
(87,	1),
(88,	1),
(90,	1),
(91,	1),
(94,	2),
(95,	2),
(96,	2),
(97,	2),
(98,	2),
(99,	2),
(100,	2),
(101,	2),
(102,	2),
(103,	2),
(104,	2),
(105,	2),
(106,	2),
(107,	2),
(108,	2),
(109,	2),
(110,	2),
(111,	4),
(112,	4),
(113,	4),
(114,	4),
(115,	4),
(116,	4),
(117,	4),
(119,	4),
(120,	4),
(121,	4),
(122,	4),
(123,	4),
(124,	4),
(125,	4),
(126,	4),
(127,	4);

DROP TABLE IF EXISTS "schedule_items";
DROP SEQUENCE IF EXISTS schedule_items_id_seq;
CREATE SEQUENCE schedule_items_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."schedule_items" (
    "id" integer DEFAULT nextval('schedule_items_id_seq') NOT NULL,
    "subject_id" integer NOT NULL,
    "day_of_week" integer NOT NULL,
    "week_type" integer,
    "lesson_number" integer NOT NULL,
    "lesson_type" character varying(20) NOT NULL,
    "room_number" character varying(20),
    CONSTRAINT "schedule_items_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "schedule_items_day_of_week_check" CHECK (((day_of_week >= 1) AND (day_of_week <= 6))),
    CONSTRAINT "schedule_items_week_type_check" CHECK ((week_type = ANY (ARRAY[1, 2]))),
    CONSTRAINT "schedule_items_lesson_number_check" CHECK (((lesson_number >= 1) AND (lesson_number <= 6))),
    CONSTRAINT "schedule_items_lesson_type_check" CHECK (((lesson_type)::text = ANY ((ARRAY['lecture'::character varying, 'practice'::character varying, 'lab'::character varying])::text[])))
)
WITH (oids = false);

INSERT INTO "schedule_items" ("id", "subject_id", "day_of_week", "week_type", "lesson_number", "lesson_type", "room_number") VALUES
(14,	5,	1,	1,	2,	'lecture',	'403эк'),
(15,	5,	1,	1,	2,	'lecture',	'403эк'),
(16,	5,	1,	1,	2,	'lecture',	'403эк'),
(17,	5,	1,	1,	2,	'lecture',	'403эк'),
(18,	2,	1,	1,	3,	'lecture',	'403эк'),
(19,	2,	1,	1,	3,	'lecture',	'403эк'),
(20,	2,	1,	1,	3,	'lecture',	'403эк'),
(21,	2,	1,	1,	3,	'lecture',	'403эк'),
(23,	1,	2,	1,	2,	'practice',	'401эк'),
(24,	3,	2,	1,	3,	'practice',	'103эк'),
(25,	3,	2,	1,	4,	'lecture',	'103эк'),
(26,	3,	2,	1,	4,	'lecture',	'103эк'),
(27,	3,	2,	1,	4,	'lecture',	'103эк'),
(28,	3,	2,	1,	4,	'lecture',	'103эк'),
(29,	1,	3,	1,	3,	'practice',	'404эк'),
(30,	1,	3,	1,	4,	'lecture',	'11гд'),
(31,	1,	3,	1,	4,	'lecture',	'11гд'),
(32,	1,	3,	1,	4,	'lecture',	'11гд'),
(33,	1,	3,	1,	4,	'lecture',	'11гд'),
(34,	4,	4,	1,	4,	'lecture',	'221гл'),
(35,	4,	4,	1,	4,	'lecture',	'221гл'),
(36,	4,	4,	1,	4,	'lecture',	'221гл'),
(37,	4,	4,	1,	4,	'lecture',	'221гл'),
(38,	2,	3,	1,	5,	'practice',	'402эк'),
(39,	2,	3,	1,	6,	'practice',	'402эк'),
(40,	4,	4,	1,	2,	'practice',	'103эк'),
(41,	3,	4,	1,	3,	'practice',	'108эк'),
(42,	4,	5,	1,	3,	'practice',	'307эк'),
(43,	1,	5,	1,	4,	'practice',	'408эк'),
(44,	2,	1,	2,	2,	'lecture',	'403эк'),
(45,	2,	1,	2,	2,	'lecture',	'403эк'),
(46,	2,	1,	2,	2,	'lecture',	'403эк'),
(47,	2,	1,	2,	2,	'lecture',	'403эк'),
(48,	1,	1,	2,	3,	'practice',	'404эк/401эк'),
(49,	3,	2,	2,	1,	'practice',	'108эк'),
(50,	4,	2,	2,	2,	'practice',	'307эк'),
(51,	1,	2,	2,	3,	'practice',	'404эк'),
(52,	3,	3,	2,	1,	'practice',	'108эк'),
(53,	5,	3,	2,	2,	'lecture',	'403эк'),
(54,	5,	3,	2,	2,	'lecture',	'403эк'),
(55,	5,	3,	2,	2,	'lecture',	'403эк'),
(56,	5,	3,	2,	2,	'lecture',	'403эк'),
(57,	1,	3,	2,	3,	'lecture',	'310эк'),
(58,	1,	3,	2,	3,	'lecture',	'310эк'),
(59,	1,	3,	2,	3,	'lecture',	'310эк'),
(60,	1,	3,	2,	3,	'lecture',	'310эк'),
(61,	4,	4,	2,	2,	'practice',	'307эк'),
(62,	3,	4,	2,	3,	'lecture',	'403эк'),
(63,	3,	4,	2,	3,	'lecture',	'403эк'),
(64,	3,	4,	2,	3,	'lecture',	'403эк'),
(65,	3,	4,	2,	3,	'lecture',	'403эк'),
(66,	4,	4,	2,	4,	'lecture',	'221гл'),
(67,	4,	4,	2,	4,	'lecture',	'221гл'),
(68,	4,	4,	2,	4,	'lecture',	'221гл'),
(69,	4,	4,	2,	4,	'lecture',	'221гл'),
(70,	2,	4,	2,	5,	'practice',	'408эк'),
(71,	3,	1,	1,	4,	'practice',	'108эк'),
(72,	2,	1,	1,	5,	'practice',	'402эк'),
(73,	2,	1,	1,	6,	'practice',	'402эк'),
(74,	3,	2,	1,	2,	'practice',	'407эк'),
(75,	4,	2,	1,	3,	'practice',	'108эк'),
(76,	1,	2,	1,	5,	'practice',	'404эк'),
(77,	1,	3,	1,	3,	'practice',	'404эк'),
(78,	3,	4,	1,	1,	'practice',	'303эк'),
(79,	4,	4,	1,	3,	'practice',	'303эк'),
(80,	1,	5,	1,	1,	'practice',	'407эк'),
(81,	1,	5,	1,	2,	'practice',	'407эк'),
(82,	3,	1,	2,	3,	'practice',	'103эк'),
(84,	2,	2,	2,	1,	'practice',	'408эк'),
(85,	4,	2,	2,	2,	'practice',	'303эк'),
(86,	4,	2,	2,	3,	'practice',	'303эк'),
(87,	3,	2,	2,	4,	'practice',	'108эк'),
(88,	1,	2,	2,	5,	'practice',	'404эк'),
(90,	1,	5,	2,	3,	'practice',	'101эк'),
(91,	1,	5,	2,	4,	'practice',	'401эк'),
(94,	2,	1,	1,	4,	'practice',	'404эк'),
(95,	3,	2,	1,	2,	'practice',	'103эк'),
(96,	1,	2,	1,	3,	'practice',	'407эк'),
(97,	2,	2,	1,	5,	'practice',	'402эк'),
(98,	1,	2,	1,	6,	'practice',	'402эк'),
(99,	3,	4,	1,	1,	'practice',	'101эк'),
(100,	1,	4,	1,	2,	'practice',	'219эк'),
(101,	4,	4,	1,	3,	'practice',	'303эк'),
(102,	1,	5,	1,	3,	'practice',	'407эк'),
(103,	2,	2,	2,	1,	'practice',	'408эк'),
(104,	1,	2,	2,	2,	'practice',	'408эк'),
(105,	4,	2,	2,	3,	'practice',	'303эк'),
(106,	3,	2,	2,	4,	'practice',	'207эк'),
(107,	3,	3,	2,	1,	'practice',	'108эк'),
(108,	4,	4,	2,	2,	'practice',	'303эк'),
(109,	4,	4,	2,	5,	'practice',	'108эк'),
(110,	1,	5,	2,	3,	'practice',	'101эк'),
(111,	3,	2,	1,	3,	'practice',	'103эк'),
(112,	4,	3,	1,	2,	'practice',	'101эк'),
(113,	4,	3,	1,	3,	'practice',	'101эк'),
(114,	3,	4,	1,	2,	'practice',	'303эк'),
(115,	1,	4,	1,	3,	'practice',	'219эк'),
(116,	2,	4,	1,	5,	'practice',	'402эк'),
(117,	2,	4,	1,	6,	'practice',	'402эк'),
(119,	1,	5,	1,	5,	'practice',	'408эк'),
(120,	1,	1,	2,	3,	'practice',	'401эк/404эк'),
(121,	3,	2,	2,	3,	'practice',	'108эк'),
(122,	1,	2,	2,	4,	'practice',	'404эк'),
(123,	2,	2,	2,	5,	'practice',	'408эк'),
(124,	4,	3,	2,	1,	'practice',	'408эк'),
(125,	3,	4,	2,	2,	'practice',	'101эк'),
(126,	1,	5,	2,	2,	'practice',	'407эк'),
(127,	4,	5,	2,	3,	'practice',	'103эк');

DROP TABLE IF EXISTS "schedule_teachers";
CREATE TABLE "lab10"."schedule_teachers" (
    "schedule_item_id" integer NOT NULL,
    "teacher_id" integer NOT NULL,
    CONSTRAINT "schedule_teachers_pkey" PRIMARY KEY ("schedule_item_id", "teacher_id")
)
WITH (oids = false);

INSERT INTO "schedule_teachers" ("schedule_item_id", "teacher_id") VALUES
(14,	4),
(15,	4),
(16,	4),
(17,	4),
(18,	1),
(19,	1),
(20,	1),
(21,	1),
(23,	2),
(24,	4),
(25,	7),
(26,	7),
(27,	7),
(28,	7),
(29,	3),
(30,	6),
(30,	2),
(31,	6),
(31,	2),
(32,	6),
(32,	2),
(33,	6),
(33,	2),
(34,	8),
(35,	8),
(36,	8),
(37,	8),
(38,	9),
(38,	10),
(39,	9),
(39,	10),
(40,	5),
(41,	4),
(42,	5),
(43,	2),
(43,	3),
(44,	1),
(45,	1),
(46,	1),
(47,	1),
(48,	2),
(48,	3),
(49,	4),
(50,	5),
(51,	2),
(51,	3),
(52,	4),
(53,	4),
(54,	4),
(55,	4),
(56,	4),
(57,	6),
(57,	2),
(58,	6),
(58,	2),
(59,	6),
(59,	2),
(60,	6),
(60,	2),
(61,	5),
(62,	7),
(63,	7),
(64,	7),
(65,	7),
(66,	8),
(67,	8),
(68,	8),
(69,	8),
(70,	9),
(70,	10),
(71,	7),
(72,	9),
(72,	10),
(73,	9),
(73,	10),
(74,	7),
(75,	7),
(76,	3),
(77,	3),
(78,	7),
(79,	7),
(80,	2),
(81,	2),
(82,	7),
(84,	9),
(84,	10),
(85,	7),
(86,	7),
(87,	7),
(88,	2),
(88,	3),
(90,	2),
(91,	3),
(94,	9),
(94,	10),
(95,	4),
(96,	3),
(97,	9),
(97,	10),
(98,	2),
(99,	4),
(100,	3),
(101,	7),
(102,	2),
(103,	9),
(103,	10),
(104,	2),
(104,	3),
(105,	7),
(106,	4),
(107,	4),
(108,	7),
(109,	7),
(110,	2),
(111,	4),
(112,	5),
(113,	5),
(114,	4),
(115,	3),
(116,	9),
(116,	10),
(117,	9),
(117,	10),
(119,	2),
(119,	3),
(120,	2),
(120,	3),
(121,	4),
(122,	2),
(122,	3),
(123,	9),
(123,	10),
(124,	5),
(125,	4),
(126,	2),
(127,	5);

DROP TABLE IF EXISTS "subjects";
DROP SEQUENCE IF EXISTS subjects_id_seq;
CREATE SEQUENCE subjects_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."subjects" (
    "id" integer DEFAULT nextval('subjects_id_seq') NOT NULL,
    "name" character varying(255) NOT NULL,
    CONSTRAINT "subjects_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX subjects_name_key ON lab10.subjects USING btree (name);

INSERT INTO "subjects" ("id", "name") VALUES
(1,	'Информационный менеджмент'),
(2,	'Корпоративные информационные системы'),
(3,	'Администрирование информационных систем'),
(4,	'Основы Web-инжиниринга'),
(5,	'Технологии облачных вычислений'),
(6,	'Еккк');

DROP TABLE IF EXISTS "teacher_subjects";
CREATE TABLE "lab10"."teacher_subjects" (
    "teacher_id" integer NOT NULL,
    "subject_id" integer NOT NULL,
    CONSTRAINT "teacher_subjects_pkey" PRIMARY KEY ("teacher_id", "subject_id")
)
WITH (oids = false);

INSERT INTO "teacher_subjects" ("teacher_id", "subject_id") VALUES
(1,	2),
(2,	1),
(3,	1),
(4,	3),
(5,	4),
(6,	1),
(7,	3),
(9,	2),
(10,	2);

DROP TABLE IF EXISTS "teachers";
DROP SEQUENCE IF EXISTS teachers_id_seq;
CREATE SEQUENCE teachers_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."teachers" (
    "id" integer DEFAULT nextval('teachers_id_seq') NOT NULL,
    "person_id" integer NOT NULL,
    CONSTRAINT "teachers_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "teachers" ("id", "person_id") VALUES
(1,	1),
(2,	2),
(3,	3),
(4,	4),
(5,	5),
(6,	6),
(7,	7),
(8,	8),
(9,	9),
(10,	10);

ALTER TABLE ONLY "lab10"."schedule_groups" ADD CONSTRAINT "schedule_groups_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."schedule_groups" ADD CONSTRAINT "schedule_groups_schedule_item_id_fkey" FOREIGN KEY (schedule_item_id) REFERENCES schedule_items(id) ON DELETE CASCADE NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."schedule_items" ADD CONSTRAINT "schedule_items_subject_id_fkey" FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."schedule_teachers" ADD CONSTRAINT "schedule_teachers_schedule_item_id_fkey" FOREIGN KEY (schedule_item_id) REFERENCES schedule_items(id) ON DELETE CASCADE NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."schedule_teachers" ADD CONSTRAINT "schedule_teachers_teacher_id_fkey" FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."teacher_subjects" ADD CONSTRAINT "teacher_subjects_subject_id_fkey" FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."teacher_subjects" ADD CONSTRAINT "teacher_subjects_teacher_id_fkey" FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."teachers" ADD CONSTRAINT "teachers_person_id_fkey" FOREIGN KEY (person_id) REFERENCES people(id) ON DELETE CASCADE NOT DEFERRABLE;

-- 2025-12-15 15:37:52 UTC