Проектирование БД «Аэропорт»

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ

РЕСПУБЛИКИ КАЗАХСТАН

Казахский национальный технический университет им. Каныша Сатпаева Институт Информационных Технологий

Кафедра «Вычислительная Техника»

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА К курсовой работе по дисциплине

«Защита и безопасность систем баз данных»

Тема: «Проектирование БД «Аэропорт» «

Алматы, 2011 год

ЗАДАНИЕ На курсовую работу по дисциплине «Защита и безопастность баз данных»

Тема: «Проектирование БД «Аэропорт» «

Задачи работы: информационная поддержка деятельности аэропорта. БД должна осуществлять:

· ведение списка авиакомпаний и их рейсов;

· учёт данных о самолетах, выполняющих рейсы;

· ведение списка пилотов.

· ведение списка диспетчеров аэропорта.

Необходимо предусмотреть:

· составление расписания рейсов;

· поиск пилотов рейса;

· получение списка рейсов авиакомпании;

· выдачу информации по конкретному рейсу;

· получение списка дежурных диспетчеров аэропорта по указанному дню;

Руководитель работы _________

подпись Задание принял к исполнению

«__» ________2011. __________

подпись Дата предоставления готовой работы к защите «___» ________ 2011 г.

СОДЕРЖАНИЕ ЗАДАНИЕ ВВЕДЕНИЕ

1. Информационное проектирование БД

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

1.2 Инфологическая модель БД

2.Реализация БД «Аэропорт»

2.1 Создание таблиц

2.2 Создание представлений

2.3 Создание триггеров

2.4 Создание процедур ЗАКЛЮЧЕНИЕ СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ Приложение, А Приложение B

ВВЕДЕНИЕ

Потоки информации, циркулирующие в мире, который нас окружает, огромны. Во времени они имеют тенденцию к увеличению. Поэтому в любой организации, как большой, так и маленькой, возникает проблема такой организации управления данными, которая обеспечила бы наиболее эффективную работу. Некоторые организации используют для этого шкафы с папками, но большинство предпочитают компьютеризированные способы — базы данных, позволяющие эффективно хранить, структурировать и систематизировать большие объемы данных. И уже сегодня без баз данных невозможно представить работу большинства финансовых, промышленных, торговых и прочих организаций. Не будь баз данных, они бы просто захлебнулись в информационной лавине. Существует много веских причин перевода существующей информации на компьютерную основу. Сейчас стоимость хранения информации в файлах ЭВМ дешевле, чем на бумаге. Базы данных позволяют хранить, структурировать информацию и извлекать оптимальным для пользователя образом. Использование клиент/серверных технологий позволяют сберечь значительные средства, а главное и время для получения необходимой информации, а также упрощают доступ и ведение, поскольку они основываются на комплексной обработке данных и централизации их хранения. Кроме того ЭВМ позволяет хранить любые форматы данных, текст, чертежи, данные в рукописной форме, фотографии, записи голоса и т. д. Для использования столь огромных объемов хранимой информации, помимо развития системных устройств, средств передачи данных, памяти, необходимы средства обеспечения диалога человек — ЭВМ, которые позволяют пользователю вводить запросы, читать файлы, модифицировать хранимые данные, добавлять новые данные или принимать решения на основании хранимых данных. Для обеспечения этих функций созданы специализированные средства — системы управления базами данных (СУБД). Современные СУБД — многопользовательские системы управления базой данных, которые специализируется на управлении массивом информации одним или множеством одновременно работающих пользователей. Современные СУБД обеспечивают — набор средств для поддержки таблиц и отношений между связанными таблицами — развитый пользовательский интерфейс, который позволяет вводить и модифицировать информацию, выполнять поиск и представлять информацию в графическом или текстовом режиме — средства программирования высокого уровня, с помощью которых можно создавать собственные приложения.

1. Информационное проектирование БД

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

Для обеспечения учета компаний, совершающих рейсы будет создана таблица Air_Companies c сущностями — название компании (Company), страна-владелец (Country), уникальный идентификатор компании (ID) — первичный ключ.

Для ведения учета воздушных судов будет создана таблица Aircrafts с сущностями — тип воздушного судна (Type), уникальный идентификатор судна (Aircraft_ID, первичный ключ), компания владелец (Company) (указывается для создания представления и связи с другими таблицами).

Для создания графика дежурств диспетчеров создается таблица Dispatch содержащая в себе сущности-фамилии и имена (Surname, Name) диспетчеров, идентификаторы диспетчеров (ID), дата и время дежурств.

Для создания расписания вылетов создается таблица Sheldule, которая содержит в себе такие основные сущности как — уникальный идентификатор рейса (Rais_ID, первичный ключ), идентификатор воздушного судна (AirCraft_ID), направление перелетов (From, To), дата (Date) и время вылета (Time) и прибытие.

