Реализация базы данных и серверной части информационной системы 'ГАИ' средствами СУБД Microsoft SQL Server

Содержание

Введение

. Реализация базы данных и серверной части информационной системы «ГАИ» средствами СУБД Microsoft SQL Server

.1 Анализ предметной области

.2 Анализ информационных задач и круга пользователей системы

.3 Выработка требований и ограничений

.4. Разработка проекта базы данных

.5 Программная реализация проекта базы данных

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

.7 Разработка триггеров для поддержки сложных ограничений целостности в базе данных

.8 Запросы

.9 Представления

Заключение

Список использованных источников

Приложение А    ПРОГРАММА СОЗДАНИЯ БАЗЫ ДАННЫХ

Приложение Б    ДИАГРАММА БАЗЫ ДАННЫХ

Приложение В    ПРОГРАММА ВВОДА ТЕСТОВЫХ ДАННЫХ

Приложение Г    РЕАЛИЗАЦИЯ ЗАПРОСОВ НА ЯЗЫКЕ SQL

Введение

Работа с информацией в наши дни один из краеугольных камней практически в любой области деятельности человека. Зачастую требуется провести сбор, хранение, изменение, выборку информации по различным критериям. Системы управления базами данных (СУБД) является очень удобным механизмом для систематизации всех процессов, связанных с обработкой информации.

База данных - организованная в соответствии с определёнными правилами и поддерживаемая в памяти компьютера совокупность данных, характеризующая актуальное состояние некоторой предметной области и используемая для удовлетворения информационных потребностей пользователей

Реляционные базы данных - базы данных, основанные на прикладной логической модели данных, описывающей структурный аспект, аспект целостности и аспект обработки данных.

Система управления базами данных - это комплекс программных и языковых средств, необходимых для создания, обработки баз данных и поддержания их в актуальном состоянии.

Задачей курсовой работы является разработка и реализация базы данных и серверной части информационной системы оптовый склад средствами СУБД Microsoft SQL Server 2008. Microsoft SQL Server - система управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Основной используемый язык запросов - Transact-SQL - является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL)с расширениями.

1. Реализация базы данных и серверной части информационной системы «ГАИ» средствами СУБД Microsoft SQL Server

.1 Анализ предметной области

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

В соответствии с предметной областью система строится с учётом следующих особенностей:

)        Каждый автомобиль имеет свой уникальный номер;

)        Каждый автомобиль должен иметь дату регистрации;

)        На каждого водителя создается запись с его данными;

Выделим базовые сущности этой предметной области:

)        Автомобили - это связь между водителями и кодами марки.

Атрибуты: код автомобиля, код владельца, код марки, госномер, номер кузова, номер двигателя, номер техпаспорта, дата выпуска, дата регистрации, цвет.

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

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

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

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

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

1.2 Анализ информационных задач и круга пользователей системы

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

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

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

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

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

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

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

1.3 Выработка требований и ограничений

база данное пользователь информационный

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

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

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

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

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

.        В отношении DRIVERS порядковые номера водителей могут начинаться с 1 и не должны превышать число 9999, водитель не может не иметь ФИО, даты рождения, адреса проживания, паспорта (соответственно, его номера), водительского удостоверения (соответственно, его номера, даты выдачи и категории).

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

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

1.4. Разработка проекта базы данных

Разработанная структура базы данных может быть представлена в следующем виде:

Рис.1. Структура БД

Таблица 1. Автомобили

Таблица 2. Автомобили в угоне

Таблица 3. Водители (владельцы а/м)

Таблица 4. Марки автомобилей

Таблица 5. Фирмы

Таблица 6. Страны

1.5 Программная реализация проекта базы данных

Программная реализация проекта базы данных выполнена с помощью операторов языка SQL CREATE, DROP, UPDATE, INSERT

Для спроектированной базы данных средствами СУБД Microsoft SQL Server 2008 R2 построена диаграмма, которая приведена в приложении Б.

Текст программы ввода тестовых данных приведен в приложении В.

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

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

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

Данная процедура выводит коды всех белых автомобилей.PROC PROC1AM.AM_CODE AS "KOD"AMAM.AM_COLOR='WHITE'

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

Имеем:

SELECT * FROM AM

Рис. 2

EXEC PROC1

Получим:

Рис. 3

. Процедура поиска автомобиля по номеру

Данная процедура выводит цвет и дату выпуска автомобиля с указанным номером

CREATE PROC PROC2

@NOMER char(40)AM.AM_REG_NUMBER AS "HOMEP", AM.AM_BIRTHDATE AS 'DATA BbIIIYSKA', AM.AM_COLOR AS 'LI,BET'AMAM.AM_REG_NUMBER=@NOMER

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

EXEC PROC2 'A001AA01'

Получим:

Рис. 4

.Процедура удаления строки автомобиля по коду.

Данная процедура удаляет автомобиль по его коду.

CREATE PROC PROC3

@CODE_AM INTAMAM.AM_CODE=@CODE_AM

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

Рис. 5

PROC3 7;* FROM AM;

Получим:

Рис. 6

1.7 Разработка триггеров для поддержки сложных ограничений целостности в базе данных

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

1)Данный триггер производит удаление всех связей из таблиц автомобилей и угнанных автомобилей при удалении владельца.

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

Предусмотренные действия: строка будет удалена из таблицы владельцев, будут удалены соответствующие строки из таблиц угона и автомобилей.

CREATE TRIGGER TRIGGERINSERTDRIVERS FOR DELETE@id = DRIVER_CODE FROM DELETEDFROM JACKED_CARS WHERE JC_DRIVER_CODE = @idFROM AM WHERE AM_DRIVER_CODE = @id'DRIVER AND DATA DELETED'

)Данный триггер не позволяет внести в таблицу владельцев повторяющуюся запись.

