база данных библиотеки. Разработка распределенной базы данных библиотека на основе ms sql server
 Скачать 0.89 Mb.
|
|
РАЗРАБОТКА РАСПРЕДЕЛЕННОЙ БАЗЫ ДАННЫХ «БИБЛИОТЕКА» НА ОСНОВЕ MS SQL SERVER Разумова Т.А. Балаковский инженерно-технологический институт — филиал федерального государственного автономного образовательного учреждения высшего профессионального образования «Национальный исследовательский ядерный университет «МИФИ» Балаково, Россия DEVELOPMENT DISTRIBUTED DATABASE «LIBRARY» BASED ON THE MS SQL SERVER Razumova.T.A. Balakovo Institute of Engineering and Technology of the National Research Nuclear University MEPhI (Moscow Engineering Physics Institute) Balakovo, Russia ВВЕДЕНИЕ В различных сферах человеческой деятельности широкое распространение получили технологии, использующие базы данных для систематизации и хранения производственной информации. Это обусловлено тем, что настоящий раздел информационных технологий имеет значительную степень внедряемости и на практике довольно просто интегрируется под каждый конкретный случай. К тому же в настоящее время существует немало всевозможных вариантов реализации баз данных (БД) и систем управления базами данных (СУБД). База данных – это информационная модель, целью создания которой является упорядоченное хранение информации, обладающей одинаковым набором свойств. Система управления базами данных – это популярное инструментальное средство, применяемое для создания и работы с БД больших объемов. СУБД, работающие с реляционными типами баз данных, в большей степени используются на персональных компьютерах. СУБД позволяют организовать, структурировать и систематизировать информацию таким образом, чтобы процесс хранения и обработки на компьютере был в достаточной степени прост и удобен. Актуальность разработки настоящей базы данных для предметной области «Библиотека» продиктована необходимостью полной автоматизации всех процессов труда работников данного учреждения, что в результате позволит значительно оптимизировать процесс работы данной государственной организации и соответственно понизить трудозатраты сотрудников. Целью данного курсового проекта является разработка БД для библиотеки. В процессе выполнения работы необходимо провести анализ предметной области, продумать назначение базы данных, приобрести практические навыки по проектированию структуру, разработки и реализации реляционной модели БД. 1 АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ Разрабатываемая база данных предназначена для предметной области «Библиотека». Библиотека – это учреждение, собирающее и хранящее (в данном случае) книги для общественного пользования, а также осуществляющее справочно-библиографическую работу и реализующее распространение книг различных жанров. База данных должна обеспечивать автоматизацию следующих процессов: поиск, добавление данных, удаление и изменение имеющейся информации о книгах, читателях и сотрудниках. Кроме того, процесс поиска должен быть максимально упрощен: организован по ключевому слову или букве. Настоящая БД должна представлять собой информационную систему обеспечения деятельности трех групп пользователей: директора, сотрудников и пользователей (в данном случае читателей) библиотеки. В базе данных должно обеспечиваться хранение и регулярное обновление следующей информации: − информация о сотрудниках, а именно, ФИО сотрудника, дата рождения сотрудника, адрес сотрудника, его контактный номер телефона, паспортные данные (серия, номер); − информация о должностях сотрудников, это: наименование должности и оклад; − информация о книгах, в частности: наименование, автор, год издания, издательство и жанр; − наименование жанров книг; − ФИО авторов книг; − информация об издательствах: название и адрес; − информация о читателях, такую как: ФИО читателя, дата рождения читателя, адрес, контактный номер телефона, а также паспортные данные; − информация о выданных книгах: дата выдачи, дата возврата, пометка о возврате, читатель, на руках у которого книга и сотрудник, который её выдал. Состав функций, обеспечивающих деятельность типового пользователя – сотрудник. Сотрудник библиотеки должен иметь возможность выполнять с помощью форм ввода и поисковых форм следующие действия: поиск читателей, регистрацию новых или обновление данных об уже существующих пользователях библиотеки, а также поиск книг и оформление заказа на пополнение библиотеки. База данных обеспечивает выполнение следующих типовых запросов сотрудника: вывод списка доступных книг и тех, которые в данный момент находятся у читателя; отображение картотеки читателей; поиск читателя по имени; вывод списка выданных книг с уточнением непосредственно человека, у которого она находится; группировка книг по жанрам; расчет времени, которое книга провела на руках; поиск книг и автоматическое оформление заказа на пополнение библиотеки. Состав функций, обеспечивающих деятельность типового пользователя – директор. Кроме функций сотрудников, директор библиотеки должен иметь возможность добавлять, редактировать и удалять с помощью форм ввода информацию о сотрудниках и должностях, в том числе изменять сумму оклада. Директор должен иметь возможность выполнять следующие запросы: поиск сотрудников, просмотр штата сотрудников, вывод информации о заработной плате сотрудников. Состав функций, обеспечивающих деятельность типового пользователя – читатель. Читатель должен иметь возможность выполнять с помощью поисковых форм следующие действия: поиск книг. Для пользователя должны быть запрещены функции добавления, удаления и изменения информации. База данных обеспечивает выполнение следующих типовых запросов читателя: вывод каталога библиотеки (книга, автор, издание); поиск книги по названию и по жанру. 2 ПОСТРОЕНИЕ ИНФОЛОГИЧЕСКОЙ МОДЕЛИ БД Проведённый анализ предметной области позволяет построить концептуальную модель. Необходимо выделить сущности с их атрибутами и определить смысловые связи между ними. Сущность − это нечто такое, о чем нужно хранить информацию в базе данных. Сущности могут моделировать конкретные или абстрактные понятия. Записи об определенных параметрах каждой из сущностей называются атрибутами. Концептуальная модель строиться либо в виде диаграммы «Сущность- Связь» (Entity-Relationship-диаграммы, ER-диаграммы), либо записывается на языке концептуального (инфологического) моделирования (ЯКМ, ЯИМ). 2.1 Построение ER-диаграммы К сущностям предметной области относятся: Читатели, Сотрудники, Книги. Сущность Читатели имеет следующие атрибуты: − Код читателя − Фамилия читателя − Имя читателя − Отчество читателя − Дата рождения читателя − Адрес читателя − Телефон читателя − Паспортные данные читателя Сущность Сотрудники имеет следующие атрибуты: − Код сотрудника − Фамилия сотрудника − Имя сотрудника − Отчество сотрудника − Дата рождения сотрудника − Адрес сотрудника − Телефон сотрудника − Паспортные данные сотрудника − Должность сотрудника Сущность Книги имеет следующие атрибуты: − ISBN (код) книги − Название книги − Автор книги − Издательство книги − Год издания книги − Жанр книги Сущности Сотрудники и Книги имеют сущности обозначения. Выделим из сущности Сотрудники сущность-обозначение: Должность. Сущность-обозначение Должность имеет следующие атрибуты: − Код должности − Название должности − Оклад Изобразим это в виде диаграммы «Сущность-Связь» (ER-диаграммы). Рисунок 1 – ER-диаграмма сущности Сотрудники Выделим из сущности Книги сущности-обозначения: Автор, Издательство, Жанр. Сущность Автор имеет следующие основные атрибуты: − Код автора − Фамилия автора − Имя автора Сущность Издательство имеет следующие основные атрибуты: − Код издательства − Название издательства − Адрес издательства Сущность Жанр имеет следующие основные атрибуты: − Код жанра − Название жанра Изобразим это в виде диаграммы «Сущность-Связь» (ER-диаграммы). Рисунок 2 – ER-диаграмма сущности Книги Между сущностью Читатели и сущностью Книги есть смысловая связь: «Читатели берут книги». Рисунок 3 – ER-диаграмма связи сущностей Читатели и Книги Связь Берут имеет размерность M:N, то есть «многие-ко-многим», так как одна книга может попасть ко многим читателям и один читатель может взять много книг. Представим данную связь двумя связями «один-ко-многим» и ассоциацией Выданные книги. К ассоциации Выданные книги также присоединяется связь «один-ко-многим» с сущностью Сотрудники, то есть один сотрудник может выдать множество книг, но одна книга не может быть выдана многими сотрудниками (за конкретный момент времени). Окончательный вариант ER-диаграммы представлен на рисунке 4. Рисунок 4 – Окончательный вариант ER-диаграммы 3 ПОСТРОЕНИЕ ДАТОЛОГИЧЕСКОЙ МОДЕЛИ БД Логическая структура базы данных, а также сама заполненная данными БД является отображением реальной предметной области. Поэтому на проектное решение непосредственно влияет специфика отображаемой предметной области, отраженная в инфологической модели и выбор конкретной СУБД. По ряду причин, среда SQL Server Management Studio 2008 является оптимальным решением при выборе среды управления базой данных. Реляционная база данных должна быть нормализована. Процесс нормализации имеет цель устранения избыточности данных и заключается в приведении к третьей нормальной форме. Первая нормальная форма (1НФ) требует, чтобы таблица была плоской и не содержала повторяющихся групп, иными словами она не должна содержать ячеек, включающих несколько значений. Вторая нормальная форма (2НФ) требует, чтобы все поля таблицы зависели от первичного ключа, то есть, чтобы первичный ключ однозначно идентифицировал запись. Для третьей нормальной формы (ЗНФ) требуется, чтобы все не ключевые столбцы таблицы зависели от первичного ключа таблицы, но были независимы друг от друга. Для четвертой нормальной формы (4НФ) требуется, чтобы в одной таблице не содержались независимые элементы данных, если между ними существует отношение "многие-ко-многим". Исходя из вышесказанного, база данных «Библиотека» (labrary) будет представлять собой восемь связанных таблиц: Книги (book), Авторы (author), Жанр (genre), Издательства (publishing), Читатели (reader), Сотрудники (employee), Должности (post) и Выданные_книги (not_book). Наличие таблиц Авторы, Жанр, Издательства и Должности обеспечивают устранение избыточности. Таблица Выданные_книги устраняет отношение «многие-ко- многим», что соответствует требованиям 4НФ. Таблица 1 содержит информацию о таблицах, находящихся в составе БД «labrary». Таблица 1. Атрибуты и отношения в составе таблиц БД «labrary» Таблица Имя столбца Тип данных Ключ таблицы по полю Ограничения Первичный ключ Внешний ключ 1 2 3 4 5 6 book book_ISBN int + identity, not null book_name varchar(40) − not null author_id int + publish_id int + book_year date − genre_id int + author author_id int + identity, not null author_name varchar(40) − not null author_forname varchar(25) − not null genre genre_id int + identity, not null genre_name varchar(30) − not null publishing publish_id int + identity, not null publish_name varchar(20) − not null publish_adress varchar(50) − not null reader read_id int + identity, not null read_name varchar(40) − not null read_birth date − not null read_adress varchar(20) − not null read_num int − read_passport int − read_forname varchar(25) − read_lastname varchar(25) − employee empl_id int + identity, not null empl_name varchar(40) − not null empl_birth date − not null empl_adress varchar(20) − not null empl_num int − empl_passport int − post_id int + empl_forname varchar(25) − not null empl_lastname varchar(25) − not null post post_id int + identity, not null post_name varchar(20) − not null post_salary smallmoney − not null not_book nbook_id int + identity, not null book_id int + read_id int + nbook_isdate date − not null nbook_retdate date − returnflag bit − empl_id int + Рисунок 5 – Диаграмма базы данных Датологическая модель базы данных разработана в соответствии с принципами нормализации, следующий этап разработки – создание физической модели данных. 4 ПОСТРОЕНИЕ ФИЗИЧЕСКОЙ МОДЕЛИ БД Физическое проектирование — создание базы данных в среде SQL Server Management Studio 2008 средствами универсального компьютерного языка SQL, применяемого для создания, модификации и управления данными в реляционных базах данных. 1. Запрос на создание базы данных create database labrary 2. Запрос на создание таблиц create table employee (empl_id int primary key identity(1,1), empl_name varchar (40) not null, empl_birth date not null, empl_adress varchar (20) not null, empl_num int, empl_passport int not null, post_id int) create table post (post_id int primary key identity(1,1), post_name varchar (20) not null, post_salary smallmoney not null) create table publishing (publish_id int primary key identity(1,1), publish_name varchar (20) not null, publish_burg varchar (20) not null) create table genre (genre_id int primary key identity(1,1), genre_name varchar (30) not null) create table author (author_id int primary key identity(1,1), author_name varchar (40) not null) create table book (book_id int primary key identity(1,1), book_name varchar (40) not null, author_id int, publish_id int, book_year date, genre_id int) create table reader (read_id int primary key identity(1,1), read_name varchar (40) not null, read_birth date not null, read_adress varchar (20) not null, read_num int, read_passport int not null) create table not_book (nbook_id int primary key identity(1,1), book_id int, read_id int, nbook_isdate date not null, nbook_retdate date, returnflag bit, empl_id int) 3. Запрос на создание внешних ключей alter table employee add constraint a1 foreign key (post_id) references post (post_id) alter table book add constraint b1 foreign key (author_id) references author (author_id) alter table book add constraint c1 foreign key (publish_id) references publishing (publish_id) alter table book add constraint d1 foreign key (genre_id) references genre (genre_id) alter table not_book add constraint e1 foreign key (book_id) references book (book_ISBN) alter table not_book add constraint g1 foreign key (read_id) references reader (read_id) alter table not_book add constraint k1 foreign key (empl_id) references employee (empl_id) 4. Запрос на изменение полей таблиц на примере таблицы «Publishing» alter table publishing drop column publish_burg alter table publishing add publish_adress varchar (50) not null 5. Запрос на заполнение таблиц на примере таблицы «Publishing» insert into publishing values ('Айрис-пресс', '129626, Москва, пр. Мира, д. 104, 2-й этаж') insert into publishing values ('Альфа-книга', '125565, Москва, а/я 4') insert into publishing values ('АСТ', '129085, Москва, Звездный бульвар, д. 21') insert into publishing values ('Росмэн', '125124, Москва, а/я 62') insert into publishing values ('Бином', '127018 г. Москва, ул.Сущевский вал, 49') insert into publishing values ('Гелеос', 'Москва, Партийный пер., д.1') insert into publishing values ('Дрофа', '127018, Москва, Сущевский вал, 49') insert into publishing values ('Эксмо', '127299, г. Москва, ул. Клары Цеткин, д.18/5') Результат запроса продемонстрирован на рисунке 6. Рисунок 6 – Результат выполнения запроса на заполнение таблицы «Publishing» Аналогично заполняются остальные таблицы, скриншоты которых приведены ниже. Рисунок 7 – Таблица «Author» Рисунок 8 – Таблица «Genre» Рисунок 9 – Таблица «Book» Рисунок 10 – Таблица «Employee» Рисунок 11 – Таблица «Not_book» Рисунок 12 – Таблица «Reader» Рисунок 13 – Таблица «Post» Следующим за разработкой физической модели БД этапом является создание запросов для облегчения работы с БД. 6. Запрос на вывод списка книг на руках select book_name as not_book, read_name, nbook_isdate from reader inner join not_book inner join book on (not_book.book_id=book.book_ISBN) on (not_book.read_id = reader.read_id) where returnflag='false' Рисунок 14 – Книги на руках 7. Запрос на вывод списка книг select book_name, author_name, publish_name from author inner join book inner join publishing on (book.publish_id = publishing.publish_id) on (book.author_id = author.author_id) Рисунок 15 – Список книг 8. Запрос на вывод списка книг в наличии select book_name, returnflag from book full outer join not_book on (book.book_ISBN=not_book.book_id) where not (book_ISBN in (select book_id from not_book) ) or (book_ISBN in (select book_id from not_book where returnflag=1)) Рисунок 16 – Книги в наличии 9. Запрос на вывод списка классических книг select book_name, genre_name from book inner join genre on (book.genre_id=genre.genre_id) where genre_name='Классика' Рисунок 17 – Классические книги 10. Запрос на вывод картотеки читателей select read_name + read_forname as read_name, read_adress, read_num from reader order by read_name asc Рисунок 18 – Картотека читателей 11. Запрос на подсчет средней заработной платы select avg(post_salary) as avg_salary from post Рисунок 19 – Средняя заработная плата 12. Запрос на подсчет штата сотрудников select count(empl_id) as count_employee from employee Рисунок 20 – Штат сотрудников 13. Запрос на поиск читателя по фамилии select read_forname, read_name, read_lastname from reader where read_forname like('Разумова%') Рисунок 21 – Результат поиска 14. Запрос на группировку и подсчет книг по жанрам select genre_name, count(book_name) as count_book from genre inner join book on (genre.genre_id=book.genre_id) group by genre_name Рисунок 22 – Количество книг по жанрам 15. Запрос на подсчет времени, которое книга была на руках select DATEDIFF (DD,nbook_isdate, nbook_retdate) as total_time, book_name, read_forname, read_name, read_lastname from book inner join not_book inner join reader on (reader.read_id = not_book.read_id) on (book.book_ISBN = not_book.book_id) where returnflag=1 Рисунок 23 – Время, которая книга была на руках Следующий шаг – создание представлений для определенных ранее групп пользователей. 16. Представление для типовой группы пользователей – читатели create view read_view as select book_name, author_name + author_forname as author_name from author inner join book full outer join not_book on (book.book_ISBN=not_book.book_id) on (book.author_id=author.author_id) where not (book_ISBN in (select book_id from not_book) ) or (book_ISBN in (select book_id from not_book where returnflag=1)) select * from read_view Рисунок 24 – Список книг в наличии с указанием авторов 17. Представление для типовой группы пользователей – директор create view direct_view as select post_salary, empl_name + ' ' + empl_forname + ' ' + empl_lastname as employee_name, post_name from employee inner join post on (employee.post_id = post.post_id) select * from direct_view Рисунок 25 – Штат сотрудников с указанием должности и размера оклада 18. Представление для типовой группы пользователей – сотрудники create view empl_view as select book_name, publish_name + ', адресс: ' + publish_adress as publishing from publishing inner join book on (book.publish_id=publishing.publish_id) select * from empl_view Рисунок 26 – Список книг с указанием издательства Необходимость создания процедур, функций и триггеров обусловлена упрощением работы сотрудника. Процедура поиска книг по входным данным снижает времени на аналогичную процедуру в ручном исполнении. Процедура подсчета времени до сдачи книг и функция, обеспечивающая оформление заказа на книги, также является необходимой частью комфортной работы оператора БД. 19. Процедура поиска книги по входным данным create procedure search_book @a varchar(40)as select book_name, author_name + author_forname as author_name, publish_name from author inner join book inner join publishing on (book.publish_id=publishing.publish_id)on (book.author_id=author.author_id) where book_name = @a execute search_book 'Война и мир. Том 1' Рисунок 27 – Результат поиска 20. Процедура подсчета времени до сдачи книги create procedure time_notbook @a date as declare @z date, @i int, @name varchar(40) set @i=1 while(@i<(select MAX(nbook_id) from not_book)) begin set @name = (select book_name from book where book_ISBN=@i) if (select returnflag from not_book where nbook_id=@i)= 0 begin set @z = (select nbook_isdate from not_book where nbook_id=@i) if dateadd(dd,10,@z)>@a begin declare @as char(50) set @as = 'до сдачи книги "' + @name + '" осталось ' + convert(varchar(5),(datediff(dd,@a,@z)))+' дней' Print @as end else Print'книга "'+ @name + '" задержана на ' + convert(varchar(5),datediff(dd,dateadd(dd,10,@z),@a))+ ' дней' end set @i+=1 end execute time_notbook '2014-08-27' Рисунок 28 – Информация о времени до сдачи книг 21. Функция для проверки наличия книг и оформления заказа create function order_book (@book_name varchar(40), @publish_name varchar(40), @book_year date) returns varchar(1000) as begin declare @i int, @g varchar(1000) set @i=1 set @g = 'Книга ' +@book_name+' ' + convert(varchar(5),year(@book_year)) +' года издания (издательство: ' +@publish_name+ ') не найдена. Оформите заказ книги.' while(@i<(select MAX(book_ISBN) from book)) begin if (((select book_name from book where book_ISBN=@i)= @book_name) and ((select publish_name from book inner join publishing on (book.publish_id=publishing.publish_id) where book_ISBN=@i)= @publish_name) and ((select book_year from book where book_ISBN=@i)= @book_year)) begin set @g = 'Книга "' +@book_name+'" ' + convert(varchar(5),year(@book_year)) +' года издания (издательство: ' +@publish_name+ ') имеется в библиотеке. Заказ не требуется.' break end set @i += 1 end return @g end select dbo.order_book ('Разговорный английский', 'Айрис-пресс1', '2013-01- 01') Рисунок 29 – Информация о времени до сдачи книг 22. Триггер на удаление информации из таблиц create trigger delete_book on not_book for delete as delete book from book, deleted where book.book_ISBN = deleted.book_id print 'Deleted from not_book' go Транзакция – это совокупность одной или нескольких SQL-инструкций, имеющих начало и конец. В конце транзакции происходит либо ее отмена, либо подтверждение. Отмена транзакции называется откатом (rollback), так как идет последовательная отмена всех сделанных изменений. Подтверждение транзакции называется фиксацией (commit). Транзакция на изменение флага при внесении информации о дате возврата книги имеет логическую подоплеку, так как в случае, если оператор при внесении даты возврата не внесет изменений в поле метки, возникнет дезинформация, во всех запросах данная книга будет выводится как не имеющаяся в наличии, что может повлечь за собой возможный сбой системы. 23. Транзакция на изменение флага при внесении информации о дате возврата книги declare @i int set @i=2 update not_book set nbook_retdate='2014-01-01' where nbook_id=@i begin tran if ((select nbook_retdate from not_book where nbook_id=@i) is not null) begin update not_book set returnflag='true' where nbook_id=(select nbook_id from not_book where nbook_id=@i) end if (@@error = 0) begin commit tran end else begin print 'Возникла ошибка' rollback tran end Рисунок 30 – До внесения даты возврата книги (nbook_id=5) Рисунок 31 –После внесения даты возврата книги (nbook_id=5) Для упрощения управления правами пользователя задействуются роли, которые похожи на группы системы безопасности Windows. При работе отдельные логически связанные пользователи помещаются в одну роль. В настоящей базе данных присутствуют три группы пользователей – сотрудники, читатели и директор. Роли позволяют ограничить действия, которые пользователи могут выполнять с элементами базы данных: таблицами, представлениями, функциями или процедурами. Например, для пользователей- читателей запрещено редактирование в любом виде, представление доступно только для просмотра; пользователи-сотрудники имеют право на чтение, запись и удаление данных; пользователь-директор имеет право на чтение и обновление записей представления. 24. Создание ролей и разрешений для работы с представлениями create role employee_role; create role reader_role; create role director_role; grant select, update on direct_view to director_role grant select, insert, delete on empl_view to employee_role grant select on read_view to reader_role deny update, insert, alter, delete on read_view to reader_role Рисунок 32 – Роли базы данных ЗАКЛЮЧЕНИЕ Современные программные системы становятся сложнее, благодаря чему решение многих задач в различных областях человеческой деятельности автоматизируется, в том числе это отразилось и на работе библиотеки. В процессе создания курсового проекта была разработана реляционная база данных по предметной области «Библиотека». Проектирование проходило в три этапа: − построение инфологической модели данных; − построение датологоческой модели данных; − разработка физической модели данных. Для упрощения работы с базой, были созданы ряд запросов, хранимых процедур, транзакций, функций и триггеров, которые автоматизируют основные процессы взаимодействия пользователя с каталогами данных. Базы данных ориентирована на работу с несколькими группами пользователей, что значительно расширяет область её применения. СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ 1. Андон Ф., Резниченко В. Язык запросов SQL / Ф. Андон, В. Резниченко – СПб.:Питер, 2006. – 415 с. 2. Глушаков С.В., Ломотько Д.В. Базы данных. Учебный курс / С.В. Глушаков – Ростов н/Д.: Феникс, 2000. – 504 с. 3. Карпова Т.С. Базы данных. Модели, разработка, реализация / Т.С. Карпова – СПб.: Питер, 2002. – 304 с. 4. Корнеев В.В. Базы данных. Интеллектуальная обработка информации / В.В. Корнеев – М.:Нолидж, 2000. – 352 с. 5. Ханcен Г., Ханcен Д. Базы данных. Разработка и управление / Г. Хансен, Д. Хансен – М.: Бином, 2012. – 704 с. 6. Шкарина Л. Язык SQL: учебный курс / Л. Шкарина. – СПб.:Питер, 2011. – 592 с. 7. Каталог API (Microsoft) и справочных материалов [Электронный ресурс] – Режим доступа. – URL: http://msdn.microsoft.com/ru-ru/library/ms123401.aspx |