数据库实验四-数据控制(完整性部分)

于 2022-04-21 19:58:11 发布
阅读量5k 收藏 39
点赞数 5
分类专栏： 数据库系统 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45755193/article/details/124330096
版权
数据控制(完整性部分)

实验环境：Windows 10操作系统、MySQL 8.0、Navicat

实验内容与完成情况：

1. 实验目的

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

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

2. 实验平台sql

sql 8.0及其交互式查询工具navicat。

3. 实验内容和要求

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

①第6题

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

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

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

【SQL语句编写】

CREATE DATABASE IF NOT EXISTS TEST4 CHARACTER SET 'utf8';

USE TEST4;

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

CREATE TABLE IF NOT EXISTS EMPLOYEE(

    EMPLOYEE_ID VARCHAR(10),

    EMPLOYEE_NAME VARCHAR(10) NOT NULL,

    -- 创建表时定义用户定义的完整性

    EMPLOYEE_AGE INT(2) CHECK(EMPLOYEE_AGE>=0 AND EMPLOYEE_AGE<=60),

    EMPLOYEE_POST VARCHAR(10) NOT NULL,

    EMPLOYEE_SALARY INT(10) NOT NULL,

    EMPLOYEE_DEPARTMENT_ID VARCHAR(10) NOT NULL,

    -- 创建表时定义定义实体完整性

    PRIMARY KEY(EMPLOYEE_ID)

);

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

CREATE TABLE IF NOT EXISTS DEPARTMENT(

    DEPARTMENT_ID VARCHAR(10),

    DEPARTMENT_NAME VARCHAR(10) NOT NULL,

    DEPARTMENT_MANAGER VARCHAR(10) NOT NULL,

    DEPARTMENT_PHONE VARCHAR(15) NOT NULL

);

-- 创建表后定义部门表的实体完整性（完整性约束命令子句）

ALTER TABLE DEPARTMENT ADD CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY(DEPARTMENT_ID);

--  创建表后定义参照完整性,职工表中部门号为外码，被参照表为部门

ALTER TABLE EMPLOYEE ADD FOREIGN KEY(EMPLOYEE_DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID);

【运行情况】

【实验要求完成情况】

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

  1. 定义每个模式的主码；

-- 对于职工表，创建表时定义定义实体完整性

PRIMARY KEY(EMPLOYEE_ID) -- 此处采用表级约束条件，也可采用列级约束条件

-- 对于部门表，创建表后定义部门表的实体完整性（完整性约束命令子句）

ALTER TABLE DEPARTMENT ADD CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY(DEPARTMENT_ID);

  1. 定义参照完整性；

-- 创建表后定义参照完整性,职工表中部门号为外码，被参照表为部门

ALTER TABLE EMPLOYEE ADD FOREIGN KEY(EMPLOYEE_DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID);

（3）定义职工年龄不得超过60岁。

-- 创建表时定义用户定义的完整性

EMPLOYEE_AGE INT(2) CHECK(EMPLOYEE_AGE>=0 AND EMPLOYEE_AGE<=60)

【实验验证违反了完整性约束条件时，系统是如何处理的】

首先插入几条正确数据，进行初始化

（1）向职工表中插入数据，若主码为空

不符合实体完整性-主码非空的条件，拒绝执行插入操作。

（2）向职工表中插入数据，若主码为重复

不符合实体完整性-主码唯一的条件，拒绝执行插入操作。

（3）向职工表中插入数据，若属性部门号在部门表中不存在

不符合参照完整性-外码不存在，拒绝执行插入操作。

（4）若直接删除部门表中某一数据，但职工表中存在该部门的员工

会破坏参照完整性，拒绝执行删除操作。

（5）向职工表中插入数据，年龄设为65

不符合用户定义的完整性，拒绝执行插入操作。

②第8题

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

【创建关系Female和关系Male】

CREATE DATABASE IF NOT EXISTS TEST4B CHARACTER SET 'utf8';

USE TEST4B;

/*

先创建关系Female和关系Male

男宾信息（邀请码，姓名，年龄，家庭住址），其中邀请码为主码；

女宾信息（邀请码，姓名，年龄，家庭住址），其中邀请码为主码；

*/

CREATE TABLE IF NOT EXISTS Male(

    MInvite_Code VARCHAR(10),

    Mname VARCHAR(5) NOT NULL,

    Mage INT(3) NOT NULL,

    Mhome VARCHAR(20) NOT NULL,

    PRIMARY KEY(MInvite_Code)

);

CREATE TABLE IF NOT EXISTS Female(

    FInvite_Code VARCHAR(10),

    Fname VARCHAR(5) NOT NULL,

    Fage INT(3) NOT NULL,

    Fhome VARCHAR(20) NOT NULL,

    PRIMARY KEY(FInvite_Code)

);

【将来宾的人数限制在50人以内】

