Pastein: -- ============================================================ -- Билет №29. Сельскохозяйственное предприятие -- Разработка, администриро

Загрузка данных
-- ============================================================
-- Билет №29. Сельскохозяйственное предприятие
-- Разработка, администрирование и защита БД
-- MSSQL-скрипт (совместим с SQL Server 2016+)
-- ============================================================

USE master;
GO
-- Если база уже есть – удалим (осторожно!)
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'SeloPro')
    DROP DATABASE SeloPro;
GO

CREATE DATABASE SeloPro;
GO

USE SeloPro;
GO

-- ============================================================
-- 1. СОЗДАНИЕ ТАБЛИЦ
-- ============================================================

-- Таблица "Поля"
CREATE TABLE Fields (
    FieldID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    AreaHa DECIMAL(10,2) NOT NULL CHECK (AreaHa > 0),
    Location NVARCHAR(200),
    SoilType NVARCHAR(50),
    Owner NVARCHAR(100) DEFAULT 'Хозяйство'
);
GO

-- Таблица "Культуры"
CREATE TABLE Crops (
    CropID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL UNIQUE,
    CropType NVARCHAR(50), -- зерновые, овощи, кормовые и т.п.
    AvgYieldCentnerPerHa DECIMAL(8,2) CHECK (AvgYieldCentnerPerHa >= 0),
    PlantingSeason NVARCHAR(50)
);
GO

-- Таблица "Сотрудники"
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(150) NOT NULL,
    Position NVARCHAR(100) NOT NULL,
    HireDate DATE DEFAULT GETDATE(),
    ExperienceYears INT CHECK (ExperienceYears >= 0),
    Phone NVARCHAR(20),
    Email NVARCHAR(100) UNIQUE
);
GO

-- Таблица "Сельскохозяйственная техника"
CREATE TABLE Machinery (
    MachineryID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Type NVARCHAR(50) NOT NULL,
    InventoryNumber NVARCHAR(50) UNIQUE NOT NULL,
    ProductionYear INT CHECK (ProductionYear BETWEEN 1900 AND YEAR(GETDATE())),
    Status NVARCHAR(20) DEFAULT 'Исправна' CHECK (Status IN ('Исправна', 'В ремонте', 'Списана')),
    LastMaintenance DATE
);
GO

-- Таблица "Сбор урожая"
CREATE TABLE Harvest (
    HarvestID INT IDENTITY(1,1) PRIMARY KEY,
    FieldID INT NOT NULL,
    CropID INT NOT NULL,
    EmployeeID INT NOT NULL,      -- ответственный
    MachineryID INT NULL,         -- техника, которая использовалась (может быть NULL)
    HarvestDate DATE NOT NULL DEFAULT GETDATE(),
    HarvestedCentners DECIMAL(10,2) NOT NULL CHECK (HarvestedCentners >= 0),
    QualityGrade NVARCHAR(20) CHECK (QualityGrade IN ('Высший', 'Первый', 'Второй', 'Стандарт', NULL)),
    Notes NVARCHAR(500),

    -- Внешние ключи
    CONSTRAINT FK_Harvest_Field FOREIGN KEY (FieldID) REFERENCES Fields(FieldID),
    CONSTRAINT FK_Harvest_Crop FOREIGN KEY (CropID) REFERENCES Crops(CropID),
    CONSTRAINT FK_Harvest_Employee FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    CONSTRAINT FK_Harvest_Machinery FOREIGN KEY (MachineryID) REFERENCES Machinery(MachineryID)
);
GO

-- Индексы для ускорения выборок
CREATE INDEX IX_Harvest_Date ON Harvest(HarvestDate);
CREATE INDEX IX_Harvest_FieldCrop ON Harvest(FieldID, CropID);
GO

-- ============================================================
-- 2. НАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ (минимум 5 записей в каждой таблице)
-- ============================================================

-- Поля
INSERT INTO Fields (Name, AreaHa, Location, SoilType)
VALUES
    ('Северное поле', 120.5, 'д. Ивановка, северный участок', 'Чернозём'),
    ('Южное поле', 85.3, 'д. Ивановка, южный участок', 'Суглинок'),
    ('Западное поле', 95.0, 'д. Заречье, запад', 'Песчаная'),
    ('Восточное поле', 110.2, 'д. Заречье, восток', 'Глинистая'),
    ('Приозёрное поле', 60.7, 'оз. Светлое', 'Торфяная'),
    ('Экспериментальное', 30.0, 'территория фермы', 'Чернозём');
GO

-- Культуры
INSERT INTO Crops (Name, CropType, AvgYieldCentnerPerHa, PlantingSeason)
VALUES
    ('Пшеница яровая', 'Зерновые', 40.5, 'Весна'),
    ('Озимая рожь', 'Зерновые', 35.2, 'Осень'),
    ('Картофель', 'Овощи', 180.0, 'Весна'),
    ('Кукуруза', 'Зерновые', 55.0, 'Весна'),
    ('Лён', 'Технические', 12.3, 'Весна'),
    ('Соя', 'Зернобобовые', 25.6, 'Весна');
