实验内容与完成情况:
一、实验目的
熟悉通过MySQL对数据进行完整性控制,通过实验加深对数据完整性的理解。
触发器是实施复杂完整性的一种机制。触发器不需要专门的语句调用,当对它所保护数据进行修改时由DBMS自动激活,以防止对数据进行不正确,未授权或不一致的修改。
二、实验内容
本实验的内容为使用MySQL对数据进行完整性控制。用实验证实,当操作违反了完整性约束条件时,系统是如何进行处理的。认真完成以下题目,按要求撰写实验报告,记录所有实验用例。
1、在MySQL Workbench中新建一个用户数据库,并使用DDL语句建立如下3个表并插入数据,使用完整性约束命名子句CONSTRAINT定义完整性约束,注意:完整性约束命名子句CONSTRAINT在MySQL中需定义成表级约束。
(1)使用完整性约束命名子句CONSTRAINT建表
表Student1:
字段名 | 类型 | 长度 | 含义 | 约束 |
Sno | Varchar | 9 | 学号 | 主码(约束名:STU_PK) |
Sname | Varchar | 20 | 姓名 | 唯一(约束名:STU_U1) |
Ssex | Enum | 性别 | ‘M’或’F’ | |
Sage | Int | 年龄 | ||
Sdept | Varchar | 20 | 所在系 |
表Course1:
字段名 | 类型 | 长度 | 含义 | 约束 |
Cno | Varchar | 4 | 课程号 | 主码(约束名:COU_PK) |
Cname | Varchar | 40 | 课程名 | 唯一(约束名:COU_U1) |
Ccredit | Enum | 学分 | ‘1’、‘2’、‘3’、’4’、‘5’ |
表SC1:
字段名 | 类型 | 长度 | 含义 | 约束 |
Sno | Varchar | 9 | 学号 | 主码(约束名:SC_PK) 外码(参照Student1的Sno,约束名:SC_FK1) |
Cno | Varchar | 4 | 课程号 | 主码(约束名:SC_PK) 外码(参照Course1的Cno,约束名:SC_FK2) |
Grade | Int | 成绩 |
(2)插入如下数据,分析什么原因导致某些数据不能正确插入?
表Student1:
Sno | Sname | Ssex | Sage | Sdept |
202115121 | LiXiaoming | M | 16 | CS |
202115122 | LiuXiaohua | M | 20 | CS |
202115123 | WangXiaofei | F | 26 | MA |
202115124 | WangXiaolin | F | 20 | IS |
202115121 | LiuXiaoye | M | 27 | IS |
原因:主码约束,id唯一,最后一个与第一个id重复
表Course1:
Cno | Cname | Ccredit |
1 | Advanced Mathematics | 4 |
2 | College English | 4 |
3 | Information System | 4 |
4 | Operating System | 3 |
5 | Operating System | 3 |
6 | Database | 3 |
原因:cname设置了唯一的unique,与上一个重复了
表SC1:
Sno | Cno | Grade |
202215121 | 1 | 80 |
202115121 | 2 | 90 |
202115123 | 1 | 95 |
202115123 | 2 | 75 |
202115124 | 1 | 95 |
202115124 | 2 | 90 |
202115124 | 8 | 55 |
原因:参考了student1的sno,在student1里面没有学号为“202215121”的学生。
原因:在course1里没有课程号为‘8’的。
2、定义并验证触发器
(1)对Student1表建立触发器,保证每个学生的年龄都大于18岁。用SQL语句验证此触发器。
插入:
更新:
(2)对SC1表建立触发器,保证对SC1表的成绩进行更新或插入时,必须在0-100之间,否则就输出错误信息:“该成绩不在0-100之间!”。
插入:
更新:
(3)对Course1表建立触发器,保证删除一门课程时,同步删除该门课程的所有选课记录。用SQL语句验证此触发器。
删除cno为‘1’的学号:
结果:
(4)对SC1建立触发器,年龄大于20岁的学生才能选“Database”这门课。用SQL语句验证此触发器。
(5)对SC1表设计一个触发器,该触发器保证每个学生所选课程的总学分不超过12分。用SQL语句验证此触发器。
连续插入前两条数据后:
观察数据: