Отчет по курсу технологии баз данных. Отчет по курсу технологии баз данных
 Скачать 358.87 Kb.
|
|
Отчет по курсу технологии баз данных Студент гр.** 2020/21 учебный год Введение Разработана информационная система, в которой хранится информация о статьях, категориях, пользователях и об их комментариях. Данную систему можно использовать на сайте-блоге, она позволит хранить, получать и использовать информацию о статьях на сайте, категориях, пользователях, комментариев к статьям. Среда разработки В качестве средств разработки (программного обеспечения) был выбран сервер: MariaDB (версии 10.3.22). Этот бесплатный сервер баз данных устанавливается и конфигурируется вместе с ПО «Open Server». В качестве оболочки для работы с SQL сервером был использован программный пакет MySQL Workbench 8.0 (Рисунок 1) – это свободное программное обеспечение, которое содержит средства моделирования, администрирования сервера и визуальной работы с базами данных.  Рисунок 1. Программный пакет MySQL Workbench Этапы работы Для решения поставленной задачи принята схема базы данных, представленная на рисунок. 2.  Рисунок. 2. Модель таблиц базы данных «БЛОГ» с указанием связей. Заполним таблицы данными:     Запросы и представления к базе данных Стандартные запросы: Количество статей: "SELECT COUNT(*) FROM article" Количество пользователей: "SELECT COUNT(*) FROM user" Количество категорий: "SELECT COUNT(*) FROM category" Количество комментариев: "SELECT COUNT(*) FROM comment" Получить статью под категорией с ID 0: "SELECT * FROM article WHERE 'id' = 0" Получить все статьи автора по ID 0: "SELECT * FROM article WHERE 'user_id' = 0" Получить все комментарии пользователя под ID 0: "SELECT * FROM comment WHERE 'user_id' = 0" Получить все комментарии статьи под ID 0: "SELECT * FROM comment WHERE 'article_id' = 0" Получить все статьи по убыванию даты создания: "SELECT * FROM article ORDER BY 'created_at' DESC" Получить все статьи в порядке убывания просмотров: "SELECT * FROM article ORDER BY 'views' DESC" Получить всех администраторов: "SELECT * user WHERE 'admin' = 1" Получить всех обычных пользователей: "SELECT * user WHERE ‘admin’ = 0" Получить три последние измененные статьи: "SELECT * FROM article ORDER BY 'updated_at' DESC LIMIT 3" Представления: 1) Вывести каталог статей, указав заголовок, содержание, имя категории, имя автора и дату создания. Так как на блоге отображаются сначала свежие статьи, дополнительно отсортируем записи от новым к старым по дате создания. VIEW `catalog` AS SELECT `a`.`header` AS `Заголовок`, `a`.`content` AS `Содержание`, `u`.`name` AS `Имя автора`, `a`.`created_at` AS `Дата публикации` FROM (`article` `a` JOIN `user` `u` ON (`u`.`id` = `a`.`user_id`)) ORDER BY `a`.`created_at` DESC Результат запроса:  2) Вывести всех администраторов блога, указав их идентификатор, имя и количество написанных статей. VIEW `admins_view` AS SELECT `u`.`id` AS `#`, `u`.`name` AS `Имя`, COUNT(`a`.`id`) AS `Статей` FROM (`user` `u` JOIN `article` `a` ON (`u`.`id` = `a`.`user_id`)) GROUP BY `u`.`id` Результат запроса:  3) Вывести всех обычных пользователей блога, указав их идентификатор, имя и количество написанных комментариев. VIEW `admins_view` AS SELECT `u`.`id` AS `#`, `u`.`name` AS `Имя`, COUNT(`a`.`id`) AS `Статей` FROM (`user` `u` JOIN `article` `a` ON (`u`.`id` = `a`.`user_id`)) GROUP BY `u`.`id` Результат запроса:  4) Вывести категории, указав их идентификатор, имя и количество написанных статей под данной категорией. VIEW `categories_view` AS SELECT `c`.`id` AS `#`, `c`.`name` AS `Имя`, COUNT(`a`.`id`) AS `Количество статей` FROM (`category` `c` JOIN `article` `a` ON (`c`.`id` = `a`.`category_id`)) GROUP BY `c`.`id` Результат запроса:  5) Вывести категории, указав их идентификатор, имя и суммарное количество просмотров у статей под ней. (начиная от самой популярной) VIEW `category_views_view` AS SELECT `c`.`id` AS `#`, `c`.`name` AS `Имя`, SUM(`a`.`views`) AS `Суммарные просмотры` FROM (`category` `c` JOIN `article` `a` ON (`c`.`id` = `a`.`category_id`)) GROUP BY `c`.`id` Результат запроса:  Хранимые процедуры и триггеры Триггеры: 1) Поставить текущее время в столбец «обновлено в» статьи, после обновления информации в ней. TRIGGER `danilblog`.`article_BEFORE_UPDATE` BEFORE UPDATE ON `article` FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); END 2) После удаления пользователя, удалить все написанные им статьи и комментарии если они есть. ENDTRIGGER `danilblog`.`user_AFTER_DELETE` AFTER DELETE ON `user` FOR EACH ROW BEGIN DELETE FROM article WHERE article.user_id = OLD.id; DELETE FROM comment WHERE comment.user_id = OLD.id; END 3) После удаления категории, удалить все статьи, написанные в данной категории. TRIGGER `danilblog`.`user_AFTER_DELETE` AFTER DELETE ON `user` FOR EACH ROW BEGIN DELETE FROM article WHERE article.user_id = OLD.id; DELETE FROM comment WHERE comment.user_id = OLD.id; END 4) После удаления статьи, удалить все комментарии написанные пользователями для данной статьи. TRIGGER `danilblog`.`user_AFTER_DELETE` AFTER DELETE ON `user` FOR EACH ROW BEGIN DELETE FROM article WHERE article.user_id = OLD.id; DELETE FROM comment WHERE comment.user_id = OLD.id; END 5) После удаления комментария, удалить все дочерние комментарии. (комментарии, которые являются ответом на данный комментарий). TRIGGER `danilblog`.`comment_AFTER_DELETE` AFTER DELETE ON `comment` FOR EACH ROW BEGIN DELETE FROM comment WHERE comment.parent_id = OLD.id; END Хранимые процедуры: Получить лучшего автора по количеству просмотров у статей. PROCEDURE `best_author`() BEGIN SELECT name, SUM(`a`.`views`) AS `Суммарные просмотры` FROM user u JOIN `article` a ON `u`.`id` = `a`.`user_id` GROUP BY name ORDER BY SUM(`a`.`views`) DESC LIMIT 1; END Пример результата:  Получить суммарные просмотры на блоге за период между двумя датами. PROCEDURE `views_between`(d1 DATE, d2 DATE) BEGIN SELECT SUM(views) AS "Суммарные просмотры" FROM article WHERE created_at BETWEEN d1 and d2; END Пример результата:  |