实验3 数据完整性

实验3 数据完整性

实验目的

熟悉通过SQL对数据进行完整性机制：了解DBMS的完整性检查和违约处理。 实验平台

SQL Serve查询编辑器。 实验学时：4 实验内容和要求

1、完整性实现：使用SQL对数据进行完整性控制（三类完整性、CHECK短语、CONSTRAIN子句、触发器）。并用实验证实，当操作违反了完整性约束条件时，DBMS是如何处理的。

2、触发器的实现

一、三类完整性的实现

对系表Dept、学生表Student、教师表Teacher、课程表Course、SC表，教师授课表TC，实现规定的完整性，并验证，当操作违反了完整性约束条件时，DBMS如何处理。

1、 Dept表

? DEPTNO CHAR（2） ?

DNAME VARCHAR（20）

? 实体完整性：DEPTNO为主码； ? 用户定义完整性

① DEPTNO的取值为01至99；

② DNAME取值非空且唯一；

CREATE TABLE Dept (

DEPTNO CHAR(2) CHECK(DEPTNO LIKE'[0-9][0-9]'AND DEPTNO NOT LIKE '00') PRIMARY KEY,

DNAME VARCHAR(20)UNIQUE NOT NULL );

2、Student表

? SNO CHAR（10） ? ? ?

SNAME VARCHAR（8） SEX CHAR（2） DEPTNO CHAR（2）

? 实体完整性：SNO为主码；

? 参照完整性：DEPTNO为外码，并实现级联更新和级联删除操作； ? 用户定义完整性

① SNO取值为10级的学号取值范围，如2010081101等。 ② SNAME 取值非空；

③ SEX的取值只能为男或女； CREATE TABLE Student

(

SNO CHAR(10) CHECK(SNO LIKE'20100811[0-9][0-9]'AND SNO NOT LIKE '2010081100') PRIMARY KEY, SNAME VARCHAR(8)NOT NULL,

SEX CHAR(2)CHECK(SEX='男'OR SEX='女'), DEPTNO CHAR(2),

FOREIGN KEY(DEPTNO)REFERENCES Dept(DEPTNO) ON DELETE CASCADE ON UPDATE CASCADE, );

3、Teacher表

? TNO CHAR（3） ? ?

? 实体完整性：TNO为主码；

? 参照完整性：DEPTNO为外码，并实现级联更新和级联删除操作； ? 用户定义完整性

① TNO取值为08开头的四位数字字符串； ② TNAME取值非空； CREATE TABLE Teacher

(

TNO CHAR(3)CHECK(TNO LIKE'08[0-9][0-9]')PRIMARY KEY, TNAME VARCHAR(8)NOT NULL, DEPTNO CHAR(2),

FOREIGN KEY(DEPTNO)REFERENCES Dept(DEPTNO) ON DELETE CASCADE ON UPDATE CASCADE );

TNAME VARCHAR（8） DEPTNO CHAR（2）

4、Course表

? CNO CHAR（4） ? TNO CHAR（3） ?

CNAME VARCHAR（20） ? CREDIT SNALLINT ? 实体完整性：CNO； ? 用户定义完整性

① CNAME取值非空；

② CREDIT取值为1至10以内的数字；

? 参照完整性： TNO为外码，并实现级联更新和级联删除操作； CREATE TABLE Course

(

CNO CHAR(4)PRIMARY KEY, TNO CHAR(3),

FOREIGN KEY(TNO)REFERENCES Teacher(TNO) ON DELETE CASCADE ON UPDATE CASCADE,

CNAME VARCHAR(20)NOT NULL,

CREDIT SMALLINT CHECK(CREDIT>0 AND CREDIT<10) );

5、SC表

? SNO CHAR（10） ? ?

CNO CHAR（4） GRADE SMALLINT

? 实体完整性：(SNO，CNO)为主码；

? 参照完整性：SNO外码、CNO外码，都实现级联删除和级联更新操作； ? 用户定义完整性

GRADE取值在0到100之间。

CREATE TABLE SC

(

SNO CHAR (10), CNO CHAR (4),

GRADE SMALLINT CHECK(GRADE>0 AND GRADE<=100), PRIMARY KEY(SNO,CNO),

FOREIGN KEY(SNO)REFERENCES Student(SNO) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY(CNO)REFERENCES Course(CNO) ON DELETE NO ACTION ON UPDATE NO ACTION, );

二、触发器的实现

1、修改Student表，增加属性CNT，SMALLINT型，表示学生的选课门数，设置CNT的默认值为0。增加属性SZK，SMALLINT型，表示素质选修课学分。 在Student表上创建以下触发器。

alter table Student add CNT SMALLINT;

设置CNT的默认值为0：

alter table Student add SZK SMALLINT;

（1）创建触发器Trig1，实现在SC表里添加一条数据时，Student表里对应的学生的CNT值自动增加1。

CREATE TRIGGER Trig1

ON SC FOR INSERT AS BEGIN UPDATE Student set CNT=CNT+1

where Student.sno in

(select sno from inserted) end

（2）创建触发器Trig2，实现在SC表里删除一条数据时，Student表里对应的学生的CNT值自动减1。

CREATE TRIGGER Trig2

ON SC FOR delete AS BEGIN UPDATE Student set CNT=CNT-1

where Student.sno in

(select sno from inserted) end

（3）创建触发器Trig3，实现不允许修改SC表的Sno和Cno。 create trigger tirg3

on sc for update as

if update(sno) or update(cno) rollback transaction

（4）SZK取值不能大于8，如果大于8，自动修改为8

CREATE TRIGGER TIRG4

ON Student

FOR INSERT,UPDATE AS

DECLARE @T char(8)

SELECT @T=SZK from inserted IF( @T>8) UPDATE Student SET SZK=8

WHERE SNO IN(SELECT SNO FROM inserted)

2、 修改Teacher表，增加属性ZC，char(5)，表示教师的职称，增加属性GZ，money，表

示教师的工资。在Teacher表上创建触发器，实现当职称为副教授时，GZ如果低于3000，则自动修改为4000。

alter table Teacher add GZ CHAR(5); alter table Teacher add MONEY INT;

create trigger GONGZHI on teacher

for insert,update as

declare @g numeric(7,2) declare @z char(8)

select @z=MONEY from inserted select @g=GZ from inserted if (@g='副教授' and @z<3000) update teacher set MONEY=4000

where TNO in(select TNO from inserted)

实验总结：通过以上的实验使我了解触发器的实现，知道怎样应用它，

知道它带给我们的好处与方便。能让我在今后的学习中更深入的了解数据库有关的操作和实现。