第十三次作业

[5.1]
将Student表中的Sno属性定义为码
分别可用在列级定义主码

create table student
   (sno char(9) primary key,
   sname char(20) not null,
   ssex char(2),
   sage smallint,
   sdept char(20)
   );

或在表级定义主码

create table student
   (sno char(9),
   sname char(20) not null,
   ssex char(2),
   sage smallint,
   sdept char(20),
   primary key(sno)
   );

[5.2]
将SC表中的Sno，Cno属性组定义为码

create table sc
  (sno char(9) not null,
  cno char(4) not null,
  grade smallint,
  primary key (sno,cno)
  );

[5.3]
定义SC中的参照完整性。

create table sc
  (sno char(9) not null,
  cno char(4) not null,
  grade smallint,
  primary key (sno,cno),
  //以上进行定义主码，以下进行引用
   foreign key(sno)references student(sno),
   foreign key(cno)references course(cno)
  );

[5.4]
显式说明参照完整性的违约处理示例

create table sc
  (sno char(9) not null,
  cno char(4) not null,
  grade smallint,
  primary key (sno,cno),
   foreign key(sno)references student(sno),
   on delete cascade      --级联删除SC表中相应的元组
   on update cascade,  --级联更新SC表中相应的元组
   foreign key(cno)references course(cno)
   on delete on action
   --删除course表中的元组造成与SC表不一致时拒绝删除
   on update cascade
   --更新course表中cno时，级联更新SC表中相应的元组
  );

[5.5]在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

create table sc
(
 sno char(9) not null,
 cno char(4) not null,
 grade smallint not null,
 primary key (sno,cno)
);

5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

create table dept
(
 deptno numeric(2),
 dname char(9) unique not null,
 location char(10),
 primary key(deptno)
);

用CHECK短语指定列值应该满足的条件
[5.7]
Student表的Ssex只允许取“男”或“女”。

create table student
(
 sno char(9) primary key,
 sname char(8) not null,
 ssex char(2) check(ssex in('男','女')),
 sage smallint,
 sdept char(20)
);

[5.8]
SC表的Grade的值应该在0和100之间。

create table sc
(
 sno char(9),cno char(4),
 grade smallint check(grade>=0 and grade <=100),
 primary key(sno,cno),
 foreign key(sno) references student(sno),
 foreign key(cno) references course(cno)
);

[例5.9]
当学生的性别是男时，其名字不能以Ms.打头。

create table student
  ( sno char(9),
  sname char(8) not null,
  ssex char(2),
  sage char(20),
  primary key(sno),
  check(ssex='女' or sname not like'Ms.%')
  );

[5.10]
建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。
在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。

create table student
( sno numeric(6) constraint c1 check (sno between 90000 and 99999),
 sname char(20) constraint c2 not null,
 sage numeric(3) constraint c3 check (sage<30),
 ssex char(2) constraint c4 check(ssex in('男','女')),
 constraint studentkey primary key(sno)
);

[5.11]
建立教师表TEACHER，要求每个教师的应发工资不低于3000元，应发工资是工资列Sal与扣除项Deduct之和。

create table teacher
  ( eno numeric(4) primary key,  --列级定义主码
   ename char(10),
   job char(8),
   sal numeric(7,2),
   deduct numeric(7,2),    --此处扣除项为负数
   deptno numeric(2),
   constraint teacherfkey foreign key(deptno) references dept(deptno),
   constraint c1 check(sal+deduct>=3000)
  );

[5.12]
使用ALTER TABLE语句修改表中的完整性限制

alter table student drop constraint c4;

[5.13]
修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40。

alter table student drop constraint c1;
alter table student add constraint c1 check (sno between 90000 and 99999);
alter table student drop constraint c3;
alter table student add constraint c3 check(sage<40):

[例5.21]
当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：SC_U（Sno,Cno,Oldgrade,Newgrade）其中Oldgrade是修改前的分数，Newgrade是修改后的分数。
先创建sc_u表：

create table sc_u
  (  sno char(8),
  cno char(8),
  oldgrade smallint,
  newgrade smallint
  );12345
create trigger sc_t
after update of grade on sc
referencing oldrow as oldtuple,
   newrow as newtuple
for each row
when(newtuple.grade>=1.1*oldtuple.grade)
  insert into sc_u(sno,cno,oldgrade,newgrade)  values(oldtuple.sno,oldtuple.cno,oldtuple.grade,newtuple.grade)
  --报错“after”附近有语法错误。

[5.22]
将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

create trigger student_count
after insert on student
referencing new table as delta
for each statement
 insert into studentinsertlog(numbers)
  select count(*) from delta

[5.23]
定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

relate trigger Insert_Or_Update_Sal 
before
insert OR or on Teacher  
                         /*触发事件是插入或更新操作*/
for each row        /*行级触发器*/
begin                             /*定义触发动作体，是PL/SQL过程块*/
 if (new.Job='教授') AND (new.Sal < 4000) 
 then  new.Sal :=4000;                
 end if;
end;