Информационная система транспортной компании в архитектуре клиент-сервер

Пояснительная записка к курсовому проекту

по дисциплине

«Базы данных»

Информационная система транспортной компании в архитектуре клиент-сервер

Реферат

Объектом разработки является информационная система, созданная с использованием средств, предоставляемых современными СУБД реляционного типа.

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

Полученные результаты: в процессе разработки проводилось изучение основных средств, предоставляемых средой MS SQL SERVER 2012, и на основе их использования разработана и отлажена программа создания базы данных и серверной части информационной системы. Средствами языка программирования C++ в среде С++ Builder разработано программное обеспечение для информационной системы транспортной компании в архитектуре клиент-сервер.

Графическая часть включает в себя диаграмму базы данных.

Введение

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

1. Информационная система транспортной компании в архитектуре клиент - сервер

В процессе выполнения курсового проекта должно быть разработано и реализовано клиент - серверное приложение для базы данных транспортной компании. Пользовательский интерфейс должен быть как можно более удобным для конечного пользователя. Серверная часть должна содержать в себе таблицы, представления, хранимые процедуры, триггеры и быть разработана на языке Transact SQL. Помимо этого должны быть представлены: структура базы данных и результаты работы программы. В качестве языка разработки была выбрана среда С++ Builder, поскольку она обладает компонентами, позволяющими работать с базами данных.

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

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

1.      Заказчики - код заказчика, Ф.И.О., название организации, адрес, телефон;

2.      Грузы - код груза, наименование, код вида автомобиля для транспортировки;

3.      Автомобили - код автомобиля, код марки, код вида автомобиля, регистрационный номер, номер кузова, номер двигателя, год выпуска, дата последнего ТО;

Взаимосвязь этих объектов происходит в таблице «Рейсы», характеристиками которой являются код рейса, код автомобиля, код заказчика, откуда, куда, дата отправления, дата прибытия, код груза, цена, отметка об оплате, отметка о возврате.

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

В качестве пользователя системы был выбран менеджер. Его информационные задачи: регистрировать рейсы. Он добавляет записи в таблицу «Рейсы».

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

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

На данные наложены ограничения, позволяющие уменьшить вероятность внесения не верной информации. Например: формат телефонного номера «+7(***)-***-**-**», где вместо «*» цифры от 0 до 9. Данные текстового типа ограничены по длине: адрес - 50 символов, ФИО - 40, номер кузова - 17.

 

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

Таблица 1. Заказчики

Имя столбца	Содержательное описание	Тип данных	Размерность	Область допустимых значений	Возможность значения Null	Роль	Пример
Kod_zakazchika	Код заказчика	целый	4	0001-9999	нет	РК	1727
FIO	Ф.И.О	символьный	40	‘А-я’ ‘ ’	нет		Иванова Мария Петровна
NAME_organization	Название организации	символьный	50	‘А-я’ ‘ ’ ‘"’	нет		ООО "Высшая лига"
Adress	Адрес	символьный	50	‘А-я’ ’0-9’ ’-’ ’ ’ ‘,’ ‘.’	нет		Калинина 42-67
Telephone	Телефон	символьный	16	‘0-9’ ’+’ ’-’	нет		+7(962)350-58-55
Profit	Прибыль	целый	7	0-9999999	нет		1234

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

Имя столбца	Содержательное описание	Тип данных	Размерность	Область допустимых значений	Возможность значения Null	Роль	Пример
Kod_vida_avto	Код вида автомобиля	целый	3	001-200	нет	РК	010
NAME_vid_avto	Наименование	символьный	40	‘А-я’ ‘ ’	нет		Цистерна

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

Имя столбца	Содержательное описание	Тип данных	Размерность	Область допустимых значений	Возможность значения Null	Роль	Пример
Kod_marki	Код марки	целый	4	0001- 9999	нет	РК	1736
NAME_marka	Наименование	символьный	20	‘A-z’	нет		Scania

Таблица 4. Виды грузов

Имя столбца	Содержательное описание	Тип данных	Размерность	Область допустимых значений	Возможностьзначения Null	Роль	Пример
Kod_vida_gruza	Код вида груза	целый	3	001-200	нет	PK	111
NAME_vid_gruza	Наименование	символьный	20	‘А-я’ ‘ ’	нет		Мебель
Kod_vida_avto_F	Код вида автомобиля	целый	3	001-200	нет	FK	020

Таблица 5. Грузы

Имя столбца	Содержательное описание	Тип данных	Размерность	Область допустимых значений	Возможность значения Null	Роль	Пример
Kod_gruza	Код груза	целый	3	001- 200	нет	РК	136
NAME_gruz	Наименование	символьный	50	‘А-я’ ‘ ’	нет		ООО Эксперт
Kod_vida_gruza_F	Код вида груза	целый	3	001-200	нет	FK	124

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