-- 题目要求的断言语句的编写

CREATE ASSERTION Party_Num

    CHECK (

        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))<=50

);

-- 由于sql不支持断言语句，故我们这里用触发器完成实验

-- 为方便验证，我们这里不超过5人

CREATE TRIGGER Party_Num1

BEFORE INSERT ON Male

FOR EACH ROW

BEGIN

    IF(

        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))>=5)

        OR

        -- 触发器实现Male的MInvite_Code与Female的FInvite_Code不一样

        ((SELECT COUNT(*) FROM Female WHERE FInvite_Code=new.MInvite_Code)=1)

        THEN SET new.MInvite_Code=NULL;

    END IF;

END;

CREATE TRIGGER Party_Num2

BEFORE INSERT ON Female

FOR EACH ROW

BEGIN

    IF(

        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))>=5)

        OR

        -- 触发器实现Female的FInvite_Code与Male的MInvite_Code不一样

        ((SELECT COUNT(*) FROM Male WHERE MInvite_Code=new.FInvite_Code)=1)

        THEN SET new.FInvite_Code=NULL;

    END IF;

END;

两表的触发器均添加成功，不仅实现了题目中不超过50人（为方便验证设为了5，改为50即可）的要求，还额外实现了男宾邀请码不能与女宾邀请码相同的要求。

【实验验证违反了完整性约束条件时，系统是如何处理的】

首先插入四条正确数据，进行初始化

（1）向Male表中插入数据，若邀请码与Female中某一女宾的邀请码相同。

触发器被执行，无法插入成功。

（2）向Female表中插入数据，若邀请码与Male中某一男宾的邀请码相同。

触发器被执行，无法插入成功。

（3）再向Male表中插入一条正确数据，然后尝试在Male表中和Female表中插入一条数据。

当两表中一共有5条数据时，再向任一表插入数据，则触发器被执行，无法插入成功。

出现的问题：

①问题一：

-- 男女宾的邀请码不能重复，想使用域的完整性约束

CREATE DOMAIN Inv_Code VARCHAR(10) NOT NULL AND UNIQUE;

sql不支持域的完整性限制，即CREATE DOMAIN语句。

②问题二：

sql不支持断言，即CREATE ASSERTION语句。

-- 男女宾总数不能超过50人

CREATE ASSERTION Party_Num

    CHECK (

        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))<=50

    );

解决方案：

  • 问题一：

为实现男女宾的邀请码不同，采用触发器，在Male触发器的IF条件下实现Male的MInvite_Code与Female的FInvite_Code不一样

((SELECT COUNT(*) FROM Female WHERE FInvite_Code=new.MInvite_Code)=1)

在Female触发器的IF条件下实现Female的FInvite_Code与Male的MInvite_Code不一样

((SELECT COUNT(*) FROM Male WHERE MInvite_Code=new.FInvite_Code)=1)

  • 问题二：

采用触发器：

CREATE TRIGGER Party_Num1

BEFORE INSERT ON Male

FOR EACH ROW

BEGIN

    IF(

        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))>=50)

        THEN SET new.MInvite_Code=NULL;

    END IF;

END;

心得体会：

通过本次实验再结合上一次的安全性实验，我更加清晰地了解到数据库完整性和安全性的区别，数据库完整性主要为了防止数据库中存在不符合语义的数据，对象是不合语义、不正确的数据；数据库安全性是为了保护数据库防止恶意破坏和非法存取，对象是非法用户、非法操作。

完整性又分为三种，分别是实体完整性、参照完整性以及用户定义的完整性，每一次设定好完整性约束条件，系统都会自动进行完整性检查和进行违约处理。

SQL语句中，断言和域中的完整性限制在MySQL数据库中都无法运用，需要通过触发器来完成这两类操作。通过编写本次实验，理解和掌握了数据库触发器的分类，了解和掌握各类数据库触发器的设计和使用，包括创建、使用、删除等基本功能，并能设计和执行相应的SQL语句来完成功能。

【实验源码-便于复制】

