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

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

实验目的

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

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

实验平台MySQL

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

实验内容和要求

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

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

第6题

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

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

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

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

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

SQL实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 部门(部门号) -- 参照完整性
);

image-20220413111847856

测试：

  • 先添加“部门”数据：

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

    image-20220413104315269

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

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

    image-20220413104554984

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

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

    image-20220413104655878

第8题

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

SQL实现

  • 先创建关系，即建表：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE Male(
    	id VARCHAR(31) PRIMARY KEY,
    	name VARCHAR(15)
    );

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

    image-20220413105205330

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    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进行测试

  • 查看当前两表字段数：

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

    image-20220413111017559

    image-20220413111031703

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

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

    image-20220413111404410

实验心得

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

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

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

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