1.Описание задания
Создать физическую модель базы данных, находящуюся в третьей нормальной форме в соответствии с заданным вариантом. Расписать ссылочную целостность БД в таблице.
2.Вариант задания
Вариант 20.
Туристический путеводитель: город, достопримечательность, адрес, тип достопримечательности (памятник, архитектурный комплекс, природный комплекс), дата создания.
3.Физическая модель БД
4.Таблица с описанием ссылочной целостности
5.Вывод
во время выполнения данной лабораторной работы была создана физическая модель базы данных, спроектирована база данных, находящаяся в третьей нормальной форме.
1.Описание задания
Создать базу данных с разработанной физической моделью. Продемонстрировать умение добавить и удалить столбец командой alter table.
2.Вариант задания
Вариант 20.
Туристический путеводитель: город, достопримечательность, адрес, тип достопримечательности (памятник, архитектурный комплекс, природный комплекс), дата создания.
3.Физическая модель БД
Рис.1. Физическая модель БД в соответствии с вариантом
4Скрипт создания таблиц базы данных на SQL
/*
Скрипт создания таблиц базы данных.
*/
— —————————-
— Таблица городов.
— —————————-
CREATE TABLE `towns`
(
`id` int(11) NOT NULL AUTO_INCREMENT, — Код города.
`name` varchar(64) NOT NULL, — Название города.
PRIMARY KEY (`id`), — Код города — первичный ключ.
UNIQUE KEY `town_name` (`name`) USING BTREE — Название уникально.
);
— —————————-
— Таблица типов.
— —————————-
CREATE TABLE `types`
(
`id` int(11) NOT NULL AUTO_INCREMENT, — Код типа.
`name` varchar(64) NOT NULL, — Название типа.
PRIMARY KEY (`id`), — Код типа — первичный ключ.
UNIQUE KEY `typename` (`name`) — Название типа уникально.
);
— —————————-
— Таблица адресов.
— —————————-
CREATE TABLE `addresses`
(
`id` int(11) NOT NULL, — Код адреса.
`town` int(11) NOT NULL, — Код города.
`street` varchar(128) NOT NULL, — Название улицы.
`building` varchar(8), — Номер здания.
PRIMARY KEY (`id`), — Первичный ключ.
KEY `town` (`town`), — Код города — внешний ключ.
CONSTRAINT `town` FOREIGN KEY (`town`)
REFERENCES `towns` (`id`) — При удалении города
ON DELETE CASCADE
ON UPDATE CASCADE — удаляются и адреса.
);
— —————————-
— Таблица достопримечательностей.
— —————————-
CREATE TABLE `sights`
(
`id` int(11) NOT NULL AUTO_INCREMENT, — Код объекта.
`name` varchar(128) NOT NULL, — Название объекта.
`create` date, — Дата создания.
`type` int(11), — Код типа объекта.
`address` int(11) NOT NULL, — Код адреса.
PRIMARY KEY (`id`), — Код объекта — первичный ключ.
KEY `sighttype` (`type`),
KEY `address` (`address`),
CONSTRAINT `address` FOREIGN KEY (`address`)
REFERENCES `addresses` (`id`) — Код адреса — внешний ключ.
ON DELETE RESTRICT — Нельзя удалять адреса,
ON UPDATE RESTRICT, — по которым находятся объекты.
CONSTRAINT `sighttype` FOREIGN KEY (`type`)
REFERENCES `types` (`id`) — Код типа — внешний ключ.
ON DELETE SET NULL — При удалении типа
ON UPDATE SET NULL — ключ обнуляется.
);
5. Скрипт изменения структуры таблиц базы данных на SQL
ALTER TABLE `sights` — Добавляем столбец.
ADD COLUMN `newcol` int NULL AFTER `address`;
ALTER TABLE `sights` — Удаляем добавленный столбец.
DROP COLUMN `newcol`;
6.Вывод
Во время выполнения данной лабораторной работы были созданы таблицы базы данных со связями по заданной физической модели, изучены операторы изменения таблицы путём добавления лишнего столбца и его последующего удаления.
1.Описание задания
Выполнить вставку тестовых данных в таблицы, созданные в ходе выполнения лабораторной работы 2. В строках, вставляемых в таблицы, должны быть данные как удовлетворяющие, так и не удовлетворяющие условиям запросов, приведенных в варианте задания. Необходимо привести свои пример использования оператором update и delete и merge.
2.Вариант задания
Вариант 20.
Туристический путеводитель: город, достопримечательность, адрес, тип достопримечательности (памятник, архитектурный комплекс, природный комплекс), дата создания.
а. достопримечательности, в которых есть слово «мать», но с него название не начинается
б. город без достопримечательностей
в. улица, на которой есть и памятники, и архитектурные комплексы
г. город, в котором нет природных комплексов
д. улица, на которой больше всего памятников
е. города с самыми старыми достопримечательностями
ж. улица, на которой есть достопримечательности всех типов
3.Физическая модель БД
Рис.1. Физическая модель БД в соответствии с вариантом
4.Наборы данных, содержащихся в таблицах БД
5.Таблица тестовых данных
6. примеры использования insert, update, delete
INSERT INTO `towns` VALUES(0, ‘Мухобрянск’);
UPDATE `towns` SET name = ‘Мухозадницк’ WHERE name = ‘Мухобрянск’;
DELETE FROM `towns` WHERE name = ‘Мухозадницк’;
7Скрипт полного заполнения базы данных
— —————————-
— Заполнение таблицы types.
— —————————-
INSERT INTO `types` VALUES (‘1’, ‘Памятник’);
INSERT INTO `types` VALUES (‘2’, ‘Архитектурный комплекс’);
INSERT INTO `types` VALUES (‘3’, ‘Природный комплекс’);
— —————————-
— Заполнение таблицы городов.
— —————————-
INSERT INTO `towns` VALUES (‘1’, ‘Стеблов’);
INSERT INTO `towns` VALUES (‘2’, ‘Арианна’);
INSERT INTO `towns` VALUES (‘3’, ‘Трикнидакна’);
INSERT INTO `towns` VALUES (‘4’, ‘Закотянск’);
INSERT INTO `towns` VALUES (‘5’, ‘Астраханск’);
INSERT INTO `towns` VALUES (‘6’, ‘Камиград’);
INSERT INTO `towns` VALUES (‘7’, ‘Сальта’);
INSERT INTO `towns` VALUES (‘8’, ‘Дродж’);
— —————————-
— Заполнение таблицы адресов.
— —————————-
INSERT INTO `addresses` VALUES (‘1’, ‘3’, ‘Улица Меченосцев’, ‘3’);
INSERT INTO `addresses` VALUES (‘2’, ‘3’, ‘Улица Меченосцев’, ’13’);
INSERT INTO `addresses` VALUES (‘3’, ‘5’, ‘Проспект Основателя’, ‘7’);
INSERT INTO `addresses` VALUES (‘4’, ‘5’, ‘Проспект Основателя’, ’12’);
INSERT INTO `addresses` VALUES (‘5’, ‘5’, ‘Проспект Основателя’, ’20’);
INSERT INTO `addresses` VALUES (‘6’, ‘8’, ‘Улица Набережная’, ’91’);
INSERT INTO `addresses` VALUES (‘7’, ‘8’, ‘Улица Набережная’, ’50’);
INSERT INTO `addresses` VALUES (‘8’, ‘8’, ‘Улица Набережная’, ’11’);
INSERT INTO `addresses` VALUES (‘9’, ‘4’, ‘Улица Емельяна Разумовского’, ‘9’);
INSERT INTO `addresses` VALUES (’10’, ‘5’, ‘Зелёная улица’, ‘2’);
— —————————-
— Заполнение таблицы достопримечательностей.
— —————————-
INSERT INTO `sights` VALUES (‘1’, ‘Памятник Матиану Воителю’, ‘0325-03-04’, ‘1’, ‘1’);
INSERT INTO `sights` VALUES (‘2’, ‘Мавзолей Риханны’, ‘0225-08-15’, ‘2’, ‘2’);
INSERT INTO `sights` VALUES (‘3’, ‘Памятник Великому Князю’, ‘0228-07-30’, ‘1’, ‘3’);
INSERT INTO `sights` VALUES (‘4’, ‘Памяьник Емельяну Разумовскому’, ‘0333-11-25’, ‘1’, ‘4’);
INSERT INTO `sights` VALUES (‘5’, ‘Стелла в честь восстановления’, ‘0330-10-17’, ‘1’, ‘5’);
INSERT INTO `sights` VALUES (‘6’, ‘Корабельный мыс’, ‘0101-03-21’, ‘3’, ‘6’);
INSERT INTO `sights` VALUES (‘7’, ‘Пирс грузчиков’, ‘0250-04-09’, ‘2’, ‘7’);
INSERT INTO `sights` VALUES (‘8’, ‘Монумент освобождения’, ‘0323-05-14’, ‘1’, ‘8’);
INSERT INTO `sights` VALUES (‘9’, ‘Мать мира’, ‘0600-10-08’, ‘1’, ‘9’);
INSERT INTO `sights` VALUES (’10’, ‘Озеро Великая мать’, ‘2001-01-01’, ‘3’, ’10’);
1.Вывод
Во время выполнения данной лабораторной работы были заполнены таблицы базы данных, изучены операторы insert, update, delete
1.Описание задания
Реализовать запросы а) … в), указанные в варианте задания. Все запросы должны не содержать вложенных запросов или агрегатных функций. (Используйте псевдонимы).
2.Вариант задания
Вариант 20.
Туристический путеводитель: город, достопримечательность, адрес, тип достопримечательности (памятник, архитектурный комплекс, природный комплекс), дата создания.
а. достопримечательности, в которых есть слово «мать», но с него название не начинается
б. город без достопримечательностей
в. улица, на которой есть и памятники, и архитектурные комплексы
3.Физическая модель БД
4.Текст запросов на SQL
— а. достопримечательности, в которых есть слово «мать»,
— но с него название не начинается
SELECT s.`name` as ‘Достопримечательность’,
s.`create`as ‘Дата создания’,
tp.`name` as ‘Тип’,
concat(t.`name`, ‘, ‘, a.street, ‘, ‘, a.building) as ‘Адрес’
FROM `sights` as s
INNER JOIN `types` as tp on s.type = tp.id
INNER JOIN `addresses` as a on s.address = a.id
INNER JOIN `towns` as t on a.town = t.id
WHERE s.`name` LIKE ‘_%мать%’;
— б. город без достопримечательностей
SELECT DISTINCT t.`name` as ‘Город’ FROM towns as t
LEFT JOIN addresses as a ON t.id = a.town
LEFT JOIN sights as s ON a.id = s.address
WHERE address IS NULL;
— в. улица, на которой есть и памятники, и архитектурные комплексы
SELECT DISTINCT a.street as ‘Улица’ FROM addresses as a
RIGHT JOIN sights as s ON a.id = s.address
WHERE s.type IN (1, 2);
5.Наборы данных. возвращаемые запросами
6.Вывод
Во время выполнения данной лабораторной работы были изучены запросы к базе данных, шаблоны и способы соединения таблиц.
1.Описание задания
Реализовать запросы г) … ж), указанные в варианте задания.
Один из запросов на максимум/минимум реализовать с помощью директивы all.
Запрос на «все» (реляционное деление) реализовать с помощью 2 not exists.
Запросы на разность реализовать в 3 вариантах: Not in, except (MySQL не поддерживает, поэтому только синтаксис), с использованием левого/правого соединения
2.Вариант задания
Вариант 20.
Туристический путеводитель: город, достопримечательность, адрес, тип достопримечательности (памятник, архитектурный комплекс, природный комплекс), дата создания.
г. город, в котором нет природных комплексов
д. улица, на которой больше всего памятников
е. города с самыми старыми достопримечательностями
ж. улица, на которой есть достопримечательности всех типов
3.Физическая модель БД
Рис.1. Физическая модель БД в соответствии с вариантом
4.Текст запросов на SQL
— г. город, в котором нет природных комплексов
— Решение через NOT IN.
SELECT `name` as ‘Город’ FROM towns
WHERE id NOT IN
(
SELECT town FROM addresses
WHERE id IN
(
SELECT address FROM sights WHERE type = 3
)
);
— Решение через except
SELECT `name` as ‘Город’ FROM towns
EXCEPT SELECT `name` as ‘Город’ FROM towns
WHERE id IN
(
SELECT town FROM addresses
WHERE id IN
(
SELECT address FROM sights WHERE type = 3
)
);
— Решение через левое соединение
SELECT `name` as ‘Город’ FROM towns WHERE id NOT IN
(
SELECT DISTINCT t.id FROM towns as t
LEFT JOIN addresses as a ON t.id = a.town
LEFT JOIN sights as s ON a.id = s.address
WHERE s.type = 3
);
— д. улица, на которой больше всего памятников
SELECT street as ‘Улица’ FROM addresses
WHERE id IN
(
SELECT address FROM sights WHERE type = 1
)
GROUP BY street
HAVING count(street) >= ALL
(
SELECT count(street) FROM addresses
WHERE id IN
(
SELECT address FROM sights WHERE type = 1
) GROUP BY street
);
— е. города с самыми старыми достопримечательностями
SELECT `name` as ‘Город’ FROM towns
WHERE id IN
(
SELECT town FROM addresses
WHERE id IN
(
SELECT address FROM sights
WHERE `create` <= ALL (SELECT `create` FROM sights)
)
);
— ж. улица, на которой есть достопримечательности всех типов
SELECT street as ‘Улица’ FROM addresses as a
INNER JOIN sights AS s ON a.id = s.address
GROUP BY a.street, s.type HAVING COUNT(a.street) = 3;
5.Наборы данных. возвращаемые запросами
Вывод
Во время выполнения данной лабораторной работы были изучены запросы к базе данных, шаблоны и способы соединения таблиц.