实验4 数据库完整性控制

注意：为了节约大家的宝贵时间，因此给出题目时，会在一定程度上给出相关的语句，大家只需要补充完善相关的SQL语句即可。

实验四数据库完整性控制
一、实验目的
1．掌握使用T-SQL定义实体完整性的方法。

2. 了解SQL Server违反实体完整性处理措施。

3. 理解参照完整性的含义。

4. 熟练掌握建立外键的方法。

5. 掌握利用FOREIGN KEY…REFERENCES子句以及各种约束保证参照完整性。

6．掌握利用短语NOT NULL、UNIQUE、CHECK保证域完整性。

7. 熟练掌握约束、规则实施用户自定义完整性
8．掌握创建触发器的方法，掌握利用触发器规范插入、更新、删除操作的方法。

二、实验内容
1、新建数据库School并创建学生表Student，包含以下属性：Sno (CHAR(5))、Sname (CHAR(8))、Ssex (CHAR(1))、Sage (INT)、Sdept (CHAR(20))，
并插入数据：( '10000','王敏','F',23,'CS')，('10000','王浩','M',25,'EE')，
创建数据库、表以及插入数据的相关语句为：
CREATE DATABASE School ;
USE School
CREATE TABLE Student(
Sno CHAR(5),
Sname CHAR(8),
Ssex CHAR(1),
Sage INT,
Sdept CHAR(20));
INSERT INTO Student values ( '10000','王敏','F',23,'CS');
INSERT INTO Student values ('10000','王浩','M',25,'EE');
执行完上述语句后查看数据库中student表中的数据。

然后再添加约束：令Sno为主键，查看结果并分析原因。

验证当与现有的数据环境不等时，无法建立实体完整性。

最后，请大家删除该student表，直接在定义表时给出实体完整性约束，要求给出把主键定义在表级和列级两种方法。

定义好表结构后，再执行如下两条插入语句，INSERT INTO Student values ( '10000','王敏','F',23,'CS');
INSERT INTO Student values ('10000','王浩','M',25,'EE');
列级：
USE School
CREATE TABLE Student(
Sno CHAR(5) primary key,
Sname CHAR(8),
Ssex CHAR(1),
Sage INT,
Sdept CHAR(20))
表级：
USE School
CREATE TABLE Student(
Sno CHAR(5),
Sname CHAR(8),
Ssex CHAR(1),
Sage INT,
Sdept CHAR(20)
primary key (Sno))
分析违反实体完整性时，系统给出了何种处理。

并将王浩的学号改为10001重新插入。

请参照实验教材P52-55实验3.1。

2、验证多重级联删除。

在数据库School中新建StudentCard表，包含以下属性：CardID (char(14))、Sno(char (5))、RemainedMoney (decimal (10,2))，令CardID为其主键，令Sno为参照Student表的外键，级联删除，并插入数据；新建ICBCCard表，包含以下属性：BankID (char(20))、CardID (char (14))、RestoredMoney (decimal (10,2))，令BankID为主键，令CardID 为参照StudentCard表的外键，级联删除，并插入数据。

本题大家可以在如下语句上进行完善：
CREATE TABLE StudentCard(
CardID char(14),
Sno char (5),
RemainedMoney decimal (10,2)
);
INSERT INTO StudentCard V ALUES ( '05212567', '10001',120.00);
INSERT INTO StudentCard V ALUES ( '05212302', '10000',130.50);
CREATE TABLE ICBCCard(
BankID char(20),
CardID char (14),
RestoredMoney decimal (10,2),
);
INSERT INTO ICBCCard V ALUES ( '9558844022312','05212302',125000.3);
INSERT INTO ICBCCard V ALUES ( '9558844023645','05212567',150000.4);
注意：请大家在建立外键时，StudentCard表的外键建立的列级，ICBCCard表建立在表级。

再执行以下语句：
INSERT INTO StudentCard V ALUES ( '05212568', '10002',120.00);
观察会发生什么？为什么？
3．通过删除Student表中的一条记录。

Delete From Student where Sno=’10000’
然后再查询StudentCard、ICBCCard表中的数据，观察发生了什么？
第2和3题请参照实验教材P55-61实验3.2。

4. 在数据库School中新建Director表，包含以下属性：Dno (CHAR(5))、Dname (CHAR(8))、Dsex (CHAR(1))、Dage (INT)、Ddept (CHAR(20))，并自定义3个约束U1、U2和U3，其中U1规定Dno字段不允许取空，U2规定Dage属性的值必须<=55，U3规定Ddept 属性的值唯一。

创建表的语句为：
CREATE TABLE Director(
Dno CHAR(5),
Dname CHAR(8) ,
Dsex CHAR(1),
Dage INT,
Ddept CHAR(20));
本题在上述语句上添加必要的约束然后运行即可。

注意：CONSTRAINT U3（约束名）是为了给出约束的名称，从而方便对该约束进行管理，否则是可以省略的。

5. Director表创建成功后，向其中插入三条数据，请逐一执行如下三条语句：
INSERT INTO Director values ('90000','赵磊','M',45,'艺术系');
INSERT INTO Director values ('90001','韩丹','F',60,'中文系');
INSERT INTO Director values ('90001','韩丹','F',50,'艺术系');
观察，三条语句执行时，可以正常插入吗？如果不能正常插入，为什么？
6. 去除U1约束。

7．创建规则rule_sex，规定插入或更新的值只能是M或F，并绑定到Director的Dsex 字段。

完成后在对象资源管理器中查看School数据库规则设置。

8．演示违反规则rule_sex的插入操作。

执行语句：
INSERT INTO Director values ('90002','蒋坤','1',34,'管理系');
执行结果是什么？为什么？
注意：第4-8题请参照实验教材P61-64实验3.3。

9、完成实验3.4 触发器设计
建立该触发器之前，先创建teacher表，创建teacher表的语句为：
CREATE TABLE Teacher
(
Eno numeric(4) primary key,
Ename char(10),
Job char(8),
Sal numeric(7,2),
Deduct numeric(7,2),
Deptno numeric(2));
10．建立一个在teacher表上的触发器T1，当插入或更新表中数据时，保证操作记录的Sal值大于0，且不得低于2100，如果低于2100，自动改为2100。

触发器的创建请参照实验教材P64-66实验3.4以及上课时所讲的实例。

11.建立一个在student表建立触发器T2，要求当更新一个记录时，表中记录的Sage值要比老记录的Sage值大，因为一般年龄只能升不能降。