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

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

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

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

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

(ПензГТУ)

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

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

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

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

на тему: "Анализ, разработка и реализация базы данных информационной системы OpenPOS"

Выполнил: студент группы 13ИС2б Борисов И.Т.

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

Пенза, 2015 г.

Задание на курсовую работу по дисциплине "Управление данными"

Студента Борисов И.Т. Группа 13ИС2б

Тема работы: "Анализ, разработка и реализация базы данных информационной системы OpenPOS"

Исходные данные (технические требования на работу)

1. Назначение базы данных: хранение данных, необходимых для функционирования информационной системы OpenPOS. Система относится к классу POS-систем и предназначена для автоматизации рабочих процессов в заведениях общественного питания.

2. Состав и структура исходных данных: база данных openpos из 11 таблиц, предназначенных для хранения информации об учете заказов и учете времени, проведенного сотрудниками на рабочем месте.

3. Перечень сущностей: данные о сотрудниках (личные данные, ID документов, необходимых для регистрации сотрудника в организации, сведения о банковском счете); журнал учета рабочего времени сотрудников; данные о владельцах подарочных карт; меню заведения; журнал учетов заказов, выполненных клиентами.

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

5. Графическая часть:

схема БД (физическая) (1 лист формата А4);

схема БД в Web-интерфейсе администрирования MySQL phpMyAdmin (1 лист формата А4).

6. Экспериментальная часть:

разработка схемы БД c использованием Sparx Systems Enterprise Architeсt v12.0.1210.

составление и отладка запросов в СУБД MySQL.

7. Срок выполнения работы по разделам:

1. Постановка задачи до
2. Разработка схемы БД до
3. Написание базовых запросов до
4. Написание продвинутых запросов до
5. Оформление графической части до
6. Оформление пояснительной записки до

Студент _________________________________ / __________ /

Задание получил "___" ________________ 201_г.

Руководитель ____________________________ / __________ /

Задание выдал "___" ________________ 201_г.

Дата защиты работы ___________________________

Студент _______________________________ / __________ /

Руководитель ___________________________ / ___________ /

Содержание

• Задание на курсовую работу по дисциплине "Управление данными"
• Введение
• 1. Анализ предметной области
• 1.2 Общие сведения о проекте
• 1.3 Основные сведения о предметной области
• 1.4 Выделение справочных и оперативных данных
• 1.5 Предполагаемые запросы к базе данных
• 1.6 Перечень хранимой информации
• 1.7 Выводы по разделу
• 2. Моделирование предметной области
• 2.1 Общие сведения о моделировании предметной области
• 2.2 Выделение сущностей, атрибутов, ключей, связей
• 2.3 Проектирование диаграммы "сущность-связь" в Enterprise Architect
• 2.4 Подготовка диаграммы "сущность-связь" в Enterprise Architect к переносу на целевую СУБД
• 2.5 Создание базы данных в MySQL. Перенос схемы из Enterprise Architect
• 2.6 Начальное заполнение базы данных
• 2.7 Создание диаграммы средствами phpMyAdmin
• 2.8 Выводы по разделу
• 3. Создание и запуск базовых запросов SQL
• 3.1 Общие сведения о базовых запросах SQL
• 3.2 Выборка данных
• 3.3 Добавление данных
• 3.4 Обновление справочных данных
• 3.5 Удаление данных
• 3.6 Применение табличных функций SQL
• 3.7 Применение скалярных функций SQL
• 3.8 Выводы по разделу
• 4. Создание и запуск комплексных запросов SQL
• 4.1 Общие сведения о комплексных запросах SQL
• 4.2 Выборка данных из нескольких таблиц
• 4.3 Применение подзапросов
• 4.4 Создание таблиц в базе данных
• 4.5 Изменение формата таблиц в базе данных
• 4.6 Удаление таблиц из базы данных
• 4.7 Применение индексов в базе данных
• 4.8 Выводы по разделу
• 5. Создание и использование представлений
• 5.1 Общие сведения о представлениях
• 5.2 Создание представлений в базе данных
• 5.3 Операции с представлениями базы данных
• 5.4 Выводы по разделу
• Заключение
• Список литературы
• Перечень принятых сокращений
• Приложения

Введение

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

1) анализ предметной области проектируемой ИС, создание плана по реализации БД, хранящей информацию, необходимую для функционирования субъекта предметной области;

