Pastein: -- ============================================================ -- ER-ДИАГРАММА: УЧЁТ ВЫПУСКА ПРОДУКЦИИ НА УЧАСТКЕ МОНТАЖА ПЛАТ -- СУБД: PostgreS

Загрузка данных
-- ============================================================
-- ER-ДИАГРАММА: УЧЁТ ВЫПУСКА ПРОДУКЦИИ НА УЧАСТКЕ МОНТАЖА ПЛАТ
-- СУБД: PostgreSQL
-- ============================================================

-- ------------------------------------------------------------
-- УДАЛЕНИЕ СТАРЫХ ТАБЛИЦ (если есть)
-- ------------------------------------------------------------
DROP TABLE IF EXISTS Выпуск_продукции CASCADE;
DROP TABLE IF EXISTS Партия CASCADE;
DROP TABLE IF EXISTS Сотрудник CASCADE;
DROP TABLE IF EXISTS Продукция CASCADE;
DROP TABLE IF EXISTS Участок CASCADE;


-- ============================================================
-- БЛОК 1. СОЗДАНИЕ ТАБЛИЦ (без связей)
-- ============================================================

-- 1.1. Участок
CREATE TABLE Участок (
    id_участка    SERIAL PRIMARY KEY,
    наименование  VARCHAR(100) NOT NULL,
    руководитель  VARCHAR(150),
    смена         VARCHAR(20)
);

-- 1.2. Продукция
CREATE TABLE Продукция (
    id_продукции  SERIAL PRIMARY KEY,
    наименование  VARCHAR(150) NOT NULL,
    артикул       VARCHAR(50) UNIQUE,
    тип_платы     VARCHAR(100),
    норма_времени DECIMAL(6,2)
);

-- 1.3. Сотрудник
CREATE TABLE Сотрудник (
    id_сотрудника   SERIAL PRIMARY KEY,
    ФИО             VARCHAR(150) NOT NULL,
    табельный_номер VARCHAR(20) UNIQUE,
    разряд          INT,
    id_участка      INT
);

-- 1.4. Партия
CREATE TABLE Партия (
    id_партии       SERIAL PRIMARY KEY,
    номер_партии    VARCHAR(50) UNIQUE,
    дата_запуска    DATE,
    id_продукции    INT,
    план_количество INT,
    факт_количество INT
);

-- 1.5. Выпуск_продукции
CREATE TABLE Выпуск_продукции (
    id_выпуска      SERIAL PRIMARY KEY,
    id_продукции    INT,
    id_сотрудника   INT,
    id_партии       INT,
    дата            DATE NOT NULL,
    количество      INT NOT NULL,
    брак            INT DEFAULT 0,
    статус          VARCHAR(20) DEFAULT 'принято'
);


-- ============================================================
-- БЛОК 2. СВЯЗИ (внешние ключи)
-- ============================================================

-- Связь 1. Сотрудник → Участок
-- (на участке работает много сотрудников)
ALTER TABLE Сотрудник
    ADD CONSTRAINT fk_сотрудник_участок
    FOREIGN KEY (id_участка) REFERENCES Участок(id_участка);

-- Связь 2. Партия → Продукция
-- (партия формируется под конкретное изделие)
ALTER TABLE Партия
    ADD CONSTRAINT fk_партия_продукция
    FOREIGN KEY (id_продукции) REFERENCES Продукция(id_продукции);

-- Связь 3. Выпуск_продукции → Продукция
-- (что выпущено)
ALTER TABLE Выпуск_продукции
    ADD CONSTRAINT fk_выпуск_продукция
    FOREIGN KEY (id_продукции) REFERENCES Продукция(id_продукции);

-- Связь 4. Выпуск_продукции → Сотрудник
-- (кто выпустил)
ALTER TABLE Выпуск_продукции
    ADD CONSTRAINT fk_выпуск_сотрудник
    FOREIGN KEY (id_сотрудника) REFERENCES Сотрудник(id_сотрудника);

-- Связь 5. Выпуск_продукции → Партия
-- (из какой партии)
ALTER TABLE Выпуск_продукции
    ADD CONSTRAINT fk_выпуск_партия
    FOREIGN KEY (id_партии) REFERENCES Партия(id_партии);


-- ============================================================
-- БЛОК 3. ЗАПОЛНЕНИЕ ДАННЫМИ (по 5 записей)
-- ============================================================

-- 3.1. Участок (5 записей)
INSERT INTO Участок (наименование, руководитель, смена) VALUES
('Участок монтажа №1',         'Иванов И.И.',    'Первая'),
('Участок монтажа №2',         'Петров П.П.',    'Вторая'),
('Участок пайки SMD',          'Сидоров С.С.',   'Первая'),
('Участок выходного контроля', 'Кузнецова А.А.', 'Вторая'),
('Участок сборки изделий',     'Морозов М.М.',   'Третья');