GO

-- Сотрудники
INSERT INTO Employees (FullName, Position, HireDate, ExperienceYears, Phone, Email)
VALUES
    ('Иванов Иван Иванович', 'Агроном', '2010-03-15', 14, '+7-911-111-1111', 'ivanov@selo.ru'),
    ('Петров Пётр Петрович', 'Тракторист', '2015-06-20', 9, '+7-911-222-2222', 'petrov@selo.ru'),
    ('Сидорова Анна Сергеевна', 'Агроном-технолог', '2018-09-01', 6, '+7-911-333-3333', 'sidorova@selo.ru'),
    ('Смирнов Алексей Дмитриевич', 'Водитель комбайна', '2012-05-10', 12, '+7-911-444-4444', 'smirnov@selo.ru'),
    ('Козлова Елена Викторовна', 'Зоотехник', '2020-01-20', 4, '+7-911-555-5555', 'kozlova@selo.ru'),
    ('Михайлов Денис Сергеевич', 'Механик', '2016-11-01', 8, '+7-911-666-6666', 'mikhailov@selo.ru');
GO

-- Техника
INSERT INTO Machinery (Name, Type, InventoryNumber, ProductionYear, Status, LastMaintenance)
VALUES
    ('МТЗ-82', 'Трактор', 'TR-001', 2018, 'Исправна', '2025-03-10'),
    ('КЗС-1218', 'Комбайн', 'CM-002', 2019, 'Исправна', '2025-02-15'),
    ('ЗИЛ-130', 'Грузовик', 'TRK-003', 2009, 'В ремонте', '2024-12-01'),
    ('Плуг ПН-4-35', 'Навесное', 'PL-004', 2020, 'Исправна', '2025-04-01'),
    ('Сеялка СЗ-5.4', 'Навесное', 'SE-005', 2021, 'Исправна', '2025-03-20'),
    ('Культиватор КПС-4', 'Навесное', 'KU-006', 2022, 'Исправна', '2025-03-25');
GO

-- Сбор урожая (связываем все таблицы)
INSERT INTO Harvest (FieldID, CropID, EmployeeID, MachineryID, HarvestDate, HarvestedCentners, QualityGrade, Notes)
VALUES
    (1, 1, 1, 1, '2025-07-20', 4850.0, 'Высший', 'Урожай отличный, без потерь'),
    (2, 2, 3, 2, '2025-08-05', 2870.0, 'Первый', 'Снижение из-за засухи'),
    (1, 3, 2, 5, '2025-08-15', 21500.0, 'Стандарт', 'Картофель чистый'),
    (3, 4, 4, 6, '2025-08-25', 5200.0, 'Высший', 'Отличная кукуруза'),
    (4, 1, 1, 1, '2025-07-28', 4100.0, 'Второй', 'Слегка полегла'),
    (5, 5, 2, 1, '2025-08-10', 700.0, 'Стандарт', 'Лён хороший'),
    (6, 6, 3, 4, '2025-08-30', 750.0, 'Первый', 'Соя, первый год');
GO

-- Добавим ещё по 1 записи для выполнения условий минимум 5, хотя уже больше
-- Дополним, чтобы было не менее 5 в каждой таблице (у нас везде ≥ 6)
GO

-- ============================================================
-- 3. ЗАПРОСЫ (все в одном скрипте)
-- ============================================================

-- 3.1 Простой запрос с условием: получить все поля площадью более 100 га
SELECT FieldID, Name, AreaHa, Location
FROM Fields
WHERE AreaHa > 100
ORDER BY AreaHa DESC;
GO

-- 3.2 Запрос с соединением таблиц: получить данные о сборе с названиями полей, культур и ответственных сотрудников
SELECT 
    h.HarvestDate,
    f.Name AS FieldName,
    c.Name AS CropName,
    e.FullName AS ResponsibleEmployee,
    h.HarvestedCentners,
    h.QualityGrade
FROM Harvest h
    INNER JOIN Fields f ON h.FieldID = f.FieldID
    INNER JOIN Crops c ON h.CropID = c.CropID
    INNER JOIN Employees e ON h.EmployeeID = e.EmployeeID
WHERE h.HarvestedCentners > 1000
ORDER BY h.HarvestDate DESC;
GO

-- 3.3 Запрос с группировкой и агрегатной функцией: общий собранный урожай по культурам (в центнерах)
SELECT 
    c.Name AS CropName,
    SUM(h.HarvestedCentners) AS TotalCentners,
    AVG(h.HarvestedCentners) AS AvgPerHarvest,
    COUNT(*) AS HarvestCount
