Главная страница
Контакты

    Главная страница


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

Скачать 126.6 Kb.



Скачать 126.6 Kb.
Дата03.10.2017
Размер126.6 Kb.

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


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

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

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

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

(ПензГТУ)

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

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

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

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

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

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

Ба́за да́нных - представленная в объективной форме совокупность самостоятельных материалов (статей, расчётов, нормативных актов, судебных решений и иных подобных материалов), систематизированных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью электронной вычислительной машины (ЭВМ).
Информационная система (ИС) - система, предназначенная для хранения, поиска и обработки информации, и соответствующие организационные ресурсы (человеческие, технические, финансовые и т. д.), которые обеспечивают и распространяют информацию (ISO/IEC 2382:2015).
Профессиона́льное образова́ние - процесс и результат профессионального становления и развития личности, сопровождающийся овладением установленными знаниями, умениями, навыками и педагогическими компетенциями по конкретным специальностям и профессиям. (А. Н. Лейбович)
Уче́бное заведе́ние - прежнее название в дореволюционной России, а впоследствии и в СССР и Российской Федерации (до 1992 года) образовательного учреждения. В соответствии с действующим российским законодательством - это учреждение, осуществляющее образовательный процесс, то есть реализующее одну или несколько образовательных программ и (или) обеспечивающее содержание и воспитание обучающихся, воспитанников. (Закон России «Об образовании»).

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

Пенза, 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.
MySQL MySQL (МФА: [maɪ ˌɛskjuːˈɛl]) - свободная реляционная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems, которая ранее приобрела шведскую компанию MySQL AB.


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.
    Таблица поиска (англ. lookup table) - это структура данных, обычно массив или ассоциативный массив, используемая с целью заменить вычисления на операцию простого поиска. Увеличение скорости может быть значительным, так как получить данные из памяти зачастую быстрее, чем выполнить трудоёмкие вычисления.
    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 Выводы по разделу
  • Заключение
  • Список литературы
  • Перечень принятых сокращений
  • Приложения

Введение

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

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

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

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

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

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

Приготовление пищи, кулинари́я или кулина́рия (лат. culīnāria (ars) - кухонное (ремесло); от лат. culīna - кухня) - человеческая деятельность по приготовлению пищи к еде. Включает в себя комплекс технологий, оборудования и рецептов.
Народное хозяйство - исторически сложившийся комплекс (совокупность) отраслей производства данной страны, взаимосвязанных между собой разделением труда. Подразделяется по видам деятельности субъектов отношений.
Общественное питание Обще́ственное пита́ние, сокр.: общепи́т - отрасль народного хозяйства, совокупность предприятий, занимающихся производством, реализацией и организацией потребления кулинарной продукции.
Предприятия общественного питания - организации, которые осуществляют производство кулинарной продукции, а также их реализацию и организацию потребления.
Предприятие общественного питания Предприятие общественного питания - общее название организации, которая оказывает услуги общественного питания посредством: производства кулинарной продукции, её реализации и организации питания различных групп населения.

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

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

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

Сеть хранения данных (СХД, англ. Storage Area Network, SAN) - представляет собой архитектурное решение для подключения внешних устройств хранения данных, таких как дисковые массивы, ленточные библиотеки, оптические приводы к серверам таким образом, чтобы операционная система распознала подключённые ресурсы как локальные.
Систе́ма управле́ния ба́зами да́нных (СУБД) - совокупность программных и лингвистических средств общего или специального назначения, обеспечивающих управление созданием и использованием баз данных.
73 и начальное заполнение БД, создание диаграммы с помощью Web-интерфейса администрирования баз данных phpMyAdmin;

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

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

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


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

