MySQL 实验四——数据库完整性

数据库完整性

目录

数据库完整性

一、实验目的

二、实验环境

三、实验内容

四、实验步骤

---

一、实验目的

（1）能够运用实体完整性、参照完整性和用户自定义完整性方法，维护数据库的完整性。

（2）能够设计SQL语句验证完整性约束是否起作用。

（3）结合具体例子阐述主键和外键的使用。

（4）能够基于数据库完整性对数据库应用系统开展方案设计，了解完整性对数据库设计的影响。

（5）能够运用触发器对实验数据进行整理、分析和解释，并能通过信息综合得出有效结论。

二、实验环境

操作系统：Windows    11

应用软件：MySQL  5.7   Navicat12

三、实验内容

任务一：

1. 有一个职工数据库的关系模式如下：

职工employee (职工号empno，姓名ename，年龄eage，职务ejob，工资esalary，部门号deptno)

部门dept(部门号deptno，名称dname，地址loc)

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

(1）定义每个模式的主码

(2)定义参照完整性，要求删除部门时职工对应的部门号设置为空，修改部门号时职工对应的部门号自动修改；

(3)定义职工的职务不能为空，职工年龄不得超过60岁，部门的名称必须唯一。

(4)向部门表中添加如下记录，体会UNIQUE约束的作用。

10，‘市场部’，‘天津’

20，‘销售部’，‘上海’

30，‘财务部’，‘北京’

40，‘人力部’，‘南京’

50，‘人力部’，‘郑州’

(5)向职工表中添加如下记录，体会NOT NULL约束的作用：

7255，‘翁磊’，34，‘销售员’，5500.0，20

7255，‘刘津’，40，‘销售员’，5700.0，20

7123，‘李明’，35，‘产品经理’，6000.0，10

7326，‘翁磊’，34，NULL，5800.0，30

(6)删除部门表中的市场部的记录，修改部门表中销售部的部门号为22，检查职工表相应属性列上的值，体会外键约束的作用。

任务二：

在任务一的职工数据库的基础上，完成下列触发器工具的设计、开发和使用验证任务。

新增部门变动历史表dept_history和工资变动历史表sal_history

部门变动历史表：dept_history(dhid, eid, olddept, newdept, uptime)

其中dhid为部门变动编号（自动增长，无需赋值），eid为员工编号， olddept为员工老部门， newdept为员工新部门，uptime为部门变动的修改时间。

工资变动历史表：sal_history(shid, eid, oldsal, newsal, uptime)，其中shid为工资变动编号（自动增长，无需赋值），eid为员工编号，oldsal为变动前的工资，newsal为变动后的工资，uptime为工资变动的修改时间。

具体操作任务为：

（1）建dept_history表，空表，便于对比观察触发器运行后的变化；

（2）建sal_history表，空表，便于对比观察触发器运行后的变化；

（3）创建触发器：当新职工入职时，员工信息表将插入1条数据。同时，触发器在部门变动历史中增加1条记录，其中olddept值为null；在工资变动历史中增加1条记录，其中oldsal值为0。

（4）创建触发器：当新职工部门或工资发生变化时，触发器执行以下操作。若部门发生变动，则在部门变动历史中增加1条记录；若工资发生变动，则在工资变动历史中增加1条记录。

四、实验步骤

任务一：

1.创建数据库（数据库名尽量用英文）

create database company
use company

2.创建dept

CREATE TABLE dept
(deptno INT(10) PRIMARY KEY,
dname CHAR(10) UNIQUE,
loc CHAR(10)
);

3.创建employee

CREATE TABLE employee
(empno CHAR(10) PRIMARY KEY,
ename CHAR(10) ,
eage INT(10),
CONSTRAINT C1 CHECK(eage<=60),
ejob CHAR(10) not null,
esalary CHAR(10),
deptno int (10),
FOREIGN KEY (deptno) REFERENCES dept(deptno)
on DELETE set NULL
on UPDATE CASCADE
);

4.向部门表中添加如下记录，体会UNIQUE约束的作用。
10，‘市场部’，‘天津’
20，‘销售部’，‘上海’
30，‘财务部’，‘北京’
40，‘人力部’，‘南京’
50，‘人力部’，‘郑州’

INSERT into dept VALUES (10, '市场部' ,'天津');
INSERT into dept VALUES (20, '销售部' ,'上海');
INSERT into dept VALUES (30, '财务部' ,'北京');
INSERT into dept VALUES (40, '人力部' ,'南京');
INSERT into dept VALUES (50, '人力部' ,'郑州');

5.向职工表中添加如下记录，体会NOT NULL约束的作用：
7255，‘翁磊’，34，‘销售员’，5500.0，20
7255，‘刘津’，40，‘销售员’，5700.0，20
7123，‘李明’，35，‘产品经理’，6000.0，10
7326，‘翁磊’，34，NULL，5800.0，30

INSERT into employee VALUES (7255,'翁磊',34,'销售员',5500.0,20);
INSERT into employee VALUES (7255,'刘津',40,'销售员',5700.0,20);
INSERT into employee VALUES (7123,'李明',35,'产品经理',6000.0,10);
INSERT into employee VALUES (7326,'翁磊',34, NULL,5800.0,30);

(6)删除部门表中的市场部的记录，修改部门表中销售部的部门号为22，检查职工表相应属性列上的值，体会外键约束的作用。

DELETE from dept where dname='市场部';
update dept set deptno=22 where dname='销售部';

任务二：

（1）创建部门变动历史表dept_history

CREATE TABLE dept_history (
	dhid INT PRIMARY KEY AUTO_INCREMENT,
	eid CHAR (6),
	olddept VARCHAR (10),
	newdept VARCHAR (10),
	uptime datetime 
) ;

（2）创建工资变动历史表sal_history

CREATE TABLE sal_history ( 
shid INT PRIMARY KEY AUTO_INCREMENT, 
eid CHAR (6), 
oldsal INT, 
newsal INT, 
uptime datetime 
);

（3）创建触发器：当新职工入职时，员工信息表将插入1条数据。同时，触发器在部门变动历史中增加1条记录，其中olddept值为null；在工资变动历史中增加1条记录，其中oldsal值为0。

DELIMITER $
CREATE TRIGGER emp_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
-- 触发器在部门变动历史中增加1条记录
	INSERT INTO dept_history ( eid, olddept, newdept )
	VALUES( new.empno, NULL, new.deptno);
-- 	在工资变动历史中增加1条记录
	INSERT INTO sal_history ( eid, oldsal, newsal )
	VALUES( new.empno, 0, new.esalary );
END $
DELIMITER ;

（4）创建触发器：当新职工部门或工资发生变化时，触发器执行以下操作。若部门发生变动，则在部门变动历史中增加1条记录；若工资发生变动，则在工资变动历史中增加1条记录。

DELIMITER $
CREATE TRIGGER emp_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
-- 当新职工部门或工资发生变化时，
 	IF
	(old.deptno != new.deptno)
	THEN
	INSERT INTO dept_history ( eid, olddept, newdept )
	VALUES(new.empno, old.deptno, new.deptno);
	END IF;
-- 	若工资发生变动
	IF
	(old.esalary != new.esalary) 
	THEN
	INSERT INTO sal_history ( eid, oldsal, newsal)
	VALUES(new.empno, old.esalary, new.esalary);	
	END IF;
END $
DELIMITER ;