FROM Harvest h
    INNER JOIN Crops c ON h.CropID = c.CropID
GROUP BY c.Name
ORDER BY TotalCentners DESC;
GO

-- 3.4 Запрос с подзапросом: найти сотрудников, которые собрали больше среднего за один сбор
SELECT DISTINCT e.FullName, e.Position
FROM Employees e
WHERE e.EmployeeID IN (
    SELECT h.EmployeeID
    FROM Harvest h
    GROUP BY h.EmployeeID
    HAVING AVG(h.HarvestedCentners) > (SELECT AVG(HarvestedCentners) FROM Harvest)
);
GO

-- 3.5 Запрос на обновление данных: повысить квалификацию (опыт) на 1 год всем сотрудникам, участвовавшим в сборе после 2025-08-01
UPDATE Employees
SET ExperienceYears = ExperienceYears + 1
WHERE EmployeeID IN (
    SELECT DISTINCT EmployeeID 
    FROM Harvest 
    WHERE HarvestDate > '2025-08-01'
);
-- Посмотрим результат
SELECT FullName, ExperienceYears FROM Employees WHERE EmployeeID IN (SELECT DISTINCT EmployeeID FROM Harvest WHERE HarvestDate > '2025-08-01');
GO

-- ============================================================
-- 4. КРАТКОЕ РУКОВОДСТВО ПОЛЬЗОВАТЕЛЯ
-- ============================================================
/*
РУКОВОДСТВО ПО РАБОТЕ С БАЗОЙ ДАННЫХ «СЕЛЬХОЗПРЕДПРИЯТИЕ»

1. НАЗНАЧЕНИЕ
   База данных предназначена для учёта полей, культур, сотрудников, техники и сборов урожая в сельскохозяйственном предприятии.

2. ОСНОВНЫЕ ТАБЛИЦЫ
   - Fields – информация о полях (название, площадь, местоположение, тип почвы).
   - Crops – культуры (название, тип, средняя урожайность, сезон посадки).
   - Employees – сотрудники (ФИО, должность, стаж, контакты).
   - Machinery – техника (название, тип, инвентарный номер, состояние, дата последнего ТО).
   - Harvest – записи о сборе урожая: связывает поле, культуру, ответственного сотрудника, технику, дату, объём собранного (ц), качество, примечания.

3. ОСНОВНЫЕ ДЕЙСТВИЯ
   - Добавление данных: использовать INSERT INTO для соответствующей таблицы.
   - Просмотр: SELECT с фильтрами (WHERE), сортировкой (ORDER BY), объединениями (JOIN).
   - Обновление: UPDATE с условием (WHERE) для изменения записей.
   - Удаление: DELETE (с осторожностью, используя транзакции).

4. ВАЖНЫЕ ОГРАНИЧЕНИЯ
   - Площадь поля (AreaHa) должна быть > 0.
   - Инвентарный номер техники уникален.
   - Стаж сотрудников (ExperienceYears) не может быть отрицательным.
   - Собранный урожай (HarvestedCentners) >= 0.
   - Качество урожая – только из списка: 'Высший','Первый','Второй','Стандарт' (может быть NULL).
   - Все внешние ключи обеспечивают целостность: нельзя добавить сбор с несуществующим полем, культурой и т.д.

5. ПРИМЕРЫ ПОЛЕЗНЫХ ЗАПРОСОВ (помимо приведённых)
   - Поля по типу почвы: SELECT * FROM Fields WHERE SoilType = 'Чернозём';
   - Техника в ремонте: SELECT * FROM Machinery WHERE Status = 'В ремонте';
   - Сбор по датам: SELECT * FROM Harvest WHERE HarvestDate BETWEEN '2025-07-01' AND '2025-07-31';
   - Общий урожай по годам: SELECT YEAR(HarvestDate) AS Year, SUM(HarvestedCentners) FROM Harvest GROUP BY YEAR(HarvestDate);

6. АДМИНИСТРИРОВАНИЕ
   - Резервное копирование: BACKUP DATABASE SeloPro TO DISK = 'путь';
   - Восстановление: RESTORE DATABASE SeloPro FROM DISK = 'путь';
   - Индексы созданы для ускорения; при больших объёмах можно добавить индексы на часто используемые столбцы.

7. БЕЗОПАСНОСТЬ
   - Рекомендуется создавать роли пользователей с минимально необходимыми правами (например, только чтение для отчётов).
   - Использовать параметризованные запросы в приложениях для защиты от SQL-инъекций.

8. ПРИМЕР ВСТАВКИ НОВОГО СБОРА
   INSERT INTO Harvest (FieldID, CropID, EmployeeID, MachineryID, HarvestDate, HarvestedCentners, QualityGrade, Notes)
   VALUES (1, 2, 3, 1, GETDATE(), 3200.0, 'Первый', 'Урожай собран вовремя');
*/
GO
Больше возможностей при регистрации:
