Курсовая работа: разработка базы данных для ООО «Лабиринт-Пост»

НЕГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ ЧАСТНОЕ УЧРЕЖДЕНИЕ
ВЫСШЕГО ОБРАЗОВАНИЯ
«МОСКОВСКИЙ ФИНАНСОВО-ПРОМЫШЛЕННЫЙ УНИВЕРСИТЕТ
«СИНЕРГИЯ»
Колледж «Синергия»
Специальность
Информационные
системы и
программирование
09.02.07
(код)
ЦЭ
Кафедра
ЦИФРОВОЙ
ЭКОНОМИКИ
(аббревиатура)
КУРСОВАЯ РАБОТА
На тему
Разработка базы данных для автоматизированной системы
управления ООО «Лабиринт-Пост»
(наименование темы)
По дисциплине
Управление и автоматизация баз данных | ПМ.05 |
Соадминистрирование и автоматизация баз данных и
серверов
(наименование дисциплины)
Обучающийся
(Ф.И.О. полностью)
МОСКВА 2025 г.
(подпись)
СОДЕРЖАНИЕ
ВВЕДЕНИЕ3
Глава 1. Проектирование базы данных5
1.1. Анализ предметной области5
1.2. Инфологическое проектирование7
1.3. Построение логической модели базы данных10
1.4. Построение физической модели базы данных15
Глава 2. Проектирование SQL-запросов19
Глава 3. Проектирование серверного приложения: хранимые процедуры,
функции, триггеры22
Глава 4. Администрирование базы данных26
ЗАКЛЮЧЕНИЕ30
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ31
ПРИЛОЖЕНИЯ33
2
ВВЕДЕНИЕ
Любая фирма, компания или любое
предприятие в огромных
количествах оперируют данными и информацией. Все данные должны иметь
структурированною форму, что бы ее можно было легко находить, сортировать,
обрабатывать. Естественно, что в эпоху глобальной компьютеризации все это
делается на уровне компьютерной техники.
В современном мире информация или данные представляют из себя
реляционные базы данных, которые состоят из таблиц со своими колонками,
строками, связями между ними.
Язык SQL является мощным инструментом в программировании БД и
их содержимого.
Целью данного курсового проекта является разработка базы данных для
автоматизированной
системы
управления
ООО
«Лабиринт-Пост»
для
автоматизации сервиса курьерской службы.
Задачи курсового проекта:

провести системный анализ предметной области ООО «Лабиринт-
Пост»;

разработать инфологическую модель базы данных;

обосновать выбор модели данных и осуществить логическое
проектирование базы данных;

нормализовать спроектированную модель и составить схему базы
данных;

осуществить реализацию БД на выбранной СУБД.
В курсовом проекте будет поэтапно разобрана базы данных.
В
первой
главе
будет
проведено
описание
организационно-
функциональной структуры объекта автоматизации, проектирование БД:
разработка инфологической модели, обоснование выбора даталогической
модели и ее графическое отображение, а также нормализация схемы до третьей
нормальной формы.
3
Во второй главе будет описано создание запросов к базе данных.
Третья глава будет содержать описание хранимых процедур, функций и
триггеров серверного приложения.
В четвертой главе описано администрирование пользователей базы
данных и произведен выбор стратегии резервного копирования и его
реализация.
4
Глава 1. Проектирование базы данных
1.1. Анализ предметной области
Общество с ограниченной ответственностью «Лабиринт-Пост» (ИНН
5040167090 КПП 504001001 ОГРН 1205000034656) расположено по адресу
140143, Московская область, Раменский район, г. Раменское, дп. Родники, ул.
Чехова, дом 2/2, этаж 1, комната 122.
Основной вид деятельности - деятельность по складированию и
хранению (коду по ОКВЭД ред.2 - 52.10).
Дополнительно заявлены следующие виды деятельности

49.42 - Предоставление услуг по перевозкам

52.29
-
Деятельность
вспомогательная
прочая,
связанная
с
перевозками

53.20.3 - Деятельность курьерская
Описание организационно-функциональной структуры подразделения
данного предприятия отвечающего за курьерскую службу представлено на
рисунке 1.
Рис. 1. Организационно-функциональная схема
Как видно из схемы, структура службы доставки довольно проста.
5
Во
главе
предприятия
стоит
руководитель.
Он
принимает
стратегические решения относительно вектора развития компании, регулирует
деятельность предприятия, решает состав сотрудников и их деятельность.
Секретарь выполняет поручения руководителя по решению различных
вопросов, связанных с организацией деятельности предприятия и руководителя,
таких как прием звонков и посетителей, организация рабочего дня
руководителя, организация собраний сотрудников и так далее.
Бухгалтерия
предприятия.
Она
занимается
вопросами,
связанными
собирает
обрабатывает
полную
и
с
активами
информацию
о
деятельности хозяйствующего субъекта, выплачивает зарплаты сотрудникам и
так далее.[1]
Менеджеры координируют поступающие заказы, работают с клиентами,
а также руководят работой курьеров.
Курьеры забирают заказы со складов и развозят их по местам
назначения.
В курьерской службе «Лабиринт-Пост» ведется учет менеджеров,
курьеров, клиентов, их заказов, товаров, доставкой которых занимается
предприятие, и складов, где эти товары хранятся.
Для менеджеров хранится следующая информация:

ФИО

Дата рождения

Паспортные данные

Телефон

Дата приема на работу
Для курьеров хранится следующая информация:

ФИО

Дата рождения

Паспортные данные

Телефон

Дата приема на работу
6
Для заказов хранится следующая информация:

Идентификатор клиента, сделавшего заказ

Идентификатор менеджера, координирующего заказ

Идентификатор курьера, доставляющего заказ

Дата доставки

Способ оплаты

Идентификатор точки самовывоза, если был выбран этот способ
доставки

Идентификатор менеджера
Для клиентов хранится следующая информация:

Имя

Тип (физическое или юридическое лицо)

Адрес

Контактный телефон

Электронная почта
Для товаров хранится следующая информация:

Название

Цена

Описание
Для складов хранится следующая информация:

Адрес склада
Для точек самовывоза хранится следующая информация:

Адрес точки
1.2. Инфологическое проектирование
Все этапы проектирования БД подразумевают создание моделей данных
об интересующей предметной области. Моделирование данных упрощает
понимание смысла элементов данных, способствует более плодотворному
общению пользователей и разработчиков.[2]
7
Исходя из важности адекватного отображения предметной области, к
моделям данных предъявляют ряд требований, и выдвигают комплекс
критериев для оценки их эффективности (оптимальности):

Структурная достоверность – соответствие способу определения и
организации информации в данной предметной области;

Простота – легкость понимания модели как разработчиками, так и
пользователями;

Выразительность – способность представлять отличия между
разными типами данных, связи между данными и ограничения;

Отсутствие избыточности – исключение излишней информации;

Целостность – согласованность по способам использования и
управления информацией.
Инфологическое (концептуальное) проектирование – процесс создания
внешней (инфологической) модели данных о предметной области, не зависящее
от любых физических аспектов ее представления.
На этом этапе используется информация, объединяющая требования
пользователей. Инфологическое проектирование базы данных не зависит от
таких подробностей ее реализации, как тип выбранной СУБД, набор
создаваемых прикладных программ, используемые языки программирования,
тип вычислительной системы и т.п. При разработке инфологическая модель
постоянно подвергается критической оценке, проверке на соответствие
требованиям пользователей, и при необходимости модифицируется. От
качества созданной инфологической модели в определяющей степени зависит
эффективность конечной базы данных.[4]
Цель
инфологического
моделирования
–
обеспечение
наиболее
естественных для человека способов сбора и представления той информации,
которую предполагается хранить в создаваемой базе данных. Поэтому
инфологическую модель данных пытаются строить на доступном широкому
кругу пользователей и разработчиков языке. Наибольшей популярностью
пользуется модель «сущность-связь» из-за своей доступности.
8
Сущность – различное множество объектов реального мира с набором
атрибутов. Атрибут – свойство сущности или связи. Связь – ассоциирование
двух или более сущностей.
Для информационной системы курьерской службы «Лабиринт-Пост» на
основе проведенного системного анализа предметной области выделены
следующие сущности:

Клиенты – содержит информацию о клиентах;

Товары – содержит информацию о товарах;

Склады – содержит информацию о складах;

Точки самовывоза – содержит информацию о точках самовывоза;

Менеджеры – содержит информацию о менеджерах;

Курьеры – содержит информацию о курьерах;

Количество по позиции – содержит информацию о количестве
определенного товара, оформленного клиентом в заказ;

Заказы – содержит информацию о заказе.
Исходя
из
приведенных
выше
сущностей,
была
построена
инфологическая модель предметной области, которая приведена на рисунке 2.
Рис. 2. Инфологическая модель предметной области
9
1.3. Построение логической модели базы данных
Рассмотрев инфологическую модель предметной области, было принято
решение, что для логического проектирования базы данных реляционная
модель данных, наиболее полно соответствует требованиям, предъявленным к
разрабатываемой информационной системе, т.к. в ней:[3]

отсутствует дублирование информации;

поддерживается целостности данных при вставке, удалении или
изменении записей;

имеется
возможность
организации
всех
видов
связи
между
отношениями 1:1, 1:M и M:M.
В реляционной базе данных даталогическое проектирование приводит к
разработке корректной схемы базы данных, т.е. такой схемы, в которой
отсутствуют нежелательные зависимости между атрибутами. При этом можно
использовать
процесс
проектирования
с
помощью
декомпозиции,
т.е.
последовательно нормализовать схему отношений, тем самым накладывая
ограничения и избавляясь от нежелательных зависимостей между атрибутами.
В реляционных базах данных (РБД) даталогическое проектирование
приводит к разработке схемы БД, т.е. совокупности схем отношений, адекватно
моделирующих объекты ПО и семантических связей между ними.
Основой анализа корректности схемы являются функциональные
зависимости между атрибутами БД. В итоге должно быть получено описание
схемы БД в терминах выбранной СУБД.[5]
Процесс разработки корректной схемы БД и является даталогическим
проектированием. Возможны 2 способа:
1. Декомпозиция (разбиение);
2. Синтез;
Для перехода от инфологической модели к реляционной существует
специальный алгоритм:

каждой сущности ставится в соответствие отношение;
10

каждому
атрибуту
сущности
ставится
в
соответствие
соответствующий атрибут соответствующего отношения;

