factory数据库考核.docx
《factory数据库考核.docx》由会员分享,可在线阅读,更多相关《factory数据库考核.docx(31页珍藏版)》请在冰点文库上搜索。
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
DECLAREmdecimal
DECLAREfdecimal
SELECTm=AVG(工资)FROMworker,salary
WHEREworker、职工号=salary、职工号AND性别='男'
SELECTf=AVG(工资)FROMworker,salary
WHEREworker、职工号=salary、职工号AND性别='女'
IF((m-f)/f>=0、5)
PRINT'男职工比女职工得工资高多了。
'
ELSEIF(m/f>=0、8ANDm/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职工号
putesum(工资)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
DECLAREs_maxCHAR(10)
DECLAREst_cursorCURSOR
FORSELECT部门名FROMworker,depart,salary
WHEREworker、部门号=depart、部门号ANDworker、职工号=salary、职工号AND工资=
(SELECTMAX(工资)FROMsalary)
OPENst_cursor
FETCHNEXTFROMst_cursorINTOs_max
PRINT'部门名'
WHILEFETCH_STATUS=0
BEGIN
PRINTCAST(s_maxASchar(10))
FETCHNEXTFROMst_cursorINTOs_max
END
CLOSEst_cursor
DEALLOCATEst_cursor
GO
(9)采用游标方式实现(7)小题得功能。
USEfactory
GO
DECLAREc_noint,s_namevarchar(10)
DECLAREst_cursorCURSOR
FORSELECTworker、职工号,worker、姓名FROMworker,salary
WHEREworker、职工号=salary、职工号AND工资<
(SELECTAVG(工资)FROMsalary)
OPENst_cursor
FETCHNEXTFROMst_cursorINTOc_no,s_name
PRINT'职工号'+''+'姓名'
WHILEFETCH_STATUS=0
BEGIN
PRINTCAST(c_noASvarchar(10))+''+s_name
FETCHNEXTFROMst_cursorINTOc_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
ADDCONSTRAINTdefault_xb
DEFAULT'男'FOR性别
GO
(7)实施salary表得“工资”列值限定在0~9999得