Имя столбца	Содержательное описание	Тип данных	Размерность	Область допустимых значений	Возможность значения Null	Роль	Пример
Kod_avto	Код автомобиля	целый	3	001-500	нет	РК	282
Kod_marki_F	Код марки	целый	3	001- 200	нет	FК	136
Kod_vida_avto_F	Код вида автомобиля	целый	3	001-200	нет	FК	176
Reg_nomer	Регистрационный номер	символьный	9	‘A-я’ ‘0-9’	нет		м345оу58
Nomer_kuzova	Номер кузова	символьный	17	‘A-z’ ‘0-9’	нет		AJ2A8J876D2F17273
Nomer_dvigatelya	Номер двигателя	символьный	10	‘A-z’ ‘0-9’	нет		MJ-J4
God_vypuska	Год выпуска	целый	4	2010-2040	нет		2013
Data_poslednego_TO	Дата последнего ТО	дата	10	2013-01-01 - 2040-01-01	нет		2013-08-23

 

Таблица 7. Рейсы

Имя столбца	Содержательное описание	Тип данных	Размерность	Область допустимых значений	Возможностьзначения Null	Роль	Пример
Kod_reisa	Код рейса	целый	5	00001-99999	нет	РК	00282
Kod_avto_F	Код автомобиля	целый	3	001- 500	нет	FК	136
Kod_zakazchika_F	Код заказчика	целый	4	0001-9999	нет	FК	0176
Otkuda	Откуда	символьный	50	‘A-я’ ‘0-9’	нет		г. Пенза, ул. Суворова 121
Kuda	Куда	символьный	50	‘A-я’ ‘0-9’	нет		г. Пенза, ул. Суворова 121
Data_otpravki	Дата отправки	Дата	10	2013-01-01 - 2040-01-01	нет		2013-08-23
Data_pribytiya	Дата прибытия	Дата	10	2013-01-01 - 2040-01-01	нет		2013-08-23
Kod_gruza_F	Код груза	целый	3	001-200	нет	FК	136
Cost	Цена	целый	7	1-9999999	нет		13600
Otmetka_ob_oplate	Отметка об оплате	бит	1	‘0-1’	нет		0
Otmetka_o_vozvrate	Отметка о возврате	бит	1	‘0-1’	нет		0

В таблицах 1 - 7 отображены сущности и их атрибуты. Их взаимосвязь также можно проиллюстрировать схемой БД (см. рис 1).

Рис 1. Схема БД.

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

Для создания базы данных была использована команда CREATE DATABASE Transport, для создания таблиц CREATE TABLE, где Transport - имя БД. Например, создание таблицы «Грузы» будет иметь вид:

TABLE Gruzy

(

Kod_gruza TINYINT PRIMARY KEY CHECK (Kod_gruza>0 AND Kod_gruza<200),_gruz VARCHAR(20) NOT NULL,_vida_gruza_F TINYINT FOREIGN KEY REFERENCES Vidy_gruzov(Kod_vida_gruza) CHECK (Kod_vida_gruza_F>0 AND Kod_vida_gruza_F<200) NOT NULL

)

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

 TABLE имя_БД и DROP TABLE имя_БД.

Добавление поля.

TABLE GruzyVes INT NOT NULL

Модификация поля.

TABLE GruzyCONSTRAINT Ves CHECK ves > 0 AND ves < 5000)

Удаление поля.

TABLE GruzyCOLUMN Ves

Для заполнения таблицы используется команда INSERT.

Into [Gruzy] Values (1,'Кран',1)

.6 Запросы

Запросы на выборку данных относят к двум группам: простые и сложные. В простых запросах выборка идет из одной таблицы, а в сложных может быть задействовано сразу несколько таблиц. Сложные запросы можно реализовать методом вложенных запросов и методом объединения таблиц. В данном проекте используются вложенные запросы.

Пример простого запроса.

.        По Дате отправки рейса определить цены оплаченных рейсов.

Data_otpravki AS [Дата отправки], Cost AS "Цена" FROM Reisy WHERE Data_otpravki BETWEEN getdate()-30 AND getdate()

Примеры сложных запросов:

2.      По Грузу определить вид автомобиля для перевозки.

 NAME_vid_avto AS "Вид автомобиля" FROM Gruzy,Vidy_gruzov, Vidy_avto WHERE Gruzy.Kod_vida_gruza_F=Vidy_gruzov.Kod_vida_gruza AND Vidy_gruzov.Kod_vida_avto_F=Vidy_avto.Kod_vida_avto AND Gruzy.NAME_gruz='Танк'

3.      По Адресу отправки определить название организации.

 NAME_organization AS "Название организации", Kuda AS [Адрес] FROM Zakazchiki, Reisy WHERE Reisy.Kod_zakazchika_F=Zakazchiki.Kod_zakazchika AND Reisy.Kuda LIKE 'г. Заречный%'

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

Представление (VIEW) - тип таблицы, чье содержание выбирается из других таблиц с помощью выполнения запроса. Поскольку значения в этих таблицах меняются, то автоматически, их значения могут быть показаны представлением. Помимо всего, представления способны маскировать от конечного пользователя, как столбцы, так и строки.

Данная работа содержит 2 представления.

Представление ViewReisy используется для более удобного отображения перечня товаров, имеющихся на складе. Оно объединяет в себе данные из четырех таблиц - это «Рейсы», «Заказчики», «Автомобили» и «Грузы» (см. Рис 2).

