factory数据库考核

1、Database programming with RDBMS课程设计职工工资管理统指导老师：郭小芳学号：1441904103姓名： 高嘉威 Database programming with RDBMS考查命题1、根据周围的实际情况，自选一个小型的数据库应用项目，并深入到应用项目中调研，进行分析和设计。例如可选择职工工资管理系统、人事管理系统、教材管理系统和小型超市商品管理系统和图书管理系统等。要求写出数据库设计报告。一、功能需求系统的基本流程是：用户登入>主界面>选择各项子系统。模块设计和功能说明（一）权限说明：对于不同的用户可以对其赋予不同的权限级别。部门主管拥有最高的权限，

2、能够对员工的资料进行增删，对部门的信息进行增删。 1）用户登入：填入用户名称，填写正确的密码，选择用户类型。 2）用户登入后，可以对自己的密码进行修改。 3）用户可以查看个人资料。以下是各部门主管所特有的权限，其他员工均没有该权限，不能实现一下的操作。 4）部门主管可以对员工信息进行浏览、查询、编辑、插入、删除。 5）部门主管可以对该公司的部门信息进行浏览、查询、编辑、插入、删除。 （二）系统的要求：职工工资管理系统开发时，应该满足以下几点 1）方便，容易操作，快捷的原则。2）对于不同的用户，应该有不同的使用权限。 

3、;3) 各主管的任务是管理部门、职工、工资信息，即时更新、登记、发布信 息，普通职工可以查询、核对自己的基本信息和工资信息。职工工资管理系统 整个系统模块划分如下图：信息查询信息管理系统管理用户登入退出系统修改密码员工信息管理部门信息管理基本信息查询薪资信息查询薪资信息管理基本信息管理系统管理：1）用户登录: 普通用户登录：只可查询信息，不可插入、修改、删除；  主管登录：可插入、修改、查询、删除信息；2）修改密码：凡是该系统的用户都可以修改自己的密码； 3）退出系统信息管理：1）部门信息管理：主管可以插入，删除，更改部门信息；2）员工信息管理：主管可以插入，删除，更改员工

4、的基本信息和薪资信息； 信息查询: 普通员工可以查询自己的基本信息和薪资信息；2、 E-R图3、 关系模型： 1.部门信息（部门编号，部门名称）2.员工信息（员工编号，姓名，性别，学历，出生日期，职务，部门编号,密码） 3.薪资信息（账号，员工编号，基本工资，福利，缺勤时间及原因,惩罚金额,应缴税 费，总薪资）2、如创建职工工资管理系统中，一个名称为factory的数据库，要求：（1）将主数据库文件factory.mdf放置在H:DBF文件夹中，其文件大小自动增长为按5 MB增长。（2）将事务日志文件factory_log.ldf放置在H:DBF文件夹中

5、，其文件大小自动增长为按1 MB增长。create database factoryon(name=factory,FILENAME='H:DBFfactory.mdf',size=3mb,filegrowth=5mb)log on(name=factory_log,FILENAME='H:DBFfactory_log.ldf',size=1mb,filegrowth=1mb)3、在上机面所创建的数据库factory中，完成如下各题操作。（1）用create语句建立职工表worker，其结构为：职工号，int；姓名，char(8)；性别，char(2)；出生日期

