数据库作业12：第五章例题（5.1~5.20）

实体完整性

• 关系模型的完整性在CREATE TABLE中用PRIMARY KEY定义。

为方便实验与记录，创建新的数据库。

[例5.1]将Student表中的Sno属性定义为码；

CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,--在列级定义主码
	Sname CHAR(20) NOT NULL,
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20)
);

--或者使用以下代码

--CREATE TABLE Student
--	(Sno CHAR(9),
--	Sname CHAR(20) NOT NULL,
--	Ssex CHAR(2),
--	Sage SMALLINT,
--	Sdept CHAR(20),
--	PRIMARYKEY(Sno)
--);

[例5.2]将SC表中的Sno、Cno属性组定义为码。

CREATE TABLE SC
	(Sno CHAR(9) NOT NULL,
	Cno CHAR(4) NOT NULL,
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno)
);

参照完整性

• 关系模式的参照完整性在CREATE TABLE中用FOREIGN
  
  KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。

[例5.3]定义SC中的参照完整性。


参照完整性中有关于课程Course表，先建立Course表。

CREATE TABLE Course(--课程表
	Cno CHAR(4) PRIMARY KEY,--列级完整性，不能取空值
	Cname CHAR(40) NOT NULL,
	Cpno CHAR(4),--先修课
	Ccredit SMALLINT,
	FOREIGN KEY(Cpno) REFERENCES Course(Cno)--表级完整性，Cpno为外码，参照Course表中的Cno列
);

然后定义SC表中的参照完整性。

CREATE TABLE SC
	(Sno CHAR(9) NOT NULL,
	Cno CHAR(4) NOT NULL,
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno),--在表级定义实体完整性
	FOREIGN KEY(Sno)REFERENCES Student(Sno),--在表级定义参照完整性
	FOREIGN KEY(Cno)REFERENCES Course(Cno)--在表级定义参照完整性
);

• 一般地，当对参照表和被参照表的操作违反了参照完整性时，系统选用默认策略，即拒绝执行。如果想让系统采用其他策略则必须在创建参照表时显式地加以说明。

[例5.4]显式说明参照完整性的违约处理示例。

按照课本语句，出现了报错，提示FOREIGN附近有语法错误

CREATE TABLE SC
	(Sno CHAR(9) NOT NULL,
	Cno CHAR(4) NOT NULL,
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno));

	FOREIGN KEY(Sno)REFERENCES Student(Sno)

	ON DELETE CASCADE,--删除Student表中的元组时，级联删除SC中相应的元素
	ON UPDATE CASCADE,--当更新Student表中的sno时，级联更新SC表中的相应元组

	FOREIGN KEY(Cno)REFERENCES Course(Cno)
	ON DELETE NO ACTION--当删除Course表中的元组造成与SC表不一致时，拒绝删除
	ON UPDATE CASCADE--当更新Course表中的Cno时，级联更新SC表中相应的元组

用户定义的完整性

• 用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。目前的关系数据库管理系统都提供了定义和检验这类完整性的机制，使用了和实体完整性、参照完整性相同的技术方法来处理他们，而不必由应用程序承担这一功能。

1、属性上的约束条件

在CREATE TABLE中定义属性的同时，可以根据应用要求定义属性上的约束条件，即属性值限制，包括：

• 列值非空（NOT NULL）。 列值唯一（UNIQUE）。 检查列值是否满足一个条件表达式（CHECK短语）。

1）不允许取空值

[例5.5]在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

CREATE TABLE SC
	(Sno CHAR(9) NOT NULL,--不允许取空值
	Cno CHAR(4) NOT NULL,--不允许取空值
	Grade SMALLINT NOT NULL,--不允许取空值
	PRIMARY KEY(Sno,Cno),--在表级定义实体完整性，隐含了Sno、Cno不允许取空值，在列级不允许取空值的定义可不写
);

2）列值唯一

[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码。

CREATE TABLE DEPT
	(Deptno NUMERIC(2),
	Dname CHAR(9) UNIQUE NOT NULL,
	Location CHAR(10),
	PRIMARY KEY(Deptno)
);

3）用CHECK短语指定列值应该满足的条件

[例5.7]Student表的Ssex只允许取“男”或“女”。

CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,
	Sname CHAR(8) NOT NULL,
	Ssex CHAR(2) CHECK(Ssex IN('男','女')),
	Sage SMALLINT,
	Sdept CHAR(20)
);

