优化学生信息管理：SQL创建与权限分配

create table customers(cid char(4)not null,cname varchar(13),
city varchar(20),
discnt real constraint discnt_max check(discnt<=15),
primary key(cid));
create table orders(ordno integer not null,montha char(3),
cid char(4)not null,aid char(3)not null,pid char(3) not null,
qty integer not null constraint qtyck check(qty>=0),
dollars float default 0.0 constraint dollarsck check(dollars>=0),
primary key(ordno),
constraint cidref foreign key(cid)references customers,
constraint aidref foreign key(aid)references agents,
constraint pidref foreign key(pid)references products);


create trigger discnt_max after insert on customers referencing new as x
for each row when(x.discnt>15)
begin 
raise_application_error(-2003,'invaid discount attempted on insert');
end;


create view cacities(ccity,acity)as
select c.city,a.city from customers c,orders o,agents a 
where c.cid=o.cid and a.aid=o.aid;


grant select,update,insert on orders to public;
grant all privileges on products to public;