Додатки Скрипт для створення бази даних

/* створення доменів*/

CREATE DOMAIN dnNum AS SMALLINT

CHECK (VALUE > 0)

NOT NULL;

CREATE DOMAIN dnOwTn AS CHAR(10)

NOT NULL;

CREATE DOMAIN dnAddrs AS CHAR (20)

NOT NULL;

CREATE DOMAIN dnTyp AS CHAR(20)

DEFAULT '1-к. квартира'

CHECK (VALUE IN ('1-к. квартира', '2-к. квартира', 'дім'))

NOT NULL;

CREATE DOMAIN dnRn AS NUMERIC(6,2)

DEFAULT 60

CHECK (VALUE > 0);

CREATE DOMAIN dnDATE AS TIMESTAMP

CHECK (Value <= "TODAY");

/* створення таблиць*/

CREATE TABLE OWNER

( NON DNNUM

OW DNOWTN

ADO DNADDRS

PRIMARY KEY (NON));

CREATE TABLE TENANT

( NTN DNNUM

TN DNOWTN

ADT DNADDRS

PRIMARY KEY (NTN));

CREATE TABLE RENT

( TYP DNTYP

RN DNRN

PRIMARY KEY (TYP));

CREATE TABLE REALTY

( ADR DNADDRS

TYP DNTYP

PRIMARY KEY (ADR));

CREATE TABLE LEASE

( NLEASE DNNUM

NTN DNNUM

NON DNNUM

ADR DNADDRS

LDATE DNDATE

PRIMARY KEY (NLEASE));

/* створення обмежень*/

ALTER TABLE REALTY

ADD CONSTRAINT FK RENT REALTY FOREIGN KEY (TYP) REFERENCES RENT (TYP);

ALTER TABLE LEASE

ADD CONSTRAINT FK LEASE OWNER FOREIGN KEY (NON) REFERENCES OWNER (NON);

ALTER TABLE LEASE

ADD CONSTRAINT FK REALTY LEASE FOREIGN KEY (ADR) REFERENCES REALTY (ADR);

ALTER TABLE LEASE

ADD CONSTRAINT FK LEASE TENANT FOREIGN KEY (NTN) REFERENCES TENANT (NTN);

/*Генератори і тригери*/

create generator owner gen;

create generator tenant gen;

create generator lease gen;

set term ^;

create trigger ownerinsert for owner

before insert

position 0

as begin

new.non=gen id(owner gen,1);

end^

set term ;^

set term ^;

create trigger tenantinsert for tenant

before insert

position 0

as begin

new.ntn=gen id(tenant gen,1);

end^

set term ;^

set term ^;

create trigger leaseinsert for lease

before insert

position 0

as begin

new.nlease=gen id(lease gen,1);

end^

set term ;^

/* Уявлення*/

CREATE VIEW LEASES ( NLEASE, NTN, ADT, NON, ADR, LDATE)

AS

select *

from lease

where ldate>'today'-30

/*Збереженні процедури*/

create procedure GET LEASE DATS (TN Name char(25))

RETURNS (Numb integer, Tn char(25), Typ char(20), Adr char(20), Rn numeric(6,2), Ow char(25), Ado char(20)), LDate Timestamp

AS

begin

for Select lease.NLease, tenant.Tn, rent.Typ, lease.Adr, rent.Rn, Owner.Ow, Owner.Ado, lease.LDate

from lease, Owner, realty, rent, tenant

Where (tenant.Tn=:Tn_Name)

and (Owner.Non=Lease.Non)

and (tenant.Ntn=Lease.Ntn)

and (realty.Adr= lease.Adr)

and (rent.Typ= realty.Typ)

INTO :NUMB : Tn : Typ : Adr : Rn : Ow : Ado : LDate

DO

SUSPEND;

END

create PROCEDURE OWNERS (ADRES CHAR(20))

RETURNS ( FAM CHAR(20), OW ADRES CHAR(29))

AS

begin

select owner.ow, owner.ado

from owner, lease

where lease.non=owner.non and lease.adr=:adres

into :fam :ow_adres;

suspend;

end