VIEW ViewReisy AS[Kod_reisa] AS [Код рейса]

,[Reg_nomer] AS [Номер автомобиля]

,[FIO] AS [Заказчик]

,[Otkuda] AS [Откуда]

,[Kuda] AS [Куда]

,[Data_otpravki] AS [Дата отправки]

,[Data_pribytiya] AS [Дата прибытия]

,[NAME_gruz] AS [Груз]

,[Cost] AS [Цена]

,[Kod_avto_F] AS [Код автомобиля]

,[Kod_zakazchika_F] AS [Код заказчика]

,[Kod_gruza_F] AS [Код груза]

,[Otmetka_ob_oplate] AS [Отметка об оплате]

,[Otmetka_o_vozvrate] AS [Отметка о возврате]

FROM [Reisy],[Zakazchiki],[Avto],[Gruzy]Reisy.Kod_zakazchika_F=Zakazchiki.Kod_zakazchikaReisy.Kod_avto_F=Avto.Kod_avtoReisy.Kod_gruza_F=Gruzy.Kod_gruza;

интерфейс триггер серверный запрос

Рис 2. Представление ViewReisy

Представление ViewZakazchiki используется для скрытия структуры бд и для реализации более удобной формы работы (см. Рис 3).

VIEW ViewZakazchiki AS[Kod_zakazchika] AS [Код заказчика]

,[FIO] AS [Заказчик]

,[NAME_organization] AS [Название организации]

,[Adress] AS [Адрес]

,[Telephone] AS [Телефон]

,[Profit] AS [Прибыль][Zakazchiki]

 

Рис 3. Представление ViewZakazchiki

 

1.8 Серверная часть информационной системы

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

 

.8.1 Хранимые процедуры  

Данный курсовой проект содержит 5 хранимых процедур.

. DefaultSizeReisy - используется в клиенте для задания ширины столбов по умолчанию.

CREATE PROCEDURE DefaultSizeReisy

@st0 INT OUTPUT,@st1 INT OUTPUT,@st2 INT OUTPUT,@st3 INT OUTPUT,

@st4 INT OUTPUT,@st5 INT OUTPUT,@st6 INT OUTPUT,@st7 INT OUTPUT,

@st8 INT OUTPUT,@st9 INT OUTPUT,@st10 INT OUTPUT,@st11 INT OUTPUT,

@st12 INT OUTPUT,@st13 INT OUTPUT

@st0=60, @st1=100, @st2=180, @st3=130, @st4=130, @st5=90, @st6=90, @st7=100,

@st8=60, @st9=0, @st10=0, @st11=0, @st12=0, @st13=0;

 

2. SelectReisy - используется для выборки данных из представления ViewReisy.

CREATE PROCEDURE SelectReisy* FROM ViewReisy;

3. SearchReisy - для поиска в представления ViewReisy.

PROCEDURE SearchReisy

@Kod_reisa VARCHAR,@Reg_nomer VARCHAR(9)=''

,@FIO VARCHAR(40)='',@Otkuda VARCHAR(50)=''

,@Kuda VARCHAR(50)='',@Data_otpravki VARCHAR(10)=''

,@Data_pribytiya VARCHAR(10)='',@NAME_gruz VARCHAR(20)=''

,@Cost VARCHAR(10)=''*ViewReisy     CAST([Код рейса] AS VARCHAR(9)) LIKE '%'+@Kod_reisa+'%' [Номер автомобиля] LIKE ('%'+@Reg_nomer+'%')

AND [Заказчик] LIKE '%'+@FIO+'%'[Откуда] LIKE '%'+@Otkuda+'%'[Куда] LIKE '%'+@Kuda+'%' [Дата отправки] LIKE '%'+@Data_otpravki+'%'

AND [Дата прибытия] LIKE '%'+@Data_pribytiya+'%'

AND [Груз] LIKE '%'+@NAME_gruz+'%'CAST([Цена] AS VARCHAR(9)) LIKE ('%'+@Cost+'%')

. DeleteReisy- для удаления рейса по его коду.

 

CREATE PROCEDURE DeleteReisy

@Kod_reisa INTFROM Reisy WHERE Kod_reisa=@Kod_reisa

5. MyStatistics - для вывода статистики по рейсам.

 

CREATE PROCEDURE MyStatistics

@kol_reysov INT OUTPUT,

@avg_reysov INT OUTPUT,

@kol_zakaz INT OUTPUT,

@kol_avto INT OUTPUT,

@kol_reysov_paid INT OUTPUT,

@kol_reysov_not_paid INT OUTPUT,

@kol_reysov=COUNT(*),@avg_reysov=AVG(Cost) FROM Reisy;@kol_zakaz=COUNT(*) FROM Zakazchiki;@kol_avto=COUNT(*) FROM Avto;@kol_reysov_paid=COUNT(*),@cost_reysov_paid=SUM(Cost) FROM Reisy WHERE [Otmetka_ob_oplate]=1 AND [Otmetka_o_vozvrate]=0;@kol_reysov_not_paid=COUNT(*),@cost_reysov_not_paid=SUM(Cost) FROM Reisy WHERE [Otmetka_ob_oplate]=0 AND [Otmetka_o_vozvrate]=0;@kol_reysov_return=COUNT(*),@cost_reysov_return=SUM(Cost) FROM Reisy WHERE [Otmetka_o_vozvrate]=1;

 

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

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

