Организация удаленного доступа к распределенным базам данных (стр. 16 из 19)

3. Borland InterBase Workgroup Server. Language Reference. - Borland International Inc, 1995 - 234 c.

4. Borland InterBase Workgroup Server. Programmer’s Guide. - Borland International Inc, 1995 - 340 c.

5. Microsoft Online Documentation: Win32 Programmers Reference.

6. R.Barker "CASE* Method - Entity Relationship Modelling". - Oracle Inc., 1990 - 243 c.

7. Биллиг В.А., Мусикаев И.Х. «Visual C++ 4. Книга для программистов». - М.: Издательский отдел «Русская редакция» ТОО «Channel Trading Ltd.» , 1996. - 352 с. ил.

8. Галатенко В. «Информационная безопасность - обзор основных положений: Ч1»: - Информационный бюллетень Jet Info №1/1996.

9. Галатенко В. «Информационная безопасность - обзор основных положений: Ч2»: - Информационный бюллетень Jet Info №2/1996.

10. Галатенко В. «Информационная безопасность - обзор основных положений: Ч3»: - Информационный бюллетень Jet Info №3/1996.

11. Грабер Мартин. “Введение в SQL”. Пер. с англ. - М.: Издательство “ЛОРИ”, 1996. - 375 с., ил.

12. Зубанов Ф. «Windows NT - выбор «профи»». - М.: Издательский отдел «Русская редакция» ТОО «Channel Trading Ltd.» , 1996. - 392 с. ил.

13. Кастер Х. «Основы Windows NT и NTFS». Пер. с англ. - М: Издательский отдел «Русская редакция» ТОО «Channel Trading Ltd.» , 1996. - 440 с. ил.

14. Ладыженский Глеб. «СУБД - коротко о главном» : - Информационный бюллетень Jet Info №3-5/1995.

15. Ларин Л.С., Челдаева Л.А., Гуськова Н.Д."Технико-экономическое обоснование дипломных проектов", Саранск, 1983, 100 с.

16. «Решения Microsoft» - Вып. 4. - М: АООТ «Типография Новости», 1996. 124 с., ил.

17. «Решения Microsoft» - Вып. 5. - М: АООТ «Типография Новости», 1997. 132 с., ил.

18. Рихтер Дж.. «Windows для профессионалов (Программирование в Win32 API для Windows 95 и Windows NT)». Пер. с англ. - М: Издательский отдел «Русская редакция» ТОО «Channel Trading Ltd.» , 1995. - 720 с. ил.

19. Паппас К., Мюррей У.. «Visual C++. Руководство для профессионалов»: пер. с англ. - Спб.: BHV - Санкт-Петербург, 1996. - 912 с., ил.

20. «Сетевые средства Windows NT»: Пер. с англ. - СПб.: BHV - Санкт-Петербург, 1996 - 496 с., ил.

21. Фролов А.В., Фролов Г.В. «Microsoft Visual C++ и MFC». - М: Диалог-МИФИ, 1996 - 288 с., ил.

22. Фролов А.В., Фролов Г.В. «Программирование для Windows NT: Ч2». - М: Диалог-МИФИ, 1997 - 271 с., ил.

23. Янг М. «Mastering Microsoft Visual C++». Пер. с англ.- К.: ВЕК+, М.: ЭНТРОП, 1997. - 704 с., ил.

Приложение 1

SQL-скрипт для генерации базы данных

CREATE GENERATOR genUslPropsKeys;

CREATE GENERATOR genUslProps;

CREATE GENERATOR genPhonesRegions;

CREATE GENERATOR genPhonesStations;

CREATE GENERATOR genPhonesStreets;

CREATE GENERATOR genPhonesBanks;

CREATE GENERATOR genTalksPay;

CREATE GENERATOR genTalks;

CREATE GENERATOR genNach;

CREATE GENERATOR genNachBillings;

CREATE GENERATOR genNachBillDates;

CREATE GENERATOR genNachConstUsl;

CREATE GENERATOR genUslDivisions;

CREATE GENERATOR genUslLgots;

CREATE GENERATOR genUslsKeys;

CREATE GENERATOR genUsls;

CREATE GENERATOR genUslCatKeys;

CREATE GENERATOR genUslCat;

CREATE GENERATOR genPhones;

CREATE GENERATOR genPhonesOwnersKeys;

CREATE GENERATOR genPhonesOwners;

CREATE GENERATOR genSysSettings;

CREATE GENERATOR genPhonesKeys;

CREATE GENERATOR genPlat;

CREATE GENERATOR genPhonesPostStations;

CREATE GENERATOR genSysLog;

CREATE GENERATOR genUslTypes;

CREATE GENERATOR genUslDivisionsKeys;

CREATE DOMAIN CALLTIME_TYPE INTEGER NOT NULL;

