CREATE INDEX ind_employee ON employee_info (employee_lname);

CREATE INDEX ind_dish ON menu_items (item_name);

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

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

ALTER TABLE employee_info DROP INDEX ind_employee;

ALTER TABLE menu_items DROP INDEX ind_dish;

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

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

Основной задачей данного раздела являлось ознакомление с комплексными SQL-запросами к предметной области. В данном разделе рассмотрены несколько комплексных запросов: выборка из таблиц-объединений, подзапросы, создание, редактирование и удаление дополнительных таблиц. Также описаны понятие индексации, принципы работы с индексами. В приложении Б.3 находится SQL-скрипт, в котором собраны комплексные запросы к разработанной БД openpos.

5. Создание и использование представлений

5.1 Общие сведения о представлениях

Представления (view) - это одно из мощных средств языка SQL, предназначенное для реализации механизм подсхем пользователей базы данных. Представления позволяют скрыть от пользователей схему базы данных. Они представляют собой хранимые в базе данных запросы, выраженные операторами SELECT. На базе одних представлений могут быть созданы новые представления, которые наследуют все свойства базовых представлений. Формировать представления могут пользователи с привилегиями SELECT для используемых в представлениях таблиц (базовых таблиц).

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

5.2 Создание представлений в базе данных

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

CREATE VIEW employee_place AS

SELECT ei. employee_id, CONCAT (ei. employee_fname, ` ', ei. employee_mname, ` ', ei. employee_lname) AS `Employee', CONCAT (ea. city, ` `, ea. street, ` `, ea. home, ` `, ea. apt) AS `Address'

FROM employee_info ei INNER JOIN employee_address ea ON ea. employee_id=ei. employee_id ORDER BY ei. employee_id;

Листинг 21 - создание представления данных из нескольких таблиц

Рисунок 18 - структура представления, созданного в БД openpos.

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

CREATE VIEW employee_activity AS

SELECT b. bill_id, e. employee_fname, e. employee_lname, b. bill_time, b. bill_tips, b. bill_total, b. bill_status, b. bill_method

FROM bills_info b

INNER JOIN employee_info e ON e. employee_id=b. employee_id

ORDER BY e. employee_id;

Листинг 22 - создание представления с использованием скалярной функции

5.3 Операции с представлениями базы данных

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

SELECT * FROM employee_place WHERE employee_id<5;

Листинг 23 - выборка данных из представления

Рисунок 19 - выборка представления, созданного в БД openpos.

Представления удаляются с помощью команды DROP VIEW. Ниже наглядно показан процесс удаления представления из базы данных.

DROP VIEW employee_place;

DROP VIEW employee_activity;

Листинг 20 - удаление представлений

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

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

Заключение

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

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

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

3) создана схема БД с её последующей реализацией и внедрением в СУБД;

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

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

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

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

3) спроектирована диаграмма сущность-связь БД информационной POS-системы OpenPOS посредством визуального моделирования схемы в EA;

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

5) визуализирована диаграмма отредактированной БД с помощью утилиты моделирования БД phpMyAdmin;

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

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

8) созданы представления данных с их последующим применением на практике.

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

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

2) разработка информационной системы OpenPOS (продумывание алгоритма работы ИС, написание исходного кода, отладка работы программы, тестирование ИС на целевом аппаратном комплексе);

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

4) презентация разработанного проекта и дальнейшее внедрение в структуру предприятий общественного питания;

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

Список литературы

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

1. Андрей Кондрашин. Ресторанный бизнес в малых городах. Секреты успешного открытия и эффективного управления. - Ресторанные ведомости, 2015

2. Виолетта Гвоздовская Управление рестораном, который любит гостей. - Ресторанные ведомости, 2013

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

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

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

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

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

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

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

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

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

Интернет ресурсы

11. Database Engineering with Enterprise Architect 12 [Электронный ресурс] URL https: // www.youtube.com/watch? v=LLtp49TU1H8 (дата обращения: 09.11.2014г.)

12. Loading Data into a Table [Электронный ресурс] URL: https: // dev. mysql.com/doc/refman/5.5/en/loading-tables.html (дата обращения: 22.11.2015г.)

13. LOAD DATA INFILE Syntax [Электронный ресурс] URL: https: // dev. mysql.com/doc/refman/5.5/en/load-data.html (дата обращения: 22.11.2015г.)

14. Configuring the Character Set and Collation for Applications [Электронный ресурс] URL: https: // dev. mysql.com/doc/refman/5.7/en/charset-applications.html (дата обращения: 22.11.2015г.)

15. How Do I Enable Remote Access To MySQL Database Server?. [Электронный ресурс] URL: http://www.cyberciti. biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html (дата обращения: 23.11.2015г.)

Перечень принятых сокращений

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

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

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

4) EA - Sparx Enterprise Architect

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

Приложения

Приложение А. 2 - схема подструктуры базы данных openpos в phpMyAdmin

Приложение Б

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

DROP TABLE IF EXISTS `bills_info` CASCADE;

CREATE TABLE `bills_info`

(

`bill_id` INT NOT NULL,

`bill_tickets` INT,

`member_id` INT 0,`employee_id` INT,

`bill_time` TIMESTAMP (0),

`bill_tips` INT,

`bill_total` INT,

`bill_status` VARCHAR (20),

`bill_method` VARCHAR (20),

CONSTRAINT `PK_bills_info` PRIMARY KEY (`bill_id`)

);

ALTER TABLE `bills_info`

ADD INDEX `IXFK_bills_info_employee_info` (`employee_id` ASC)

;

ALTER TABLE `bills_info`

ADD INDEX `IXFK_bills_info_membership` (`member_id` ASC)

;

SET FOREIGN_KEY_CHECKS=1

Приложение Б.1 - создание таблицы

SELECT * FROM employee_info ORDER BY employee_birthday DESC;

SELECT bill_id, member_id, employee_id, bill_total, bill_time FROM bills_info WHERE return_date IS NULL;

SELECT * FROM employee_military WHERE employee_mil_status IS NOT NULL;

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

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

UPDATE employee_paperwork SET employee_passport=5609911911 WHERE employee_id=5;

DELETE FROM employee_info WHERE employee_lname='Novikov';

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;

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

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

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

Приложение Б.2 - скрипт создания базовых запросов

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

FROM bills_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;

SELECT bill_id, bill_time, bill_total, bill_status, bill_method FROM bills_info WHERE bill_id IN

(SELECT bill_id, COUNT (ticket_id) AS `quantity' FROM bills_components WHERE quantity > 1 GROUP BY bill_id);

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)

);

