Инфологическое проектирование
Анализ предметной области.
База данных создаётся для информационной поддержки деятельности научно-технической библиотеки.
БД должна обеспечивать:
· ведение автоматизированного учёта выдачи/приёма литературы;
· ведение очередей на литературу (по заказам);
· учёт рейтинга изданий (количество читателей и дата последней выдачи);
· поиск литературы по требуемым разделу, теме, автору, ключевому слову (с заданием интересующего периода);
· составление списков должников по годам.
Выделим базовые сущности этой предметной области:
Читатели. Атрибуты читателей: ФИО, дата рождения, телефон, адрес, паспортные данные. Для читателей необходимо хранить список взятых и заказанных книг.
Разделы (ББК). Атрибуты: шифр, название.
Книги. Атрибуты книг: название, авторы, количество книг в библиотеке, издательство, город, год выпуска, количество страниц, дата последней выдачи. Для книг нужно вычислять и хранить их рейтинг, принадлежность к определенному разделу.
Для составления очередей на книгу и списка должников связи читатель ― книга (заказать и читать) должны содержать номер и дату заказа.
ER-диаграмма приведена на рис. 1.
Рис. 1. ER-диаграмма библиотеки
Анализ информационных задач
и круга пользователей системы
Система создаётся для обслуживания следующих групп пользователей:
· читатели;
· сотрудники, осуществляющие выдачу книг;
· сотрудники отдела комплектации, осуществляющие регистрацию поступления книг в библиотеку.
Определим границы информационной поддержки пользователей:
1) Функциональные возможности:
· ведение БД (запись, чтение, модификация, удаление в архив);
· обеспечение логической непротиворечивости БД;
· обеспечение защиты данных от несанкционированного или случайного доступа (определение прав доступа);
· реализация наиболее часто встречающихся запросов в готовом виде.
2) Готовые запросы:
· выдача и возвращение книги читателем;
· добавление/удаление заказа на книгу;
· добавление/удаление книги;
· просмотр списка должников с указание взятых книг;
· поиск книги по разным критериям.
Логическое проектирование реляционной БД
Преобразование ER–диаграммы в схему базы данных
На основе ранее созданной ER-диаграммы построим схему БД:
Рис. 2. Схема БД "Библиотека"
Составление реляционных отношений:
Отношение Читатели (readers) | |||
Номер читательского билета | r_id | N(6) | Первичный ключ |
ФИО | r_name | C(50) | Обязательное поле |
Пол | r_sex | C(1) | Обязательное поле |
Паспортные данные | r_pasport | C(80) | Обязательное поле |
Дата рождения | r_born | D | Обязательное поле |
Адрес | r_addr | C(60) | Обязательное поле |
Телефон | r_tel | C(30) |
Отношение Разделы (partitions): | |||
Шифр раздела | p_code | C(10) | Первичный ключ |
Название | p_name | C(50) | Обязательное поле |
Отношение Книги (books) | |||
Идентификатор книги | b_id | N(10) | Суррогатный первичный ключ |
Название книги | b_name | C(100) | Обязательное поле |
Авторы книги | b_author | C(100) | Многозначное поле |
Издательство | b_publisher | С(30) | Обязательное поле |
Город | b_city | C(30) | Обязательное поле |
Год выпуска | b_year | N(4) | Обязательное поле, |
Количество экземпляров | b_quantity | N(3) | Обязательное поле, больше 0 |
Количество страниц | b_pages | N(5) | Обязательное поле, больше 0 |
Рейтинг | b_rating | N(5) | Обязательное поле, по умолчанию 0 |
Дата последней выдачи | b_lastdate | D | |
Шифр раздела | p_code | C(10) | Внешний ключ (partitions) |
Отношение Заказы книг (book_order): | ||||
Дата заказа | bdate | D | Обязательное поле | |
Идентификатор читателя | r_id | N(6) | Внешний ключ (readers) | Уникальная совокупность полей |
Идентификатор книги | b_id | N(10) | Внешний ключ (books) |
Отношение Абонемент (Abonement): | |||
Идентификатор книги | b_id | N(10) | Внешний ключ (books) |
Дата, когда взяли | bdate | D | Обязательное поле |
Идентификатор читателя | r_id | N(6) | Внешний ключ (readers) |
Дата, когда вернули | edate | D |
Нормализация полученных отношений (до 4 НФ)
1 НФ:
Для приведения таблиц к 1НФ необходимо разбить сложные атрибуты на простые, а многозначные атрибуты вынести в отдельные отношения.
Но мы не будем выносить многозначный атрибут “авторы” отношения “книги” в отдельное отношение, т. к. для технической литературы поиск всегда происходит по первому автору, а остальные могут не учитываться.
2 НФ:
В нашей базе данных составные первичные ключи имеет отношение “заказы книг”. Неключевые атрибуты этого отношения функционально полно зависят от первичных ключей.
3 НФ:
В отношении "Книги" присутствует транзитивная зависимость Книга -> Издательство -> Город. Вынесем её в отдельные отношения "Издательства" и "Города".
4 НФ:
Отношения данной базы данных не нарушают требований 4НФ, т. к. в них нет многозначных зависимостей.
После нормализации из таблицы "Книги" выделились три таблицы:
Отношение Города (City) | |||
Город | c_name | C(30) | Первичный ключ |
Отношение Издательства (publishers) | |||
Издательство | p_name | С(30) | Обязательное поле |
Город | p_city | C(30) | Внешний ключ |
Отношение Книги (books) | |||
Идентификатор книги | b_id | N(10) | Суррогатный первичный ключ |
Название книги | b_name | C(100) | Обязательное поле |
Авторы книги | b_author | C(100) | Многозначное поле |
Издательство | b_name | С(30) | Внешний ключ |
Год выпуска | b_year | N(4) | Обязательное поле, больше 1800 |
Количество экземпляров | b_quantity | N(3) | Обязательное поле, больше 0 |
Количество страниц | b_pages | N(5) | Обязательное поле, больше 0 |
Рейтинг | b_rating | N(5) | Обязательное поле, по умолчанию 0 |
Дата последней выдачи | b_lastdate | D | |
Шифр раздела | p_code | C(20) | Внешний ключ (partitions) |
Схема БД после нормализации приведена на рис. 3.
Рис. 2. Схема БД "Библиотека" после нормализации
Определение дополнительных ограничений целостности
1. Количество книг на руках не может превышать общего количества книг.
2. Последний экземпляр не выдается на руки.
Эти ограничения целостности нельзя реализовать в схеме БД, поэтому мы реализуем это проверкой с помощью запросов.
Права доступа к таблицам для групп пользователей
Таблицы | Группы пользователей (роли) | ||
Сотрудники отдела комплектации | Сотрудники отдела выдачи | Читатели | |
Города (city) | SIUD | S | S |
Издательства | SIUD | S | S |
Разделы (partitions) | SIUD | S | S |
Книги (books) | SIUD | SU1 | S |
Читатели (readers) | SUID | ||
Абонемент | SUID | ||
Заказы (book_order) | SIUD | SI |
1) сотрудники отдела выдачи имеют право изменять поля «рейтинг» и «дата последней выдачи» в таблице "Книги".
Реализация проекта базы данных
Создание таблиц:
1. «Читатели»
create table readers(
r_id numeric(6) primary key,
r_name varchar(50) not null,
r_born date not null,
r_sex char(1) not null,
r_pasport varchar(80) not null,
r_addr varchar(60) not null,
r_tel varchar(30)
);
2. «Разделы»
create table partitions(
p_code varchar(10) not null primary key,
p_name varchar(50) not null
);
3. "Города"
create table city(
b_name varchar(30) primary key);
4. "Издательства"
create table publishers(
p_name varchar(30) primary key,
p_city varchar(30) references city);
5. «Книги»
create table books(
b_id numeric(10) not null primary key,
b_name varchar(100) not null,
b_authors varchar(100) not null,
b_publisher varchar(30) not null
references publishers,
b_quantity numeric(3) not null,
b_pages numeric(5) not null,
b_rating numeric(5) not null default '0',
b_lastdate date,
p_code varchar(10) not null
references partitions,
CHECK (b_quantity > 0)
);
6. «Заказы книг»
create table book_order(
b_date date not null,
r_id numeric(6) not null references readers,
b_id numeric(10) not null references books,
unique(r_id, b_id)
);
7. «Абонемент»
create table abonement(
bdate date not null,
r_id numeric(6) not null references readers,
b_id numeric(10) not null references books,
edate date
);
Для задания прав доступа к таблицам введем три роли (role), которые будут соответствовать заданным группам пользователей:
1. Сотрудники, выдающие/ принимающие книги:
create role employee;
grant all on readers to employee;
grant select on partitions to employee;
grant select on city to employee;
grant select on publishers to employee;
grant select, update (b_rating, b_lastdate)
on books to employee;
grant all on book_order to employee;
grant all on abonement to employee;
2. Сотрудники, пополняющие базу данных книг:
create role staff;
grant all on partitions to staff;
grant all on books to staff;
grant all on city to staff;
grant all on publishers to staff;
3. Читатели:
create role reader;
grant select on partitions to reader;
grant select on city to reader;
grant select on publishers to reader;
grant select on books to reader;
grant select, insert on book_order to reader;
Теперь добавление пользователя к какой-либо группе осуществляется запросом:
grant <роль> to <пользователь>;
Готовые запросы:
1) Список должников (должниками будут считаться читатели, взявшие и не вернувшие книгу в течение 14 дней):
a) Задолженность определенного читателя:
select * from abomement
where bdate+14 < sysdate
and edate is null
and r_id = <id читателя>;
b) Книги, которые не вернули в положенный срок:
select * from books
where b_id IN (select b_id from abonement
where bdate+14 < sysdate
and edate is null);
2) Выдача книг:
update books
set b_quantity = b_quantity - 1,
b_rating = b_rating + 1,
b_lastdate = trunc(sysdate)
where b_id = <id книги>
and b_quantity > 1;
insert into abonement (bdate, r_id, b_id)
values(trunc(sysdate), <id читателя>,
<id книги>);
3) Возврат книг:
update books set b_quantity = b_quantity + 1
where b_id = <id книги>;
update abonement set edate = trunc(sysdate)
where r_id = <id читателя>
and b_id = <id книги>;
4) Проверка того, что выдано столько книг, сколько есть в библиотеке, и не больше:
select *
from books b
where b_quantity >= (select count(*)
from abonement a
where a. b_id = b. b_id
and edate is null);
Основные порталы (построено редакторами)