Безопасность систем баз данных: Задания на лабораторные работы № 1-6. Вариант 21 (База данных "Заметки пользователей блога")

Лабораторная работа №1

Исходной информацией для построения ER-диаграммы базы данных является следующий перечень атрибутов:

·  учетная запись (email) пользователя;

·  статус пользователя в социальной сети (заблокирован/не заблокирован);

·  фамилия пользователя;

·  имя пользователя;

·  псевдоним пользователя (ник);

·  заголовок заметки;

·  уникальный числовой идентификатор заметки;

·  наименование тега для заметок;

·  содержимое заметки;

·  дата и время добавления заметки;

·  дата и время последнего редактирования заметки;

·  тип доступа на просмотр заметки;

·  тип доступа на комментирование заметки;

·  премодерация комментариев (установлена/не установлена);

·  ссылка на фотографию;

·  ссылка на видеозапись;

·  ссылка на аудиозапись;

·  ссылка на файл;

·  заголовок комментария;

·  дата и время добавления комментария;

·  статус комментария (скрытый/опубликованный).

Перечисленные атрибуты необходимо сгруппировать по сущностям и связям. Определение сущностей и структуры связей выполняется студентом самостоятельно. В процессе работы исходный список атрибутов может быть уточнен и дополнен.

Лабораторная работа №3

Реализовать следующие запросы к БД.

1. Получить перечень заметок указанного пользователя, имеющих указанный тег.

2. Получить перечень всех комментариев к блогу указанного пользователя.

3. Получить перечень комментариев к указанной заметке указанного пользователя.

4. Получить перечень аудиозаписей/видеозаписей/фотографий/файлов, на которые ведут ссылки из указанной заметки указанного пользователя.

5. Получить данные о количестве пользователей, прекративших вести блог (т. е. не добавлявших новые заметки больше года) и никогда не пользовавшихся блогом, соотнести с общим количеством. Реализовать запрос в виде представления:

Всего пользователей	Никогда не вели блог	Прекратили вести блог	Процент никогда не ведших блог	Процент прекративших вести блог
…	…	…	…	…
…	…	…	…	…
…	…	…	…	…

Для реализации запроса можно использовать вспомогательные представления.

Лабораторная работа №4

Реализовать в базе данных следующие функции и хранимые процедуры.

1. Функция, возвращающая перечень заметок указанного пользователя, имеющих указанный тег. Параметры: имя учетной записи пользователя, наименование тега.

2. Функция, возвращающая перечень всех комментариев к блогу указанного пользователя. Параметры: имя учетной записи пользователя, флаг отображения скрытых комментариев.

3. Функция, возвращающая перечень комментариев к указанной заметке указанного пользователя. Параметры: имя учетной записи пользователя, идентификатор заметки, флаг отображения скрытых комментариев.

4. Функция, возвращающая перечень аудиозаписей/видеозаписей/фотографий/файлов, на которые ведут ссылки из указанной заметки указанного пользователя. Параметры: имя учетной записи пользователя, идентификатор заметки и дополнительный числовой параметр поиска: 0 – поиск аудиозаписей, 1 – поиск видеозаписей, 2 – поиск фотографий, 3 – поиск файлов.

5. Процедура добавления заметки в блог.

Входные параметры: имя учетной записи пользователя, заголовок заметки, дата и время добавления, дата и время редактирования, содержимое заметки, тип разрешения на просмотр, тип разрешения на комментирование, флаг премодерации комментариев.

Примечание. Дата и время добавления и редактирования заметки по умолчанию равны текущей дате и текущему времени соответственно.

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверное имя учетной записи.

6. Процедура добавления комментария к заметке.

Входные параметры: имя учетной записи владельца блога, идентификатор заметки, имя учетной записи автора комментария, заголовок комментария, содержимое комментария.

Примечание 1. Процедура прописывает текущие дату и время добавления комментария.

Примечание 2. Процедура не задает статус комментария (ставит NULL). Статус нового комментария автоматически определяется триггером (см. лабораторную работу №6).

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверное имя учетной записи владельца;

·  2 – неверное имя учетной записи автора комментария;

·  3 – неверный идентификатор заметки.

7. Процедура удаления заметки.

Входные параметры: имя учетной записи владельца блога, идентификатор заметки.

Примечание. Процедура должна удалять только заметку. Для удаления связанных комментариев необходимо настроить каскадное удаление (см. лабораторную работу №2).

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверное имя учетной записи владельца;

·  2 – неверный идентификатор заметки.

Лабораторная работа №6

Реализовать в базе данных следующие ограничения.

1. Тип доступа на просмотр и комментирование блога/заметки принимает одно из следующих значений: 0 – «только мне», 1 – «только мне и друзьям», 2 – «только мне, друзьям и друзьям друзей», 3 – «всем пользователям».

2. Статус добавляемого комментария устанавливается в соответствии с настройками премодерации блога/заметки.

3. Нельзя ничего менять в блоге пользователя, чья учетная запись заблокирована.

4. Дата и время создания заметки – более ранние, чем дата и время последнего редактирования, или совпадают с ними.

5. Дата и время создания заметки – более ранние, чем дата и время добавления комментария к заметке.