.        Вставка записи в табличку перенаправленная со вставки в представление.

TRIGGER InsertReysViewReisyOF INSERT@Kod_reisa INT,@Reg_nomer VARCHAR(9),@FIO VARCHAR(40)

,@Otkuda VARCHAR(50),@Kuda VARCHAR(50),@Data_otpravki VARCHAR(10)

,@Data_pribytiya VARCHAR(10),@NAME_gruz VARCHAR(20),@Cost INT

,@Otmetka_ob_oplate INT,@Otmetka_o_vozvrate INT@Kod_reisa=[Код рейса],@Reg_nomer=[Номер автомобиля]

,@FIO=[Заказчик],@Otkuda=[Откуда],@Kuda=[Куда]

,@Data_otpravki=[Дата отправки],@Data_pribytiya=[Дата прибытия]

,@Cost=[Цена],@NAME_gruz=[Груз],@Otmetka_ob_oplate=[Отметка об оплате]

,@Otmetka_o_vozvrate=[Отметка о возврате]

FROM inserted;Reisy VALUES(@Kod_reisa,(SELECT Kod_avto FROM Avto WHERE [Reg_nomer]=@Reg_nomer),(SELECT Kod_zakazchika FROM Zakazchiki WHERE [FIO]=@FIO),      @Otkuda,@Kuda,@Data_otpravki,@Data_pribytiya,(SELECT Kod_gruza FROM Gruzy WHERE [NAME_gruz]=@NAME_gruz),@Cost,@Otmetka_ob_oplate,         @Otmetka_o_vozvrate);;

Триггер вызывается при вставке в представление ViewReisy и делает вставку данных в таблицу «Рейсы» (см. Рис 4).

Запрос на вставку:

ViewReisy ([Код рейса],[Номер автомобиля],[Заказчик],[Откуда],[Куда],

[Дата отправки],[Дата прибытия],[Груз],[Цена],[Отметка об оплате],[Отметка о возврате])(123,'с876уа58','Печеркин Александр Андреевич', 'г. Сосновоборск, ул. Сосновая 7','г. Кузнецк, ул. Белинского 151','2013-08-10','2013-09-14','Пропан',58100,1,0)

Рис 4. Таблица до и после работы триггера.

.        Обновление прибыли от клиента при вставке.

TRIGGER EvalProfitReisyINSERT, DELETE, UPDATE@Kod_zakazchika INT,@i INT=1;@i<=(SELECT COUNT(*) FROM inserted) BEGINTOP 1 @Kod_zakazchika=TMP.Kod_zakazchika_F(SELECT TOP (@i) Kod_zakazchika_F FROM inserted ORDER BY Kod_zakazchika_F DESC) AS TMP ORDER BY TMP.Kod_zakazchika_F;(SELECT COUNT(Reisy.Cost) FROM Reisy WHERE @Kod_zakazchika=Reisy.Kod_zakazchika_F AND Reisy.Otmetka_o_vozvrate=0 AND Reisy.Otmetka_ob_oplate=1)>0BEGINZakazchiki SET Profit=(SELECT SUM(Reisy.Cost) FROM Reisy WHERE @Kod_zakazchika=Reisy.Kod_zakazchika_F AND Reisy.Otmetka_o_vozvrate=0 AND Reisy.Otmetka_ob_oplate=1) WHERE Kod_zakazchika=@Kod_zakazchika; END;@i=@i+1;;@i=1;@i<=(SELECT COUNT(*) FROM deleted WHERE Kod_zakazchika_F NOT IN (SELECT Kod_zakazchika_F FROM inserted)) BEGIN

 

SELECT TOP 1 @Kod_zakazchika=TMP.Kod_zakazchika_F(SELECT TOP (@i) Kod_zakazchika_F FROM deleted WHERE Kod_zakazchika_F NOT IN (SELECT Kod_zakazchika_F FROM inserted) ORDER BY Kod_zakazchika_F DESC) AS TMP          ORDER BY TMP.Kod_zakazchika_F;(SELECT COUNT(Reisy.Cost) FROM Reisy WHERE Kod_zakazchika = Reisy.Kod_zakazchika_F AND Reisy.Otmetka_o_vozvrate=0 AND Reisy.Otmetka_ob_oplate=1)>0 BEGINZakazchiki SET Profit=(SELECT SUM(Reisy.Cost) FROM Reisy WHERE @Kod_zakazchika=Reisy.Kod_zakazchika_F AND Reisy.Otmetka_o_vozvrate=0 AND Reisy.Otmetka_ob_oplate=1) WHERE Kod_zakazchika=@Kod_zakazchika;;@i=@i+1;;;

Данный триггер при добавлении в таблицу рейсы пересчитывает прибыль от заказчиков (см. Рис 5).

 

Рис 5. До и после добавления рейса.

 

1.9 Запросы клиентского приложения к базе данных

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

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