первичный ключ сущности становится PK соответствующего
отношения, при этом атрибуты, входящие в PK, обязательны для
заполнения (NOT NULL);

в каждое отношение, соответствующее подчинённой сущности,
добавляется набор атрибутов основной сущности, являющийся в ней
первичным ключом. В отношении, соответствующее подчинённой
сущности эти атрибуты становятся FK (внешним ключом);

по умолчанию, все атрибуты, не входящие в PK, необязательны;

для отражения категоризации сущностей возможны несколько
вариантов;

все связи М:М должны быть раскрыты
Воспользуемся данным алгоритмом и опишем каждую сущность
инфологической модели:
Клиенты:

Идентификатор клиента – int, NOT NULL, PK

Логин клиента – varchar(20), NOT NULL, UNIQUE

Hash пароля клиента – varchar(50), NOT NULL, UNIQUE

Имя клиента – varchar(45), NOT NULL, UNIQUE

Тип клиента – varchar(20), NOT NULL

Адрес клиента – varchar(45), NOT NULL

Контактный номер – varchar(15), UNIQUE

Электронный адрес – varchar(45), UNIQUE
Курьеры:

Идентификатор курьера – int, NOT NULL, PK

Логин курьера – varchar(20), NOT NULL, UNIQUE

Hash пароля курьера – varchar(50), NOT NULL, UNIQUE

Имя курьера – varchar(45), NOT NULL, UNIQUE

Дата рождения – date
11

Паспортные данные – varchar(100), NOT NULL, UNIQUE

Дата приема на работу – date, NOT NULL

Контактный номер - varchar(15), NOT NULL, UNIQUE
Менеджеры:

Идентификатор менеджера – int, NOT NULL, PK

Логин курьера – varchar(20), NOT NULL, UNIQUE

Hash пароля курьера – varchar(50), NOT NULL, UNIQUE

Имя курьера – varchar(45), NOT NULL, UNIQUE

Дата рождения – date

Паспортные данные – varchar(100), NOT NULL, UNIQUE

Дата приема на работу – date, NOT NULL

Контактный номер - varchar(15), NOT NULL, UNIQUE
Заказы:

Идентификатор заказа – int, NOT NULL, PK

Идентификатор клиента– int, FK, NOT NULL

Идентификатор курьера – int, FK, NOT NULL

Дата доставки - date

Метод оплаты – varchar(45), NOT NULL

Идентификатор точки самовывоза– int, FK

Идентификатор менеджера– int, FK, NOT NULL
Количество по позиции:

Идентификатор подзаказа – int, NOT NULL, PK

Идентификатор товара– int, FK, NOT NULL

Идентификатор заказа– int, FK, NOT NULL

Количество – int, NOT NULL
Товары:

Идентификатор товара – int, PK, NOT NULL

Название – varchar(45), NOT NULL, UNIQUE

Цена – int, NOT NULL

Описание– varchar(45)
12
Точки самовывоза:

Идентификатор точки – int, PK, NOT NULL

Адрес точки – varchar(45), NOT NULL
Склады:

Идентификатор склада – int, PK, NOT NULL

