mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计

转载

level 2024-08-21 18:09:41

文章标签 数据库 sql 存储过程 mysql 文章分类 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图

mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计_数据库

关系模型

1)ER图转为关系表

注:加粗的属性为主键

工程（工程号，工程名,工程所在地,工程开始时间,工程结束时间）

职工（职工号，职工姓名，职位，小时工资率）

酬金（工程号，职工号，工时）

部门（部门编号，部门名称）

2)修改表结构

目标:达到3NF范式

显然工程表,酬金表,部门表满足 3NF

而职工表中存在 传递函数依赖 职工号 -> 职位, 职位 -> 小时工资率 因此我们需要将其拆分达到满足3NF的要求

所以职工表可以拆成2个表

职工（职工号，职工姓名，职位）

职能 ( 职位，小时工资率)

3)最终关系表

注(加粗代表主键,下划线代表外键)

工程表（工程号，工程名,工程所在地,工程开始时间,工程结束时间）

职工表（职工号，职工姓名，职位, 部门编号）

职能表 ( 职位，小时工资率)

酬金表（工程号，职工号，工时）

部门表（部门编号，部门名称）

2)数据插入

建表插入数据的代码见附件中的sql文件

已插入的数据如下

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)

函数function1

1.设计一个存储过程(函数)，以工程编号作为输入参数，实现计算某工程所支付的工人酬金。没有该工程返回-1

代码

def func1(pno):
    try:
        conn = mysql.connector.connect(**config)
        print('connect successful!')
        print('=='*50)
        # 使用cursor()方法获取操作游标 
        cursor = conn.cursor()
        # 记录开始时间
        start=time.time()
        # 执行SQL语句 
        cursor.execute(f"SELECT employee.ename ename,hourrate,worktime FROM job,employee,reward where reward.pno='{pno}'and reward.eno=employee.eno and employee.epost=job.epost;")
        # 获取所有记录列表
        results = cursor.fetchall()
        results=np.array(results)
        if len(results) == 0:
            print('工程号不存在')
        else:
            s = 0
            for li in results:
                name = li[0]
                money = int(li[1])*int(li[2])
                s += money
                print(f'应该支付{name}的薪金是:{money}元')
            print(f'编号为{pno}的项目共支付工人薪金:{s}元')
    except:
        print("Error: unable to fecth data")
    finally:
        try:
            # 关闭数据库连接
            print('=='*50)
            print('try to close connect...')
            conn.close()
            print('connect closed!')
            # 打印程序运行时间
            print(f'Run time: {time.time()-start:.6f}s')
        except:
            pass

测试结果

mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计_sql_02

函数function2

2.实现员工变更部门的存储过程，成功返回 1，否则返回0；变更过程计入档案；职工号/变更类型/原先部分(新增null)/新的部门(离开null)/日期/经手人

这里的档案我们选择新建一个change表

change (职工号,变更类型,原先部分(新增null),新的部门(离开null),日期,经手人)

这道题有2个要求,一个是对职工表的修改,一个是记录过程进入档案

代码

def func2(eno,change_reason,new_dno,who):
    try:
        conn = mysql.connector.connect(**config)
        print('connect successful!')
        print('=='*50)
        # 使用cursor()方法获取操作游标 
        cursor = conn.cursor()
        # 记录开始时间
        start=time.time()
        # 执行SQL语句 
        cursor.execute(f"SELECT dno FROM employee as e where eno = '{eno}';")
        # 获取所有记录列表
        results = cursor.fetchall()
        if len(results) == 0:
            results = 'null' # 新人加入,插入emp表
            sql = f"INSERT INTO `salary`.`employee` (`eno`, `dno`) VALUES ('{eno}', '{new_dno}');"
            cursor.execute(sql)
            conn.commit()
            print('新人加入成功')
        else:
            results = results[0][0] # 获取旧的 dno
            print(results)
            sql = f"UPDATE `salary`.`employee` SET `dno` = '{new_dno}' WHERE (`eno` = '{eno}');"
            cursor.execute(sql)
            conn.commit()
            print('修改部门成功')
        date = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
        sql = f"INSERT INTO `salary`.`message` (`eno`, `reason`, `olddno`, `newdno`, `date`, `who`) VALUES ('{eno}', '{change_reason}', '{results}', '{new_dno}', '{date}', '{who}');"
        cursor.execute(sql)
        conn.commit()
        print('登记完成')
    except:
        print("Error: unable to fecth data")
    finally:
        try:
            # 关闭数据库连接
            print('=='*50)
            print('try to close connect...')
            conn.close()
            print('connect closed!')
            # 打印程序运行时间
            print(f'Run time: {time.time()-start:.6f}s')
        except:
            pass

