职工-工程数据库课程设计

以下GUI界面均为Navicat

1) E-R总体图&&关系模式

打?为主码
部门 department（部门号did?，部门名dname）
职位 job（职位号jid?，职位名jname，小时工资率srate）
职工 staff（职工号sid?，职工名 sname，部门号did,职位 jid）
工程 project（工程号pid?，工程名pname，工程所在地pl，开始日期pst，结束日期pet）
打卡 clockio（打卡类型punchTheClock（0上班1下班），时间timestamp?，职工号sid?，工程号pid?）
职员变更（记录号logid?，日期ltime，职工号sid，变更类型ctype，原先部门pd，现在部门cd，经手人handlerid）

2)DDL语句 建表/约束、存储过程或函数

建表、约束：

• CREATE TABLE `clockio` (
`punchTheClock` tinyint(4) NOT NULL COMMENT ‘打卡类型（0上班1下班）‘,
`time_stamp` timestamp NOT NULL COMMENT ‘时间‘,
`sid` int(11) NOT NULL COMMENT ‘职工号‘,
`pid` int(11) NOT NULL COMMENT ‘工程号‘,
PRIMARY KEY (`time_stamp`,`sid`,`pid`) USING BTREE,
KEY `sid` (`sid`),
KEY `pid` (`pid`),
CONSTRAINT `pid` FOREIGN KEY (`pid`) REFERENCES `project` (`pid`),
CONSTRAINT `sid` FOREIGN KEY (`sid`) REFERENCES `staff` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  




• CREATE TABLE `department` (
`did` int(255) NOT NULL COMMENT ‘部门号‘,
`dname` varchar(255) DEFAULT NULL COMMENT ‘部门名‘,
KEY `did` (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  




• CREATE TABLE `job` (
`jid` int(11) NOT NULL COMMENT ‘职位号‘,
`jname` varchar(255) DEFAULT NULL COMMENT ‘职位名‘,
`srate` float(255,0) DEFAULT NULL COMMENT ‘小时工资率‘,
PRIMARY KEY (`jid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  




• CREATE TABLE `jobchanged` (
`logid` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘记录号‘,
`ltime` timestamp NOT NULL COMMENT ‘记录发生时间‘,
`sid` int(11) NOT NULL COMMENT ‘职工号‘,
`ctype` varchar(255) NOT NULL COMMENT ‘变更类型‘,
`pd` int(11) DEFAULT NULL COMMENT ‘原先部门号‘,
`cd` int(11) DEFAULT NULL COMMENT ‘现在部门号‘,
`handlerid` int(11) DEFAULT NULL COMMENT ‘经手人编号‘,
PRIMARY KEY (`logid`),
KEY `sid1` (`sid`),
KEY `pd` (`pd`),
KEY `cd` (`cd`),
KEY `handlerid` (`handlerid`),
CONSTRAINT `cd` FOREIGN KEY (`cd`) REFERENCES `department` (`did`),
CONSTRAINT `handlerid` FOREIGN KEY (`handlerid`) REFERENCES `staff` (`sid`),
CONSTRAINT `pd` FOREIGN KEY (`pd`) REFERENCES `department` (`did`),
CONSTRAINT `sid1` FOREIGN KEY (`sid`) REFERENCES `staff` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  




• CREATE TABLE `project` (
`pid` int(11) NOT NULL COMMENT ‘工程号‘,
`pname` varchar(255) DEFAULT NULL COMMENT ‘工程名‘,
`pl` varchar(255) DEFAULT NULL COMMENT ‘工程所在地‘,
`pst` date DEFAULT NULL COMMENT ‘工程开始时间‘,
`pet` date DEFAULT NULL COMMENT ‘工程结束时间‘,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  




• CREATE TABLE `staff` (
`sid` int(11) NOT NULL COMMENT ‘职工号\r\n‘,
`sname` varchar(255) DEFAULT NULL COMMENT ‘职工名‘,
`did` int(11) DEFAULT NULL COMMENT ‘部门号‘,
`jid` int(11) DEFAULT NULL COMMENT ‘职位号‘,
PRIMARY KEY (`sid`),
KEY `did` (`did`),
KEY `jid` (`jid`),
CONSTRAINT `did` FOREIGN KEY (`did`) REFERENCES `department` (`did`),
CONSTRAINT `jid` FOREIGN KEY (`jid`) REFERENCES `job` (`jid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  

存储过程/函数：

计算工程需要支付的工资：

CREATE DEFINER=`root`@`%` PROCEDURE `calculate_salary`(IN num INT,OUT result INT)
BEGIN
DECLARE exist INT;
DECLARE start_time TIMESTAMP;
DECLARE end_time TIMESTAMP;
SELECT count(*) INTO exist FROM project WHERE pid = num;
IF exist = 0 THEN
SET result = -1;
ELSE
SELECT DATE_FORMAT(pst,‘%Y-%m-%d‘) INTO start_time FROM project WHERE pid = num;
SELECT DATE_FORMAT(pet,‘%Y-%m-%d‘) INTO end_time FROM project WHERE pid = num;
SELECT SUM(TIMESTAMPDIFF(HOUR,stime,etime)*srate) INTO result
FROM
(SELECT a.sid,a.day1,stime,etime FROM
((SELECT sid,DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) AS day1,min(DATE_FORMAT(time_stamp,‘%Y-%m-%d %H:%i:%s‘)) stime
FROM clockio
WHERE punchTheClock = 0 AND pid = num AND
DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) between start_time and end_time group by sid,day1) a,
(SELECT sid,DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) AS day1,max(DATE_FORMAT(time_stamp,‘%Y-%m-%d %H:%i:%s‘)) etime
FROM clockio
WHERE punchTheClock = 1 AND pid = num AND
DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) between start_time and end_time group by sid,day1) b)
WHERE a.sid = b.sid AND a.day1 = b.day1) AS job_time,staff,job
WHERE staff.sid = job_time.sid AND staff.jid = job.jid;
END IF;
END


职工职位变更：

CREATE DEFINER=`root`@`%` PROCEDURE `change_job`(IN `staff_id` int,IN `staff_name` varchar(255),IN `staff_job` int,IN `change_type` varchar(255),IN `new_department` int,IN `dealer` int,OUT `result` int)
BEGIN
declare old_department int;
IF change_type = ‘enter‘ THEN
INSERT staff VALUES(staff_id,staff_name,new_department,staff_job);
INSERT jobchanged(ltime,sid,ctype,pd,cd,handlerid) values(NOW(),staff_id,change_type,null,new_department, dealer);
SET result=1;
ELSEIF change_type = ‘leave‘ THEN
SELECT did INTO old_department FROM staff WHERE sid=staff_id;
INSERT jobchanged(ltime,sid,ctype,pd,cd,handlerid) values(NOW(),staff_id,change_type,old_department,null, dealer);
DELETE from staff WHERE sid = staff_id;
SET result=1;
ELSE
SELECT did INTO old_department FROM staff WHERE sid=staff_id;
IF old_department = new_department THEN
SET result=0;
ELSE
UPDATE staff SET did = new_department WHERE sid=staff_id;
INSERT jobchanged(ltime,sid,ctype,pd,cd,handlerid) values(NOW(),staff_id,change_type,old_department, new_department,dealer);
SET result=1;
END IF;
END IF;
END


打卡：

CREATE DEFINER=`root`@`%` PROCEDURE `punch_in`(IN ptype tinyint,IN snum int,IN pnum int,OUT result INT)
BEGIN
DECLARE now_time TIMESTAMP;
DECLARE cnt INT;
DECLARE judge INT;
SELECT current_timestamp INTO now_time;
SELECT count(*) INTO cnt FROM clockio
WHERE sid = snum AND punchTheClock = 0
AND DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) = DATE_FORMAT(now_time,‘%Y-%m-%d‘);
SELECT count(*) INTO judge FROM clockio
WHERE sid = snum AND punchTheClock = ptype
AND DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) = DATE_FORMAT(now_time,‘%Y-%m-%d‘);
IF DATE_FORMAT(CURRENT_TIMESTAMP,‘%H‘) NOT BETWEEN 0 AND 24 THEN
SET result = 0;
ELSEIF judge >= 1 THEN
SET result = 0;
ELSEIF cnt = 0 and ptype = 1 THEN
SET result = 0;
ELSE
INSERT INTO clockio VALUES(ptype,now_time,snum,pnum);
SET result = 1;
END IF;
END


3)数据初始化

department表：

job表：

project表：

staff表：

clockio表：

jobchanged：

4)测试存储过程/函数

测试工程2所需支付的所有工人酬金：

同理查询工程1：

根据目前已有的打卡记录可以验证存储过程函数运算正确：

测试员工职位变更：

1. 

   
   



2. 

   
   



3. 

   
   



4. 

   进行入职操作：

   

   

   

   员工离职操作：

   

   

   

   

   
   

测试员工打卡：

当前时间为凌晨 不在允许打卡的范围内 所以返回零

对其中判断部分进行修改后重新运行 就成功了 多了一条新的数据

在本日没有上班打卡的前提下进行下班打卡操作 也返回零 规避了打卡记录错乱的风险