1.10 Пользовательский интерфейс клиентского приложения

При проектировке приложения надо учитывать то, что оно будет использоваться продолжительное время пользователем, не знающим языка Transact - SQL. Поэтому, запросы к серверу должны быть прописаны в исходном коде ПО, а сам интерфейс (см. Рис 6 - Рис 10) должен быть максимально интуитивным и не утомлять работника.

Рис 6. Главное окно

Рис 7. Окно добавления рейса

 

Рис 8. Окно статистики

Рис 9. Подробности о заказчике

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

Заключение

В результате выполнения курсового проекты было разработано и реализовано клиент - серверное приложение для менеджера транспортной компании. При помощи приложения можно работать с рейсами. Приложение разработано в среде С++ Builder на языке C++. Серверная часть реализована в SQL SERVER 2012 на языке Transact - SQL. Программа отлажена. Результаты работы проиллюстрированы. Задание выполнено в полном объеме.

 

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

1. А. Я. Архангельский. Программирование в C++ Builder. - Бином, 2010. 1304с.

. И. А. Казакова. Основы языка Transact SQL. - Пенза: Издательство ПГУ, 2013. 163 с.

. А. Г. Бондарь. Interbase и Firebird. - Санкт-Петербург: БХВ-Петербург, 2012. 593 с.

Приложение А

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

Приложение Б

Текст программы создания БД

CREATE DATABASE Transport

Создание таблиц БД (пункт 2)Transport

TABLE Marka_avto

(_marki TINYINT PRIMARY KEY CHECK (Kod_marki>0 AND Kod_marki<200),_marka VARCHAR(20) NOT NULL

)

TABLE Vidy_avto

(_vida_avto TINYINT PRIMARY KEY CHECK (Kod_vida_avto>0 AND Kod_vida_avto<200),_vid_avto VARCHAR(40) NOT NULL

)

TABLE Vidy_gruzov

(_vida_gruza TINYINT PRIMARY KEY CHECK (Kod_vida_gruza>0 AND Kod_vida_gruza<200),_vid_gruza VARCHAR(20) NOT NULL,_vida_avto_F TINYINT FOREIGN KEY REFERENCES Vidy_avto(Kod_vida_avto) CHECK (Kod_vida_avto_F>0 AND Kod_vida_avto_F<200) NOT NULL

)

TABLE Gruzy

(_gruza TINYINT PRIMARY KEY CHECK (Kod_gruza>0 AND Kod_gruza<200),_gruz VARCHAR(20) NOT NULL,_vida_gruza_F TINYINT FOREIGN KEY REFERENCES Vidy_gruzov(Kod_vida_gruza) CHECK (Kod_vida_gruza_F>0 AND Kod_vida_gruza_F<200) NOT NULL

)

TABLE Avto

(_avto SMALLINT PRIMARY KEY CHECK (Kod_avto>0 AND Kod_avto<500),_marki_F TINYINT FOREIGN KEY REFERENCES Marka_avto(Kod_marki) CHECK (Kod_marki_F>0 AND Kod_marki_F<200) NOT NULL,_vida_avto_F TINYINT FOREIGN KEY REFERENCES Vidy_avto(Kod_vida_avto) CHECK (Kod_vida_avto_F>0 AND Kod_vida_avto_F<200) NOT NULL,_nomer CHAR(9) CHECK (Reg_nomer LIKE '[а,е,м,о,с,у][0-9][0-9][0-9][а,е,м,о,с,у][а,е,м,о,с,у][0-9][0-9][0-9," "]') NOT NULL,

Nomer_kuzova VARCHAR(17) CHECK (Nomer_kuzova LIKE '[0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9,A-H,J-N,P,R-Z][0-9][0-9][0-9][0-9]') NOT NULL,

Nomer_dvigatelya VARCHAR(10) NOT NULL,_vypuska SMALLINT CHECK (God_vypuska>=2010 AND God_vypuska<=2040) NOT NULL,_poslednego_TO DATE CHECK (Data_poslednego_TO BETWEEN '2013-01-01' AND '2040-01-01') NOT NULL

)

TABLE Zakazchiki

(_zakazchika SMALLINT PRIMARY KEY CHECK (Kod_zakazchika>0 AND Kod_zakazchika<10000),VARCHAR(40) NOT NULL,_organization VARCHAR(50) NOT NULL,VARCHAR(50) NOT NULL,VARCHAR(16) CHECK (Telephone LIKE '[+][7][(][0-9][0-9][0-9][)][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]') NOT NULL

)

TABLE Reisy

