"разработка бд" ms sql server

ВведениеВ данном курсовом проекте будет разработана база данных для учета работы городского транспорта.Объектом исследования является процесс разработки базы данных, а предметом курсовой работы является разработка БД «Городской транспорт».Цель курсовой работы – создание БД «Городской транспорт».Для достижения поставленной цели необходимо выполнить следующие задачи:ознакомиться с литературой по данной теме;проанализировать предметную область;построить ER-модель;создать таблицы и установить связи между ними, индексы, триггеры, пользователей с различным уровнем доступа; заполнить таблицы данными;создать хранимые процедуры, UDF, представления.создать автоматические задачи по резервному копированию всей базы и отдельных таблиц с разными параметрами.БД «Городской транспорт» облегчит работу сотрудников транспортного предприятия. В частности, позволит создать централизованное хранилище информации о городском транспорте, его работе и маршрутах.Курсовой проект направлен на приобретение практических навыков проектирования баз данных и разработки программного обеспечения по управлению базами данных с использованием СУБД. Предварительная работаОписание предметной областиГородской транспорт — разновидность пассажирского транспорта как отрасли, предоставляющей услуги по перевозке людей по маршрутам, которые перевозчик заранее устанавливает, доводя до общего сведения способ доставки (транспортное средство), размер и форму оплаты, гарантируя регулярность (повторяемость движения по завершении производственного цикла перевозки), а также неизменяемость маршрута по требованию пассажиров. Транспортные средства, используемые в общественном транспорте:автобусы, в том числе: обычный автобус, электробус, гиробус;трамваи;троллейбусы — оснащён электрическим двигателем с постоянным питанием от контактной сети. В Западной Европе считается подвидом автобуса.поезда (вагоны с локомотивом либо моторвагонные, также разные промежуточные формы (например локомотив с моторными и обычными вагонами)). Назначением базы данных для учета городского транспорта является создание централизованного хранилища информации о городском транспорте, их движении, назначении маршрутов и т.д.Пользователями базы данных являются сотрудники транспортного предприятия, управляющего городским транспортом.База данных должна хранить информацию о:о городском транспорте;о видах транспортах;о маршрутах;о водителях;о назначении транспорта и водителей на маршрут. Обоснование выбора СУБД и программного обеспеченияДля разработки базы данных для ведения учета городского транспорта следует выбрать реляционную модель базы данных, основанную на табличном представлении данных [1].Рассмотрим наиболее популярные СУБД на данный момент:Oracle;PostgreSQL;MS SQL Server.В таблице 1 представлена сравнительная характеристика выбранных СУБД.Таблица 1Сравнительная характеристика СУБДСУБДMS SQL Server Oracle PostgreSQL Вид базы данныхреляционнаяреляционнаяреляционнаяСайтwww.mssqlserver.ruwww.oracle.comwww.postgresql.orgТехническая документацияwww.mssqlserver.ru /reference-manualsdev. oracle.com/docwww.postgresql.org/docs/manualsРазработчикMicrosoftOraclePostgreSQL Global Development Group Текущая версия2017, апрель 20175.7.12, апрель 20179.5.2, март 2017ЛицензияLLCOpen SourceOpen Source ПоддерживаемыеОС на сервереWindowsFreeBSDLinuxOS XSolarisWindowsFreeBSDLinuxNetBSDSolarisUnixWindows Схема БДдададаSQLдададаСпособы доступаC/C++ APIOLE DBADO.NETJDBC ODBCADO.NETJDBCODBCбиблиотека C APIADO.NETJDBCODBCТриггерыдададаНа основе проведенного исследования СУБД, для разработки базы данных «Городской транспорт» выбрана СУБД MS SQL Server.Основным критерием выбора СУБД послужила ее распространенность и наличие бесплатной версии Express. Если в дальнейшем будет необходимость интегрировать разрабатываемое приложение в какую-то систему или же экспортировать накопленную БД в стороннее приложение такой выбор СУБД представляется наиболее удачным, так как СУБД MS SQL Server имеет встроенные механизмы экспорта данных. Описание программных продуктов, используемых при проектировании БДErwin Data ModelerErwin Data Modeler — это компьютерная программа для проектирования и документирования баз данных. Модели данных помогают визуализировать структуру данных, обеспечивая эффективный процесс организации, управления и администрирования таких аспектов деятельности предприятия, как уровень сложности данных, технологий баз данных и среды развертывания. Изначально разработанный компанией Logic Works, erwin был приобретен рядом компаний, прежде чем был выделен частной инвестиционной фирмой Parallax Capital Partners, которая приобрела и объединила его в качестве отдельной организации, erwin, Inc., с генеральным директором Адамом Famularo.Движок программного обеспечения основан на методе IDEF1X, хотя теперь он также поддерживает диаграммы, отображаемые с альтернативной инженерной нотацией для информационных технологий, а также нотацию для размерного моделирования.Microsoft SQL Server 2019 MS SQL Server – реляционная СУБД, разработанная компанией Microsoft.SQL Server выпускается в двух основных редакциях Standard и Enterprise. На основе последней создана также редакция для разработчиков Developer Edition, лицензия на которую позволяет разрабатывать и тестировать системы и приложения [12].Важной особенностью MS SQL Server является поддержка зеркалирования и кластеризации баз данных. В данном контексте кластер сервера представляет собой набор одинаково настроенных серверов. Такой кластер позволяет равномерно распределить нагрузку между серверами, увеличив тем самым производительность работы с базой данных. Все сервера кластера имеют одинаковое виртуально имя. Если один из серверов недоступен, то запрос перенаправляется на другой сервер кластера.На рисунке 1 представлен список служб MS SQL Server [5].Рисунок 1 - Список служб MS SQL ServerMS SQL Server обладает обширными возможностями, которые позволяют сделать установку, развертывание и эксплуатацию СУБД максимально комфортной и простой [5].Установка и обновление СУБД происходит с помощью визуального интерфейса GUI. Программа установки имеет механизмы автоматического определения установки MS SQL Server на компьютер перед установкой чтобы предложить обновление системы или установку нового экземпляра [5].MS SQL Server запускается в виде набора служб, которые представляют собой специальные программы. Для каждой службы создаются ключи в реестре HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Services [5].Для того, чтобы установить MS SQL Server на сервере баз данных необходимо выполнить следующие требования [5]:платформа .NET Framework 3.5 с пакетом обновления 1 (SP1);жесткий диск со свободным местом не менее 6Гб;монитор с разрешением не менее 800x600 пикселей;процессор с тактовой частотой 2,0 ГГц или выше;процессор x64: AMD Opteron, AMD Athlon 64, Intel Xeon с поддержкой Intel EM64T, Intel Pentium IV с поддержкой EM64T;процессор x86: процессор, совместимый с Pentium III или выше;Проектирование базы данныхКонцептуальная модельВ процессе исследования предметной области были выделены следующие сущности [2]:транспорт;тип транспорта;водитель;маршрут;остановки;тип движения по маршруту;назначение маршрута.Концептуальная модель базы данных представлена на рисунке 2.Рисунок 2 - Концептуальная модель базы данных 7239021717000Каждый транспорт имеет определенный тип транспорта. К одному типов транспортов относится много транспортаКаждый транспорт может иметь несколько назначенных маршрутов, которые последовательно сменяют друг друга по датам назначения и снятия маршрута.Маршрут имеет несколько остановок. Каждый маршрут имеет тип движения по маршруту.Каждый водитель может быть последовательно назначен на любой маршрут.Логическая модель базы данныхЛогическая (даталогическая) модель представляет собой модель базы данных, которая не привязана к конкретной СУБД. В ней выделяют основные объекты БД и определяют связи между этими объектами. Иногда определятся типы данных отдельных объектов. Данная модель построена методом Сущность-связь (Entity Relationship) [3].Логическая модель представлена на рисунке 3. Рисунок 3 Логическая модель базы данных Создание физической модели предметной областиФизическая модель содержит всю информацию, необходимую для реализации конкретной БД [4]. Физическая модель – логическая модель базы данных, выраженная в терминах языка описания данных конкретной СУБД.Физическая модель базы данных содержит все детали, необходимые конкретной СУБД для создания базы: наименования таблиц и столбцов, типы данных, определения первичных и внешних ключей и т.п.Физическая модель строится на основе логической с учетом ограничений, накладываемых возможностями выбранной СУБД (в данном случае - MS SQL Server)[4].Физическая модель БД «Городской транспорт» представлена на рисунке 4 [4].Рисунок 4 - Физическая модель предметной областиВ таблице 1 представлена структура таблицы «Транспорт».Таблица 1 – Структура таблицы «Транспорт»№ п/пНаименование поляСпецификация данныхИмя поляТип данныхКлючОграничения целостности1ИдентификаторРегистрационный номерintPNot null2Идентификатор типа транспортаКод типа транспортаintFNot null3Количество местКоличество местint-4МаркаМаркаVarchar (100)-5Год выпускаГод выпускаint-В таблице 2 представлена структура таблицы «Водитель».Таблица 2 – Структура таблицы «Водитель»№ п/пНаименование поляСпецификация данныхИмя поляТип данныхКлючОграничения целостности1ИдентификаторКод водителяintPNot null2ФИОФИОVarchar (50)Not null3ТелефонТелефонVarchar (50)-4АдресАдресVarchar (150)-5Паспортные данныеПаспортные данныеVarchar (150)-В таблице 3 представлена структура таблицы «Назначение маршрута».Таблица 3 – Структура таблицы «Назначение маршрута»№ п/пНаименование поляСпецификация данныхИмя поляТип данныхКлючОграничения целостности1ИдентификаторКод назначенияintPNot null2Дата назначения Дата назначения DatetimeNot null3Дата отменыДата отменыDatetime-4Регистрационный номерРегистрационный номерVarchar (20)FNot null5Номер маршрутаНомер маршрутаintFNot null6Идентификатор водителяКод водителяintFNot nullВ таблице 4 представлена структура таблицы «Маршрут».Таблица 4 – Структура таблицы «Маршрут»№ п/пНаименование поляСпецификация данныхИмя поляТип данныхКлючОграничения целостности1ИдентификаторНомер маршрутаintPNot null2Наименование маршрута Наименование маршрута Varchar (100)Not null3Код типа движения по маршрутуКод типа движения по маршрутуintNot nullВ таблице 5 представлена структура таблицы «Тип движения по маршруту».Таблица 5 – Структура таблицы «Тип движения по маршруту»№ п/пНаименование поляСпецификация данныхИмя поляТип данныхКлючОграничения целостности1ИдентификаторКод типа движенияintPNot null2Наименование типа движения по маршруту Наименование типа движения по маршрутуVarchar (50)Not nullВ таблице 6 представлена структура таблицы «Остановки».Таблица 6 – Структура таблицы «Остановки»№ п/пНаименование поляСпецификация данныхИмя поляТип данныхКлючОграничения целостности1ИдентификаторКод остановкиintPNot null2Номер маршрутаНомер маршрутаintNot null3Наименование остановокНаименование остановокVarchar (50)4Время прибытияВремя прибытияTime5Время отправленияВремя отправленияTimeВ таблице 7 представлена структура таблицы «Тип транспорта».Таблица 7 – Структура таблицы «Тип транспорта»№ п/пНаименование поляСпецификация данныхИмя поляТип данныхКлючОграничения целостности1ИдентификаторКод типа транспортаintPNot null2НаименованиеНаименованиеVarchar (50)Not nullСоздание базы данных в MS SQL ServerЗапускаем «MS SQL Server 2019». В панели «Обозреватель объектов» щелчком правой кнопки мыши на объекте «Базы данных» вызываем контекстное меню. В появившемся списке выбираем «Создать Базу данных» (рисунок 5).Рисунок 5 - Создание БД В появившемся окне в строке «Имя базы данных» задаем имя «Transport» (рисунок 6).Рисунок 6 - Окно «Создание базы данных»Создание таблицНа рисунках 7-13 представлена структура созданных таблиц БД.Рисунок 7 – Структура таблицы «Водитель»Рисунок 8 – Структура таблицы «Остановки»Рисунок 9 – Структура таблицы «Назначение маршрута»Рисунок 10 – Структура таблицы «Тип движения маршрута»Рисунок 11 – Структура таблицы «Маршрут»Рисунок 12 – Структура таблицы «Тип транспорта»Рисунок 13 – Структура таблицы «Транспорт»Нормализация отношенийНормализация — это процесс организации данных в базе данных, включающий создание таблиц и установление отношений между ними в соответствии с правилами, которые обеспечивают защиту данных и делают базу данных более гибкой, устраняя избыточность и несогласованные зависимости [2].Принято считать, что наиболее подходящей формой нормализации, которая обеспечивает целостность и непротиворечивость данных, является третья нормальная форма (3НФ). Рассмотрим 1НФ, 2НФ и 3НФ более подробно.Первая нормальная формаДля того чтобы привести базу данных к первой нормальной форме необходимо устранить повторяющиеся группы данных в таблицах БД, при этом создав отдельную таблицу для каждой группы данных. Далее следует создать первичный ключ и идентифицировать каждую таблицу [2].Вторая нормальная формаВторая нормальная форма отличается от первой нормальной форму наличием внешних ключей, которые связывают поля разных таблиц [2].Третья нормальная формаЧтобы привести базу данных к третьей нормальной форме необходимо устранить поля, не зависящие от ключа [2]. Если значение поля или группы полей может относиться к нескольким таблицам, необходимо выделить их в отдельную таблицу.Тем не менее, привести таблицы базы данных к третьей нормальной форме не всегда возможно и нужно.Согласно определению третьей нормальной формы, нужно создавать отдельные таблицы для хранения городов, индексов и т.д. Но увеличение количества мелких таблиц уменьшит производительность базы данных и приведет к исчерпанию объема памяти.Все связи в созданной схеме базы данных имеют кратность «один-ко-многим», что свидетельствует о том, что схема базы данных нормализована до 3НФ.Создание связей Для создания связей таблиц необходимо щелкнуть правой кнопкой мыши по пункту «Диаграммы баз данных» и выбрать пункт «Создать диаграмму базы данных», после чего выбрать все таблицы, нажав кнопку «Добавить». Затем нужно произвести связывание таблиц по ключевым полям (рисунок 14). Рисунок 14 - Связи таблицСоздание триггеровТриггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.Триггеры «INSERT» запускаются при каждой попытке создать новую запись в таблице с помощью команды «INSERT». При попытке вставить новую запись в таблицу SQL Server копирует эту запись в таблицу триггеров БД и специальную таблицу inserted.Для демонстрации работы триггера «INSERT» была использована таблица «Назначение маршрута». Триггер запрещает внесение нового назначения на маршрут, если предыдущий маршрут для назначаемого транспорта не закончен (рисунок 15). Рисунок 15 - Триггер «INSERT»Триггеры «DELETE» используются для предотвращения удаления данных пользователями из БД. При наличии триггера Delete SQL Server переносит удаляемую запись в логическую таблицу «deleted», т.е. записи не исчезают полностью, и на них можно ссылаться в коде.Создание пользователейДля эффективного управления и обеспечения безопасности БД в SQL сервере можно создавать пользователей с различными правами доступа и с функциями.Для БД «Городской транспорт» выделим две категории пользователей:администратор – имеет полные права на работу с системой;диспетчер транспортной компании - ведет учет назначения транспортных средств на маршруты.На рисунке 16 представлена форма для создания роли пользователя для администрирования базы данных. Администратор имеет полный доступ к данным.Рисунок 16 – Создание роли пользователя «Администратор»Рисунок 17 – Создание роли пользователя «Диспетчер»Также были созданы пользователи для ролей администратора и диспетчера (рисунок 18 и 19).Рисунок 18 – Создание пользователя для роли администратораРисунок 19 – Создание пользователя для роли администратораСоздание резервного копированияДля создания резервного копирования необходимо выбрать в меню «Создать резервную копию» (рисунок 20).Рисунок 20 – Создание резервной копииНастройка резервного копирования представлена на рисунке 21.Рисунок 21 – Настройка резервного копированияРисунок 22 – Настройка резервного копированияРисунок 23 – Настройка резервного копированияОписание языка T-SQLSQL–запросыПосле наполнения базы данных были созданы сдедующие запросы.Список действующих маршрутов:Рисунок 24 – Запрос «Активные маршруты»Количество отработанных маршрутов каждым транспортом:Рисунок 25 – Запрос «Количество назначений»Создание представленийSQL-представление (SQL view) — это виртуальная таблица, составленная из других таблиц или представлений. Представление не имеет своих собственных данных, а объединяет данные из таблиц или представлений, которые в него входят. Представления создаются с помощью операторов SELECT. На рисунке 26 представлено представление для оценки длительности маршрута.Рисунок 26 – Оценка длительности маршрутаSELECT dbo.Маршрут.[Номер маршрута], dbo.Маршрут.[Наименование маршрута], dbo.[Тип движения по маршруту].[Наименование типа движения], COUNT(dbo.Остановки.[Код остановки]) AS [Количество остановок], MIN(dbo.Остановки.[Время отправления]) AS [Начало маршрута], MAX(dbo.Остановки.[Время прибытия]) AS [Окончание маршрута], DATEDIFF(hh, MIN(dbo.Остановки.[Время прибытия]), MAX(dbo.Остановки.[Время отправления])) AS [Длительность маршрута]FROM dbo.Маршрут INNER JOIN dbo.Остановки ON dbo.Маршрут.[Номер маршрута] = dbo.Остановки.[Номер маршрута] INNER JOIN dbo.[Тип движения по маршруту] ON dbo.Маршрут.[Код типа движения по маршруту] = dbo.[Тип движения по маршруту].[Код типа движения по маршруту]GROUP BY dbo.Маршрут.[Номер маршрута], dbo.Маршрут.[Наименование маршрута], dbo.[Тип движения по маршруту].[Наименование типа движения]На рисунке 27 представлена форма представления для оценки работы водителей с разбивкой по месяцам.Рисунок 27 – Оценка работы водителей с разбивкой по месяцамSELECT YEAR(dbo.[Назначение маршрута].[Дата назначения]) AS Год, MONTH(dbo.[Назначение маршрута].[Дата назначения]) AS Месяц, dbo.Водитель.ФИО, COUNT(dbo.[Назначение маршрута].[Код назначения]) AS [Количество маршрутов]FROM dbo.Водитель INNER JOIN dbo.[Назначение маршрута] ON dbo.Водитель.[Код водителя] = dbo.[Назначение маршрута].[Код водителя]GROUP BY YEAR(dbo.[Назначение маршрута].[Дата назначения]), MONTH(dbo.[Назначение маршрута].[Дата назначения]), dbo.Водитель.ФИОСоздание UDF – функцииПользовательская скалярная функция возвращает в качестве ответа единственное значение при каждом вызове функции. Функции являются скалярнозначными, если предложение RETURNS определяет один из скалярных типов данных. Скалярнозначные функции могут быть определены с помощью нескольких операторов Transact-SQL. Создадим скалярную функцию, которая будет выводить количество остановок выбранного маршрута: Рисунок 28 - Скалярная функция «Количество остановок выбранного маршрута»Вызвать данную функцию можно таким образом (рисунок 29):Рисунок 29 – Вызов функцииМногооператорная функция - возвращает таблицу созданную одним или несколькими операторами Transact-SQL, чем напоминает хранимые процедуры. В отличие от процедур, на такие функции можно ссылаться в WHERE как на объект просмотра. Создадим функцию, которая выведет список всех транспортных средств с указанием их типа (рисунок 30). Рисунок 30 - Многооператорная функция «Список всех транспортных средств с указанием их типа»Вызвать данную функцию можно таким образом (рисунок 31):Рисунок 31 – Вызов функцииЭкспорт БД в MS ExсelДля того чтобы произвести экспорт всей базы данных используем Мастер импорта и экспорта SQL Server (рисунок 32)Рисунок 32 - Вызов Мастера импорта и экспорта SQL ServerВ появившемся окне жмем далее, и в следующем окне выбираем источник данных (рисунок 33).Рисунок 33 - Выбор источника данныхВ следующем окне выбираем назначение данных. В нашем случае это MS Excel. И выбираем путь до файла (рисунок 34).Рисунок 34 - Выбор назначения данных MS ExcelДалее выбираем тип экспорта (рисунок 35):Рисунок 35 – Указание типа экспортаВыбираем экспортируемые объекты (рисунок 36).Рисунок 36 – Экспортируемые объектыРезультаты экспорта представлены на рисунке 37.Рисунок 37 - Результаты экспортаНа рисунке 38 представлен экспортированный файл.Рисунок 38 – Экспортированный файлЗаключениеВ ходе выполнения курсовой работы была создана база данных «Городской транспорт», направленная на облегчения работы сотрудников транспортного транспорта.Исследована предметная область. Подробно описана постановка задачи курсового проекта. Определены основные моменты, которые должны быть учтены при проектировании базы данных.Рассмотрен список выделенных сущностей и их атрибутов. Создана модель «Сущность-связь».В состав базы данных входят таблицы, между которыми определена связь. Рассмотрены функциональные связи между атрибутами выделенных сущностями, проведена нормализация базы данных к 3НФ.Создана инфологическая и даталогическая модели БД.Для проектирования базы данных была выбрана СУБД MS SQL Server.Созданы основные запросы к базе данных. Рассмотрены механизмы защиты данных и требования к техническому обеспечению.Рассмотрена инструкция по использованию базы данных.В ходе выполнения курсового проекта были приобретены устойчивые навыки проектирования баз данных и разработки запросов. Полученные знания могут быть использованы при разработке более серьезных коммерческих проектов.Список используемой литературыКоротков Э.М. Разработка баз данных: учебник. - 2-е изд. – М.: ИНФРА - М, 2019. – 398 с.Кузин А. В. Базы данных: учеб. Пособие для студентов ВУЗов / А. В. Кузин, С. В. Левонисова. – 5-е изд., испр. – М.: издательский центр «Академия», 2020. – 320 с.Кузнецов С. Д. Базы данных: учебник для студентов учреждений высшего профессионального образования / С. Д. Кузнецов – – М.: издательский центр «Академия», 2019. – 496 с.Мирошниченко Г. А. Реляционные базы данных: практические приемы оптимальных решений. – СПб.: БХВ-Петербург, 2015. – 400 с.Мухин К.А. Проектирование баз данных. SQL Server, М.: Горячая линия – Телеком, 2017. – 240 с.