2) создание схемы базы данных (далее - БД) и её дальнейшая разработка для хранения информации в ИС;

3) реализация БД посредством создания, изменения, удаления хранимых данных, а также манипуляции данными посредством выборки из таблиц.

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

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

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

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

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

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

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

1. Анализ предметной области

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

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

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

Главной целью разрабатываемой базы данных является хранения данных для информационной системы OpenPOS. Данная ИС предназначена для автоматизации организации управления заведениями общественного питания. Целевой аудиторией openpos являются такие предприятия общественного питания, как рестораны, бары, кафе, столовые, пиццерии, уличные киоски. Одним словом, организации, имеющие в процессе своей деятельности необходимость учета поступающих заказов в режиме реального времени, а также в учете времени, проведенного сотрудниками на рабочем месте.

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

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

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

3) хранение меню заведения;

4) учет заказов, поступающих от клиентов и последующее создание счета, включающего общую сумму заказов.

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

Рисунок 1 - POS-машина

Принципы работы проектируемой ИС следует разделить на 2 части: модуль работы с сотрудниками и модуль работы с клиентами.

Модуль работы с сотрудниками включает в себя:

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

2) хранение необходимой информации о сотрудниках;

3) регистрацию сотрудника как по прибытию на рабочее место, так и по уходу с рабочего места. Каждый сотрудник имеет свой ID, по которому он унифицируется как субъект ИС. По разнице временных отметок генерируется общее количество рабочего времени сотрудника для последующего учета руководителями организации, имеющими права администратора в системе, а также сумма почасовой заработной платы.

Модуль работы с сотрудниками включает в себя:

1) хранение меню организации общественного питания с возможностью дальнейшего редактирования;

2) автоматическая генерация заказов пользователей (tickets) на основании данных, введенных участником обслуживающего персонала как пользователем ИС;

3) автоматическая генерация счета для оплаты услуг клиентом организации на основании поступивших заказов с последующим обновлением статуса (оплачено/не оплачено).

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

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

В состав отрасли общественного питания входят:

1) предприятия общественного питания - осуществляют производство кулинарной продукции, а также её реализацию и организацию потребления.

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

3) доготовочные предприятия (общественного питания) - осуществляют приготовление блюд из полуфабрикатов и кулинарных изделий.

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

OpenPOS является одним из экземпляров POS-систем. POS-система (POS - Point of Sale) - это программно-аппаратный комплекс, функционирующий на базе фискального регистратора; за POS-системой закреплен типичный набор кассовых функций: учет и отпуск товара, прием и выдача денег, аннулирование факта покупки и др. На рисунке 2 показан базовый интерфейс экземпляра POS-системы.

информационная система базовый запрос

Рисунок 2 - пользовательский интерфейс системы POS

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

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

ИС	R-Keeper	Liko	Jowi
Тип установки	локально	локально	локально
Возможность расширения	нет	да	да
Сенсорный ввод	+	+	+
Пользовательский интерфейс	6	7	8
Стоимость	От 77000 до 180000 рублей	23990 рублей	$150 в месяц

Исходя из проведенного анализа стоит отметить, что системы, являющиеся потенциальными конкурентами OpenPOS, имеют свои преимущества и недостатки. Основным же недочетом является большая сумма общих затрат на приобретение и внедрение системы. Предполагается, что система OpenPOS решит данный недостаток вместе с сохранением всех плюсов традиционных POS-систем (в том числе и сенсорный ввод, реализуемый с помощью утилиты ELO Touchscreen). Разрабатываемая ИС обеспечит объем высоких продаж за счет модульной архитектуры: заведения общественного питания, являющиеся потенциальными клиентами, могут приобрести не полную версию OpenPOS, а только те модули, необходимые для решения своих задач.

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

Также особенностью системы OpenPOS является адаптация POS-системы под особенности ресторанного бизнеса в России: например, добавление в систему возможности хранения номеров документов, необходимых для регистрации и содержании сотрудника в организации.

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

БД информационной системы OpenPOS, названная openpos, содержит набор справочных и оперативных данных. Всего БД содержит 11 таблиц.

Справочными данными является информация о сотрудниках системы: личные данные (адрес, дата рождения, контакты для связи), идентификационные номера личных документов (внутренний паспорт, ИНН, СНИЛС и т.д.), реквизиты банковского счета. Также справочными данными в разрабатываемой БД является журнал подарочных карт заведения: идентификатор подарочной карты, имя/фамилия владельца, скидка, действующая при предъявлении карты и меню заведения.

