《数据库系统概论》课程之实验四数据控制(完整性部分)

实验目的

（1）熟悉通过SQL对数据进行完整性控制。

（2）完成书本上习题的上机练习。

实验平台MySQL

MySQL8.0及其图形化工具Navicat Premium。

实验内容和要求

​ 使用SQL对数据进行完整性控制（3类完整性、CHECK短语、CONSTRAIN子句、触发器）。用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。根据以下要求认真填写实验报告，记录所有的实验用例。

教材：《数据库系统概论》第5版——第五章习题P172

第6题

假设有下面两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；

部门（部门号，名称，经理名，电话），其中部门号为主码。

用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：

（1）定以每个模式的主码；（2）定义参照完整性；（3）定义职工年龄不得超过60岁。

SQL实现

CREATE TABLE 部门(
	部门号 VARCHAR(31) PRIMARY KEY, -- 在列级定义主码，实现实体完整性
	名称 VARCHAR(15),
	经理名 VARCHAR(15),
	电话 VARCHAR(15)
);

CREATE TABLE 职工 
(
	职工号 VARCHAR(31), 
	姓名 VARCHAR(15),
	年龄 INT,
	CHECK (年龄<=60),	-- 属性上的约束条件
	职务 VARCHAR(15),
	工资 DOUBLE,
	部门号 VARCHAR(31),
    PRIMARY KEY(职工号), -- 在表级定义主码，实现实体完整性
	FOREIGN KEY(部门号) REFERENCES 部门(部门号) -- 参照完整性
);

测试：

• 先添加“部门”数据：

  INSERT INTO 部门 VALUES(2,'保卫部','小明',12345678);

  

• 添加年龄超过60岁的职工，失败

  INSERT INTO 职工 VALUES(2,'小强',65,'保安',2000,2);

  

• 添加年龄不超过60岁的职工，成功

  INSERT INTO 职工 VALUES(3,'小刚',55,'保安',2000,2);

  

第8题

​ 某单位想举行一个小型的联谊会，关系Male记录注册的男宾信息，关系Female记录注册的女宾信息，建立一个断言，将来宾的人数限制在50人以内。（提示，先创建关系Female和关系Male）。

SQL实现

• 先创建关系，即建表：

  CREATE TABLE Male(
  	id VARCHAR(31) PRIMARY KEY,
  	name VARCHAR(15)
  );
  
  CREATE TABLE Female(
  	id VARCHAR(31) PRIMARY KEY,
  	name VARCHAR(15)
  );

  

• 由于MySQL不支持ASSERTION断言，故使用触发器实现将来宾的人数限制在50人以内。

  delimiter //
  create trigger count_check_male before insert 
  on Male for each row
  begin
      declare msg varchar(200);
      declare count int;
      set count = (select count(*) from Male) + (select count(*) from Female);
      if (count >= 50) then  								-- 当前总来宾数为50
          set msg = "The maximum of guest is 50.";    
          signal sqlstate 'HY000' SET message_text = msg;     -- 触发Mysql错误信号，定义其信息
      end if;
  end //
  
  create trigger count_check_female before insert 
  on Female for each row
  begin
      declare msg varchar(200);
      declare count int;
      set count = (select count(*) from Male) + (select count(*) from Female);
      if (count >= 50) then  								-- 当前总来宾数为50
          set msg = "The maximum of guest is 50.";    
          signal sqlstate 'HY000' SET message_text = msg;    -- 触发Mysql错误信号，定义其信息
      end if;
  end //
  delimiter ;

测试：

• 50人数目过多，将触发器条件count >= 50改为count >= 5进行测试

• 查看当前两表字段数：

  SELECT COUNT(*) FROM Male;
  SELECT COUNT(*) FROM Female;

  

  

• 再往Male表插入信息，插入失败得到错误信息：

  INSERT INTO Male VALUES(6,'F');

  

实验心得

通过这次实验，我熟悉了如何在MySQL中使用SQL语句进行完整性控制，主要收获如下。

在第6题中，我掌握了如何在定义模式，即建表时实现数据库完整性，分别为：

• 使用PRIMARY KEY定义主码实现实体完整性，可以在列级定义或表级定义。
• 通过定义FOREIGN KEY实现参照完整性。注意，若要在定义模式时定义外键，需要先定义主键作为其他表的外键的那个表。
• 通过CHECK实现属性的约束条件。

在第7题中，由于MySQL不支持ASSERTION断言，故使用触发器实现断言的功能。对于某个操作的触发器，可以在其实现中加入判断语句，并使用signal sqlstate 'HY000'定义错误信号使在某个条件下该操作能够失效，并输出错误信息。此外，通过巧妙的方法可以更方便地对我们的实现进行测试，保证其正确性。