数据库课程设计(mysql命令行)
------必做内容(工程薪资基本项目管理)：
前言(课设要求)
1、项目需求简述
- 每位职工可以参加几个不同的工程，且每个工程有多名职工参与；
- 每位职工有一个职位，且多名职工可能有相同的职位；
- 职位决定小时工资率，相同的职位具有相同的小时工资率；
- 工程的基本信息包括：工程号和工程名称，工程所在地址，起始时间，结束时间；
- 职工的相关信息包括：职工号、姓名(职位和酬金?)；
- 企业按职工在每一个工程中完成的工时，计算酬金。
- 员工档案管理 员工有新增、离开、部门变更
- 其它业务：企业有不同的部门(部门编号及名称)组成，每个员工隶属于部门之一; 职工参加工程上班签到和下班签到
2、要求
- 应用规范设计法设计该数据库，要求达到3NF。请给出详细设计过程(E-R及E-R到关系表清晰的转换关系说明)。
- 指出每个关系的主码和外码。
- 设计一个存储过程(函数)，以工程编号作为输入参数，实现计算某工程所支付的工人酬金。没有该工程返回-1
- 实现员工变更部门的存储过程，成功返回 1，否则返回0；变更过程计入档案；职工号/变更类型/原先部分(新增null)/新的部门(离开null)/日期/经手人
- 员工参加工程的上班和下班 存储过程/函数(注意时间点 早上6：之前和晚上12点后 不允许操作，当天没有上班的不允许下班的操作…)
3、成果形式
- 文档 E-R总体图(实体名称/联系属性/联系类型)，每个实体详细的属性及其候选码属性可以另行标注(实体图或者实体名(x,x…))
- DDL语句 建表/约束、存储过程或函数
- 数据初始化 一定数量的
- 测试存储过程/函数 截图记录
- 实验体会
正文
根据项目需求简述,我们可以分析出以下几条基本的结论:
- 职工与工程之间的关系: 多对多
- 职工与职位之间的关系: 多对1
- 职工与部门之间的关系: 多对1
- 职位与小时工资率之间的关系: 一对一
- 工程具有的属性: 工程号,工程名称,工程所在地址,起始时间,结束时间
- 职工具有的属性：职工号,职工姓名
- 部门具有的属性: 部门编号,部门名称
- 职位具有的属性: 职位名,小时工资率
1)需求设计
实体呈现
根据上述分析,我决定将 职工,职位,工程,部门当做基本的实体,如下
注:加粗部分为候选码
ER图呈现
关系模型+转换步骤
1)ER图转为关系表
根据ER图先将其转化为几个基本的关系表,转换原则
- 1:n 1端的候选码属性必须放入n端扩充
- m:n 两端的候选码都要加入到派生表中
得到如下四个表
其中职位名和部门编号为外键, 酬金表是职工和工程的派生表
工程（工程号，工程名,工程所在地,工程开始时间,工程结束时间）
职工（职工号，职工姓名, 职位名, 部门编号）
职位 (职位名, 小时工薪率)
酬金（工程号，职工号，工时）
部门（部门编号，部门名称）
2)修改表结构
目标:达到3NF范式
显然 工程表,酬金表,部门表,职工表都 满足 3NF
所以这里无需修改表结构
3)最终关系表
注(加粗代表主键,下划线代表外键)
工程表（工程号，工程名,工程所在地,工程开始时间,工程结束时间）
职工表（职工号，职工姓名, 职位名, 部门编号）
职位表 (职位名, 小时工薪率)
酬金表（工程号，职工号，工时）
部门表（部门编号，部门名称）
2)创建表+插入数据
1.数据库创建
CREATE DATABASE IF NOT EXISTS `salary`
USE `salary`;2.表的操作
部门表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`dno` varchar(20) NOT NULL,
`dname` varchar(45) NOT NULL,
PRIMARY KEY (`dno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `department` WRITE;
INSERT INTO `department` VALUES ('CW','财务部'),('RS','人事部'),('SC','市场部'),('YF','研发部');
UNLOCK TABLES;职工表
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`eno` varchar(20) NOT NULL,
`ename` varchar(45) NOT NULL,
`epost` varchar(45) NOT NULL,
`dno` varchar(20) NOT NULL,
PRIMARY KEY (`eno`),
KEY `c1_idx` (`dno`),
KEY `e2_idx` (`epost`),
CONSTRAINT `e1` FOREIGN KEY (`dno`) REFERENCES `department` (`dno`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `e2` FOREIGN KEY (`epost`) REFERENCES `job` (`epost`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `employee` WRITE;
INSERT INTO `employee` VALUES ('CW01','方平','会计','CW'),('RS01','张峰','主任','RS'),('SC01','楚阳','业务员','SC'),('SC02','宁缺','服务员','SC'),('YF01','君陌','技术员','YF');
UNLOCK TABLES;职位表
DROP TABLE IF EXISTS `job`;
CREATE TABLE `job` (
`epost` varchar(45) NOT NULL,
`hourrate` varchar(45) NOT NULL,
PRIMARY KEY (`epost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `job` WRITE;
INSERT INTO `job` VALUES ('业务员','250'),('主任','500'),('会计','200'),('技术员','300'),('服务员','80');
UNLOCK TABLES;工程表
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
`pno` varchar(20) NOT NULL,
`pname` varchar(45) NOT NULL,
`paddr` varchar(45) NOT NULL,
`pstart` date DEFAULT NULL,
`pfinal` date DEFAULT NULL,
PRIMARY KEY (`pno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `project` WRITE;
INSERT INTO `project` VALUES ('001','house','宁波','2020-04-11','2020-08-11'),('002','hospital','杭州','2020-03-30','2021-04-03'),('003','store','温州','2020-01-24','2020-06-28'),('004','park','上海','2020-01-01','2020-11-24'),('005','bigdata','东北','2020-03-15','2020-12-31');
UNLOCK TABLES;酬金表
DROP TABLE IF EXISTS `reward`;
CREATE TABLE `reward` (
`pno` varchar(20) NOT NULL,
`eno` varchar(20) NOT NULL,
`worktime` varchar(45) NOT NULL,
PRIMARY KEY (`pno`,`eno`),
KEY `eno_idx` (`eno`),
CONSTRAINT `c1` FOREIGN KEY (`pno`) REFERENCES `project` (`pno`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `c2` FOREIGN KEY (`eno`) REFERENCES `employee` (`eno`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `reward` WRITE;
INSERT INTO `reward` VALUES ('001','CW01','5'),('001','SC02','8'),('002','SC01','6'),('002','YF01','7'),('003','RS01','4'),('003','YF01','6'),('004','CW01','6'),('004','RS01','8'),('004','SC01','3'),('005','CW01','7'),('005','SC02','4'),('005','YF01','3');
UNLOCK TABLES;档案表
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`eno` varchar(45) NOT NULL,
`reason` varchar(45) NOT NULL,
`olddno` varchar(45) DEFAULT NULL,
`newdno` varchar(45) DEFAULT NULL,
`date` varchar(45) NOT NULL,
`who` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;已插入的数据如下
mysql> select * from department;
+-----+--------+
| dno | dname |
+-----+--------+
| CW | 财务部 |
| RS | 人事部 |
| SC | 市场部 |
| YF | 研发部 |
+-----+--------+
4 rows in set (0.00 sec)
mysql> select * from employee;
+------+-------+--------+-----+
| eno | ename | epost | dno |
+------+-------+--------+-----+
| CW01 | 方平 | 会计 | CW |
| RS01 | 张峰 | 主任 | RS |
| SC01 | 楚阳 | 业务员 | SC |
| SC02 | 宁缺 | 服务员 | SC |
| YF01 | 君陌 | 技术员 | YF |
+------+-------+--------+-----+
5 rows in set (0.00 sec)
mysql> select * from job;
+--------+----------+
| epost | hourrate |
+--------+----------+
| 业务员 | 250 |
| 主任 | 500 |
| 会计 | 200 |
| 技术员 | 300 |
| 服务员 | 80 |
+--------+----------+
5 rows in set (0.00 sec)
mysql> select * from project;
+-----+----------+-------+------------+------------+
| pno | pname | paddr | pstart | pfinal |
+-----+----------+-------+------------+------------+
| 001 | house | 宁波 | 2020-04-11 | 2020-08-11 |
| 002 | hospital | 杭州 | 2020-03-30 | 2021-04-03 |
| 003 | store | 温州 | 2020-01-24 | 2020-06-28 |
| 004 | park | 上海 | 2020-01-01 | 2020-11-24 |
| 005 | bigdata | 东北 | 2020-03-15 | 2020-12-31 |
+-----+----------+-------+------------+------------+
5 rows in set (0.00 sec)
mysql> select * from reward;
+-----+------+----------+
| pno | eno | worktime |
+-----+------+----------+
| 001 | CW01 | 5 |
| 001 | SC02 | 8 |
| 002 | SC01 | 6 |
| 002 | YF01 | 7 |
| 003 | RS01 | 4 |
| 003 | YF01 | 6 |
| 004 | CW01 | 6 |
| 004 | RS01 | 8 |
| 004 | SC01 | 3 |
| 005 | CW01 | 7 |
| 005 | SC02 | 4 |
| 005 | YF01 | 3 |
+-----+------+----------+
12 rows in set (0.00 sec)3)存储过程
函数function1
1.设计一个存储过程(函数)，以工程编号作为输入参数，实现计算某工程所支付的工人酬金。没有该工程返回-1
代码
delimiter //
drop procedure if exists calSalary;
create procedure calSalary(in inpno varchar(20))
begin
declare flag int;
declare tmpresult int;
select if(inpno not in (select pno from project),-1,0) into flag;
if flag=-1 then
select flag;
else
select sum(hourrate*worktime) result into tmpresult FROM job,employee,reward where reward.pno=inpno and reward.eno=employee.eno and employee.epost=job.epost;
select tmpresult;
end if;
end //
delimiter ;测试结果
mysql> call calSalary(001)
-> ;
+-----------+
| tmpresult |
+-----------+
| 1640 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call calSalary(111)
-> ;
+------+
| flag |
+------+
| -1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)函数function2
2.实现员工变更部门的存储过程，成功返回 1，否则返回0；变更过程计入档案；职工号/变更类型/原先部分(新增null)/新的部门(离开null)/日期/经手人
这里的需要用到档案,所以我们选择新建一个change表
change (职工号,变更类型,原先部分(新增null),新的部门(离开null),日期,经手人)
这道题有2个要求,一个是对职工表的修改,一个是记录过程进入档案
代码
delimiter //
drop procedure if exists move;
create procedure move(
in ineno varchar(20),
in change_reason varchar(20),
in new_dno varchar(20),
in who varchar(20)
)
begin
declare results varchar(3);
declare flag int;
declare date varchar(45);
SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d %T') into date;
select if(ineno not in (select eno from employee),-1,0) into flag;
if flag=-1 then
INSERT INTO `salary`.`employee` (`eno`, `dno`) VALUES (ineno,new_dno);
set results = '';
else
UPDATE `salary`.`employee` SET `dno` = new_dno WHERE (`eno` = ineno);
SELECT dno into results FROM employee as e where eno = ineno;
end if;
INSERT INTO `salary`.`message` (`eno`, `reason`, `olddno`, `newdno`, `date`, `who`) VALUES (ineno, change_reason, results, new_dno, date, who);
select 1;
end //
delimiter ;测试结果
这是现在的employee和message表,我们看看接下来他发生的变化
mysql> select * from message;
+----+------+----------+--------+--------+---------------------+--------+
| id | eno | reason | olddno | newdno | date | who |
+----+------+----------+--------+--------+---------------------+--------+
| 5 | RS01 | 换部门 | 人事部 | 技术部 | 2020-04-11 16:09:31 | 董事长 |
| 27 | RS02 | 新人入部 | null | CW | 2020-04-11 18:56:18 | 经理 |
| 28 | RS01 | 更换部门 | RS | CW | 2020-04-11 18:56:24 | 董事长 |
| 29 | RS03 | 新人入部 | null | CW | 2020-04-11 18:57:37 | 经理 |
| 30 | RS01 | 更换部门 | CW | YF | 2020-04-11 18:57:45 | 董事长 |
| 31 | YF01 | 更换部门 | YF | RS | 2020-04-11 18:59:00 | 董事长 |
| 32 | RS01 | 变更 | CW | CW | 2020-05-06 11:53:55 | TTY |
+----+------+----------+--------+--------+---------------------+--------+
7 rows in set (0.00 sec)
mysql> select * from employee;
+------+-------+--------+------+
| eno | ename | epost | dno |
+------+-------+--------+------+
| CW01 | 方平 | 会计 | CW |
| RS01 | 张峰 | 主任 | CW |
| RS02 | NULL | NULL | CW |
| RS03 | NULL | NULL | CW |
| SC01 | 楚阳 | 业务员 | SC |
| SC02 | 宁缺 | 服务员 | SC |
| YF01 | 君陌 | 技术员 | RS |
+------+-------+--------+------+
7 rows in set (0.00 sec)老员工变更
call move('SC01','变更','YF','ZQQ')mysql> call move('SC01','变更','YF','ZQQ');
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
mysql> select * from employee;
+------+-------+--------+------+
| eno | ename | epost | dno |
+------+-------+--------+------+
| CW01 | 方平 | 会计 | CW |
| RS01 | 张峰 | 主任 | CW |
| RS02 | NULL | NULL | CW |
| RS03 | NULL | NULL | CW |
| SC01 | 楚阳 | 业务员 | YF |
| SC02 | 宁缺 | 服务员 | SC |
| YF01 | 君陌 | 技术员 | RS |
+------+-------+--------+------+
7 rows in set (0.00 sec)
mysql> select * from message;
+----+------+----------+--------+--------+---------------------+--------+
| id | eno | reason | olddno | newdno | date | who |
+----+------+----------+--------+--------+---------------------+--------+
| 5 | RS01 | 换部门 | 人事部 | 技术部 | 2020-04-11 16:09:31 | 董事长 |
| 27 | RS02 | 新人入部 | null | CW | 2020-04-11 18:56:18 | 经理 |
| 28 | RS01 | 更换部门 | RS | CW | 2020-04-11 18:56:24 | 董事长 |
| 29 | RS03 | 新人入部 | null | CW | 2020-04-11 18:57:37 | 经理 |
| 30 | RS01 | 更换部门 | CW | YF | 2020-04-11 18:57:45 | 董事长 |
| 31 | YF01 | 更换部门 | YF | RS | 2020-04-11 18:59:00 | 董事长 |
| 32 | RS01 | 变更 | CW | CW | 2020-05-06 11:53:55 | TTY |
| 33 | SC01 | 变更 | YF | YF | 2020-05-06 11:59:05 | ZQQ |
+----+------+----------+--------+--------+---------------------+--------+
8 rows in set (0.00 sec)我们可以看出,旧员工更换部门的操作也是成功的
加入新员工
call move('YF02','新人入部','YF','TTY')mysql> call move('YF02','新人入部','YF','TTY');
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> select * from employee;
+------+-------+--------+------+
| eno | ename | epost | dno |
+------+-------+--------+------+
| CW01 | 方平 | 会计 | CW |
| RS01 | 张峰 | 主任 | CW |
| RS02 | NULL | NULL | CW |
| RS03 | NULL | NULL | CW |
| SC01 | 楚阳 | 业务员 | YF |
| SC02 | 宁缺 | 服务员 | SC |
| YF01 | 君陌 | 技术员 | RS |
| YF02 | NULL | NULL | YF |
+------+-------+--------+------+
8 rows in set (0.00 sec)
mysql> select * from message;
+----+------+----------+--------+--------+---------------------+--------+
| id | eno | reason | olddno | newdno | date | who |
+----+------+----------+--------+--------+---------------------+--------+
| 5 | RS01 | 换部门 | 人事部 | 技术部 | 2020-04-11 16:09:31 | 董事长 |
| 27 | RS02 | 新人入部 | null | CW | 2020-04-11 18:56:18 | 经理 |
| 28 | RS01 | 更换部门 | RS | CW | 2020-04-11 18:56:24 | 董事长 |
| 29 | RS03 | 新人入部 | null | CW | 2020-04-11 18:57:37 | 经理 |
| 30 | RS01 | 更换部门 | CW | YF | 2020-04-11 18:57:45 | 董事长 |
| 31 | YF01 | 更换部门 | YF | RS | 2020-04-11 18:59:00 | 董事长 |
| 32 | RS01 | 变更 | CW | CW | 2020-05-06 11:53:55 | TTY |
| 33 | SC01 | 变更 | YF | YF | 2020-05-06 11:59:05 | ZQQ |
| 34 | YF02 | 新人入部 | | YF | 2020-05-06 12:01:02 | TTY |
+----+------+----------+--------+--------+---------------------+--------+
9 rows in set (0.00 sec)上图可以看到我们将新人加入了部门,这里因为不知道新人的名字和职位,如果按之前的插入会出现问题,所以在这一步的时候我修改了表的结构,让ename和epost可以为空值
函数function3
3.员工参加工程的上班和下班 存储过程/函数(注意时间点 早上6：之前和晚上12点后 不允许操作，当天没有上班的不允许下班的操作…)
这里新建一个表 来记录员工每天的签到情况
status表 (id(自增长),员工号,日期 , 是否签到,是否签退)
Y表示已签到/签退
N表示未签到/签退
这里我认为题目没有说清楚具体的签到时间和签退时间区间,仅仅说了24.00-6.00不允许操作,所以我加上了flag参数来表示员工的举动,type= 1代表签到,type= 0代表签退
代码
drop procedure if exists workUpDown;
delimiter //
create procedure workUpDown(
in ineno varchar(20),
in type int
)
begin
declare flag int;
declare date varchar(45);
declare judge varchar(5);
declare result varchar(10);
SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d %T') into date;
if type=1 then
select if (date_format(now(),'%T')>="06:00:00",1,0) into flag;
if flag=1 then
INSERT INTO `salary`.`status` (`eno`, `date`, `ifup`) VALUES (ineno, date, 'Y');
set result='签到成功!';
else
set result='签到时间错误!';
end if;
select result;
else
select if (date_format(now(),'%T')>"00:00:00" and date_format(now(),'%T')<"06:00:00",1,0) into flag;
if flag=0 then
SELECT ifup into judge FROM salary.status where eno =ineno;
if judge='Y' then
UPDATE `salary`.`status` SET `ifdown` = 'Y' WHERE (`eno` = ineno);
set result='签退成功'
else
set result='抱歉，您尚未签到';
end if;
else
set result='签退时间错误!';
end if;
select result;
end if;
end //
delimiter ;测试结果
签到
mysql> select * from status;
+----------+------+------------+------+--------+
| idstatus | eno | date | ifup | ifdown |
+----------+------+------------+------+--------+
| 9 | CW01 | 2020-04-11 | Y | NULL |
| 10 | SC02 | 2020-04-11 | Y | Y |
| 11 | SC01 | 2020-04-11 | Y | Y |
+----------+------+------------+------+--------+
3 rows in set (0.00 sec)
mysql> call workUpDown('YF01',1);
+-----------+
| result |
+-----------+
| 签到成功! |
+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select * from status;
+----------+------+---------------------+------+--------+
| idstatus | eno | date | ifup | ifdown |
+----------+------+---------------------+------+--------+
| 9 | CW01 | 2020-04-11 | Y | NULL |
| 10 | SC02 | 2020-04-11 | Y | Y |
| 11 | SC01 | 2020-04-11 | Y | Y |
| 12 | YF01 | 2020-05-06 14:08:19 | Y | NULL |
+----------+------+---------------------+------+--------+
4 rows in set (0.00 sec)可以看出现在 ifdown字段是空值,下面进行签退操作
签退
mysql> call workUpDown('YF01',0)
-> ;
+--------+
| result |
+--------+
| 签退成功 |
+--------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select * from status;
+----------+------+---------------------+------+--------+
| idstatus | eno | date | ifup | ifdown |
+----------+------+---------------------+------+--------+
| 9 | CW01 | 2020-04-11 | Y | NULL |
| 10 | SC02 | 2020-04-11 | Y | Y |
| 11 | SC01 | 2020-04-11 | Y | Y |
| 12 | YF01 | 2020-05-06 14:08:19 | Y | Y |
+----------+------+---------------------+------+--------+
4 rows in set (0.00 sec)上图可以看到 ifdown字段加上了Y
非法签退
mysql> call workUpDown('YF02',0)
-> ;
+------------------+
| result |
+------------------+
| 抱歉，您尚未签到 |
+------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)上图看出,如果没有签到的用户去签退,将会给出提示
4)实验体会
本次实验让我巩固了mysql的操作,在上学期的学习中,我已经熟练的掌握了命令行对mysql的各种操作,这次实验是可以说是一次很好的复习,从需求分析开始到建表,再到最后的编写函数,花费了我好几个小时的时间,但是成功的结果让我收获满满
除去上学期所学的命令行的一些操作,在这次实验中,我结合了python操纵mysql,并且学习了mysql图形界面工具workbench的使用,它们在一定程度上使得我对mysql的操作更加方便
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