Динамическими данными является информация о заказах и итоговых счетах на оплату. Эти типы данных обновляются постоянно.

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

SQL - этом мощный и в то же время не комплексный язык для управления базами данных. Он поддерживается практически всеми современными базами данных. SQL подразделятся на два подмножества команд: DDL (Data Definition Language - язык определения данных) и DML (Data Manipulation Language - язык обработки данных). Команды DDL используются для создания новых баз данных, таблиц и столбцов, а команды DML - для чтения, записи, сортировки, фильтрования, удаления данных. В рамках курсовой работы будут рассмотрены и команды DDL и команды DML.

Предполагаемые запросы:

1) выбрать из таблицы employee_account всех сотрудников, у которых срок истечения действия банковского счета истекает в течение года;

2) выбрать из таблицы employee_adress сотрудников, проживающих в том же городе, где находится заведение;

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

4) выбрать из таблиц bills все открытые (неоплачиваемые счета);

5) добавить в таблицу menu_items новое блюдо;

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

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

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

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

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

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

2. Моделирование предметной области

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

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

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

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

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

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

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

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

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

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

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

Всего в рамках проектируемой БД были выделены две сущности: управленческая часть (сведения о сотрудниках заведения общественного питания) и клиентская часть (журнал подарочных карт, история заказов, меню заведения). В соответствии с теорией нормальных форм данные клиентской части разделены на 6 таблиц: employee_info (основная информация о сотрудниках: ФИО, должность, контакты для связи), employee_address (фактический адрес проживания сотрудников), employee_paperwork (ID документов сотрудников), employee_account (банковские реквизиты сотрудников для проведения безналичного расчета), employee_military (данные о призыву на воинскую службу в РФ: не годен, имеет приписное свидетельство, имеет военный билет) и employee_timekeeping (фиксированная дана начала и дата окончания рабочего дня сотрудников). В таблицах 2-7 представлена структура вышеперечисленных таблиц, атрибуты, тип данных атрибутов, а также выделены атрибуты, играющие роль первичного ключа (PK) и внешнего ключа (FK).

Таблица 2 - таблица employee_info

employee_id (int)	employee_fname (varchar)	employee_lname (varchar)	employee_birthday (date)	employee_gender (varchar)	employee_position (varchar)
PK
employee_join (date)	employee_family (varchar)	employee_phone (varchar)	employee_email (varchar)	employee_photo (varchar)

Таблица 3 - таблица employee_address

employee_id (int)	employee_region (varchar)	employee_city (varchar)	employee_street (varchar)	employee_home (varchar)	employee_apt (varchar)	employee_zip (int)
PK+FK (employee_info)

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

employee_id (int)	employee_passport (int)	employee_inn (int)	employee_snils (int)	employee_book (int)
PK+FK (employee_info)

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

employee_id (int)	employee_card (int)	employee_accnum (int)	employee_card_exp (date)
PK+FK (employee_info)

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

employee_id (int)	employee_mil_status (varchar)	employee_mil_id (int)
PK+FK (employee_info)

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

employee_id (int)	shift_id (int)	shift_date (date)	shift_clockin (date)	shift_clockout (date)	shift_total (date)
PK+FK (employee_info)

Данные клиентской сущности разделены на 5 таблиц: membership (сведения о подарочных картах пользователя), menu_items (список блюд в меню заведения и их статус: в наличии/отсутствует), tickets (сводная таблица заказов от клиентов), bills_components (таблица соответствия заказов и ID общего счета) и bills_info (ведение счетов: данные о сотруднике, обслужившем клиента, ID клиента с подарочной картой - по умолчанию 0, сумма счета, дата генерации счета и статус - оплачено/не оплачено). В таблицах 8-12 представлена структура вышеперечисленных таблиц, атрибуты, тип данных атрибутов, а также выделены атрибуты, играющие роль первичного ключа (PK) и внешнего ключа (FK).

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

member_id (int)	member_fname (varchar)	member_lname (varchar)	membership_discount (int)	membership_status (varchar)
PK

Таблица 9 - таблица menu_items

item_id (int)	item_name (varchar)	item_price (int)	item_status (varchar)	item_description (varchar)
PK

Таблица 10 - таблица tickets

