CREATE DOMAIN PKey INTEGER
CHECK (value >0);
CREATE DOMAIN Deleted INTEGER
CHECK (value =0 or value=1);
CREATE TABLE Addresses (
adr_id Pkey NOT NULL,
st_title VARCHAR(30) NOT NULL,
house_N Pkey not null,
App_N int,
Greu_N PKey NOT NULL,
del deleted DEFAULT '0' not null,
PRIMARY KEY (adr_id)
);
CREATE TABLE GREU (
Greu_N PKey NOT NULL,
del deleted DEFAULT '0' not null,
PRIMARY KEY (Greu_N)
);
CREATE TABLE Materials (
code PKey NOT NULL,
title VARCHAR(30) NOT NULL,
ed VARCHAR(5) NOT NULL,
price numeric(10,2) NOT NULL,
del deleted DEFAULT '0' not null,
PRIMARY KEY (code),
CHECK (ed in('шт','м','кв.м','куб.м','кг'))
);
CREATE TABLE Tarifs (
razr PKey NOT NULL,
pay numeric(10,2) not null,
PRIMARY KEY (razr),
CHECK (razr>=3 and razr<=5)
);
CREATE TABLE Workers (
tab_N PKey NOT NULL,
fio VARCHAR(30) NOT NULL,
prof VARCHAR(20) NOT NULL,
razr PKey NOT NULL,
ktu numeric(1,1),
Greu_N PKey NOT NULL,
del deleted DEFAULT '0' not null,
PRIMARY KEY (tab_N),
CHECK (ktu>=0.5 and ktu<=1.5),
CHECK (razr>=3 and razr<=5)
);
CREATE TABLE Workers_Works (
tab_N PKey NOT NULL,
work_ID PKey NOT NULL,
wdate DATE NOT NULL,
adr_id PKey NOT NULL,
fact_time numeric(2,2) NOT NULL,
made numeric(10,2) NOT NULL,
PRIMARY KEY (tab_N, work_ID, adr_id, wdate)
);
CREATE TABLE Works (
work_ID Pkey NOT NULL,
title VARCHAR(30) NOT NULL,
time_lim numeric(10,2) NOT NULL,
ed VARCHAR(5) NOT NULL,
del deleted DEFAULT '0' not null,
PRIMARY KEY (work_ID),
CHECK (ed in('шт','м','кв.м','куб.м'))
);
CREATE TABLE Works_Materials (
work_ID Pkey NOT NULL,
code Pkey NOT NULL,
spend_lim numeric(10,2) NOT NULL,
PRIMARY KEY (work_ID, code)
);
Приложение 2 Целостность базы данных
(обязательное)
Create generator ADRgen;
Set generator ADRgen to 4;
SET TERM ; !!
create procedure Generate
returns(val int)
as
begin
val=GEN_ID(ADRgen, 1);
end !!
Приложение 3 Доступ к данным
(обязательное)
Хранимые процедуры
Create procedure TotalCost (date1 date, date2 date, nakl float)
Returns (total float)
As
Declare variable summ float;
Declare variable fact_time float;
Declare variable ktu float;
Declare variable pay float;
Declare variable made float;
Declare variable spend_lim float;
Declare variable price float;
Begin
total=0;
For
select ktu, fact_time, pay
from Workers_Works, Workers, Tarifs
where (Workers_Works.tab_N=Workers.tab_N) and (Tarifs.razr=Workers.razr) and
(wdate>=:date1) and (wdate<=:date2)
into :ktu, :fact_time, :pay
do
begin
total=:total+:fact_time*:pay*:ktu*0.3+:fact_time*:pay;
end
For
select distinct made, spend_lim, price
from Workers_Works ww, Works_Materials wm, Materials m
where (ww.work_id=wm.work_id) and (wm.code=m.code) and
(wdate>=:date1) and (wdate<=:date2)
into :made, :spend_lim, :price
do
begin
total=:total+:spend_lim*:price*:made;
end
total=:total+:nakl;
suspend;
End !!
Create procedure DirLook (date1 date, date2 date, nakl float, adr integer)
Returns (total float, total_time float)
As
Declare variable fact_time float;
Declare variable ktu float;
Declare variable pay float;
Declare variable made float;
Declare variable spend_lim float;
Declare variable price float;
Begin
total=0;
total_time=0;
For
select ktu,pay,fact_time
from Workers_Works, Workers, Tarifs
where (Workers_Works.tab_N=Workers.tab_N) and (Tarifs.razr=Workers.razr) and
(adr_id=:adr) and (wdate>=:date1) and (wdate<=:date2)
into :ktu, :pay, :fact_time
do
begin
total=:total+:fact_time*:pay*:ktu*0.3+:fact_time*:pay;
end
For
select distinct made, spend_lim, price, fact_time
from Workers_Works ww, Works_Materials wm, Materials m
where (ww.work_id=wm.work_id) and (wm.code=m.code) and
(wdate>=:date1) and (wdate<=:date2) and (adr_id=:adr)
into :made, :spend_lim, :price, :fact_time
do
begin
total_time=:total_time+:fact_time;
total=:total+:spend_lim*:price*:made;
end
total=:total+nakl;
suspend;
End !!
Create procedure DirLookEach (date1 date, date2 date, adr integer)
Returns (works varchar(30), exp float, mater float, tme float, total float)
As
Declare variable wid int;
Declare variable fact_time float;
Declare variable ktu float;
Declare variable pay float;
Declare variable made float;
Declare variable spend_lim float;
Declare variable price float;
Begin
for
select distinct title, work_id
from Workers_Works, Works
where (Workers_Works.work_id=Works.work_id) and
(adr_id=:adr) and (wdate>=:date1) and (wdate<=:date2)
into :works, :wid
do
begin
exp=0;
tme=0;
mater=0;
For
select ktu,pay,fact_time
from Workers_Works, Workers, Tarifs
where (Workers_Works.work_id=:wid)and(Workers_Works.tab_N=Workers.tab_N) and (Tarifs.razr=Workers.razr)
and
(adr_id=:adr) and (wdate>=:date1) and (wdate<=:date2)
into :ktu, :pay, :fact_time
do
begin
exp=:exp+:fact_time*:pay*:ktu*0.3+:fact_time*:pay;
end
For
select distinct made, spend_lim, price, fact_time
from Workers_Works ww, Works_Materials wm, Materials m
where (ww.work_id=:wid) and (ww.work_id=wm.work_id) and (wm.code=m.code)
and
(adr_id=:adr) and (wdate>=:date1) and (wdate<=:date2)
into :made, :spend_lim, :price, :fact_time
do
begin
mater=:mater+:spend_lim*:price*:made;
tme=:tme+fact_time;
end
total=:exp+:mater;
suspend;
end
End !!
Пользователи
Create role ECONOMIST;
Grant all
On Works
To ECONOMIST;
Grant all
On Workers
To ECONOMIST;
Grant all
On GREU
To ECONOMIST;
/* ----------------------------------- BUCHGALTER --------------------------------------- */
Create role BUCHGALTER;
Grant all
On Materials
To BUCHGALTER;
Grant select, update
On Tarifs
To BUCHGALTER;
/* ----------------------------------- PTO --------------------------------------- */
Create role PTO;
Grant all
On ADDRESSES
To PTO;
Grant all
On Workers_Works
To PTO;
Grant all
On Works_Materials
To PTO;
Grant select
On Materials
To PTO;
Grant execute on procedure UpdateParams
To PTO;
/* -----------------------------------DIRECTOR --------------------------------------- */
Create role DIRECTOR;
Grant execute on procedure DirLookEach
To Director;
Grant execute on procedure DirLook
To Director;
Grant execute on procedure TotalCost
To Director;
|