Для ведения учета пилотов, совершающих рейсы создается таблица Workers, включающая в себя сущности — имена и фамилии пилотов (Surname, Name), уникальный идентификатор — паспорт пилота (Passport), отношение его к определенной авиа компании.

1.2 Инфологическая модель БД

На рисунке 1 представлена логическая модель базы данных «Аэропорт»

Рисунок 1 Инфологическая модель

2.Реализация БД «Аэропорт»

2.1 Создание таблиц Для создания таблиц использовались SQL скрипты сгенерированные в ERWIN (см. Приложение В стр 15)

На рисунке 2 представлена структура таблицы Air_Company

Рисунок 2 Структура таблицы Air_Company

Для таблицы Aircfarts будет создана следующая структура, приведенная на рисунке 3

Рисунок 3 Структура таблицы Aircrafts

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

Рисунок 4 Структура таблицы Sheldule

Для таблицы Workers будет создана следующая структура, приведенная на рисунке 5

Рисунок 5 Структура таблицы Workers

Для таблицы Dispatch будет создана следующая структура, приведенная на рисунке 6

Рисунок 5 Структура таблицы Dispatch

2.2 Создание представлений В процессе разработки базы данных «Аэропорт» возникла необходимость создания представлений, для отображения информации о прибывающих и убывающих рейсах, отображения подробной информации о рейсе, поиск пилота на рейсе, представление расписания рейсов.

Представление Arrival рисунок 6 — данное представление отображает список рейсов, прибытие которых ожидается. Представление отображает информацию об идентификаторе рейса, дате и времени его прибытия.

SELECT TOP (100) PERCENT Rais_ID, [From], Date, TimeA

FROM dbo. Sheldule ORDER BY Date, TimeA

Рисунок 6 Представление Arrival

Представление Departure рисунок7 — отображение информации о вылетах.

SELECT TOP (100) PERCENT Rais_ID, [To], Date, TimeD

FROM dbo. Sheldule ORDER BY Date, TimeD

Рисунок 7 Представление Departure

Представление Pilot na reise рисунок 8 — представление отображает список пилотов, совершающих полеты, с указанием уникального номера рейса, типа воздушного судна

SELECT TOP (100) PERCENT dbo.Sheldule.Rais_ID, dbo.Sheldule.Date, dbo.Workers.Surname, dbo.Workers.Name, dbo.Workers.Type_of_Pilot

FROM dbo. Sheldule INNER JOIN

dbo.Aircrafts ON dbo.Sheldule.AirCraft_ID = dbo.Aircrafts.Aircraft_ID INNER JOIN

dbo.Workers ON dbo.Aircrafts.Company = dbo.Workers.ID_of_Company

ORDER BY dbo.Sheldule.Date

Рис8. Представление Pilot na Reise

Рис9. Связи представления Pilot na Reise

аэропорт база данное информационный Представление Raspisanie reisov рисунок 10 — представление отображает расписание вылетов с указанием идентификатора рейса, компании совершающей рейс, даты и времени вылета и прибытия данного рейса.

SELECT dbo.Sheldule.Rais_ID, dbo.Aircrafts.Aircraft_ID AS AirCraft, dbo.Sheldule. From], dbo.Sheldule. To], dbo.Sheldule.Date, dbo.Sheldule.TimeD, dbo.Sheldule.TimeA,

dbo.Air_Companies.Company, dbo. Air_Companies.ID

FROM dbo. Aircrafts INNER JOIN

dbo.Sheldule ON dbo.Aircrafts.Aircraft_ID = dbo.Sheldule.AirCraft_ID INNER JOIN

dbo.Air_Companies ON dbo.Aircrafts.Company = dbo. Air_Companies.ID

Рисунок 10 Представления Raspisanie Reisov

Рисунок 11 Связи представления Raspisanie Reisov

Представление Kompanii i piloti (рис13) — представление отображает принадлежность пилотов к авиакомпаниям.

SELECT dbo. Air_Companies.Company, dbo. Air_Companies.Country,

dbo.Workers.Surname, dbo.Workers.Name, dbo.Workers.Passport,

dbo.Workers.Type_of_Pilot

FROM dbo. Air_Companies INNER JOIN

dbo.Workers ON dbo. Air_Companies.ID = dbo.Workers.ID_of_Company

Рисунок 12 Представление связей Kompanii i piloti

2.3 Создание триггеров Триггер — это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.

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

Триггер TestCompanies, осуществляет проверку наличия компаний, не имеющих воздушных судов. При наличии таковых, триггер удаляет запись о данной компании из таблицы Air_Companies. Программная реализация триггера см. приложение B (стр 15)

