数据库作业十三

数据库完整性

1.实体完整性
实体完整性(英语：Entity integrity)是在关系模型中，数据库完整性三项规则的其中之一。实体完整性这项规则要求每个数据表都必须有主键，而作为主键的所有字段，其属性必须是独一及非空值。
将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),
PRIMARY KEY(Sno)
)

例5.2

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,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)    
    )

显示说明参照完整性的违约处理示例

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  --级联删除
  ON UPDATE CASCADE, --级联更新
FOREIGN KEY Cno REFERENCES Student(Cno)
  ON DELETE NO ACTION  --拒绝删除
  ON UPDATE CASCADE --级联更新
)

用户自定义的完整性
用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
例如某个属性必须取唯一值，某个非主属性也不能取空值，某个属性的取值范围在0-100之间等
在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),
)

建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主键

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

Student表的Ssex只允许取“男”或“女”

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

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)
           )

当学生的性别是男时，其名字不能以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.%')
      )

完整性约束命名子句
语法：

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

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

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

建立教师表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 TEACHERKey FOREIGN KEY (Deptno)
   REFERENCES DEPT(Deptno),
CONSTRAINT C5 CHECK(Sal + Deduct >= 3000)
)

去掉5.10中Student1表中的对性别的限制

ALTER TABLE Student1
DROP CONSTRAINT C4

修改Student1表中的条件，要求学号改为900000~999999之间，年龄在由小于30改为小于40.

--先删掉以前的约束，再建立新的约束
ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999),
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK(Sage < 40)

断言

语法：

--创建断言
CREATE ASSERTION<断言名><CHECK 子句>
--删除断言
DROP ASSERTION <断言名>

限制数据库课程最多60名学生选修

CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60 >= (SELECT count(*)
              FROM Course,SC
		      WHERE SC.Cno=Course.Cno and Course.Cname =N'数据库')
		   	)

限制每一门课程最多60名学生选修

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

TRIGGER 触发器

语法：

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

--删除触发器
DROP TRIGGER <触发器名> ON <表名>

当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：
SC_U（Sno,Cno,Oldgrade,Newgrade）
其中Oldgrade是修改前的分数，Newgrade是修改后的分数

CREATE TRIGGER  SC_T		
AFTER UPDATE OF Grade ON SC
REFERENCING
	 OLD row  AS  OldTuple,
	 NEW row AS  NewTuple
FOR EACH ROW 	
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)
	 INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)  
VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中

CREATE TRIGGER Student_Count
AFTER INSERT ON Student  	         
REFERENCING
 	NEW TABLE AS DELTA
FOR EACH STATEMENT  
    INSERT INTO StudentInsertLog (Numbers)
	SELECT COUNT(*) FROM DELTA

定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”

CREATE TRIGGER Insert_Or_Update_Sal 
BEFORE INSERT OR UPDATE ON Teacher     
FOR EACH ROW       
BEGIN                         
  IF (new.Job='教授') AND (new.Sal < 4000) 
     THEN  new.Sal :=4000;                
   	 END IF
END                          

定义触发器

CREATE TRIGGER Student_Time
ON Student  	         
AFTER
INSERT
AS 
	declare @UserName    nchar(10)
	declare @DateTime    datetime
	
	select @UserName = system_user
	select @DateTime = CONVERT(datetime,GETDATE(),120) --2018-04-11 16:33:10

	INSERT INTO StudentInsertLogUser(UserName,DateAndTime)
	VALUES (@UserName,@DateTime)

PROCEDURE 存储过程

语法

--创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>
--执行存储过程
CALL/PERFORM  PROCEDURE 过程名([参数1,参数2,...])
--修改存储过程
ALTER PROCEDURE 过程名1  RENAME TO 过程名2
--删除存储过程
DROP  PROCEDURE 过程名()

利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中

--标准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 Accout
	            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=inAccount;
   IF inAccount IS NULL THEN  		
	  ROLLBACK; 	         	 		
	  RETURN;
   END IF;
   UPDATE Account SET total=total-amount
    WHERE accountnum=outAccount; /
   UPDATE Account SET total=total + amount 
    WHERE   accountnum=inAccount; 
   COMMIT;                       	
END;

--T-SQL形式
IF (exists (select * from sys.objects where name = 'Proc_TRANSFER'))
    DROP PROCEDURE Proc_TRANSFER
GO
CREATE PROCEDURE Proc_TRANSFER 
@inAccount INT,@outAccount  INT,@amount FLOAT
 
AS
BEGIN TRANSACTION TRANS   
   	DECLARE		
	@totalDepositOut Float,
	@totalDepositIn Float,
	@inAccountnum INT
    
	SELECT @totalDepositOut = total FROM Account	WHERE accountnum = @outAccount
	
	IF @totalDepositOut IS NULL               	   
		BEGIN
			PRINT '转出账户不存在或账户中没有存款'
			ROLLBACK TRANSACTION TRANS 	   
			RETURN
		END

	IF @totalDepositOut < @amount     	
		BEGIN
			PRINT '账户存款不足'
			ROLLBACK TRANSACTION TRANS 				
			RETURN
		END
	  
	SELECT @inAccountnum = accountnum  FROM Account	WHERE accountnum = @inAccount
	 
	IF @inAccountnum IS NULL   		                       
		BEGIN
			PRINT '转入账户不存在'
			ROLLBACK TRANSACTION TRANS          	 		
			RETURN
		END
	
	BEGIN
		UPDATE Account SET total = total - @amount	WHERE	accountnum = @outAccount
		UPDATE Account SET total = total + @amount	WHERE   accountnum = @inAccount
		PRINT '转账完成，请取走银行卡'
		COMMIT TRANSACTION TRANS                       	
		RETURN
	END

---

触发器以前不常用，突然一写有些手生。多写几遍后，写起来还可以。