Предметная область - множество всех предметов, свойства которых и отношения, между которыми рассматриваются в научной теории.
Тео́рия (греч. θεωρία - рассмотрение, исследование) - учение, система идей или принципов. Является совокупностью обобщённых положений, образующих науку или её раздел[уточнить]. Теория выступает как информационная модель синтетического знания, в границах которой отдельные понятия, гипотезы и законы теряют прежнюю автономность и становятся элементами целостной системы.
В логике - предполагаемая область возможных значений предметных переменных логического языка. Понятие предметной области является одним из самых важных компонентов информационной системы, так как отсутствие конструктивного определения понятия предметной области существенно сдерживает развитие математической теории информационных систем и информационных технологий.
Информацио́нные техноло́гии (ИТ, также - информационно-коммуникационные технологии) - процессы, методы поиска, сбора, хранения, обработки, предоставления, распространения информации и способы осуществления таких процессов и методов (ФЗ № 149-ФЗ); приёмы, способы и методы применения средств вычислительной техники при выполнении функций сбора, хранения, обработки, передачи и использования данных (ГОСТ 34.003-90); ресурсы, необходимые для сбора, обработки, хранения и распространения информации (ISO/IEC 38500:2008).


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


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

Главной целью разрабатываемой базы данных является хранения данных для информационной системы OpenPOS. Данная ИС предназначена для автоматизации организации управления заведениями общественного питания. Целевой аудиторией openpos являются такие предприятия общественного питания, как рестораны, бары, кафе, столовые, пиццерии, уличные киоски. Одним словом, организации, имеющие в процессе своей деятельности необходимость учета поступающих заказов в режиме реального времени, а также в учете времени, проведенного сотрудниками на рабочем месте.
Храни́лище да́нных (англ. Data Warehouse) - предметно-ориентированная информационная база данных, специально разработанная и предназначенная для подготовки отчётов и бизнес-анализа с целью поддержки принятия решений в организации.
Целевая группа, целевая аудитория - термин, используемый в маркетинге или рекламе для обозначения группы людей, объединённых общими признаками, или объединённой ради какой-либо цели или задачи. Под общими признаками могут пониматься любые характеристики, требуемые организаторами (например, работающие замужние женщины от 25 до 35 лет, носящие очки).
Система реального времени (СРВ) - это система, которая должна реагировать на события во внешней по отношению к системе среде или воздействовать на среду в рамках требуемых временных ограничений. Оксфордский словарь английского языка говорит об СРВ как о системе, для которой важно время получения результата.


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

1) ведение кадрового учета, хранение информации о сотрудниках предприятия, такой, как личные данные, идентификаторы документов, необходимых для регистрации сотрудника, банковские реквизиты для безналичного расчета с сотрудником в рамках трудового договора;
Информа́ция (от лат. informātiō - «разъяснение, представление, понятие о чём-либо», от лат. informare - «придавать вид, форму, обучать; мыслить, воображать») - сведения, независимо от формы их представления.
Персона́льные да́нные (ПДн) - любая информация, относящаяся к прямо или косвенно определенному или определяемому физическому лицу (субъекту персональных данных).
Трудово́й догово́р - в трудовом праве письменный документ - соглашение между работником и работодателем, которое устанавливает их взаимные права и обязанности. В соответствии с трудовым договором работник обязуется лично выполнять работу по определённой должности, соответствующей его квалификации, а работодатель обязуется предоставлять работнику работу, обеспечивать условия труда и своевременно выплачивать заработную плату.


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

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

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

В качестве аппаратного комплекса может подойти несколько вычислительных устройств (компьютеры, ноутбуки, планшеты), однако целевым устройством будут являться компьютеры-моноблоки, используемые ИС в качестве терминалов управления.
Заработная плата (оплата труда работника) - вознаграждение за труд в зависимости от квалификации работника, сложности, количества, качества и условий выполняемой работы, а также компенсационные и стимулирующие выплаты.
Компью́тер (англ. computer, МФА: [kəmˈpjuː.tə(ɹ)] - «вычислитель») - устройство или система, способная выполнять заданную, чётко определённую, изменяемую последовательность операций. Это чаще всего операции численных расчётов и манипулирования данными, однако сюда относятся и операции ввода-вывода.
На рисунке 1 показан пример функционирующих POS-машин, функционирующих в заведениях общественного питания.

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

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

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

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

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

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


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

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

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

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

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

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


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

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