[5.8]SC表的Grade的值应该在0和100之间。

CREATE TABLE SC
	(Sno CHAR(9),
	Cno CHAR(4),
	Grade SMALLINT CHECK(Grade>=0 AND Grade <=100),
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY(Sno)REFERENCES Student(Sno),
	FOREIGN KEY(Cno)REFERENCES Course(Cno)
);

2、元组上的约束条件

• 在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。

[例5.9]当学生的性别是男时，其名字不能以Ms.打头。

CREATE TABLE Student
	(Sno CHAR(9),
	Sname CHAR(8) NOT NULL,
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20),
	PRIMARY KEY(Sno),
	CHECK(Ssex='女' OR Sname NOT LIKE 'Ms.%')
);

性别是女性的元组都能通过该项CHECK检查，因为Ssex=’女’成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头，因为Ssex=’男’时，条件要想为真值，Sname NOT LIKE ‘Ms.%’必须为真值。

完整性约束命名子句

• SQL还在CREATE TABLE语句中提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名，从而可以灵活地增加、删除一个完整性约束条件。

1、完整性约束命名子句

CONSTRAINT<完整性约束条件名><完整性约束条件>

<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK短语等。

[例5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

CREATE TABLE Student
	(Sno NUMERIC(6)
		CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
	Sname CHAR(20)
		CONSTRAINT C2 NOT NULL,
	Sage NUMERIC(3)
		CONSTRAINT C3 CHECK(Sage<30),
	Ssex CHAR(2)
		CONSTRAINT C4 CHECK(Ssex IN('男','女')),
		CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

[例5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元。


应发工资是工资列Sal与扣除项Deduct之和。

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),
	CONSTRAINT TEACHERFKey FOREIGN KEY(Deptno)
	REFERENCES DEPT(Deptno),
	CONSTRAINT C1 CHECK(Sal+Deduct>=3000)
);

• 可以使用ALTER TABLE语句修改表中的完整性。

[例5.12]去掉例5.10Student表中对性别的限制。

ALTER TABLE Student
	DROP CONSTRAINT C4;

[例5.13]修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40。

ALTER TABLE Student
	DROP CCONSTRAINT C1;
ALTER TABLE Student
	ADD CONSTERAINT C1 CHECK(Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student
	DROP CONSTRAINT C3;
ALTER TABLE Student
	ADD CONSTRAINT C3 CHECK(Sage<40);

域中的完整性限制

• SQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域以及该域应该满足的完整性约束条件，然后就可以用域来定义属性。

[例5.14]建立一个性别域，并声明性别域的取值范围。

CREATE DOMAIN GenderDomain CHAR(2)
	CHECK(VALUE IN('男','女'));

[例5.15]建立一个性别域GenderDomain，并对其中的限制命名。

CREATE DOMAIN GenderDomain CHAR(2)
	CONSTRAINT GD CHECK(VALUE IN('男','女'));

[例5.16]删除域GenderDomain的限制条件GD。

ALTER DOMAIN GenderDomain
	DROP  CONSTRAINT GD;

[例5.17]在域GenderDomain上增加性别的限制条件GDD。

ALTER DOMAIN GenderDomain
	ADD CONSTRAINT GDD CHECK(VALUE IN('1','0'));

断言

• 在SQL中可以使用数据定义语言中的CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束。

1、创建断言的语句格式

CREATE ASSERTION<断言名><CHECK 子句>

每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。

[例5.18]限制数据库课程最多60名学生选修。

CREATE ASSERTION ASSE_SC_DB_NUM
	CHECK(60>=(SELECT count(*)
		FROM Course,SC
		WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME='数据库')
);

每当学生选修课程时，将在SC表中插入一条元组（Sno,Cno,NULL）,ASSE_SC_DB_NUM断言被触发检查。如果选修数据库的人数已经超过60人，CHECK子句返回值为“假”，对SC表的插入操作被拒绝。

[例5.19]限制每一门课程最多60名学生选修。

CREATE ASSERTION ASSE_SC_CNUM1
	CHECK(60>=ALL(SELECT count(*)
				FROM SC
				GROUP by cno)
				);

[例5.20]限制每个学期每一门课程最多60名学生选修。


首先修改SC表的模式，增加一个“学期(TERM)”的属性。

ALTER TABLE SC ADD TERM DATE;--先修改SC表，增加TERM属性，它的类型是DATE

--定义断言：
CREATE ASSERTION ASSE_SC_CNUM2
	CHECK(60>=ALL(select count(*)from SC group by cno,TERM));

2、删除断言的语句格式

DROP ASSERTION<断言名>；

• 如果断言很复杂，则系统在检测和维护断言上的开销较高，这是在使用断言时应该注意的。

以上为本次实验全部内容，近期学习压力稍大，理解课本内容仍需多看书，多练习。理应坚持不懈，不断努力！

感谢阅读~

** 2021-4-22更新**

触发器

• 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制。
• 触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

定义触发器

SQL使用CREATE TRIGGER命令建立触发器，其一般格式为

CREATE TRIGGER<触发器名>
{BEFORE|AFTER}<触发事件>ON<表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH{ROW|STATEMENT}
[WHEN<触发条件>]<触发动作体>

[例5.21]当对表SC的Grade属性进行修改时，若分数增加10%，则将此次操作记录到另一个表SC_U(Sno、Cno、Oldgrade、Newgrade)中，其中Oldgrade是修改前的分数。

• 标准SQL语句如下：

IF(OBJECT_ID('SC_T') IS NOT NULL)        -- 判断名为 Student_Count 的触发器是否存在
DROP TRIGGER SC_T        -- 删除触发器
GO
CREATE TRIGGER SC_T           --SC.T触发器名字
	AFTER UPDATE OF Grade ON SC   --UPDATE OF Grade ON SC 触发事件
                              --AFTER是触发时机
REFERENCING 
	OLDROW AS OLDTUPLE,
	NEWROW AS NEWTUPLE --REFERENCING指出引用变量，但是T-SQL并不支持
FOR EACH ROW    --行级触发器，每执行一次Grade的更新，下面规则就执行一次
WHEN (NEWTUPLE.Grade>=1.1*OLDTUPLE.Grade)  --触发条件
	INSERT INTO SC_U(Sno,Cno,OLDGRADE,NEWGARADE)   --INSERT操作
	VALUES(OLDTUPLE.Sno,OLDTUPLE.Cno,OLDTUPLE.Grade,NEWTUPLE.Grade)

T-SQL语句：

CREATE TABLE SC_U
	(Sno CHAR(9),
	Cno CHAR(4),
	OLDGRADE SMALLINT,
	NEWGRADE SMALLINT
);

T-SQL中实现上述断言的功能如下：

if(object_id('trg_sc_insert') is not null)
	drop trigger trg_sc_insert;
go

create trigger trg_sc_insert
on SC for insert --不支持before，for相当于after
as
	declare @sum_p int,
			@Sno varchar(15),
			@Cno varchar(10),
			@Grade smallint; --定义自变量
	select @sum_p=count(*) from Course,SC where SC.Cno=Course.Cno and Course.Cname='数据库';
	select @Sno=Sno,@Cno=Cno,@Grade=Grade from inserted; --获取插入的元组的属性值
	if(@sum_p>60)
		begin
			delete SC where Sno=@Sno and Cno=@Cno and Grade=@Grade;
		end
go

T-SQL语句：

IF(OBJECT_ID('SC_T')IS NOT NULL)
	DROP TRIGGER TRG_SC_INSERT;
GO
CREATE TRIGGER SC_T
ON SC AFTER UPDATE
AS
	DECLARE @Sno VARCHAR(15),
			@Cno VARCHAR(10),
			@old_Grade SMALLINT,
			@new_Grade SMALLINT;
	SELECT @Sno=Sno,@Cno=Cno,@new_Grade=Grade from inserted;
	SELECT @old_Grade FROM DELETED;
	IF(@new_Grade>1.1*@old_Grade)
		BEGIN
			INSERT INTO SC_U(Sno,Cno,OLDGRADE,NEWGRADE)
			VALUES(@Sno,@Cno,@old_Grade,@new_Grade);
		END
GO

执行修改语句如下：

DROP TRIGGER <触发器名> ON <表名>;

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。

存储过程和函数

• 存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可

1、存储过程

1）创建存储过程

CREATE OR REPLACE PROCEDURE 过程名 ([参数1,参数2,...])
AS<过程化SQL语言>;

[例8.9]利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中，假设账户关系表为Account（Accountnum，Total）。


先建立一个表用于实验：

CREATE TABLE T_RE
(ANUM CHAR(3), --账户编号
TOTAL FLOAT);  --账户余额
INSERT INTO T_RE VALUES(101,200),(102,300);

SELECT * FROM T_RE;

标准SQL语句：

CREATE OR REPLACE PROCEDURE TRANSFER
(INACCOUNT INT,OUTACCOUNT INT,AMOUNT FLOAT)
AS DECLARE
TOTALDEPOSITOUT FLOAT;
TOTALDEPOSITIN FLOAT;
INACCOUNTNUM INT;
BEGIN
SELECT TOTAL INTO TOTALDEPOSITOUT
FROM ACCOUNT 
WHERE ACCOUNTNUM=OUTACCOUNT;
IF TOTALDEPOSITOUT IS NULL THEN
ROLLBACK;
RETURN;
END IF;
IF TOTALDEPOSITOUT<AMOUNT THEN
ROLLBACK;
RETURN;
END IF;
SELECT ACCOUNTNUM INTO INACCOUNTNUM 
FROM ACCOUNT
WHERE ACCOUNTNUM=IN ACCOUNT；
IF INACCOUNT IS NULL THEN
ROLLBACK;
RETURN;
END IF;
UPDATE ACCOUNT SET TOTAL=TATAL-AMOUNT WHERE ACCOUNTNUM=OUTACCOUNT;
UPDATE ACCOUNT SET TOTAL=TATAL+AMOUNT WHERE ACCOUNTNUM=INACCOUNT;
COMMIT;
END;

T-SQL语句：

IF(EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='P_T'))
DROP PROCEDURE P_T
GO
CREATE PROCEDURE P_T
@INID CHAR(3),@OUTID CHAR(3),@AMOUNT FLOAT
AS
BEGIN
TRANSACTION TRANS
DECLARE @TOTALOUT FLOAT,@TOTALIN FLOAT,@OUTIDNUM CHAR(3);   --查询转出账户余额
SELECT @TOTALOUT=TOTAL FROM T_RE WHERE ANUM=@OUTID;
IF(@TOTALOUT IS NULL) BEGIN PRINT'账户不存在或者无余额'
ROLLBACK TRANSACTION TRANS;RETURN ; END; --回滚事务
IF(@TOTALOUT<@AMOUNT)BEGIN PRINT'账户余额不足' 
ROLLBACK TRANSACTION TRANS;RETURN;END;
SELECT @TOTALIN=ANUM FROM T_RE WHERE ANUM=@INID;
IF(@TOTALIN IS NULL) BEGIN PRINT'转入账户不存在'
ROLLBACK TRANSACTION TRANS;RETURN;END;
BEGIN
UPDATE T_RE SET TOTAL=TOTAL-@AMOUNT WHERE ANUM=@OUTID;
UPDATE T_RE SET TOTAL=TOTAL+@AMOUNT WHERE ANUM=@INID;
PRINT'请取走银行卡'
COMMIT TRANSACTION TRANS;
RETURN;
END;

金额转账：

SELECT * FROM T_RE;
EXEC	P_T
		@INID = 101,	--转入账户
		@OUTID = 102,	--转出账户
		@amount = 50		--转出金额

SELECT * FROM T_RE;

2、执行存储过程。

CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...]);

[例8.10]从账户01转100元到02账户中

CALL PROCEDURE TRANSFER (01,02,100);

3、修改存储过程

使用ALTER PROCEDURE 重命名一个存储过程

ALTER PROCEDURE 过程1 RENAME TO 过程2；

4、删除存储过程

DROP PROCEDURE 过程名();

2、函数

1、函数的定义语句格式。

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,...])RETURNS<类型>
AS <过程化SQL块>;

2、函数的执行语句格式。

CALL/SELECT 函数名 ([参数1,参数2,...]);

3、修改函数。

可以使用ALTER FUNCTION重命名一个自定义函数；

ALTER FUNCTION 过程名1 RENAME TO 过程名2；

可以使用ALTER FUNCTION重新编译一个函数

ALTER FUNCTION 函数名 COMPILE;

至此，例题全部完成，T-SQL与标准SQL有非常大的区别！加油！

感谢阅读！