Реализация базы данных 'Государственной автоинспекции'
ОГЛАВЛЕНИЕ
ВВЕДЕНИЕ.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
.1 Основные
понятия и определения
.1.1 Типы
моделей баз данных
.1.2
Иерархическая модель
.1.3 Сетевая
модель
.1.4
Табличная или реляционная модель
.1.5 Понятие
первичного ключа
.1.6
Реляционные отношения между таблицами базы данных
.1.7
Ссылочная целостность и каскадные воздействия
.1.8 Понятие
внешнего ключа
.1.9 Индексы
и методы доступа
.1.10
Нормализация таблиц при проектировании БД
.2 Выбор
СУБД. ПРОЕКТНАЯ ЧАСТЬ
.1
Концептуальная модель предметной области
.2 Логическая
модель базы данных
.3 Физическая
модель базы данных. ПРОГРАММНАЯ ЧАСТЬ
.1 Разработка
запросов на языке t-sql
.2 Разработка
хранимых процедур
.3 Разработка
триггеров
ЗАКЛЮЧЕНИЕ
ИСТОЧНИКИ И
ЛИТЕРАТУРА
ПРИЛОЖЕНИЯ
ВВЕДЕНИЕ
Базы данных стали основой информационных систем и в корне изменили методы
работы многих организаций. В частности, в последние годы развитие технологии
баз данных привело к созданию весьма мощных и удобных в эксплуатации систем.
Благодаря этому системы баз данных стали доступными широкому кругу
пользователей. Но, к сожалению, кажущаяся простота таких систем способствовала
тому, что пользователи стали самостоятельно создавать базы данных и приложения,
не имея достаточных знаний о методах проектирования эффективно работающих
систем, что часто приводило к непроизводительным затратам ресурсов и
некачественным результатам. Вызванная этим неудовлетворенность пользователей
стала причиной возникновения известного "кризиса программного
обеспечения", или так называемой "депрессии программного обеспечения",
последствия которой не устранены и поныне.
В широком смысле слова база данных (БД) - это совокупность сведений о
конкретных объектах реального мира в какой-либо предметной области.
Реляционная база данных - это совокупность отношений, содержащих всю
информацию, которая должна храниться в БД. Пользователи воспринимают такую базу
данных как совокупность таблиц, связанных между собой. Система управления
базами данных - это комплекс программных и языковых средств, необходимых для
создания, обработки баз данных и поддержания их в актуальном состоянии.
Целью курсовой работы является получение и углубление знаний в
проектировании структуры базы данных, разработке и реализации модели базы
данных, а также в приобретении практических навыков проектирования реляционной
базы данных. Задачей курсовой работы является разработка и реализация базы
данных информационной системы“ГАИ” (Государственной авто инспекции) средствами
СУБД Microsoft SQL Server 2008.
Система создаётся для обслуживания следующих групп пользователей:
. Сотрудники ГАИ;
. Владельцы автомобилей.
Функциональные возможности:
. ведение базы данных (запись, чтение, модификация, удаление);
. реализация хранимых процедур и триггеров для поддержания сложных
ограничений целостности в базе данных;
. реализация наиболее часто встречающихся запросов и представлений
для пользователей в готовом виде.
I. Теоретическая часть
1.1 Основные
понятия и определения
Любой из нас, начиная с раннего детства, многократно
сталкивался с «базами данных». Это - всевозможные справочники (например,
телефонный), энциклопедии и т. п. Записная книжка - это тоже «база данных»,
которая есть у каждого из нас.
Базы данных представляют собой информационные модели,
содержащие данные об объектах и их свойствах. Базы данных хранят информацию о
группах объектов с одинаковым набором свойств.
Например, база данных «Записная книжка» хранит
информацию о людях, каждый из которых имеет фамилию, имя, телефон и так далее.
Библиотечный каталог хранит информацию о книгах, каждая из которых имеет
название, автора, год издания и так далее.
Информация в базах данных хранится в упорядоченном
виде. Так, в записной книжке все записи упорядочены по - алфавиту, а в
библиотечном каталоге - либо по алфавиту - алфавитный каталог), либо по области
знания (предметный каталог).
Информационная система - это совокупность программно-аппаратных средств,
способов и людей, которые обеспечивают сбор, хранение, обработку и выдачу
информации для решения поставленных задач. На ранних стадиях использования
информационных систем применялась файловая модель обработки. В дальнейшем в
информационных системах стали применяться базы данных. Базы данных являются
современной формой организации, хранения и доступа к информации. Примерами
крупных информационных систем являются банковские системы, системы заказов
железнодорожных билетов и т.д.
База данных - это интегрированная совокупность структурированных и
взаимосвязанных данных, организованная по определенным правилам, которые
предусматривают общие принципы описания, хранения и обработки данных. Обычно
база данных создается для предметной области.
Предметная область - это часть реального мира, подлежащая изучению с
целью создания базы данных для автоматизации процесса управления.
Система управления базами данных (СУБД) - комплекс программных средств
для создания баз данных, хранения и поиска в них необходимой информации.
Моделью называется некий объект-заменитель, который в определенных
условиях может заменить объект-оригинал, воспроизведя интересующие на свойства
и характеристики оригинала, причем имеет существенные преимущества, удобства
(наглядность, доступность испытаний, легко изменяются и т.д.) Т.е. МОДЕЛЬ - это
некоторое упрощенное подобие реального объекта.
Информационная модель - это информация (знания,
сведения) о реальном объекте, процессе, явлении.
Информация, отражающая существенные признака объекта,
процесса или явления и хранящаяся в памяти ПК, представляет собой компьютерную
информационную модель (КИМ).
1.1.1 Типы
моделей баз данных
Существует несколько различных структур информационных
моделей и соответственно различных типов баз данных: табличная, сетевая,
иерархическая.
Рис. 1 Типы моделей баз данных
1.1.2 Иерархическая
модель
Иерархические базы данных графически могут быть представлены
как перевернутое дерево, состоящее из объектов различных уровней. Верхний
уровень (корень дерева) занимает один объект, второй - объекты второго уровня и
так далее.
Между объектами существуют связи, каждый объект может
включать в себя несколько объектов более низкого уровня. Такие объекты
находятся в отношении предка (объект, более близкий к корню) к потомку (объект
более низкого уровня), при этом объект-предок может не иметь потомков или иметь
их несколько, тогда как объект-потомок обязательно имеет только одного предка.
Объекты, имеющие общего предка, называются близнецами.
Пример - структура школы (Рис 2.):
Рис. 2 Структура школы
1.1.3 Сетевая
модель
Сетевая база данных является обобщением иерархической
за счет допущения объектов, имеющих более одного предка. Вообще, на связи между
объектами в сетевых моделях не накладывается никаких ограничений. Сетевая база
данных является обобщением иерархической за счет допущения объектов, имеющих
более одного предка. Вообще, на связи между объектами в сетевых моделях не
накладывается никаких ограничений.
Пример: посещение учащимися одной группы спортивных
секций (Рис. 3).
Рис. 3 Посещение учащимися одной группы спортивных
секций
1.1.4 Табличная
или реляционная модель
В настоящее время наибольшее распространение при
разработке БД получили реляционные модели данных. Реляционная модель данных
является совокупностью простейших двумерных таблиц -отношений (англ.
relation),т.е. простейшая двумерная таблица определяется как отношение
(множество однотипных записей, объединенных одной темой).
Реляционные БД представляют связанную между собой совокупность
таблиц-сущностей базы данных (ТБД). Связь между таблицами может находить свое
отражение в структуре данных, а может только подразумеваться, то есть
присутствовать на неформализованном уровне.
Каждая таблица БД представляется как совокупность строк и столбцов, где
строки соответствуют экземпляру объекта, конкретному событию или явлению, а
столбцы - атрибутам (признакам, характеристикам, параметрам) объекта, события,
явления. При практической разработке БД таблицы-сущности зовутся таблицами,
строки-экземпляры - записями, столбцы-атрибуты - полями ТБД. Одно из важнейших
достоинств реляционных баз данных состоит в том, что можно хранить логически
сгруппированные данные в разных таблицах и задавать связи между ними, объединяя
их в единую базу. Такая организация данных позволяет уменьшить избыточность
хранимых данных, упрощает их ввод и организацию запросов и отчетов.
1.1.5 Понятие
первичного ключа
В каждой таблице БД может существовать первичный ключ. Под первичным
ключом понимают поле или набор полей, однозначно (уникально) идентифицирующих
запись. Первичный ключ должен быть минимально достаточным: в нем не должно быть
полей, удаление которых из первичного ключа не отразится на его уникальности.
Таблица 1. Преподаватель
|
Таб. №
|
ФИО
|
Уч. степень
|
Уч. звание
|
Код кафедры
|
|
101
|
Андреев А.П.
|
Д-р техн. наук
|
Профессор
|
01
|
|
102
|
Апухтин И.С.
|
Канд. техн. наук
|
Доцент
|
01
|
|
103
|
Глухов И.Л.
|
Канд. техн. наук
|
Доцент
|
01
|
|
104
|
Сеченов Ю.Б.
|
Канд. техн. наук
|
Доцент
|
01
|
В качестве первичного ключа в таблице «Преподаватель» (Табл.1) может
выступать только «Таб. №», значения других полей могут повторяться внутри
данной таблицы.
1.1.6 Реляционные
отношения между таблицами базы данных
Между двумя или более таблицами базы данных могут существовать отношения
подчиненности. Отношения подчиненности определяют, что для каждой записи
главной таблицы {master,называемой
еще родительской} может существовать одна или несколько записей в подчиненной
таблице {detail, называемой еще дочерней}.
Существует три разновидности связей между таблицами базы данных:
· «один-ко-многим»,
· «один-к-одному»,
· «многие-ко-многим».
Отношение «один-ко-многим» имеет место, когда одной записи родительской
таблицы может соответствовать несколько записей в дочерней таблице.
Связь "один-ко-многим" является самой распространенной для
реляционных баз данных.
В широко распространенной нотации структуры баз данных IDEF1X отношение «один-ко-многим» изображается путем соединения
таблиц линией, которая на стороне дочерней таблицы оканчивается кружком или
иным символом. Поля, входящие в первичный ключ для данной ТБД, всегда
расположены вверху и отчеркнуты от прочих полей линией (Рис. 4).
Рис. 4 Отношение «один-ко-многим»
Отношение «один-к-одному» имеет место, когда одной записи в родительской
таблице соответствует одна запись в дочерней таблице (Рис. 5).
Рис. 5 Отношение «один-к-одному»
Данное отношение используют, если не хотят, чтобы таблица БД «не
распухала» от второстепенной информации.
Отношение «многие-ко-многим» имеет место, когда:
а) записи в родительской таблице может соответствовать больше одной
записи в дочерней таблице;
б) записи в дочерней таблице может соответствовать больше одной записи в
родительской таблице.
Например, каждой студент изучает несколько дисциплин. Каждая дисциплина
изучается несколькими студентами (Рис. 6).
Рис. 6 Отношение «многие-ко-многим»
1.1.7 Ссылочная
целостность и каскадные воздействия
Рассмотрим наиболее часто встречающуюся в базах данных связь
«один-ко-многим». Как можно заметить, дочерняя и родительская таблицы связаны
между собой по общему полю «Шифр группы» (Рис. 7). Назовем это поле полем
связи.
Рис. 7 Связь «один-ко-многим»
Возможны два вида изменений, которые приведут к утере связей между
записями в родительской и дочерней таблицах (Рис. 8):
· изменение значения поля связи в записи родительской таблицы
без изменения значений полей связи в соответствующих записях дочерней таблицы;
· изменение значения поля связи в одной из записей дочерней
таблицы без соответствующего изменения значения полей связи в родительской и
дочерней таблицах.
Рис. 8Утеря связей между записями
И в первом, и втором случаях наблюдается нарушение целостности базы
данных, поскольку информация в ней становится недостоверной. Следовательно,
нужно блокировать действия, которые нарушают целостность связей между
таблицами, которую называют ссылочной целостностью.
Чтобы предотвратить потерю ссылочной целостности, используется механизм
каскадных изменений. Он состоит в обеспечении следующих требований:
· необходимо запретить изменение поля связи в записи дочерней
таблицы без синхронного изменения полей связи в родительской таблице;
· при изменении поля связи в записи родительской таблице,
следует синхронно изменить значения полей связи в соответствующих записях
дочерней таблицы;
· при удалении записи в родительской таблице, следует удалить
соответствующие записи в дочерней таблице.
Необходимость разрешения или запрещения каскадных изменений обычно
реализуется в СУБД при определении связей между таблицами. Собственно, таким
образом, и происходит создание ссылочной целостности.
1.1.8 Понятие
внешнего ключа
Для обеспечения ссылочной целостности в дочерней таблице создается
внешний ключ. Во внешний ключ входят поля связи дочерней таблицы. Для связей
типа "один-ко-многим" внешний ключ по составу полей должен совпадать
с первичным ключом родительской таблицы.
1.1.9 Индексы
и методы доступа
Индексы представляют собой механизмы быстрого доступа к данным в таблицах
БД.
Сущность индексов состоит в том, что они хранят значения индексных поле
(т.е. полей, по которым построен индекс) и указатель на запись в таблице.
При последовательном методе доступа для выполнения запроса к таблице БД
просматриваются все записи таблицы, от первой до последней.
При индексно-последовательном методе доступа для выполнения запроса к
таблице БД указатель в индексе устанавливается на первую строку,
удовлетворяющую условию запроса (или его части), и считывается запись из
таблицы по хранящемуся на нее в индексе указателю.
Определение первичных и внешних ключей таблиц БД приводят к созданию
индексов по полям, объявленным в составе первичных или внешних ключей.
1.1.10 Нормализация
таблиц при проектировании БД
При проектировании структуры новой БД определяют сущности (объекты,
явления) предметной области, которые должны найти свое отражение в базе данных.
В конечном итоге анализ предметной области должен привести к созданию эскиза
БД. Сначала желательно изобразить сущности и связи между ними. Как правило,
каждой сущности в БД соответствует таблица. Затем - в эскизе второго порядка -
для каждой таблицы БД приводится список атрибутов - полей записи.
На этом этапе процесс проектирования структур БД является процессом
творческим, неоднозначным, с другой стороны, узловые его моменты могут быть
формализованы.
Одной из таких формализаций является требование, согласно которому
реляционная база данных должна быть нормализована. Процесс нормализации имеет
своей целью устранение избыточности данных и заключается в приведении к третьей
нормальной форме (3НФ).
Первая нормальная форма (1НФ) требует, чтобы каждое поле таблицы БД:
· было неделимым;
· не содержало повторяющихся групп.
Неделимость поля означает, что значение поля не должно делиться на более
мелкие значения. Например, если в поле "Подразделение" содержится
название факультета и название кафедры, требование неделимости не соблюдается и
необходимо из данного поля выделить или название факультета, или кафедры в
отдельное поле.
Повторяющимися являются поля, содержащие одинаковые по смыслу значения.
Например, если требуется получить статистику сдачи экзаменов по предметам,
можно создать поля для хранения данных об оценке по каждому предмету. Однако в
этом случае мы имеем дело с повторяющимися группами.
Вторая нормальная форма (2НФ) требует, чтобы все поля таблицы зависели от
первичного ключа, то есть, чтобы первичный ключ однозначно определял запись и
не был избыточен. Те поля, которые зависят только от части первичного ключа,
должны быть выделены в составе отдельных таблиц.
Третья нормальная форма (ЗНФ) требует, чтобы значение любого поля
таблицы, не входящего в первичный ключ, не зависело от значения другого поля,
не входящего в первичный ключ.
Для решения задачи данной курсовой работы была разработана и реализована
реляционная база данных.
1.2 Выбор
СУБД
Для разработки и реализации базы данных информационной системы “ГАИ”
(Государственной авто инспекции) использовалась СУБД Microsoft SQL Server
2008.SQL Server - система управления реляционными базами данных (СУРБД),
разработанная корпорацией Microsoft. Основной используемый язык запросов -
Transact - SQL, создан совместно Microsoft и Sybase. Transact - SQL является
реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с
расширениями. Это надежная, производительная и интеллектуальная платформа
данных, способная отвечать нуждам наиболее ресурсоемких бизнес-приложений. Она
позволяет сократить время и издержки на разработку и сопровождение приложений,
а также предоставлять практически применимую информацию на каждое рабочее место
предприятия.
Система SQL Server 2008 отталкивается от концепции
платформы данных Майкрософт: она упрощает управление любыми данными в любом
месте и в любой момент времени. Она позволяет хранить в базах данных
информацию, полученную из структурированных, полуструктурированных и
неструктурированных источников, таких как изображения и музыка. В SQL Server
2008 имеется большой набор интегрированных служб, расширяющих возможности
использования данных: можно составлять запросы, выполнять поиск, проводить
синхронизацию, делать отчеты, анализировать данные. Все данные хранятся на
основных серверах, входящих в состав центра обработки данных. К ним
осуществляется доступ с настольных компьютеров и мобильных устройств. Таким
образом, можно полностью контролировать данные независимо от того, где они
сохранены.
Система SQL Server 2008 позволяет обращаться к данным
из любого приложения, разработанного с применением технологий Microsoft .NET и
VisualStudio, а также в пределах сервисно - ориентированной архитектуры и
бизнес-процессов - через MicrosoftBizTalkServer. Сотрудники, отвечающие за сбор
и анализ информации, могут работать с данными, не покидая привычных приложений,
которыми они пользуются каждый день, например, приложений выпуска 2007 системы
MicrosoftOffice. SQL Server 2008 позволяет создать надежную, производительную,
интеллектуальную платформу, отвечающую всем требованиям по работе с данными.SQL
(T-SQL) - процедурное расширение языка SQL, созданное компанией Microsoft (для
Microsoft SQL Server) и Sybase (для Sybase ASE).был расширен такими дополнительными
возможностями как:
· управляющие операторы,
· локальные и глобальные переменные,
· различные дополнительные функции для
обработки строк, дат, математики и т. п.,
· поддержка аутентификации
MicrosoftWindows
Язык Transact-SQL является ключом к использованию MS
SQL Server. Все приложения, взаимодействующие с экземпляром MS SQL Server,
независимо от их реализации и пользовательского интерфейса, отправляют серверу
инструкции Transact-SQL.
II. Проектная часть
.1 Концептуальная
модель предметной области
База данных создаётся для информационного обслуживания зарегистрированных
водителей и автомобилей. БД должна содержать данные о зарегистрированных
автомобилях, в том числе угнанные машины, а также, данные о владельцах. В
соответствии с предметной областью система строится с учётом следующих
особенностей: Каждый автомобиль имеет свой уникальный номер; Каждый автомобиль
должен иметь дату регистрации, госномер, цвет; На каждого водителя создается
запись с его данными.
Задачи, решаемые с помощью запросов на выборку:
1.Показать коды всех белых автомобилей
.Показать все немецкие марки автомобилей
.Показать всех водителей с фамилией Гришин (GRISHIN)
4.Показать информацию по белым автомобилям, владельцами которых являются
водители категории В
5.Показать все угнанные автомобили, выпущенные после 10.10.1991
.Показать все угнанные после 01.01.2003 автомобили
.Показать все угнанные после 01.01.2003 белые автомобили
.Показать все угнанные у водителей категории В после 01.01.2003
автомобили
.Показать всех водителей AUDI
.Показать всех водителей белых AUDI
2.2 Логическая
модель базы данных
Выделим базовые сущности этой предметной области: Автомобили - это связь
между водителями и кодами марки. Атрибуты: код автомобиля, код владельца, код
марки, госномер, номер кузова, номер двигателя, номер техпаспорта, дата
выпуска, дата регистрации, цвет. Угнанные автомобили. Атрибуты: код заявки,
дата угона, дата подачи заявления, код автомобиля, код владельца,
обстоятельства угона, отметка о нахождении, дата нахождения.
Владельцы. Атрибуты: код владельца, ФИО, дата рождения, адрес, номер
паспорта, номер прав, дата выдачи прав, категория.
Марки. Атрибуты: код марки, название, код фирмы, код страны.
Фирмы. Атрибуты: код фирмы, название.
Страны. Атрибуты: код страны, название.
Логическая модель базы данных представлена на рисунке 9.
Рис. 9 Логическая модель базы данных
база
реляционный ссылочный триггер
2.3 Физическая
модель базы данных
Основные ограничения целостности:
.Текстовые поля могут содержать только символы русского алфавита, цифры и
знаки препинания.
.В отношении FIRM порядковые
номера фирм могут начинаться с 1 и не должны превышать число 9999, фирма не
может не иметь названия.
.В отношении COUNTRY порядковые
номера стран могут начинаться с 1 и не должны превышать число 9999, страна не
может не иметь названия.
.В отношении AM_MARK порядковые номера марок автомобилей
могут начинаться с 1 и не должны превышать число 9999, марка автомобиля не
может не иметь названия.
.В отношении DRIVERS порядковые
номера водителей могут начинаться с 1 и не должны превышать число 9999,
водитель не может не иметь ФИО, даты рождения, адреса проживания, паспорта
(соответственно, его номера), водительского удостоверения (соответственно, его
номера, даты выдачи и категории). 6.В отношении AM порядковые номера автомобилей могут начинаться с 1 и не
должны превышать число 9999, поставленный на учет автомобиль не может не иметь
владельца (его кода), марки (ее кода), государственного регистрационного
номера, номера кузова, номера двигателя, техпаспорта (его номера), даты
выпуска, даты регистрации и цвета. 7.В отношении JACKED_CARS
порядковые номера угнанных автомобилей могут начинаться с 1 и не должны
превышать число 9999, угнанный автомобиль не может быть незарегистрированным, ввиду
чего все требования для отношения зарегистрированных автомобилей справедливы
для данного отношения.
Таблица 2. Автомобили
|
Имя столбца
|
Содерж. описание
|
Тип данных
|
Размер
|
Область допустимых значений
|
Роль
|
Пример
|
|
AM_CODE
|
Код автомобиля
|
Целый
|
4
|
0001-9999
|
PK
|
1234
|
|
AM_DRIVER_CODE
|
Код водителя
|
Целый
|
4
|
0001-9999
|
FK
|
1234
|
|
AM_MARK_CODE
|
Код марки
|
Целый
|
4
|
0001-9999
|
FK
|
1234
|
|
AM_REG_NUMBER
|
Госномер
|
Символьный
|
8
|
«А-Я», «0-9»
|
|
А001АА01
|
|
AM_BODY_NUMBER
|
Номер кузова
|
Целый
|
9
|
000000001-999999999
|
|
123456789
|
|
AM_ENGINE_NUMBER
|
Номер двигателя
|
Целый
|
9
|
000000001-999999999
|
|
123456789
|
|
AM_TECHPASSPORT_NUMBER
|
Номер техпаспорта
|
Целый
|
9
|
000000001-999999999
|
|
123456789
|
|
AM_BIRTHDATE
|
Дата выпуска
|
Дата
|
10
|
|
|
01.01.2000
|
|
AM_REGISTRATION_DATE
|
Дата регистрации
|
Дата
|
10
|
|
|
01.01.2000
|
|
AM_COLOR
|
цвет
|
Символьный
|
7
|
«А-Я», «0-9»
|
|
Белый
|
Таблица 3. Автомобили в угоне
|
Имя столбца
|
Содерж. описание
|
Тип данных
|
Размер
|
Область допустимых значений
|
Роль
|
Пример
|
|
JC_CODE
|
Код угона
|
Целый
|
4
|
0001-9999
|
PK
|
1234
|
|
JC_JACKDATE
|
Дата угона
|
Целый
|
10
|
|
|
01.01.2000
|
|
JC_REPORT_DATE
|
Дата подачи заявки
|
Дата
|
10
|
|
|
01.01.2000
|
|
JC_AM_CODE
|
Код угнанного а/м
|
Целый
|
4
|
0001-9999
|
FK
|
1234
|
|
JC_DRIVER_CODE
|
Код владельца
|
Целый
|
4
|
0001-9999
|
FK
|
1234
|
|
JC_ADDITIONAL
|
Доп. Сведения
|
Символьный
|
30
|
«А-Я», «0-9»
|
|
Ночью
|
|
JC_FOUND
|
Отметка о нахождении
|
Логический
|
1
|
0-1
|
|
1
|
|
JC_FOUND_DATE
|
Дата нахождения
|
Дата
|
10
|
|
|
01.01.2000
|
Таблица 4. Водители (владельцы а/м)
|
Имя столбца
|
Содерж. описание
|
Тип Данных
|
Размер
|
Область допустимых значений
|
Роль
|
Пример
|
|
DRIVER_CODE
|
Код водителя
|
Целый
|
4
|
0001-9999
|
PK
|
1234
|
|
DRIVER_FIO
|
ФИО
|
Символьный
|
33
|
«А-Я», «0-9»
|
|
Банников Денис Павлович
|
|
DRIVER_BIRTHDATE
|
Дата рождения
|
Дата
|
10
|
|
|
06.11.1991
|
|
DRIVER_ADRESS
|
Адрес
|
Символьный
|
30
|
«А-Я», «0-9»
|
|
Отцовский пер. 1-1
|
|
DRIVER_PASSPORT
|
Номер паспорта
|
Целый
|
10
|
1111111111-9999999999
|
|
5602123456
|
|
DRIVER_RULES
|
Номер вод. удостовер.
|
Символьный
|
12
|
|
|
123А4569Р1
|
|
DRIVER_RULES_DATE
|
Дата выдачи прав
|
Дата
|
10
|
|
|
06.11.2009
|
|
DRIVER_CATEGORY
|
Категория прав
|
1
|
«А-Я»
|
|
В
|
Таблица 5. Марки автомобилей
|
Имя столбца
|
Содержательное описание
|
Тип Данных
|
Размер
|
Область допустимых значений
|
Роль
|
Пример
|
|
MARK_CODE
|
Код марки
|
Целый
|
4
|
0001-9999
|
PK
|
1234
|
|
MARK_NAME
|
Название марки
|
Символьный
|
30
|
«А-Я»
|
|
Лада
|
|
FIRM_CODE
|
Код фирмы
|
Целый
|
4
|
0001-9999
|
FK
|
1234
|
|
COUNTRY_CODE
|
Код страны
|
Целый
|
4
|
0001-9999
|
FK
|
1234
|
Таблица 5. Фирмы
|
Имя столбца
|
Содержательное описание
|
Тип Данных
|
Размер
|
Область допустимых значений
|
Роль
|
Пример
|
|
FIRM_CODE
|
Код фирмы
|
Целый
|
4
|
0001-9999
|
PK
|
1234
|
|
FIRM_NAME
|
Название фирмы
|
Символьный
|
30
|
«А-Я»
|
|
Audi
|
Таблица 6. Страны
|
Имя столбца
|
Содержательное описание
|
Тип Данных
|
Размер
|
Область допустимых значений
|
Роль
|
Пример
|
|
COUNTRY_CODE
|
Код страны
|
Целый
|
4
|
0001-9999
|
PK
|
1234
|
|
COUNTRY_NAME
|
Название страны
|
Символьный
|
30
|
«А-Я»
|
|
Россия
|
Программа для создания базы данных представлена в
Приложении А.
III. Программная часть
.1 Разработка
запросов на языке t-sql
. Показать коды всех белых автомобилей (Рис. 10).
AM_CODE from AM where AM_COLOR='WHITE'
Рис. 10 Коды всех белых автомобилей
. Показать все немецкие марки автомобилей (Рис. 11).
MARK_NAME from AM_MARK where COUNTRY_CODE LIKE 2
Рис. 11Немецкие марки автомобилей
. Показать всех водителей с фамилией Гришин (GRISHIN) (Рис. 10)
DRIVER_FIO from DRIVERS where DRIVER_FIO LIKE 'GRISHIN%'
Рис. 12 Водителей с фамилией Гришин
4.Показать информацию по белым автомобилям, владельцами которых являются
водители категории В (Рис. 13).
* FROM AM,DRIVERS WHERE AM.AM_COLOR LIKE 'WHITE' AND
DRIVERS.DRIVER_CATEGORY LIKE 'B' AND DRIVERS.DRIVER_CODE = AM.AM_DRIVER_CODE
Рис. 13 Белым автомобилям, владельцами которых являются водители
категории В
.Показать все угнанные автомобили, зарегистрированные после 10.10.1991
(Рис. 14).
* FROM JACKED_CARS,AM WHERE JACKED_CARS.JC_AM_CODE=AM.AM_CODE AND
AM.AM_REGISTRATION_DATE>'10.10.1991'
Рис. 14 Угнанные автомобили, зарегистрированные после 10.10.1991
.Показать все угнанные автомобили после 01.01.2003 (Рис. 15).
* FROM JACKED_CARS, AM WHERE JACKED_CARS.JC_AM_CODE = AM.AM_CODE AND
JACKED_CARS.JC_JACKDATE > '01.01.2003'
Рис. 15 Угнанные автомобили после 01.01.2003
.Показать все угнанные после 01.01.2003 белые автомобили (Рис. 16)
* FROM JACKED_CARS,AM WHEREJACKED_CARS.JC_AM_CODE=AM.AM_CODE AND
JACKED_CARS.JC_JACKDATE>'01.01.2003' ANDAM.AM_COLOR = 'WHITE'
Рис. 16 Угнанные после 01.01.2003 белые автомобили
.Показать все угнанные автомобили после 01.01.2003 у водителей категории
В (Рис. 17).
* FROM JACKED_CARS, AM, DRIVERS WHERE JACKED_CARS.JC_AM_CODE = AM.AM_CODE
AND JACKED_CARS.JC_JACKDATE > '01.01.2003' AND AM.AM_CODE =
DRIVERS.DRIVER_CODE AND DRIVERS.DRIVER_CATEGORY = 'B'
Рис. 17 Угнанные автомобили после 01.01.2003 у водителей категории В
9.Показать всех водителейAUDI(Рис. 18).
SELECT * FROM DRIVERS, AM WHERE DRIVERS.DRIVER_CODE =AM.AM_DRIVER_CODE
AND AM.AM_MARK_CODE = 3
Рис. 18 ВодителиAUDI
.Показать всех водителей белых AUDI(Рис. 19).
* FROM DRIVERS, AM WHERE DRIVERS.DRIVER_CODE = AM.AM_DRIVER_CODE AND
AM.AM_MARK_CODE = 3 AND AM.AM_COLOR = 'WHITE'
Рис. 19 Водители белых AUDI
3.2 Разработка
хранимых процедур
Для облегчения работы с БД были разработаны следующие процедуры:
. Процедура просмотра кодов белых автомобилей.
Данная процедура выводит коды всех белых автомобилей (Рис. 21).
PROCEDURE PROC1 ASAM_CODE FROM AM WHERE AM.AM_COLOR = 'WHITE'
Выполнение и результат:
Имеем (Рис. 20):
* FROM AM
Рис. 20 Все автомобили
Получим (Рис. 21):
Рис. 21 Коды всех белых автомобилей
2. Процедура поиска автомобиля по номеру
Данная процедура выводит цвет и дату выпуска автомобиля с указанным
номером.
CREATE PROC PROC2
@NOMER char(40)
AS SELECT AM_REG_NUMBER, AM_BIRTHDATE, AM_COLOR FROM AM WHERE AM.AM_COLOR
= @NOMERPROC2 'A001AA01'
Получим:
Рис. 22 Цвет и дата выпуска автомобиля с номером 'A001AA01'
3.3 Разработка
триггеров
Данный триггер производит проверку правельности ввода водительской
категории.
Событие: в базу данных производится добавлениеводителя.
Предусмотренные действия: SQL выдаёт ошибку с сообщением о том что
вводимой категории не существует (Рис. 23) или же сообщение о том, что данные
водителя введены(Рис. 24).
Рис. 23 Сообщением о том что вводимой категории не существует
Рис. 24
Данные водителя введены
CREATE TRIGGER ADDDRIVERS
ON DRIVERS
AFTER INSERT
SET NOCOUNT ON;(select DRIVER_CATEGORY from inserted)!='A' (select
DRIVER_CATEGORY from inserted)!='B'(select DRIVER_CATEGORY from inserted)!='C'(select
DRIVER_CATEGORY from inserted)!='D''CATEGORY NO EXIST''ENTERED'
ЗАКЛЮЧЕНИЕ
В ходе выполнения курсовой работы были получены знания в проектировании
структуры базы данных, разработке и реализации модели базы данных, а также
приобретены практические навыки проектирования реляционной базы данных. Была
разработана и реализована база данных информационной системы «ГАИ». Разработана
структура базы данных, состоящая из 6 таблиц. Разработаны ограничения
целостности для сохранения логической непротиворечивости данных в системе.
Разработаны наиболее часто употребляемые в данной предметной области запросы.
Разработаны и отлажены хранимые процедуры, упрощающие работу с БД. Разработаны
и отлажены триггеры, осуществляющие проверку сложных логических условий.
Таким образом, были успешно реализованы все особенности предметной
области и требования, заданные на этапе проектирования.
ИСТОЧНИКИ И ЛИТЕРАТУРА
1. Карпова
Т.С. Базы данных: модели, разработка, реализация. - СПб.: Питер, 2001
2. И.
Казакова. Основы языка TransactSQL - ПГУ, 2010
. Кириллов
В.В. Введение в реляционные базы данных. - СПб.: БХВ-Петербург, 2012
. ИцикБен-Ган
- Microsoft SQL Server 2008. Основы T-SQL - БХВ-Петербург, 2009
5. https://www.microsoft.com/sqlserver/2008/ru/ru/overview.aspx
6. https://ru.wikipedia.org/wiki/Microsoft_SQL_Server
. https://ru.wikipedia.org/wiki/Transact-SQL
. http://www.lessons-tva.info/edu/e-inf2/m2t4.html
. http://informatic.ugatu.ac.ru/lib/office/Proekt.htm
. http://www.informatika.edusite.ru/lezione9_42.htm
. https://msdn.microsoft.com/ru-ru/library/bb510741.aspx
ПРИЛОЖЕНИЯ
Приложение А
ПРОГРАММА СОЗДАНИЯ БАЗЫ ДАННЫХ
CREATE DATABASE GAI_DB(NAME=GAI_Data,
='c:\GAI_Data.mdf', =50, =150,=10%)ON (=SQLStepLog,='c:\GAI_log.ldf', =50,
=150,=10%
CREATE TABLE FIRM
(FIRM_CODE INT PRIMARY KEY CHECK (FIRM_CODE < 9999 AND
FIRM_CODE > 0000),FIRM_NAME CHAR(30) NOT NULL,)TABLE COUNTRY
(COUNTRY_CODE INT PRIMARY KEY CHECK (COUNTRY_CODE < 9999
AND COUNTRY_CODE > 0000),COUNTRY_NAME CHAR(30) NOT NULL,)TABLE AM_MARK
(MARK_CODE INT PRIMARY KEY CHECK ( MARK_CODE > 0000 AND
MARK_CODE < 9999),MARK_NAME CHAR (30) NOT NULL,FIRM_CODE INT NOT NULL CHECK
(FIRM_CODE < 9999 AND FIRM_CODE > 0000) FOREIGN KEY REFERENCES FIRM
(FIRM_CODE),COUNTRY_CODE INT NOT NULL CHECK (COUNTRY_CODE > 0000 AND
COUNTRY_CODE < 9999) FOREIGN KEY REFERENCES COUNTRY (COUNTRY_CODE))TABLE
DRIVERS
(DRIVER_CODE INT PRIMARY KEY CHECK (DRIVER_CODE > 0000 AND
DRIVER_CODE < 9999),DRIVER_FIO CHAR (30) NOT NULL,DRIVER_BIRTHDATE DATE NOT
NULL,DRIVER_ADRESS CHAR (30) NOT NULL,DRIVER_PASSPORT INT NOT NULL,DRIVER_RULES
CHAR (12) NOT NULL,DRIVER_RULES_DATE DATE NOT NULL,DRIVER_CATEGORY CHAR (1) NOT
NULL,)TABLE AM
(AM_CODE INT PRIMARY KEY CHECK (AM_CODE < 9999 AND AM_CODE
> 0000),AM_DRIVER_CODE INT NOT NULL FOREIGN KEY REFERENCES DRIVERS
(DRIVER_CODE),AM_MARK_CODE INT NOT NULL FOREIGN KEY REFERENCES AM_MARK
(MARK_CODE),AM_REG_NUMBER CHAR (8) NOT NULL,AM_BODY_NUMBER INT NOT
NULL,AM_ENGINE_NUMBER INT NOT NULL,AM_TECHPASSPORT_NUMBER INT NOT
NULL,AM_BIRTHDATE DATE NOT NULL,AM_REGISTRATION_DATE DATE NOT NULL,AM_COLOR
CHAR (7) NOT NULL,)TABLE JACKED_CARS
(JC_CODE INT PRIMARY KEY CHECK (JC_CODE > 0 AND JC_CODE
< 9999),JC_JACKDATE DATE NOT NULL,JC_REPORT_DATE DATE NOT NULL,JC_AM_CODE
INT NOT NULL FOREIGN KEY REFERENCES AM (AM_CODE),JC_DRIVER_CODE INT NOT NULL
FOREIGN KEY REFERENCES DRIVERS (DRIVER_CODE),JC_ADDITIONAL CHAR (100),JC_FOUND
BIT,JC_FOUND_DATE DATE,)
Приложение Б
ПРОГРАММА ВВОДА ТЕСТОВЫХ ДАННЫХ
INSERT INTO COUNTRY VALUES (1, 'RUSSIA');INTO COUNTRY VALUES (2,
'GERMANY');INTO COUNTRY VALUES (3, 'JAPAN');INTO COUNTRY VALUES (4,
'FRANCE');INTO COUNTRY VALUES (5, 'UK');INTO FIRM VALUES (1, 'LADA');INTO FIRM
VALUES (2, 'VW');INTO FIRM VALUES (3, 'AUDI');INTO FIRM VALUES (4,
'NISSAN');INTO FIRM VALUES (5, 'HONDA');INTO AM_MARK VALUES (1, 'LADA', 1,
1);INTO AM_MARK VALUES (2, 'VW', 2, 2);INTO AM_MARK VALUES (3, 'AUDI', 3,
3);INTO AM_MARK VALUES (4, 'NISSAN', 4, 4);INTO AM_MARK VALUES (5, 'HONDA', 5,
5);INTO AM VALUES (1, 1, 1, 'A001AA01', 123456789, 987654321, 789456321, '01.10.1990',
'03.10.1990','WHITE');INTO AM VALUES (2, 2, 1, 'A002AA01', 123456788,
987654322, 789456322, '02.09.1991', '04.09.1991','BLACK');INTO AM VALUES (3, 3,
1, 'A003AA01', 123456787, 987654323, 789456323, '03.08.1992',
'05.08.1992','WHITE');INTO AM VALUES (4, 4, 2, 'A004AA01', 123456786,
987654324, 789456324, '04.07.1993', '06.07.1993','RED');INTO AM VALUES (5, 5,
2, 'A005AA01', 123456785, 987654325, 789456325, '05.06.1994',
'07.06.1994','GREEN');INTO AM VALUES (6, 6, 2, 'A006AA01', 123456784,
987654326, 789456326, '06.05.1995', '08.05.1995','BLUE');INTO AM VALUES (7, 7,
3, 'A007AA01', 123456783, 987654327, 789456327, '07.04.1996',
'09.04.1996','GRAY');INTO AM VALUES (8, 8, 3, 'A008AA01', 123456782, 987654328,
789456328, '08.03.1997', '10.03.1997','WHITE');INTO AM VALUES (9, 9, 3,
'A009AA01', 123456781, 987654329, 789456329, '09.02.1998',
'11.02.1998','BLUE');INTO AM VALUES (10, 10, 4, 'A010AA01', 123456780,
987654320, 789456320, '10.01.1999', '12.01.1999','GRAY');INTO JACKED_CARS
VALUES (1, '01.01.2001', '02.01.2001', 1, 1, 'V NOCH NA 29E', 0, NULL);INTO
JACKED_CARS VALUES (2, '02.02.2002', '03.02.2002', 2, 2, 'SO STOYANKI', 0,
NULL);INTO JACKED_CARS VALUES (3, '03.03.2003', '04.03.2003', 3, 3,
'SIGNALIZACIYA NE SRABOTALA', 0, NULL);INTO JACKED_CARS VALUES (4,
'04.04.2004', '05.04.2004', 4, 4, 'UGNANA DVUMYA NEIZVESTNYMI', 0, NULL);INTO
JACKED_CARS VALUES (5, '05.05.2005', '06.05.2005', 5, 5, 'S POMOSCHYU
EVAKUATORA', 0, NULL);INTO DRIVERS VALUES (1, 'IGNATOVA POLINA OLEGOVNA',
'01.01.1992', 'AVTONOMNAYA 1-2', 1234567890, '123AP1234567', '01.01.2010',
'B');INTO DRIVERS VALUES (2, 'BORMOTOVA VIKTORIYA PAVLOVNA', '02.02.1991',
'AKSAKOVA 2-3', 1234567891, '123AP1234567', '01.01.2010', 'B');INTO DRIVERS
VALUES (3, 'MISKO KSENIA ANATOLIEVNA', '03.03.1990', 'AKTIVNAYA 3-4',
1234567892, '123AP1234567', '01.01.2010', 'B');INTO DRIVERS VALUES (4,
'SERIKOVA ULIYA DMITRIEVNA', '04.04.1989', 'ANTONOVA 4-5', 1234567893,
'123AP1234567', '01.01.2010', 'B');INTO DRIVERS VALUES (5, 'ASHAKINA GALINA
SERGEEVNA', '05.05.1988', 'AUSTRINA 5-6', 1234567894, '123AP1234567',
'01.01.2010', 'B');INTO DRIVERS VALUES (6, 'BONDAREVA TATYANA MIHAILOVNA',
'06.06.1987', 'BAIDUKOVA 6-7', 1234567895, '123AP1234567', '01.01.2010',
'B');INTO DRIVERS VALUES (7, 'GRISHIN DMITRY ANDREEVICH', '07.07.1986',
'BAKUNINA 7-8', 1234567896, '123AP1234567', '01.01.2010', 'B');INTO DRIVERS
VALUES (8, 'MARCHENDO ELENA ANDREEVNA', '08.08.1985', 'BATAISKAYA 8-9',
1234567897, '123AP1234567', '01.01.2010', 'B');INTO DRIVERS VALUES (9,
'MILKOVSKY EVGENY DENISOVICH', '09.09.1984', 'BELINSKOGO 9-10', 1234567898,
'123AP1234567', '01.01.2010', 'B');INTO DRIVERS VALUES (10, 'KUZNETSOV DENIS
SERGEEVICH', '10.10.1983', 'BAUMANA 10-11', 1234567899, '123AP1234567',
'01.01.2010', 'B');INTO DRIVERS VALUES (11, 'KARPOV OLEG SERGEEVICH',
'11.11.1993', 'LENINA 12-13', 1234567899, '003AP1234567', '11.04.2003',
'A');INTO DRIVERS VALUES (12, 'BOBROV IVAN SERGEEVICH', '17.04.1981', 'LENINA
10-10', 1234569999, '223AP1234567', '04.04.2011', 'D');