测试结果

这是现在的employee表,而message仍是个空表,我们看看接下来他发生的变化

eno	ename	epost	dno
CW01	方平	会计	CW
RS01	张峰	主任	RS
SC01	楚阳	业务员	SC
SC02	宁缺	服务员	SC
YF01	君陌	技术员	YF

mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计_数据库_03

上图可以看到我们将新人加入了部门,这里因为不知道新人的名字和职位,如果按之前的插入会出现问题,所以在这一步的时候我修改了表的结构,让ename和epost可以为空值

mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计_mysql_04

我们可以看出,旧员工更换部门的操作也是成功的

函数function3

3.员工参加工程的上班和下班存储过程/函数(注意时间点早上6：之前和晚上12点后不允许操作，当天没有上班的不允许下班的操作…)

这里新建一个表来记录员工每天的签到情况

status (id(自增长),员工号,日期 , 是否签到,是否签退)

Y表示已签到/签退

N表示未签到/签退

这里我认为题目没有说清楚具体的签到时间和签退时间区间,仅仅说了24.00-6.00不允许操作,所以我加上了flag参数来表示员工的举动,flag = 0代表签到,flag= 1代表签退,默认的值为0

代码

def func3(eno,flag=0):
    try:
        conn = mysql.connector.connect(**config)
        print('connect successful!')
        print('=='*50)
        # 使用cursor()方法获取操作游标 
        cursor = conn.cursor()
        # 记录开始时间
        start=time.time()
        day = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))[:10]# 获取年月日
        hour = datetime.datetime.now().hour # 获取小时
        minute = datetime.datetime.now().minute # 分
        second = datetime.datetime.now().second # 秒
        print(day,hour,minute,second)
        if 6 <= hour <= 24: # 早上6点到晚上24.00之间
            if (minute > 0 or second > 0 ) and hour == 24:  #超过24.00
                print('您已经超时,不允许操作')
            else:
                if flag == 0: # 签到,插入数据
                    sql = f"INSERT INTO `salary`.`status` (`eno`, `date`, `ifup`) VALUES ('{eno}', '{day}', 'Y');"
                    cursor.execute(sql)
                    conn.commit()
                    print(f'{eno}已经成功签到!')
                else: # 签退
                    sql = f"SELECT ifup FROM salary.status where eno ='{eno}';"
                    cursor.execute(sql)
                    results = cursor.fetchall()
                    if len(results) == 0:
                        print('抱歉，您尚未签到')
                    else:
                        sql = f"UPDATE `salary`.`status` SET `ifdown` = 'Y' WHERE (`eno` = '{eno}');"
                        cursor.execute(sql)
                        conn.commit()
                        print(f'{eno}已经签退成功!')
    except:
        print("Error: unable to fecth data")
    finally:
        try:
            # 关闭数据库连接
            print('=='*50)
            print('try to close connect...')
            conn.close()
            print('connect closed!')
            # 打印程序运行时间
            print(f'Run time: {time.time()-start:.6f}s')
        except:
            pass

测试结果

mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计_数据库_05

可以看出现在 ifdown字段是空值,下面进行签退操作

mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计_数据库_06

上图可以看到 ifdown字段加上了Y

mysql数据库项目式教程双色版答案项目四课后答案数据库项目课程设计_存储过程_07

上图看出,如果没有签到的用户去签退,将会给出提示

3)最终结果综合