Адрес склада – varchar(45), NOT NULL
Исходя из приведенных отношений, построим даталогическую схему БД
(рисунок 3):
Рис. 3. Даталогическая схема БД
Нормальная форма — свойство отношения в реляционной модели
данных, характеризующее его с точки зрения избыточности, которая
потенциально может привести к логически ошибочным результатам выборки
или изменения данных. Нормальная форма определяется как совокупность
требований, которым должно удовлетворять отношение.[6]
13
Нормализация – это процесс преобразования базы данных к виду,
отвечающему
нормальным
формам.
Нормализация
предназначена
для
приведения структуры базы данных к виду, обеспечивающему минимальную
избыточность, то есть нормализация не имеет целью уменьшение или
увеличение производительности работы, или же уменьшение или увеличение
объёма
БД.
Конечной
целью
нормализации
является
уменьшение
потенциальной противоречивости хранимой в БД информации. Устранение
избыточности производится, как правило, за счёт декомпозиции отношений
таким образом, чтобы в каждом отношении хранились только первичные факты
(то есть факты, не выводимые из других хранимых фактов).
Таблица находится в первой нормальной форме, если каждый её атрибут
атомарен, то есть может содержать только одно значение. Таким образом, не
существует 1НФ таблицы, в полях которых могут храниться списки значений.
Для приведения таблицы к 1НФ обычно требуется разбить таблицу на
несколько отдельных таблиц.
Отношение находится во второй нормальной форме, если она находится
в первой нормальной форме, и при этом любой её атрибут, не входящий в
состав первичного ключа, функционально полно зависит от первичного ключа.
Функционально полная зависимость означает, что атрибут функционально
зависит от всего первичного составного ключа, но при этом не находится в
функциональной зависимости от какой-либо из входящих в него атрибутов
(частей). Или другими словами: в 2НФ нет не ключевых атрибутов, зависящих
от части составного ключа.[7]
Отношение находится в третьей нормальной форме, если она находится
во второй нормальной форме 2НФ и при этом любой ее не ключевой атрибут
зависит только от первичного ключа. Таким образом, отношение находится в
3НФ тогда и только тогда, когда оно находится во 2НФ и отсутствуют
транзитивные зависимости не ключевых атрибутов от ключевых.
14
3НФ – отношение находится в 3НФ, если оно находится во 2НФ и не
содержит транзитивных зависимостей. Все отношения данной модели
находятся в 3НФ, т.к. ни в одном из них нет транзитивных зависимостей.
При решении практических задач в большинстве случаев третья
нормальная форма является достаточной. Поэтому процесс проектирования
базы данных, как правило, заканчивается приведением к ней.[9]
Если посмотреть на даталогическую модель, можно выяснить, что
разрабатываемая база данных уже удовлетворяет требованиям третьей
нормальной формы. Следовательно, процесс нормализации проводить не
нужно.
1.4. Построение физической модели базы данных
На основе даталогической схемы БД опишем базу данных которая
содержит 9 таблиц (таблицы 1 - 9):
Таблица 1
Таблица БД «Clients» - Клиенты
Наименование
поля
Идентификатор
клиента
Логин клиента
Hash
пароля
клиента
Имя клиента
Тип клиента
Адрес клиента
Контактный
номер
Электронный
адрес
Идентификатор
поля
Client_ID
Тип поля
Длина
int
Username
Password_hash
varchar
varchar
20
50
Name
Type
Address
Contact_number
varchar
varchar
varchar
varchar
45
20
45
15
Email
varchar
45
Прочее
PK
15
Таблица 2
Таблица БД «Couriers» - Курьеры
Наименование
поля
Идентификатор
курьера
Логин курьера
Hash
пароля
курьера
Имя курьера
Дата рождения
Паспортные
данные
Дата приема на
работу
Контактный номер
Идентификатор поля
Тип поля
Длина
Courier_ID
int
Username
Password_hash
varchar
varchar
20
50
FIO
Birth_date
Passport
varchar
date
varchar
45
Hire_date
date
Contact_number
varchar
Прочее
PK
100
15
Таблица 3
Таблица БД «Managers» - Менеджеры
Наименование
поля
Идентификатор
менеджера
Логин курьера
Hash
пароля
курьера
Имя курьера
Дата рождения
Паспортные
данные
Дата приема на
работу
Контактный номер
Идентификатор поля
Тип поля
Длина
Manager_ID
int
Username
Password_hash
varchar
varchar
20
50
FIO
Birth_date
Passport
varchar
date
varchar
45
Hire_date
date
Contact_number
varchar
Прочее
PK
100
15
Таблица 4
Таблица БД «Order» - Заказ
Наименование
поля
Идентификатор
заказа
Идентификатор
клиента
Идентификатор
курьера
Дата доставки
Метод оплаты
Идентификатор поля
Тип поля
Длина
Прочее
Order_ID
int
PK
Client_ID
int
FK
Courier_ID
int
FK
Delivery_date
Payment_method
date
varchar
16
45
Продолжение таблицы 4
Идентификатор
точки самовывоза
Идентификатор
менеджера
Self_pickup_ID
int
FK
Manager_ID
int
FK
Таблица 5
Таблица БД «Position_count» - Количество по позиции
Наименование
поля
Идентификатор
подзаказа
Идентификатор
товара
Идентификатор
заказа
Количество
Идентификатор
поля
Posibon_ID
Тип поля
Длина
Прочее
int
PK
Product_ID
int
FK
Order_ID
int
FK
Amount
int
Таблица 6
Таблица БД «Products» - Товары
Наименование
поля
Идентификатор
товара
Название
Цена
Описание
Идентификатор
поля
Product_ID
Тип поля
int
Name
Price
Description
varchar
int
varchar
Длина
Прочее
PK
45
45
Таблица 7
Таблица БД «Self_pickup_points» - Точки самовывоза
Наименование
поля
Идентификатор
точки
Адрес точки
Идентификатор
поля
Point_ID
Тип поля
int
Point_address
varchar
Длина
Прочее
PK
45
Таблица 8
Таблица БД «Warehouses» - Склады
Наименование
поля
Идентификатор
склада
Адрес склада
Идентификатор
поля
Warehouse_ID
Тип поля
int
Address
varchar
17
Длина
Прочее
PK
45
Промежуточная таблица “товары к сладам”, реализующая связь многие
ко многим между данными таблицами
Таблица 9
Таблица БД «Product_to_warehouse» – Товары к складам
Наименование
поля
Идентификатор
склада
Идентификатор
товара
Идентификатор
поля
Warehouse_ID
Тип поля
Длина
Прочее
int
FK
Product_ID
int
FK
Программный код создания и заполнения таблиц базы данных приведен
в приложении.
В первой главе курсовой работы приведена разработка информационнологической модели. Выделены сущности, дано их описание и построена
инфологическая модель предметной области. На основании инфологической
модели построена реляционная модель данных, дан список атрибутов ее
отношений и проведена нормализация до третьей нормальной формы.
Произведено физическое проектирование базы данных системы
18
Глава 2. Проектирование SQL-запросов
Рассмотрим наиболее распространенные запросы к базе данных службы
доставки.
Сколько каждый курьер доставил заказов:
SELECT courier_id, COUNT(order_id)
From delivery_list
WHERE date_arrived IS NOT NULL
GROUP BY courier_id;
Информация о заказах, которые не были доставлены клиентам:
SELECT * FROM delivery_list
WHERE taken NOT IN ('Yes');
Сумма заказов за весь год:
SELECT SUM(amount_of_orders) AS orders_per_year FROM
year_statistics;
Убывающая сортировка заказов по месяцам:
SELECT month_name, amount_of_orders
FROM year_statistics
ORDER BY amount_of_orders DESC;
Вывести месяц, где больше всего заказов:
SELECT month_name, amount_of_orders FROM year_statistics
WHERE amount_of_orders = (SELECT MAX(amount_of_orders)
FROM year_statistics);
Популярность районов по количествам клиентов:
SELECT district
FROM customers
GROUP BY district
ORDER BY COUNT(district) DESC;
Детальная информация по заказам, а также время их доставки:
SELECT *, SEC_TO_TIME(TIMESTAMPDIFF(second, date_get,
date_arrived))
19
AS time_of_delivery
FROM orders
NATURAL JOIN delivery_list;
Выборка клиентов, которые живут в районе "South":
SELECT * FROM Customers
WHERE district IN ('South');
Детали заказа (номер, товар, количество и цена).
SELECT orders_products.order_id, products.menu_name,
quantity,
ROUND(price*quantity, 2) AS total_price
FROM orders_products
INNER JOIN products ON orders_products.product_id =
products.product_id
ORDER BY order_id, quantity;
Запрос на выборку что какому курьеру на машине доступен район из
таблицы клиентов:
SELECT DISTINCT courier_info.courier_id,
customers.district
FROM courier_info
CROSS JOIN customers WHERE courier_info.delivery_type =
'car'
ORDER BY courier_id;
Информация о имени клиента, его мобильном телефоне и номере заказа:
SELECT customers.first_name, customers.last_name,
customers.phone_number, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id =
orders.customer_id;
Заполнение таблицы товарами
INSERT INTO goods SET
20
product ='стол',
count = 2,
price = 3000;
Нам нужно узнать кто является администратором
SELECT First_name,Middle_name,Last_name,data_rojg,
dolgnost,oklad
FROM Personal ASp
INNER JOIN Dolgnost AS d
ON ID_dolgnost = ID_dolgnost
WHERE dolgnost = 'администратор'
Поиск цены по наименованию продукта
DELETE FROM FoodTest
WHERE FoodTest.PnoductName = 'Молоток слесарный'
В данной главе произведено проектирование запросов к базе данных
службы доставки для представления данных в удобном пользователю виде.
21
Глава 3. Проектирование серверного приложения: хранимые процедуры,
функции, триггеры
С базой могут работать курьеры, менеджеры и клиенты.
Курьеры и менеджеры могут просматривать заказы, распределенные на
них. Клиенты могут оформлять заказы и видеть информацию о них.
Создадим процедуру, которая будет выбирать (и возвращать) три
колонки данных для таблицы Courier.
CREATE PROCEDURE Courier_Procedure
AS
SELECT Username, FIO, Pasport
FROM Courier
WHERE Hire_date >= ‘2022-01-01’
Создадим процедуру, которая будет выбирать товары стоимостью более
указанной.
CREATE PROCEDURE bigSum
AS
SELECT
p.ID_prodagi,
t.naimenovanie,
t.cena_shtyka,
p.kol_yo_prodanih, p.cena_pokypki
FROM Prodagi AS p
INNER JOIN Tovar AS t
ON p.IDjovar = t.IDjovar
WHERE tcena_shtyka > '15000'ANDp.kol_vo_prodanih > '3'
При запуске любого набора этих команд, будет создана хранимая
процедура.[10]
Для запуска этой хранимой процедуры необходимо обратится к ней по
имени, как это показано ниже. Например:
Courier_Procedure
GO
22
Для того, чтобы удалять товары, если они не хранятся ни на одном из
складов, создадим триггер:
CREATE
OR REPLACE TRIGGER noProduct BEFORE DELETE ON Warehouses
FOR EACH ROW DECLARE CURSOR productOnDelete IS
SELECT
Products.Product_ID
FROM
Products MINUS (
SELECT
Product_to_warehouse.Product_ID
FROM
Product_to_warehouse
GROUP BY
Product_to_warehouse.Product_ID
);
BEGIN
DELETE FROM
Product_to_warehouse
WHERE
Warehouse_ID =: old.Warehouse_ID;
FOR prod IN productOnDelete LOOP
DELETE FROM
Products
WHERE
Product_ID = prod.Product_ID;
END LOOP;
END;
Для того, чтобы назначать заказам менеджера, создадим триггер:
CREATE
OR REPLACE TRIGGER giveManager BEFORE INSERT ON Orders
FOR EACH ROW DECLARE managerID int(5): = 10000;
managerOrders int(5): = 10000;
CURSOR managerChoose IS
SELECT
Manager_ID,
COUNT(*) AS MOrders
FROM
23
Orders
GROUP BY
Manager_ID;
BEGIN FOR man IN managerChoose LOOP IF man.MOrders <
managerOrders THEN BEGIN managerID: = man.Manager_ID;
managerOrders: = man.MOrders;
END;
END IF;
END LOOP;
: new.Manager_ID: = managerID;
END;
Для того, чтобы назначать заказам курьера, создадим триггер:
CREATE
OR REPLACE TRIGGER giveCourier BEFORE INSERT ON Orders
FOR EACH ROW DECLARE courierID int(5): = 10000;
courierOrders int(5): = 10000;
CURSOR courierChoose IS
SELECT
Courier_ID,
COUNT(*) AS MOrders
FROM
Orders
GROUP BY
Courier_ID;
BEGIN FOR cur IN courierChoose LOOP IF cur.MOrders <
courierOrders THEN BEGIN courierID: = cur.Courier_ID;
courierOrders: = cur.MOrders;
END;
END IF;
END LOOP;
24
: new.Courier_ID: = courierID;
END;
В данной главе произведено проектирование серверного приложения.
Созданы хранимые процедуры, функции и триггеры. Описан механизм их
запуска.
25
Глава 4. Администрирование базы данных
Процесс создания пользователей внутри SQL Server можно разделить на
3 этапа:

создание логина - учетной записи для подключения к SQL Server;

затем создание пользователя базы данных, которому соответствует
этот логин;

предоставление пользователю необходимых разрешений.
Первое,
что
нужно
сделать
при
предоставлении
разрешений
пользователю - это предоставить ему логин, т. е. учетную запись, которая будет
использоваться для подключения к серверу SQL Server. Прежде, чем создать
логин, определим, какой тип будем использовать.[12]
В SQL Server существует два типа логина:

логин Windows. При использовании логинов Windows в системные
таблицы
базы
данных
master
записывается
идентификаторе
учетной
записи
Windows
информация
(но
не
об
пароль).
Аутентификация производится обычными средствами Windows при
входе пользователя на свой компьютер.

логин SQL Server. При использовании логина SQL Server пароль для
этого логина хранится вместе с идентификатором логина в базе данных
master. При подключении пользователя к серверу ему придется указать
имя логина и пароль.
Для лучшей зашиты обычно создается логин SQL Server. Для примера
создадим логин для пользователя Ivanova. Откроем контейнер Security - Logins
- New Login. Откроется диалоговое окно Login New, вкладка General. Запишем
имя логина Ivanova, выберем SQL Server Authentication, Password: 1, Confirm
password: 1, параметры Default database(База данных по умолчанию) и Default
language (Язык по умолчанию) оставим без изменения.
Установим переключатель Enforce password policy и Enforce password
expiration
26
Enforce password policy (Использовать парольную политики) – позволяет
определить требования к паролям
Enforce password expiration (Включить устаревание пароля) - определяет,
будут ли на логин SQL Server распространяться те же требования по смене
пароля через определенный промежуток времени, что и для учетных записей
Windows.
Далее перейдем на вкладку Status (Состояние) и убедимся в
правильности настроек Permissions to connect to database engine: Grant; Login
enabled.
Далее нажмем OK, автоматически будет создан логин и пользователь
логина Ivanova для базы данных.
Резервное копирование - один из самых надежных способов сохранить и
предохранить свои данные от потери или порчи. Различают три вида
резервного копирования:

полное резервное копирование (full backup);

разностное (differential backup);

резервное копирование журналов транзакций (transaction log backup).
При настройке резервного копирования можно настроить расписание, по
которому будет производиться резервное копирование. Для базы данных
определим следующее расписание: раз в неделю — полное резервное
копирование, раз в неделю - разностное резервное копирование, несколько раз
в день — резервное копирование журналов транзакций, один раз в сутки –
резервное копирование файловых групп.[14]
Создадим полное резервное копирование базы данных, используя
графический интерфейс Management Studio. Откроем окно резервного
копирования из контекстного меню Tasks - Backup для базы данных.
Определим следующие параметры резервного копирования:

Database: - имя базы данных, резервное копирование которой
производим.
27

Recovery model (Режим восстановления): Full - информация о
текущем режиме восстановления базы данных.

Backup type (Тип резервного копирования): Full - тип резервного
копирования.

Backup set name (Имя резервной копии): Library-Full Database Backup
- имя резервной копии

Destination (Назначение) Back up to: Disk, далее выберем Add и
укажем место назначения резервной копии в виде файла на диске.
После этого настроим расписание резервного копирования. Для этого
выберем Script – Script to job, откроется окно New Job. В диалоговом окне New
Job зададим имя Full Back Up Database, затем перейдем на вкладку Schedules и
создадим расписание (рисунок 4).
Рис. 4. Расписание резервного копирования
Далее
нажимаем
ОК,
и
полное
резервное
копирование
будет
обновляться один раз в неделю в воскресенье в полдень.
Аналогичным образом настроим:

разностное резервное копирование с именем Diff Back Up Database,
которое будет обновляться один раз в неделю, в воскресенье в 12.10 PM;
28

резервное копирования журнала транзакций с именем Log Back Up
Database, которое будет обновляться ежедневно в течение каждых 6
часов, начиная с 12:00: PM, заканчивая в 11:59:59 AM;