ticket_id (int)	item_id (int)	item_quantity (int)	ticket_price (int)
PK	FK (menu_items)

Таблица 11 - таблица bills_components

bill_id (int)	ticket_id (int)
PK+FK (bills_info)	PK+FK (tickets)

Таблица 12 - таблица bills_info

bill_id (int)	bill_tickets (int)	member_id (int)	employee_id (int)	bill_time (timestamp)
PK
bill_tips (int)	bill_total (int)	bill_status (varchar)	bill_method (varchar)

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

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

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

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

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

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

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

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

Операционной системой, на которой расположен MySQL является CentOS 6 - дистрибутив семейства Linux. Основное преимущество CentOS - широкий спектр возможностей и способность быть компонентом реализации отказоустойчивых систем, что очень важно с точки зрения выбора программной платформы для разрабатываемой ИС.

База данных на основе SQL кода была создана с помощью командной оболочки bash. Стоит отметить команды выбора кодировки данных по умолчанию. Одной из проблем баз данных программного обеспечения является распознавание данных на русском языке. В MySQL по умолчанию стоит кодировка latin1, не распознающая кириллицу. Поэтому сразу после создания базы тип кодировки был изменен на UTF-8.

cd /opt

mysql - u root - p

CREATE DATABASE openpos

DEFAULT CHARACTER SET utf8

DEFAULT COLLATE utf8_general_ci;

USE openpos

Mysql - u root - p openpos < database. sql

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

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

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

Существуют несколько способов добавления записей в таблицы. В данной задаче наиболее целесообразным выглядит загрузка записей из текстового файла (команда LOAD DATA LOCAL INFILE). Для этого для каждой таблицы необходимо создать отдельный текстовый файл, записать в него данные в специальном формате и загрузить данные из файла в таблицу при помощи командной строки MySQL. Для добавления записи о модуле достаточно выполнения команды INSERT.

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

USE openpos;

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

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

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

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

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

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

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

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

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

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

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

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

На рисунке показан результат работы одной из команд. В общей комплексности в каждую таблицу было добавлено 10 записей.

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

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

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

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

Для запуска интерфейса phpMyAdmin необходимо зайти на адрес http://localhost/phpmyadmin с веб-бразуера и пройти процедуру аутентификации. В меню phpMyAdmin последовательно были выбраны нужные БД с последующим просмотром диаграмм "сущность-связь". На рисунке 5 показан интерфейс управления базами данных в phpMyAdmin.

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

В приложении А.2 представлена диаграмма спроектированной БД openpos.

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

Основной целью данного раздела являлось моделирование предметной области и её внедрение в рабочий процесс. Были описаны общие сведения о моделировании предметной области, этапы моделирования области. Разработана схема БД, описаны сущности, атрибуты, первичные и внешние ключи, а также связи между таблицами. Посредством инструмента графической визуализации EA была спроектирована диаграмма "сущность-связь", на основании которой автоматически сгенерирован SQL-код создания таблиц. С помощью данного кода схема БД была перенесена из EA в РСУБД MySQL, находящуюся в Linux-дистрибутиве CentOS 6. Таблицы созданной БД были заполнены данными из текстовых файлов. Отредактированная БД была повторно визуализирована в виде диаграммы с помощью phpMyAdmin.

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

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

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

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

4) умение проектировать структуры данных на основе модели предметной области.

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 Выборка данных

Для извлечения записей из таблиц в SQL определен оператор SELECT. С помощью этой команды осуществляется не только операция реляционной алгебры "выборка" (горизонтальное подмножество), но и предварительное соединение (join) двух и более таблиц, которое рассмотрено в 4 главе данной курсовой работы.

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

SELECT * FROM employee_info ORDER BY employee_birthday DESC;

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

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

SELECT bill_id, member_id, employee_id, bill_total, bill_time FROM bills_info WHERE bill_status='doesn`t paid';

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

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

Также можно выбрать статистику сотрудников, имеющих статус военнообязанных на территории РФ.

SELECT * FROM employee_military WHERE employee_mil_status IS NOT NULL;

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

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

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

INSERT INTO tickets VALUES (932, 1, 1, 50);

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

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

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

INSERT INTO membership VALUES (11, Shilyn, Kirill, 0.1, active);

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

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

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

UPDATE employee_paperwork SET employee_passport=5609911911 WHERE employee_id=5;

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

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

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

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