/*第6题：假设有下面两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；
部门（部门号，名称，经理名，电话），其中部门号为主码。
用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：
（1）定义每个模式的主码；
（2）定义参照完整性；
（3）定义职工年龄不得超过60岁。
*/
CREATE DATABASE IF NOT EXISTS TEST4 CHARACTER SET 'utf8';
USE TEST4;
-- 职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码
CREATE TABLE IF NOT EXISTS EMPLOYEE(
    EMPLOYEE_ID VARCHAR(10),
    EMPLOYEE_NAME VARCHAR(10) NOT NULL,
    -- 创建表时定义用户定义的完整性
    EMPLOYEE_AGE INT(2) CHECK(EMPLOYEE_AGE>=0 AND EMPLOYEE_AGE<=60),
    EMPLOYEE_POST VARCHAR(10) NOT NULL,
    EMPLOYEE_SALARY INT(10) NOT NULL,
    EMPLOYEE_DEPARTMENT_ID VARCHAR(10) NOT NULL,
    -- 创建表时定义定义实体完整性
    PRIMARY KEY(EMPLOYEE_ID)
);
-- 部门（部门号，名称，经理名，电话），其中部门号为主码
CREATE TABLE IF NOT EXISTS DEPARTMENT(
    DEPARTMENT_ID VARCHAR(10),
    DEPARTMENT_NAME VARCHAR(10) NOT NULL,
    DEPARTMENT_MANAGER VARCHAR(10) NOT NULL,
    DEPARTMENT_PHONE VARCHAR(15) NOT NULL
);
-- 创建表后定义部门表的实体完整性（完整性约束命令子句）
ALTER TABLE DEPARTMENT ADD CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY(DEPARTMENT_ID);
--  创建表后定义参照完整性,职工表中部门号为外码，被参照表为部门
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(EMPLOYEE_DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID);
-- 插入部门数据
INSERT INTO DEPARTMENT VALUES('D001','财务部','张三','010-1234567');
INSERT INTO DEPARTMENT VALUES('D002','人事部','李四','010-7654321');
-- 插入职工数据
INSERT INTO EMPLOYEE VALUES('E001','张三','20','经理','5000','D001');
INSERT INTO EMPLOYEE VALUES('E002','李四','30','经理','6000','D001');
INSERT INTO EMPLOYEE VALUES('E003','王五','40','经理','7000','D002');
INSERT INTO EMPLOYEE VALUES('E004','赵六','50','经理','8000','D002');
INSERT INTO EMPLOYEE VALUES('E005','钱七','60','经理','9000','D002');
-- 若直接删除部门表中某一数据，但职工表中存在该部门的员工
DELETE FROM DEPARTMENT WHERE DEPARTMENT_ID='D001';

/*
第8题：某单位想举行一个小型的联谊会，
关系Male记录注册的男宾信息，
关系Female记录注册的女宾信息，
建立一个断言，将来宾的人数限制在50人以内。
*/
CREATE DATABASE IF NOT EXISTS TEST4B CHARACTER SET 'utf8';
USE TEST4B;
/*
（提示，先创建关系Female和关系Male）
男宾信息（邀请码，姓名，年龄，家庭住址），其中邀请码为主码；
女宾信息（邀请码，姓名，年龄，家庭住址），其中邀请码为主码；
*/
CREATE TABLE IF NOT EXISTS Male(
    MInvite_Code VARCHAR(10),
    Mname VARCHAR(5) NOT NULL,
    Mage INT(3) NOT NULL,
    Mhome VARCHAR(20) NOT NULL,
    PRIMARY KEY(MInvite_Code)
);
CREATE TABLE IF NOT EXISTS Female(
    FInvite_Code VARCHAR(10),
    Fname VARCHAR(5) NOT NULL,
    Fage INT(3) NOT NULL,
    Fhome VARCHAR(20) NOT NULL,
    PRIMARY KEY(FInvite_Code)
);

-- 题目要求的断言语句的编写
CREATE ASSERTION Party_Num
    CHECK (
        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))<=50
    );
-- 由于MySQL不支持断言语句，故我们这里用触发器完成实验
-- 为方便验证，我们这里不超过5人
CREATE TRIGGER Party_Num1
BEFORE INSERT ON Male
FOR EACH ROW
BEGIN
    IF(
        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))>=5)
        OR
        -- 触发器实现Male的MInvite_Code与Female的FInvite_Code不一样
        ((SELECT COUNT(*) FROM Female WHERE FInvite_Code=new.MInvite_Code)=1)
        THEN SET new.MInvite_Code=NULL;
    END IF;
END;
CREATE TRIGGER Party_Num2
BEFORE INSERT ON Female
FOR EACH ROW
BEGIN
    IF(
        ((SELECT COUNT(*) FROM Male) +(SELECT COUNT(*) FROM Female))>=5)
        OR
        -- 触发器实现Female的FInvite_Code与Male的MInvite_Code不一样
        ((SELECT COUNT(*) FROM Male WHERE MInvite_Code=new.FInvite_Code)=1)
        THEN SET new.FInvite_Code=NULL;
    END IF;
END;

-- 向Male表插入数据
INSERT INTO Male VALUES('M001','张三','20','天马小区');
INSERT INTO Male VALUES('M002','李四','22','天马小区');
INSERT INTO Male VALUES('M003','王五','22','天马小区');
INSERT INTO Male VALUES('M004','赵六','22','天马小区');

-- 向Female表插入数据
INSERT INTO Female VALUES('F001','小花','18','天马小区');
INSERT INTO Female VALUES('F002','小芳','20','天马小区');
INSERT INTO Female VALUES('F003','小小','20','天马小区');

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

我是蒸饺吖～

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值