factory数据库考核资料.docx

factory数据库考核资料.docx

《factory数据库考核资料.docx》由会员分享，可在线阅读，更多相关《factory数据库考核资料.docx（32页珍藏版）》请在冰豆网上搜索。

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