Реализация базы данных "Государственной автоинспекции"

Разработка базы данных средствами СУБД Microsoft SQL Server 2008. Исследование понятия первичного и внешнего ключа. Реляционные отношения между таблицами базы данных. Ссылочная целостность и каскадные воздействия. Проектирование запросов и триггеров.

Рубрика Программирование, компьютеры и кибернетика
Вид курсовая работа
Язык русский
Дата добавления 27.05.2015
Размер файла 1,0 M

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

Размещено на 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


Подобные документы

  • Основные понятия базы данных. Разработка сложной формы для обработки данных. Модели организации данных. Архитектура Microsoft Access. Реляционные связи между таблицами баз данных. Проектирование базы данных. Модификация данных с помощью запросов действий.

    лабораторная работа [345,5 K], добавлен 20.12.2011

  • Программные продукты, используемые при проектировании базы данных. Разработка базы данных "Библиотека" с использование программного проекта Microsoft SQL Server. Создание таблиц, триггеров, пользователей, репликации, запросов, функций, процедур.

    курсовая работа [897,6 K], добавлен 21.11.2011

  • Понятие реляционной модели данных, целостность ее сущности и ссылок. Основные этапы создания базы данных, связывание таблиц на схеме данных. Проектирование базы данных книжного каталога "Books" с помощью СУБД Microsoft Access и языка запросов SQL.

    курсовая работа [838,9 K], добавлен 25.11.2010

  • Программные средства для реализации базы данных и серверной части информационной системы "Учета технического обслуживания станков" средствами СУБД Microsoft SQL Server 2008. Разработка триггеров для поддержки сложных ограничений целостности в базе данных.

    курсовая работа [768,3 K], добавлен 01.02.2013

  • Разработка базы данных в СУБД Microsoft SQL Server 2008 Express для автоматизированного учета пассажирских перевозок по Ставропольскому краю и механизмов управления ими при помощи триггеров. Экономическая эффективность от внедрения программного продукта.

    курсовая работа [3,4 M], добавлен 22.06.2011

  • Информационные задачи и круг пользователей системы. Выработка требований и ограничений. Разработка проекта базы данных. Программная реализация проекта базы данных. Разработка хранимых процедур для поддержки сложных ограничений целостности в базе данных.

    курсовая работа [706,2 K], добавлен 17.06.2012

  • Разработка базы данных для информационной поддержки деятельности аптеки с целью автоматизированного ведения данных о лекарствах аптеки. Проектирование схемы базы данных с помощью средства разработки структуры базы данных Microsoft SQL Server 2008.

    курсовая работа [3,6 M], добавлен 18.06.2012

  • Создание реляционной базы данных "Деканат ВУЗа", средствами СУБД MS SQL Server 2000. Разработка клиентского приложения с удобным пользовательским интерфейсом (сопровождающегося меню и справочной системой). Описание связей между таблицами базы данных.

    курсовая работа [3,0 M], добавлен 06.12.2014

  • Освоение сервисной системы управления базами данных Microsoft SQL. Разработка базы данных "Служба АТС" в среде Microsoft SQL Server Management Studio и создание запросов на языке SQL. Апробация инфологической модели "сущность - связь" базы данных.

    курсовая работа [2,9 M], добавлен 29.06.2015

  • Разработка базы данных, содержащей информацию, необходимую Государственной инспекции по маломерным судам для выдачи билетов владельцам судов. Особенности создания файла и диаграмм базы данных, SQL-запросов. Объекты информационной модели и их свойства.

    курсовая работа [1,3 M], добавлен 24.10.2012

Работы в архивах красиво оформлены согласно требованиям ВУЗов и содержат рисунки, диаграммы, формулы и т.д.
PPT, PPTX и PDF-файлы представлены только в архивах.
Рекомендуем скачать работу.