SQL语句（六）_NUIST

一、            实验目的

1.       掌握实体完整性、参照完整性和用户自定义完整性的定义方法。能够写出两种定义完整性的SQL语句。设计SQL语句验证完整性约束是否起作用。

2.       设计SQL语句验证完整性约束是否起作用。

3.       能够根据实际情况定义完整性约束的违约处理方式。

4.       会写简单的触发语句。

 

二、            实验内容和要求

1.      在实验一所创建的学生数据库中，增加以下对象的完整约束的描述

可以

（1）     使用Alter table的add  constraint语句完成

（2）     用窗口界面完成（记得保存）

表1 Student表结构

列名	说明	数据类型	约束
Sno	学号	普通编码定长字符串，长度为7	主键
Sname	姓名	普通编码定长字符串，长度为10	非空
Ssex	性别	普通编码定长字符串，长度为2	取值范围：{男，女}
Sage	年龄	微整型（tinyint）	取值范围：15-26
Sdept	所在系	普通编码不定长字符串，长度为20	默认值为“计算机系”
Sid	身份证号	普通编码定长字符串，长度为10
Sdate	入学日期	日期	默认为系统当前日期

注：默认值关键词default ，系统当前日期用getdate()函数

表2 Course表结构

列名	说明	数据类型	约束
Cno	课程号	普通编码定长字符串，长度为10	主键
Cname	课程名	普通编码不定长字符串，长度为20	非空
Credit	学时数	整型	取值大于0
Semester	学期	小整型
Tno	教师号	普通编码定长字符串，长度为8

 

表3 SC表结构

列名	说明	数据类型	约束
Sno	学号	普通编码定长字符串，长度为7	外键，引用Student的主键
Cno	课程号	普通编码定长字符串，长度为10	外键，引用Course的主键
Grade	成绩	小整型	取值范围为0-100

 

表4 Teacher表结构

列名	说明	数据类型	约束
Tno	教师号	普通编码定长字符串，长度为8	主键，约束名PK_Teacher
Tname	教师名	普通编码定长字符串，长度为10	候选码
Salary	工资	定点小数，小数点前4位，小数点后2位
Title	职称	普通编码定长字符串，长度为4	取值范围为：{教授，副教授，讲师}。

表1 Student表:

ALTER TABLE Student ADD Sdate DATE;

ALTER TABLE Student ADD CONSTRAINT c1_student CHECK (Sname IS NOT NULL);

ALTER TABLE Student ADD CONSTRAINT c2_student CHECK (Ssex = '男' OR Ssex = '女');

ALTER TABLE Student ADD CONSTRAINT c3_student CHECK (Sage BETWEEN 15 AND 26);

ALTER TABLE Student ADD DEFAULT '计算机系' FOR Sdept;

ALTER TABLE Student ADD DEFAULT GETDATE() FOR Sdate;

表2 Course表:

ALTER TABLE Course ADD CONSTRAINT c1_course CHECK (Cname IS NOT NULL);

ALTER TABLE Course ADD CONSTRAINT c2_course CHECK (Credit > 0);

ALTER TABLE Course ADD Tno CHAR(8);

表3 SC表:

ALTER TABLE SC ADD CONSTRAINT c1_sc CHECK(Grade BETWEEN 0 AND 100);

表4 Teacher表:

ALTER TABLE Teacher ADD CONSTRAINT PK_Teacher PRIMARY KEY(Tno);

ALTER TABLE Teacher ADD CONSTRAINT c1_teacher UNIQUE(Tname);

ALTER TABLE Teacher ADD CONSTRAINT c2_teacher CHECK (Title = '教授' OR Title = '副教授' OR Title = '讲师');

2.      完整性约束的修改及定义违约处理（使用Alter table语句完成）

（1）     对SC表，外键SNO默认违约处理为级联删除，CNO的违约处理为级联更新。

ALTER TABLE SC ADD CONSTRAINT c2_sc FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADE;

ALTER TABLE SC ADD CONSTRAINT c3_sc FOREIGN KEY(Cno) REFERENCES Course(Cno) ON UPDATE CASCADE;

 （2）     对Teacher表，Tno为主键，约束名为PK_Teacher，Tname为候选码。

