ЛР1
.docx
Порядок выполнения работы:
Создать в СУБД (MySql) новую схему данных для хранения пользовательских объектов.
В этой новой схеме данных с помощью запросов на языке DDL SQL реализовать таблицы, соответствующие реляционным отношениям схемы
данных полученной в контрольной работе с помощью оператора CREATE
TABLE.
3) Заполнить созданные таблицы строками данных для проверки правильного
выбора первичных ключей и работоспособности ссылок между таблицами.
Для проектирования схемы базы данных воспользуемся инструментом MySql Workbench.
Рисунок 1. UML-диаграмма «Бассейн»
Создание схемы данных «Swimmingpool»:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `swimmingpool` DEFAULT CHARACTER SET utf8 ;
USE `swimmingpool` ;
Создание таблицы «Группа»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Группа` (
`Номер_группы` INT NOT NULL AUTO_INCREMENT,
`Вид_группы` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Номер_группы`))
ENGINE = InnoDB;
Создание таблицы «Бассейн»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Бассейн` (
`Номер_бассейна` INT NOT NULL AUTO_INCREMENT,
`Название` VARCHAR(45) NOT NULL,
`Длина_дорожки` INT NOT NULL,
`Глубина` INT NOT NULL,
PRIMARY KEY (`Номер_бассейна`))
ENGINE = InnoDB;
Создание таблицы «Должность»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Должность` (
`Номер_должности` INT NOT NULL AUTO_INCREMENT,
`Название` VARCHAR(45) NOT NULL,
`Трудовой_стаж` INT NOT NULL,
PRIMARY KEY (`Номер_должности`))
ENGINE = InnoDB;
Создание таблицы «Сотрудник»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Сотрудник` (
`Номер_сотрудника` INT NOT NULL AUTO_INCREMENT,
`Номер_должности` INT NOT NULL,
`ФИО` VARCHAR(45) NOT NULL,
`Номер_паспорта` INT NOT NULL,
PRIMARY KEY (`Номер_сотрудника`),
INDEX `Должность_idx` (`Номер_должности` ASC),
CONSTRAINT `Должность`
FOREIGN KEY (`Номер_должности`)
REFERENCES `swimmingpool`.`Должность` (`Номер_должности`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
Создание таблицы «Посетитель»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Посетитель` (
`Номер_посетителя` INT NOT NULL,
`Номер_группы` INT NOT NULL,
`Номер_сотрудника` INT NOT NULL,
`ФИО` VARCHAR(45) NOT NULL,
`№абонемента` INT NOT NULL,
PRIMARY KEY (`Номер_посетителя`),
INDEX `Группа_idx` (`Номер_группы` ASC),
INDEX `Сотрудник_idx` (`Номер_сотрудника` ASC),
CONSTRAINT `Группа`
FOREIGN KEY (`Номер_группы`)
REFERENCES `swimmingpool`.`Группа` (`Номер_группы`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Сотрудник`
FOREIGN KEY (`Номер_сотрудника`)
REFERENCES `swimmingpool`.`Сотрудник` (`Номер_сотрудника`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
Создание таблицы «Услуги бассейна»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Услуги_бассейна` (
`Номер_услуги_бассейна` INT NOT NULL AUTO_INCREMENT,
`Вид_занятий` VARCHAR(45) NOT NULL,
`Наличие_тренера` VARCHAR(45) NOT NULL,
`Аренда_дорожек` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Номер_услуги_бассейна`))
ENGINE = InnoDB;
Создание таблицы «Вид услуги»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Вид_услуги` (
`№недели` INT NOT NULL,
`День_недели` INT NOT NULL,
`Номер_группы` INT NOT NULL,
`Номер_вида_услуги` INT NOT NULL AUTO_INCREMENT,
`Номер_бассейна` INT NOT NULL,
`Номер_услуги_бассейна` INT NOT NULL,
PRIMARY KEY (`Номер_вида_услуги`),
INDEX `Группа_idx` (`Номер_группы` ASC),
INDEX `Номер_ бассейна_idx` (`Номер_бассейна` ASC),
INDEX `Номер_вида_услуги_idx` (`Номер_услуги_бассейна` ASC),
CONSTRAINT `Номер_группы`
FOREIGN KEY (`Номер_группы`)
REFERENCES `swimmingpool`.`Группа` (`Номер_группы`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Номер_бассейна`
FOREIGN KEY (`Номер_бассейна`)
REFERENCES `swimmingpool`.`Бассейн` (`Номер_бассейна`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Номер_услуги_бассейна`
FOREIGN KEY (`Номер_услуги_бассейна`)
REFERENCES `swimmingpool`.`Услуги_бассейна` (`Номер_услуги_бассейна`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
Создание таблицы «Время посещения»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Время_посещения` (
`Номер_посетителя` INT NOT NULL,
`Номер_сотрудника` INT NOT NULL,
`Номер_бассейна` INT NOT NULL,
`Дата` DATETIME NOT NULL,
`Время` DATETIME NOT NULL,
`Номер_времени_посещения` INT NOT NULL AUTO_INCREMENT,
INDEX `Номер_сотрудника_idx` (`Номер_сотрудника` ASC),
INDEX `Номер_бассейна_idx` (`Номер_бассейна` ASC),
PRIMARY KEY (`Номер_времени_посещения`),
CONSTRAINT `Номер_посетителя`
FOREIGN KEY (`Номер_посетителя`)
REFERENCES `swimmingpool`.`Посетитель` (`Номер_посетителя`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Номер_сотрудника_3`
FOREIGN KEY (`Номер_сотрудника`)
REFERENCES `swimmingpool`.`Сотрудник` (`Номер_сотрудника`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Номер_бассейна_3`
FOREIGN KEY (`Номер_бассейна`)
REFERENCES `swimmingpool`.`Бассейн` (`Номер_бассейна`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
Создание таблицы «Работает»:
CREATE TABLE IF NOT EXISTS `swimmingpool`.`Работает` (
`Номер_бассейна` INT NOT NULL,
`Номер_сотрудника` INT NOT NULL,
PRIMARY KEY (`Номер_бассейна`, `Номер_сотрудника`),
INDEX `Номер_сотрудника_idx` (`Номер_сотрудника` ASC),
CONSTRAINT `Номер_бассейна_2`
FOREIGN KEY (`Номер_бассейна`)
REFERENCES `swimmingpool`.`Бассейн` (`Номер_бассейна`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Номер_сотрудника_2`
FOREIGN KEY (`Номер_сотрудника`)
REFERENCES `swimmingpool`.`Сотрудник` (`Номер_сотрудника`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
Заполнение данными таблицы «Бассейн»:
use swimmingpool;
INSERT INTO бассейн
VALUES (1,'Дельфин', '100','10'),
(2,'Касатка', '200','15'),
(3,'Кенгуру', '50','12'),
(4,'Заяц', '70','17');
Заполнение данными таблицы «Группа»:
INSERT INTO группа
VALUES (1,'Профессионалы'),
(2,'Любители'),
(3,'Юниоры'),
(4,'Детская');
Заполнение данными таблицы «Должность»:
INSERT INTO должность
VALUES (1,'Тренер', '2'),
(2,'Тренер', '5'),
(3,'Спасатель', '5'),
(4,'Уборщица', '3');
Заполнение данными таблицы «Сотрудник»:
INSERT INTO сотрудник
VALUES (1,1, 'Кошкин', 101),
(2,2, 'Собакин', 102),
(3,3, 'Синицын', 103),
(4,4, 'Журавлева', 104);
Заполнение данными таблицы «Посетитель»:
INSERT INTO посетитель (Номер_посетителя, Номер_группы, Номер_сотрудника, ФИО, №абонемента) VALUES
(1,1,(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=1),'Иванов', '101'),
(2,1,(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=1),'Петров', '102'),
(3,1,(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=1),'Сидоров', '103'),
(4,2,(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=2),'Александров', '104'),
(5,2,(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=2),'Григорьев', '105'),
(6,2,(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=2),'Алексеев', '106');
Заполнение данными таблицы «Услуги бассейна»:
INSERT INTO услуги_бассейна
VALUES (1,'Плаванье', 'да','нет'),
(2,'Прыжки', 'нет','да'),
(3,'Плаванье', 'нет','да'),
(4,'Прыжки', 'да','нет');
Заполнение данными таблицы «Работает»:
INSERT INTO работает (Номер_бассейна, Номер_сотрудника) VALUES
((SELECT Номер_бассейна FROM бассейн WHERE Название='Дельфин'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=1)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Касатка'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=1)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Кенгуру'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=2)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Заяц'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=2)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Дельфин'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=3)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Касатка'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=3)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Кенгуру'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=3)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Заяц'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=3)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Дельфин'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=4)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Касатка'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=4)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Кенгуру'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=4)),
((SELECT Номер_бассейна FROM бассейн WHERE Название='Заяц'),(SELECT Номер_сотрудника FROM сотрудник WHERE Номер_должности=4));
Заполнение данными таблицы «Время посещения»:
INSERT INTO время_посещения
VALUES (1,1,1,'2000-05-01','14:00',1),
(2,1,2,'2000-05-01','16:00',2),
(3,1,1,'2000-05-01','14:00',3),
(4,2,3,'2000-05-01','14:00',4),
(5,2,4,'2000-05-01','16:00',5),
(6,2,3,'2000-05-01','14:00',6);
Заполнение данными таблицы «Вид услуги»:
INSERT INTO вид_услуги
VALUES (1,1,1,1,1,1),
(1,2,2,2,3,2),
(2,1,1,3,2,3),
(2,2,2,4,4,4);
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Рисунок 2. Реляционная диаграмма схемы «Бассейн»
Вывод: в ходе работы были сформированы и выполнены SQL-запросы для создания таблиц базы данных.