devlab11.sql - main - Daibov/web-nodejs-labs

devlab11.sql
289 строк12 KB
Hasagj
lab11 finished
06 май 2026, 20:57
06 май 2026, 20:57c7a3e32
-- Adminer 5.4.2 PostgreSQL 16.13 dump

DROP TABLE IF EXISTS "audience";
DROP SEQUENCE IF EXISTS "lab11".audience_id_seq;
CREATE SEQUENCE "lab11".audience_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."audience" (
    "id" integer DEFAULT nextval('audience_id_seq') NOT NULL,
    "number" integer,
    "frame_id" integer,
    CONSTRAINT "audience_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "audience" ("id", "number", "frame_id") VALUES
(4,	107,	1),
(6,	4532,	1),
(10,	4324,	1),
(1,	5313,	4),
(2,	12561,	3);

DROP TABLE IF EXISTS "categories";
DROP SEQUENCE IF EXISTS "lab11".categories_id_seq;
CREATE SEQUENCE "lab11".categories_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."categories" (
    "id" integer DEFAULT nextval('categories_id_seq') NOT NULL,
    "name" character varying(100) NOT NULL,
    CONSTRAINT "categories_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "categories" ("id", "name") VALUES
(1,	'Смартфоны'),
(2,	'Ноутбуки'),
(3,	'Аксессуары'),
(4,	'Периферия'),
(12,	'Телевизоры');

DROP TABLE IF EXISTS "departments";
DROP SEQUENCE IF EXISTS "lab11"."departmentы_id_seq";
CREATE SEQUENCE "lab11"."departmentы_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."departments" (
    "id" integer DEFAULT nextval('"departmentы_id_seq"') NOT NULL,
    "title" character varying(255),
    CONSTRAINT "departmentы_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "departments" ("id", "title") VALUES
(2,	'Кафедра информационных систем'),
(1,	'Кафедра системного анализа'),
(3,	'Кафедра статистики и прикладной математики'),
(4,	'Кафедра теории и бух.учёта');

DROP TABLE IF EXISTS "disciplines";
DROP SEQUENCE IF EXISTS "lab11".disciplines_id_seq;
CREATE SEQUENCE "lab11".disciplines_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."disciplines" (
    "id" integer DEFAULT nextval('disciplines_id_seq') NOT NULL,
    "title" character varying(255),
    CONSTRAINT "disciplines_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "disciplines" ("id", "title") VALUES
(1,	'Веб-дизайн'),
(2,	'Проектирование информационных систем'),
(3,	'Информатика'),
(4,	'Мультимедиа технологии');

DROP TABLE IF EXISTS "frames";
DROP SEQUENCE IF EXISTS "lab11"."frameы_id_seq";
CREATE SEQUENCE "lab11"."frameы_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."frames" (
    "id" integer DEFAULT nextval('"frameы_id_seq"') NOT NULL,
    "title" character varying(255),
    CONSTRAINT "frameы_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "frames" ("id", "title") VALUES
(1,	'Эконом'),
(2,	'Гидромелиорации'),
(3,	'Главный'),
(4,	'Механизации');

DROP TABLE IF EXISTS "individuals";
DROP SEQUENCE IF EXISTS "lab11".individuals_id_seq;
CREATE SEQUENCE "lab11".individuals_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."individuals" (
    "id" integer DEFAULT nextval('individuals_id_seq') NOT NULL,
    "firstname" character varying(255),
    "lastname" character varying(255),
    "patronymic" character varying(255),
    "position_id" integer NOT NULL,
    CONSTRAINT "individuals_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "individuals" ("id", "firstname", "lastname", "patronymic", "position_id") VALUES
(1,	'Иван',	'Иванов',	'Иванович',	3),
(3,	'Василий',	'Петров',	'Валерьевич',	5),
(2,	'Серпухов',	'Дмитрий',	'Анатольевич',	2),
(4,	'Пётр',	'Сидоров',	'Васильевич',	1);

DROP TABLE IF EXISTS "lessons";
DROP SEQUENCE IF EXISTS "lab11".lessons_id_seq;
CREATE SEQUENCE "lab11".lessons_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."lessons" (
    "id" integer DEFAULT nextval('lessons_id_seq') NOT NULL,
    "discipline_id" integer,
    "audience_id" integer,
    "teacher_id" integer,
    "time" character varying(20),
    "day" character varying(10),
    "week" integer,
    "type" integer,
    "groupa" character varying(10),
    CONSTRAINT "lessons_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "lessons" ("id", "discipline_id", "audience_id", "teacher_id", "time", "day", "week", "type", "groupa") VALUES
(5,	2,	6,	2,	'11:30-13:00',	'Пн',	1,	1,	'ПИ2402'),
(6,	2,	10,	2,	'11:30-13:00',	'Вт',	1,	1,	'ПИ2402'),
(2,	4,	10,	1,	'13:50-15:20',	'Пн',	2,	1,	'ПИ2402');

DROP TABLE IF EXISTS "orders";
DROP SEQUENCE IF EXISTS "lab11".orders_id_seq;
CREATE SEQUENCE "lab11".orders_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."orders" (
    "id" integer DEFAULT nextval('orders_id_seq') NOT NULL,
    "product_id" integer,
    "created_at" timestamp DEFAULT now(),
    "number" integer,
    "count" integer,
    CONSTRAINT "orders_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "orders" ("id", "product_id", "created_at", "number", "count") VALUES
(25,	1,	'2026-04-16 23:20:34.714452',	1,	1),
(26,	2,	'2026-04-16 23:20:34.736576',	1,	1),
(27,	3,	'2026-04-16 23:20:34.75286',	1,	1),
(28,	4,	'2026-04-16 23:20:34.76998',	1,	1),
(29,	1,	'2026-04-16 23:20:55.480678',	2,	1),
(30,	2,	'2026-04-16 23:20:55.495471',	2,	1),
(31,	3,	'2026-04-16 23:20:55.513333',	2,	1),
(32,	4,	'2026-04-16 23:20:55.548196',	2,	1),
(33,	1,	'2026-04-17 07:48:48.359315',	3,	1),
(34,	3,	'2026-04-17 07:48:48.380519',	3,	1),
(35,	5,	'2026-04-17 07:48:48.394364',	3,	1);

DROP TABLE IF EXISTS "positions";
DROP SEQUENCE IF EXISTS "lab11".positions_id_seq;
CREATE SEQUENCE "lab11".positions_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."positions" (
    "id" integer DEFAULT nextval('positions_id_seq') NOT NULL,
    "title" character varying(255),
    CONSTRAINT "positions_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "positions" ("id", "title") VALUES
(2,	'Бугхалтер'),
(1,	'Ст.Преподаватель'),
(3,	'Профессор'),
(4,	'Декан'),
(5,	'Ассистент');

DROP TABLE IF EXISTS "products";
DROP SEQUENCE IF EXISTS "lab11".products_id_seq;
CREATE SEQUENCE "lab11".products_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab11"."products" (
    "id" integer DEFAULT nextval('products_id_seq') NOT NULL,
    "title" character varying(255) NOT NULL,
    "price" integer NOT NULL,
    "amount" integer DEFAULT '0' NOT NULL,
    "category_id" integer,
    "image" text,
    CONSTRAINT "products_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

INSERT INTO "products" ("id", "title", "price", "amount", "category_id", "image") VALUES
(6,	'Samsung Galaxy S24+',	9500000,	10,	1,	NULL),
(8,	'Google Pixel 8 Pro',	9000000,	6,	1,	NULL),
(10,	'Xiaomi 14 Ultra',	11000000,	4,	1,	NULL),
(11,	'Xiaomi Redmi Note 13',	2500000,	50,	1,	NULL),
(12,	'Poco X6 Pro',	3200000,	30,	1,	NULL),
(14,	'Nothing Phone (2)',	6000000,	11,	1,	NULL),
(16,	'ASUS Zenfone 10',	7200000,	5,	1,	NULL),
(17,	'MacBook Pro 16 M3 Max',	35000000,	2,	2,	NULL),
(18,	'MacBook Pro 14 M3',	18000000,	5,	2,	NULL),
(19,	'MacBook Air 15 M2',	13000000,	8,	2,	NULL),
(20,	'MacBook Air 13 M1',	8500000,	15,	2,	NULL),
(21,	'ASUS ROG Strix Scar 18',	32000000,	3,	2,	NULL),
(22,	'ASUS Zenbook 14 OLED',	11000000,	7,	2,	NULL),
(23,	'ASUS TUF Gaming A15',	9500000,	12,	2,	NULL),
(24,	'Lenovo Legion Pro 7',	28000000,	4,	2,	NULL),
(25,	'Lenovo ThinkPad X1 Carbon',	21000000,	6,	2,	NULL),
(26,	'Lenovo IdeaPad Slim 3',	4500000,	30,	2,	NULL),
(27,	'Dell XPS 13 Plus',	19000000,	5,	2,	NULL),
(28,	'HP Spectre x360',	16000000,	4,	2,	NULL),
(29,	'MSI Raider GE78',	29000000,	3,	2,	NULL),
(30,	'Acer Swift Go 14',	8000000,	10,	2,	NULL),
(33,	'Apple AirPods 3',	1700000,	35,	3,	NULL),
(34,	'Sony WH-1000XM5',	3500000,	15,	3,	NULL),
(35,	'Sony WF-1000XM5',	2800000,	20,	3,	NULL),
(36,	'Marshall Major IV',	1300000,	25,	3,	NULL),
(37,	'JBL Flip 6',	1100000,	50,	3,	NULL),
(38,	'Яндекс Станция Макс',	3000000,	10,	3,	NULL),
(39,	'Apple Watch Ultra 2',	8500000,	5,	3,	NULL),
(40,	'Apple Watch Series 9',	4500000,	12,	3,	NULL),
(41,	'Samsung Galaxy Watch 6',	2800000,	15,	3,	NULL),
(42,	'Xiaomi Smart Band 8',	350000,	100,	3,	NULL),
(43,	'Anker PowerBank 737',	1200000,	15,	3,	NULL),
(44,	'Baseus Charger 65W',	300000,	60,	3,	NULL),
(45,	'Кабель Ugreen USB-C',	80000,	200,	3,	NULL),
(46,	'Apple AirTag',	350000,	50,	3,	NULL),
(47,	'Logitech MX Master 3S',	1100000,	20,	4,	NULL),
(48,	'Logitech G Pro X Superlight',	1400000,	15,	4,	NULL),
(49,	'Razer DeathAdder V3',	1300000,	10,	4,	NULL),
(50,	'Keychron K2 Pro',	1200000,	12,	4,	NULL),
(51,	'NuPhy Air75 v2',	1500000,	8,	4,	NULL),
(52,	'Logitech MX Keys S',	1150000,	15,	4,	NULL),
(53,	'Монитор Dell UltraSharp 27',	5500000,	5,	4,	NULL),
(54,	'Монитор LG UltraGear 32',	4200000,	8,	4,	NULL),
(55,	'Монитор Xiaomi Curved 34',	3000000,	10,	4,	NULL),
(56,	'Веб-камера Logitech C920',	800000,	25,	4,	NULL),
(57,	'Веб-камера Razer Kiyo Pro',	1500000,	10,	4,	NULL),
(58,	'Микрофон Blue Yeti',	1400000,	12,	4,	NULL),
(59,	'Микрофон HyperX QuadCast',	1600000,	8,	4,	NULL),
(60,	'Elgato Stream Deck MK.2',	1800000,	6,	4,	NULL),
(15,	'Sony Xperia 1 V',	11500000,	0,	1,	NULL),
(9,	'Google Pixel 7a',	4500000,	8,	1,	NULL),
(31,	'Huawei MateBook X Pro',	14500000,	7,	2,	NULL),
(61,	'Карта захвата AverMedia',	2000000,	5,	4,	NULL),
(13,	'OnePlus 12',	7500000,	4,	1,	NULL),
(2,	'iPhone 15',	8500000,	15,	1,	NULL),
(4,	'iPhone 13 mini',	6000000,	5,	1,	NULL),
(32,	'Apple AirPods Pro 2',	2400000,	40,	3,	NULL),
(1,	'iPhone 15 Pro Max',	15000000,	4,	1,	NULL),
(3,	'iPhone 14',	7000000,	5,	1,	NULL),
(5,	'Samsung Galaxy S24 Ultra',	14000000,	3,	1,	NULL);

DROP TABLE IF EXISTS "teachers";
DROP SEQUENCE IF EXISTS "lab11".teachers_id_seq;
CREATE SEQUENCE "lab11".teachers_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

CREATE TABLE "lab11"."teachers" (
    "id" integer DEFAULT nextval('teachers_id_seq') NOT NULL,
    "individual_id" integer,
    "department_id" integer,
    "experience" integer NOT NULL
)
WITH (oids = false);

INSERT INTO "teachers" ("id", "individual_id", "department_id", "experience") VALUES
(2,	2,	3,	5),
(1,	1,	4,	30),
(2,	4,	1,	7);

ALTER TABLE ONLY "lab11"."audience" ADD CONSTRAINT "audience_frame_id_fkey" FOREIGN KEY (frame_id) REFERENCES frames(id);

ALTER TABLE ONLY "lab11"."individuals" ADD CONSTRAINT "individuals_position_id_fkey" FOREIGN KEY (position_id) REFERENCES positions(id);

ALTER TABLE ONLY "lab11"."lessons" ADD CONSTRAINT "lessons_audience_id_fkey" FOREIGN KEY (audience_id) REFERENCES audience(id);
ALTER TABLE ONLY "lab11"."lessons" ADD CONSTRAINT "lessons_discipline_id_fkey" FOREIGN KEY (discipline_id) REFERENCES disciplines(id);

ALTER TABLE ONLY "lab11"."orders" ADD CONSTRAINT "orders_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id);

ALTER TABLE ONLY "lab11"."products" ADD CONSTRAINT "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id);

ALTER TABLE ONLY "lab11"."teachers" ADD CONSTRAINT "teachers_department_id_fkey" FOREIGN KEY (department_id) REFERENCES departments(id);
ALTER TABLE ONLY "lab11"."teachers" ADD CONSTRAINT "teachers_department_id_fkey1" FOREIGN KEY (department_id) REFERENCES departments(id);
ALTER TABLE ONLY "lab11"."teachers" ADD CONSTRAINT "teachers_individual_id_fkey" FOREIGN KEY (individual_id) REFERENCES individuals(id);
ALTER TABLE ONLY "lab11"."teachers" ADD CONSTRAINT "teachers_individual_id_fkey1" FOREIGN KEY (individual_id) REFERENCES individuals(id);

-- 2026-04-26 21:09:16 UTC