2) заготовочные предприятия, или цехи общественного питания - осуществляют производство полуфабрикатов, кулинарной продукции, снабжая ими доготовочные предприятия, магазины кулинарии и предприятия розничной торговли.
Ро́зничная торго́вля или рете́йл (от англ. retail - «розничный, в розницу») - продажа товаров (услуг) небольшим количеством, поштучно. Осуществляется через предприятия розничной торговли. Объектом розничной торговли является покупатель, приобретающий товар, предназначенный для личного, семейного, домашнего или иного пользования, не связанного с предпринимательской деятельностью.


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

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

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

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

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

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

Настоя́щее - часть линии времени, состоящая из событий, которые происходят в данный момент, то есть определенная область пространства-времени. При определённых допущениях под настоящим временем понимаются текущие дни, месяцы и даже годы.
Интерфе́йс по́льзователя, он же по́льзовательский интерфейс (UI - англ. user interface) - интерфейс, обеспечивающий передачу информации между пользователем-человеком и программно-аппаратными компонентами компьютерной системы (ISO/IEC/IEEE 24765-2010).
Програ́ммное обеспе́чение (допустимо также произношение обеспече́ние) (ПО) - все или часть программ, процедур, правил и соответствующей документации системы обработки информации (ISO/IEC 2382-1:1993).

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

ИС

R-Keeper

Liko

Jowi

Тип установки

локально

локально

локально

Возможность расширения

нет

да

да

Сенсорный ввод

Пользовательский интерфейс

6

7

8

Стоимость

От 77000 до 180000 рублей

23990 рублей

$150 в месяц

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

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

Быстрое питание, блюдо быстрого питания, пищевой продукт быстрого приготовления, фастфуд (англ. fast «быстрый» и food «пища») - питание с уменьшенным временем употребления и приготовления пищи, с упрощёнными или упразднёнными столовыми приборами или вне стола.
Данный клиент не будет заинтересован в учете сотрудников организации и системе подарочных карт, для него основной проблемой будет являться учет заказов и последующей выручки. Он может приобрести систему OpenPOS, содержащую в себе лишь несколько клиентских модулей. Это существенно уменьшит затраты на автоматизацию деятельности предпринимателя. Таким образом, основным преимуществом проекта перед конкурентами планируется сделать гибкость и способность работать в режиме загрузки/выгрузки отдельных программных модулей.
Мо́дульное программи́рование - это организация программы как совокупности небольших независимых блоков, называемых модулями, структура и поведение которых подчиняются определённым правилам. Использование модульного программирования позволяет упростить тестирование программы и обнаружение ошибок.

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


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

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

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

Динамическими данными является информация о заказах и итоговых счетах на оплату. Эти типы данных обновляются постоянно.
Тип данных (тип) - множество значений и операций на этих значениях (IEEE Std 1320.2-1998).


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 посредством добавления таблиц, атрибутов, формирования первичных и внешних ключей и визуализации конечной схемы БД.
Вне́шний ключ (англ. foreign key) - понятие теории реляционных баз данных, относящееся к ограничениям целостности базы данных.
Стадия реализации состояла из генерации SQL-кода создания структуры данных в EA и дальнейшего переноса схемы в РСУБД MySQL посредством запуска сгенерированного SQL-кода. Стадия обеспечения эффективности реализации модели также состояла из загрузки данных в таблицы проектируемой БД и отображения диаграммы отредактированной БД в веб-инструменте администрирования СУБД phpMyAdmin.



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

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

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

