VIII Международная студенческая научная конференция Студенческий научный форум - 2016

ВВЕДЕНИЕ

В различных сферах человеческой деятельности широкое распространение получили технологии, использующие базы данных для систематизации и хранения производственной информации. Это обусловлено тем, что настоящий раздел информационных технологий имеет значительную степень внедряемости и на практике довольно просто интегрируется под каждый конкретный случай. К тому же в настоящее время существует немало всевозможных вариантов реализации баз данных (БД) и систем управления базами данных (СУБД).

База данных – это информационная модель, целью создания которой является упорядоченное хранение информации, обладающей одинаковым набором свойств.

Система управления базами данных – это популярное инструментальное средство, применяемое для создания и работы с БД больших объемов.

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

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

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

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

1. Запрос на создание таблиц

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)

1. Запрос на создание внешних ключей

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)

1. Запрос на изменение полей таблиц на примере таблицы «Publishing»

alter table publishing drop column publish_burg

alter table publishing add publish_adress varchar (50) not null

1. Запрос на заполнение таблиц на примере таблицы «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»

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

1. Запрос на вывод списка книг на руках

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 – Книги на руках

1. Запрос на вывод списка книг

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 – Список книг

1. Запрос на вывод списка книг в наличии

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 – Книги в наличии

1. Запрос на вывод списка классических книг

select book_name, genre_name from book inner join genre on (book.genre_id=genre.genre_id) where genre_name='Классика'

Рисунок 17 – Классические книги

1. Запрос на вывод картотеки читателей

select read_name + read_forname as read_name, read_adress, read_num from reader order by read_name asc

Рисунок 18 – Картотека читателей

1. Запрос на подсчет средней заработной платы

select avg(post_salary) as avg_salary from post

Рисунок 19 – Средняя заработная плата

1. Запрос на подсчет штата сотрудников

select count(empl_id) as count_employee from employee

Рисунок 20 – Штат сотрудников

1. Запрос на поиск читателя по фамилии

select read_forname, read_name, read_lastname from reader where read_forname like('Разумова%')

Рисунок 21 – Результат поиска

1. Запрос на группировку и подсчет книг по жанрам

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 – Количество книг по жанрам

1. Запрос на подсчет времени, которое книга была на руках

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 – Время, которая книга была на руках

Следующий шаг – создание представлений для определенных ранее групп пользователей.

1. Представление для типовой группы пользователей – читатели

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 – Список книг в наличии с указанием авторов

1. Представление для типовой группы пользователей – директор

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 – Штат сотрудников с указанием должности и размера оклада

1. Представление для типовой группы пользователей – сотрудники

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 – Список книг с указанием издательства

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

1. Процедура поиска книги по входным данным

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 – Результат поиска

1. Процедура подсчета времени до сдачи книги

create procedure time_notbook @a date as

declare @z date, @i int, @name varchar(40)

set @i=1

while(@i@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 – Информация о времени до сдачи книг

1. Функция для проверки наличия книг и оформления заказа

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

Код для цитирования: Скопировать