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

Федеральное агентство по образованию Российской Федерации

Государственное образовательное учреждение высшего профессионального образования

«Ивановский государственный химико-технологический университет»

Кафедра информационных технологий

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

К КУРСОВОМУ ПРОЕКТУ

по дисциплине Управление данными

Тема: Проектирование базы данных и разработка приложения «Туристическая фирма» средствами СУБД Firebird и Borland Delphi

Студент Синогина А.И.

Курс 3 Группа 42

Руководитель Марчук Н.А.

г. Иваново, 2009 г.



Содержание

Задание на курсовой проект

Введение

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

2. Структура базы данных

3. Содержание заполненных таблиц

4. ИАС «Туристическая фирма»

4.1 Меню соединения с базой данных

4.2 Главное меню приложения

4.3 Справочники

4.4 Регистрация новой путевки

4.5 Подбор путевки по параметрам

4.6 Анализ работы фирмы

4.7 Справки по проданным путевкам

4.8 Общие сведения

Заключение

Список используемой литературы

Приложение



Задание на курсовой проект

Описание предметной области

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

Работа с клиентами в вашей компании организована следующим образом: у каждого клиента, пришедшего к вам, собираются некоторые стандартные данные - фамилия, имя, отчество, адрес, телефон. После этого сотрудники выясняют у клиента, где он хотел бы отдыхать. При этом ему демонстрируются различные варианты, включающие страну проживания, особенности местного климата, имеющиеся отели разного класса. Наряду с этим обсуждается возможная длительность пребывания и стоимость путевки. В случае если удалось договориться и найти для клиента приемлемый вариант, вы регистрируете факт продажи путевки (или путевок, если клиент покупает сразу несколько путевок), фиксируя дату отправления. Иногда вы решаете предоставить клиенту некоторую скидку.

Таблицы

Маршруты (Код маршрута, Страна, Климат, Длительность, Отель, Стоимость).

Путевки (Код маршрута, Код клиента, Дата отправления, Количество, Скидка).

Клиенты (Код клиента, Фамилия, Имя, Отчество, Адрес, Телефон).

Развитие постановки задачи

Теперь ситуация изменилась. Фирма работает с несколькими отелями в нескольких странах. Путевки продаются на одну, две или четыре недели. Стоимость путевки зависит от длительности тура и отеля.

Скидки, которые предоставляет фирма, фиксированы. Например, при покупке более одной путевки предоставляется скидка 5%. Скидки могут суммироваться.

Функции системы.

1. Ведение справочника «Отель»;

2. Ведение справочника «Страна»;

3. Ведение справочника «Маршрут»;

4. Ведение справочника «Клиент»;

5. Оформление факта продажи новой путевки на основе поддерживаемых справочников;

6. Анализ работы туристической фирмы на основе имеющейся в таблицах информации;

7. Поиск и анализ накопленной в базе информации о путевках.



Введение

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

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

СУБД Firebird является свободно распространяемой СУБД. Она может работать на разных операционных системах, в частности GNU/Linux, Microsoft Windows и разнообразных Unix платформах. Свободность распространения позволяет использовать эту СУБД в качестве инструмента, удобного для обучения и разработки небольших учебных проектов баз данных.

В Firebird реализованы средства для работы с триггерами, хранимыми процедурами и вычисляемыми полями, а также поддержка стандарта ANSI SQL. Среди основных преимуществ СУБД Firebird можно выделить многоверсионную архитектуру, что обеспечивает параллельную обработку оперативных и аналитических запросов (поскольку читающие пользователи не блокируют пишущих), компактность и поддержку множества способов доступа к базе данных, включая собственные наборы данных для Delphi, C/C++, классы для ODBC и JDBC.

Для разработки базы данных своего курсового проекта я выбрала именно СУБД Firebird потому, что обладаю некоторым опытом работы с этой СУБД. Также она дает возможность реализовать все заявленные к проекту требования.

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

Для разработки приложения в своем курсовом проекте я использовала среду программирования Borland Delphi. Это достаточно мощное средство объектно-ориентированного программирования. Не смотря на то, что первые версии этого продукта были выпущены давно, он постоянно развивается. Среди основных преимуществ Delphi перед другими языками программирования можно выделить:

-быстрота разработки приложений;

-наращиваемость за счет встраивания новых компонент и инструментов в среду Delphi;

-высокая производительность разработанного приложения;

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



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

В ходе проведенного системного анализа предметной области имеющиеся таблицы были преобразованы и добавлены новые:

Путевка (НомерПутевки, КодТура, ДатаОтъезда, Количество, Скидка, КодКлиента);

Клиент (КодКлиента, ФИО, Адрес, Телефон, СуммаСкидки);

Тур (КодТура, Длительность, Цена, КодСтраны, КодОтеля);

Страна (КодСтраны, Название, Особенности);

Отель (КодОтеля, Название, КлассОтеля, ЦенаПроживания, КодСтраны, Курорт);

Доход (НомерПутевки, Стоимость, Доход, ДатаПродажи);

Скидка (Вид, РазмерСкидки).

По данному описанию предметной области была построена инфологическая модель с помощью пакета ERWin:

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

На основе построенной логической модели была выполнена структурная схема базы данных:



Рис.2. Схема базы данных



2. Структура базы данных

База данных создана в менеджере баз данных InterBase/Fireberd. Данное технологическое решения я выбрала в связи с тем, что уже имею некоторый опыт работы с этим менеджером и знакома с особенностями SQL-диалекта, используемого в Fireberd.

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

1. Таблица Pass (Путевка)

Номер путевки	Код маршрута	Код клиента	Дата отправления	Количество	Скидка

CREATE TABLE PASS (

CODEOFPASS Integer NOT NULL,

DEPARTUREDATE Date,

QUANTITY Integer,

CLIENTCODE Integer NOT NULL,

ROUTECODE Integer NOT NULL,

DISCOUNT Decimal(3,2),

PRIMARY KEY (CODEOFPASS)

);

ALTER TABLE PASS ADD FOREIGN KEY (ROUTECODE) REFERENCES ROUTE(ROUTECODE);

ALTER TABLE PASS ADD FOREIGN KEY (CLIENTCODE) REFERENCES CLIENT(CLIENTCODE);

CREATE INDEX XIF1PASS ON PASS(CLIENTCODE);

CREATE INDEX XIF2PASS ON PASS(ROUTECODE);

CREATE UNIQUE INDEX XPKPASS ON PASS(CODEOFPASS);

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

CREATE GENERATOR GEN_CODEOFPASS;

SET GENERATOR GEN_CODEOFPASS TO 1030

CREATE OR ALTER TRIGGER TR_PASS_CODE_BI FOR PASS

ACTIVE BEFORE INSERT POSITION 1

AS

begin

if (new.CodeOfPass is null)

then new.CodeOfPass = GEN_ID (GEN_CodeOfPass, 1);

end

Значение поля Discount определяется некоторым бизнес-правилом:

Discount = 0,05+discountsum, при количестве купленных путевок не меньше 3;

Discount = 0,1+discountsum, если до даты отправления остается не больше 4 дней;

Discount = 0,03+discountsum, если накопленная клиентом стоимость путевок превышает 50.000 руб.;

Discount = 0 , т.е. скидка на данную путевку не предоставляется.

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

CREATE OR ALTER TRIGGER TR_PASS_DISCOUNT_BI FOR PASS

ACTIVE BEFORE INSERT POSITION 2

AS

declare variable sumcost Numeric(15,2);

declare variable DISCOUNT Numeric(3,2);

declare variable discountsum Numeric(3,2);

begin

select discountsum

from client

where client.CLIENTCODE=new.CLIENTCODE

into :discountsum;

if (new.DEPARTUREDATE-current_date<=4) then

begin

DISCOUNT=0.1;

new.DISCOUNT=:DISCOUNT+ :discountsum;

exit;

end

select sum(cost)

from profit join pass on pass.CODEOfpass=profit.CODEOFPASS

where pass.ClientCode=new.CLIENTCODE

into :sumcost;

if (:sumcost>5000)then

begin

DISCOUNT=0.03;

new.DISCOUNT=:DISCOUNT+ :discountsum;

exit;

end

if (new.QUANTITY>=3) then

begin

DISCOUNT=0.05;

new.DISCOUNT=:DISCOUNT+ :discountsum;

exit;

end

else

begin

DISCOUNT=0;

new.DISCOUNT=:DISCOUNT+ :discountsum;

end

end

При вставке записи в таблицу Pass триггер проверяет выполнение одного из трех условий. При нахождении истинного условия триггер прекращает работу.

Из описания предметной области видно, что данные этой таблицы будут использоваться при заполнении таблицы Profit и пересчете поля DiscountSum таблицы Client. Для обеспечения этих задач были создан триггер, реагирующий на вставку записи в таблицу Pass:

CREATE OR ALTER TRIGGER TR_PASS_SUMDISCOUNT_PROFIT_AI FOR PASS

ACTIVE AFTER INSERT POSITION 0

AS

declare variable price Numeric(15,2);

declare variable cost Numeric(15,2);

declare variable profit Numeric(15,2);

declare variable discountsum Numeric(3,2);

begin

if (new.codeofpass<>0) then

begin

select discountsum from client where CLIENTCODE = new.CLIENTCODE into :discountsum;

if (discountsum<0.3) then

update client

set

DISCOUNTSUM = DISCOUNTSUM+ new.DISCOUNT

where CLIENTCODE = new.CLIENTCODE;

select price from route

where route.ROUTECODE = new.ROUTECODE

into :price ;

cost = :price*new.QUANTITY*(1-new.DISCOUNT);

profit = :cost * 0.15;

insert into profit ( Codeofpass,

Cost,

Profit,

DATEOFSAIL)

values ( new.codeofpass,

:cost,

:profit,

current_date) ; end

2. Таблица Client (Клиент).

Код клиента	ФИО	Адрес	Телефон	Сумма скидки

CREATE TABLE CLIENT (

CLIENTCODE Integer NOT NULL,

FIO Varchar(60),

TEPHONENUMBER Char(11),

DISCOUNTSUM Numeric(3,2),

ADRESS Varchar(60),

PRIMARY KEY (CLIENTCODE)

);

CREATE UNIQUE INDEX XPKCLIENT ON CLIENT(CLIENTCODE);

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



CREATE GENERATOR GEN_CLIENTCODE;

SET GENERATOR GEN_CLIENTCODE TO 1001

CREATE OR ALTER TRIGGER TR_CLIENT_CODE_BI FOR CLIENT

ACTIVE BEFORE INSERT POSITION 0

AS

begin

if (new.ClientCode is null)

then new.ClientCode = GEN_ID ( Gen_ClientCode,1);

new.DISCOUNTSUM = 0; end

3. Таблица Route (Тур)

Код Тура	Стоимость	Код страны	Код отеля	Длительность

CREATE TABLE ROUTE (

ROUTECODE Integer NOT NULL,

COUNTRYCODE Integer NOT NULL,

HOTELCODE Integer NOT NULL,

DURATION Integer,

PRICE Numeric(15,2) NOT NULL,

PRIMARY KEY (ROUTECODE)

);

ALTER TABLE ROUTE ADD CONSTRAINT FK_ROUTE_1 FOREIGN KEY (HOTELCODE) REFERENCES HOTEL(HOTELCODE);

ALTER TABLE ROUTE ADD FOREIGN KEY (COUNTRYCODE) REFERENCES COUNTRY(COUNTRYCODE);

CREATE INDEX IDX_ROUTE_1 ON ROUTE(HOTELCODE);

CREATE INDEX XIF1ROUTE ON ROUTE(COUNTRYCODE);

CREATE UNIQUE INDEX XPKROUTE ON ROUTE(ROUTECODE);

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

CREATE GENERATOR GEN_ROUTECODE;

SET GENERATOR GEN_ROUTECODE TO 43

CREATE OR ALTER TRIGGER TR_ROUTE_CODE_BI FOR ROUTE

ACTIVE BEFORE INSERT POSITION 0

AS

begin

if (new.RouteCode is null)

then new.RouteCode = GEN_ID (GEN_RouteCode, 1);

end

Также для данной таблицы был создан триггер, позволяющий вычислять поле Price на основе значения поля Price таблицы Hotel:

CREATE OR ALTER TRIGGER TR_ROUTE_PRICE_BIU FOR ROUTE

ACTIVE BEFORE INSERT OR UPDATE POSITION 1

AS

declare variable life Numeric(15,2);

begin

select price from hotel

where hotel.HOTELCODE = new.HOTELCODE

into :life;

new.PRICE= :life * new.duration;

end

4. Таблица Country (Страна)

Код страны	Название	Особенности

CREATE TABLE COUNTRY (

COUNTRYCODE Integer NOT NULL,

NAME Varchar(20),

CLIMATE Char(50),

PRIMARY KEY (COUNTRYCODE)

);

CREATE UNIQUE INDEX XPKCOUNTRY ON COUNTRY(COUNTRYCODE);

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

CREATE GENERATOR GEN_COUNTRYCODE;

SET GENERATOR GEN_COUNTRYCODE TO 203

CREATE OR ALTER TRIGGER TR_COUNTRY_CODE_BI FOR COUNTRY

ACTIVE BEFORE INSERT POSITION 0

AS

begin

if (new.CountryCode is null)

then new.CountryCode = GEN_ID (GEN_CountryCode,1);

end

5. Таблица Hotel (Отель)

Код отеля	Название	Класс отеля	Цена проживания	Код страны

CREATE TABLE HOTEL (

HOTELCODE Integer NOT NULL,

NAME Varchar(20),

HOTELKLASS Varchar(20),

COUNTRYCODE Integer NOT NULL,

PRICE Numeric(15,2),

SITY Varchar(20) NOT NULL,

CONSTRAINT PK_HOTEL PRIMARY KEY (HOTELCODE)

);

ALTER TABLE HOTEL ADD CONSTRAINT FK_HOTEL_0 FOREIGN KEY (COUNTRYCODE) REFERENCES COUNTRY(COUNTRYCODE);

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

CREATE GENERATOR GEN_HOTELCODE;

SET GENERATOR GEN_HOTELCODE TO 514

CREATE OR ALTER TRIGGER TR_HOTEL_CODE_BI FOR HOTEL

ACTIVE BEFORE INSERT POSITION 0

AS

begin

if (new.HotelCode is null)

then new.HotelCode = GEN_ID (GEN_HotelCode, 1);

end

6. Таблица Profit (Доход)

Номер путевки	Стоимость	Доход	Дата продажи

CREATE TABLE PROFIT (

CODEOFPASS Integer NOT NULL,

DATEOFSAIL Date NOT NULL,

COST Numeric(15,2),

PROFIT Numeric(15,2)

);

ALTER TABLE PROFIT ADD FOREIGN KEY (CODEOFPASS) REFERENCES PASS(CODEOFPASS);

CREATE INDEX XIF1PROFIT ON PROFIT(CODEOFPASS);

7. Таблица Discount (Скидка)

Вид	Размер скидки

CREATE TABLE DISCOUNT (

DISCOUNTKIND Varchar(50) NOT NULL,

SUMMA Numeric(3,2) NOT NULL

);



3. Содержимое заполненных таблиц

Таблица: Клиент

Таблица: Скидка

Таблица: Маршруты



Таблица: Отель

Таблица: Путевка

Таблица: Прибыль



Таблица: Страна



4. ИАС «Туристическая фирма»

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

4.1 Меню соединения с базой данных

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

Окно приложения реализовано отдельным классом.

unit Connect;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls, Buttons, DataModule, AppEvnts;

type

Tlogin = class(TForm)

Label1: TLabel;

Label2: TLabel;

Label3: TLabel;

edPath: TEdit;

edUser: TEdit;

edPassword: TEdit;

SpeedButton1: TSpeedButton;

OpenDialog1: TOpenDialog;

btOK: TButton;

btCancel: TButton;

procedure btOkClick(Sender: TObject);

procedure SpeedButton1Click(Sender: TObject);

procedure btCancelClick(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

login: Tlogin;

implementation

uses Main;

{$R *.dfm}

procedure Tlogin.btOkClick(Sender: TObject);

var Result: Integer;

begin

Result := DataModule1.ConnectToDB

(edPath.Text, edUser.Text, edPassword.Text);

if Result <> 1

then

begin

ModalResult := mrOk;

Application.CreateForm(Tform2, Form2);

Form2.ShowModal;

end;

DataModule1.IBDatabase1AfterConnect(Sender);

end;

procedure Tlogin.SpeedButton1Click(Sender: TObject);

begin

if OpenDialog1.Execute then

edPath.Text:=OpenDialog1.filename;

end;

procedure Tlogin.btCancelClick(Sender: TObject);

begin

close;

end;

end.

При нажатии кнопки «ОК» происходит соединение с базой данных и отображение главной формы приложения. При нажатии кнопки «Отмена» - выход из приложения.

4.2 Главное меню приложения

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



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

//открытие нужной вкладки

procedure TForm2.tvMenuChange (Sender: TObject; Node: TTreeNode);

begin

PageControl1.ActivePageIndex := Node.ImageIndex - 1;

end;

Содержание меню проекта:

1. Справочники

а) справочник стран

б) справочник отелей

в) справочник туристических маршрутов

г) справочник клиентов

2. Регистрация новой путевки

3. Подбор путевки по параметрам

4. Анализ работы фирмы

а) динамика доходов фирмы

б) анализ продаж

5. Справки по проданным путевкам

а) маршруты купленные на заданный период

б) самые активные клиенты

6. Общие сведения

Рассмотрим подробнее реализацию каждого пункта меню.

4.3 Справочники

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

Загрузка таблиц происходит сразу после успешного соединения с базой данных с помощью процедуры AfterConnect модуля данных.

procedure TDataModule1.IBDatabase1AfterConnect (Sender: TObject);

begin

dsRoute.Active := True;

dsClient.Active := True;

dsPass.Active := True;

dsCountry.Active:=True;

dsProfit.Active:=True;

qSumDiscount.Active:=true;

dsHotel.Active:=True;

end;

Каждой таблице главного окна соответствует два компонента в DataModule'е:

TIBDataSet - служит для непосредственного хранения данных, взятых их таблиц базы данных;

TDataSource - служит для обеспечения связи наборов данных с визуальными компонентами Delphi;

Для управления записями в таблицах на форме присутствует компонент TDBNavigator. Он позволяет добавлять, удалять или изменять записи в таблицах, а также перемещаться по записям.

Для обеспечения этих функций со стороны базы данных для каждого набора данных были написаны запросы на поддержание целостности базы данных (тесты запросов см. Приложение).

На вкладке «Справочник стран» предусмотрена такая функция как расчет средней стоимости маршрута в выбранную страну. Для этого из главной формы вызывается процедура модуля данных SrCost.



//процедура главной формы

procedure TForm2.dbedSrCostChange(Sender: TObject);

begin

DataModule1.SrCost(lcbCountry.text);

end;

//процедура модуля данных

procedure TDataModule1.SrCost(countryname:string);

begin

qSrCost.Active:=False;

qSrCost.ParamByName ('CountryName').AsString:=countryname;

DataModule1.qSrCost.Active:=True;

end;

Код SQL-запроса: firebird delphi туристический фирма

select avg(PRICE)

from ROUTE inner join Country on route.countrycode=country.countrycode

where Name=:CountryName

Результат выполнения запроса:



4.4 Регистрация новой путевки

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

Как видно на скриншоте, этот пункт позволяет добавлять все поля в таблицу Pass. При этом выбор клиента и номера маршрута производится из ниспадающих списков, которые берут данные соответственно из таблиц Client и Route. Для исключения ошибки выбора по ФИО при наличии в базе данных двух клиентов с абсолютно идентичными именами в специальное поле выводится код выбранного клиента.

Код SQL-запроса для вывода кода клиента по ФИО:

select clientcode

from CLIENT

where fio=:fio

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

procedure TForm2.DBNavigator5Click(Sender: TObject; Button: TNavigateBtn);

begin

DataModule1.PassDiscount(lcbFio.text);

DataModule1.PassCost(lcbFio.text);

end;

//процедуры модуля данных

procedure TDataModule1.PassDiscount (fio:string);

begin

qPassDiscount.Active:=False;

qPassDiscount.ParamByName ('fio').AsString:=fio;

DataModule1.qPassDiscount.Active:=True;

end;

procedure TDataModule1.PassCost (FIO:string);

begin

qPassCost.Active:=False;

qPassCost.ParamByName ('Fio').AsString:=Fio;

DataModule1.qPassCost.Active:=True;

end;

Код SQL-запроса для вывода размера скидки на путевку:

select discount

from PASS join profit on pass.codeofpass=profit.codeofpass

where clientcode in (select clientcode from client where fio=:fio)

and dateofsail = current_date

Код SQL-запроса для вывода стоимости путевки:

select cost

from PROFIT join pass on profit.CODEOFPASS=pass.CODEOFPASS

where dateofsail=current_date and

clientcode in (select clientcode from client where fio=:fio)

Результат выполнения:

4.5 Подбор путевки по параметрам

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



Выбор страны для подбора маршрута производится из ниспадающего списка. Нижняя и верхняя границы цен вводятся в текстовые поля. При нажатии кнопки ОК вызывается процедура модуля данных, которая инициирует выполнение соответствующего запроса.

//процедура главной формы

procedure TForm2.btOKClick(Sender: TObject);

begin

DataModule1.SelectPass(edMinPrice.text, edMaxPrice.text,

AnsiUpperCase(countryname.Text));

end;

//процедура модуля данных

procedure TDataModule1.SelectPass(MinPrice, MaxPrice:STRING; countryname: string);

begin

qSelectPass.Active:=False;

qSelectPass.ParamByName

('CountryName').AsString := countryname;

qSelectPass.ParamByName

('MinPrice').AsInteger:= sTRtOiNT(MinPrice);

qSelectPass.ParamByName

('MaxPrice').AsInteger:= StrToInt(MaxPrice);

DataModule1.qSelectPass.Active:= True;

end;

Код SQL-запроса:

select routecode,duration, route.price, hotel.name as HotelName, sity

from route join country on route.COUNTRYCODE=country.COUNTRYCODE

join hotel on route.HOTELCODE=hotel.HOTELCODE

where country.name = :countryname and route.price between :minprice and :maxprice

Результат выполнения:



4.6 Анализ работы фирмы

Анализ работы фирмы включает две возможности: расчет динамики доходов фирмы и анализ продаж. Вкладка для расчета динамики доходов имеет следующий вид:

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

//процедура главной формы

procedure TForm2.btOKanalisClick(Sender: TObject);

begin

with Chart1.Series[0] do

begin

clear;

DataModule1.ProfitDinamic(edYear.text);

DataModule1.qProfitDinamic.First;

while not (DataModule1.qProfitDinamic.Eof) do

begin

AddY(StrToFloat(DataModule1.qProfitDinamic.FieldValues['profit'],

DataModule1.qProfitDinamic.FieldValues['mesyac']);

DataModule1.qProfitDinamic.Next;

end;

end;

end;

//процедура модуля данных

procedure TDataModule1.ProfitDinamic (yearAnalysis: string);

begin

qProfitDinamic.Active:=False;

qProfitDinamic.ParamByName ('YearAnalysis').AsInteger := StrToInt(yearAnalysis);

DataModule1.qProfitDinamic.Active:=True;

end;

Код SQL-запроса:

select extract(month from DATEOFSAIL) mesyac, sum (Profit) profit

from PROFIT

where extract (year from dateofsail)=:yearAnalysis

group by extract(month from DATEOFSAIL)

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

//процедура главной формы

procedure TForm2.btOKanalisClick(Sender: TObject);

begin

with Chart1.Series[0] do

begin

clear;

DataModule1.ProfitDinamic(edYear.text);

DataModule1.qProfitDinamic.First;

while not (DataModule1.qProfitDinamic.Eof) do

begin

AddY(StrToFloat(DataModule1.qProfitDinamic.FieldValues['profit'],

DataModule1.qProfitDinamic.FieldValues['mesyac']);

DataModule1.qProfitDinamic.Next;

end;

end;

end;

//процедура модуля данных

procedure TDataModule1.ProfitDinamic (yearAnalysis: string);

begin

qProfitDinamic.Active:=False;

qProfitDinamic.ParamByName ('YearAnalysis').AsInteger := StrToInt(yearAnalysis);

DataModule1.qProfitDinamic.Active:=True;

end;

Код SQL-запроса:

select extract(month from DATEOFSAIL) mesyac, sum (Profit) profit

from PROFIT

where extract (year from dateofsail)=:yearAnalysis

group by extract(month from DATEOFSAIL)

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

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

//процедура главной формы для кнопки ОК

procedure TForm2.btOKReportClick(Sender: TObject);

begin

DataModule1.SailAnalysis(edYearReport.Text);

end;

//процедура главной формы для кнопки Напечатать отчет

procedure TForm2.btPrintReportClick(Sender: TObject);

begin

DataModule1.SailAnalysis(edYearReport.Text);

Form1.QRLabel3.Caption := edYearReport.Text;

Form1.QuickRep1.Preview;

end;



//процедура модуля данных

procedure TDataModule1.SailAnalysis (YearCode:string);

begin

qSailAnalysis.Active := False;

qSailAnalysis.ParamByName('YearCode').AsInteger := StrToInt(YearCode);

DataModule1.qSailAnalysis.Active := True;

end;

Код SQL-запроса:

select FIO, sum(QUANTITY)quantity, sum(COST)cost

from PASS join client on pass.CLIENTCODE=client.CLIENTCODE

join profit on pass.CODEOFPASS=profit.CODEOFPASS

where extract(year from DATEOFSAIL) = :YearCode

group by fio

Запрос позволяет выбрать данные для построения отчета: ФИО клиента, количество купленных этим клиентом путевок и их общая стоимость. Анализ данных производится за год. Анализируемый год :YearCode запрос получает из приложения.

Вид отчета в режиме конструктора:

Результат выполнения:

Пример построения отчета:

4.7 Справки по проданным путевкам

Этот пункт меню дает пользователю возможность узнать различную справочную информацию. Он имеет два подменю: маршруты, купленные на заданный период и самые активные клиенты.

Первый подпункт позволяет выбрать все маршруты, купленные на заданный период. Вкладка с примером выполнения запроса имеет вид:



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

//процедура главной формы

procedure TForm2.dbedCodeChange(Sender: TObject);

begin

DataModule1.PassClientCode (lcbFio.Text);

end;

//процедура модуля данных

procedure TDataModule1.PassClientCode (FIO:string);

begin

qPassClientCode.Active:=False;

qPassClientCode.ParamByName ('FIO').AsString:=Fio;

DataModule1.qPassClientcode.Active:=True;

end;

Код SQL-запроса:

select clientcode, quantity, pass.departuredate, DURATION, pass.RouteCode

from ROUTE inner join pass on route.ROUTECODE=pass.ROUTECODE

where pass.departuredate between :StartDate and :EndDate

Запрос выполняет поиск маршрутов тех путевок и вывод данных по ним, дата отправления которых лежит в интервале между двумя значениями :startdate и :enddate. Значения этих параметров задаются в приложении в соответствующих полях.

Подпункт меню «Самые активные клиенты» позволяет пользователю ИАС выявить в списке зарегистрированных клиентов тех, которые имеют наибольшую накопленную стоимость купленных путевок за год.

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

//процедура главной формы

procedure TForm2.btOkKlientClick(Sender: TObject);

begin

DataModule1.CoolClient(edYearClient.text);

end;

//процедура модуля данных

procedure TDataModule1.CoolClient (year: string);

begin

qCoolClient.Active:=False;

qCoolClient.ParamByName ('Year').AsInteger:=StrToInt(Year);

DataModule1.qCoolClient.Active:=True;

end;

Код SQL-запроса:

select pass.CLIENTCODE, c.Fio, SUM(PROFIT.COST) AS Summa

from CLIENT c inner join PASS on c.CLIENTCODE = pass.CLIENTCODE

join PROFIT on PASS.CODEOFPASS = PROFIT.CODEOFPASS

where extract(year from profit.DATEOFSAIL)=:year

group by pass.CLIENTCODE, c.fio

HAVING (SUM(PROFIT.COST) = (select max(Summa) from

(select pass.CLIENTCODE, SUM(PROFIT.COST) AS Summa

from PASS join PROFIT on PASS.CODEOFPASS = PROFIT.CODEOFPASS

GROUP BY pass.CLIENTCODE) as tmp))

Запрос вывод краткую информацию о кленте (КодКлиента и ФИО), имеющего максимальную накопленную стоимость путевок, а также сумму купленных этим клиентом путевок. При выполнении запроса анализируются данные за один год. Значение переменной :year вводится из приложения. Внешний вид формы с примером выполнения запроса имеет вид:

4.8 Общие сведения

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

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

//процедура главной формы для выбора дешевых маршрутов

procedure TForm2.Button1Click(Sender: TObject);

begin

DBGrid10.DataSource:= DataModule1.dsourseDeshevie;

DBGrid10.Columns.Items[0].FieldName:='RouteCode';

DBGrid10.Columns.Items[1].FieldName:='Price';

DBGrid10.Columns.Items[0].Title.Caption:='Код маршрута';

DBGrid10.Columns.Items[1].Title.Caption:='Цена';

DataModule1.Deshevie(lcbCountryName.Text);

end;

//процедура главной формы для выбора дорогих путевок

procedure TForm2.Button2Click(Sender: TObject);

begin

DBGrid10.DataSource:= DataModule1.dsourseDorogie;

DBGrid10.Columns.Items[0].FieldName:='RouteCode';

DBGrid10.Columns.Items[1].FieldName:='Price';

DBGrid10.Columns.Items[0].Title.Caption:='Код маршрута';

DBGrid10.Columns.Items[1].Title.Caption:='Цена';

DataModule1.Dorogie(lcbCountryName.Text);

end;

В модуле данных им соответствуют две процедуры:

//на поиск дешевых путевок

procedure TDataModule1.Deshevie(name:string);

begin

qDeshevie.Active:=False;

qDeshevie.ParamByName('Name').AsString:=name;

DataModule1.qDeshevie.Active:=True;

end;

//на поиск дорогих путевок

procedure TDataModule1.Dorogie(name:string);

begin

qDorogie.Active:=False;

qDorogie.ParamByName('Name').AsString:=name;

DataModule1.qDorogie.Active:=True;

end;

Код SQL-запроса на поиск дешевых путевок:

select routecode, price

from ROUTE

where countrycode=

(select countrycode

from country

where name=:name) and

price<(select avg(price) from route

where countrycode=(select countrycode from country where name=:name ))

Код SQL-запроса на поиск дорогих путевок:

select routecode, price

from ROUTE

where countrycode=

(select countrycode

from country

where name=:name) and

price>(select avg(price) from route

where countrycode=(select countrycode from country where name=:name ))

Запрос работает по следующему правилу. Путевка считается дешевой, если ее стоимость ниже средней стоимости всех путевок в данную страну, и дорогой - если ее стоимость превышает среднюю по выбранной стране. Название страны запрос получает из приложения во внутреннюю переменную :name.

Вкладка с выполненным запросом для выбора дешевых путевок имеет вид:

Для выбора дорогих путевок:



В поле Клиенты, не имеющие скидок отображаются Код Клиента и ФИО тех клиентов, сумма скидки которых равна нулю. Таблица активируется при запуске приложения.

Код SQL-запроса:

select clientcode, fio

from CLIENT

where discountsum=0

Запрос выбирает тех клиентов, которые не имеют накопленной скидки.



Заключение

В ходе выполнения курсового проекта были разработаны:

1. Исходные таблицы: Путевка, Клиент, Тур, Страна, Отель, Доход;

2. Триггеры, обеспечивающие автоинкримент полей и вычисление отдельных полей таблиц;

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

4. Запросы к базе данных:

-расчет средней стоимости путевок для выбранной страны;

-вывод размера скидки на только что зарегистрированную путевку;

-вывод стоимости только что купленной путевки;

-подбор туристических маршрутов в определенную страну с заданными пользователем границами цены;

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

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

-вывод информации о маршрутах, купленных на заданный период времени;

-поиск клиентов, имеющих максимальную накопленную стоимость купленных путевок;

-вывод дешевых путевок в выбранную страну;

-вывод дорогих путевок в выбранную страну;

-вывод списка клиентов, не имеющих скидки.

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

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

Приложение дает возможность:

-вводить, изменять и удалять данные о клиентах, туристических маршрутах, странах и отелях, с которыми работает фирма;

-осуществлять регистрацию продажи новой путевки с возможностью подбора туристического маршрута по цене;

-производить поиск выкупленных путевок по дате отправлении;

-проводить ретроспективный анализ работы фирмы по размеру прибыли за год;

-получать общие сведения по стоимости путевок в конкретную страну;

-получать отчет по продажам путевок за год;

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

Во время реализации курсового проекта мной были изучены приемы по разработке баз данных в СУБД Firebird, написанию триггеров к БД и SQL-запросов на добавление/модификацию данных.

Были изучены и освоены способы реализации работы с базами данных в среде программирования Delphi.



Список используемой литературы

1. Баженова И.Ю. Delphi7: самоучитель программиста/ И.Ю. Баженова. - М.: Кудиц-образ, 2003. - 448с.

2. Борри Х. Firebird: руководство разработчика БД/ Х. Борри.- пер.с англ. - СПб.: БХВ-Петербург, 2006. - 1104с.

3. Грофф Дж. SQL: полное руководство/ Дж.Грофф, П. Вайнберг. - пер. с англ. - К.: Издательская группа BHV, 2001. - 816 с.

4. Ратманова И.Д., Железняк Н.В. Проектирование баз данных и разработка приложений в СУБД InterBase/Firebird: учеб.-метод. пособие / ГОУВПО «Ивановский государственный энергетический университет имени В.И. Ленина».? Иваново, 2007.? 116 с.



Приложение

Тексты запросов на поддержание целостности базы данных.

1. Ведение справочника стран.

Добавление новой страны.

insert into COUNTRY

(COUNTRYCODE, NAME, CLIMATE)

values

(:COUNTRYCODE, :NAME, :CLIMATE)

Изменение данных о стране.

update COUNTRY

set

COUNTRYCODE = :COUNTRYCODE,

NAME = :NAME,

CLIMATE = :CLIMATE

where

COUNTRYCODE = :OLD_COUNTRYCODE

Удаление данных о стране.

delete from COUNTRY

where

COUNTRYCODE = :OLD_COUNTRYCODE

2. Ведение справочника отелей.

Добавление сведений о новом отеле.



insert into HOTEL

(NAME, HOTELKLASS, COUNTRYCODE, PRICE, SITY)

values

(:NAME, :HOTELKLASS, :COUNTRYCODE, :PRICE, :SITY)

Изменение сведений об отеле.

update HOTEL

set

NAME = :NAME,

HOTELKLASS = :HOTELKLASS,

COUNTRYCODE = :COUNTRYCODE,

PRICE = :PRICE,

SITY = :SITY

where

HOTELCODE = :OLD_HOTELCODE

Удаление отеля из списка.

delete from HOTEL

where

HOTELCODE = :OLD_HOTELCODE

3. Ведение справочника туристических маршрутов.

Добавление нового туристического маршрута.

insert into ROUTE

(ROUTECODE, COUNTRYCODE, HOTELCODE, DURATION, PRICE)

values

(:ROUTECODE, :COUNTRYCODE, :HOTELCODE, :DURATION, :PRICE)

Изменение данных о туристическом маршруте.

update ROUTE

set

ROUTECODE = :ROUTECODE,

COUNTRYCODE = :COUNTRYCODE,

HOTELCODE = :HOTELCODE,

DURATION = :DURATION,

PRICE = :PRICE

where

ROUTECODE = :OLD_ROUTECODE and

COUNTRYCODE = :OLD_COUNTRYCODE and

HOTELCODE = :OLD_HOTELCODE and

DURATION = :OLD_DURATION and

PRICE = :OLD_PRICE

Удаление данных о туристическом маршруте.

delete from ROUTE

where

ROUTECODE = :OLD_ROUTECODE and

COUNTRYCODE = :OLD_COUNTRYCODE and

HOTELCODE = :OLD_HOTELCODE and

DURATION = :OLD_DURATION and

PRICE = :OLD_PRICE

4. Ведение справочника клиентов.

Добавление нового клиента.

insert into CLIENT

(CLIENTCODE, FIO, TEPHONENUMBER, DISCOUNTSUM, ADRESS)

values

(:CLIENTCODE, :FIO, :TEPHONENUMBER, :DISCOUNTSUM, :ADRESS)

Изменение сведений о клиенте.

update CLIENT

set

CLIENTCODE = :CLIENTCODE,

FIO = :FIO,

TEPHONENUMBER = :TEPHONENUMBER,

DISCOUNTSUM = :DISCOUNTSUM,

ADRESS = :ADRESS

where

CLIENTCODE = :OLD_CLIENTCODE

Удаление данных о клиенте.

delete from CLIENT

where

CLIENTCODE = :OLD_CLIENTCODE

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