keis3.sql - master - GameHacker228/SynergyCase3

keis3.sql
144 строки6 KB
GameHacker228
Create: keis3.sql
24 июл 2026, 18:36
24 июл 2026, 18:368fd69e8
-- ============================================================
-- База данных "Туризм"
-- Кейс-задача №3
-- ============================================================

-- Создаём базу данных
CREATE DATABASE IF NOT EXISTS turizm
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE turizm;

-- ============================================================
-- ТАБЛИЦЫ-СПРАВОЧНИКИ (3 штуки)
-- ============================================================

-- 1. Справочник стран
CREATE TABLE IF NOT EXISTS strany (
    id_strany     INT AUTO_INCREMENT PRIMARY KEY,
    nazvanie      VARCHAR(100) NOT NULL,
    kontinent     VARCHAR(50)  NOT NULL,
    opisanie      TEXT
);

-- 2. Справочник отелей
CREATE TABLE IF NOT EXISTS oteli (
    id_otelya     INT AUTO_INCREMENT PRIMARY KEY,
    nazvanie      VARCHAR(150) NOT NULL,
    zvezdy        TINYINT      NOT NULL CHECK (zvezdy BETWEEN 1 AND 5),
    id_strany     INT          NOT NULL,
    adres         VARCHAR(255),
    tsena_za_noch DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (id_strany) REFERENCES strany(id_strany)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- 3. Справочник клиентов
CREATE TABLE IF NOT EXISTS klienty (
    id_klienta    INT AUTO_INCREMENT PRIMARY KEY,
    familiya      VARCHAR(100) NOT NULL,
    imya          VARCHAR(100) NOT NULL,
    telefon       VARCHAR(20),
    email         VARCHAR(150),
    pasport       VARCHAR(20)  UNIQUE NOT NULL
);

-- 4. Справочник типов туров
CREATE TABLE IF NOT EXISTS tipy_turov (
    id_tipa       INT AUTO_INCREMENT PRIMARY KEY,
    nazvanie      VARCHAR(100) NOT NULL,
    opisanie      TEXT
);

-- ============================================================
-- ТАБЛИЦА ПЕРЕМЕННОЙ ИНФОРМАЦИИ
-- Заказы туров (ссылается на все справочники)
-- ============================================================

CREATE TABLE IF NOT EXISTS zakazy (
    id_zakaza     INT AUTO_INCREMENT PRIMARY KEY,
    id_klienta    INT          NOT NULL,
    id_otelya     INT          NOT NULL,
    id_tipa       INT          NOT NULL,
    data_nachala  DATE         NOT NULL,
    data_kontsa   DATE         NOT NULL,
    kolichestvo_lyudei TINYINT NOT NULL DEFAULT 1,
    obshaya_stoimost   DECIMAL(12,2),
    status        ENUM('Новый','Подтверждён','Оплачен','Отменён') DEFAULT 'Новый',
    data_zakaza   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_klienta) REFERENCES klienty(id_klienta)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (id_otelya) REFERENCES oteli(id_otelya)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (id_tipa) REFERENCES tipy_turov(id_tipa)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- ============================================================
-- ЗАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ
-- ============================================================

-- Страны
INSERT INTO strany (nazvanie, kontinent, opisanie) VALUES
    ('Турция',   'Азия',   'Популярное туристическое направление с тёплым климатом'),
    ('Египет',   'Африка', 'Страна пирамид и курортов Красного моря'),
    ('Испания',  'Европа', 'Богатая культура, Барселона и пляжи Коста Брава'),
    ('Таиланд',  'Азия',   'Экзотические пляжи, буддийские храмы и уличная еда');

-- Отели
INSERT INTO oteli (nazvanie, zvezdy, id_strany, adres, tsena_za_noch) VALUES
    ('Rixos Premium Antalya',  5, 1, 'Белек, Анталья, Турция',   12500.00),
    ('Limak Lara Deluxe',      5, 1, 'Лара, Анталья, Турция',     9800.00),
    ('Steigenberger Al Dau',   5, 2, 'Хургада, Египет',           7200.00),
    ('Iberostar Las Letras',   4, 3, 'Гран-Виа, Мадрид, Испания', 6500.00),
    ('The Sala Samui',         4, 4, 'Ко Самуи, Таиланд',         5400.00);

-- Клиенты
INSERT INTO klienty (familiya, imya, telefon, email, pasport) VALUES
    ('Иванов',   'Сергей',   '+7-900-123-45-67', 'ivanov@mail.ru',    '4520 123456'),
    ('Петрова',  'Мария',    '+7-911-234-56-78', 'petrova@gmail.com', '4521 654321'),
    ('Сидоров',  'Алексей',  '+7-922-345-67-89', 'sidorov@yandex.ru', '4522 111222'),
    ('Козлова',  'Ирина',    '+7-933-456-78-90', 'kozlova@inbox.ru',  '4523 333444');

-- Типы туров
INSERT INTO tipy_turov (nazvanie, opisanie) VALUES
    ('Пляжный',       'Отдых у моря, всё включено'),
    ('Экскурсионный', 'Посещение исторических мест и достопримечательностей'),
    ('Горнолыжный',   'Зимний отдых на горнолыжных курортах'),
    ('Круиз',         'Морской круиз по нескольким странам');

-- Заказы туров
INSERT INTO zakazy (id_klienta, id_otelya, id_tipa, data_nachala, data_kontsa, kolichestvo_lyudei, obshaya_stoimost, status) VALUES
    (1, 1, 1, '2024-07-10', '2024-07-24', 2, 350000.00, 'Оплачен'),
    (2, 3, 2, '2024-08-05', '2024-08-15', 1,  98000.00, 'Подтверждён'),
    (3, 4, 2, '2024-09-01', '2024-09-08', 2, 118000.00, 'Новый'),
    (4, 5, 1, '2024-10-15', '2024-10-25', 3, 194400.00, 'Оплачен'),
    (1, 2, 1, '2024-12-20', '2024-12-30', 2, 215600.00, 'Подтверждён');

-- ============================================================
-- ПОЛЕЗНЫЕ ЗАПРОСЫ ДЛЯ ДЕМОНСТРАЦИИ
-- ============================================================

-- Просмотр всех заказов с именами клиентов, отелей и стран
SELECT
    z.id_zakaza,
    CONCAT(k.familiya, ' ', k.imya)  AS klient,
    o.nazvanie                        AS otel,
    s.nazvanie                        AS strana,
    t.nazvanie                        AS tip_tura,
    z.data_nachala,
    z.data_kontsa,
    z.kolichestvo_lyudei,
    z.obshaya_stoimost,
    z.status
FROM zakazy z
JOIN klienty  k ON z.id_klienta = k.id_klienta
JOIN oteli    o ON z.id_otelya  = o.id_otelya
JOIN strany   s ON o.id_strany  = s.id_strany
JOIN tipy_turov t ON z.id_tipa  = t.id_tipa
ORDER BY z.data_nachala;