DELETE FROM employee_info WHERE employee_lname='Novikov';

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

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

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

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

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

SELECT employee_id, employee_card_exp, (CASE employee_card_exp WHEN employee_card_exp < '2017-01-01' THEN 'expiring' ELSE 'non-expiring' END) AS 'state' FROM employee_account ORDER BY 3 DESC;

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

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

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

SELECT CONCAT (employee_fname, ` `, employee_mname, ` ', employee_lname) AS `Full name', employee_position FROM employee_info;

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

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

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

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

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

SELECT bill_id, COUNT (ticket_id) AS `Tickets' FROM bills_components GROUP BY bill_id;

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

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

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

SELECT MAX (bill_price) AS Bill record' FROM bills_info;

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

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

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

Основной задачей данного раздела курсовой работы являлось анализ сущности базовых запросов языка SQL, освоение и применение базовых запросов языка SQL, направленных на работу с одной таблицей. В разделе показаны как DML-команды (выборка данных из таблицы, использование табличных и скалярных функций), так и DDL-команды (добавление записей в таблицу, удаление записей из таблицы, обновление отдельных значений в записях таблицы). В приложении Б.2 находится скрипт базовых запросов SQL, выполненный в БД openpos.

4. Создание и запуск комплексных запросов SQL

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

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

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

Одна из наиболее важных особенностей запросов SQL - это их способность определять связи между многочисленными таблицами и выводить информацию из них в терминах этих связей, всю внутри одной команды. Этот вид операции называется - объединением, которое является одним из видов операций в реляционных базах данных. Главное в реляционном подходе это связи, которые можно создавать между позициями данных в таблицах. Используя объединения, мы непосредственно связываем информацию с любым номером таблицы, и таким образом способны создавать связи между сравнимыми фрагментами данных. При объединении таблицы, представленные списком в предложении FROM запроса, отделяются запятыми. Предикат запроса может ссылаться к любому столбцу любой связанной таблицы и, следовательно, может использоваться для связи между ними. Обычно, предикат сравнивает значения в столбцах различных таблиц, чтобы определить, удовлетворяет ли WHERE установленному условию.

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

SELECT bi. bill_id, m. member_fname, m. member_lname, menu. item_name, bi. bill_status;

FROM bill_info bi

INNER JOIN membership m ON bi. member_id=m. member_id

INNER JOIN bills_components bc ON bc. bill_id=bi. bill_id

INNER JOIN tickets t ON t. ticket_id=bc. ticket_id

INNER JOIN menu_items menu ON menu. item_id=t. item_id

ORDER BY m. member_id;

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

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

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

Запрос находящийся внутри другого запроса называется подзапросом. Подзапрос возвращает значение, которое будет использоваться в основном запросе. Использования подзапросов, есть использования по очереди двух запросов, сперва получаем данные из подзапроса, а затем используем эти данные в качестве данных основного запроса.

В БД openpos подзапрос можно применить в таблице bills_info. Например, можно отобразить информацию о счетах, включающих более 1 заказа. На рисунке 15 показаны результаты работы запроса.

SELECT bill_id, bill_time, bill_total, bill_status, bill_method FROM bills_info WHERE bill_id IN (SELECT bill_id FROM bills_components, COUNT (ticket_id) AS `quantity' WHERE quantity > 1 GROUP BY bill_id);

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

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

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

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

В рамках данного подраздела в БД openpos создана дополнительная таблица menu_ingredients, позволяющая вести учет ингридиентов, необходимых для приготовления описанных в меню блюд. Атрибутами данной таблицы являются ID блюда (item_id), играющий роль внешнего ключа, связывающего таблицу с другой таблицей menu_items, ID ингредиента (ingredient_id) как первичный ключ, его имя и итоговая цена (ingredient_name, ingredient_price). На рисунке 16 показана структура созданной таблицы.

CREATE TABLE menu_ingredients

(

item_id INT NOT NULL,

ingredient_id INT NOT NULL,

ingredient_name VARCHAR (20),

ingredient_price INT NOT NULL,

PRIMARY KEY (ingredient_id),

FOREIGN KEY (item_id) REFERENCES menu_items (item_id)

);

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

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

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

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

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

ALTER TABLE menu_ingredients ADD COLUMN ingredient_quantity INT;

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

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

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

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

DROP TABLE menu_ingredients;

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

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

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

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

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