(_reisa SMALLINT PRIMARY KEY CHECK (Kod_reisa>0 AND Kod_reisa<100000),_avto_F SMALLINT FOREIGN KEY REFERENCES Avto(Kod_avto) CHECK (Kod_avto_F>0 AND Kod_avto_F<500) NOT NULL,_zakazchika_F SMALLINT FOREIGN KEY REFERENCES Zakazchiki(Kod_zakazchika) CHECK (Kod_zakazchika_F>0 AND Kod_zakazchika_F<10000) NOT NULL,VARCHAR(50) NOT NULL,VARCHAR(50) NOT NULL,_otpravki DATE CHECK (Data_otpravki BETWEEN '2013-01-01' AND '2040-01-01') NOT NULL,_pribytiya DATE CHECK (Data_pribytiya BETWEEN '2013-01-01' AND '2040-01-01') NOT NULL,_gruza_F SMALLINT FOREIGN KEY REFERENCES Gruzy(Kod_gruza) CHECK (Kod_gruza_F>0 AND Kod_gruza_F<10000) NOT NULL,INT CHECK (Cost>0 AND COST<10000000) NOT NULL,_ob_oplate BIT NOT NULL,_o_vozvrate BIT NOT NULL

)

 

Приложение В

Реализация запросов на языке SQL

Простые запросы.

Простые запросы.

.        По Дате отправки рейса определить цены оплаченных рейсов.Data_otpravki AS Дата, Cost AS "Цена" FROM Reisy WHERE Data_otpravki BETWEEN getdate()-70 AND getdate()

-2.     По Номеру кузова определить дату последнего ТО.Data_poslednego_TO AS "Дата последнего ТО" FROM Avto WHERE Nomer_kuzova = 'H38A8N846D2F13478'

-3.     По ФИО заказчика определить его номер телефона.Telephone AS "Телефон" FROM Zakazchiki WHERE FIO LIKE 'Филиппов%'

-Сложные запросы.

-4.     По Дате отправки груза определить список ФИО заказчиков.Data_otpravki AS Дата, FIO AS "ФИО" FROM Zakazchiki, Reisy WHERE Reisy.Kod_zakazchika_F=Zakazchiki.Kod_zakazchika AND Reisy.Data_otpravki BETWEEN getdate()-70 AND getdate()

-5.     По Регистрационному номеру авто определить марку.NAME_marka AS "Марка" FROM Marka_avto, Avto WHERE Avto.Kod_marki_F=Marka_avto.Kod_marki AND Avto.Reg_nomer='м345оу58'

-6.     По Дате отправления определить виды грузов.Data_otpravki AS Дата, NAME_vid_gruza AS "Вид груза" FROM Reisy,Gruzy,Vidy_gruzov WHERE Reisy.Kod_gruza_F=Gruzy.Kod_gruza AND Gruzy.Kod_vida_gruza_F=Vidy_gruzov.Kod_vida_gruza AND Reisy.Data_otpravki BETWEEN getdate()-70 AND getdate()

-7.     По Грузу определить вид автомобиля для перевозки.NAME_vid_avto AS "Вид автомобиля" FROM Gruzy,Vidy_gruzov, Vidy_avto WHERE Gruzy.Kod_vida_gruza_F=Vidy_gruzov.Kod_vida_gruza AND Vidy_gruzov.Kod_vida_avto_F=Vidy_avto.Kod_vida_avto AND Gruzy.NAME_gruz='Танк'

-8.     По Цене рейса определить виды грузов.NAME_vid_gruza AS "Вид груза", Cost AS Цена FROM Reisy,Gruzy,Vidy_gruzov WHERE Reisy.Kod_gruza_F=Gruzy.Kod_gruza AND Gruzy.Kod_vida_gruza_F=Vidy_gruzov.Kod_vida_gruza AND Reisy.Cost BETWEEN 10000 AND 30000

-9.     По Адресу отправки определить название организации.NAME_organization AS "Название организации",Kuda AS Адрес FROM Zakazchiki, Reisy WHERE Reisy.Kod_zakazchika_F=Zakazchiki.Kod_zakazchika AND Reisy.Kuda LIKE 'г. Заречный%'

-10.   По Дате прибытия определить виды автомобилей.Data_pribytiya AS Дата,NAME_vid_avto AS "Виды автомобилей" FROM Reisy, Avto, Vidy_avto WHERE Reisy.Kod_avto_F=Avto.Kod_avto AND Avto.Kod_vida_avto_F=Vidy_avto.Kod_vida_avto AND Reisy.Data_pribytiya BETWEEN getdate()-70 AND getdate()

Рис 12. Результаты запросов

 

Приложение Г

Реализация клиентского приложения на языке С++

//main.h

//---------------------------------------------------------------------------

#ifndef mainH

#define mainH

//---------------------------------------------------------------------------

#include <Classes.hpp>

#include <Controls.hpp>

#include <StdCtrls.hpp>

#include <Forms.hpp>

#include <ADODB.hpp>

#include <DB.hpp>

#include <DBGrids.hpp>

#include <Grids.hpp>

#include <Buttons.hpp>

//---------------------------------------------------------------------------

#include "Avto.h"

#include "AvtoTable.h"

#include "Zakazchiki.h"

#include "ZakazchikiTable.h"

#include "Gruz.h"

#include "GruzTable.h"

#include "Reisy.h"

#include "MarkaTable.h"

#include "VidyAvtoTable.h"

#include "VidyGruzovTable.h"

#include "Statistic.h"

//---------------------------------------------------------------------------TFormMain : public TForm

{

__published:        // IDE-managed Components*ADOConnection1;*ADOQuery1;*DBGrid1;*DataSource1;*BitBtnDelete;*BitBtnAdd;*BitBtnEdit;*Edit_Kod_reisa;*Edit_Reg_nomer;*Edit_Zakazchik;*Edit_Otkuda;*Edit_Kuda;*Edit_Data_otpravki;*Edit_Data_pribytiya;*Edit_Gruz;*Edit_Cost;*ButtonSearch;*ButtonCancel;*ButtonClear;*BitBtnAvtoTable;*BitBtnZakazchiki;*BitBtnGruzTable;*BitBtnVidyAvtoTable;*BitBtnMarkaTable;*BitBtnVidyGruzivTable;*ADOStoredProcDefaultSize;*BitBtn1;__fastcall DBGrid1DrawColumnCell(TObject *Sender, const TRect &Rect, int DataCol,*Column, TGridDrawState State);__fastcall DBGrid1CellClick(TColumn *Column);__fastcall ButtonSearchClick(TObject *Sender);__fastcall ButtonCancelClick(TObject *Sender);__fastcall ButtonClearClick(TObject *Sender);__fastcall BitBtnDeleteClick(TObject *Sender);__fastcall BitBtnAddClick(TObject *Sender);__fastcall BitBtnEditClick(TObject *Sender);__fastcall BitBtnAvtoTableClick(TObject *Sender);__fastcall BitBtnZakazchikiClick(TObject *Sender);__fastcall BitBtnGruzTableClick(TObject *Sender);__fastcall BitBtnMarkaTableClick(TObject *Sender);__fastcall BitBtnVidyAvtoTableClick(TObject *Sender);__fastcall Edit_Kod_reisaKeyDown(TObject *Sender, WORD &Key, TShiftState Shift);__fastcall BitBtnVidyGruzivTableClick(TObject *Sender);__fastcall BitBtn1Click(TObject *Sender);

:        // User declarations*defaultSize,n_defaultSize;__fastcall DBGrid_Default_setting();:             // User declarations

__fastcall TFormMain(TComponent* Owner);

};

//---------------------------------------------------------------------------PACKAGE TFormMain *FormMain;

//---------------------------------------------------------------------------

#endif

 

//main.cpp

//---------------------------------------------------------------------------

#include <vcl.h>

#pragma hdrstop

#include "main.h"

//---------------------------------------------------------------------------

#pragma package(smart_init)

#pragma resource "*.dfm"*FormMain;

//---------------------------------------------------------------------------

__fastcall TFormMain::TFormMain(TComponent* Owner)

: TForm(Owner)

{->Connected=1;->Active=1;

// 0-й параметр ReturnValue>Parameters->Refresh();_defaultSize=ADOStoredProcDefaultSize->Parameters->Count-1;(int i=0;i<n_defaultSize+1;i++)

{>Parameters->Items[i]->Value=0;

}>ExecProc();=new int[n_defaultSize];(int i=0;i<n_defaultSize;i++)

{[i]=ADOStoredProcDefaultSize->Parameters->Items[i+1]->Value;

}ADOStoredProcDefaultSize;_Default_setting();

}

//---------------------------------------------------------------------------__fastcall TFormMain::DBGrid1DrawColumnCell(TObject *Sender, const TRect &Rect,DataCol, TColumn *Column, TGridDrawState State)

{(Column->Field->DataSet->FieldByName("Отметка об оплате")->AsBoolean==0)

{->Canvas->Brush->Color = clSilver;

}

{->Canvas->Brush->Color = (TColor)RGB(190,255,150);

}

(Column->Field->DataSet->FieldByName("Отметка о возврате")->AsBoolean==1)

{->Canvas->Brush->Color = (TColor)RGB(250,200,90);

}

(Column->FieldName=="Заказчик" || Column->FieldName=="Номер автомобиля" || Column->FieldName=="Груз")

{->Canvas->Font->Color = clBlue;->Canvas->Font->Style = TFontStyles() << fsUnderline;

}

->DefaultDrawColumnCell(Rect, DataCol, Column, State);

}

//---------------------------------------------------------------------------__fastcall TFormMain::DBGrid1CellClick(TColumn *Column)

{(Column->FieldName=="Номер автомобиля")

{*Form2=new TForm2(this,ADOConnection1,3,ADOQuery1->Fields->Fields[9]->AsInteger);->ShowModal();Form2;

}if(Column->FieldName=="Заказчик")

{*Form3=new TForm3(this,ADOQuery1->Fields->Fields[10]->AsInteger,ADOConnection1);->ShowModal();Form3;

}if(Column->FieldName=="Груз")

{*Form4=new TForm4(this,ADOQuery1->Fields->Fields[11]->AsInteger,ADOConnection1);->ShowModal();Form4;

}

}

//---------------------------------------------------------------------------__fastcall TFormMain::ButtonSearchClick(TObject *Sender)

{->Close();->SQL->Clear();->SQL->Add(System::String("")+

"EXEC       SearchReisy '"+_Kod_reisa->Text+"','"+_Reg_nomer->Text+"','"+_Zakazchik->Text+"','"+_Otkuda->Text+"','"+_Kuda->Text+"','"+_Data_otpravki->Text+"','"+_Data_pribytiya->Text+"','"+_Gruz->Text+"','"+_Cost->Text+"'"

);->Open();_Default_setting();

}

//---------------------------------------------------------------------------__fastcall TFormMain::DBGrid_Default_setting()

{(int i=0;i<n_defaultSize;i++)

{(defaultSize[i]) DBGrid1->Columns->Items[i]->Width=defaultSize[i];DBGrid1->Columns->Items[i]->Visible=0;

}

}

//---------------------------------------------------------------------------__fastcall TFormMain::ButtonCancelClick(TObject *Sender)

{->Close();->SQL->Clear();->SQL->Add("EXEC SelectReisy");->Open();_Default_setting();

}

//---------------------------------------------------------------------------__fastcall TFormMain::ButtonClearClick(TObject *Sender)

{_Kod_reisa->Text="";_Reg_nomer->Text="";_Zakazchik->Text="";_Otkuda->Text="";_Kuda->Text="";_Data_otpravki->Text="";_Data_pribytiya->Text="";_Gruz->Text="";_Cost->Text="";

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnDeleteClick(TObject *Sender)

{kod_reisa=ADOQuery1->Fields->Fields[0]->AsInteger;->Close();->SQL->Clear();->SQL->Add(System::String("")+"EXEC DeleteReisy "+kod_reisa);->ExecSQL();->SQL->Clear();->SQL->Add("EXEC SelectReisy");->Open();_Default_setting();

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnAddClick(TObject *Sender)

{*Form5=new TForm5(this,ADOConnection1,1);->ShowModal();Form5;->Close();->SQL->Clear();->SQL->Add("EXEC SelectReisy");->Open();_Default_setting();

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnEditClick(TObject *Sender)

{*Form5=new TForm5(this,ADOConnection1,2,ADOQuery1->Fields->Fields[0]->AsInteger);->ShowModal();Form5;->Close();->SQL->Clear();->SQL->Add("EXEC SelectReisy");->Open();_Default_setting();

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnAvtoTableClick(TObject *Sender)

{*Form6=new TForm6(this,ADOConnection1);

Form6->ShowModal();Form6;

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnZakazchikiClick(TObject *Sender)

{*Form7=new TForm7(this,ADOConnection1);

Form7->ShowModal();Form7;

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnGruzTableClick(TObject *Sender)

{*Form8=new TForm8(this,ADOConnection1);->ShowModal();Form8;

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnMarkaTableClick(TObject *Sender)

{*Form9=new TForm9(this,ADOConnection1);->ShowModal();Form9;

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnVidyAvtoTableClick(TObject *Sender)

{*Form10=new TForm10(this,ADOConnection1);->ShowModal();Form10;

}

//---------------------------------------------------------------------------__fastcall TFormMain::Edit_Kod_reisaKeyDown(TObject *Sender, WORD &Key, TShiftState Shift)

{(Key==13)

{(Sender);

}

}

//---------------------------------------------------------------------------__fastcall TFormMain::BitBtnVidyGruzivTableClick(TObject *Sender)

{*Form11=new TForm11(this,ADOConnection1);->ShowModal();Form11;

}

//---------------------------------------------------------------------------

__fastcall TFormMain::BitBtn1Click(TObject *Sender)

{*Form=new TFormStat(this,ADOConnection1);

Form->ShowModal();Form;

}

//---------------------------------------------------------------------------

Приложение Д

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

Рис 13 Результат работы процедуры SearchReisy с параметром ФИО «на»

 

EXEC SelectReisy

Рис 14. Результат работы процедуры SelectReisy

 

EXEC         DefaultSizeReisy

@st0 = @st0 OUTPUT,@st1 = @st1 OUTPUT,@st2 = @st2 OUTPUT,

@st3 = @st3 OUTPUT,@st4 = @st4 OUTPUT,@st5 = @st5 OUTPUT,

@st6 = @st6 OUTPUT,@st7 = @st7 OUTPUT,@st8 = @st8 OUTPUT,

@st9 = @st9 OUTPUT,@st10 = @st10 OUTPUT,@st11 = @st11 OUTPUT,

@st12 = @st12 OUTPUT, @st13 = @st13 OUTPUT

Рис 15. Результат работы процедуры DefaultSizeReisy

EXEC MyStatistics

@kol_reysov = @kol_reysov OUTPUT,

@avg_reysov = @avg_reysov OUTPUT,

@kol_zakaz = @kol_zakaz OUTPUT,

@kol_avto = @kol_avto OUTPUT,

@kol_reysov_paid = @kol_reysov_paid OUTPUT,

@kol_reysov_not_paid = @kol_reysov_not_paid OUTPUT,

@kol_reysov_return = @kol_reysov_return OUTPUT,

@cost_reysov_paid = @cost_reysov_paid OUTPUT,

@cost_reysov_not_paid = @cost_reysov_not_paid OUTPUT,

@cost_reysov_return = @cost_reysov_return OUTPUT

 

Рис 16. Результат работы процедуры MyStatistic

EXEC DeleteReis 5

 

Рис 17. Результат работы процедуры DeleteReis с параметром 5