Вариант 23

1. Спроектировать базу данных «Библиотека» методом нормализации и методом семантического моделирования. Данные, которые должны храниться в базе, представлены в виде одной таблицы со следующими столбцами:

Код раздела;

Наименование раздела (художественная, научная, детская, медицинская и т.д. литература);

Код книги;

Название книги;

Код автора;

Фамилия, имя, отчество автора (ФИО автора);

Год издания книги;

Количество экземпляров в фонде библиотеки;

Количество экземпляров в наличии;

Номер читательского билета;

Фамилия, имя, отчество читателя (ФИО читателя);

Дата выдачи книги;

Срок, на который выдана книга;

Дата возврата книги (фактическая).

2. Разработать базу данных, таблицы и заполнить их данными. Библиотечный фонд должен содержать не менее 30 наименований книг, список разделов – не менее четырех разделов, а список авторов не менее семи авторов. Сведения о выдаче книг – не менее 20 записей, сведения о читателях – не менее десяти. Должны быть предусмотрены варианты как не своевременного возврата книг, так и не возврат – более 2-3 месяцев. Поле Количество экземпляров в наличии изменяется после выдачи книги читателю, а перед первым расчетом приравнивается к значению поля Количество экземпляров в фонде библиотеки. Задать минимальное значение поля Срок, на который выдана книга, равным четырнадцати дням. Добавить в таблицу, в которой содержатся поля Дата выдачи книги и Дата возврата книги (фактическая), ограничение для проверки правильности ввода этих полей: Дата возврата книги не должна быть ранее Даты выдачи книги. Создать для каждой таблицы первичный ключ, а также внешние ключи для всех дочерних таблиц. Установить постоянные отношения между дочерними и родительскими таблицами (создаются при построении диаграммы базы данных). Задать ограничение NOT NULL для всех полей, входящих в первичные или внешние ключи.

3. Структуры таблиц и содержимое таблиц привести во второй главе пояснительной записки к курсовому проекту «Создание таблиц».

4. В третьей главе курсового проекта «Основные команды SQL для извлечения, добавления и изменения данных» выполнить следующие действия и представить их результаты.

4.1. Создать запрос для выдачи списка книг определенного раздела: Код раздела, Наименование раздела, Код книги, ФИО автора, Год издания книги, Наименование издательства, Количество экземпляров в наличии, Количество экземпляров в фонде библиотеки.

4.2. Отредактировать предыдущий запрос таким образом, чтобы наименование раздела можно было вводить с клавиатуры.

3. Задать первоначальное значение поля Количество экземпляров в наличии равным полю Количество экземпляров в фонде библиотеки, поле Дата возврата книги (фактическая) равным 01.01 2030 (с помощью запроса).

4. Выдать список читателей просрочивших возврат книг более чем на 30 дней. В список должны войти как читатели, которые уже возвратили, так и те, что пока не вернули книги. Показать поля: Номер читательского билета, ФИО читателя, Адрес читателя, Дата выдачи книги, Срок, на который выдана книга, Дата возврата книги (фактическая).

4.5. Создать сгруппированный запрос для подсчета общего количества книг каждого автора, как в фонде библиотеки, так и в наличии, а также рассчитать коэффициент востребованности книги. Коэффициент востребованности = Количество экземпляров в наличии / Количество экземпляров в фонде библиотеки.

4.6. Увеличить Срок, на который выдана определенная книга определенному читателю на 5 дней.

4.7. Скопировать в новую таблицу следующие данные: Наименование раздела, Код книги, Название книги, ФИО автора, Номер читательского билета, ФИО читателей, у которых Количество просроченных дней более 30. Количество просроченных дней=Текущая дата - Дата выдачи книги.

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

5.1. Создать функцию для определения Коэффициента востребованности книги определенного автора. Проверить работу функции с помощью запроса.

5.2. Создать процедуру с параметрами Код книги, Номер читательского билета и Дата возврата книги (фактическая). Процедура должна выполнять проверку, есть ли данная книга, выданная по данному Номеру читательского билета в списке выданных книг. Если такая запись существует, следует изменить значение поля Дата возврата книги (фактическая) на значение параметра. Если параметры введены неверно, следует выдать сообщение.

6. В пятой главе должны быть разработаны и отображены триггеры, обеспечивающие целостность данных в создаваемой базе данных.

6.1. Создать триггер, который при вводе новой записи о выдаче книги проверял бы, существует ли Код данной книги в списке книг, а Номер читательского билета – в списке читателей. В случае неправильного ввода того или другого параметра следует выдать сообщение и откатить транзакцию. Если параметры введены правильно, необходимо уменьшить значение поля Количество экземпляров в наличии на 1.

6.2. Создать триггер на удаление записи из списка читателей. В теле триггера осуществить проверку, существуют ли невозвращенные читателем книги и отменить удаление, если такие записи имеются.