https://pastein.ru/t/5pS
Загрузка данных
DROP DATABASE IF EXISTS hotel_db;
CREATE DATABASE hotel_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hotel_db;
-- Отели
CREATE TABLE hotels (
hotel_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(60),
address VARCHAR(200),
stars INT,
phone VARCHAR(30),
UNIQUE KEY uq_hotel_name (name)
) ENGINE=InnoDB;
-- Категории номеров
CREATE TABLE room_types (
type_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
capacity INT,
base_price DECIMAL(10,2),
description VARCHAR(200),
UNIQUE KEY uq_type_name (name)
) ENGINE=InnoDB;
-- Номера
CREATE TABLE rooms (
room_id INT AUTO_INCREMENT PRIMARY KEY,
hotel_id INT NOT NULL,
type_id INT NOT NULL,
number VARCHAR(10),
floor INT,
status VARCHAR(20) DEFAULT 'available',
CONSTRAINT fk_rooms_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_rooms_type FOREIGN KEY (type_id) REFERENCES room_types(type_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
UNIQUE KEY uq_room (hotel_id, number)
) ENGINE=InnoDB;
-- Гости
CREATE TABLE guests (
guest_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
passport VARCHAR(20),
phone VARCHAR(30),
email VARCHAR(100),
country VARCHAR(60),
UNIQUE KEY uq_guest_passport (passport)
) ENGINE=InnoDB;
-- Бронирования
CREATE TABLE bookings (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
guest_id INT NOT NULL,
room_id INT NOT NULL,
check_in DATE NOT NULL,
check_out DATE NOT NULL,
status VARCHAR(20) DEFAULT 'new',
total_price DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_bookings_guest FOREIGN KEY (guest_id) REFERENCES guests(guest_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_bookings_room FOREIGN KEY (room_id) REFERENCES rooms(room_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
-- Услуги
CREATE TABLE services (
service_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80),
price DECIMAL(10,2)
) ENGINE=InnoDB;
-- Доп. услуги в бронировании (M:N)
CREATE TABLE booking_services (
id INT AUTO_INCREMENT PRIMARY KEY,
booking_id INT NOT NULL,
service_id INT NOT NULL,
quantity INT DEFAULT 1,
CONSTRAINT fk_bs_booking FOREIGN KEY (booking_id) REFERENCES bookings(booking_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_bs_service FOREIGN KEY (service_id) REFERENCES services(service_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
-- Данные
INSERT INTO hotels (name,city,address,stars,phone) VALUES
('Grand Hotel','Москва','Тверская 1',5,'+7(495)100-10-10'),
('Sea View','Сочи','Курортный 5',4,'+7(862)200-20-20'),
('Nevsky Palace','СПб','Невский 10',5,'+7(812)300-30-30'),
('Kazan Inn','Казань','Баумана 15',3,'+7(843)400-40-40'),
('Ural Plaza','Екб','Мира 3',4,'+7(343)500-50-50'),
('Siberia','Нск','Ленина 20',4,'+7(383)600-60-60'),
('Rostov Yard','Ростов','Садовая 12',3,'+7(863)700-70-70'),
('Bashkiria','Уфа','Ленина 8',3,'+7(347)800-80-80'),
('Volga','Самара','Победы 4',4,'+7(846)900-90-90'),
('Tula Hotel','Тула','Советская 2',3,'+7(487)111-11-11');
INSERT INTO room_types (name,capacity,base_price,description) VALUES
('Single',1,3000,'Одноместный'),('Double',2,5000,'Двухместный'),
('Twin',2,5200,'Две кровати'),('Suite',3,9000,'Люкс'),
('Family',4,7500,'Семейный'),('Studio',2,6000,'Студия'),
('Deluxe',2,8000,'Делюкс'),('Economy',1,2000,'Эконом'),
('President',4,20000,'Президентский'),('Apartment',3,12000,'Апартаменты');
INSERT INTO rooms (hotel_id,type_id,number,floor,status) VALUES
(1,1,'101',1,'available'),(1,2,'102',1,'available'),(1,4,'201',2,'occupied'),
(2,3,'105',1,'available'),(2,5,'210',2,'available'),(3,6,'301',3,'maintenance'),
(4,7,'401',4,'available'),(5,8,'501',5,'available'),(6,9,'601',6,'available'),
(7,10,'701',7,'available');
INSERT INTO guests (first_name,last_name,passport,phone,email,country) VALUES
('Иван','Петров','4501 111111','+79001111111','petrov@mail.ru','Россия'),
('Анна','Сидорова','4502 222222','+79002222222','sidorova@mail.ru','Россия'),
('Олег','Кузнецов','4503 333333','+79003333333','kuznetsov@mail.ru','Россия'),
('Мария','Иванова','4504 444444','+79004444444','ivanova@mail.ru','Беларусь'),
('Дмитрий','Смирнов','4505 555555','+79005555555','smirnov@mail.ru','Казахстан'),
('Елена','Попова','4506 666666','+79006666666','popova@mail.ru','Россия'),
('Сергей','Васильев','4507 777777','+79007777777','vasiliev@mail.ru','Россия'),
('Ольга','Новикова','4508 888888','+79008888888','novikova@mail.ru','Россия'),
('Павел','Морозов','4509 999999','+79009999999','morozov@mail.ru','Россия'),
('Ирина','Зайцева','4510 000000','+79000000000','zaytseva@mail.ru','Россия');
INSERT INTO bookings (guest_id,room_id,check_in,check_out,status,total_price) VALUES
(1,1,'2024-05-01','2024-05-05','confirmed',12000),
(2,2,'2024-05-02','2024-05-06','confirmed',20000),
(3,3,'2024-05-03','2024-05-07','checked_in',36000),
(4,4,'2024-05-04','2024-05-08','new',20800),
(5,5,'2024-05-05','2024-05-09','confirmed',30000),
(6,6,'2024-05-06','2024-05-10','cancelled',0),
(7,7,'2024-05-07','2024-05-11','confirmed',32000),
(8,8,'2024-05-08','2024-05-12','checked_in',8000),
(9,9,'2024-05-09','2024-05-13','new',80000),
(10,10,'2024-05-10','2024-05-14','confirmed',48000);
INSERT INTO services (name,price) VALUES
('Завтрак',500),('Трансфер',1500),('Спа',3000),('Экскурсия',2500),('Прачечная',800),
('Парковка',300),('Мини-бар',1200),('Ужин',900),('Бассейн',600),('Фитнес',700);
INSERT INTO booking_services (booking_id,service_id,quantity) VALUES
(1,1,2),(1,3,1),(2,2,1),(3,1,3),(4,5,1),(5,4,2),(6,7,1),(7,1,2),(8,9,1),(9,10,3);
DROP DATABASE IF EXISTS clinic_db;
CREATE DATABASE clinic_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE clinic_db;
CREATE TABLE specializations (
spec_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
description VARCHAR(200),
UNIQUE KEY uq_spec_name (name)
) ENGINE=InnoDB;
CREATE TABLE doctors (
doctor_id INT AUTO_INCREMENT PRIMARY KEY,
spec_id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
degree VARCHAR(50),
phone VARCHAR(30),
hire_date DATE,
CONSTRAINT fk_doc_spec FOREIGN KEY (spec_id) REFERENCES specializations(spec_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE patients (
patient_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
birth_date DATE,
gender CHAR(1),
phone VARCHAR(30),
address VARCHAR(200),
insurance VARCHAR(30),
UNIQUE KEY uq_patient_insurance (insurance)
) ENGINE=InnoDB;
CREATE TABLE offices (
office_id INT AUTO_INCREMENT PRIMARY KEY,
number VARCHAR(10),
floor INT,
capacity INT
) ENGINE=InnoDB;
CREATE TABLE appointments (
appt_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
office_id INT NOT NULL,
appt_date DATETIME NOT NULL,
reason VARCHAR(200),
status VARCHAR(20) DEFAULT 'planned',
CONSTRAINT fk_appt_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_appt_doctor FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_appt_office FOREIGN KEY (office_id) REFERENCES offices(office_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE diagnoses (
diagnosis_id INT AUTO_INCREMENT PRIMARY KEY,
appt_id INT NOT NULL,
code VARCHAR(20),
name VARCHAR(200),
notes VARCHAR(300),
CONSTRAINT fk_diag_appt FOREIGN KEY (appt_id) REFERENCES appointments(appt_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE prescriptions (
prescription_id INT AUTO_INCREMENT PRIMARY KEY,
appt_id INT NOT NULL,
medication VARCHAR(100),
dosage VARCHAR(50),
duration_days INT,
CONSTRAINT fk_presc_appt FOREIGN KEY (appt_id) REFERENCES appointments(appt_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO specializations (name,description) VALUES
('Терапевт','Общий приём'),('Хирург','Операции'),('Кардиолог','Сердце'),
('Невролог','Нервная система'),('Педиатр','Дети'),('Стоматолог','Зубы'),
('Окулист','Глаза'),('Дерматолог','Кожа'),('ЛОР','Ухо-горло-нос'),('Эндокринолог','Гормоны');
INSERT INTO doctors (spec_id,first_name,last_name,degree,phone,hire_date) VALUES
(1,'Иван','Петров','к.м.н.','+79001111111','2015-09-01'),
(2,'Анна','Сидорова','д.м.н.','+79002222222','2010-09-01'),
(3,'Олег','Кузнецов','к.м.н.','+79003333333','2016-09-01'),
(4,'Мария','Иванова',NULL,'+79004444444','2018-09-01'),
(5,'Дмитрий','Смирнов','к.м.н.','+79005555555','2014-09-01'),
(6,'Елена','Попова',NULL,'+79006666666','2019-09-01'),
(7,'Сергей','Васильев','к.м.н.','+79007777777','2013-09-01'),
(8,'Ольга','Новикова',NULL,'+79008888888','2020-09-01'),
(9,'Павел','Морозов','к.м.н.','+79009999999','2017-09-01'),
(10,'Ирина','Зайцева','д.м.н.','+79000000000','2008-09-01');
INSERT INTO patients (first_name,last_name,birth_date,gender,phone,address,insurance) VALUES
('Алексей','Александров','1985-05-12','М','+79111111111','ул. Ленина 1','INS001'),
('Екатерина','Борисова','1990-08-23','Ж','+79112222222','ул. Мира 2','INS002'),
('Сергей','Волков','1978-02-14','М','+79113333333','ул. Кирова 3','INS003'),
('Ольга','Григорьева','1995-11-03','Ж','+79114444444','ул. Пушкина 4','INS004'),
('Николай','Дмитриев','1965-07-19','М','+79115555555','пр. Свободный 5','INS005'),
('Татьяна','Егорова','1988-01-25','Ж','+79116666666','ул. Гагарина 6','INS006'),
('Игорь','Жуков','2000-03-11','М','+79117777777','ул. Садовая 7','INS007'),
('Ксения','Зимина','2010-09-30','Ж','+79118888888','ул. Советская 8','INS008'),
('Роман','Ильин','1975-12-05','М','+79119999999','ул. Победы 9','INS009'),
('Анастасия','Крылова','1992-06-18','Ж','+79110000000','ул. Партизан 10','INS010');
INSERT INTO offices (number,floor,capacity) VALUES
('101',1,1),('102',1,1),('201',2,1),('202',2,1),('301',3,1),
('302',3,1),('401',4,1),('402',4,1),('501',5,1),('502',5,1);
INSERT INTO appointments (patient_id,doctor_id,office_id,appt_date,reason,status) VALUES
(1,1,1,'2024-06-01 09:00','Кашель','completed'),
(2,2,2,'2024-06-01 10:00','Боль в колене','completed'),
(3,3,3,'2024-06-02 11:00','Боль в груди','completed'),
(4,4,4,'2024-06-02 12:00','Головная боль','completed'),
(5,5,5,'2024-06-03 09:30','Температура','completed'),
(6,6,6,'2024-06-03 10:30','Зубная боль','completed'),
(7,7,7,'2024-06-04 11:30','Плохое зрение','planned'),
(8,8,8,'2024-06-04 12:30','Сыпь','planned'),
(9,9,9,'2024-06-05 09:00','Боль в ухе','planned'),
(10,10,10,'2024-06-05 10:00','Щитовидка','planned');
INSERT INTO diagnoses (appt_id,code,name,notes) VALUES
(1,'J20','Острый бронхит','Назначен антибиотик'),
(2,'M17','Гонартроз','Рентген колена'),
(3,'I20','Стенокардия','ЭКГ'),
(4,'G43','Мигрень','МРТ'),
(5,'J11','Грипп','Постельный режим'),
(6,'K02','Кариес','Пломбирование'),
(7,'H52','Миопия','Очки'),
(8,'L20','Атопический дерматит','Мазь'),
(9,'H66','Отит','Капли'),
(10,'E03','Гипотиреоз','Гормоны');
INSERT INTO prescriptions (appt_id,medication,dosage,duration_days) VALUES
(1,'Амоксициллин','500 мг 3 раза/день',7),
(2,'Ибупрофен','400 мг 2 раза/день',10),
(3,'Нитроглицерин','1 табл. при боли',30),
(4,'Суматриптан','50 мг при приступе',14),
(5,'Арбидол','200 мг 4 раза/день',5),
(6,'Парацетамол','500 мг 3 раза/день',3),
(7,'Витамин A','1 капля 2 раза/день',30),
(8,'Бепантен','наносить 2 раза/день',14),
(9,'Отипакс','2 капли 3 раза/день',7),
(10,'L-тироксин','50 мкг утром',90);
DROP DATABASE IF EXISTS fitness_db;
CREATE DATABASE fitness_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fitness_db;
CREATE TABLE trainers (
trainer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
specialization VARCHAR(80),
phone VARCHAR(30),
hire_date DATE
) ENGINE=InnoDB;
CREATE TABLE members (
member_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
birth_date DATE,
gender CHAR(1),
phone VARCHAR(30),
email VARCHAR(100),
join_date DATE,
status VARCHAR(20) DEFAULT 'active'
) ENGINE=InnoDB;
CREATE TABLE membership_types (
type_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
duration_days INT,
price DECIMAL(10,2),
description VARCHAR(200),
UNIQUE KEY uq_mt_name (name)
) ENGINE=InnoDB;
CREATE TABLE memberships (
membership_id INT AUTO_INCREMENT PRIMARY KEY,
member_id INT NOT NULL,
type_id INT NOT NULL,
start_date DATE,
end_date DATE,
paid_amount DECIMAL(10,2),
CONSTRAINT fk_mem_member FOREIGN KEY (member_id) REFERENCES members(member_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_mem_type FOREIGN KEY (type_id) REFERENCES membership_types(type_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE classes (
class_id INT AUTO_INCREMENT PRIMARY KEY,
trainer_id INT NOT NULL,
name VARCHAR(80),
day_of_week VARCHAR(20),
start_time TIME,
duration_min INT,
capacity INT,
CONSTRAINT fk_class_trainer FOREIGN KEY (trainer_id) REFERENCES trainers(trainer_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE visits (
visit_id INT AUTO_INCREMENT PRIMARY KEY,
member_id INT NOT NULL,
class_id INT NOT NULL,
visit_date DATE,
attended BOOLEAN DEFAULT TRUE,
CONSTRAINT fk_visit_member FOREIGN KEY (member_id) REFERENCES members(member_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_visit_class FOREIGN KEY (class_id) REFERENCES classes(class_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO trainers (first_name,last_name,specialization,phone,hire_date) VALUES
('Иван','Петров','Йога','+79001111111','2018-09-01'),
('Анна','Сидорова','Пилатес','+79002222222','2019-09-01'),
('Олег','Кузнецов','Бокс','+79003333333','2017-09-01'),
('Мария','Иванова','Аэробика','+79004444444','2020-09-01'),
('Дмитрий','Смирнов','Кроссфит','+79005555555','2016-09-01'),
('Елена','Попова','Зумба','+79006666666','2021-09-01'),
('Сергей','Васильев','Плавание','+79007777777','2015-09-01'),
('Ольга','Новикова','Стретчинг','+79008888888','2019-09-01'),
('Павел','Морозов','Тренажёрный зал','+79009999999','2014-09-01'),
('Ирина','Зайцева','Йога','+79000000000','2018-09-01');
INSERT INTO members (first_name,last_name,birth_date,gender,phone,email,join_date,status) VALUES
('Алексей','Александров','1990-05-12','М','+79111111111','a@mail.ru','2023-01-10','active'),
('Екатерина','Борисова','1992-08-23','Ж','+79112222222','b@mail.ru','2023-02-15','active'),
('Сергей','Волков','1988-02-14','М','+79113333333','v@mail.ru','2023-03-20','active'),
('Ольга','Григорьева','1995-11-03','Ж','+79114444444','g@mail.ru','2023-04-05','active'),
('Николай','Дмитриев','1985-07-19','М','+79115555555','d@mail.ru','2023-05-11','frozen'),
('Татьяна','Егорова','1998-01-25','Ж','+79116666666','e@mail.ru','2023-06-18','active'),
('Игорь','Жуков','2000-03-11','М','+79117777777','j@mail.ru','2023-07-22','active'),
('Ксения','Зимина','1993-09-30','Ж','+79118888888','z@mail.ru','2023-08-30','active'),
('Роман','Ильин','1980-12-05','М','+79119999999','i@mail.ru','2023-09-14','active'),
('Анастасия','Крылова','1997-06-18','Ж','+79110000000','k@mail.ru','2023-10-01','active');
INSERT INTO membership_types (name,duration_days,price,description) VALUES
('Разовый',1,500,'1 посещение'),('Месяц',30,3000,'30 дней'),
('Квартал',90,8000,'3 месяца'),('Год',365,25000,'12 месяцев'),
('Студенческий',30,1500,'Скидка студентам'),('VIP',365,50000,'Безлимит + тренер'),
('Утренний',30,2000,'До 12:00'),('Вечерний',30,2500,'После 18:00'),
('Выходного дня',30,1800,'Сб, Вс'),('Семейный',30,5000,'До 3 человек');
INSERT INTO memberships (member_id,type_id,start_date,end_date,paid_amount) VALUES
(1,2,'2024-01-01','2024-01-31',3000),
(2,3,'2024-01-05','2024-04-04',8000),
(3,4,'2024-01-10','2025-01-09',25000),
(4,5,'2024-02-01','2024-03-02',1500),
(5,6,'2024-02-10','2025-02-09',50000),
(6,7,'2024-03-01','2024-03-31',2000),
(7,8,'2024-03-05','2024-04-04',2500),
(8,9,'2024-03-10','2024-04-09',1800),
(9,10,'2024-04-01','2024-05-01',5000),
(10,2,'2024-04-05','2024-05-05',3000);
INSERT INTO classes (trainer_id,name,day_of_week,start_time,duration_min,capacity) VALUES
(1,'Йога для начинающих','Пн','08:00',60,15),
(2,'Пилатес','Вт','09:00',60,15),
(3,'Бокс','Ср','19:00',90,10),
(4,'Аэробика','Чт','18:00',60,20),
(5,'Кроссфит','Пт','07:00',75,12),
(6,'Зумба','Сб','10:00',60,25),
(7,'Плавание','Вс','11:00',60,10),
(8,'Стретчинг','Пн','20:00',60,15),
(9,'Силовая','Вт','18:00',90,10),
(10,'Йога для продвинутых','Чт','08:00',60,12);
INSERT INTO visits (member_id,class_id,visit_date,attended) VALUES
(1,1,'2024-04-01',TRUE),
(2,2,'2024-04-02',TRUE),
(3,3,'2024-04-03',TRUE),
(4,4,'2024-04-04',FALSE),
(5,5,'2024-04-05',TRUE),
(6,6,'2024-04-06',TRUE),
(7,7,'2024-04-07',TRUE),
(8,8,'2024-04-08',FALSE),
(9,9,'2024-04-09',TRUE),
(10,10,'2024-04-10',TRUE);
DROP DATABASE IF EXISTS boardgames_db;
CREATE DATABASE boardgames_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE boardgames_db;
CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
description VARCHAR(200),
UNIQUE KEY uq_cat_name (name)
) ENGINE=InnoDB;
CREATE TABLE publishers (
publisher_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
country VARCHAR(60),
website VARCHAR(100),
UNIQUE KEY uq_pub_name (name)
) ENGINE=InnoDB;
CREATE TABLE games (
game_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
category_id INT NOT NULL,
publisher_id INT,
min_players INT,
max_players INT,
min_age INT,
play_time_min INT,
complexity DECIMAL(3,1),
CONSTRAINT fk_game_cat FOREIGN KEY (category_id) REFERENCES categories(category_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_game_pub FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE members (
member_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
nickname VARCHAR(50),
phone VARCHAR(30),
email VARCHAR(100),
join_date DATE,
UNIQUE KEY uq_member_nick (nickname)
) ENGINE=InnoDB;
CREATE TABLE tables_ (
table_id INT AUTO_INCREMENT PRIMARY KEY,
number VARCHAR(10),
capacity INT,
location VARCHAR(50)
) ENGINE=InnoDB;
CREATE TABLE sessions (
session_id INT AUTO_INCREMENT PRIMARY KEY,
game_id INT NOT NULL,
table_id INT NOT NULL,
session_date DATETIME,
duration_min INT,
organizer_id INT,
CONSTRAINT fk_sess_game FOREIGN KEY (game_id) REFERENCES games(game_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_sess_table FOREIGN KEY (table_id) REFERENCES tables_(table_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_sess_organizer FOREIGN KEY (organizer_id) REFERENCES members(member_id)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE session_participants (
id INT AUTO_INCREMENT PRIMARY KEY,
session_id INT NOT NULL,
member_id INT NOT NULL,
score INT,
is_winner BOOLEAN DEFAULT FALSE,
CONSTRAINT fk_sp_session FOREIGN KEY (session_id) REFERENCES sessions(session_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_sp_member FOREIGN KEY (member_id) REFERENCES members(member_id)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE KEY uq_sess_member (session_id, member_id)
) ENGINE=InnoDB;
INSERT INTO categories (name,description) VALUES
('Стратегия','Долгие партии'),('Евро','Экономические'),('Америтреш','Тематика и удача'),
('Кооператив','Против игры'),('Пати','Для вечеринок'),('Карточная','Карточные'),
('Детская','Для детей'),('Абстракт','Логика'),('Варгейм','Военные'),
('Дуэль','Для двоих');
INSERT INTO publishers (name,country,website) VALUES
('Hobby World','Россия','hobbyworld.ru'),
('Zvezda','Россия','zvezda.org.ru'),
('Asmodee','Франция','asmodee.com'),
('Days of Wonder','США','daysofwonder.com'),
('Fantasy Flight','США','fantasyflightgames.com'),
('Stonemaier Games','США','stonemaiergames.com'),
('Czech Games Edition','Чехия','czechgames.com'),
('Kosmos','Германия','kosmos.de'),
('Ravensburger','Германия','ravensburger.de'),
('Hasbro','США','hasbro.com');
INSERT INTO games (title,category_id,publisher_id,min_players,max_players,min_age,play_time_min,complexity) VALUES
('Catan',2,8,3,4,10,90,2.3),
('Ticket to Ride',2,4,2,5,8,60,1.9),
('Carcassonne',8,8,2,5,7,45,1.9),
('Pandemic',4,6,2,4,8,45,2.4),
('7 Wonders',2,3,3,7,10,30,2.3),
('Dixit',5,3,3,6,8,30,1.2),
('Munchkin',6,1,3,6,10,90,1.6),
('Uno',6,10,2,10,7,30,1.1),
('Chess',8,2,2,2,6,60,3.5),
('Twilight Struggle',10,5,2,2,13,180,3.6);
INSERT INTO members (first_name,last_name,nickname,phone,email,join_date) VALUES
('Алексей','Александров','Alex','+79111111111','a@mail.ru','2023-01-01'),
('Екатерина','Борисова','Kate','+79112222222','b@mail.ru','2023-01-15'),
('Сергей','Волков','Wolf','+79113333333','v@mail.ru','2023-02-01'),
('Ольга','Григорьева','Olya','+79114444444','g@mail.ru','2023-02-20'),
('Николай','Дмитриев','Nick','+79115555555','d@mail.ru','2023-03-05'),
('Татьяна','Егорова','Tanya','+79116666666','e@mail.ru','2023-03-20'),
('Игорь','Жуков','Igor','+79117777777','j@mail.ru','2023-04-01'),
('Ксения','Зимина','Ksu','+79118888888','z@mail.ru','2023-04-15'),
('Роман','Ильин','Roma','+79119999999','i@mail.ru','2023-05-01'),
('Анастасия','Крылова','Nastya','+79110000000','k@mail.ru','2023-05-20');
INSERT INTO tables_ (number,capacity,location) VALUES
('T1',4,'Зал A'),('T2',4,'Зал A'),('T3',6,'Зал A'),('T4',6,'Зал B'),
('T5',2,'Зал B'),('T6',8,'VIP'),('T7',4,'Зал C'),('T8',4,'Зал C'),
('T9',2,'Зал D'),('T10',6,'Зал D');
INSERT INTO sessions (game_id,table_id,session_date,duration_min,organizer_id) VALUES
(1,1,'2024-06-01 18:00',90,1),
(2,2,'2024-06-02 19:00',60,2),
(3,3,'2024-06-03 17:00',45,3),
(4,4,'2024-06-04 20:00',45,4),
(5,5,'2024-06-05 18:30',30,5),
(6,6,'2024-06-06 19:30',30,6),
(7,7,'2024-06-07 18:00',90,7),
(8,8,'2024-06-08 20:00',30,8),
(9,9,'2024-06-09 17:00',60,9),
(10,10,'2024-06-10 19:00',180,10);
INSERT INTO session_participants (session_id,member_id,score,is_winner) VALUES
(1,1,10,TRUE),(1,2,8,FALSE),(1,3,7,FALSE),
(2,2,120,TRUE),(2,4,100,FALSE),
(3,3,85,FALSE),(3,5,95,TRUE),
(4,4,0,TRUE),(4,6,0,TRUE),
(5,5,55,TRUE),(5,7,50,FALSE),
(6,6,30,TRUE),(6,8,28,FALSE),
(7,7,15,TRUE),(7,9,12,FALSE),
(8,8,500,TRUE),(8,10,300,FALSE),
(9,9,1,TRUE),
(10,10,20,TRUE);
DROP DATABASE IF EXISTS lawfirm_db;
CREATE DATABASE lawfirm_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lawfirm_db;
CREATE TABLE practice_areas (
area_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
description VARCHAR(200),
UNIQUE KEY uq_area_name (name)
) ENGINE=InnoDB;
CREATE TABLE lawyers (
lawyer_id INT AUTO_INCREMENT PRIMARY KEY,
area_id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
license_no VARCHAR(30),
phone VARCHAR(30),
email VARCHAR(100),
hire_date DATE,
hourly_rate DECIMAL(10,2),
CONSTRAINT fk_law_area FOREIGN KEY (area_id) REFERENCES practice_areas(area_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
UNIQUE KEY uq_lawyer_license (license_no)
) ENGINE=InnoDB;
CREATE TABLE clients (
client_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
type VARCHAR(20) DEFAULT 'individual',
phone VARCHAR(30),
email VARCHAR(100),
address VARCHAR(200),
inn VARCHAR(20)
) ENGINE=InnoDB;
CREATE TABLE cases (
case_id INT AUTO_INCREMENT PRIMARY KEY,
client_id INT NOT NULL,
lawyer_id INT NOT NULL,
area_id INT NOT NULL,
case_number VARCHAR(30),
title VARCHAR(200),
open_date DATE,
close_date DATE,
status VARCHAR(20) DEFAULT 'open',
CONSTRAINT fk_case_client FOREIGN KEY (client_id) REFERENCES clients(client_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_case_lawyer FOREIGN KEY (lawyer_id) REFERENCES lawyers(lawyer_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_case_area FOREIGN KEY (area_id) REFERENCES practice_areas(area_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
UNIQUE KEY uq_case_number (case_number)
) ENGINE=InnoDB;
CREATE TABLE hearings (
hearing_id INT AUTO_INCREMENT PRIMARY KEY,
case_id INT NOT NULL,
hearing_date DATETIME,
court VARCHAR(150),
judge VARCHAR(100),
result VARCHAR(200),
CONSTRAINT fk_hearing_case FOREIGN KEY (case_id) REFERENCES cases(case_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE invoices (
invoice_id INT AUTO_INCREMENT PRIMARY KEY,
case_id INT NOT NULL,
amount DECIMAL(12,2),
issued_at DATE,
paid_at DATE,
status VARCHAR(20) DEFAULT 'unpaid',
CONSTRAINT fk_invoice_case FOREIGN KEY (case_id) REFERENCES cases(case_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO practice_areas (name,description) VALUES
('Гражданское право','Споры, договоры'),('Уголовное право','Защита'),
('Корпоративное право','Бизнес'),('Налоговое право','Налоги'),
('Семейное право','Разводы, алименты'),('Трудовое право','Работник/работодатель'),
('Недвижимость','Сделки с недвижимостью'),('Интеллектуальная собственность','Товарные знаки'),
('Административное право','Штрафы, жалобы'),('Международное право','ВЭД');
INSERT INTO lawyers (area_id,first_name,last_name,license_no,phone,email,hire_date,hourly_rate) VALUES
(1,'Иван','Петров','LIC001','+79001111111','petrov@law.ru','2015-09-01',5000),
(2,'Анна','Сидорова','LIC002','+79002222222','sidorova@law.ru','2010-09-01',7000),
(3,'Олег','Кузнецов','LIC003','+79003333333','kuznetsov@law.ru','2016-09-01',6000),
(4,'Мария','Иванова','LIC004','+79004444444','ivanova@law.ru','2018-09-01',5500),
(5,'Дмитрий','Смирнов','LIC005','+79005555555','smirnov@law.ru','2014-09-01',6500),
(6,'Елена','Попова','LIC006','+79006666666','popova@law.ru','2019-09-01',4500),
(7,'Сергей','Васильев','LIC007','+79007777777','vasiliev@law.ru','2013-09-01',8000),
(8,'Ольга','Новикова','LIC008','+79008888888','novikova@law.ru','2020-09-01',4000),
(9,'Павел','Морозов','LIC009','+79009999999','morozov@law.ru','2017-09-01',6000),
(10,'Ирина','Зайцева','LIC010','+79000000000','zaytseva@law.ru','2008-09-01',9000);
INSERT INTO clients (first_name,last_name,type,phone,email,address,inn) VALUES
('Алексей','Александров','individual','+79111111111','a@mail.ru','ул. Ленина 1',NULL),
('ООО Ромашка','-','company','+74951001010','romashka@mail.ru','ул. Мира 10','7701234567'),
('Екатерина','Борисова','individual','+79112222222','b@mail.ru','ул. Мира 2',NULL),
('ООО Лютик','-','company','+74952002020','lutik@mail.ru','ул. Садовая 20','7702345678'),
('Сергей','Волков','individual','+79113333333','v@mail.ru','ул. Кирова 3',NULL),
('ИП Иванов','-','company','+74953003030','ip_ivanov@mail.ru','ул. Пушкина 30','7703456789'),
('Ольга','Григорьева','individual','+79114444444','g@mail.ru','ул. Пушкина 4',NULL),
('ООО Берёза','-','company','+74954004040','bereza@mail.ru','ул. Лесная 40','7704567890'),
('Николай','Дмитриев','individual','+79115555555','d@mail.ru','пр. Свободный 5',NULL),
('ООО Дуб','-','company','+74955005050','dub@mail.ru','ул. Дубовая 50','7705678901');
INSERT INTO cases (client_id,lawyer_id,area_id,case_number,title,open_date,close_date,status) VALUES
(1,1,1,'C-001','Спор о договоре','2024-01-10',NULL,'open'),
(2,2,2,'C-002','Уголовное дело','2024-02-05',NULL,'open'),
(3,3,3,'C-003','Регистрация ООО','2024-02-20','2024-03-15','closed'),
(4,4,4,'C-004','Налоговый спор','2024-03-01',NULL,'open'),
(5,5,5,'C-005','Развод и алименты','2024-03-10',NULL,'open'),
(6,6,6,'C-006','Трудовой спор','2024-03-25','2024-04-10','closed'),
(7,7,7,'C-007','Сделка с недвижимостью','2024-04-01',NULL,'open'),
(8,8,8,'C-008','Регистрация ТЗ','2024-04-15',NULL,'open'),
(9,9,9,'C-009','Обжалование штрафа','2024-05-01','2024-05-20','closed'),
(10,10,10,'C-010','ВЭД-контракт','2024-05-10',NULL,'open');
INSERT INTO hearings (case_id,hearing_date,court,judge,result) VALUES
(1,'2024-02-01 10:00','Арбитражный суд Москвы','Судья А.А.','Отложено'),
(2,'2024-03-01 11:00','Тверской районный суд','Судья Б.Б.','Продолжение'),
(3,'2024-02-25 12:00','ИФНС №1','-','Завершено'),
(4,'2024-04-05 09:00','Арбитражный суд СПб','Судья В.В.','Отложено'),
(5,'2024-04-15 10:30','Мировой суд','Судья Г.Г.','Решение вынесено'),
(6,'2024-04-05 14:00','Районный суд','Судья Д.Д.','Мировое соглашение'),
(7,'2024-05-01 15:00','Росреестр','-','Зарегистрировано'),
(8,'2024-05-15 10:00','Роспатент','-','В процессе'),
(9,'2024-05-10 11:00','Административный суд','Судья Е.Е.','Отменён'),
(10,'2024-06-01 13:00','Арбитражный суд','Судья Ж.Ж.','Отложено');
INSERT INTO invoices (case_id,amount,issued_at,paid_at,status) VALUES
(1,50000,'2024-01-15','2024-01-20','paid'),
(2,70000,'2024-02-10','2024-03-01','paid'),
(3,30000,'2024-02-25','2024-03-15','paid'),
(4,55000,'2024-03-05',NULL,'unpaid'),
(5,65000,'2024-03-15','2024-04-01','paid'),
(6,45000,'2024-03-30','2024-04-10','paid'),
(7,80000,'2024-04-05',NULL,'unpaid'),
(8,40000,'2024-04-20',NULL,'unpaid'),
(9,60000,'2024-05-05','2024-05-20','paid'),
(10,90000,'2024-05-15',NULL,'unpaid');
