Размещено на http://www.allbest.ru/

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ

I. ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

1.1 Основные понятия и определения

1.1.1 Типы моделей баз данных

1.1.2 Иерархическая модель

1.1.3 Сетевая модель

1.1.4 Табличная или реляционная модель

1.1.5 Понятие первичного ключа

1.1.6 Реляционные отношения между таблицами базы данных

1.1.7 Ссылочная целостность и каскадные воздействия

1.1.8 Понятие внешнего ключа

1.1.9 Индексы и методы доступа

1.1.10 Нормализация таблиц при проектировании БД

1.2 Выбор СУБД

II. ПРОЕКТНАЯ ЧАСТЬ

2.1 Концептуальная модель предметной области

2.2 Логическая модель базы данных

2.3 Физическая модель базы данных

III. ПРОГРАММНАЯ ЧАСТЬ

3.1 Разработка запросов на языке t-sql

3.2 Разработка хранимых процедур

3.3 Разработка триггеров

ЗАКЛЮЧЕНИЕ

ИСТОЧНИКИ И ЛИТЕРАТУРА

ПРИЛОЖЕНИЯ

ВВЕДЕНИЕ

Базы данных стали основой информационных систем и в корне изменили методы работы многих организаций. В частности, в последние годы развитие технологии баз данных привело к созданию весьма мощных и удобных в эксплуатации систем. Благодаря этому системы баз данных стали доступными широкому кругу пользователей. Но, к сожалению, кажущаяся простота таких систем способствовала тому, что пользователи стали самостоятельно создавать базы данных и приложения, не имея достаточных знаний о методах проектирования эффективно работающих систем, что часто приводило к непроизводительным затратам ресурсов и некачественным результатам. Вызванная этим неудовлетворенность пользователей стала причиной возникновения известного "кризиса программного обеспечения", или так называемой "депрессии программного обеспечения", последствия которой не устранены и поныне.

В широком смысле слова база данных (БД) - это совокупность сведений о конкретных объектах реального мира в какой-либо предметной области.

Реляционная база данных - это совокупность отношений, содержащих всю информацию, которая должна храниться в БД. Пользователи воспринимают такую базу данных как совокупность таблиц, связанных между собой. Система управления базами данных - это комплекс программных и языковых средств, необходимых для создания, обработки баз данных и поддержания их в актуальном состоянии.

Целью курсовой работы является получение и углубление знаний в проектировании структуры базы данных, разработке и реализации модели базы данных, а также в приобретении практических навыков проектирования реляционной базы данных. Задачей курсовой работы является разработка и реализация базы данных информационной системы“ГАИ” (Государственной авто инспекции) средствами СУБД Microsoft SQL Server 2008.

Система создаётся для обслуживания следующих групп пользователей:

1. Сотрудники ГАИ;

2. Владельцы автомобилей.

Функциональные возможности:

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

2. реализация хранимых процедур и триггеров для поддержания сложных ограничений целостности в базе данных;

3. реализация наиболее часто встречающихся запросов и представлений для пользователей в готовом виде.

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.

Microsoft 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 позволяет создать надежную, производительную, интеллектуальную платформу, отвечающую всем требованиям по работе с данными.

Transact-SQL (T-SQL) -- процедурное расширение языка SQL, созданное компанией Microsoft (для Microsoft SQL Server) и Sybase (для Sybase ASE).

SQL был расширен такими дополнительными возможностями как:

· управляющие операторы,

· локальные и глобальные переменные,

· различные дополнительные функции для обработки строк, дат, математики и т. п.,

· поддержка аутентификации MicrosoftWindows

Язык Transact-SQL является ключом к использованию MS SQL Server. Все приложения, взаимодействующие с экземпляром MS SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.



II. Проектная часть

2.1 Концептуальная модель предметной области

База данных создаётся для информационного обслуживания зарегистрированных водителей и автомобилей. БД должна содержать данные о зарегистрированных автомобилях, в том числе угнанные машины, а также, данные о владельцах. В соответствии с предметной областью система строится с учётом следующих особенностей: Каждый автомобиль имеет свой уникальный номер; Каждый автомобиль должен иметь дату регистрации, госномер, цвет; На каждого водителя создается запись с его данными.

Задачи, решаемые с помощью запросов на выборку:

1.Показать коды всех белых автомобилей

2.Показать все немецкие марки автомобилей

3.Показать всех водителей с фамилией Гришин (GRISHIN)