6、，datetime；党员否char(2）；参加工作，datetime；部门号，int。其中“职工号”为主键。并用insert语句在worker表中输入如下记录：职工号姓名性别出生日期党员否参加工作部门号1孙华男01/03/52是10/10/701012陈明男05/08/45否01/01/651023程西女06/10/80否07/10/021014孙天奇女03/10/65是07/10/871025刘夫文男01/11/42否08/10/601026刘欣男10/08/52否01/07/701017余慧男12/04/80否07/10/021038张旗男11/10/80否07/10/02102use f

7、actorygocreate table worker(职工号 int primary key, 姓名 char(8), 性别 char(2), 出生日期 datetime, 党员否 char(2), 参加工作 datetime, 部门号 int,);insert into workervalues(1,'孙华','男',01/03/52,'是',10/10/70,101),(2,'陈明','男',05/08/45,'否',01/01/65,102),(3,'程西','女&#

8、39;,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,

9、9;张旗','男',11/10/80,'否',07/10/02,102)（2）用create语句建立部门表depart，其结构为：部门号，int；部门名，char(10)。其中，“部门号”为主键。并用insert语句在depart表中输入如下记录：部门号部门名101财务处102人事处103市场部use factorygocreate table depart(部门号 int primary key,部门名 char(10);insert into departvalues(101,'财务处'),(102,'人事处'), (

10、103,'市场部')（3）用create建立职工工资表salary，其结构为：职工号，int；姓名，char(8)；日期，datetime；工资，decimal(6,1)。其中，“职工号”和“日期”为主键。并用insert语句在salary表中输入如下记录：职工号姓名日期工资1孙华01/04/041201.52陈明01/04/041350.63程西01/04/04750.84孙天奇01/04/04900.05刘夫文01/04/042006.86刘欣01/04/041250.07余慧01/04/04725.08张旗01/04/04728.0use factory gocreate

11、 table salary(职工号 int primary key,姓名 char(8),日期 datetime,工资 decimal(6,1);insert into salaryvalues(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,&#

12、39;余慧',01/04/04,725.0),(8,'张旗',01/04/04,728.0)（4） 建立worker、depart和salary 3个表之间的关系。4、在建立的factory数据库上，完成如下各题的程序，要求以文本格式显示结果。（1）显示所有职工的年龄，并按职工号递增排序。USE factoryGOSELECT 职工号,YEAR(参加工作)-YEAR(出生日期) AS 年龄 FROM workerORDER BY 职工号 ASCGO（2） 求出各部门的党员人数。USE factoryGOSELECT 部门号,COUNT(党员否) AS 党员人数 FROM

13、 workerGROUP BY 部门号GO（3） 显示所有职工的姓名和2004年1月份的工资。USE factoryGOSELECT worker.姓名,工资 FROM worker,salaryWHERE worker.职工号=salary.职工号GO（4） 显示所有职工的职工号、姓名和平均工资。USE factoryGOSELECT worker.职工号,worker.姓名,AVG(工资) AS 平均工资 FROM worker,salaryGROUP BY worker.职工号,worker.姓名GO（5） 显示所有职工的职工号、姓名、部门名和2004年2月份的工资，并按部门名的顺序排列

14、。USE factoryGOSELECT worker.职工号,worker.姓名,部门名,工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号ORDER BY 部门名GO（6） 显示各部门名和该部门的所有职工平均工资。USE factoryGOSELECT 部门名,AVG(工资) AS 平均工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号GROUP BY 部门名GO（7）

15、 显示所有平均工资高于1200的部门名和对应的平均工资。USE factoryGOSELECT 部门名,AVG(工资)AS 平均工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号GROUP BY 部门名HAVING AVG(工资)>1200GO（8） 显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属于管理部门，市场部属于市场部门。USE factoryGOSELECT 职工号,姓名,部门类型=CASE WHEN 部门名='财务处' THEN '

16、管理部门'WHEN 部门名='人事处' THEN '管理部门'WHEN 部门名='市场部' THEN '市场部门'END FROM worker,departWHERE worker.部门号=depart.部门号GO（9） 若存在职工号为10的职工，则显示其工作部门名称，否则显示相应提示信息。USE factoryGOIF EXISTS(SELECT * FROM workerWHERE 职工号=10)BEGIN SELECT 部门名 FROM worker,depart WHERE worker.部门号=depart.部

17、门号 AND 职工号=10ENDELSE PRINT'不存在职工号为的员工。' GO（10） 求出男女职工的平均工资，若男职工平均工资高出女职工平均工资50%，则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在1.50.8之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职工的工资高多了”的信息。USE factoryGOSELECT 性别,AVG(工资) AS 平均工资 FROM worker,salaryWHERE worker.职工号=salary.职工号GROUP BY 性别GODECLARE m decimalDEC

18、LARE f decimalSELECT m=AVG(工资) FROM worker,salaryWHERE worker.职工号=salary.职工号 AND 性别='男'SELECT f=AVG(工资) FROM worker,salaryWHERE worker.职工号=salary.职工号 AND 性别='女'IF(m-f)/f>=0.5) PRINT '男职工比女职工的工资高多了。'ELSE IF(m/f>=0.8 AND m/f<=1.5) PRINT '男职工跟女职工的工资差不多。'ELSE PRI

19、NT '女职工比男职工的工资高多了。'GO5、在上机实验题4建立的factory数据库上，完成如下各题（所有SELECT语句的查询结果以文本格式显示）。（1）删除factory数据库上各个表之间建立的关系。ALTER TABLE salaryDROP CONSTRAINT FK_salary_workerGOALTER TABLE workerDROP CONSTRAINT FK_worker_departGO（2） 显示各职工的工资记录和相应的工资小计。use factorygoselect 职工号,姓名,工资 from salaryorder by 职工号compute s

20、um(工资) by 职工号go（3） 按性别和部门名的所有组合方式列出相应的平均工资。USE factoryGOSELECT 性别,部门名,AVG(工资) AS 平均工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号GROUP BY 性别,部门名GO（4） 在worker表中使用以下语句插入一个职工记录：INSERT INTO worker VALUES(20,'陈立','女','55/03/08',1,'75/10/10

21、9;,4)在depart表中使用以下语句插入一个部门记录：INSERT INTO depart VALUES(5,'设备处')对worker和depart表进行全外连接显示职工的职工号、姓名和部门名，然后删除这两个插入的记录。USE factoryGOINSERT INTO worker VALUES('20','陈立','55/03/08',1,'75/10/10',4)INSERT INTO depart VALUES('5','设备处')GOSELECT 职工号,姓名,部门名

22、FROM workerFULL OUTER JOIN depart ON worker.部门号=depart.部门号GODELETE FROM workerWHERE 职工号=20GODELETE FROM departWHERE 部门号=5GO（5） 显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。USE factoryGOSELECT worker.职工号,worker.姓名,部门名,salary.日期,工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工

23、资=(SELECT MAX(工资) FROM salary)GO（6） 显示最高工资的职工所在的部门名。USE factoryGOSELECT 部门名 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资=(SELECT MAX(工资) FROM salary)GO（7） 显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。USE factoryGOSELECT worker.职工号,worker.姓名 FROM worker,salaryWHERE worker.职工号

24、=salary.职工号 AND 工资<(SELECT AVG(工资) FROM salary)GO（8） 采用游标方式实现（6）小题的功能。USE factoryGODECLARE s_max CHAR(10)DECLARE st_cursor CURSOR FOR SELECT 部门名 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资=(SELECT MAX(工资) FROM salary)OPEN st_cursorFETCH NEXT FROM st_curso

25、r INTO s_maxPRINT '部门名'WHILE FETCH_STATUS = 0 BEGIN PRINT CAST(s_max AS char(10) FETCH NEXT FROM st_cursor INTO s_maxENDCLOSE st_cursorDEALLOCATE st_cursorGO（9） 采用游标方式实现（7）小题的功能。USE factoryGODECLARE c_no int,s_name varchar(10)DECLARE st_cursor CURSOR FOR SELECT worker.职工号,worker.姓名 FROM work

26、er,salaryWHERE worker.职工号=salary.职工号 AND 工资<(SELECT AVG(工资) FROM salary)OPEN st_cursorFETCH NEXT FROM st_cursor INTO c_no,s_namePRINT '职工号'+''+'姓名'WHILE FETCH_STATUS = 0 BEGIN PRINT CAST(c_no AS varchar(10)+''+s_name FETCH NEXT FROM st_cursor INTO c_no,s_nameENDCLO

27、SE st_cursorDEALLOCATE st_cursorGO（10） 先显示worker表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。USE factoryGOSELECT COUNT(*)AS 职工人数 FROM workerGOBEGIN TRANSACTIONINSERT INTO workerVALUES(11,'小明','女','1955/03/08','1','1975/10/10','4')SA

28、VE TRANSACTION TRAN1SELECT COUNT(*)AS 职工人数 FROM workerGOROLLBACK TRANSACTIONSELECT COUNT(*)AS 职工人数 FROM workerGO6、在factory数据库上，使用T-SQL语句完成如下各题：（1）在worker表中的“部门号”列上创建一个非聚集索引；若该索引已存在，则删除后重建。CREATE NONCLUSTERED INDEX bmh_index ON worker(部门号)GO（2） 在salary表的“职工号”和“日期”列创建聚集索引，并且强制唯一性。CREATE UNIQUE CLUSTER

29、ED INDEX zgh_rq_index ON salary(职工号,日期)GO7、在factory数据库上，使用T-SQL语句完成如下各题：（1）建立视图view1，查询所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。USE factoryGOCREATE VIEW view1ASSELECT worker.职工号,worker.姓名,部门名,工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号GO（2） 建立视图view2，查询所有职工的职工号、姓名和平

30、均工资。USE factoryGOCREATE VIEW view2ASSELECT worker.职工号,worker.姓名,AVG(工资) AS 平均工资 FROM worker,salaryGROUP BY worker.职工号,worker.姓名GO（3） 建立视图view3，查询各部门名和该部门的所有职工平均工资。USE factoryGOCREATE VIEW view3ASSELECT 部门名,AVG(工资) AS 平均工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号

31、GROUP BY 部门名GO（4） 显示视图view3的定义。USE factoryGOEXEC sp_help view3GO8、在factory数据库上，使用T-SQL语句完成如下各题：（1）修改worker表的结构，添加“E_mail varchar(20)”字段。USE factoryGOALTER TABLE workerADD E_mail varchar(20)GO（2） 将上例中worker表中“E_mail”字段的数据类型改为varchar(30)USE factoryGOALTER TABLE workerALTER COLUMN E_mail varchar(30)GO（

32、3） 删除worker表中的“E_mail”字段USE factoryGOALTER TABLE workerDROP COLUMN E_mailGO（4） 将worker表中姓名为“陈明”的部门号改为“101”。USE factoryGOUPDATE workerSET 部门号='101'WHERE 姓名='陈明'GO（5） 删除工资表salary中姓名为“陈明”的记录。USE factoryGODELETE FROM workerWHERE 姓名='陈明'GO（6） 实施worker表的“性别”列默认值为“男”的约束。USE factoryG

33、OALTER TABLE workerADD CONSTRAINT default_xbDEFAULT '男' FOR 性别GO（7） 实施salary表的“工资”列值限定在09999的约束。USE factoryGOALTER TABLE salaryADD CONSTRAINT check_gzCHECK (工资>=0 AND 工资<=9999)GO（8） 实施depart表的“部门号”列值唯一的非聚集索引的约束。USE factoryGOALTER TABLE departADD CONSTRAINT unique_bmhUNIQUE NONCLUSTERED

34、 (部门号)GO（9） 为worker表建立外键“部门号”，参考表depart的“部门号”列。USE factoryGOALTER TABLE workerADD CONSTRAINT wj_worker_departFOREIGN KEY (部门号) REFERENCES depart(部门号)GO（10） 建立一个规则sex：性别='男' OR 性别='女'，将其绑定到worker表的“性别”列上。USE factoryGOCREATE RULE sexAS 性别='男' OR 性别='女'GOEXEC sp_bindrule

35、 'sex','worker.性别'GO（11） 删除（1）小题所建立的约束。USE factoryGOALTER TABLE workerDROP CONSTRAINT default_xbGO（12） 删除（2）小题所建立的约束。USE factoryGOALTER TABLE salaryDROP CONSTRAINT check_gzGO（12） 删除（3）小题所建立的约束。USE factoryGOALTER TABLE departDROP CONSTRAINT unique_bmhGO（14） 删除（4）小题所建立的约束。USE factoryGO

36、ALTER TABLE workerDROP CONSTRAINT wj_worker_departGO（15） 解除（5）小题所建立的绑定并删除规则sex。EXEC sp_unbindrule 'worker.性别'GODROP RULE sexGO9、在的factory数据库上，使用T-SQL语句完成如下各题：（1）创建一个为worker表添加职工记录的存储过程Addworker。CREATE PROCEDURE Addworkernumber int,name char(8),gender char(2),birthday datetime,party char(2),s

37、tarttime datetime,departnumber intASBEGIN INSERT INTO worker VALUES(number,name,gender,birthday,party,starttime,departnumber)ENDGO（2） 创建一个存储过程Delworker删除worker表中指定职工号的记录。USE factoryGOCREATE PROCEDURE Delworkernumber intASBEGIN DELETE FROM worker WHERE 职工号=numberENDGO（3） 显示存储过程Delworker。USE factoryGO

38、EXEC sp_helptext DelworkerGO（4）删除存储过程Addworker和Delworker。USE factoryGODROP PROCEDURE Addworker,DelworkerGO10、在factory数据库上，使用T-SQL语句完成如下各题：（1）在表depart上创建一个触发器depart_update，当更改部门号时同步更改worker表中对应的部门号。USE factoryGOCREATE TRIGGER depart_update ON departFOR UPDATEASIF UPDATE(部门号)BEGIN DECLARE number1 int,

39、number2 int SELECT number1=deleted.部门号,number2=inserted.部门号 FROM deleted,inserted UPDATE worker SET 部门号=number1 WHERE 部门号=number2ENDGO（2） 在表worker上创建一个触发器worker_delete，当删除职工记录时同步删除salary表中对应职工的工资记录。USE factoryGOCREATE TRIGGER worker_delete ON workerFOR DELETEASBEGIN DELETE FROM salary WHERE 职工号= (SE

40、LECT 职工号 FROM deleted)ENDGO（3） 删除触发器depart_update。DROP TRIGGER depart_updateGO（4）删除触发器worker_delete。DROP TRIGGER worker_deleteGO11、在SQL Server管理控制器中完成如下操作：（1）创建一个登录账号XYZ/123（其默认的工作数据库为factory；其“服务器角色”设置为sysadmin；将“映射到此登录名的用户”设置为Factory，使其具有public权限；设置安全对象LCB-PC服务器具有Connect SQL权限）。CREATE LOGIN XYZWIT

41、H PASSWORD='123',DEFAULT_DATABASE=factoryGOEXEC sp_addsrvrolemember 'XYZ','sysadmin'GOCREATE USER FactoryFOR LOGIN XYZGOGRANT public TO FactoryGOGRANT Connect SQL TO LCB_PCGO（2） 修改（1）中为factory数据库创建的用户账号XYZ的属性，使XYZ登录账号对factory数据库具有db_owner权限。12、 使用SQL Server管理控制器对factory数据库执行完

42、全备份（备份到H:DBFbackup1文件中）和恢复操作。13、使用SQL Server管理控制器生成数据库factory的脚本文件H:DBFfactory.sql，并显示该文件的内容。USE masterGO/* Object: Database factory Script Date: 12/23/2015 12:51:34 */CREATE DATABASE factory ON PRIMARY ( NAME = N'factory', FILENAME = N'G:DBFfactory.mdf' , SIZE = 3072KB , MAXSIZE = U

43、NLIMITED, FILEGROWTH = 5120KB ) LOG ON ( NAME = N'factory_log', FILENAME = N'G:DBFfactory_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )GOALTER DATABASE factory SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')beginEXEC factory.dbo.sp_fulltext_database