Событие: в базу данных добавляется запись о водителе с ФИО, которые уже имеются в таблице БД.

Предусмотренные действия: SQL выдаёт сообщение, что такая запись уже есть.TRIGGER TRIGGERINSERTDRIVERS FOR INSERT@FIO CHAR(33), @FIO1 CHAR(33)@FIO=DRIVER_FIO FROM INSERTED@FIO1=DRIVER_FIO FROM DRIVERS WHERE DRIVER_FIO=@FIO@FIO=@FIO1TRANSACTION'THIS DRIVER ALREADY EXISTS''DRIVER ADDED'

1.8 Запросы

Все запросы на получение практически любого количества данных из одной или нескольких таблиц выполняются с помощью предложения SELECT. В общем случае результатом реализации предложения SELECT является другая таблица.

В курсовой работе разработаны следующие запросы:

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

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

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

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

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

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

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

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

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

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

Подробный текст запросов и результаты их выполнения приведены в приложении Г.

1.9 Представления

В данной работе были разработаны следующие представления, основанные на нескольких таблицах:

. Создаем представление, включающее в себя ФИО водителя, марку его автомобиля и его цвет.

CREATE VIEW VIEW1 ("BODuTEJIb", "MAPKA", "LI,BET")SELECT DRIVERS.DRIVER_FIO, AM_MARK.MARK_NAME, AM.AM_COLORDRIVERS, AM_MARK, AMAM.AM_DRIVER_CODE=DRIVERS.DRIVER_CODE AND AM.AM_MARK_CODE=AM_MARK.MARK_CODE

Рис. 7

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

CREATE VIEW VIEW2 ("DATA BbIIIYCKA", "LI,BET", "HOMEP")SELECT MAX(AM.AM_BIRTHDATE), AM.AM_COLOR, AM.AM_REG_NUMBERAMBY AM.AM_BIRTHDATE,AM.AM_COLOR, AM.AM_REG_NUMBER

Рис. 8

. Создадим представление, которое можно использовать для быстрого анализа владельца и его автомобиля.

CREATE VIEW VIEW3 ("BODuTEJIb", "IIACIIOPT", "ADPEC", "HOMEP", "LI,BET", "MAPKA")SELECT DRIVERS.DRIVER_FIO, DRIVERS.DRIVER_PASSPORT, DRIVERS.DRIVER_ADRESS,AM.AM_REG_NUMBER, AM.AM_COLOR, AM_MARK.MARK_NAMEDRIVERS, AM, AM_MARKAM.AM_DRIVER_CODE=DRIVERS.DRIVER_CODE AND AM.AM_MARK_CODE=AM_MARK.MARK_CODE

Рис. 9

Разработана структура базы данных, состоящая из 6 таблиц.

Разработаны ограничения целостности для сохранения логической непротиворечивости данных в системе.

Разработаны наиболее часто употребляемые в данной предметной области запросы.

Разработаны и отлажены хранимые процедуры, упрощающие работу с БД.

Разработаны и отлажены триггеры, осуществляющие проверку сложных логических условий и синхронизацию таблиц между собой при их изменении.

Разработаны представления, повышающие комфорт и безопасность работы с системой.

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

Список использованных источников

1.  М. Грабер «ПониманиеSQL».

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

.        Электронный справочник по Transact SQL

#"552524.files/image010.gif">

Рис.10.Диаграмма БД

Приложение В

ПРОГРАММА ВВОДА ТЕСТОВЫХ ДАННЫХ

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

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

Приложение Г

РЕАЛИЗАЦИЯ ЗАПРОСОВ НА ЯЗЫКЕ SQL

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

SELECT AM.AM_CODE "KOD"AMAM.AM_COLOR='WHITE'

Рис. 11

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

SELECT AM_MARK.MARK_NAME 'MAPKA'AM_MARKAM_MARK.COUNTRY_CODE LIKE 2

Рис. 12

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

SELECT DRIVERS.DRIVER_FIO "FIO"DRIVERSDRIVERS.DRIVER_FIO LIKE 'GRISHIN%'

Рис. 13

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

SELECT S.*AM S, DRIVERS IS.AM_DRIVER_CODE=I.DRIVER_CODE AND I.DRIVER_CATEGORY='B' AND S.AM_COLOR='WHITE'

Рис. 14

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

SELECT S.*, I.AM_BIRTHDATE "BbIIIYCK"JACKED_CARS S, AM IS.JC_CODE=I.AM_CODE AND I.AM_REGISTRATION_DATE>'10.10.1991'

Рис. 15

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

SELECT S.*JACKED_CARS S, AM IS.JC_CODE=I.AM_CODE AND S.JC_JACKDATE>'01.01.2003'

Рис. 16

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

SELECT S.*JACKED_CARS S, AM IS.JC_CODE=I.AM_CODE AND S.JC_JACKDATE>'01.01.2003' AND I.AM_COLOR='WHITE'

Рис. 17

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

SELECT S.*JACKED_CARS S,AM I, DRIVERS OS.JC_AM_CODE=I.AM_CODE AND S.JC_JACKDATE>'01.01.2003' AND I.AM_DRIVER_CODE=O.DRIVER_CODE AND O.DRIVER_CATEGORY='B'

Рис. 18

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

SELECT D.*DRIVERS D, AM AD.DRIVER_CODE=A.AM_DRIVER_CODE AND A.AM_MARK_CODE=3

Рис. 19

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

SELECT D.*DRIVERS D, AM AD.DRIVER_CODE=A.AM_DRIVER_CODE AND A.AM_MARK_CODE=3 AND A.AM_COLOR = 'WHITE'

Рис. 20