ALTER TABLE menu_ingredients ADD COLUMN ingredient_quantity INT;

DROP TABLE menu_ingredients;

CREATE INDEX ind_employee ON employee_info (employee_lname);

CREATE INDEX ind_dish ON menu_items (item_name);

ALTER TABLE employee_info DROP INDEX ind_employee;

ALTER TABLE menu_items DROP INDEX ind_dish;

Приложение Б.3 - скрипт создания комплексных запросов

CREATE VIEW employee_place AS

SELECT ei. employee_id, CONCAT (ei. employee_fname, ` ', ei. employee_mname, ` ', ei. employee_lname) AS `Employee',

CONCAT (ea. employee_city, ` `, ea. employee_street, ` `, ea. employee_home, ` `, ea. employee_apt) AS `Address'

FROM employee_info ei INNER JOIN employee_address ea ON ea. employee_id=ei. employee_id ORDER BY ei. employee_id;

CREATE VIEW employee_activity AS

SELECT b. bill_id, e. employee_fname, e. employee_lname, b. bill_time, b. bill_tips, b. bill_total, b. bill_status, b. bill_method

FROM bills_info b

INNER JOIN employee_info e ON e. employee_id=b. employee_id

ORDER BY e. employee_id;

SELECT * FROM employee_place WHERE employee_id<5;

DROP VIEW employee_place;

DROP VIEW employee_activity;

Размещено на Allbest.ru