, SQL.
DDL:
1. POSTS ():
create table posts (
p_id numeric(3) primary key,
p_post varchar(30) not null,
p_salary numeric(8,2) not null check(p_salary > 0));
2. ROOMS ():
create table rooms (
r_no numeric(3) primary key,
r_tel varchar(10),
unique(r_no, r_tel));
3. EMPLOYEES ():
create table employees (
e_id numeric(4) primary key,
e_fname varchar(20) not null,
e_lname varchar(30) not null,
e_born date,
e_sex char(1) not null check(e_sex in ('','')),
e_post numeric(3) references posts,
e_room numeric(3),
e_tel varchar(10),
e_inn char(12) not null,
e_passp char(12) not null,
e_org varchar(30) not null,
e_pdate date not null,
e_addr varchar(50),
foreign key(e_room,e_tel) references rooms(r_no,r_tel));
.
, . 6.4.4, GRANT. user1, . CUSTOMERS, ORDERS, ITEMS :
grant insert, update on customers to user1;
grant insert, update, delete on orders to user1;
grant insert, update, delete on items to user1;
BOOKS :
create view goods (id, title, circul, price, rest)
as select b_contract, b_title, b_circul, b_price, b_rest
from books;
grant select on goods to user1;
:
1. (, ):
create view edits as
select b_contract, b_title, b_date
from books
where b_rest is null or b_rest > 0;
2. , :
create view edits (title, fname, lname) as
select b_title, e_fname, e_lname /* */
from books, employees e
where b_edit=e_id and
(b_publ is null or b_publ > sysdate);
union /*sysdate */
select b_title, a_fname, a_lname
from books, employees e, editors d
where b.b_contract=d.b_id and d.e_id=a.e_id and
(b_publ is null or b_publ > sysdate)
order by 1;
3. :
create view edits (title, total) as
select b_title, (circulrest)*priceadvance
from books
where b_rest is null or b_rest > 0;
, ( , ). :
create index e_posts on employees(e_post);
create index b_editors on books(b_edit);
|
|
|
create unique index r_tel on rooms(r_no,r_tel);
. . . . : .
:
1. . / .. . .: , 2002. 688 .
2. : , , / .. . .: , 2001. 304 .
3. . Microsoft Access 2000: . .: , 2001. 416 .
4. . . Access. .: , 2000. - 105 .
5. ., ., Office 97. , , -, 1997
6. .. . / - : , 2002.-115
7. ., ., Access Windows 95 , BHV - -, 1997
8. .. . / - : , 2002.-114 .
9. .. - , 2002.
10. .. . / - :, 2002.-18
:
1. . . , 6- .: . .. - .; . "", 2000
2. . . . - .: , 1999. 240 .: .
3. . . : 2000 . .: , 1998
4. ,, ,. . . 1. - .: , 1985. - 287 .:
[1] ..