-- 3.2. Продукция (5 записей)
INSERT INTO Продукция (наименование, артикул, тип_платы, норма_времени) VALUES
('Контроллер управления', 'CTRL-001', 'Двухсторонняя', 45.50),
('Плата питания',         'PWR-002',  'Однослойная',   30.00),
('Модуль связи',          'COM-003',  'Многослойная',  60.25),
('Датчик температуры',    'TEMP-004', 'Однослойная',   25.00),
('Блок индикации',        'IND-005',  'Двухсторонняя', 40.00);

-- 3.3. Сотрудник (5 записей)
INSERT INTO Сотрудник (ФИО, табельный_номер, разряд, id_участка) VALUES
('Алексеев Алексей Алексеевич', 'T-1001', 3, 1),
('Борисова Бэла Борисовна',     'T-1002', 4, 2),
('Волков Виктор Викторович',    'T-1003', 5, 3),
('Громова Галина Григорьевна',  'T-1004', 2, 4),
('Дмитриев Дмитрий Дмитриевич', 'T-1005', 4, 5);

-- 3.4. Партия (5 записей)
INSERT INTO Партия (номер_партии, дата_запуска, id_продукции, план_количество, факт_количество) VALUES
('P-2024-001', '2024-01-10', 1, 100, 98),
('P-2024-002', '2024-01-15', 2, 200, 195),
('P-2024-003', '2024-01-20', 3, 150, 150),
('P-2024-004', '2024-02-01', 4, 300, 290),
('P-2024-005', '2024-02-10', 5, 120, 118);

-- 3.5. Выпуск_продукции (5 записей)
INSERT INTO Выпуск_продукции (id_продукции, id_сотрудника, id_партии, дата, количество, брак, статус) VALUES
(1, 1, 1, '2024-01-12', 50,  2,  'принято'),
(2, 2, 2, '2024-01-17', 100, 5,  'принято'),
(3, 3, 3, '2024-01-22', 75,  0,  'принято'),
(4, 4, 4, '2024-02-03', 150, 10, 'отклонено'),
(5, 5, 5, '2024-02-12', 60,  2,  'принято');


-- ============================================================
-- БЛОК 4. ПРОВЕРКА СВЯЗЕЙ
-- ============================================================

-- 4.1. Посмотреть все внешние ключи в базе
SELECT
    tc.table_name        AS таблица,
    kcu.column_name      AS колонка,
    ccu.table_name       AS связана_с_таблицей,
    ccu.column_name      AS связана_с_колонкой
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name;


-- ============================================================
-- БЛОК 5. АНАЛИТИЧЕСКИЕ ЗАПРОСЫ
-- ============================================================

-- 5.1. Все записи выпуска с расшифровкой
SELECT
    в.id_выпуска,
    п.наименование AS изделие,
    п.артикул,
    с.ФИО          AS сотрудник,
    у.наименование AS участок,
    па.номер_партии,
    в.дата,
    в.количество,
    в.брак,
    в.статус
FROM Выпуск_продукции в
JOIN Продукция   п  ON п.id_продукции  = в.id_продукции
JOIN Сотрудник   с  ON с.id_сотрудника = в.id_сотрудника
JOIN Участок     у  ON у.id_участка    = с.id_участка
JOIN Партия      па ON па.id_партии    = в.id_партии
ORDER BY в.дата;

-- 5.2. Итоги по участкам
SELECT
    у.наименование AS участок,
    SUM(в.количество) AS всего_выпущено,
    SUM(в.брак)       AS всего_брака,
    ROUND(100.0 * SUM(в.брак) / NULLIF(SUM(в.количество), 0), 2) AS процент_брака
FROM Выпуск_продукции в
JOIN Сотрудник с ON с.id_сотрудника = в.id_сотрудника
JOIN Участок   у ON у.id_участка    = с.id_участка
GROUP BY у.наименование
ORDER BY процент_брака DESC;

-- 5.3. Выполнение плана по партиям
SELECT
    па.номер_партии,
    п.наименование AS изделие,
    па.план_количество,
    па.факт_количество,
    ROUND(100.0 * па.факт_количество / NULLIF(па.план_количество, 0), 2) AS процент_выполнения
FROM Партия па
JOIN Продукция п ON п.id_продукции = па.id_продукции
ORDER BY па.дата_запуска;

-- 5.4. Выработка по сотрудникам
SELECT
    с.ФИО,
    с.табельный_номер,
    у.наименование AS участок,
    SUM(в.количество) AS всего_выпущено,
    SUM(в.брак)       AS брак
FROM Выпуск_продукции в
JOIN Сотрудник с ON с.id_сотрудника = в.id_сотрудника
JOIN Участок   у ON у.id_участка    = с.id_участка
GROUP BY с.ФИО, с.табельный_номер, у.наименование
ORDER BY всего_выпущено DESC;

-- ============================================================
-- КОНЕЦ СКРИПТА
-- ============================================================
Больше возможностей при регистрации:
