Анализ, разработка и реализация базы данных встраиваемого модуля информационной системы IP-телефонии

Размещено на http://www.allbest.ru/

МИНОБРНАУКИ РОССИИ

Федеральное государственное бюджетное образовательное учреждение

высшего профессионального образования

«Пензенский государственный технологический университет»

(ПензГТУ)

Факультет информационных и образовательных технологий

название института

Кафедра «Информационные технологии и системы»

название кафедры

КУРСОВАЯ РАБОТА

Дисциплина «Управление данными»

на тему: «Анализ, разработка и реализация базы данных встраиваемого модуля информационной системы IP-телефонии»

Выполнил: студент группы 13ИД1б

Пащенко В.М.

Руководитель: ст. преподаватель кафедры ИТС

Пискаев К.Ю.

Пенза, 2015 г.



ВВЕДЕНИЕ

Данная курсовая работа имеет следующую задачу: проектирование базы данных (далее - БД) для информационной системы (далее - ИС) электронных библиотечных архивов «Библиотека.ru».

Задачи, поставленные в рамках выполнения курсовой работы:

1) изучение предметной области, её особенностей и специфик, анализ области и создание плана по реализации БД, хранящей информацию, необходимую для корректной работы элемента предметной области;

2) разработка БД информационной системы «Библиотека.ru», предназначенной для упрощения и автоматизации работы с документацией в муниципальных библиотеках на территории РФ.

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

Данная работа включает в себя 5 основных разделов:

1) анализ предметной области: краткая аннотация к курсовой работе, определение основных понятий предметной области, пояснение структуры и функционирования распределенной системы хранения данных в рабочем режиме;

2) моделирование анализируемой предметной области: внедрение в разработку основных понятий реляционной БД (ключей, значений, атрибутов, связей), проектирование схемы в БД инструментом визуального моделирования Sparx Enterprise Architect 12 (далее - EA), генерация SQL-кода проектируемой БД, перенос схемы БД в систему управления базами данных (далее - СУБД) MySQL 5.1.73 и начальное заполнение БД, создание диаграммы посредством использования Web-интерфейса администрирования баз данных phpMyAdmin;

3) работа в смоделированной БД посредством базовых запросов языка SQL: создание SQL-кода запросов, необходимых для использования в БД - выборка данных из таблицы, добавление, обновление и удаление данных из таблиц, применение к таблицам встроенных функций;

4) работа в смоделированной БД посредством сложных запросов языка SQL: создание SQL-кода запросов, необходимых для использования в БД - применение подзапросов в качестве условий SQL-команд, выборка данных из нескольких таблиц одновременно, применение индексов к отдельным столбцам таблиц, внедрение дополнительных таблиц в проектируемую БД (создание, редактирование, удаление таблиц).



1. АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ

табличный скалярный запрос данные

Для разработки проекта, а именно алгоритмов работы и основных принципов его действия, необходим анализ предметной области. Анализ предметной области - деятельность, направленная на выявление реальных потребностей заказчика, а также на выяснения смысла высказанных требований. Анализ предметной области является первым шагом этапа системного анализа, с которого начинается разработка программной системы.

1.1 Общие сведения о проекте

Главной целью разрабатываемой базы данных является хранения данных, необходимых для функционирования информационной системы «Библиотека.ru». Данная ИС предназначена для муниципальных библиотек на территории РФ, а именно:

1) автоматизация переноса данных с архивов библиотек в электронную среду;

2) автоматизация процесса ведения документации работниками заведений (в первую очередь, библиотекарей);

3) упрощение регистрации выдачи книг посетителям библиотеки;

4) автоматизация оформления отчетности для руководителей заведений (глав администраций, имеющих библиотеку как собственность).

Целевой аудиторией системы «Библиотека.ru» являются библиотеки как муниципальные унитарные организации, имеющие большую базу литературы, базу посетителей (клиентов) и необходимость в создании многофункциональной информационной структуры для упрощения процесса документооборота и управления системой архивов библиотеки.

Схему работы данной ИС стоит поделить на 2 части.

1) Клиентская часть. При выдаче книги посетителю библиотекарь регистрирует факт выдачи и информацию о выданной книге. Система сохраняет полученную информацию в общей базе, присвоив ей личный идентификатор и связав её с дополнительной информацией (данные об авторе книги, о посетителе, которому книга была выдана и т.д.).

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

1.2 Основные сведения о предметной области

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

Современные библиотеки получили развитие с 15 в. с изобретением книгопечатания. В России монастырские библиотеки появились в 11 - 12 вв., первые светские - в 18 в. при Академии наук, Московском университете, первая крупная публичная библиотека - в Санкт-Петербурге в 1814. В начале 1990-х гг. в Российской Федерации (далее - РФ) имелось около 63 тыс. библиотек общим фондом свыше 1,2 млрд. экземпляров.

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

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

В последнее время существует тенденция к переходу на электронные литературные ресурсы, доступные каждому, кто имеет электронное устройство (мобильный телефон, планшет, электронная книга, ноутбук, компьютер), способное отобразить текстовую информацию. Но бумажные книги также не теряют свою актуальность и будут популярны еще очень долго. Причиной тому является атмосфера, получаемая читателями при использовании бумажных книг. Также бумажная литература является энергонезависимым источником и имеет способность к передаче между людьми. Таким образом появилось общественное движение буккросинга, задачей которой является объединение людей из разных культур и слоев населения по следующему принципу: человек, прочитав книгу, оставляет её в общественном месте (парк, кафе, станция метро), для того, чтобы другой человек мог эту книгу найти и прочитать; тот в свою очередь должен повторить это же действие.

В настоящее время на рынке ИС не существует свободных решений для муниципальных учреждений, что приводит к дополнительным расходам из средств государственного бюджета. В этом и состоит основная цель информационной системы «Библиотека.ru» - создание продукта свободного программного обеспечения для последующего внедрения в муниципальную структуру. Данное решение создаст много положительных результатов, в первую очередь, сохранение финансовых средств на благоустройство территории местных районов, муниципальных учреждений. Примером для разработки и проектирования системы является бесплатная информационная система доступа к электронной литературе "Единое окно доступа к образовательным ресурсам" (ИС "Единое окно ").

В рамках данной курсовой работы был проведен анализ потенциальных конкурентов системы на территории РФ - «Ирбис» и «Моя библиотека». Ниже приводится таблица сравнения информационных библиотечных систем автоматизации процессов в малых и средних библиотеках. Критери1 объема документации оценивался по десятибалльной шкале (0 - отсутствие критерия в структуре программного продукта, 10 - компонент программного обеспечения доведен до совершенства и является одним из его основных преимуществ). Данные взяты с официальных сайтов анализируемых систем.

Таблица 1 - сравнение информационных библиотечных систем по данным на 13.12.2015г.

ИС	Asterisk	FreeSWITCH
Максимальное кол-во документов	16000000	20000000
Наличие графического интерфейса (GUI)	да	да
Дата выхода последней версии	2012 г.	2011 г.
Совместимость с международными коммуникативными форматами	да	нет
Оценка объема документации системы	6	8

Данный анализ показал, что конкурентные ИС имеют свои преимущества и недостатки. Основной задачей при реализации системы «Библиотека.ru» будет являться внедрение полноценного аналога платного программного обеспечения с открытым исходным кодом. Совместимость с международными коммуникативными форматами также будет являться важным компонентом при дальнейшей разработке проекта, так как при успехе реализации возможно дальнейшее расширение за пределами РФ.



1.3 Выделение справочных и оперативных данных

БД информационной системы, названная library_ru содержит набор справочных и оперативных данных. Всего БД содержит 7 таблиц. Справочными данными является информация о работниках, использующих библиотечную систему (таблица employees), информация об авторах книг и издательствах (таблицы authors и publishers соответственно), а также данные о жанре литературы (таблица genre). Динамическими данными является информация хранящихся на территории библиотеки книг и зарегистрированных в системе посетителях (таблицы books и customers соответственно), а также данные о выдаче книг (таблица rent).

1.4 Предполагаемые запросы к базе данных

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

Предполагаемые запросы к БД library_ru:

1) добавить в таблицу customers нового посетителя;

2) загрузить в таблицу books сведения о прибывших книгах;

3) создать выборку из таблицы rent с целью просмотра истории выдачи книг отдельному посетителю;

4) выбрать из таблицы books все книги с определенным ID автора;

5) выбрать из таблицы authors всех авторов определенной страны, труды которых приходятся на отдельный век;

6) удалить из таблицы employees уволившегося сотрудника.

1.5 Перечень хранимой информации

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

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

Выводы по разделу

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

2. МОДЕЛИРОВАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ

Моделирование и интерпретация моделей как основное средство и инструмент научного отражения и исследования разного рода реальных объектов сами являются предметом многочисленных исследований и разработок. Как известно, существует два основных вида нематематических моделей: содержательно-описательные и формально-математические. Инструментом исследований моделируемого объекта в первом случае являются мыслительно-логические эксперименты с его содержательно-описательной моделью. Такое моделирование используется в тех случаях, когда моделируемый объект слабо структурирован, т.е. по каким-то причинам для него в рамках существующей теории отсутствует возможность сильной структуризации, приводящей к математическим методам исследования. Следует отметить, что модели сложных реальных объектов и явлений нередко оказываются интегрированными, т.е. включающими в себя и содержательно-описательную, и формально-математическую составляющие.

Модель предметной области - это некоторая теория, в которой предметная область отражается в виде совокупности:

1) базовых структурированных понятий - базовых модельных объектов и отношений между ними (законов);

2) правил формирования моделей объектов, не сводимых к одной из базовых моделей, в виде специальных структур, образуемых из базовых понятий;

3) методов анализа моделей, обеспечивающих получение ответов на вопросы, рассматриваемые этой теорией.

Модель предметной области, как правило, включает в себя две части: описательную и функциональную. Описательная (декларативная) часть теории представляет собой совокупность базовых понятий, специфицирующих в рамках этой теории объекты предметной области и отношения между ними. Функциональная (процедурная) часть теории содержит принятые в ней правила и способы манипулирования моделями с целью решения задач.

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

2.1 Общие сведения о моделировании предметной области

Стадии формирования модели предметной области:

1) формализация, обеспечивающая однозначное описание структуры предметной области;

2) применение графических средств отображения модели;

3) реализация, подразумевающая наличие средств физической реализации модели предметной области;

4) обеспечение оценки эффективности реализации модели предметной области на основе определенных методов и вычисляемых показателей (в данном случае оценку эффективности обеспечивает заполнение БД данными в корректном формате и отображение результатов работы).

На стадии формализации была сформирована схема базы данных library_ru, распределены данные по таблицам в соответствии с теориями нормальных форм, выделены сущности, атрибуты, ключи, связи между таблицами - компоненты, формирующие БД. Стадия применения графических средств отображения модели была реализована инструментом визуального моделирования Sparx Enterprise Architect посредством добавления таблиц, атрибутов, формирования первичных и внешних ключей и визуализации конечной схемы БД. Стадия реализации состояла из двух частей: генерация SQL-кода создания БД в EA и дальнейший перенос схемы в РСУБД MySQL посредством запуска сгенерированного SQL-кода. Стадия обеспечения эффективности реализации модели также состояла из двух частей: загрузка данных в таблицы проектируемой БД и отображение диаграммы отредактированной БД в веб-инструменте администрирования СУБД phpMyAdmin.

2.2 Выделение сущностей, атрибутов, ключей, связей

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

В процессе формализации предметной области БД были созданы 7 таблиц, связанных между собой. Распределенная архитектура БД обеспечивает её мобильность, сокращение времени запросов и отсутствие избыточности в элементах базы данных.

Таблицы спроектированы в соответствии с теорией пяти нормальных форм:

1) значения всех атрибутов таблицы атомарны (неделимы);

2) каждый неключевой атрибут функционально полно зависит от первичного ключа таблицы;

3) каждый неключевой атрибут нетранзитивно зависит от первичного ключа (т.е. зависит только от первичного ключа, и больше ни от одного другого атрибута);

4) отстутсвуют многозначные зависимости, не являющиеся функциональными зависимостями;

5) любая зависимость определяется только его возможными ключами таблицы.

Всего в рамках проектируемой БД были выделены три сущности: сведения об участниках процесса приема/передачи библиотечных ресурсов - библиотекарей и посетителей, журнал выдачи книг и сводная информация об имеющейся литературе и её составляющей: авторов, издателей, жанров произведения. В соответствии с теорией нормальных форм информация о пользователях системы была разделена на 2 таблицы: employees (персональные данные о работниках, использующих библиотечную ИС) и customers (персональные данные о посетителях библиотеки, занесенных в общий каталог системы). В таблицах 1-4 представлена структура вышеперечисленных таблиц, атрибуты, тип данных атрибутов, а также выделены атрибуты, играющие роль первичного ключа (PK) и внешнего ключа (FK).

Таблица 3 - общая структура таблиц customers и employees

id (int)	fname (varchar)	lname (varchar)	city (varchar)	street (varchar)	home (varchar)
PK
ap (int)	phone (varchar)	passport (int)	birthyear (int)	birthmonth (varchar)	birthday (int)

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

Таблица 4 - таблица rent

rent_id (int)	book_id (int)	customer_id (int)	employee_id (int)	deli_date (timestamp)	return_date (timestamp)
PK	FK (books)	FK (customers)	FK (employees)

Информация о книге была разбита на 4 таблицы: таблица genre, хранящие сведения обо всех типах произведений, хранящихся в библиотеке, таблица author, в которой указаны сведения об авторе книги, таблица publishers, где хранится информация обо всех издателях, впускающих литературу на территории РФ, книги которых присутствуют в сводном каталоге библиотеки и главная таблица books, которая связана со всеми вышеперечисленными таблицами посредством внешних ключей, основной информацией которой является название книги и её ISBN-идентификатор - международный стандартный номер книги.

Таблица 5 - таблица genre

genre_id (int)	genre_name (varchar)
PK

Таблица 6 - таблица authors

au_id (int)	au_fname (varchar)	au_lname (varchar)	au_country (varchar)	age (int)
PK

Таблица 7 - таблица publishers

pub_id (int)	pub_name (varchar)	pub_city (varchar)	pub_country (varchar)
PK

Таблица 8 - таблица books

book_id (int)	au_id (int)	pub_id (int)	genre_id (int)	book_title (varchar)	pages (int)	pub_year (int)	isbn (varchar)
PK	FK (authors)	FK (publishers)	FK (genre)



2.3 Проектирование диаграммы «сущность-связь» в Enterprise Architect

Для наглядности предметной области была спроектирована диаграмма БД «сущность-связь» с применением графических средств отображения модели. Очень важным свойством модели "сущность-связь" является то, что она может быть представлена в виде графической схемы. Это значительно облегчает анализ предметной области. В качестве средства визуализации была использована программа Sparx EA 12. Сначала были созданы таблицы БД. Затем были добавлены атрибуты с выделением первичных ключей и присвоением ограничения «NOT NULL» для некоторых атрибутов. Напоследок средствами EA было проведено построение диаграммы полученной базы данных с последующим нанесением связей «первичный ключ-внешний ключ» между таблицами. На рисунке 1 наглядно показан процесс проектирования таблиц с помощью встроенного инструмента Database Builder. В приложении A.1 показана итоговая физическая схема БД.

Рисунок 1 - проектирование таблиц базы данных в EA

2.4 Подготовка диаграммы «сущность-связь» в Enterprise Architect к переносу на целевую СУБД

В процессе подготовки диаграммы БД «сущность-связь» к переносу на целевую СУБД MySQL был автоматически сгенерирован SQL-код - скрипт создания таблиц базы данных. Во встроенном инструменте EA Database Builder в опциях главного элемента MySQL (открытие меню правой кнопкой мыши) был выбран пункт «Generate DDL». Затем в меню сохранения схемы в формате SQL были выбраны все таблицы БД и путь к сохранению файла. При нажатии кнопки «Generate» сгенерированный SQL-код сохранился на компьютере. Так диаграмма сущность-связь была подготовлена к переносу на целевую СУБД. На рисунке 3 наглядно показан процесс сохранения схемы БД в формате SQL. В приложении Б.1 находится часть сгенерированного SQL-кода.

Рисунок 2 - генерация SQL-кода в EA

2.5 Создание базы данных в MySQL. Перенос схемы из Enterprise Architect

Целевой СУБД, предназначенной для управления данными, хранящимися в библиотечной ИС «Библиотека.ru» является реляционная СУБД MySQL 5.1.73. Операционной системой, на которой расположен MySQL является Debian Jessie 8.2 - дистрибутив семейства Linux. Данный дистрибутив был выбран для платформы разработки ИС (в частности базы данных) по нескольким причинам: наличие одной из лучших систем управления пакетами apt, оперативная поддержка и большое пользовательское сообщество, а также способность становления частью отказоустойчивой системы.

База данных на основе SQL кода была создана с помощью командной оболочки bash.

cd /opt

mysql -u root -p

CREATE DATABASE library_ru;

USE library_ru

mysql library_ru < database.sql

Листинг 1 - создание базы данных в MySQL

2.6 Начальное заполнение базы данных

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

Существуют 2 способа добавления записей в таблицы: с помощью команды INSERT и с помощью загрузки записей из текстового файла (команда LOAD DATA LOCAL INFILE). Первый способ более удобен для добавления небольшого количества записей (не больше 5), второй способ - для большого количества записей. В данной задаче наиболее целесообразным выглядит добавление записей из текстового файла. Для этого для каждой таблицы необходимо создать отдельный текстовый файл, записать в него данные в формате, удобном для записи в хранилище РСУБД, и загрузить данные из файла в таблицу при помощи консоли MySQL. Для добавления записи о модуле достаточно выполнения команды INSERT. Для начального заполнения базы данных были созданы 7 текстовых файлов. На рисунке 3 показан формат записи строк в файл.

Рисунок 3 - формат текстового файла для добавления значений в БД

Затем был создан SQL-скрипт, состоящий из нескольких команд добавления данных из файла в таблицы, а также добавления записи о модуле library_ru, а именно версия модуля, имя и флаг доступности 1. Уникальный ID модуля СУБД создаст сама с помощью функции авто-инкремента значений в столбце. В листинге 2 указан список скрипт, использованный для заполнения БД.

USE library_ru;

LOAD DATA LOCAL INFILE '/opt/customers.txt' INTO TABLE customers;

LOAD DATA LOCAL INFILE '/opt/employees.txt' INTO TABLE employees;

LOAD DATA LOCAL INFILE '/opt/rent.txt' INTO TABLE rent;

LOAD DATA LOCAL INFILE '/opt/authors.txt' INTO TABLE authors;

LOAD DATA LOCAL INFILE '/opt/publishers.txt' INTO TABLE publishers;

LOAD DATA LOCAL INFILE '/opt/genre.txt' INTO TABLE genre;

LOAD DATA LOCAL INFILE '/opt/books.txt' INTO TABLE books;

Листинг 2 - SQL-скрипт заполнения БД

На рисунках 6-7 показаны результаты работы команд. В общей сложности в каждую таблицу, хранящую информацию о звонках, добавлено по 20 значений, в таблицы-словари - по 10, в таблицу информации о дикторах - 5.

Рисунок 4 - результат работы команды LOAD DATA в таблице books

2.7 Создание диаграммы средствами phpMyAdmin

Для проверки эффективности реализованной модели предметной области необходимо проверить ее графическое отображение. Таким образом мы можем убедиться, что спроектированная БД не имеет ошибок. Для этого нужно повторно построить диаграмму «сущность-связь». Для этой цели в MySQL используется Web-приложение администрирования БД phpMyAdmin. На данном этапе стоит задача проверить связность разработанной базы данных.

Создание диаграммы состоит из нескольких шагов. Сначала данные о компьютере с MySQL были добавлены в существующую конфигурацию phpMyAdmin (файл конфигурации Web-сервера Apache /etc/httpd/conf.d/phpmyadmin.conf). После этого веб-сервер Apache был перезапущен, а изменения конфигурации вступили в силу. После этого для запуска интерфейса phpMyAdmin в веб-браузере был набран адрес http://localhost/phpmyadmin с дальнейшим прохождением процедуры аутентификации.

В меню phpMyAdmin последовательно были выбраны нужные БД с последующим просмотром диаграмм «сущность-связь» (инструмент Designer в меню More). На рисунке 5 показан интерфейс управления базами данных в phpMyAdmin.

Рисунок 5 - интерфейс управления БД в phpMyAdmin

Выводы по разделу

Основной целью данного раздела являлось моделирование предметной области (выделение основных элементов базы данных, проектирование составных моделей БД) и её внедрение в рабочий процесс. Описаны общие сведения о моделировании предметной области, этапы моделирования области. Разработана схема БД, описаны сущности, атрибуты, первичные и внешние ключи, а также связи между таблицами.

Посредством инструмента графической визуализации EA была спроектирована диаграмма «сущность-связь», на основании которой автоматически сгенерирован SQL-код создания таблиц. С помощью данного кода схема БД была перенесена из EA в РСУБД MySQL, находящуюся в Linux-дистрибутиве Debian Jessie 8.2. Таблицы созданной БД были заполнены данными из текстовых файлов. Отредактированная БД была повторно визуализирована в виде диаграммы с помощью phpMyAdmin.



3. СОЗДАНИЕ И ЗАПУСК БАЗОВЫХ ЗАПРОСОВ SQL

3.1 Общие сведения о базовых запросах SQL

В процессе выполнения данного раздела к разработанной БД информационной системы "Библиотека.ru" были применены базовые запросы языка SQL.

Для этого на компьютере с установленным MySQL необходимо запустить клиента mysql, в окне которого можно вводить команды SQL. Команды SQL не чувствительны к регистру, но традиционно они набираются прописными буквами.

Структурированный язык запросов (Structured Query Language) - стандарт коммуникации с базой данных, который поддержан ANSI. Самая последняя версия - SQL-99, хотя новый стандарт SQL-200n уже находится в разработке. Большинство баз данных твердо придерживается стандарта ANSI-92. Было много обсуждений по поводу введения более современных стандартов, но изготовители коммерческих баз данных отклоняются от этого, развивая свои новые концепции манипуляции хранимыми данными. Почти каждая отдельная база данных использует некоторый уникальный набор синтаксиса, хоть и очень сильно подобного стандарту ANSI. В большинстве случаев, этот синтаксис является расширением базового стандарта, хотя бывают случаи, когда такой синтаксис приводит к различным результатам для разных баз данных. Всегда неплохой идеей будет просмотр документации к базе данных, особенно, если получаются неожиданные результаты.

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

Есть четыре основных типа запросов данных в SQL, которые относятся к так называемому языку манипулирования данными (Data Manipulation Language или DML): SELECT - выбрать строки из таблиц; INSERT - добавить строки в таблицу; UPDATE - изменить строки в таблице; DELETE удалить строки в таблице.

3.2 Выборка данных

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

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

SELECT * FROM books ORDER BY pub_year DESC;

Листинг 3 - сортированная выборка записей

Одним из предполагаемых SELECT-запросов к базе будет выявление актов выдачи книги без даты возврата (т.е. список книг, находящихся на руках у пользователей). Результат выполнения команды показан на рисунке 6.

SELECT rent_id, book_id, customer_id, deli_date FROM rent WHERE return_date IS NULL;

Листинг 4 - выборка по условию

Рисунок 6 - результат выполнения команды SELECT в БД library_ru



Также можно выбрать статистику выдачи книг отдельному пользователю, создав запрос на выборку по уникальному ID пользователя.

SELECT book_id, employee_id, deli_date, return_date FROM rent WHERE customer_id = 5;

Листинг 5 - выборка значений по внешнему ключу

3.3 Добавление данных

Оператор INSERT применяется для добавления информации в базу данных. Более часто запросы на добавление записей в БД library_ru будут направляться таблицу rent, так как происходит постоянное пополнение списка актов выдачи книги. На рисунке 7 показан результат работы команды INSERT.

INSERT INTO rent VALUES (21, 5, 3, 1, 2015-12-23 15:16:54, NULL);

Листинг 6 - добавление информации о новом акте выдачи книги в таблицу rent



Рисунок 7 - результат добавления записи в таблицу rent.

Также присутствует необходимость в добавлении новых пользователей библиотеки. Запрос будет заключаться в добавлении персональных данных о пользователе командой INSERT.

INSERT INTO customers VALUES (21, 'Сергей', 'Миронов', 'Пенза', 'Злобина', '11а', 15, '+7-937-353-6405', 5609512986, 1976, 'Июнь', 23);

Листинг 7 - добавление нового пользователя в таблицу

3.4 Обновление справочных данных

Оператор UPDATE применяется для обновления информации в базу данных. Обычно обновление справочных данных применяется для изменения группы записей, объединенных конкретным условием. БД library_ru имеет справочные данные в таблице publishers, где указаны общие сведения об издательствах, распространяющих литературу на территории РФ, продукты которой имеются в наличии в библиотечном каталоге. Например, возможно изменение информации о месторасположении издательства при его переезде в другой город. Результат выполнения команды показан на рисунке 14.

UPDATE publishers SET pub_city='Москва' WHERE pub_name='ЛИТЕРА';

Листинг 8 - обновление данных о книжном издательстве

Рисунок 8 - результат обновления записей

3.5 Удаление данных

Оператор DELETE применяется для удаления информации из базы данных. Обычно удаление данных применяется либо для освобождения памяти под свежие записи (динамические записи), либо в том случае, если какая-либо информация больше не является актуальной (статические данные). В БД library_ru нужно стереть запись об аккаунте работника, уволившегося из организации. На рисунке 9 показан результат выполнения команды.

DELETE FROM employees WHERE employee_lname='Трофимова';

Листинг 9 - удаление записи о работнике/пользователе системы



Рисунок 9 - результат удаления записей из БД library_ru.

3.6 Применение табличных функций SQL

Встроенные табличные функции языка SQL служат для получения более точных результатов выборки команды SELECT. В данном разделе показано применение таких табличных функций, как CONCAT (объединение значений),, DATEDIFF (вычисление разницы между двумя датами) и CASE (добавление значений в зависимости от выполнения условия).

В БД library_ru можно с помощью функций CASE и DATEDIFF охарактеризовать состояние актов выдачи книг и выделить из них пользование с действующим временным сроком возврата и с истекшим сроком возврата (более 60 дней с момента начала аренды). На рисунке 10 показаны результаты работы запроса.

SELECT rent_id, book_id, customer_id,

(CASE deli_date WHEN DATEDIFF (deli_date, 0000-00-00) < 61 THEN `active' ELSE `obsolete' END) AS `state'

FROM rent WHERE return_date IS NULL ORDER BY 4 DESC;

Листинг 10 - применение операторов CASE и DATEDIFF

Рисунок 10 - результат выполнения функций CASE и DATEDIFF



Можно более наглядно показать инициалы посетителей библиотеке, занесенных в клиентскую базу системы (таблица customers), объединив их имена и фамилии в одну строку функцией CONCAT, а также вывести полную дату рождения. На рисунке 11 показаны результаты работы запроса.

SELECT CONCAT (customer_fname, ' ', customer_lname) AS 'Полное имя', CONCAT (customer_birthday, ' ', customer_birthmonth, ' ', customer_birthyear) AS 'День Рождения' FROM customers;

Листинг 11 - применение функции CONCAT

Рисунок 11 - результат выполнения функции CONCAT.

3.7 Применение скалярных функций SQL

Скалярные функции SQL предназначены для подсчета максимальных, минимальных, средних значений атрибутов, количества значений по определенному параметру и суммы их значений. Особенность скалярных функций в том, что они всегда возвращают только одно значение. В данном разделе показано применение функций MAX (максимальное значение) и COUNT (подсчет среднего значения).

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

SELECT au_id, COUNT (*) AS `Число книг в каталоге' FROM books GROUP BY au_id;

Листинг 12 - применение скалярной функции COUNT

Рисунок 12 - результат выполнения функции COUNT.

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

SELECT MAX(pub_id) AS 'Максимальное число книг от одного издателя' FROM books;

Листинг 13 - применение функции MAX

Рисунок 13 - результат выполнения функции MAX.

Выводы по разделу

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

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



4. СОЗДАНИЕ И ЗАПУСК СЛОЖНЫХ ЗАПРОСОВ SQL

4.1 Общие сведения о сложных запросах SQL

Под сложными запросами языка SQL подразумеваются запросы, которые могут обрабатывать несколько элементов данных одновременно, а именно: соединение, группировка, агрегация из разных таблиц в одну сводную структуру данных. Сложные запросы нужны для создания более масштабных операций, манипулирующих значениями БД. В данном разделе рассматриваются такие запросы, как выборка из таблиц-объединений, подзапросы команды SELECT, а также работа с таблицей как с атомарной структурой: создание дополнительных таблиц, изменение формата таблиц и удаление целых таблиц из БД. Также описываются понятие индексации и принципы работы с индексами.

4.2 Выборка данных из нескольких таблиц

SQL позволяет в одном запросе обращаться к нескольким таблицам. Именно это свойство и сделало язык SQL столь популярным. БД library_ru позволяет создавать множество объединений и запросов из нескольких таблиц одновременно, так как таблицы имеют множество связей между собой. Например, можно выполнить запрос с использованием внутреннего объединения, результатом которого будет соотношение личных данных посетителей библиотеки (имя, фамилия, место проживания) к названиям и авторам выданных книг. На рисунке 14 показан результат запроса.

SELECT r.rent_id, c.customer_fname, c.customer_lname, c.customer_city, b.book_title, a.au_fname, a.au_lname

FROM rent r

INNER JOIN customers c ON r.customer_id=c.customer_id

INNER JOIN books b ON b.book_id=r.book_id

INNER JOIN authors a ON b.au_id=a.au_id

ORDER BY r.rent_id;

Листинг 14 - создание объединений таблиц для выборки

Рисунок 14 - результаты запроса из нескольких таблиц

4.3 Применение подзапросов

Когда вы используете подзапросы в SQL, вы можете обратиться к внутреннему запросу таблицы в предложении внешнего запроса FROM , сформировав - соотнесенный подзапрос. Когда вы делаете это, подзапрос выполняется неоднократно, по одному разу для каждой строки таблицы основного запроса. Соотнесенный подзапрос - один из большого количества тонких понятий в SQL из-за сложности в его оценке. Если вы сумеете овладеть им, вы найдете что он очень мощный, потому что может выполнять сложные функции с помощью очень лаконичных указаний.

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

SELECT customer_id, customer_fname, customer_lname, customer_city FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM customers WHERE customer_city <> 'Пенза');

Листинг 15 - подзапрос

Рисунок 15 - результаты использования подзапроса.

4.4 Создание таблиц в базе данных

Процесс создания новых таблиц в базе данных является очень важным в процессе улучшения проекта или, проще говоря, работы над ошибками, когда нужно учесть дополнительную информацию при работе реализуемого проекта. В SQL таблицы создаются с помощью команды CREATE TABLE.

В рамках данного подраздела в БД library_ru создана дополнительная таблица rent_description, позволяющая описывать подробности акта выдачи книги (причина возврата книги позже положенного срока, отдельная договоренность между библиотекарем и посетителем и т.д.). Атрибутами данной таблицы являются ID акта выдачи (rent_id), играющий роль как первичного ключа, так и внешнего ключа, связывающего таблицу с другой таблицей rent и текст с подробным описанием акта выдачи (rent_text). Информация о маркировке текста будет добавлена в следующем подразделе как дополнительный атрибут таблицы. На рисунке 16 показана структура созданной таблицы.

CREATE TABLE rent_description

(

rent_id INT NOT NULL,

rent_text VARCHAR(20) DEFAULT `N/A',

PRIMARY KEY (rent_id),

FOREIGN KEY (rent_id) REFERENCES rent (rent_id)

);

Листинг 16 - создание таблицы

Рисунок 16 - структура таблицы, созданной в БД library_ru

4.5 Изменение формата таблиц в базе данных

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

1) добавить в таблицу определение нового столбца;

2) удалить столбец из таблицы;

3) изменить значение по умолчанию для какого-либо столбца;

4) добавить или удалить первичный ключ таблицы;

5) добавить или удалить внешний ключ таблицы;

6) добавить или удалить условие уникальности;

7) добавить или удалить условие на значение.

В БД library_ru в соответствии с добавленной таблицей о дополнительной информации, прилагаемой к акту выдачи книги rent_description создан атрибут rent_marked, позволяющий библиотечной ИС "Библиотека.ru" помечать отдельные описания актов, выделяя их среди остальных описаний. Это может быть использования в качестве напоминания библиотекарям, являющимися пользователями ИС. Атрибут хранит бинарные данные: "1" показывает системе, что содержащийся в том же кортеже текст нужно маркировать, "0" - что не нужно маркировать. На рисунке 17 показана структура отредактированной таблицы.

ALTER TABLE rent_description ADD COLUMN rent_marked TINYINT(1);

Листинг 17 - добавление нового атрибута в таблицу

Рисунок 17 - структура отредактированной таблицы call_status

4.6 Удаление таблиц из базы данных

Обычно с таблицей в базе данных помимо самой информации, хранящейся в базе, связано несколько объектов, например индекс, создаваемый первичным ключом, или ограничение, налагаемое на столбцы таблицы. При удалении таблицы РСУБД автоматически удаляет и любой связанный с ней индекс. Для удаления таблицы из БД необходимо выполнить команду DROP TABLEВ БД library_ru удалена таблица rent_description, созданная в данном разделе.

DROP TABLE rent_description;

Листинг 18 - удаление таблицы

4.7 Применение индексов в базе данных

Индекс -- объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск -- например, сбалансированного дерева.

В SQL индексы создаются командой CREATE INDEX. Для создания индексов атрибутов, каждое значение которых уникально, можно воспользоваться командой CREATE UNIQUE INDEX. При создании уникального индекса атрибута СУБД запретит добавлять повторяющиеся значения в индексируемый атрибут.

При создании БД library_ru системой генерирования DDL-команд в Enterprise Architect были автоматически сгенерированы индексы по первичным ключам всех созданных таблиц. Также в данной БД наиболее предпочтительным выглядит использование индексов в таблицах books и rent, так как в них предполагается хранить большое количество записей, а также часто обращаться к базе для их поиска, а также в таблице customers, где предполагается поиск посетителя библиотеки в клиентской базе по его имени и фамилии.

CREATE INDEX ind_books ON books (book_id);

CREATE INDEX ind_rent ON rent (rent_id);

CREATE INDEX ind_complex_customers ON customers (customer_fname, customer_lname);

Листинг 19 - создание индексов

Удаление индексов выполняется с помощью команды DROP INDEX. Данная операция была применена для всех индексов, созданных в ходе написания данного раздела.

ALTER TABLE books DROP INDEX ind_books;

ALTER TABLE rent DROP INDEX ind_rent;

ALTER TABLE customers DROP INDEX ind_complex_customers;

Листинг 20 - удаление индексов из таблиц

Выводы по разделу

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

В данном разделе рассмотрены несколько сложных запросов: выборка из таблиц-объединений, подзапросы команды SELECT создание дополнительных таблиц, изменение формата таблиц и удаление целых таблиц из БД.

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

В приложении Б. находится SQL-скрипт, в котором собраны сложные запросы к разработанной БД library_ru.



ЗАКЛЮЧЕНИЕ

В рамках данной курсовой работы были выполнены задачи, направленные на формирование распределенной структуры хранения данных на базе РСУБД:

1) была изучена предметная область, а именно её актуальность, основные понятия, особенности и принципы работы элемента предметной области;

2) была смоделирована БД посредством создания и запуска SQL-запросов;

3) была разработана БД для информационной системы электронных библиотечных архивов «Библиотека.ru», а также была проведена работа с БД посредством создания и запуска SQL-запросов.

В ходе выполнения курсовой работы:

1) проанализирована предметная область;

2) разработана структура распределенной системы хранения данных (выделение сущностей, ключей, атрибутов и связей БД, наиболее частых запросов к БД);

3) спроектирована диаграмма сущность-связь БД для информационной системы электронных библиотечных архивов «Библиотека.ru» с помощью визуального моделирования схемы в EA;

4) создана БД в РСУБД MySQL с последующим заполнением данных из текстового файла и визуализацией диаграммы отредактированной БД посредством веб-инструмента моделирования БД phpMyAdmin;

5) созданы базовые SQL-запросы (выборка, добавление/изменение/удаление строк) к СУБД с их последующим применением;

6) созданы сложные SQL-запросы (подзапросы, выборка из нескольких таблиц, индексация, создание/добавление/удаление таблиц) к СУБД с их последующим применением.

В рамках дальнейшей работы над проектом планируется:

1) более углубленное изучение и анализ предметной области;

2) разработка и проектирование ИС электронных библиотечных архивов «Библиотека.ru» (продумывание алгоритма работы модуля, написание исходного кода, отладка работы программных модулей);

3) внедрение модуля в систему муниципальных библиотек Пензенской области;

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



СПИСОК ЛИТЕРАТУРЫ

Основная литература

1. Кевин Янк. PHP и MySQL. От новичка к профессионалу. - Эксмо, 2013

2. Бэрон Шварц, Вадим Ткаченко. MySQL. Оптимизация производительности - Символ-плюс, 2010г.

3. Алекс Кригель, Борис Трухнов. SQL. Библия пользователя - Вильямс, 2010г.

Дополнительная литература

4. Стефан Фаро. Рефакторинг SQL-приложений - Символ, 2009 г.

5. Владимир Грекул, Галина Денищенко, Нина Коровкина. Проектирование информационных систем - Бином, 2008г.

6. Алан Бьюли. Изучаем SQL. - Символ, 2007г.

7. Мартин Грабер. SQL - Лори, 2007г.

8. Д. Крёнке. Теория и практика построения баз данных. - Питер, 2005 г.

9. Крис Фиайли. SQL. Руководство по изучению языка ДМК Пресс, 2004 г.



ПЕРЕЧЕНЬ ПРИНЯТЫХ СОКРАЩЕНИЙ

1) БД - База данных

2) СУБД - Система управления баз данных

3) EA - Sparx Enterprise Architect

4) ПО - Программное обеспечение

5) РФ - Российская Федерация

6) ИС - Информационная система



ПРИЛОЖЕНИЕ А

Схема базы данных

Физическая схема базы данных информационной системы «Библиотека.ru» в EA



Схема базы данных стороннего информационной системы «Библиотека.ru» в phpMyAdmin



ПРИЛОЖЕНИЕ Б

SQL-код базы данных

DROP TABLE IF EXISTS `books` CASCADE;

CREATE TABLE `books`

(

`book_id` INT NOT NULL,

`au_id` INT NOT NULL,

`pub_id` INT NOT NULL,

`genre_id` INT NOT NULL,

`book_title` VARCHAR(50),

`pages` INT,

`pub_year` INT,

`isbn` VARCHAR(20) NOT NULL,

CONSTRAINT `PK_books` PRIMARY KEY (`book_id`)

);

ALTER TABLE `books`

ADD CONSTRAINT `FK_books_authors`

FOREIGN KEY (`au_id`) REFERENCES `authors` (`au_id`) ON DELETE Restrict ON UPDATE Restrict;

ALTER TABLE `books`

ADD CONSTRAINT `FK_books_genre`

FOREIGN KEY (`genre_id`) REFERENCES `genre` (`genre_id`) ON DELETE Restrict ON UPDATE Restrict;

ALTER TABLE `books`

ADD CONSTRAINT `FK_books_publishers`

FOREIGN KEY (`pub_id`) REFERENCES `publishers` (`pub_id`) ON DELETE Restrict ON UPDATE Restrict;