factory数据库考核资料.docx
《factory数据库考核资料.docx》由会员分享，可在线阅读，更多相关《factory数据库考核资料.docx（29页珍藏版）》请在冰豆网上搜索。
factory数据库考核资料
》DatabaseprogrammingwithRDBMS《课程设计
职工工资管理统
指导老师：
郭小芳
学号：
1441904103
姓名：
高嘉威
《DatabaseprogrammingwithRDBMS》考查命题
1、根据周围的实际情况，自选一个小型的数据库应用项目，并深入到应用项目中调研，进行分析和设计。
例如可选择职工工资管理系统、人事管理系统、教材管理系统和小型超市商品管理系统和图书管理系统等。
要求写出数据库设计报告。
一、功能需求
系统的基本流程是：
用户登入—>主界面—>选择各项子系统。
模块设计和功能说明
（一）权限说明：
对于不同的用户可以对其赋予不同的权限级别。
部门主管拥有最高的权限，能够对员工的资料进行增删，对部门的信息进行增删。
1）用户登入：
填入用户名称，填写正确的密码，选择用户类型。
2）用户登入后，可以对自己的密码进行修改。
3）用户可以查看个人资料。
以下是各部门主管所特有的权限，其他员工均没有该权限，不能实现一下的操作。
4）部门主管可以对员工信息进行浏览、查询、编辑、插入、删除。
5）部门主管可以对该公司的部门信息进行浏览、查询、编辑、插入、删除。
（二）系统的要求：
职工工资管理系统开发时，应该满足以下几点
1）方便，容易操作，快捷的原则。
2）对于不同的用户，应该有不同的使用权限。
3）各主管的任务是管理部门、职工、工资信息，即时更新、登记、发布信
息，普通职工可以查询、核对自己的基本信息和工资信息。
整个系统模块划分如下图：
用户登入
职工工资管理系统
信息查询基本信息查询
系统管理
信息管理员工信息管理
修改密码
退出系统
薪资信息查询
部门信息管理基本信息管理
薪资信息管理
系统管理：
1）用户登录:
普通用户登录：
只可查询信息，不可插入、修改、删除；
主管登录：
可插入、修改、查询、删除信息；2）修改密码：
凡是该系统的用户都可以修改自己的密码；）退出系统3信息管理：
1）部门信息管理：
主管可以插入，删除，更改部门信息；2）员工信息管理：
主管可以插入，删除，更改员工的基本信息和薪资信息；信息查询:
普通员工可以查询自己的基本信息和薪资信息；二、E-R图
三、关系模型：
1.部门信息（部门编号，部门名称）
密码）,2.员工信息（员工编号，姓名，性别，学历，出生日期，职务，部门编号,应缴税3.薪资信息（账号，员工编号，基本工资，福利，缺勤时间及原因,惩罚金额费，总薪资）
factory的数据库，要求：
、如创建职工工资管理系统中，一个名称为25H:
\DBF文件夹中，其文件大小自动增长为按放置在）将主数据库文件（1factory.mdfMB增长。
（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
余慧1
男孙华
12/04/8001/04/04
否
07/10/021201.5
103
8
张旗2
男陈明
11/10/8001/04/04
否
07/10/021350.6
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）,
）,10210/02,'否',07/'8,'张旗',男',11/10/80（
（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表中输入如下记录：
750.801/04/043程西900.001/04/044孙天奇2006.8501/04/04刘夫文1250.0601/04/04刘欣725.0701/04/04余慧728.0
8
01/04/04
张旗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）,
）728.0/04,张旗',01/04（8,'（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'女职工比男职工的工资高多了。
'
GO5、在上机实验题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
@s_name
））+（PRINTCAST@c_noASvarchar（10''+@s_name
INTOst_cursorFROMNEXTFETCH@c_no,END
st_cursorCLOSEst_cursorDEALLOCATEGO（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
GO6、在factory数据库上，使用T-SQL语句完成如下各题：
（1）在worker表中的“部门号”列上创建一个非聚集索引；若该索引已存在，则删除后重建。
CREATENONCLUSTEREDINDEXbmh_indexONworker（部门号）
GO
（2）在salary表的“职工号”和“日期”列创建聚集索引，并且强制唯一性。
CREATEUNIQUECLUSTEREDINDEXzgh_rq_indexONsalary（职工号,日期）
GO7、在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的定义。
USEfactor
升级会员 