【数据库系统概论】实验六 完整性控制

一、实验目的

1．掌握数据库完整性控制原理
2．掌握SQL Server数据库系统完整性控制方法

二、实验内容

1. 创建一个触发器，“数据库”课程的选课人数不能超过5人。
2. 创建一个触发器，规定每位同学选课门数不能超过3门。
3. 创建一个触发器，当修改成绩表中成绩时，如果变化幅度超过10%，将操作记录在下表**SC_U（Sno,Cno,Oldgrade,Newgrade）**中。

三、问题和要求

1．写出你操作并正确执行的代码及结果。

-- 创建数据库stu_db
CREATE DATABASE stu_db;
-- 选择stu_db
USE stu_db;
-- 创建模式T
CREATE SCHEMA T;
-- 如果表存在先执行删除操作
DROP TABLE IF EXISTS T.SC;
DROP TABLE IF EXISTS T.Course;
DROP TABLE IF EXISTS T.Student;
-- 在模式T中创建表Student
CREATE TABLE T.Student(	
  Sno CHAR(9) PRIMARY KEY,            
  Sname CHAR(20) UNIQUE, 
  Ssex CHAR(2),
  Sage SMALLINT,
  Sdept CHAR(20)
); 
-- 在模式T中创建表Course
CREATE TABLE T.Course(	
  Cno CHAR(4) PRIMARY KEY,
  Cname CHAR(40),            
  Cpno CHAR(4),               	                      
  Ccredit SMALLINT,
  FOREIGN KEY (Cpno) REFERENCES  T.Course(Cno) 
); 
-- 在模式T中创建表SC
CREATE TABLE T.SC(
  Sno CHAR(9), 
  Cno CHAR(4),  
  Grade SMALLINT,
  PRIMARY KEY (Sno,Cno),                      
  FOREIGN KEY (Sno) REFERENCES T.Student(Sno), 
  FOREIGN KEY (Cno) REFERENCES T.Course(Cno) 
); 
-- 在表中插入T.Student的数据
INSERT INTO T.Student(Sno,Sname,Ssex,Sage,Sdept) VALUES ('202110767', '杨润玲', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110768', '朱娅玲', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110769', '徐江', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110770', '周双凤', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110771', '张秋玲', '女', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110772', '卢成思', '男', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110775', '胡梁蕊', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110777', '李银娇', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110780', '滕佳美', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110794', '颜蓉', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110806', '杨瑜', '男', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110810', '杨登洪', '女', 23, '大数据学院');
INSERT INTO T.Student VALUES ('202110817', '刘宇洁', '男', 19, '大数据学院');
INSERT INTO T.Student VALUES ('202110818', '张钰雪', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110820', '王庆', '男', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110822', '陈天浪', '男', 23, '大数据学院');
INSERT INTO T.Student VALUES ('202110824', '贺铄清', '男', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110828', '魏列镜', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110830', '撒月星', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110831', '余艳红', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110838', '方绍玉', '女', 21, '大数据学院');
-- 在表中插入T.Course的数据
INSERT INTO T.Course(Cno,Cname,Cpno,Ccredit) VALUES ('1', '数据结构', NULL, 3);
INSERT INTO T.Course VALUES ('2', '操作系统', NULL, 3);
INSERT INTO T.Course VALUES ('3', '计算机组成原理', NULL, 3);
INSERT INTO T.Course VALUES ('4', '计算机网络', NULL, 3);
INSERT INTO T.Course VALUES ('5', '数据库系统原理及应用', '1', 3);
INSERT INTO T.Course VALUES ('6', 'C语言程序设计', NULL, 3);
INSERT INTO T.Course VALUES ('7', 'Python程序设计', NULL, 2);
INSERT INTO T.Course VALUES ('8', '大数据应用技术', NULL, 2);
-- 涉及到完整性约束要求，先插入T.Course的数据再更新关联的列
UPDATE T.Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE T.Course SET Cpno = '3' WHERE Cno = '2' 
UPDATE T.Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE T.Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE T.Course SET Cpno = '6' WHERE Cno = '7' 
-- 在表中插入T.SC的数据
INSERT INTO T.SC(Sno,Cno,Grade) VALUES ('202110767', '1', 91);
INSERT INTO T.SC VALUES ('202110767', '5', 66);
INSERT INTO T.SC VALUES ('202110767', '6', 94);
INSERT INTO T.SC VALUES ('202110767', '7', 55);
INSERT INTO T.SC VALUES ('202110768', '1', 50);
INSERT INTO T.SC VALUES ('202110768', '2', 13);
INSERT INTO T.SC VALUES ('202110768', '8', 69);
INSERT INTO T.SC VALUES ('202110769', '1', 80);
INSERT INTO T.SC VALUES ('202110769', '3', 80);
INSERT INTO T.SC VALUES ('202110769', '5', 72);
INSERT INTO T.SC VALUES ('202110769', '6', 14);
INSERT INTO T.SC VALUES ('202110770', '1', 63);
INSERT INTO T.SC VALUES ('202110770', '2', 40);
INSERT INTO T.SC VALUES ('202110770', '3', 43);
INSERT INTO T.SC VALUES ('202110770', '4', 73);
INSERT INTO T.SC VALUES ('202110771', '1', 38);
INSERT INTO T.SC VALUES ('202110771', '2', 38);
INSERT INTO T.SC VALUES ('202110771', '3', 62);
INSERT INTO T.SC VALUES ('202110771', '5', 90);
INSERT INTO T.SC VALUES ('202110772', '1', 46);
INSERT INTO T.SC VALUES ('202110772', '6', 46);
INSERT INTO T.SC VALUES ('202110775', '1', 89);
INSERT INTO T.SC VALUES ('202110775', '7', 89);
INSERT INTO T.SC VALUES ('202110777', '1', 41);
INSERT INTO T.SC VALUES ('202110777', '8', 41);
INSERT INTO T.SC VALUES ('202110780', '1', 19);
INSERT INTO T.SC VALUES ('202110780', '7', 19);
INSERT INTO T.SC VALUES ('202110794', '1', 1);
INSERT INTO T.SC VALUES ('202110794', '6', 1);
INSERT INTO T.SC VALUES ('202110806', '1', 92);
INSERT INTO T.SC VALUES ('202110806', '5', 92);
INSERT INTO T.SC VALUES ('202110810', '1', 15);
INSERT INTO T.SC VALUES ('202110810', '4', 15);
INSERT INTO T.SC VALUES ('202110817', '1', 2);
INSERT INTO T.SC VALUES ('202110817', '3', 2);
INSERT INTO T.SC VALUES ('202110818', '1', 56);
INSERT INTO T.SC VALUES ('202110818', '2', 56);
INSERT INTO T.SC VALUES ('202110820', '1', 33);
INSERT INTO T.SC VALUES ('202110822', '1', 85);
INSERT INTO T.SC VALUES ('202110822', '5', 85);
INSERT INTO T.SC VALUES ('202110824', '1', 57);
INSERT INTO T.SC VALUES ('202110824', '3', 26);
INSERT INTO T.SC VALUES ('202110828', '1', 53);
INSERT INTO T.SC VALUES ('202110828', '2', 53);
INSERT INTO T.SC VALUES ('202110828', '5', 36);
INSERT INTO T.SC VALUES ('202110830', '1', 77);
INSERT INTO T.SC VALUES ('202110830', '5', 70);
INSERT INTO T.SC VALUES ('202110830', '6', 76);
INSERT INTO T.SC VALUES ('202110830', '7', 49);
INSERT INTO T.SC VALUES ('202110831', '1', 30);
INSERT INTO T.SC VALUES ('202110831', '2', 81);
INSERT INTO T.SC VALUES ('202110838', '1', 4);
INSERT INTO T.SC VALUES ('202110838', '5', 97);
INSERT INTO T.SC VALUES ('202110838', '6', 99);
-- 执行查询
SELECT * FROM T.Student;
SELECT * FROM T.Course;
SELECT * FROM T.SC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131

1、创建一个触发器，“数据库”课程的选课人数不能超过5人。

CREATE TRIGGER S_1 ON T.SC
AFTER INSERT AS
	DECLARE @COUNT INT,@Sno CHAR(9),@Cno CHAR(4),@Grade SMALLINT;
	SELECT @COUNT=COUNT(*) FROM T.Course,T.SC WHERE T.Course.Cno=T.SC.Cno AND T.Course.Cname='数据库系统原理及应用'
	SELECT @Sno=Sno,@Cno=Cno,@Grade=Grade FROM INSERTED;
IF(@COUNT>5)
  	BEGIN
    		DELETE FROM T.SC WHERE @Sno=Sno AND @Cno=Cno
  	END
1
2
3
4
5
6
7
8
9

– 查询现有数据

SELECT * FROM T.Course,T.SC 
WHERE T.Course.Cno=T.SC.Cno 
AND T.Course.Cname='数据库系统原理及应用';
1
2
3

– 删除T.SC的所有数据

DELETE FROM T.SC;
1

– 执行插入语句，重新插入数据后，进行查看

SELECT * FROM T.Course,T.SC 
WHERE T.Course.Cno=T.SC.Cno 
AND T.Course.Cname='数据库系统原理及应用';
1
2
3

– 删除触发器S_1

DROP TRIGGER S_1 ON T.sc;
1

2、创建一个触发器，规定每门课课程数不能超过3门。

CREATE TRIGGER S_2 ON T.SC
AFTER INSERT AS
	DECLARE @COUNT INT,@Sno CHAR(9),@Cno CHAR(4),@Grade SMALLINT;
	SELECT @Sno=Sno,@Cno=Cno,@Grade=Grade FROM INSERTED;
	SELECT @COUNT=COUNT(*) FROM T.SC WHERE @Cno=Cno
IF(@COUNT>3)
	BEGIN
		DELETE FROM T.SC WHERE @Sno=Sno AND @Cno=Cno
	END;
1
2
3
4
5
6
7
8
9

– 查询现有数据

SELECT * FROM T.Course,T.SC 
WHERE T.Course.Cno=T.SC.Cno 
ORDER BY T.SC.Sno;
1
2
3

– 删除T.SC的所有数据

DELETE FROM T.SC;
1

– 重新插入数据后进行查看

SELECT * FROM T.Course,T.SC 
WHERE T.Course.Cno=T.SC.Cno 
ORDER BY T.SC.Sno;
1
2
3

3、创建一个触发器，当修改成绩表中成绩时，如果变化幅度超过10%，将操作记录在下表SC_U（Sno,Cno,Oldgrade,Newgrade）中。

– 创建表T.SC_U

CREATE TABLE T.SC_U(
  Sno CHAR(9), 
  Cno CHAR(4),  
  OldGrade SMALLINT,
  NewGrade SMALLINT
  PRIMARY KEY (Sno,Cno),
  FOREIGN KEY (Sno) REFERENCES T.Student(Sno),
  FOREIGN KEY (Cno) REFERENCES T.Course(Cno)
);
1
2
3
4
5
6
7
8
9

– 创建触发器S_3

CREATE TRIGGER S_3 ON T.SC
AFTER UPDATE AS
	DECLARE @COUNT INT,@Sno CHAR(9),@Cno CHAR(4),@OldGrade SMALLINT,@NewGrade SMALLINT;
IF UPDATE(Grade)
BEGIN
	SELECT @Sno=Sno,@Cno=Cno,@NewGrade=Grade FROM INSERTED;
	SELECT @Sno=Sno,@Cno=Cno,@OldGrade=Grade FROM DELETED;
IF(@NewGrade > @OldGrade+@OldGrade*0.1)
	BEGIN
		INSERT INTO T.SC_U VALUES(@Sno,@Cno,@OldGrade,@NewGrade)
	END
END
1
2
3
4
5
6
7
8
9
10
11
12

– 查询更改成绩前的数据

SElECT * FROM T.SC
WHERE Cno = '6'
AND Sno='202110769';
1
2
3

– 更新成绩 (变化幅度超过10%)

UPDATE T.SC SET Grade = '60' 
WHERE Cno = '6' 
AND Sno='202110769';
1
2
3

– 查询更改成绩后的的数据

SElECT * FROM T.SC  WHERE Cno = '6' 
AND Sno='202110769';
1
2

– 查询T.SC_U看是否有记录

SELECT * FROM T.SC_U;
1