ALTER TABLE Teacher ADD CONSTRAINT PK_Teacher PRIMARY KEY(Tno);

ALTER TABLE Teacher ADD CONSTRAINT c1_teacher UNIQUE(Tname);

3.      完整性约束的检查和违约处理

（1）对Student表插入两条元组，

Sno	Sname	Ssex	Sage	Sdept	Sid	Sdate
0811101	张勇	男	21	计算机系
0831104	钱程	男	28	通信工程系

会产什么结果？将结果截屏，分析原因。

1)      Sno为Student表主键, 不允许重复, 而Student表中已有Sno为0811101的学生记录, 违反了主键约束, 因此不能插入。

2)      已为Sage添加了自定义完整性约束: 取值范围：15-26, 此记录中Sage = 28违反了自定义完整性约束, 因此不能插入。

将student表中年龄（sage）字段的约束改为15-45，再试试看。提示：先drop constraint，再add constraint

3)      修改约束后改记录已符合约束, 因此可以插入。

 

（2）删除student表中元组

0811103

王敏

女

20

计算机系

会产生什么结果，请截屏，并分析原因。将SC表中关于参照完整性的默认违约处理更改为拒绝执行，再删除student表中的此元组，此时会产生什么结果，请截屏，并分析原因。

1)      SC中的学号为0811103的记录会被一起删除。

2)      修改参照完整性为不执行后, SC中存在学号为0811103的记录, 因此从Student表删除学号为0811103的记录时会涉及到SC中的外键约束, 会被拒绝删除。

（3）将Course表中元组

C007

数据结构

4

4

更新为

C007

通信原理

4

4

再执行查询“查询选修了课程号为007的同学的学号，姓名，课程号，课程名和成绩”

对查询结果进行截屏。

SELECT Student.Sno, Sname, SC.Cno, Cname, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno WHERE Course.Cno = 'C007';

（4）输入一条符合SC表中完整性约束的要求的元组，将结果截屏，解释输入的元组有哪些必须要满足的约束要求。

INSERT INTO SC VALUES('0811101', 'C007', 99);

Sno需要满足外键约束: Sno要在被参照表Student中的Sno里选取, Cno需要满足外键约束: Cno要在被参照表Course中的Cno里选取, 联合主键Sno, Cno要满足完整性约束: 不能出现重复, Grade需要满足自定义完整性: 取值在0~100间。

以上操作，如果能够执行，需要给出SQL语句。

4.      触发器

（1）         在表Student中建立触发器，实现表Student和表SC的级联删除，也就是只要删除表Student中的元组学号为s1，则表SC中SNO为s1的元组也要删除。

执行delete from Student where SNo='0811101'，检查一下结果

CREATE TRIGGER t_student ON Student INSTEAD OF DELETE AS BEGIN DECLARE @id CHAR(7) SELECT @id = Sno FROM deleted DELETE FROM Student WHERE Sno = @id DELETE FROM SC WHERE Sno = @id END;

触发器成功执行。

 注意: 由于之前已经对SC表添加了级联删除的自定义约束, 因此这里触发器要成功的话要先把约束删除(表-设计-关系), 然后再执行

（2）course表中建立触发器﹐当向course表中插入一条选课记录时﹐检查表的Tno属性是否存在于表Teacher中﹐如果存在，则可以插入，否则，不能向course表中插入这条记录。

向course表中插入一条元组（c009，操作系统，4，5，T006）检查一下结果

CREATE TRIGGER t_course ON Course INSTEAD OF INSERT AS BEGIN DECLARE @id CHAR(10), @name VARCHAR(20), @credit TINYINT, @semester SMALLINT, @t_id CHAR(8) SELECT @id = Cno, @name = Cname, @credit = Credit, @semester = Semester ,@t_id = Tno FROM inserted IF((SELECT Teacher.Tno FROM Teacher JOIN inserted ON Teacher.Tno = @t_id) IS NOT NULL) BEGIN INSERT INTO Course VALUES(@id, @name, @credit, @semester, @t_id) END ELSE PRINT('插入失败') END;

Teacher表中没有教师编号为T006的记录, 插入失败, 触发器成功执行。

---

声明:

1. 仅供参考, 不保证正确性!

2. 图片模糊是因为我写完以后用word另存为html自动压缩了.