4.Показать информацию по белым автомобилям, владельцами которых являются водители категории В

5.Показать все угнанные автомобили, выпущенные после 10.10.1991

6.Показать все угнанные после 01.01.2003 автомобили

7.Показать все угнанные после 01.01.2003 белые автомобили

8.Показать все угнанные у водителей категории В после 01.01.2003 автомобили

9.Показать всех водителей AUDI

10.Показать всех водителей белых AUDI

2.2 Логическая модель базы данных

Выделим базовые сущности этой предметной области: Автомобили - это связь между водителями и кодами марки. Атрибуты: код автомобиля, код владельца, код марки, госномер, номер кузова, номер двигателя, номер техпаспорта, дата выпуска, дата регистрации, цвет. Угнанные автомобили. Атрибуты: код заявки, дата угона, дата подачи заявления, код автомобиля, код владельца, обстоятельства угона, отметка о нахождении, дата нахождения.

Владельцы. Атрибуты: код владельца, ФИО, дата рождения, адрес, номер паспорта, номер прав, дата выдачи прав, категория.

Марки. Атрибуты: код марки, название, код фирмы, код страны.

Фирмы. Атрибуты: код фирмы, название.

Страны. Атрибуты: код страны, название.

Логическая модель базы данных представлена на рисунке 9.

Рис. 9 Логическая модель базы данных

база реляционный ссылочный триггер

2.3 Физическая модель базы данных

Основные ограничения целостности:

1.Текстовые поля могут содержать только символы русского алфавита, цифры и знаки препинания.

2.В отношении FIRM порядковые номера фирм могут начинаться с 1 и не должны превышать число 9999, фирма не может не иметь названия.

3.В отношении COUNTRY порядковые номера стран могут начинаться с 1 и не должны превышать число 9999, страна не может не иметь названия.

4.В отношении AM_MARK порядковые номера марок автомобилей могут начинаться с 1 и не должны превышать число 9999, марка автомобиля не может не иметь названия.

5.В отношении 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. Программная часть

3.1 Разработка запросов на языке t-sql

1. Показать коды всех белых автомобилей (Рис. 10).

SELECT AM_CODE from AM where AM_COLOR='WHITE'

Рис. 10 Коды всех белых автомобилей

2. Показать все немецкие марки автомобилей (Рис. 11).

SELECT MARK_NAME from AM_MARK where COUNTRY_CODE LIKE 2

Рис. 11Немецкие марки автомобилей

3. Показать всех водителей с фамилией Гришин (GRISHIN) (Рис. 10)

SELECT DRIVER_FIO from DRIVERS where DRIVER_FIO LIKE 'GRISHIN%'

Рис. 12 Водителей с фамилией Гришин



4.Показать информацию по белым автомобилям, владельцами которых являются водители категории В (Рис. 13).

SELECT * FROM AM,DRIVERS WHERE AM.AM_COLOR LIKE 'WHITE' AND DRIVERS.DRIVER_CATEGORY LIKE 'B' AND DRIVERS.DRIVER_CODE = AM.AM_DRIVER_CODE

Рис. 13 Белым автомобилям, владельцами которых являются водители категории В

5.Показать все угнанные автомобили, зарегистрированные после 10.10.1991 (Рис. 14).

SELECT * 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

6.Показать все угнанные автомобили после 01.01.2003 (Рис. 15).

SELECT * 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

7.Показать все угнанные после 01.01.2003 белые автомобили (Рис. 16)

SELECT * 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 белые автомобили

8.Показать все угнанные автомобили после 01.01.2003 у водителей категории В (Рис. 17).

SELECT * 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

10.Показать всех водителей белых AUDI(Рис. 19).

SELECT * 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 Разработка хранимых процедур

Для облегчения работы с БД были разработаны следующие процедуры:

1. Процедура просмотра кодов белых автомобилей.

Данная процедура выводит коды всех белых автомобилей (Рис. 21).

CREATE PROCEDURE PROC1 AS

SELECT AM_CODE FROM AM WHERE AM.AM_COLOR = 'WHITE'

Выполнение и результат:

Имеем (Рис. 20):

SELECT * 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 = @NOMER

EXEC PROC2 'A001AA01'

Получим:

Рис. 22 Цвет и дата выпуска автомобиля с номером 'A001AA01'



3.3 Разработка триггеров

Данный триггер производит проверку правельности ввода водительской категории.

Событие: в базу данных производится добавлениеводителя.

