数据库习题答案（范明）

第五章

5.4

create table Suppliers(
 Sno char(7) primary key,
 Sname char(10) not null,
 Status char(2),
 Scity char(10)
);
create table Parts(
 Pno char(7) primary key,
 Pname char(10) not null,
 Color char(5),
 Weight real
);
create table Projects(
 Jno char(7) primary key,
 Jname char(10) not null,
 Jcity char(10) 
);
create table SPJ(
 Sno char(7),
 Pno char(7),
 Jno char(7),
 Quantity int not null,
 primary key (Sno,Pno,Jno),
 foreign key (Sno) references Suppliers(Sno)
 on update cascade on delete cascade,
 foreign key (Pno) references Parts(Pno)
 on update cascade on delete cascade,
 foreign key (Jno) references Projects(Jno)
 on update cascade
);

5.5

假设删除的Employee元组Eno=111，则表中所有MgrNo=111的元组都会被删除

5.6

cteate assertion LoanConstraint check(
 not exists(
  select * from Branch B,Loan L where B.BranchName=‘大学路’and   B.BranchName=L.BranchName and assets >= (select sum(amount) from Loan    where BranchName=B.BranchName)
 )
);

5.7

(1)create trigger IEGradesInsert
after insert on SC
referencing new row as nrow
for each row
where(nrow.Sno in (select Sno from Students where Dno='IE'))
begin atomic
 insert IEGrades (
  select S.Sno,Sname,Cname,Grade from Students S,SC,Course C
  where S.Sno=SC.Sno and C.Cno=SC.Cno and SC.Sno=nrow.Sno
 );
end