factory数据库考核

1、修,及装火洋Jiangsu university of science and technologyDatabase programming with RDBMS课程设计职工工资管理统指导老师：郭小芳学号：03姓名： 高嘉威Database programming with RDBMS » 考查命题1、根据周围的实际情况， 自选一个小型的数据库应用项目， 并深入到应用项目中调研， 进行分析和设计。例如可选择职工工资管理系统、 人事管理系统、教材管理系统和小型超市 商品管理系统和图书管理系统等。要求写出数据库设计报告。一、功能需求系统的基本流程是：用户登入一主界面一 选择各项子系统。模

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

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

4、，姓名,性别，学历，出生日期，职务，部门编号，密码）3 .薪资信息（账号工编号，基本工资，福利，缺勤时间及原因，惩罚金额，应缴税费，总薪资）2、如创建职工工资管理系统中，一个名称为 factory的数据库，要求：（1）将主数据库文件放置在 H:DBF文件夹中，其文件大小自动增长为按5 MB增长。（2）将事务日志文件放置在 H:DBF文件夹中，其文件大小自动增长为按1 MB增长。createdatabasefactory on(name=factory,FILENAME ='H:DBF',size=3mb,filegrowth=5mb)log on(name=factory_lo

5、g,FILENAME ='H:DBF',size=1mb,filegrowth=1mb)3、在上机面所创建的数据库factory中，完成如下各题操作。(1)用create语句建立职工表 worker,其结构为：职工号，int;姓名，char(8);性别，char(2);出生日期，datetime;党员否char(2);参加工作，datetime;部门号，into其中“职 工号”为主键。并用insert语句在worker表中输入如下记录：职工号姓名性别出生日期党员否参加工作部门号1孙华男01/03/52是10/10/701012陈明男05/08/45否01/01/651023程西

