factory数据库考核资料.docx
《factory数据库考核资料.docx》由会员分享,可在线阅读,更多相关《factory数据库考核资料.docx(32页珍藏版)》请在冰豆网上搜索。
![factory数据库考核资料.docx](https://file1.bdocx.com/fileroot1/2023-2/3/815fb2db-f42e-487a-b78f-ca2ed42efe31/815fb2db-f42e-487a-b78f-ca2ed42efe311.gif)
factory数据库考核资料
《DatabaseprogrammingwithRDBMS》课程设计
职工工资管理统
指导老师:
郭小芳
学号:
1441904103
姓名:
高嘉威
《DatabaseprogrammingwithRDBMS》考查命题
1、根据周围的实际情况,自选一个小型的数据库应用项目,并深入到应用项目中调研,进行分析和设计。
例如可选择职工工资管理系统、人事管理系统、教材管理系统和小型超市商品管理系统和图书管理系统等。
要求写出数据库设计报告。
一、功能需求
系统的基本流程是:
用户登入—>主界面—>选择各项子系统。
模块设计和功能说明
(一)权限说明:
对于不同的用户可以对其赋予不同的权限级别。
部门主管拥有最高的权限,能够对员工的资料进行增删,对部门的信息进行增删。
1)用户登入:
填入用户名称,填写正确的密码,选择用户类型。
2)用户登入后,可以对自己的密码进行修改。
3)用户可以查看个人资料。
以下是各部门主管所特有的权限,其他员工均没有该权限,不能实现一下的操作。
4)部门主管可以对员工信息进行浏览、查询、编辑、插入、删除。
5)部门主管可以对该公司的部门信息进行浏览、查询、编辑、插入、删除。
(二)系统的要求:
职工工资管理系统开发时,应该满足以下几点
1)方便,容易操作,快捷的原则。
2)对于不同的用户,应该有不同的使用权限。
3)各主管的任务是管理部门、职工、工资信息,即时更新、登记、发布信
息,普通职工可以查询、核对自己的基本信息和工资信息。
整个系统模块划分如下图:
系统管理:
1)用户登录:
普通用户登录:
只可查询信息,不可插入、修改、删除;
主管登录:
可插入、修改、查询、删除信息;
2)修改密码:
凡是该系统的用户都可以修改自己的密码;
3)退出系统
信息管理:
1)部门信息管理:
主管可以插入,删除,更改部门信息;
2)员工信息管理:
主管可以插入,删除,更改员工的基本信息和薪资信息;
信息查询:
普通员工可以查询自己的基本信息和薪资信息;
2、E-R图
3、关系模型:
1.部门信息(部门编号,部门名称)
2.员工信息(员工编号,姓名,性别,学历,出生日期,职务,部门编号,密码)
3.薪资信息(账号,员工编号,基本工资,福利,缺勤时间及原因,惩罚金额,应缴税费,总薪资)
2、如创建职工工资管理系统中,一个名称为factory的数据库,要求:
(1)将主数据库文件factory.mdf放置在H:
\DBF文件夹中,其文件大小自动增长为按5MB增长。
(2)将事务日志文件factory_log.ldf放置在H:
\DBF文件夹中,其文件大小自动增长为按1MB增长。
createdatabasefactory
on
(
name=factory,
FILENAME='H:
\DBF\factory.mdf',
size=3mb,
filegrowth=5mb
)
logon
(
name=factory_log,
FILENAME='H:
\DBF\factory_log.ldf',
size=1mb,
filegrowth=1mb
)
3、在上机面所创建的数据库factory中,完成如下各题操作。
(1)用create语句建立职工表worker,其结构为:
职工号,int;姓名,char(8);性别,char
(2);出生日期,datetime;党员否char
(2);参加工作,datetime;部门号,int。
其中“职工号”为主键。
并用insert语句在worker表中输入如下记录:
职工号
姓名
性别
出生日期
党员否
参加工作
部门号
1
孙华
男
01/03/52
是
10/10/70
101
2
陈明
男
05/08/45
否
01/01/65
102
3
程西
女
06/10/80
否
07/10/02
101
4
孙天奇
女
03/10/65
是
07/10/87
102
5
刘夫文
男
01/11/42
否
08/10/60
102
6
刘欣
男
10/08/52
否
01/07/70
101
7
余慧
男
12/04/80
否
07/10/02
103
8
张旗
男
11/10/80
否
07/10/02
102
usefactory
go
createtableworker
(职工号intprimarykey,
姓名char(8),
性别char
(2),
出生日期datetime,
党员否char
(2),
参加工作datetime,
部门号int,
);
insertintoworker
values(1,'孙华','男',01/03/52,'是',10/10/70,101),
(2,'陈明','男',05/08/45,'否',01/01/65,102),
(3,'程西','女',06/10/80,'否',07/10/02,101),
(4,'孙天奇','女',03/10/65,'是',07/10/87,102),
(5,'刘夫文','男',01/11/42,'否',08/10/60,102),
(6,'刘欣','男',10/08/52,'否',01/07/70,101),
(7,'余慧','男',12/04/80,'否',07/10/02,103),
(8,'张旗','男',11/10/80,'否',07/10/02,102)
(2)用create语句建立部门表depart,其结构为:
部门号,int;部门名,char(10)。
其中,“部门号”为主键。
并用insert语句在depart表中输入如下记录:
部门号
部门名
101
财务处
102
人事处
103
市场部
usefactory
go
createtabledepart
(部门号intprimarykey,
部门名char(10)
);
insertintodepart
values(101,'财务处'),
(102,'人事处'),
(103,'市场部')
(3)用create建立职工工资表salary,其结构为:
职工号,int;姓名,char(8);日期,datetime;工资,decimal(6,1)。
其中,“职工号”和“日期”为主键。
并用insert语句在salary表中输入如下记录:
职工号
姓名
日期
工资
1
孙华
01/04/04
1201.5
2
陈明
01/04/04
1350.6
3
程西
01/04/04
750.8
4
孙天奇
01/04/04
900.0
5
刘夫文
01/04/04
2006.8
6
刘欣
01/04/04
1250.0
7
余慧
01/04/04
725.0
8
张旗
01/04/04
728.0
usefactory
go
createtablesalary
(职工号intprimarykey,
姓名char(8),
日期datetime,
工资decimal(6,1)
);
insertintosalary
values(1,'孙华',01/04/04,1201.5),
(2,'陈明',01/04/04,1350.6),
(3,'程西',01/04/04,750.8),
(4,'孙天奇',01/04/04,900.0),
(5,'刘夫文',01/04/04,2006.8),
(6,'刘欣',01/04/04,1250.0),
(7,'余慧',01/04/04,725.0),
(8,'张旗',01/04/04,728.0)
(4)建立worker、depart和salary3个表之间的关系。
4、在建立的factory数据库上,完成如下各题的程序,要求以文本格式显示结果。
(1)显示所有职工的年龄,并按职工号递增排序。
USEfactory
GO
SELECT职工号,YEAR(参加工作)-YEAR(出生日期)AS年龄FROMworker
ORDERBY职工号ASC
GO
(2)求出各部门的党员人数。
USEfactory
GO
SELECT部门号,COUNT(党员否)AS党员人数FROMworker
GROUPBY部门号
GO
(3)显示所有职工的姓名和2004年1月份的工资。
USEfactory
GO
SELECTworker.姓名,工资FROMworker,salary
WHEREworker.职工号=salary.职工号
GO
(4)显示所有职工的职工号、姓名和平均工资。
USEfactory
GO
SELECTworker.职工号,worker.姓名,AVG(工资)AS平均工资FROMworker,salary
GROUPBYworker.职工号,worker.姓名
GO
(5)显示所有职工的职工号、姓名、部门名和2004年2月份的工资,并按部门名的顺序排列。
USEfactory
GO
SELECTworker.职工号,worker.姓名,部门名,工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
ORDERBY部门名
GO
(6)显示各部门名和该部门的所有职工平均工资。
USEfactory
GO
SELECT部门名,AVG(工资)AS平均工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GROUPBY部门名
GO
(7)显示所有平均工资高于1200的部门名和对应的平均工资。
USEfactory
GO
SELECT部门名,AVG(工资)AS平均工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GROUPBY部门名
HAVINGAVG(工资)>1200
GO
(8)显示所有职工的职工号、姓名和部门类型,其中财务部和人事部属于管理部门,市场部属于市场部门。
USEfactory
GO
SELECT职工号,姓名,部门类型=
CASE
WHEN部门名='财务处'THEN'管理部门'
WHEN部门名='人事处'THEN'管理部门'
WHEN部门名='市场部'THEN'市场部门'
END
FROMworker,depart
WHEREworker.部门号=depart.部门号
GO
(9)若存在职工号为10的职工,则显示其工作部门名称,否则显示相应提示信息。
USEfactory
GO
IFEXISTS
(SELECT*FROMworker
WHERE职工号=10)
BEGIN
SELECT部门名FROMworker,depart
WHEREworker.部门号=depart.部门号AND职工号=10
END
ELSE
PRINT'不存在职工号为‘’的员工。
'
GO
(10)求出男女职工的平均工资,若男职工平均工资高出女职工平均工资50%,则显示“男职工比女职工的工资高多了”的信息;若男职工平均工资与女职工平均工资比率在1.5~0.8之间,则显示“男职工跟女职工的工资差不多”的信息;否则显示“女职工比男职工的工资高多了”的信息。
USEfactory
GO
SELECT性别,AVG(工资)AS平均工资FROMworker,salary
WHEREworker.职工号=salary.职工号
GROUPBY性别
GO
DECLARE@mdecimal
DECLARE@fdecimal
SELECT@m=AVG(工资)FROMworker,salary
WHEREworker.职工号=salary.职工号AND性别='男'
SELECT@f=AVG(工资)FROMworker,salary
WHEREworker.职工号=salary.职工号AND性别='女'
IF((@m-@f)/@f>=0.5)
PRINT'男职工比女职工的工资高多了。
'
ELSEIF(@m/@f>=0.8AND@m/@f<=1.5)
PRINT'男职工跟女职工的工资差不多。
'
ELSE
PRINT'女职工比男职工的工资高多了。
'
GO
5、在上机实验题4建立的factory数据库上,完成如下各题(所有SELECT语句的查询结果以文本格式显示)。
(1)删除factory数据库上各个表之间建立的关系。
ALTERTABLEsalary
DROPCONSTRAINTFK_salary_worker
GO
ALTERTABLEworker
DROPCONSTRAINTFK_worker_depart
GO
(2)显示各职工的工资记录和相应的工资小计。
usefactory
go
select职工号,姓名,工资fromsalary
orderby职工号
computesum(工资)by职工号
go
(3)按性别和部门名的所有组合方式列出相应的平均工资。
USEfactory
GO
SELECT性别,部门名,AVG(工资)AS平均工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GROUPBY性别,部门名
GO
(4)在worker表中使用以下语句插入一个职工记录:
INSERTINTOworkerVALUES(20,'陈立','女','55/03/08',1,'75/10/10',4)
在depart表中使用以下语句插入一个部门记录:
INSERTINTOdepartVALUES(5,'设备处')
对worker和depart表进行全外连接显示职工的职工号、姓名和部门名,然后删除这两个插入的记录。
USEfactory
GO
INSERTINTOworkerVALUES('20','陈立','55/03/08',1,'75/10/10',4)
INSERTINTOdepartVALUES('5','设备处')
GO
SELECT职工号,姓名,部门名FROMworker
FULLOUTERJOINdepartONworker.部门号=depart.部门号
GO
DELETEFROMworker
WHERE职工号=20
GO
DELETEFROMdepart
WHERE部门号=5
GO
(5)显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。
USEfactory
GO
SELECTworker.职工号,worker.姓名,部门名,salary.日期,工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号AND工资=
(SELECTMAX(工资)FROMsalary)
GO
(6)显示最高工资的职工所在的部门名。
USEfactory
GO
SELECT部门名FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号AND工资=
(SELECTMAX(工资)FROMsalary)
GO
(7)显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。
USEfactory
GO
SELECTworker.职工号,worker.姓名FROMworker,salary
WHEREworker.职工号=salary.职工号AND工资<
(SELECTAVG(工资)FROMsalary)
GO
(8)采用游标方式实现(6)小题的功能。
USEfactory
GO
DECLARE@s_maxCHAR(10)
DECLAREst_cursorCURSOR
FORSELECT部门名FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号AND工资=
(SELECTMAX(工资)FROMsalary)
OPENst_cursor
FETCHNEXTFROMst_cursorINTO@s_max
PRINT'部门名'
WHILE@@FETCH_STATUS=0
BEGIN
PRINTCAST(@s_maxASchar(10))
FETCHNEXTFROMst_cursorINTO@s_max
END
CLOSEst_cursor
DEALLOCATEst_cursor
GO
(9)采用游标方式实现(7)小题的功能。
USEfactory
GO
DECLARE@c_noint,@s_namevarchar(10)
DECLAREst_cursorCURSOR
FORSELECTworker.职工号,worker.姓名FROMworker,salary
WHEREworker.职工号=salary.职工号AND工资<
(SELECTAVG(工资)FROMsalary)
OPENst_cursor
FETCHNEXTFROMst_cursorINTO@c_no,@s_name
PRINT'职工号'+''+'姓名'
WHILE@@FETCH_STATUS=0
BEGIN
PRINTCAST(@c_noASvarchar(10))+''+@s_name
FETCHNEXTFROMst_cursorINTO@c_no,@s_name
END
CLOSEst_cursor
DEALLOCATEst_cursor
GO
(10)先显示worker表中的职工人数,开始一个事务,插入一个职工记录,再显示worker表中的职工人数,回滚该事务,最后显示worker表中的职工人数。
USEfactory
GO
SELECTCOUNT(*)AS职工人数FROMworker
GO
BEGINTRANSACTION
INSERTINTOworker
VALUES(11,'小明','女','1955/03/08','1','1975/10/10','4')
SAVETRANSACTIONTRAN1
SELECTCOUNT(*)AS职工人数FROMworker
GO
ROLLBACKTRANSACTION
SELECTCOUNT(*)AS职工人数FROMworker
GO
6、在factory数据库上,使用T-SQL语句完成如下各题:
(1)在worker表中的“部门号”列上创建一个非聚集索引;若该索引已存在,则删除后重建。
CREATENONCLUSTEREDINDEXbmh_indexONworker(部门号)
GO
(2)在salary表的“职工号”和“日期”列创建聚集索引,并且强制唯一性。
CREATEUNIQUECLUSTEREDINDEXzgh_rq_indexONsalary(职工号,日期)
GO
7、在factory数据库上,使用T-SQL语句完成如下各题:
(1)建立视图view1,查询所有职工的职工号、姓名、部门名和2004年2月份工资,并按部门名顺序排列。
USEfactory
GO
CREATEVIEWview1
AS
SELECTworker.职工号,worker.姓名,部门名,工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GO
(2)建立视图view2,查询所有职工的职工号、姓名和平均工资。
USEfactory
GO
CREATEVIEWview2
AS
SELECTworker.职工号,worker.姓名,AVG(工资)AS平均工资FROMworker,salary
GROUPBYworker.职工号,worker.姓名
GO
(3)建立视图view3,查询各部门名和该部门的所有职工平均工资。
USEfactory
GO
CREATEVIEWview3
AS
SELECT部门名,AVG(工资)AS平均工资FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GROUPBY部门名
GO
(4)显示视图view3的定义。
USEfactory
GO
EXECsp_helpview3
GO
8、在factory数据库上,使用T-SQL语句完成如下各题:
(1)修改worker表的结构,添加“E_mailvarchar(20)”字段。
USEfactory
GO
ALTERTABLEworker
ADDE_mailvarchar(20)
GO
(2)将上例中worker表中“E_mail”字段的数据类型改为varchar(30)
USEfactory
GO
ALTERTABLEworker
ALTERCOLUMNE_mailvarchar(30)
GO
(3)删除worker表中的“E_mail”字段
USEfactory
GO
ALTERTABLEworker
DROPCOLUMNE_mail
GO
(4)将worker表中姓名为“陈明”的部门号改为“101”。
USEfactory
GO
UPDATEworker
SET部门号='101'
WHERE姓名='陈明'
GO
(5)删除工资表salary中姓名为“陈明”的记录。
USEfactory
GO
DELETEFROMworker
WHERE姓名='陈明'
GO
(6)实施worker表的“性别”列默认值为“男”的约束。
USEfactory
GO
ALTERTABLEworker
ADD