def main():
    while True:
        a = input('请输入序号进行操作:\n1.查看某工程支付的工人佣金\n2.员工变更部门\n3.员工签到/退\n4.查看更多信息\n5.退出操作\n')  
        if a == '1':
            pno = input('请输入想要查询的工程号:')
            func1(pno)
        elif a == '2':
            eno,change_reason,new_dno,who=input('请按顺序输入以下信息(以逗号间隔)\n职工号,变更类型,新的部门,经手人:').split(',')
            func2(eno,change_reason,new_dno,who)
        elif a == '3':
            eno = input('请输入你的职工号:')
            flag = int(input('如想签到请输0,签退请输1:'))
            func3(eno,flag=flag)
        elif a == '4':
            print('请输入sql语句来进行你想查看的操作:')
            sql = input()
            executing(sql)
        else:
            print('感谢使用')
            break

请输入序号进行操作:
1.查看某工程支付的工人佣金
2.员工变更部门
3.员工签到/退
4.查看更多信息
5.退出操作
1
请输入想要查询的工程号:004
connect successful!
====================================================================================================
应该支付方平的薪金是:1200元
应该支付张峰的薪金是:4000元
应该支付楚阳的薪金是:750元
编号为004的项目共支付工人薪金:5950元
====================================================================================================
try to close connect...
connect closed!
Run time: 0.000995s
请输入序号进行操作:
1.查看某工程支付的工人佣金
2.员工变更部门
3.员工签到/退
4.查看更多信息
5.退出操作
2
请按顺序输入以下信息(以逗号间隔)
职工号,变更类型,新的部门,经手人:YF01,更换部门,RS,董事长
connect successful!
====================================================================================================
修改部门成功,YF01员工已经从YF部门加入RS部门!
登记完成
====================================================================================================
try to close connect...
connect closed!
Run time: 0.008980s
请输入序号进行操作:
1.查看某工程支付的工人佣金
2.员工变更部门
3.员工签到/退
4.查看更多信息
5.退出操作
3
请输入你的职工号:SC01
如想签到请输0,签退请输1:0
connect successful!
====================================================================================================
2020-04-11 18 59 14
SC01已经成功签到!
====================================================================================================
try to close connect...
connect closed!
Run time: 0.002995s
请输入序号进行操作:
1.查看某工程支付的工人佣金
2.员工变更部门
3.员工签到/退
4.查看更多信息
5.退出操作
3
请输入你的职工号:SC01
如想签到请输0,签退请输1:1
connect successful!
====================================================================================================
2020-04-11 18 59 21
SC01已经签退成功!
====================================================================================================
try to close connect...
connect closed!
Run time: 0.022945s
请输入序号进行操作:
1.查看某工程支付的工人佣金
2.员工变更部门
3.员工签到/退
4.查看更多信息
5.退出操作
4
请输入sql语句来进行你想查看的操作:
SELECT * FROM salary.employee;
connect successful!
begin to execute
[['CW01' '方平' '会计' 'CW']
 ['RS01' '张峰' '主任' 'YF']
 ['RS02' None None 'CW']
 ['RS03' None None 'CW']
 ['SC01' '楚阳' '业务员' 'SC']
 ['SC02' '宁缺' '服务员' 'SC']
 ['YF01' '君陌' '技术员' 'RS']]
try to close connect...
connect closed!
Run time: 0.000997s
请输入序号进行操作:
1.查看某工程支付的工人佣金
2.员工变更部门
3.员工签到/退
4.查看更多信息
5.退出操作
5
感谢使用

4)实验体会

本次实验让我巩固了mysql的操作,在上学期的学习中,我已经熟练的掌握了命令行对mysql的各种操作,这次实验是可以说是一次很好的复习,从需求分析开始到建表,再到最后的编写函数,花费了我好几个小时的时间,但是成功的结果让我收获满满

除去上学期所学的命令行的一些操作,在这次实验中,我结合了python操纵mysql,并且学习了mysql图形界面工具workbench的使用,它们在一定程度上使得我对mysql的操作更加方便

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java后端返回前端Json格式数据后端返回json数据给前端

下一篇：Android app定时重启功能实现 android 定时启动

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