6、一女06/10/80否07/10/021014孙天奇女03/10/65是07/10/871025刘夫文r男01/11/42否08/10/601026刘欣男10/08/52否01/07/701017余慧男12/04/80否07/10/021038张旗男11/10/80否07/10/02102usefactorygocreate table worker(职工号 int primary key,姓名 char(8),性别 char(2),出生日期 datetime,党员否char(2),参加工作 datetime,部门号int,);insert into workervalues(1,'孙

7、华','男',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/

8、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表中输入如下记录：部门号部门名10

9、1财务处102人事处103市场部usefactorygocreate table depart（部门号 int primary key,部门名char（10）;insert into departvalues（101,财务处'）,（102,'人事处'）,（103,市场部）（3）用create建立职工工资表salary,其结构为：职工号，int;姓名，char（8）;日期，datetime;工资，decimal（6,1）。其中，"职工号"和"日期"为主键。并用insert语句在salary 表中输入如下记录：职工号姓名日期工资1孙华

10、01/04/042陈明01/04/043程西01/04/044孙天奇01/04/045刘夫文01/04/046刘欣01/04/047余慧01/04/048张旗01/04/04usefactorygocreate table salary(职工号 int primary key,姓名 char(8), 日期 datetime,工资 decimal(6,1);insert into salaryvalues(1,'孙华',01/04/04,(2,陈明',01/04/04,(3,'程西',01/04/04,(4,孙天奇',01/04/04,(5,

11、9;刘夫文',01/04/04,(6,'刘欣',01/04/04,(7,'余慧',01/04/04,(8,'张旗',01/04/04,(4)建立 worker、depart和salary 3个表之间的关系。4、在建立的factory数据库上，完成如下各题的程序，要求以文本格式显示结果。(1)显示所有职工的年龄，并按职工号递增排序。USE factoryGOSELECT职工号，丫EAR (参加工作)-YEAR (出生日期)AS年齿餐FROM worker ORDER BY 职工号 ASC GO(2)求出各部门的党员人数。USE factor

12、yGOSELECT部门号，COUNT (党员否)AS党员人数 FROM 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)显示所有职工的职

13、工号、姓名、部门名和2004年2月份的工资，并按部门名的顺序排列。GOSELECT worker.职工号，worker.姓名，部门名，工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 ORDER BY部门名GO(6)显示各部门名和该部门的所有职工平均工资。USE factory GOSELECT 部门名，AVG (工资)AS 平均工资 FROM worker ,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=sala

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

15、39;THEN管理部门'WHEN部门名='人事处'THEN管理部门'WHEN部门名='市场部'THEN '市场部门'ENDFROM worker ,departWHERE worker.部门号=depart.部门号 GO(9)若存在职工号为10的职工，则显示其工作部门名称，否则显示相应提示信息。USE factory GOIF EXISTS(SELECT * FROM workerWHERE职工号=10)BEGINSELECT 部门名 FROM worker,departWHERE worker.部门号=depart.部门号 AN

16、D 职工号=10ENDELSEPRINT 不存在职工号为'的员工。'GO(10)求出男女职工的平均工资，若男职工平均工资高出女职工平均工资50%,则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职工的工资 高多了 ”的信息。USE factory GOSELECT 性别，AVG (工资)AS 平均工资 FROM worker ,salaryWHERE worker.职工号=salary.职工号GROUP BY 性别GODECLARE m decimalDECLARE f de

17、cimalSELECT m=AVG(工资)FROM worker ,salaryWHERE worker.职工号=salary.职工号AND性别='男'SELECT f=AVG (工资)FROM worker ,salaryWHERE worker.职工号=salary.职工号AND性别='女'IF(m-f)/f>=PRINT男职工比女职工的工资高多了。'ELSE IF(m/f>= AND m/f<=PRINT男职工跟女职工的工资差不多。'ELSEPRINT女职工比男职工的工资高多了。'GO5、在上机实验题4建立的fac

18、tory数据库上，完成如下各题(所有 SELECT语句的查询 结果以文本格式显示)。(1)删除factory数据库上各个表之间建立的关系。ALTER TABLE salaryDROP CONSTRAINT FK_salary_worker GOALTER TABLE workerDROP CONSTRAINT FK_worker_depart GO(2)显示各职工的工资记录和相应的工资小计。usefactory goselect职工号，姓名，工资 from salaryorder by职工号compute sum(工资)by 职工号 go(3)按性别和部门名的所有组合方式列出相应的平均工资。U

19、SE factory GOSELECT 性别，部门名，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 VALU

20、ES(5,'设备处')对worker和depart表进行全外连接显示职工的职工号、姓名和部门名，然后删除这两 个插入的记录。USE factoryGOINSERT 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.部门号

21、=depart.部门号 GO DELETE FROM worker WHERE职工号=20 GO DELETE FROM depart WHERE部门号=5GO(5)显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。 USE factoryGOSELECT worker.职工号，worker.姓名，部门名，salary.日期，工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资= (SELECT MAX (工资)FROM salary) GO(6)显示最高工

22、资的职工所在的部门名。USE factory GOSELECT 部门名 FROM worker ,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资= (SELECT MAX (工资)FROM salary) GO(7)显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。USE factory GOSELECT worker.职工号，worker.姓名 FROM worker,salaryWHERE worker.职工号=salary.职工号AND工资(SELECT AVG (工资)FROM

23、salary)GO(8)采用游标方式实现(6)小题的功能。USE factoryGODECLARE s_max CHAR (10)DECLARE st_cursor CURSORFOR SELECT 部门名 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资= (SELECT MAX (工资)FROM salary)OPEN st_cursorFETCH NEXT FROM st_cursor INTO s_maxPRINT部门名'WHILE FETCH_STATUS

24、 = 0BEGINPRINT 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_namevarchar(10)DECLARE st_cursor CURSORFOR SELECT worker.职工号，worker.姓名 FROM worker ,salaryWHERE worker.职工号=salary.职工号AND工资(SELECT AVG

25、(工资)FROM salary)OPEN st_cursorFETCH NEXT FROM st_cursor INTO c_no,s_namePRINT 职工号+''+'姓名'WHILE FETCH_STATUS = 0BEGINPRINT CAST (c_no AS varchar(10)+' '+s_nameFETCH NEXT FROM st_cursor INTO c_no,s_nameENDCLOSE st_cursorDEALLOCATE st_cursorGO(10)先显示worker表中的职工人数，开始一个事务，插入一个职工记录

26、，再显示worker 表中的职工人数，回滚该事务，最后显示worker表中的职工人数。USE factoryGOSELECT COUNT (*)AS 职工人数 FROM worker GO BEGIN TRANSACTION INSERT INTO workerVALUES (11,'小明','女','1955/03/08','1','1975/10/10','4')SAVE TRANSACTION TRAN1SELECT COUNT (*)AS 职工人数 FROM worker GO ROLLBA

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

28、用 T-SQL语句完成如下各题：(1)建立视图view1 ,查询所有职工的职工号、姓名、部门名和2004年2月份工资,并按部门名顺序排列。USE factoryGOCREATE VIEW view1 ASSELECT worker.职工号，worker.姓名，部门名，工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GO(2)建立视图view2,查询所有职工的职工号、姓名和平均工资。USE factoryGO CREATE VIEW view2 ASSELECT worker.职工

29、号，worker.姓名,AVG (工资)AS 平均工资 FROM worker ,salary GROUP BY worker.职工号，worker.姓名 GO(3)建立视图view3,查询各部门名和该部门的所有职工平均工资。USE factory GO CREATE VIEW view3 ASSELECT 部门名，AVG (工资)AS 平均工资 FROM worker ,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GROUP BY部门名GO(4)显示视图view3的定义。USE factoryGOEX

30、EC 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(3)删除worker表中的"E_mail字段USE factoryG

31、OALTER TABLE workerDROP COLUMN E_mailGO(4)将worker表中姓名为 陈明”的部门号改为 101”。USE factoryGOUPDATE workerSET部门号='101'WHERE姓名='陈明GO(5)删除工资表salary中姓名为陈明”的记录。USE factoryGODELETE FROM workerWHERE姓名='陈明GO(6)实施worker表的“性别”列默认值为“男”的约束。USE factory GO ALTER TABLE worker ADD CONSTRAINT default_xb DEFAU

32、LT 男'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(9)为worker表建立外键"部门号"，参考表 d

33、epart的“部门号”歹U。 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 性别='女' GOEXEC sp_bindrule 'sex','work

34、er.性别' GO(11)删除(1)小题所建立的约束。USE factory GO ALTER TABLE worker DROP CONSTRAINT default_xb GO(12)删除(2)小题所建立的约束。USE factoryGOALTER TABLE salaryDROP CONSTRAINT check_gzGO(12)删除(3)小题所建立的约束。USE factoryGOALTER TABLE departDROP CONSTRAINT unique_bmhGO(14)删除(4)小题所建立的约束。USE factoryGOALTER TABLE workerDROP

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

36、departnumber int AS BEGININSERT INTO workerVALUES (number,name,gender,birthday,party,starttime,departnumber) ENDGO(2)创建一个存储过程 Delworker删除worker表中指定职工号的记录。USE factoryGOCREATE PROCEDURE Delworker number int ASBEGIN DELETE FROM worker WHERE 职工号=number END GO3 3)显示存储过程 Delworker。USE factory GO EXEC sp_h

37、elptext Delworker GO (4)删除存储过程 Addworker 和 Delworker。 USE factory GODROP PROCEDURE Addworker ,Delworker GO 10、在factory数据库上，使用 T-SQL语句完成如下各题： (1)在表depart上创建一个触发器 depart_update,当更改部门号时同步更改worker表中对应的部门号。USE factory GOCREATE TRIGGER depart_update ON depart FOR UPDATE AS IF UPDATE (部门号) BEGINDECLARE num

38、ber1 int,number2 intSELECT number1=deleted.部门号，number2 =inserted.部门号FROM deleted,inserted UPDATE worker SET 部门号=number1 WHERE 部门号=number2 ENDGO(2)在表worker上创建一个触发器 worker_delete ,当删除职工记录时同步删除salary表中对应职工的工资记录。USE factory GOCREATE TRIGGER worker_delete ON worker FOR DELETEAS BEGINDELETE FROM salary WH

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

40、EATE LOGIN XYZWITH PASSWORD ='123',DEFAULT_DATABASE = factory GOEXEC 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权限。跳辘!此普蒙锄用士【i）nu

41、m鬃J.架构db_d 电叮 dd vr MdsrIbo* id。 ndbKep 01 LSei vtrnutsstun'si siitenjdp.已自用（hint旅户，1*好艇昌邑成员驰门）fiv心四icce：£1Jninit_t icjn-pr.psrat dtJ.L_4白!:工tLditwri tor一 n血性自I-J db_dtnyj«t tr ”加曲,denydativriterWT»4rd_securi tya-ln i n / public口ns12、使用SQL Server管理控制器对factory数据库执行完全备份（备份到H:DBFback

42、up1 文件中）和恢复操作。獴度-fart门ryJ.二 *心箫助琪醺熊励锂醐里热曲离二仅豆赳膏的仍Microsoft SQL Server Management Siudio勖鱼划时间.在Q）；目标就前F1的11典“口 i .疗mjV：E璃同谑盘口13、使用SQL Server管理控制器生成数据库factory的脚本文件，并显示该文件的内容。USE masterGO/* Object: Database factory Script Date: 12/23/2015 12:51:34 */CREATE DATABASE factory ON PRIMARY(NAME = N'facto

43、ry' , 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 )GOALTER DATABASE factory SET COMPATIBILITY_LEVEL = 100 GOIF (1 = FULLTEXTSERVICEPROPERTY ('IsFullTextInstalled') beginEXEC factory .dbo .sp_fu