резервное копирования файловой группы Primary с именем Back Up
FilesgoupPrimary, файловой группы USERS - Back UP FilesgoupUSERSс
расписанием ежедневного обновления в 8:00:00 PM.
В данной главе описано создание пользовательских ролей базы данных;
настройка авторизации пользователей для доступа к базе данных.
Произведен выбор стратегии резервного копирования, произведена
настройка расписания резервного копирования.
29
ЗАКЛЮЧЕНИЕ
В курсовом проекте разработана база данных для автоматизированной
системы управления ООО «Лабиринт-Пост» для автоматизации сервиса
курьерской службы.
Решены следующие задачи курсового проекта:
Проведен системный анализ предметной области курьерской службы
ООО «Лабиринт-Пост».
Описано ведение учета в подразделении занимающемся управлением
сервисом доставки.
Проведено инфологическое (концептуальное) проектирование – процесс
создания внешней (инфологической) модели данных о предметной области, не
зависящее от любых физических аспектов ее представления.
Проведено даталогическое проектирование схемы базы данных, т.е.
такой схемы, в которой отсутствуют нежелательные зависимости между
атрибутами. Последовательно нормализовали схему отношений, тем самым
наложив ограничения и избавляясь от нежелательных зависимостей между
атрибутами.
Построена физическая модель базы данных сервиса службы доставки.
Осуществлена реализация БД на выбранной СУБД MS SQL Server 2019:

проектирование запросов к базе данных для представления данных в
удобном пользователю виде;