Всего в рамках проектируемой БД были выделены две сущности: управленческая часть (сведения о сотрудниках заведения общественного питания) и клиентская часть (журнал подарочных карт, история заказов, меню заведения). В соответствии с теорией нормальных форм данные клиентской части разделены на 6 таблиц: employee_info (основная информация о сотрудниках: ФИО, должность, контакты для связи), employee_address (фактический адрес проживания сотрудников), employee_paperwork (ID документов сотрудников), employee_account (банковские реквизиты сотрудников для проведения безналичного расчета), employee_military (данные о призыву на воинскую службу в РФ: не годен, имеет приписное свидетельство, имеет военный билет) и employee_timekeeping (фиксированная дана начала и дата окончания рабочего дня сотрудников).
Удостоверение гражданина, подлежащего призыву на военную службу - документ, выдаваемый гражданам Российской Федерации (России) при прохождении комиссии по постановке граждан на воинский учёт (для мужчин - с 1 января по 31 марта года, в котором им исполняется 17 лет; для женщин - после получения военно-учётной специальности).
Военная служба - вид государственной службы, представляющий собой профессиональную служебную деятельность граждан государства (лиц без гражданства: Россия, США, Франция и другие) на воинских должностях в вооруженных силах, других войсках, воинских (специальных) формированиях и органах, осуществляющих функции по обеспечению обороны и безопасности государства.
В таблицах 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 - широкий спектр возможностей и способность быть компонентом реализации отказоустойчивых систем, что очень важно с точки зрения выбора программной платформы для разрабатываемой ИС.
Точка зрения (англ. point of view, POV) - жизненная позиция, с которой субъект оценивает происходящие вокруг него события. Термин произошёл от «точки зрения» - места, где находится наблюдатель и от которого зависит видимая им перспектива.


База данных на основе SQL кода была создана с помощью командной оболочки bash.
Оболо́чка операцио́нной систе́мы (от англ. shell «оболочка») - интерпретатор команд операционной системы, обеспечивающий интерфейс для взаимодействия пользователя с функциями системы.
Стоит отметить команды выбора кодировки данных по умолчанию. Одной из проблем баз данных программного обеспечения является распознавание данных на русском языке. В 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, так как происходит постоянное обслуживания клиентов путем генерации итогового счета на основе ценников в меню ресторана.
Обслу́живание клие́нтов (англ. customer service) - это обеспечение обслуживания клиентов до, во время и после покупки товара или услуги.
Предполагается автоматизация расчета клиентов. На рисунке 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, где хранятся идентификаторы документов, требуемых трудовым законодательством РФ.
Трудово́е пра́во - самостоятельная отрасль права, регулирующая отношения в сфере труда. Является относительно молодой отраслью права (момент возникновения относится к рубежу 19-20 веков), а одним из основателей самостоятельной отрасли трудового права стал учёный Л.С.
Например, один из сотрудников сменил паспорт и теперь нужно изменить номер паспорта в базе. Результат выполнения команды показан на рисунке 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 охарактеризовать состояние банковских карт сотрудников по сроку истечения и выделить из них те, которые необходимо заменить в скором времени.
Ба́нковская ка́рта (англ. Bank Card, BCard, BC) - пластиковая карта, обычно привязанная к одному или нескольким расчётным счетам в банке. Используется для оплаты товаров и услуг, в том числе через Интернет, а также снятия наличных.
На рисунке 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.
Числова́я фу́нкция (в математике) - функция, которая действует из одного числового пространства (множества) в другое числовое пространство (множество). Числовые множества - это множества натуральных ( N } ), рациональных ( Q } ), вещественных ( R } ) и комплексных чисел ( C } ) вместе с определёнными над соответствующими множествами алгебраическими операциями и с заданным на каждом множестве отношении линейным порядком. Числовые пространства - это числовые множества вместе с функцией расстояния, заданной на соответствующем множестве.


Также в БД 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, где предполагается поиск определенного блюда по его названию.