factory数据库考核

1、精品文档 Q七淼屛於乂垮 Jiangsu university of science and technology Database programmi ng with RDBMS 课程设计 职工工资管理统 指导老师：郭小芳 学号：1441904103 姓名： 咼嘉威 Database programmi ng with RDBMS考查命题 i、根据周围的实际情况，自选一个小型的数据库应用项目， 并深入到应用项目中调研, 进行分析和设计。例如可选择职工工资管理系统、 人事管理系统、教材管理系统和小型超市 商品管理系统和图书管理系统等。要求写出数据库设计报告。 一、功能需求 系统的基本流程是：用

2、户登入一主界面一 选择各项子系统。 模块设计和功能说明 （一）权限说明： 对于不同的用户可以对其赋予不同的权限级别。部门主管拥有最高的权限，能够 对员工的资料进行增删，对部门的信息进行增删。 1）用户登入：填入用户名称，填写正确的密码，选择用户类型。 2）用户登入后，可以对自己的密码进行修改。 3）用户可以查看个人资料。 以下是各部门主管所特有的权限，其他员工均没有该权限，不能实现一下的操作。 4）部门主管可以对员工信息进行浏览、查询、编辑、插入、删除。 5）部门主管可以对该公司的部门信息进行浏览、查询、编辑、插入、删除。 （二）系统的要求： 职工工资管理系统开发时，应该满足以下几点 1 ）方

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

4、； 、E-R 图 图 应交祝费 姓名 员工编号 藝资信息 员工信慝 考勤 出生曰期 st 脈号 管理 惩罚金颠 部门信息 部门名称 熬门狷昌 缺勤时囘護 原因 三、关系模型: ,惩罚金额，应缴税 口 号， 号， 1. 部门信息（部门编号，部门名称） 2. 员工信息（员工编号，姓名，性别，学历，出生日期，职务，部门编号 3. 薪资信息（账号，员工编号，基本工资，福利，缺勤时间及原因 费，总薪资） 2、如创建职工工资管理系统中，一个名称为factory的数据库，要求: （1 ）将主数据库文件factory.mdf 放置在H:DBF 文件夹中，其文件大小自动增长为 按5 MB增长。 (2 )将事务日

5、志文件factory_log.ldf 放置在H:DBF文件夹中，其文件大小自动增长 为按1 MB增长。 create database factory on ( name =factory , FILENAME =H:DBFfactory.mdf size=3mb , filegrowth =5mb ) log on ( name =factory_log , FILENAME =H:DBFfactory_log.ldf size = 1 mb , filegrowth =1 mb ) 3、在上机面所创建的数据库factory中，完成如下各题操作。 (1 )用create 语句建立职工表 wo

6、rker，其结构为：职工号，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

7、 否 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 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 ,是,

8、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语句

9、建立部门表 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) ; 日

10、期，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 use factory go create table salary (职工号 int prima

11、ry key , 姓名 char (8), 日期 datetime , 工资 decimal (6,1) ); in sert in to salary 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

12、 / 04,728.0 ) (4) 建立 worker、depart和salary 3个表之间的关系。 Id 汕 ISQL WQ16M -丄欽StS +亠拓能 :* II toy H英 t -a 电左 4、在建立的factory数据库上，完成如下各题的程序，要求以文本格式显示结果。 (1 )显示所有职工的年龄，并按职工号递增排序。 USE factory GO SELECT 职工号，丫EAR (参加工作)-YEAR (岀生日期)AS 年龄 FROM worker ORDER BY 职工号 ASC GO (2) 求出各部门的党员人数。 USE factory GO SELECT 部门号 ,COU

13、NT （党员否 ） 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 . 职工

14、号 ,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 平均工资 F

15、ROM 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 （工

16、资 ） 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

17、 EXISTS （SELECT * FROM worker WHERE 职工号 =10 ） BEGIN SELECT 部门名 FROM worker ,depart WHERE worker .部门号 =depart .部门号 AND 职工号 =10 END ELSE PRINT 不存在职工号为的员工。 GO （ 10）求出男女职工的平均工资，若男职工平均工资高出女职工平均工资50% ，则显 示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在 1.50.8之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男 职工的工资高多了”的信息。 USE fa

18、ctory 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 性别 =女 I

