Построим на основе структуры, представленной на рисунке 2.4, диаграмму «сущность-связь» в нотации П. Чена для того, чтобы выделить атрибуты, а также связи между сущностями. Результат представлен на рисунке 2.5.
Рисунок 2.5 – Диаграмма «сущность-связь» в нотации П. Чена
Итак, диаграмма «сущность-связь» позволила представить результаты анализа предметной области в достаточно простом, наглядном, но в то же время формализованном виде.
Построение модели, основанной на ключах (Key Based model, KB)
Для построения модели основанной на ключах, сущности диаграммы «сущность-связь» были идентифицированы посредствам выбора ключевых атрибутов, которые однозначно определяют каждую из них. В результате была получена структура, изображенная на рисунке 2.6.
Рисунок 2.6 – Модель, основанная на ключах
Таким образом, была построена модель основанная на ключах, которая показывает связи между таблицами.
2.4 Построение полной̆ атрибутивной̆ модели в нотации IDEF1X
Полная атрибутивная модель – более подробное представление данных, чем модель, основанная на ключах. Она включает описание всех сущностей, внешних и первичных ключей и предназначена для представления структуры данных и ключей, которые соответствуют предметной области.
Построим полную атрибутивную модель базы данных фитнес клуба (рисунок 2.7).
Рисунок 2.7 – Полная атрибутивная модель в нотации IDEF1X
Процесс преобразования базы данных к виду, отвечающему нормальным формам, называется нормализацией. Нормализация позволяет обезопасить базу данных от логических и структурных проблем, называемых аномалиями данных. Таблица, прошедшая нормализацию, менее подвержена проблемам, связанным с нарушением целостности, так как ее структура предполагает определение связей между данными, что исключает необходимость в существовании записей с повторяющейся информацией.
Первая нормальная форма
Отношение находится в первой нормальной форме если в ней нет повторяющихся строк, все значения скалярные и атрибуты простые. Изображённые на рисунке 2.7 отношения удовлетворяют этим условиям, соответственно нормализации ко второй нормальной форме не требуется.
Вторая нормальная форма
Отношение находится во второй нормальной форме, при условии, если оно находится в первой нормальной форме, и каждый не ключевой атрибут характеризуется полной функциональной зависимостью от первичного ключа. Изображённые на рисунке 2.7 отношения удовлетворяют этим условиям, соответственно нормализации ко второй нормальной форме не требуется.
Третья нормальная форма
Отношение находится в третьей нормальной форме, при условии, если оно находится во второй нормальной форме и нет зависимостей одних ключевых атрибутов от других. Все атрибуты зависят только от первичного ключа.
Отношения в базе данных (рисунок 2.7) удовлетворяют этим условиям, соответственно нормализации не требуется.
Также отношение находится в нормальной форме Бойса-Кодда, при условии, если ключевые атрибуты не зависят от неключевых.
Отношения в базе данных (рисунок 2.7) удовлетворяют этим условиям, соответственно нормализации не требуется.
Выводы раздела 2
Таким образом, была разработана логическая модель данных, включающая в себя диаграмму «сущность-связь» в нотации Чена, модель, основанную на ключах, полную атрибутивную модель.
Также была подробно рассмотрена нормализация отношений от 1НФ к нормальной форме Бойса-Кодда включительно. Построение логической модели аргументировано приведением к нормальной форме Бойса-Кодда для улучшения структура базы данных перед реализацией.
3 Разработка физической модели базы данных
3.1. Выбор аппаратной и программной платформы для реализации БД
В качестве СУБД была выбрана phpMyAdmin MySql так как подключение к нему не составит труда, с ним удобно работать из-за простого интерфейса, также оно позволяет администрировать сервера через браузер, создавать, копировать, удалять переименовывать и изменять таблицы. Осуществлять сопровождение таблиц, выполнять SQL – запросы, управлять ключами, загружать текстовые файлы в таблицы, экспортировать данные в различных форматах, управлять пользователями и привилегиями, создавать графическую схему базы данных, проверять целостность ссылочных данных таблицах. Помимо этого раннее была получена практика работы с ним.
3.2. Реализация базы данных
Полный листинг SQL скрипта представлен в Приложении А.
Физическая модель была разработана в веб-приложении phpMyAdmin.
Рисунок 3.1 – Представление разработанной базы данных
Рисунок 3.2 – Добавление данных в таблицу «Залы»
Рисунок 3.3 – Добавление данных в таблицу «Услуги»
Рисунок 3.4 – Добавление данных в таблицу «Сотрудники»
Рисунок 3.5 – Добавление данных в таблицу «Клиенты»
Рисунок 3.6 – Добавление данных в таблицу «Клубные карты»
Рисунок 3.7 – Добавление данных в таблицу «Расписание»
Таким образом, было выяснена причина выбора программной и аппаратной платформы для реализации БД, продемонстрирована созданная физическая модель, а также таблицы были заполнены данными, для дальнейшей обработки.
3.3. Проверка целостности отношений
Проверим целостность БД на основе отношения «Расписание» - «Залы», удалив из отношения «Залы» кортеж.
Рисунок 3.8 – Таблица «Расписание» до удаления поля
На рисунке 3.8 видно, что все клиенты, которые должны были заниматься в зале, удалённом (с ID = 5), были удалены с таблицы «Расписание». Так как она имела внешний ключ на таблицу «Залы».
Рисунок 3.9 – Таблица «Расписание» после удаления поля
Проверим целостность на основе отношения «Сотрудники» - «Клиенты», добавив поле в таблицы «Клиенты» с несуществующим номером сотрудника в таблице «Сотрудники».
Рисунок 3.10 – Добавление поля в таблицу «Клиенты»
Как видно из рисунка 3.10 произошла ошибка при добавлении, нарушение ключа который ссылался на таблицу «Сотрудники» от таблицы «Клиенты».
Проверим целостность на основе отношения «Клубные карты» - «Клиенты», добавив поле в таблицы «Клубные карты» с несуществующим номером клиента.
Рисунок 3.11 – Добавление поля в таблицу «Клубные карты»
Как видно из рисунка 3.11 при добавлении, произошло нарушение целостности, а именно ключа который ссылается на таблицу «Клиенты» от таблицы «Клубные карты».
Проверим целостность на основе отношений «Клубные карты» – «Услуги».
Рисунок 3.12 – Добавление поля в таблицу «Клубные карты» для проверки целостности
Как видно из рисунка 3.12 при добавлении, произошло нарушение целостности, а именно ключа, который ссылается на таблицу «Услуги» от таблицы «Клубные карты».
Проверим целостность на основе отношений «Сотрудники» - «Услуги».
Рисунок 3.13 – Изменение поля в таблице «Сотрудники» для проверки целостности
Как видно из рисунка 3.13 при изменении, произошло нарушение целостности, а именно ключа который ссылается на таблицу «Сотрудники» от таблицы «Услуги».
Проверим целостность на основе отношений «Расписание» - «Сотрудники».
Рисунок 3.14 – Изменение поля в таблице «Расписание» для проверки целостности
Как видно из рисунка 3.14 при изменении, произошло нарушение целостности, а именно ключа который ссылается на таблицу «Сотрудники» от таблицы «Расписание».
Проверим целостность на основе отношений «Расписание» - «Клиенты»
Рисунок 3.15 – Изменение поля в таблице «Расписание» для проверки целостности
Как видно из рисунка 3.15 при изменении, произошло нарушение целостности, а именно ключа который ссылается на таблицу «Клиенты» от таблицы «Расписание». Важной составляющей БД для всех ее пользователей являются запросы на выборку данных.
Для тестирования были разработаны запросы, представленные ниже.
Запрос 1. Вывести клиента, который проживает по адресу ул Гоголя 16 кв 16:
Рисунок 3.16 – Результат запроса 1
Запрос 2. Вывести среднюю длительность занятия, проводимых 25 ноября:
Рисунок 3.17 – Результат запроса 2
Запрос 3. Вывести фамилию имя отчество, сотрудников у которых оклад больше 100000
Рисунок 3.18 – Результат запроса 3
Запрос 4. Вывести наименование услуги с минимальной ценой
Рисунок 3.19 – Результат запроса 4
Запрос 5. Вывести информацию про самую дорогу клубную карту
Рисунок 3.20 – Результат запроса 5
Запрос 6. Вывести фамилию имя и отчество сотрудника, который обслуживает клиента проживающего по адресу «ул Гоголя 16 кв 16»:
Рисунок 3.21 – Результат запроса 6
Запрос 7. Вывести фамилию и имя клиента, а также обслуживающего сотрудника, занятия у которого будет проводиться «25.11.2019 10:00:00»:
Рисунок 3.22 – Результат запроса 7
Запрос 8. Вывести фамилии и имена тех клиентов, у которых цена клубной карты выше средней цены клубных карт.
Рисунок 3.23 – Результат запроса 8
Запрос 9. Вывести информацию о клиенте, имеющий самую дорогую клубную карту.
Рисунок 3.24 – Результат запроса 9
Запрос 10. Вывести информацию о клиенте, имеющем больше одной клубной карты.
Рисунок 3.25 – Результат запроса 10
Таким образом, для проверки корректности работы БД было реализовано 15 запросов, в которых были использованы различные функциональные возможности СУБД MySql. Также для проверки целостности БД были реализованы запросы на добавление, удаление и изменения ссылочных данных.
3.4. Разграничение прав доступа
В фитнес клубе будет 2 взаимодействующих лица с базой данных.
1) Администратор – имеет полный доступ к базе данных.
2) Менеджер – может добавлять, удалять, изменять значения полей в таблицах.
3) Клиент – может производить выборку данных из таблицы.
Рисунок 3.26 – Добавление ролей
Таким образом, для разграничения прав доступа, были добавлены 3 роли: менеджер, администратор и клиент. Которые имеют различные ограничения по работе с БД.
3.4. Расчёт информационных параметров
При построении физической БД рекомендуется следующая связь с категориями логической модели:
1) объект –отношение- файл;
2) экземпляр объекта - логическая запись;
3) атрибут - поле;
4) структурная связь – указатель (для иерархической или сетевой модели).
Длина логической записи j-ого файла определяется как сумма длин полей, её составляющих и указателей, если они организуются разработчиком. Так, для наиболее общего случая (иерархическая или сетевая модель) длину логической записи можно определить по формуле (1):
[байт], (1)
где Mj - число групп полей в записях, lij длина группы [байт].
Если отдельным атрибутам объекта соответствуют переменная длина полей, то определяется максимальная maxLj и средняя длины записи m{ Lj }.
Для реляционной модели характерно фиксированное число, как для длины каждого поля записи, так и для количества полей каждой записи, поэтому формулу (2) можно представить в следующем виде:
[байт], (2)
где M - число полей в записи j-ого файла, lj - длина j-ого поля.
Объем памяти, необходимый для размещения информационного фонда без учёта системных данных и указателей составит, и его можно рассчитать по формуле (3):
[байт], (3)
где N - количество отношений реляционной базы данных, Kj- количество записей j-го файла.
Приращение информационного фонда можно рассчитать по формуле (4):
[байт-1], (4)
где 
- число добавленных типов записей, 
- интенсивность добавления записей в файл j -го типа. Указанная интенсивность оценивается на этапе инфологического проектирования, разрабатываемой автоматизированной системы хранения данных.
Зная первоначальный объём Vобщ памяти, выделенной под развёртывание БД, и объем программного обеспечения VПО, представляется возможным оценить время заполнения информационного фонда. Формулу (5) можно представить в следующем виде:
[время] (5)
Указанная величина будет определяться интенсивностью добавлений и первоначальными объемами памяти, значение последних определятся на основании анализа предметной области на этапе инфологического проектирования.
Время резервного копирования определяется интенсивностью отказов, сопровождающихся потерей данных. Формулу (6) можно представить в следующем виде:
[время], (6)
где 
- интенсивность отказов, сопровождающихся потерей данных. Если данные такого рода отсутствуют, то копирование производится через промежутки времени, в которые поступает порция данных порядка 20% первоначального объёма БД. Формулу (7) можно представить в следующем виде:
(7)
Количество обращений к логическим записям можно представить в виде формулы (8):
, (8)
где 
- количество обращений к записям j -го типа в i -м запросе.
Интенсивность обращений к информационному фонду можно представить в виде формулы (9)
, (9)
где 
- частота выполнения i - того запроса, Z - число запросов, обработка которых предусмотрена СУБД (определяется на этапе инфологического проектирования). При формировании набора характерных запросов.
Средний объем данных, предоставляемый пользователю во время выполнения i - ого запроса можно представить в виде формулы (10):
(10)
Изначально, для переменных длин полей возьмем максимальную длину поля, следовательно, размер типов данных для хранения в памяти будет составлять:
1) Integer – 4 (байта);
2) Varchar(x) – 2*n(байт);
3) Date – 10 (байт);
4) Time – 3 (байт);
5) Float – 4 (байт)
Были рассчитаны длины логических записей:
1) Таблица «Клиенты»: L1 = 190 (байта);
2) Таблица «Услуги»: L2 = 238 (байт);
3) Таблица «Сотрудники»: L3 = 20 (байт);
4) Таблица «Залы»: L4 = 130 (байт);
5) Таблица «Расписание»: L5 = 100 (байт);
6) Таблица «Клубные карты»: L6 = 100 (байт);
Рассчитаем объем памяти, необходимой для размещения информационного фонда:
I = 5600 (байт)
Приращение информационного фонда:
ΔI = 302.19(1/байт)
Время резервного копирования:
0,57 (минут)
Предположим, что база данных будет размещена на жестком диске размером 2 Гбайта (3221225472 байта), а программное обеспечение вместе с операционной системой составляет 500 Мбайт (5242800 байт).
0,99 (минут)
Выводы по разделу 3
Таким образом была разработана физическая модель базы данных фитнес клуба. было выяснена причина выбора программной и аппаратной платформы для реализации БД, продемонстрирована созданная физическая модель, а также таблицы были заполнены данными, для дальнейшей обработки. для проверки корректности работы БД было реализовано 15 запросов, в которых были использованы различные функциональные возможности СУБД MySql. Также для проверки целостности БД были реализованы запросы на добавление, удаление и изменения ссылочных данных. Были разграничены права доступа к БД. И рассчитаны информационные параметры для выяснения объема памяти информационного фонда, времени резервного копирования, среднего объема данных предоставляемых пользователю во время выполнения запросы.
4 Разработка клиентского приложения
4.1 Обоснование выбора языка программирования
В качестве языка программирования был выбран C#, так как он является объектно-ориентированным, что позволяет описывать абстрактные конструкции на основе предметной области, и реализовывать между ними взаимодействие. Для реализации пользовательского интерфейса был использован набор управляемых библиотек Windows Forms, так как он упрощает доступ к элементам интерфейса Windows, в его состав входят многофункциональные элементы пользовательского интерфейса, позволяющие создать визуально красивые и удобные интерфейсы.
4.2 Разработка интерфейса пользователя
Пользовательский интерфейс был реализован с помощью форм. Полный листинг представлен в Приложении Б.
На рисунке 4.1 представлено главное меню приложения, которое служит для выбора нужной функции.
Рисунок 4.2 – Главное меню приложения
На рисунке 4.3 представлена таблица «Клиенты», в которой можно изменять, удалять, добавлять записи, так же производить поиск по полю.
Рисунок 4.3 – Таблица «Клиенты»
На рисунке 4.4 представлено диалоговое окно добавления записи в таблицу «Клиенты», которое появляется при нажатии на кнопку «Создание». В самом окне производится ввод данных, требуемых для создания записи.
Рисунок 4.4 – Диалоговое окно добавления записи в таблицу «Клиенты»
На рисунке 4.5 продемонстрировано диалоговое окно изменения записи в таблице «Клиенты», которое появляется при нажатии на кнопку «Изменение». В нём возможно произвести правки в записи, в случае неверного заполнения.
Рисунок 4.5 – Диалоговое окно изменения записи в таблице «Клиенты»
На рисунке 4.6 представлено диалоговое окно подтверждения удаления записи, которое появляется при нажатии на кнопку «Удаление». Это предусмотрено для того чтобы пользователь случайным образом не удалил записи.
Рисунок 4.6 – Диалоговое окно подтверждения удаления записи в таблице «Клиенты»
На рисунке 4.7 продемонстрировано диалоговое окно подтверждение отмены обновления записи в таблице «Клиенты», которое появляется при нажатии в диалоговом окне обновления, на кнопку «Отмена», для того чтобы пользователь удостоверился, точно ли стоит ему покидать окно либо нет.
Рисунок 4.7 – Диалоговое окно подтверждения отмены обновления записи в таблице «Клиенты»
Аналогичным образом производится работа всех остальных таблиц.
4.3 Тестирование работы приложения
Далее проведём правильность работы приложения. Так как обработка всех форм происходит аналогичным образом, то для демонстрации работы достаточно проверить корректность на одной из таблиц. К примеру, возьмём таблицу «Сотрудники».
На рисунке 4.8 представлена таблица «Сотрудники», над которой мы можем проводить CRUD операции.
Рисунок 4.8 – Демонстрация таблицы «Сотрудники»
Проверим правильность работы метода добавления записи в таблицу «Сотрудники». Для этого нажмём на кнопку «Создать», после чего введём данные записи, и нажмём на кнопку «Сохранить».
Рисунок 4.9 – Демонстрация работы добавления записи в таблицу
Проверим, добавилась ли запись, как видим при успешном добавлении появляется диалоговое окно, которое содержит сообщение об успешном добавлении записи таблицу.
Рисунок 4.10 – Демонстрация появления сообщения при успешном добавлении
Проверяем и как видим запись была добавлена в таблицу (Рисунок 4.11).
Рисунок 4.11 – Демонстрация успешного добавления записи в таблицу «Сотрудники»
Далее проверим корректность работы изменения записи, для этого нажмём на кнопку изменить напротив записи, которую хотим изменить, после чего введем данные на которые хотим изменить данные полей записи (Рисунок 4.12).
Рисунок 4.12 – Демонстрация работы возможности изменения записи
На рисунке 4.13 продемонстрируем успешность выполнения работы изменения записи.
Рисунок 4.13 – Демонстрация работы изменения записи
Из рисунка 4.13 видно, что запись была изменена, то есть метод изменения работает корректно.
Далее проверим корректность работы метода поиска данных, для этого в поле поиска введем фамилию нужного сотрудника, по которой произойдёт фильтрация данных и вывод записей, удовлетворяющих введенному значению (Рисунок 4.14).
Рисунок 4.14 – Демонстрация работы метода поиска записи
Из рисунка 4.14 видно, что поиск производится корректно, так как значение поля фамилии соответствует введенному в поле поиска. То есть при совпадении подстроки уже происходит фильтрация.
Далее проверим корректность работы метода удаления записи, для этого нажмём на кнопку «Удалить» напротив той записи, которую требуется удалить, после чего появиться диалоговое окно, которое запросит подтверждение удаления записи, что исключает удаление записи случайным образом (Рисунок 4.15).
Рисунок 4.15 – Демонстрация работы метода удаления записи
Рисунок 4.16 – Демонстрация работы успешности удаления записи
Из рисунка 4.16 видно, что запись была удалена, соответственно метод работает корректно.
Далее проверим валидацию данных, в случае некорректного ввода, должно появляться соответствующее диалоговое окно с сообщением (Рисунок 4.17).
Рисунок 4.17 – Демонстрация корректности валидации данных
На рисунке 4.17 видно, что, в случае если не установлены значения полей, появляется соответствующее диалоговое окно.
Далее проверим корректность при вводе отрицательного значения в одно из полей (Рисунок 4.18).
Рисунок 4.18 – Демонстрация корректности валидации данных
Также была реализована небольшая статистика, в которой можно увидеть самого востребованного сотрудника, самую дорогую клубную карту и количество клиентов, посещаемых клуб (Рисунок 4.19).
Рисунок 4.19 – Демонстрация работы статистики
Из рисунка 4.19, видно, что статистические данные выводятся корректно, так как соответствуют действительному.
Вывод по разделу 4
В разделе 4 было описано каким образом и с помощью каких инструментов было реализовано клиентское приложение. Далее для примера была продемонстрирована работа приложения на одной из таблиц. Было выяснено что всё работает корректно.
Заключение
В ходе выполнения данного курсового проекта были ознакомлены с таким понятием как база данных. Была спроектирована база данных эмулирующая предметную область «Фитнес клуб», предназначенная для хранения, сборки и обработки информации. К базе данных для удобства было разработано клиентское приложение, которое позволяет обрабатывать информацию, связанную с предметной областью.
Выполнение данной курсовой работы включало в себя несколько этапов. Для ознакомления с предметной областью проведён анализ, для реализации проекта. На основе полученных данных была спроектирована логическая модель, с помощью которой были получены все сущности предметной области, также их свойства и связей между ними. Для того чтобы было легче производить запросы, была проведена нормализация структуры БД до НФБК. Далее была разработана физическая модель данных в СУБД phpmyadmin. После чего было проведено тестирование базы данных, проверка на целостность, так же были реализованы запросы с использованием объединения, агрегатных функций, выборки прочих функций СУБД.
Разработка клиентского приложения также включало в себя множество этапов. Был выбран язык программирования C# и программная среда Visual Studio. Затем были реализованы интерфейс и логика. После чего приложение было протестировано. В итоге полученное приложение могут использовать работники фитнес клуба, которые будут отвечать за ведение БД.
На финальном этапе была составлена документация, содержащая описание разработанной системы, результаты тестирования, анализ и структуру базы данных.
Список использованных сокращений
ИТ – информационные технологии
БД – база данных
ПрО – предметная область
CRUD – create, read, update, delete. Операции создания, чтения, изменения и удаления
ССС – сложная сетевая структура
ПСС – простая сетевая структура
KB – Key Based model. Модель, основанная на ключах
НФ – нормальная форма
НФБК – нормальная форма Бойса Кодда
СУБД – система управления базами данных
Список ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ
1.Введение в системы баз данных / К. Дж. Дейт; [пер. с англ. К.А. Птицина]. – Москва, Санкт-Петербург, Киев: Вильяме, 2005. – 1315 с.
2. Троелсен, Эндрю Язык программирования C# 7.0 и платформа.NET 4.7 / Эндрю Троелсен. - М.: Вильямс, 2015. - 1 486 c
3.Руководство по MySQL [Электронный ресурс] URL: https://metanit.com/sql/mysql/
4.MySQL [Электронный ресурс] URL: https://www.mysql.com/
5.Раздел MYSQL на METANIT.COM [Электронный ресурс] URL: https://metanit.com/mysql/
6.Stack Overflow - Where Developers Learn, Share, & Build Careers [Электронный ресурс] URL: https://stackoverflow.com/
ПРИЛОЖЕНИЕ А
Код запросов
//Создание таблицы "сотрудники"
CREATE TABLE staff(
staffId INT NOT NULL AUTO_INCREMENT,
address VARCHAR(50) NOT NULL,
phoneNumber VARCHAR(13) NOT NULL,
workExperience FLOAT(5,1) NOT NULL DEFAULT 0 CHECK(workExperience>0),
salary INT NOT NULL DEFAULT 0 CHECK(salary>0),
service VARCHAR(30) NOT NULL,
surname VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
patronymic VARCHAR(100) NOT NULL,
PRIMARY KEY(staffId)
);
//Создание таблицы (Клиенты)
CREATE TABLE clients(
clientId int NOT NULL AUTO_INCREMENT,
staffId INT NOT NULL,
subscriptionId INT NOT NULL,
address VARCHAR(50) NOT NULL,
phoneNumber VARCHAR(13) NOT NULL,
surname VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
patronymic VARCHAR(100) NOT NULL,
PRIMARY KEY(clientId),
CONSTRAINT fk_clientsToStaff FOREIGN KEY (staffId) REFERENCES staff(staffId)
ON DELETE CASCADE ON UPDATE CASCADE
);
//Создание таблицы "Услуги"
CREATE TABLE service(
serviceId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nameService VARCHAR(50) NOT NULL,
price INT NOT NULL
);
//Добавление внешнего ключа от "Сотрудники" к "Услуги"
ALTER TABLE staff
ADD CONSTRAINT fk_staffToService FOREIGN KEY(serviceId) REFERENCES service(serviceId)
ON DELETE CASCADE ON UPDATE CASCADE;
//Создание таблицы залы
CREATE TABLE halls(
hallId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
hallName VARCHAR(50) NOT NULL
);
//Создание таблицы расписание
CREATE TABLE schedule(
scheduleId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
staffId INT NOT NULL,
clientId INT NOT NULL,
hallId INT NOT NULL,
serviceId INT NOT NULL,
dateLesson DateTime NOT NULL,
lessonDuration INT NOT NULL CHECK (lessonDuration<=3 AND lessonDuration>=0),
CONSTRAINT fk_scheduleToStaff FOREIGN KEY (staffId) REFERENCES staff(staffId)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_scheduleToClients FOREIGN KEY (clientId) REFERENCES clients(clientId)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_scheduleToHalls FOREIGN KEY (hallId) REFERENCES halls(hallId)
ON DELETE CASCADE ON UPDATE CASCADE,
);
//Создание таблицы Абонементы
CREATE TABLE club_cards
(
cardId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
startCard DateTime NOT NULL,
endSubCard DateTime NOT NULL CHECK (startSubscription<endSubscription),
price INT NOT NULL CHECK(price>=0),
serviceId INT NOT NULL,
clientId INT NOT NULL,
CONSTRAINT fk_subscriptionsToService FOREIGN KEY (serviceId) REFERENCES service(serviceId)
ON DELETE CASCADE ON UPDATE CASCADE
);
//Добавление внешнего ключа от Клиенты к Абонементы
ALTER TABLE club_cards
ADD CONSTRAINT fk_ClubCardsToclients FOREIGN KEY(clientId) REFERENCES clients(clientId)
ON DELETE CASCADE ON UPDATE CASCADE;
////////////////////////////////Целостность
//Удаление строки с таблицы "Залы"
ALTER TABLE halls
DELETE FROM halls WHERE hallId = 4;
//Добавление данных в таблицу "Клиенты" для проверки целостности
INSERT clients(clientId, staffId,address, phoneNumber,surname,name,patronymic)
VALUES (11,15,"ул А 2 кв 4","88081231422","Иванов","Иван","Николаевич");
//Добавление данных в таблицу "Клубные карты" для проверки целостности
INSERT club_cards(cardId, clientId,startCard,endCard,price,serviceId)
VALUES (11,15,"22.12.2020 00:00:00","26.12.2020 00:00:00",10000,1);
//Добавление данных в таблицу "Клубные карты" для проверки целостности
INSERT club_cards(cardId, clientId,startCard,endCard,price,serviceId)
VALUES (11,5,"22.12.2020 00:00:00","26.12.2020 00:00:00",10000,25);
//Изменения поля в таблице сотрудники для проверки целостности
UPDATE staff SET serviceId=15 WHERE serviceId = 1;
//Изменения поля в таблице расписание для проверки целостности
UPDATE schedule SET staffId=15 WHERE staffId = 4;
//Изменения поля в таблице расписание для проверки целостности
UPDATE schedule SET clientId=15 WHERE clientId = 1;
//Добавление привилегей Админа
CREATE USER 'admin'@'127.0.0.1' IDENTIFIED BY 'qwerty12345'
GRANT ALL PRIVILEGES ON *. * TO 'admin'@'127.0.0.1';
FLUSH PRIVILEGES;
//Добавление привилегей Менеджера
CREATE USER 'manager'@'127.0.0.1' IDENTIFIED BY 'qwerty123456'
GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'manager'@'127.0.0.1';
FLUSH PRIVILEGES;
//Демонстрационные запросы
// Первый запрос
SELECT * FROM clients WHERE address = "ул Гоголя 16 кв 16";
// Второй запрос
SELECT AVG(lessonDuration) AS average_duration FROM schedules WHERE DATE_FORMAT(dateLesson,'%Y-%m-%d')='2019-11-25';
// Третий запрос
SELECT name, surname, patronymic FROM staffs WHERE salary >100000;
// Четвёртый запрос
SELECT nameService, MIN(price) as min_price FROM services;
// Пятый запрос
SELECT *, MAX(price) FROM club_cards;
// Шестой запрос
SELECT staffs.surname, staffs.name, staffs.patronymic FROM clients JOIN staffs WHERE clients.staffId=staffs.staffId AND clients.address = "ул Гоголя 16 кв 16";
// Седьмой запрос
SELECT staffs.surname, staffs.name, clients.surname, clients.name FROM clients
JOIN schedules ON schedules.clientId = clients.clientId
AND DATE_FORMAT(dateLesson, '%Y-%m-%d %H:%i:%s') = '2019-11-25 10:00:00'
JOIN staffs ON clients.staffId = staffs.staffId;
// Восьмой запрос
SELECT clients.surname, clients.name FROM club_cards
JOIN clients ON club_cards.clientId = clients.clientId AND club_cards.price > (
SELECT AVG(club_cards.price)
FROM club_cards);
// Девятый запрос
SELECT clients.surname,clients.name FROM club_cards
JOIN clients ON clients.clientId = club_cards.clientId AND club_cards.price = (SELECT MIN(club_cards.price) FROM club_cards);
//Десятый запрос
SELECT clients.surname, clients.name FROM club_cards JOIN clients ON club_cards.clientId=clients.clientId
AND (SELECT COUNT(club_cards.clientId) FROM club_cards GROUP BY(club_cards.clientId))>1;
//Самый восстребованный сотрудник:
SELECT staffs.surname,staffs.name, COUNT(*) as demaned_staff FROM clients JOIN staffs ON staffs.staffId=clients.staffId
GROUP BY clients.staffId ORDER BY demaned_staff DESC LIMIT 1;
ПРИЛОЖЕНИЕ Б
Clients.cs
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;
namespace TooSharp.Models
{
[DebuggerStepThrough]
public class Clients: TooSharp.Core.ITSModel
{
#region CODE
public static string TableName = "clients";
public string GetTableName() { return TableName; }
public List<string> GetColumns() {return Enum.GetValues(typeof(COLUMNS)).Cast<COLUMNS>().Select(v => v.ToString()).ToList(); }
public static TooSharp.Core.TSQueryBuilder<Client> Records() { return new TooSharp.Core.TSQueryBuilder<Client>(TooSharp.TSRelationships.getInstance()); }
public static TooSharp.Core.TSQueryBuilder<Client> Records(object[] columns) { return new TooSharp.Core.TSQueryBuilder<Client>(TooSharp.TSRelationships.getInstance(),columns); }
#endregion
#region COLUMNS
public enum COLUMNS
{
clientId,
staffId,
address,
phoneNumber,
surname,
name,
patronymic,
//column
}
#endregion
}
[DebuggerStepThrough]
public class Client: TooSharp.Core.TSmodel
{
#region PROPERTIES
[TSKey]
public int ClientId { get; private set; }=-1;
public int StaffId { get; set; }
[TSNotEmpty]
public string Address { get; set; }
[TSNotEmpty]
public string PhoneNumber { get; set; }
[TSNotEmpty]
public string Surname { get; set; }
[TSNotEmpty]
public string Name { get; set; }
[TSNotEmpty]
public string Patronymic { get; set; }
public Staff GetStaff() { return Staffs.Records().Get(this.StaffId); }
public void SetStaff(Staff staff) { this.StaffId = staff.StaffId; }
public TooSharp.Core.TSQueryBuilder<Club_card> GetClub_cards() { return Club_cards.Records().Where(Club_cards.COLUMNS.clientId,this.ClientId);}
public void AddClub_card(Club_card club_card) { club_card.ClientId = this.ClientId; club_card.Save(); }
public void AddClub_cards(IEnumerable<Club_card> club_cards) {IDbTransaction __ = TooSharp.Connection.BeginTransaction(); foreach (Club_card item in club_cards) AddClub_card(item); __.Commit();}
public TooSharp.Core.TSQueryBuilder<Schedule> GetSchedules() { return Schedules.Records().Where(Schedules.COLUMNS.clientId,this.ClientId);}
public void AddSchedule(Schedule schedule) { schedule.ClientId = this.ClientId; schedule.Save(); }
public void AddSchedules(IEnumerable<Schedule> schedules) {IDbTransaction __ = TooSharp.Connection.BeginTransaction(); foreach (Schedule item in schedules) AddSchedule(item); __.Commit();}
//property
#endregion
}
}
Club_Cards.cs
namespace TooSharp.Models
{
[DebuggerStepThrough]
public class Club_cards: TooSharp.Core.ITSModel
{
#region CODE
public static string TableName = "club_cards";
public string GetTableName() { return TableName; }
public List<string> GetColumns() {return Enum.GetValues(typeof(COLUMNS)).Cast<COLUMNS>().Select(v => v.ToString()).ToList(); }
public static TooSharp.Core.TSQueryBuilder<Club_card> Records() { return new TooSharp.Core.TSQueryBuilder<Club_card>(TooSharp.TSRelationships.getInstance()); }
public static TooSharp.Core.TSQueryBuilder<Club_card> Records(object[] columns) { return new TooSharp.Core.TSQueryBuilder<Club_card>(TooSharp.TSRelationships.getInstance(),columns); }
#endregion
#region COLUMNS
public enum COLUMNS
{
cardId,
startCard,
endCard,
price,
serviceId,
clientId,
//column
}
#endregion
}
[DebuggerStepThrough]
public class Club_card: TooSharp.Core.TSmodel
{
#region PROPERTIES
[TSKey]
public int CardId { get; private set; }=-1;
[TSNotEmpty]
public DateTime StartCard { get; set; }
[TSNotEmpty]
public DateTime EndCard { get; set; }
[TSNotEmpty]
public int Price { get; set; }
public int ServiceId { get; set; }
public int ClientId { get; set; }
public Service GetService() { return Services.Records().Get(this.ServiceId); }
public void SetService(Service service) { this.ServiceId = service.ServiceId; }
public Client GetClient() { return Clients.Records().Get(this.ClientId); }
public void SetClient(Client client) { this.ClientId = client.ClientId; }
//property
#endregion
}
}
Halls.cs
namespace TooSharp.Models
{
[DebuggerStepThrough]
public class Halls: TooSharp.Core.ITSModel
{
#region CODE
public static string Ta