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

База данных создана в менеджере баз данных 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

);