19、F（m -f）/ f=0.5 ） PRINT 男职工比女职工的工资高多了。 ELSE IF（m / f=0.8 AND m / f=1.5 ） PRINT 男职工跟女职工的工资差不多。 ELSE PRINT 女职工比男职工的工资高多了。 GO 5 、在上机实验题 4 建立的 factory 数据库上，完成如下各题（所有 SELECT 语句的 查询结果以文本格式显示）。 （1 ）删除 factory 数据库上各个表之间建立的关系。 ALTER TABLE salary DROP CONSTRAINT FK_salary_worker GO ALTER TABLE worker DROP CONS

20、TRAINT 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

21、 .职工号 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

22、/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 .姓名 ,部门名 ,sal

23、ary . 日期 ,工资 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

24、 （工资 ） 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 部门名

25、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

26、_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_curso

27、r INTO c_no ,s_name PRINT 职工号 + +姓名 WHILE FETCH_STATUS = 0 BEGIN PRINT CAST (c_no AS varchar (10 )+ s_name FETCH NEXT FROM st_cursor INTO c_no ,s_name END CLOSE st_cursor DEALLOCATE st_cursor GO ( 10)先显示 worker 表中的职工人数，开始一个事务，插入一个职工记录，再显示 worker 表中的职工人数，回滚该事务，最后显示 worker 表中的职工人数。 USE factory GO SELE

28、CT 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 表中的“部门号”列

29、上创建一个非聚集索引；若该索引已存在，则删除 后重建。 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 fact

30、ory 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 ,sa

31、lary 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

32、 view3 GO 8 、在 factory 数据库上，使用 T-SQL 语句完成如下各题： 1 ）修改 worker 表的结构，添加“ E_mailvarchar（2 0） ”字段。 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

33、”字段 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 wo

34、rker ADD CONSTRAINT default_xb DEFAULT 男 FOR 性别 GO （7）实施salary表的“工资”列值限定在09999的约束。 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 （

35、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)

36、 删除( 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 wor

37、ker 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 c

38、har （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

39、 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 TRIGGE

40、R 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 表中对应职工的工资记

41、录。 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 ）创建一个登录账号 XY

42、Z/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 G

43、O GRANT Connect SQL TO LCB_PC GO （2）修改 （1）中为 factory 数据库创建的用户账号 XYZ 的属性， 使 XYZ 登录账号对 factory 数据库具有 db_owner 权限。 可编辑 _w 联附$111愷录名的用户加： 用户 StUSSft 0 U23 db_dflnyditw*fi6f r i Lkhk h Kt9tr FtctorjF Jb 诚订 n idb Kcpji tSei ?fr hurtSrvtlipDB n HIE stunsisii 怙 npdb n TutDB 己启用 Hil 縣户：ictry 岬弟色詆身也皿加血y (Lt_t

44、 icjn-pr.psrat or J.L_ 鮎t 址* hr tor n if血込匕 LJ db_dtnyjt v tir dsecuri tya-ln i n / fuKlic 口何 12、使用SQL Server管理控制器对factory 数据库执行完全备份（备份到 H:DBFbackup1 文件中）和恢复操作。 备吩裁揖有-fndcry 瓷= Kt帮助 Microsoft SQL Server Management Studio a 症数IB曲TKtrr胡劳伯少响” Z欣复制前 杏毛帏帝舀n 俪.桃 加; 繼0) 0瞒鮒 1如珂z购1 saan. 哋 内歆C 昏瀕过唧搁： 釦菇_ S

45、!1 I 13、使用 SQL Server 管理控制器生成数据库factory 的脚本文件 H:DBFfactory.sql，并显示该文件的内容。 USE master GO * /* Object: Database factory Script Date: 12/23/2015 12:51:34 */ CREATE DATABASE factory ON PRIMARY (NAME = Nfactory , FILENAME = NG:DBFfactory.mdf MAXSIZE = UNLIMITED , FILEGROWTH = 5120 KB ) LOG ON ,SIZE = 3072 KB (NAME = Nfactory_log , FILENAME = NG:DBFfactory_log.ldf 1024 KB , MAXSIZE = 2048 GB , FILEGROWTH = 1024 KB ) GO ,SIZE = ALTER DATABASE factory SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY (I