数据库实验6 ：完整性

实验目的：

理解SQL Server的实体完整性、参照完整性和用户自定义的完整性；掌握SQL Server中实体完整性的建立，实践违反实体完整性的结果；掌握使用外键以及利用FOREIGN KEY…REFERENCES 子句以及各种约束保证参照完整性的方法；掌握用户自定义约束，实践用户自定义完整性，利用查询分析器用CONSTRAINT、CHECK 等子句保证用户自定义完整性；了解触发器的作用及一般用法，掌握定义触发器方法。

实验内容：

基于实验一创建的教学管理JXGL数据库，实现数据完整性操作。

实验步骤：

一、建立下表，执行

1. 修改S表中S1元组sno为S10

1. 删除S表中S10元组

1. 修改P表中P1元组pno为P10

1. 删除P表中P10元组

1. 删除P表中P1元组

1. 修改J表中J1元组jno为J10

1. 删除J表中J10元组

1. 删除J表中J1元组

二、 在教学管理JXGL数据库中进行如下操作，写出对应的SQL语句：

create database JXGL;

create table student

(Sno char(9) primary key,

Sname char(20) unique,

Ssex char(2),

Sage smallint,

Sdept char(20)

);

insert

into student

values('201215121','李勇','男',20,'CS'),

('201215122','刘晨','女',19,'CS'),

('201215123','王敏','女',18,'MA'),

('201215124','李莉','女',20,'MA'),

('201215125','张立','男',19,'IS'),

('201215126','刘晓意','男',24,'CS');

create table course

(Cno char(4) primary key,

Cname char(20) NOT NULL,

Cpno char(4),

Ccredit smallint,

foreign key (Cpno)references course (Cno)

);

insert

into course(Cno,Cname,Cpno,Ccredit)

values('2','数学',NULL,2),

('6','数据处理',NULL,2),

('4','操作系统','6',3),

('7','PASCAL语言','6',4),

('5','数据结构','7',4),

('1','数据库','5',4),

('3','Design _ Pattern','1',4);

create table SC(

Sno char(9),

Cno char(4),

grade smallint,

primary key(Sno,Cno),

foreign key(Sno)references student (Sno),

foreign key(Cno)references course (Cno)

);

insert into SC values

('201215121','1',92),

('201215121','2',85),

('201215121','3',88),

('201215121','5',NULL),

('201215122','2',90),

('201215122','3',80),

('201215123','5',59),

('201215125','1',50),

('201215125','2',NULL);

1. 建立新的学生表S1，在S1上定义表级主码。

create table s1

(

sno char(9)primary key,

sname char(20),

ssex char(2),

sage smallint,

ssept char(20)

)

1. SC表上定义参照完整性约束后，写语句分别从SC表和Student表去违反参照完整性约束。

delete

from sc

where sno='201215123'

1. 参照课本第5章中的例4定义新的选课表SC1，显式说明参照完整性的违约处理方法，并验证是否正确处理。

 create table sc1

(

  sno char(9),

cno char(4),

grade SMALLINT

PRIMARY key (sno,cno),

FOREIGN key(sno) REFERENCES student(sno)

on UPDATE CASCADE on DELETE CASCADE,

FOREIGN key(cno) REFERENCES course(cno)

on UPDATE CASCADE on DELETE CASCADE,

)

1. 参照课本第5章中的例10定义新的学生表S2，为S2中定义的约束进行命名，并用alter table语句去对表S2中已定义的某个约束进行删除操作。

CREATE TABLE s2

(

sno char(9),

sname char(20),

ssex char(2),

sage SMALLINT,

ssept char(20),

CONSTRAINT C1 PRIMARY key(sno)

)

ALTER TABLE s2

drop CONSTRAINT C1;

1. A建立新的学生表S、课程表C和选课表SC2，表SC2上不用定义外码约束；

create table S3

(

sno char(10) primary key,

sname char(20),

sage smallint

);

create table C

(

cno char(10) primary key,

cname char(20),

ccredit smallint

);

create table SC2

(

sno char(10),

cno char(10),

grade int

);

1. B往S和SC2中输入一些记录，并用select语句观察S和SC2中的记录值；

insert into s3 values('2021001','小凯',23),('2021002','jlq',20);

insert into sc2 values('2021001','1',90),('2021002','2',95);

select * from S3;

select * from SC2;

1. C.定义触发器update_sc，其功能是当学生表S中的学号发生变化时，自动更新选课表SC2中该学生选课记录中的学号；

create trigger update_sc

on s3 

for update

as

if UPDATE(sno)

begin 

update sc2 set sno=i.sno from sc2 sc1,deleted d,inserted i

where sc1.sno=d.sno

end;

1. D.更新学生表S中某个选了课的学生的学号，并用select语句观察SC2中的该生的学号是否自动更新了；

update s3 set sno='2021009' where sno='2021001';

select * from S3;

select * from SC2;

1. 参照C步骤，试着写一下定义级联删除功能的触发器。

CREATE TRIGGER trigger_delete

before delete on s3

for each row

begin

DELETE from sc2 where sc2.sno=s3.id;

end;

1. 思考题：如果SC2表中定义了参照完整性约束，那么如何用触发器方法来实现类似C中的级联更新功能和E中的级联删除功能。

A—D的实现参考下列语句：

create table S

(

sno char(10) primary key,

sname char(20),

sage smallint

);

create table C

(

cno char(10) primary key,

cname char(20),

ccredit smallint

);

create table SC2

(

sno char(10),

cno char(10),

grade int

);

insert into s values('001','shanzhang',10);

insert into sc values('001','1',90);

select * from S;

select * from SC2;

create trigger update_sc

on s

for update

as

if UPDATE(sno)

begin 

update sc set sno=i.sno from sc sc1,deleted d,inserted i

where sc1.sno=d.sno

end ；

update s set sno='002' where sno='001'；

select * from S;

select * from SC2;