Предусмотренные действия: SQL выдаёт ошибку с сообщением о том что вводимой категории не существует (Рис. 23) или же сообщение о том, что данные водителя введены(Рис. 24).

Рис. 23 Сообщением о том что вводимой категории не существует

Рис. 24 Данные водителя введены



CREATE TRIGGER ADDDRIVERS

ON DRIVERS

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

IF (select DRIVER_CATEGORY from inserted)!='A'

IF (select DRIVER_CATEGORY from inserted)!='B'

IF (select DRIVER_CATEGORY from inserted)!='C'

IF (select DRIVER_CATEGORY from inserted)!='D'

BEGIN

rollback

print'CATEGORY NO EXIST'

END

ELSE

BEGIN

print'ENTERED'

END

END



ЗАКЛЮЧЕНИЕ

В ходе выполнения курсовой работы были получены знания в проектировании структуры базы данных, разработке и реализации модели базы данных, а также приобретены практические навыки проектирования реляционной базы данных. Была разработана и реализована база данных информационной системы «ГАИ». Разработана структура базы данных, состоящая из 6 таблиц. Разработаны ограничения целостности для сохранения логической непротиворечивости данных в системе. Разработаны наиболее часто употребляемые в данной предметной области запросы. Разработаны и отлажены хранимые процедуры, упрощающие работу с БД. Разработаны и отлажены триггеры, осуществляющие проверку сложных логических условий.

Таким образом, были успешно реализованы все особенности предметной области и требования, заданные на этапе проектирования.



ИСТОЧНИКИ И ЛИТЕРАТУРА

1. Карпова Т.С. Базы данных: модели, разработка, реализация. - СПб.: Питер, 2001

2. И. Казакова. Основы языка TransactSQL - ПГУ, 2010

3. Кириллов В.В. Введение в реляционные базы данных. - СПб.: БХВ-Петербург, 2012

4. ИцикБен-Ган - 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

7. https://ru.wikipedia.org/wiki/Transact-SQL

8. http://www.lessons-tva.info/edu/e-inf2/m2t4.html

9. http://informatic.ugatu.ac.ru/lib/office/Proekt.htm

10. http://www.informatika.edusite.ru/lezione9_42.htm

11. https://msdn.microsoft.com/ru-ru/library/bb510741.aspx



ПРИЛОЖЕНИЯ

Приложение А

ПРОГРАММА СОЗДАНИЯ БАЗЫ ДАННЫХ

CREATE DATABASE GAI_DB

ON

PRIMARY (NAME=GAI_Data,

FILENAME='c:\GAI_Data.mdf',

SIZE=50,

MAXSIZE=150,

FILEGROWTH=10%)

LOG ON (

NAME=SQLStepLog,

FILENAME='c:\GAI_log.ldf',

SIZE=50,

MAXSIZE=150,

FILEGROWTH=10%

CREATE TABLE FIRM

(FIRM_CODE INT PRIMARY KEY CHECK (FIRM_CODE < 9999 AND FIRM_CODE > 0000),FIRM_NAME CHAR(30) NOT NULL,)

CREATE TABLE COUNTRY

(COUNTRY_CODE INT PRIMARY KEY CHECK (COUNTRY_CODE < 9999 AND COUNTRY_CODE > 0000),COUNTRY_NAME CHAR(30) NOT NULL,)

CREATE 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))

CREATE 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,)

CREATE 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,)

CREATE 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');

INSERT INTO COUNTRY VALUES (2, 'GERMANY');

INSERT INTO COUNTRY VALUES (3, 'JAPAN');

INSERT INTO COUNTRY VALUES (4, 'FRANCE');

INSERT INTO COUNTRY VALUES (5, 'UK');

INSERT INTO FIRM VALUES (1, 'LADA');

INSERT INTO FIRM VALUES (2, 'VW');

INSERT INTO FIRM VALUES (3, 'AUDI');

INSERT INTO FIRM VALUES (4, 'NISSAN');

INSERT INTO FIRM VALUES (5, 'HONDA');

INSERT INTO AM_MARK VALUES (1, 'LADA', 1, 1);

INSERT INTO AM_MARK VALUES (2, 'VW', 2, 2);

INSERT INTO AM_MARK VALUES (3, 'AUDI', 3, 3);

INSERT INTO AM_MARK VALUES (4, 'NISSAN', 4, 4);

INSERT INTO AM_MARK VALUES (5, 'HONDA', 5, 5);