Триггер TestPass, осуществляет контроль добавления записи о новом работнике, проверяя уникальность вводимого значения столбца Passport. При совпадении, триггер осуществляет откат транзакции. Программная реализация триггера см. приложение B (стр 15)

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

В разрабатываемой БД была создана процедура NewWorker, которая осуществляет добавление записи о новом сотруднике в таблицу Workers. Код процедуры см в приложении B (стр15)

ЗАКЛЮЧЕНИЕ

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

1.УМК «Защита и безопасность систем баз данных», Алматы: КазНТУ, 2008. — 60с

2. http://www.intuit.ru/department/database/sql/14/

3. http://msdn.microsoft.com/ru-ru/library/ms189799.aspx

Приложение, А Структурная ER-диаграммы создаваемой БД «Аэропорт» и отображающая связи таблиц.

ЕR-диаграмма БД «Аэропорт»

Приложение B

SELECT dbo.Sheldule.Rais_ID, dbo.Aircrafts.Aircraft_ID AS AirCraft, dbo.Sheldule. From], dbo.Sheldule. To], dbo.Sheldule.Date, dbo.Sheldule.TimeD, dbo.Sheldule.TimeA,

dbo.Air_Companies.Company, dbo. Air_Companies.ID

FROM dbo. Aircrafts INNER JOIN

dbo.Sheldule ON dbo.Aircrafts.Aircraft_ID = dbo.Sheldule.AirCraft_ID INNER JOIN

dbo.Air_Companies ON dbo.Aircrafts.Company = dbo. Air_Companies.ID

SELECT TOP (100) PERCENT dbo.Sheldule.Rais_ID, dbo.Sheldule.Date, dbo.Workers.Surname, dbo.Workers.Name, dbo.Workers.Type_of_Pilot

FROM dbo. Sheldule INNER JOIN

dbo.Aircrafts ON dbo.Sheldule.AirCraft_ID = dbo.Aircrafts.Aircraft_ID INNER JOIN

dbo.Workers ON dbo.Aircrafts.Company = dbo.Workers.ID_of_Company

ORDER BY dbo.Sheldule.Date

SELECT dbo. Air_Companies.Company, dbo. Air_Companies.Country, dbo.Workers.Surname, dbo.Workers.Name, dbo.Workers.Passport, dbo.Workers.Type_of_Pilot

FROM dbo. Air_Companies INNER JOIN

dbo.Workers ON dbo. Air_Companies.ID = dbo.Workers.ID_of_Company

SELECT TOP (100) PERCENT dbo.Dispatch.Surname, dbo.Dispatch.ID, dbo.Dispatch.Date, dbo.Dispatch.BeginT, dbo.Dispatch.EndT, dbo.Sheldule.Rais_ID

FROM dbo. Dispatch INNER JOIN

dbo.Sheldule ON dbo.Dispatch.Date = dbo.Sheldule.Date

WHERE (dbo.Dispatch.Date = '01.05.2011')

ORDER BY dbo.Dispatch.BeginT, dbo.Dispatch.Surname

SELECT TOP (100) PERCENT Rais_ID, [To], Date, TimeD

FROM dbo. Sheldule ORDER BY Date, TimeD

SELECT TOP (100) PERCENT Rais_ID, [From], Date, TimeA

FROM dbo. Sheldule ORDER BY Date, TimeA

ALTER TRIGGER Sec ON Workers AFTER INSERT

AS

DECLARE @Pas nvarchar (20)

select @Pas=Passport from inserted

if (select COUNT (*) FROM Workers WHERE Passport=@Pas) > 1

begin

ROLLBACK TRANSACTION

RAISERROR ('Takoi Pasport est', 1,11)

END

ALTER Trigger First ON Air_Companies FOR Delete

AS

DECLARE @ID_C NVARCHAR (50)

Select @ID_C=ID From DELETED

IF (Select Count (*) From Aircrafts WHERE Company=@ID_C)<>0

Begin

ROLLBACK

RAISERROR ('NELZIA UDALIT', 1,11)

END

DELETE FROM Air_Companies WHERE ID='EXP1111'

ALTER procedure NewWorker

@Sn AS nChar (15), @nm AS nChar (15), @Ag AS Char (10), @Pas AS varchar (20), @ID AS nvarchar (10), @Tp AS nvarchar (10)

AS

Insert INTO Workers (Surname, Name, Age, Passport, ID_of_Company, Type_of_Pilot)

VALUES (@Sn,@nm,@Ag,@Pas,@ID,@Tp)

go

EXECUTE NewWorker @Sn='A',@nm='B',@Ag='13',@Pas='A3ITA4151',@ID='da',@Tp='f'