factory数据库考核

《Database programming with RDBMS》

课程设计

职工工资管理统

指导老师：郭小芳 学号：03 姓名： 高嘉威

《Database programming with RDBMS》考查命题

1、根据周围的实际情况，自选一个小型的数据库应用项目，并深入到应用项目中调研，进行分析和设计。例如可选择职工工资管理系统、人事管理系统、教材管理系统和小型超市商品管理系统和图书管理系统等。要求写出数据库设计报告。

一、功能需求

系统的基本流程是：用户登入—>主界面—>选择各项子系统。

模块设计和功能说明

（一）权限说明：

对于不同的用户可以对其赋予不同的权限级别。部门主管拥有最高的权限，能够对员工的资料进行增删，对部门的信息进行增删。

1）用户登入：填入用户名称，填写正确的密码，选择用户类型。 2）用户登入后，可以对自己的密码进行修改。 3）用户可以查看个人资料。

以下是各部门主管所特有的权限，其他员工均没有该权限，不能实现一下的操作。 4）部门主管可以对员工信息进行浏览、查询、编辑、插入、删除。 5）部门主管可以对该公司的部门信息进行浏览、查询、编辑、插入、删除。

（二）系统的要求：

职工工资管理系统开发时，应该满足以下几点 1）方便，容易操作，快捷的原则。

2）对于不同的用户，应该有不同的使用权限。

3) 各主管的任务是管理部门、职工、工资信息，即时更新、登记、发布信 息，普通职工可以查询、核对自己的基本信息和工资信息。 整个系统模块划分如下图：

用户登入 修改密码 退出系统 部门信息管理 员工信息管理 基本信息查询 薪资信息查询 系统管理 信息管理 信息查询 职工工资管理系统 基本信息管理

薪资信息管理

系统管理：

1）用户登录: 普通用户登录：只可查询信息，不可插入、修改、删除；

主管登录：可插入、修改、查询、删除信息；

2）修改密码：凡是该系统的用户都可以修改自己的密码； 3）退出系统

信息管理：

1）部门信息管理：主管可以插入，删除，更改部门信息；

2）员工信息管理：主管可以插入，删除，更改员工的基本信息和薪资信息；

信息查询:

普通员工可以查询自己的基本信息和薪资信息；

二、E-R图

三、关系模型：

1.部门信息（部门编号，部门名称）

2.员工信息（员工编号，姓名，性别，学历，出生日期，职务，部门编号,密码）

3.薪资信息（账号，员工编号，基本工资，福利，缺勤时间及原因,惩罚金额,应缴税 费，总薪资）

2、如创建职工工资管理系统中，一个名称为factory的数据库，要求：

（1）将主数据库文件放置在H:\\DBF文件夹中，其文件大小自动增长为按5 MB增长。 （2）将事务日志文件放置在H:\\DBF文件夹中，其文件大小自动增长为按1 MB增长。

create database factory on (

name=factory,

FILENAME='H:\\DBF\\', size=3mb,

filegrowth=5mb )

log on (

name=factory_log,

FILENAME='H:\\DBF\\', size=1mb,

filegrowth=1mb )

3、在上机面所创建的数据库factory中，完成如下各题操作。

（1）用create语句建立职工表worker，其结构为：职工号，int；姓名，char(8)；性别，char(2)；出生日期，datetime；否char(2）；参加工作，datetime；部门号，int。其中“职工号”为主键。并用insert语句在worker表中输入如下记录：

职工号 1 2 3 4 5 6 7 8 姓名 孙华 陈明 程西 孙天奇 刘夫文 刘欣 余慧 张旗 性别 男 男 女 女 男 男 男 男 出生日期 01/03/52 05/08/45 06/10/80 03/10/65 01/11/42 10/08/52 12/04/80 11/10/80 否 是 否 否 是 否 否 否 否 参加工作 10/10/70 01/01/65 07/10/02 07/10/87 08/10/60 01/07/70 07/10/02 07/10/02 部门号 101 102 101 102 102 101 103 102 use factory go

create table worker

(职工号 int primary key, 姓名 char(8), 性别 char(2), 出生日期 datetime, 否 char(2), 参加工作 datetime, 部门号 int, );

insert into worker

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 部门名 财务处 人事处 市场部 use factory go

create table depart

(部门号 int primary key, 部门名 char(10) );

insert into depart values(101,'财务处'), (102,'人事处'), (103,'市场部')

（3）用create建立职工工资表salary，其结构为：职工号，int；姓名，char(8)；日期，datetime；工资，decimal(6,1)。其中，“职工号”和“日期”为主键。并用insert语句在salary表中输入如下记录：

职工号 1 2 3 4 5 6 7 8 姓名 孙华 陈明 程西 孙天奇 刘夫文 刘欣 余慧 张旗 日期 01/04/04 01/04/04 01/04/04 01/04/04 01/04/04 01/04/04 01/04/04 01/04/04 工资 use factory go

create table salary

(职工号 int primary key, 姓名 char(8), 日期 datetime, 工资 decimal(6,1) );

insert into salary

values(1,'孙华',01/04/04,, (2,'陈明',01/04/04,, (3,'程西',01/04/04,, (4,'孙天奇',01/04/04,, (5,'刘夫文',01/04/04,, (6,'刘欣',01/04/04,, (7,'余慧',01/04/04,, (8,'张旗',01/04/04,

（4）建立worker、depart和salary 3个表之间的关系。

4、在建立的factory数据库上，完成如下各题的程序，要求以文本格式显示结果。 （1）显示所有职工的年龄，并按职工号递增排序。

USE factory

GO

SELECT 职工号,YEAR(参加工作)-YEAR(出生日期) AS 年龄 FROM worker ORDER BY 职工号 ASC GO

（2）求出各部门的人数。

USE factory

GO

SELECT 部门号,COUNT(否) AS 人数 FROM worker GROUP BY 部门号 GO

（3）显示所有职工的姓名和2004年1月份的工资。

USE factory

GO

SELECT worker.姓名,工资 FROM worker,salary WHERE worker.职工号=salary.职工号 GO

（4）显示所有职工的职工号、姓名和平均工资。

USE factory

GO

SELECT worker.职工号,worker.姓名,AVG(工资) AS 平均工资 FROM worker,salary GROUP BY worker.职工号,worker.姓名 GO

（5）显示所有职工的职工号、姓名、部门名和2004年2月份的工资，并按部门名的顺序排列。

USE factory

GO

SELECT worker.职工号,worker.姓名,部门名,工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 ORDER BY 部门名 GO

（6）显示各部门名和该部门的所有职工平均工资。

USE factory

GO

SELECT 部门名,AVG(工资) AS 平均工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GROUP BY 部门名 GO

（7）显示所有平均工资高于1200的部门名和对应的平均工资。

USE factory

GO

SELECT 部门名,AVG(工资)AS 平均工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GROUP BY 部门名 HAVING AVG(工资)>1200 GO

（8）显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属于管理部门，市场部属于市场部门。

USE factory

GO

SELECT 职工号,姓名,部门类型= CASE

WHEN 部门名='财务处' THEN '管理部门' WHEN 部门名='人事处' THEN '管理部门' WHEN 部门名='市场部' THEN '市场部门' END

FROM worker,depart

WHERE worker.部门号=depart.部门号 GO

（9）若存在职工号为10的职工，则显示其工作部门名称，否则显示相应提示信息。

USE factory GO IF EXISTS

(SELECT * FROM worker WHERE 职工号=10)

BEGIN

SELECT 部门名 FROM worker,depart

WHERE worker.部门号=depart.部门号 AND 职工号=10 END ELSE

PRINT'不存在职工号为‘’的员工。' GO

（10）求出男女职工的平均工资，若男职工平均工资高出女职工平均工资50%，则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在～之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职工的工资高多了”的信息。

USE factory

GO

SELECT 性别,AVG(工资) AS 平均工资 FROM worker,salary WHERE worker.职工号=salary.职工号 GROUP BY 性别 GO

DECLARE @m decimal

DECLARE @f decimal

SELECT @m=AVG(工资) FROM worker,salary WHERE worker.职工号=salary.职工号 AND 性别='男' SELECT @f=AVG(工资) FROM worker,salary WHERE worker.职工号=salary.职工号 AND 性别='女' IF((@m-@f)/@f>=

PRINT '男职工比女职工的工资高多了。' ELSE IF(@m/@f>= AND @m/@f<=

PRINT '男职工跟女职工的工资差不多。' ELSE

PRINT '女职工比男职工的工资高多了。' GO

5、在上机实验题4建立的factory数据库上，完成如下各题（所有SELECT语句的查询结果以文本格式显示）。

（1）删除factory数据库上各个表之间建立的关系。

ALTER TABLE salary

DROP CONSTRAINT FK_salary_worker GO

ALTER TABLE worker

DROP CONSTRAINT FK_worker_depart GO

（2）显示各职工的工资记录和相应的工资小计。 use factory go

select 职工号,姓名,工资 from salary order by 职工号

compute sum(工资) by 职工号

go

（3）按性别和部门名的所有组合方式列出相应的平均工资。

USE factory GO

SELECT 性别,部门名,AVG(工资) AS 平均工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GROUP BY 性别,部门名 GO

（4）在worker表中使用以下语句插入一个职工记录：

INSERT INTO worker VALUES(20,'陈立','女','55/03/08',1,'75/10/10',4)

在depart表中使用以下语句插入一个部门记录：

INSERT INTO depart VALUES(5,'设备处')

对worker和depart表进行全外连接显示职工的职工号、姓名和部门名，然后删除这两个插入的记录。

USE factory

GO

INSERT INTO worker VALUES('20','陈立','55/03/08',1,'75/10/10',4) INSERT INTO depart VALUES('5','设备处') GO

SELECT 职工号,姓名,部门名 FROM worker

FULL OUTER JOIN depart ON worker.部门号=depart.部门号 GO

DELETE FROM worker WHERE 职工号=20 GO

DELETE FROM depart WHERE 部门号=5 GO

（5）显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。

USE factory

GO

SELECT worker.职工号,worker.姓名,部门名,salary.日期,工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资= (SELECT MAX(工资) FROM salary) GO

（6）显示最高工资的职工所在的部门名。

USE factory

GO

SELECT 部门名 FROM worker,depart,salary

WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资= (SELECT MAX(工资) FROM salary) GO

（7）显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。

USE factory

GO

SELECT worker.职工号,worker.姓名 FROM worker,salary

WHERE worker.职工号=salary.职工号 AND 工资< (SELECT AVG(工资) FROM salary) GO

（8）采用游标方式实现（6）小题的功能。

USE factory GO

DECLARE @s_max CHAR(10)

DECLARE st_cursor CURSOR

FOR SELECT 部门名 FROM worker,depart,salary

WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资= (SELECT MAX(工资) FROM salary) OPEN st_cursor

FETCH NEXT FROM st_cursor INTO @s_max PRINT '部门名'

WHILE @@FETCH_STATUS = 0 BEGIN

PRINT CAST(@s_max AS char(10))

FETCH NEXT FROM st_cursor INTO @s_max END

CLOSE st_cursor DEALLOCATE st_cursor GO

（9）采用游标方式实现（7）小题的功能。

USE factory GO

DECLARE @c_no int,@s_name varchar(10)

DECLARE st_cursor CURSOR

FOR SELECT worker.职工号,worker.姓名 FROM worker,salary WHERE worker.职工号=salary.职工号 AND 工资< (SELECT AVG(工资) FROM salary) OPEN st_cursor

FETCH NEXT FROM st_cursor INTO @c_no,@s_name PRINT '职工号'+' '+'姓名' WHILE @@FETCH_STATUS = 0 BEGIN

PRINT CAST(@c_no AS varchar(10))+' END

CLOSE st_cursor DEALLOCATE st_cursor GO

'+@s_name

FETCH NEXT FROM st_cursor INTO @c_no,@s_name

（10）先显示worker表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。

USE factory

GO

SELECT COUNT(*)AS 职工人数 FROM worker GO

BEGIN TRANSACTION

INSERT INTO worker

VALUES(11,'小明','女','1955/03/08','1','1975/10/10','4') SAVE TRANSACTION TRAN1

SELECT COUNT(*)AS 职工人数 FROM worker GO

ROLLBACK TRANSACTION

SELECT COUNT(*)AS 职工人数 FROM worker GO

6、在factory数据库上，使用T-SQL语句完成如下各题：

（1）在worker表中的“部门号”列上创建一个非聚集索引；若该索引已存在，则删除后重建。

CREATE NONCLUSTERED INDEX bmh_index ON worker(部门号) GO

（2）在salary表的“职工号”和“日期”列创建聚集索引，并且强制唯一性。

CREATE UNIQUE CLUSTERED INDEX zgh_rq_index ON salary(职工号,日期) GO

7、在factory数据库上，使用T-SQL语句完成如下各题：

（1）建立视图view1，查询所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。

USE factory GO

CREATE VIEW view1

AS

SELECT worker.职工号,worker.姓名,部门名,工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GO

（2）建立视图view2，查询所有职工的职工号、姓名和平均工资。

USE factory GO

CREATE VIEW view2

AS

SELECT worker.职工号,worker.姓名,AVG(工资) AS 平均工资 FROM worker,salary GROUP BY worker.职工号,worker.姓名 GO

（3）建立视图view3，查询各部门名和该部门的所有职工平均工资。

USE factory GO

CREATE VIEW view3 AS

SELECT 部门名,AVG(工资) AS 平均工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GROUP BY 部门名 GO

（4）显示视图view3的定义。

USE factory GO

EXEC sp_help view3 GO

8、在factory数据库上，使用T-SQL语句完成如下各题： （1）修改worker表的结构，添加“E_mail varchar(20)”字段。

USE factory GO

ALTER TABLE worker ADD E_mail varchar(20) GO

（2）将上例中worker表中“E_mail”字段的数据类型改为varchar(30)

USE factory GO

ALTER TABLE worker

ALTER COLUMN E_mail varchar(30) GO

（3）删除worker表中的“E_mail”字段

USE factory GO

ALTER TABLE worker DROP COLUMN E_mail GO

（4）将worker表中姓名为“陈明”的部门号改为“101”。

USE factory GO

UPDATE worker SET 部门号='101' WHERE 姓名='陈明' GO

（5）删除工资表salary中姓名为“陈明”的记录。

USE factory GO

DELETE FROM worker WHERE 姓名='陈明' GO

（6）实施worker表的“性别”列默认值为“男”的约束。

USE factory GO

ALTER TABLE worker ADD CONSTRAINT default_xb DEFAULT '男' FOR 性别 GO

（7）实施salary表的“工资”列值限定在0～9999的约束。

USE factory GO

ALTER TABLE salary

ADD CONSTRAINT check_gz

CHECK (工资>=0 AND 工资<=9999) GO

（8）实施depart表的“部门号”列值唯一的非聚集索引的约束。

USE factory GO

ALTER TABLE depart

ADD CONSTRAINT unique_bmh UNIQUE NONCLUSTERED (部门号) GO

（9）为worker表建立外键“部门号”，参考表depart的“部门号”列。

USE factory GO

ALTER TABLE worker

ADD CONSTRAINT wj_worker_depart

FOREIGN KEY (部门号) REFERENCES depart(部门号) GO

（10）建立一个规则sex：@性别='男' OR @性别='女'，将其绑定到worker表的“性别”列上。

USE factory GO

CREATE RULE sex

AS @性别='男' OR @性别='女' GO

EXEC sp_bindrule 'sex','worker.性别' GO

（11）删除（1）小题所建立的约束。

USE factory GO

ALTER TABLE worker

DROP CONSTRAINT default_xb GO

（12）删除（2）小题所建立的约束。

USE factory GO

ALTER TABLE salary

DROP CONSTRAINT check_gz GO

（12）删除（3）小题所建立的约束。

USE factory GO

ALTER TABLE depart

DROP CONSTRAINT unique_bmh GO

（14）删除（4）小题所建立的约束。

USE factory GO

ALTER TABLE worker

DROP CONSTRAINT wj_worker_depart GO

（15）解除（5）小题所建立的绑定并删除规则sex。

EXEC sp_unbindrule 'worker.性别' GO

DROP RULE sex GO

9、在的factory数据库上，使用T-SQL语句完成如下各题： （1）创建一个为worker表添加职工记录的存储过程Addworker。

CREATE PROCEDURE Addworker @number int, @name char(8), @gender char(2), @birthday datetime, @party char(2), @starttime datetime, @departnumber int AS BEGIN

INSERT INTO worker

VALUES(@number,@name,@gender,@birthday,@party,@starttime,@departnumber) END GO

（2）创建一个存储过程Delworker删除worker表中指定职工号的记录。

USE factory GO

CREATE PROCEDURE Delworker @number int AS

BEGIN

DELETE FROM worker WHERE 职工号=@number END GO

（3）显示存储过程Delworker。

USE factory GO

EXEC sp_helptext Delworker GO

（4）删除存储过程Addworker和Delworker。

USE factory GO

DROP PROCEDURE Addworker,Delworker GO

10、在factory数据库上，使用T-SQL语句完成如下各题：

（1）在表depart上创建一个触发器depart_update，当更改部门号时同步更改worker表中对应的部门号。

USE factory GO

CREATE TRIGGER depart_update ON depart FOR UPDATE AS

IF UPDATE(部门号) BEGIN

DECLARE @number1 int,@number2 int

SELECT @number1=deleted.部门号,@number2=inserted.部门号 FROM deleted,inserted UPDATE worker

SET 部门号=@number1 WHERE 部门号=@number2 END GO

（2）在表worker上创建一个触发器worker_delete，当删除职工记录时同步删除salary表中对应职工的工资记录。

USE factory GO

CREATE TRIGGER worker_delete ON worker FOR DELETE AS BEGIN

DELETE FROM salary WHERE 职工号=

(SELECT 职工号 FROM deleted)

END GO

（3）删除触发器depart_update。

DROP TRIGGER depart_update GO

（4）删除触发器worker_delete。

DROP TRIGGER worker_delete GO

11、在SQL Server管理控制器中完成如下操作：

（1）创建一个登录账号XYZ/123（其默认的工作数据库为factory；其“服务器角色”设置为sysadmin；将“映射到此登录名的用户”设置为Factory，使其具有public权限；设置安全对象LCB-PC服务器具有Connect SQL权限）。

CREATE LOGIN XYZ WITH PASSWORD='123', DEFAULT_DATABASE=factory GO

EXEC sp_addsrvrolemember 'XYZ','sysadmin' GO

CREATE USER Factory FOR LOGIN XYZ GO

GRANT public TO Factory GO

GRANT Connect SQL TO LCB_PC GO

（2）修改（1）中为factory数据库创建的用户账号XYZ的属性，使XYZ登录账号对factory数据库具有db_owner权限。

12、使用SQL Server管理控制器对factory数据库执行完全备份（备份到H:\\DBF\\backup1文件中）和恢复操作。

13、使用SQL Server管理控制器生成数据库factory的脚本文件H:\\DBF\\，并显示该文件的内容。

USE [master] GO

/****** Object: Database [factory] Script Date: 12/23/2015 12:51:34 ******/ CREATE DATABASE [factory] ON PRIMARY

( NAME = N'factory', FILENAME = N'G:\\DBF\\' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB )

LOG ON

( NAME = N'factory_log', FILENAME = N'G:\\DBF\\' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )

GO

ALTER DATABASE [factory] SET COMPATIBILITY_LEVEL = 100 GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin

EXEC [factory].[dbo].[sp_fulltext_database] @action = 'enable' end GO

ALTER DATABASE [factory] SET ANSI_NULL_DEFAULT OFF GO

ALTER DATABASE [factory] SET ANSI_NULLS OFF GO

ALTER DATABASE [factory] SET ANSI_PADDING OFF GO

ALTER DATABASE [factory] SET ANSI_WARNINGS OFF GO

ALTER DATABASE [factory] SET ARITHABORT OFF GO

ALTER DATABASE [factory] SET AUTO_CLOSE OFF GO

ALTER DATABASE [factory] SET AUTO_CREATE_STATISTICS ON GO

ALTER DATABASE [factory] SET AUTO_SHRINK OFF GO

ALTER DATABASE [factory] SET AUTO_UPDATE_STATISTICS ON GO

ALTER DATABASE [factory] SET CURSOR_CLOSE_ON_COMMIT OFF GO

ALTER DATABASE [factory] SET CURSOR_DEFAULT GLOBAL GO

ALTER DATABASE [factory] SET CONCAT_NULL_YIELDS_NULL OFF GO

ALTER DATABASE [factory] SET NUMERIC_ROUNDABORT OFF GO

ALTER DATABASE [factory] SET QUOTED_IDENTIFIER OFF GO

ALTER DATABASE [factory] SET RECURSIVE_TRIGGERS OFF GO

ALTER DATABASE [factory] SET DISABLE_BROKER GO

ALTER DATABASE [factory] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO

ALTER DATABASE [factory] SET DATE_CORRELATION_OPTIMIZATION OFF GO

ALTER DATABASE [factory] SET TRUSTWORTHY OFF GO

ALTER DATABASE [factory] SET ALLOW_SNAPSHOT_ISOLATION OFF GO

ALTER DATABASE [factory] SET PARAMETERIZATION SIMPLE GO

ALTER DATABASE [factory] SET READ_COMMITTED_SNAPSHOT OFF GO

ALTER DATABASE [factory] SET HONOR_BROKER_PRIORITY OFF GO

ALTER DATABASE [factory] SET READ_WRITE GO

ALTER DATABASE [factory] SET RECOVERY FULL GO

ALTER DATABASE [factory] SET MULTI_USER GO

ALTER DATABASE [factory] SET PAGE_VERIFY CHECKSUM GO

ALTER DATABASE [factory] SET DB_CHAINING OFF GO