INSERT INTO AM VALUES (1, 1, 1, 'A001AA01', 123456789, 987654321, 789456321, '01.10.1990', '03.10.1990','WHITE');

INSERT INTO AM VALUES (2, 2, 1, 'A002AA01', 123456788, 987654322, 789456322, '02.09.1991', '04.09.1991','BLACK');

INSERT INTO AM VALUES (3, 3, 1, 'A003AA01', 123456787, 987654323, 789456323, '03.08.1992', '05.08.1992','WHITE');

INSERT INTO AM VALUES (4, 4, 2, 'A004AA01', 123456786, 987654324, 789456324, '04.07.1993', '06.07.1993','RED');

INSERT INTO AM VALUES (5, 5, 2, 'A005AA01', 123456785, 987654325, 789456325, '05.06.1994', '07.06.1994','GREEN');

INSERT INTO AM VALUES (6, 6, 2, 'A006AA01', 123456784, 987654326, 789456326, '06.05.1995', '08.05.1995','BLUE');

INSERT INTO AM VALUES (7, 7, 3, 'A007AA01', 123456783, 987654327, 789456327, '07.04.1996', '09.04.1996','GRAY');

INSERT INTO AM VALUES (8, 8, 3, 'A008AA01', 123456782, 987654328, 789456328, '08.03.1997', '10.03.1997','WHITE');

INSERT INTO AM VALUES (9, 9, 3, 'A009AA01', 123456781, 987654329, 789456329, '09.02.1998', '11.02.1998','BLUE');

INSERT INTO AM VALUES (10, 10, 4, 'A010AA01', 123456780, 987654320, 789456320, '10.01.1999', '12.01.1999','GRAY');

INSERT INTO JACKED_CARS VALUES (1, '01.01.2001', '02.01.2001', 1, 1, 'V NOCH NA 29E', 0, NULL);

INSERT INTO JACKED_CARS VALUES (2, '02.02.2002', '03.02.2002', 2, 2, 'SO STOYANKI', 0, NULL);

INSERT INTO JACKED_CARS VALUES (3, '03.03.2003', '04.03.2003', 3, 3, 'SIGNALIZACIYA NE SRABOTALA', 0, NULL);

INSERT INTO JACKED_CARS VALUES (4, '04.04.2004', '05.04.2004', 4, 4, 'UGNANA DVUMYA NEIZVESTNYMI', 0, NULL);

INSERT INTO JACKED_CARS VALUES (5, '05.05.2005', '06.05.2005', 5, 5, 'S POMOSCHYU EVAKUATORA', 0, NULL);

INSERT INTO DRIVERS VALUES (1, 'IGNATOVA POLINA OLEGOVNA', '01.01.1992', 'AVTONOMNAYA 1-2', 1234567890, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (2, 'BORMOTOVA VIKTORIYA PAVLOVNA', '02.02.1991', 'AKSAKOVA 2-3', 1234567891, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (3, 'MISKO KSENIA ANATOLIEVNA', '03.03.1990', 'AKTIVNAYA 3-4', 1234567892, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (4, 'SERIKOVA ULIYA DMITRIEVNA', '04.04.1989', 'ANTONOVA 4-5', 1234567893, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (5, 'ASHAKINA GALINA SERGEEVNA', '05.05.1988', 'AUSTRINA 5-6', 1234567894, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (6, 'BONDAREVA TATYANA MIHAILOVNA', '06.06.1987', 'BAIDUKOVA 6-7', 1234567895, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (7, 'GRISHIN DMITRY ANDREEVICH', '07.07.1986', 'BAKUNINA 7-8', 1234567896, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (8, 'MARCHENDO ELENA ANDREEVNA', '08.08.1985', 'BATAISKAYA 8-9', 1234567897, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (9, 'MILKOVSKY EVGENY DENISOVICH', '09.09.1984', 'BELINSKOGO 9-10', 1234567898, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (10, 'KUZNETSOV DENIS SERGEEVICH', '10.10.1983', 'BAUMANA 10-11', 1234567899, '123AP1234567', '01.01.2010', 'B');

INSERT INTO DRIVERS VALUES (11, 'KARPOV OLEG SERGEEVICH', '11.11.1993', 'LENINA 12-13', 1234567899, '003AP1234567', '11.04.2003', 'A');

INSERT INTO DRIVERS VALUES (12, 'BOBROV IVAN SERGEEVICH', '17.04.1981', 'LENINA 10-10', 1234569999, '223AP1234567', '04.04.2011', 'D');

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