созданы хранимые процедуры, функции и триггеры
Описан процесс администрирования пользователей БД и создания
расписания резервного копирования для базы данных.
30
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ
1. Нильceн Пoл, «Microsoft SQL Server 2019. Библия пoльзoватeля», 2019г.
2. Poбepт Виeйpа, «Пpoгpаммиpoваниe баз данных MS SQL Server. Базoвый
кypc», 2020г.
3. Пoл Yилтoн, Джoн Кoлби, «Ввeдeниe в SQL», 2022г.
4. Воронова Л.И. «Лабораторный практикум по дисциплине “базы данных”»
- Москва 2020г.
5. Селевцов, Л.И. Автоматизация технологических процессов: Учебник /
Л.И. Селевцов. - М.: Academia, 2019. - 160 c.
6. Кузнецов, С. Д. Основы баз данных / С.Д. Кузнецов. - М.: Бином.
Лаборатория знаний, Интернет-университет информационных
технологий,
2022. - 488 c.
7. Глушаков, С.В. Базы данных / Д.В. Ломотько. - М.: Харьков: Фолио, 2019.
- 504 c.
8. Яргер, Р.Дж. MySQL и mSQL: Базы данных для небольших предприятий
и Интернета / Р.Дж. Яргер, Дж. Риз, Т. Кинг. - М.: СПб: Символ- Плюс, 2021. 560 c.
9. Смирнова
Н.Н.,
Сорокин
А.А.,
Тельнов
Ю.Ф.
Проектирование
экономических информационных систем [Текст] Москва, 2018
10. Симионов Ю.Ф., Боромотов В.В. Информационный менеджмент. —
Ростов н.Д: Феникс, 2019 г. — 250 с.
11. Чипига А. Ф., Информационная безопасность автоматизированных
систем, М, Издательство: Гелиос АРВ, 2019 г. — 336 с.
12. Электронный
[Электронный
ресурс:
ресурс]
–
Википедия.
Базы
Свободная
данных-
Режим
энциклопедия
доступа
-
https://ru.wikipedia.org/wiki/%D0%91%D0%B0%D0%B7%D0%B0_%D0%B4%D0
%B0%D0%BD%D0%BD%D1%8B%D1%85
31
13. Электронный
информационных
ресурс:
StudFiles
технологий
для
[Электронный
БД
ресурс]
–Режим
–
Обзор
доступа
-
https://studfiles.net/preview/5157336/page:3/
14. Электронный ресурс: Office-menu[Электронный ресурс] – Нормализация
базы данных – Режим доступа - http://office-menu.ru/uroki-sql/51-normalizatsiyabazy-dannykh
15. Флоренция Нами [Электронный ресурс] – Статья «Применение баз
данных
в
современном
мире»
–
Режим
доступа
https://idaten.ru/technology/primenenie-baz-dannih-v-sovremennom-mire
32
-
ПРИЛОЖЕНИЯ
Программный код:
CREATE TABLE
Products (
Product_ID INT PRIMARY KEY,
Name VARCHAR(45) NOT NULL,
Price INT NOT NULL,
Description VARCHAR(45) NULL
);
CREATE SEQUENCE ProductID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
Clients (
Client_ID INT PRIMARY KEY,
Username VARCHAR(20) NOT NULL,
Password_hash VARCHAR(50) NOT NULL,
Name VARCHAR(45) NOT NULL,
Client_Type VARCHAR(20) NOT NULL,
Address VARCHAR(45) NOT NULL,
Contact_number VARCHAR(15) NULL,
Email VARCHAR(45) NULL
);
CREATE SEQUENCE ClientID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
Warehouses (
Warehouse_ID INT PRIMARY KEY,
Address VARCHAR(45) NOT NULL
);
CREATE SEQUENCE WarehouseID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
33
Couriers (
Courier_ID INT PRIMARY KEY,
Username VARCHAR(20) NOT NULL,
Password_hash VARCHAR(50) NOT NULL,
FIO VARCHAR(45) NOT NULL,
Birth_date DATE NOT NULL,
Passport VARCHAR(100) NOT NULL,
Hire_date DATE NOT NULL,
Contact_number VARCHAR(15) NOT NULL
);
CREATE SEQUENCE CourierID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
Self_pickup_points (
Point_ID INT PRIMARY KEY,
Point_address VARCHAR(45) NOT NULL
);
CREATE SEQUENCE PointID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
Managers (
Manager_ID INT PRIMARY KEY,
Username VARCHAR(20) NOT NULL,
Password_hash VARCHAR(50) NOT NULL,
FIO VARCHAR(45) NOT NULL,
Birth_date DATE NOT NULL,
Passport VARCHAR(100) NOT NULL,
Hire_date DATE NOT NULL,
Contact_number VARCHAR(15) NOT NULL
);
CREATE SEQUENCE ManagerID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
Orders (
Order_ID INT PRIMARY KEY,
Client_ID INT NOT NULL,
34
Courier_ID INT NOT NULL,
Delivery_date DATE NULL,
Payment_method VARCHAR(45) NOT NULL,
Self_pickup_ID INT NULL,
Manager_ID INT NOT NULL
);
ALTER TABLE
Orders
ADD
CONSTRAINT fk_clientId FOREIGN KEY (Client_ID)
REFERENCES Clients(Client_ID);
ALTER TABLE
Orders
ADD
CONSTRAINT fk_courierId FOREIGN KEY (Courier_ID)
REFERENCES Couriers(Courier_ID);
ALTER TABLE
Orders
ADD
CONSTRAINT fk_selfpickupId FOREIGN KEY (Self_pickup_ID)
REFERENCES Self_pickup_points(Point_ID);
ALTER TABLE
Orders
ADD
CONSTRAINT fk_managerId FOREIGN KEY (Manager_ID)
REFERENCES Managers(Manager_ID);
CREATE SEQUENCE OrderID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
Position_count (
Position_ID INT PRIMARY KEY,
Product_ID INT NOT NULL,
Order_ID INT NOT NULL,
Amount INT NOT NULL
)
ALTER TABLE
Position_count
ADD
35
CONSTRAINT fk_producId FOREIGN KEY (Product_ID)
REFERENCES Products(Product_ID);
ALTER TABLE
Position_count
ADD
CONSTRAINT fk_orderId FOREIGN KEY (Order_ID) REFERENCES
Orders(Order_ID);
CREATE SEQUENCE PositionID START
WITH
1 INCREMENT BY 1;
CREATE TABLE
Product_to_warehouse (Warehouse_ID INT, Product_ID INT)
ALTER TABLE
Product_to_warehouse
ADD
CONSTRAINT pk_Id PRIMARY KEY (Warehouse_ID,
Product_ID);
ALTER TABLE
Product_to_warehouse
ADD
CONSTRAINT fk_warehouseId FOREIGN KEY (Warehouse_ID)
REFERENCES Warehouses(Warehouse_ID);
ALTER TABLE
Product_to_warehouse
ADD
CONSTRAINT fk_productId FOREIGN KEY (Product_ID)
REFERENCES Products(Product_ID);
INSERT INTO
Products
VALUES
(
ProductID.nextval,
'Шиферные гвозди',
3,
'90мм/5мм/18мм/'
);
INSERT INTO
Products
36
VALUES
(
ProductID.nextval,
'Молоток слесарный',
58,
'Молоток слесарный 100гр с деревянной ручкой'
);
INSERT INTO
Clients
VALUES
(
ClientID.nextval,
'Client1',
'098f6bcd4621d373cade4e832627b4f6',
'Виталий Иванов',
'Физ. лицо',
'Москва',
'88005553535',
'vitalya@ya.ru'
);
INSERT INTO
Clients
VALUES
(
ClientID.nextval,
'Client2',
'ad0234829205b9033196ba818f7a872b',
'ЗАО Строй',
'Юр. лицо',
'Москва',
'89034457812',
'stroy@ya.ru'
);
INSERT INTO
Warehouses
VALUES
(WarehouseID.nextval, 'Москва, ул. Пушкина д.3');
INSERT INTO
Warehouses
VALUES
(
37
WarehouseID.nextval,
'Москва, ул. Академика Королева д.12'
);
INSERT INTO
Couriers
VALUES
(
CourierID.nextval,
'Courier1',
'c79d74a1e537c13ccb4ec1c6266d5a8d',
'Петрова С.А.',
'03/12/1990',
'123456/12',
'13/03/2017',
'89346570173'
);
INSERT INTO
Couriers
VALUES
(
CourierID.nextval,
'Courier2',
'61b49956a9f21a7268ea9d175aa0fc91',
'Иванов К.С.',
'04/03/1995',
'145656/03',
'10/05/2017',
'89346567183'
);
INSERT INTO
Self_pickup_points
VALUES
(PointID.nextval, 'ул.Якиманка д.74');
INSERT INTO
Self_pickup_points
VALUES
(PointID.nextval, 'Мирской проезд д.16');
INSERT INTO
Managers
VALUES
38
(
ManagerID.nextval,
'Manager1',
'c240642ddef994358c96da82c0361a58',
'Васильева К.А.',
'03/11/1980',
'199956/12',
'13/03/2017',
'8934445173'
);
INSERT INTO
Managers
VALUES
(
ManagerID.nextval,
'Manager2',
'8df5127cd164b5bc2d2b78410a7eea0c',
'Пилан И.И.',
'04/04/1987',
'124566/12',
'13/03/2017',
'89346668173'
);
INSERT INTO
Product_to_warehouse
VALUES
(3, 4);
INSERT INTO
Product_to_warehouse
VALUES
(3, 6);
INSERT INTO
Product_to_warehouse
VALUES
(6, 6);
39