CREATE DOMAIN CURR_TYPE FLOAT DEFAULT 0 NOT NULL;

CREATE DOMAIN DATE_TYPE DATE NOT NULL;

CREATE DOMAIN DESCR_TYPE CHAR(32);

CREATE DOMAIN PHONE_TYPE CHAR(7) NOT NULL;

CREATE DOMAIN PROCENT_TYPE FLOAT DEFAULT 100 NOT NULL

CHECK (VALUE BETWEEN 0 AND 300);

CREATE TABLE Nach (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

Usl INTEGER NOT NULL,

Phone INTEGER,

UslSum CURR_TYPE,

NachDate DATE_TYPE,

BillDate DATE_TYPE

);

ALTER TABLE Nach

ADD CONSTRAINT XPKNach PRIMARY KEY (Code);

CREATE TABLE NachBillDates (

Code INTEGER NOT NULL,

BillingDate INTEGER NOT NULL

);

ALTER TABLE NachBillDates

ADD CONSTRAINT XPKBillDates PRIMARY KEY (Code);

CREATE TABLE NachBillings (

Code INTEGER NOT NULL,

Division INTEGER NOT NULL,

Owner INTEGER NOT NULL,

BillDateCode INTEGER NOT NULL

);

ALTER TABLE NachBillings

ADD CONSTRAINT XPKNachBillings PRIMARY KEY (Code);

CREATE TABLE NachConstUsl (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

Usl INTEGER NOT NULL,

Phone INTEGER NOT NULL,

UslSum CURR_TYPE,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

ALTER TABLE NachConstUsl

ADD CONSTRAINT XPKNachConstUsl PRIMARY KEY (Code);

CREATE TABLE Phones (

Code INTEGER NOT NULL,

Street INTEGER NOT NULL,

Owner INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Comment DESCR_TYPE,

PhoneNmb PHONE_TYPE,

InstallDate DATE_TYPE,

RemoveDate DATE_TYPE,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

ALTER TABLE Phones

ADD CONSTRAINT XPKPhones PRIMARY KEY (Code);

CREATE TRIGGER Phones_BUH FOR Phones

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM Phones

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));

END ^

CREATE TRIGGER Phones_BIH FOR Phones

BEFORE INSERT POSITION 0

AS

BEGIN

DELETE FROM Phones

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END ^

CREATE TRIGGER Phones_BDH FOR Phones

BEFORE DELETE POSITION 0

AS

BEGIN

UPDATE Phones

SET EndDate = old.EndDate

WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey));

END ^

CREATE TABLE PhonesBanks (

Code INTEGER NOT NULL,

Name1 DESCR_TYPE,

PMFO CHAR(12) NOT NULL,

Name2 DESCR_TYPE,

ELMFO CHAR(12) NOT NULL,

PlatCount SMALLINT NOT NULL,

Acc1 CHAR(12) NOT NULL,

Acc2 CHAR(12) NOT NULL

);

CREATE INDEX XIEPhonesBanksName ON PhonesBanks

(

Name1,

Name2

);

ALTER TABLE PhonesBanks

ADD CONSTRAINT XPKPhonesBanks PRIMARY KEY (Code);

CREATE TABLE PhonesKeys (

Code INTEGER NOT NULL

);

ALTER TABLE PhonesKeys

ADD CONSTRAINT XPKPhonesKeys PRIMARY KEY (Code);

CREATE TABLE PhonesOwners (

Code INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Name1 DESCR_TYPE,

Name2 DESCR_TYPE,

Category INTEGER NOT NULL,

Bank INTEGER,

Street INTEGER NOT NULL,

PostStation INTEGER,

House CHAR(5),

Corpus CHAR(3),

Flat CHAR(3),

Account CHAR(5),

RS CHAR(9),

INN CHAR(13),

Nmb_Dogov CHAR(6),

Date_Dogov DATE,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

ALTER TABLE PhonesOwners

ADD CONSTRAINT XPKPhonesOwners PRIMARY KEY (Code);

CREATE TRIGGER PhonesOwners_BUH FOR PhonesOwners

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE PhonesOwners

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE PhonesOwners

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM PhonesOwners

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));

END ^

CREATE TRIGGER PhonesOwners_BIH FOR PhonesOwners

BEFORE INSERT POSITION 0

AS

BEGIN

DELETE FROM PhonesOwners

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

UPDATE PhonesOwners

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END ^

CREATE TRIGGER PhonesOwners_BDH FOR PhonesOwners

BEFORE DELETE POSITION 0

AS

BEGIN

UPDATE PhonesOwners

SET EndDate = old.EndDate

WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey));

END ^

CREATE TABLE PhonesOwnersKeys (

Code INTEGER NOT NULL,

InRest CURR_TYPE,

OutRest CURR_TYPE,

NDolg INTEGER NOT NULL

);

ALTER TABLE PhonesOwnersKeys

ADD CONSTRAINT XPKPhonesOwnersKeys PRIMARY KEY (Code);

CREATE TABLE PhonesPostStations (

Code INTEGER NOT NULL,

Name DESCR_TYPE,

Region INTEGER NOT NULL,

PostIndex CHAR(6) NOT NULL,

PostNmb CHAR(6) NOT NULL

);

CREATE UNIQUE INDEX XAKPhonesPostStationsIndex ON PhonesPostStations

(

PostIndex

);

CREATE UNIQUE INDEX XAKPhonesPostStationsPostNmb ON PhonesPostStations

(

PostNmb

);

CREATE INDEX XIEPhonesPostStationsName ON PhonesPostStations

(

Name

);

ALTER TABLE PhonesPostStations

ADD CONSTRAINT XPKPhonesPostStations PRIMARY KEY (Code);

CREATE TABLE PhonesRegions (

Code INTEGER NOT NULL,

Name DESCR_TYPE NOT NULL

);

CREATE INDEX XIEPhonesRegionsName ON PhonesRegions

(

Name

);

ALTER TABLE PhonesRegions

ADD CONSTRAINT XPKPhonesRegions PRIMARY KEY (Code);

CREATE TABLE PhonesStations (

Code INTEGER NOT NULL,

Region INTEGER NOT NULL,

Name DESCR_TYPE NOT NULL

);

CREATE INDEX XIEPhonesStationsName ON PhonesStations

(

Name

);

ALTER TABLE PhonesStations

ADD CONSTRAINT XPKPhonesStations PRIMARY KEY (Code);

CREATE TABLE PhonesStreets (

Code INTEGER NOT NULL,

Station INTEGER NOT NULL,

Region INTEGER NOT NULL,

Name DESCR_TYPE

);

CREATE INDEX XIEPhonesStreetsName ON PhonesStreets

(

Name

);

ALTER TABLE PhonesStreets

ADD CONSTRAINT XPKPhonesStreets PRIMARY KEY (Code);

CREATE TABLE Plat (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

ToUsl INTEGER,

PlatDate DATE_TYPE,

PlatType INTEGER NOT NULL,

DocNmb CHAR(12) NOT NULL

);

ALTER TABLE Plat

ADD CONSTRAINT XPKPlat PRIMARY KEY (Code);

CREATE TABLE SysLog (

Code INTEGER NOT NULL,

TableName CHAR(16) NOT NULL,

OpType INTEGER NOT NULL,

NewData CHAR(64) NOT NULL,

OpDate DATE NOT NULL

);

ALTER TABLE SysLog

ADD CONSTRAINT XPKSysLog PRIMARY KEY (Code);

CREATE TABLE SysSettings (

Code INTEGER NOT NULL,

TimeTalksUsl INTEGER NOT NULL,

NullOwner INTEGER NOT NULL

);

ALTER TABLE SysSettings

ADD CONSTRAINT XPKSysSettings PRIMARY KEY (Code);

CREATE TABLE Talks (

Code INTEGER NOT NULL,

DayCode INTEGER NOT NULL,

Phone INTEGER NOT NULL,

ToPhone INTEGER NOT NULL,

CallTime CALLTIME_TYPE,

PhoneNmb PHONE_TYPE,

HowLong INTEGER NOT NULL,

ToPhoneNmb PHONE_TYPE,

Calculated SMALLINT NOT NULL,

CallDate DATE_TYPE

);

CREATE INDEX XAK1TalksCallDate ON Talks

(

CallDate

);

ALTER TABLE Talks

ADD CONSTRAINT XPKTalks PRIMARY KEY (Code);

CREATE TABLE TalksPay (

Code INTEGER NOT NULL,

Phone INTEGER NOT NULL,

TotalSum CURR_TYPE,

TotalLgotTime CALLTIME_TYPE,

TotalFullTime CALLTIME_TYPE,

TotalTime COMPUTED BY (TotalLgotTime+TotalFullTime),

CallDate DATE_TYPE

);

ALTER TABLE TalksPay

ADD CONSTRAINT XPKTalksPay PRIMARY KEY (Code);

CREATE TABLE UslCat (

Code INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Name DESCR_TYPE,

Parent INTEGER NOT NULL,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

CREATE INDEX XIEUslCatName ON UslCat

(

Name

);

CREATE INDEX XIEUslCatParent ON UslCat

(

Parent

);

ALTER TABLE UslCat

ADD CONSTRAINT XPKUslCat PRIMARY KEY (Code);

CREATE TRIGGER UslCat_BUH FOR UslCat

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE UslCat

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE UslCat

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE UslCat

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE UslCat

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM UslCat

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));