factory数据库考核

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

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

3、用权限。3）各主管的任务是管理部门、职工、工资信息，即时更新、登记、发布信 息，普通职工可以查询、核对自己的基本信息和工资信息。整个系统模块划分如下图：系统管理：1）用户登录：普通用户登录：只可查询信息，不可插入、修改、删除；主管登录：可插入、修改、查询、删除信息；2）修改密码：凡是该系统的用户都可以修改自己的密码；3）退出系统信息管理：1）部门信息管理：主管可以插入，删除，更改部门信息；2）员工信息管理：主管可以插入，删除，更改员工的基本信息和薪资信息;信息查询：普通员工可以查询自己的基本信息和薪资信息；、E-R 图图应交祝费姓名员工编号藝资信息员工信慝考勤出生曰期st脈号管理惩罚金颠部门信

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

5、ry,FILENAME 二'H:DBFfactory.mdf,size=3mb,filegrowth=5mb)log on(n ame=factory_log,FILENAME 二'H:DBFfactory_log.ldf ,size=1mb,filegrowth=1mb)3、在上机面所创建的数据库factory中，完成如下各题操作。(1 )用create语句建立职工表 worker，其结构为：职工号，int ； , char(8)；性别，char(2)； 出生日期，datetime；党员否char(2);参加工作，datetime;部门号，int。其中"职工号” 为

6、主键。并用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/02102usefactorygocreate table worker(职工号 int primary key,char(8),性别

7、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,'程西','女',06/10/80,'否',07/10/02,101),(4,'天奇','女',03/1

8、0/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语句建立部门表d

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

10、； 工资，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.0usefactorygocreate table salary(职工号 int primary key,char(8),日期 datetime, 工资 decimal(6,1);inser

11、t 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),亿'余慧',01/04/04,725.0),(8,'旗 ',01/04/04,728.0)(4) 建立 worker、depart和salary

12、 3个表之间的关系。4、在建立的factory数据库上，完成如下各题的程序，要求以文本格式显示结果。(1) 显示所有职工的年龄，并按职工号递增排序。USE factoryGOSELECT 职工号，丫EAR (参加工作)-YEAR (岀生日期)AS 年龄 FROM worker ORDER BY 职工号 ASCGO(2) 求出各部门的党员人数。USE factoryGOSELECT部门号，COUNT(党员否)AS党员人数 FROM workerGROUP BY部门号GO(3) 显示所有职工的和 2004年1月份的工资。USE factoryGOSELECT worker.,工资 FROM wor

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

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

15、worker.部门号=depart.部门号 AND worker.职工号=salary.职工号GROUP BY部门名HAVING AVG（工资）1200GO（8）显示所有职工的职工号、和部门类型，其中财务部和人事部属于管理部门，市场 部属于市场部门。USE factoryGOSELECT职工号”部门类型=CASEWHEN 部门名='财务处'THEN '管理部门'WHEN 部门名='人事处'THEN '管理部门'WHEN 部门名='市场部'THEN '市场部门'ENDFROM worker ,depa

16、rtWHERE worker.部门号=depart.部门号GO（9）若存在职工号为10的职工，则显示其工作部门名称，否则显示相应提示信息。USE factoryGOIF EXISTS（SELECT * FROM workerWHERE职工号=10）BEGINSELECT 部门名 FROM worker,departWHERE worker.部门号=depart.部门号 AND 职工号=10ENDELSEPRINT'不存在职工号为的员工。'GO(10) 求出男女职工的平均工资，若男职工平均工资高出女职工平均工资50%，则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与

17、女职工平均工资比率在1.50.8之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职 工的工资高多了”的信息。USE factoryGOSELECT 性别,AVG (工资)AS 平均工资 FROM worker,salaryWHERE worker.职工号=salary.职工号GROUP BY 性另ijGODECLARE m decimalDECLARE f decimalSELECT m=AVG(工资)FROM worker ,salaryWHERE worker.职工号=salary.职工号 AND 性别='男'SELECT f=AVG (工资)FROM

18、 worker ,salaryWHERE worker.职工号=salary.职工号 AND 性别='女'IF(m-f)/f>=0.5)PRINT 男职工比女职工的工资高多了。'ELSE IF(m/f>=0.8 AND m/f<=1.5)PRINT '男职工跟女职工的工资差不多。'ELSEPRINT '女职工比男职工的工资高多了。'GO5、在上机实验题4建立的factory数据库上，完成如下各题(所有SELECT语句的查询 结果以文本格式显示)。(1) 删除factory数据库上各个表之间建立的关系。ALTER TABL

19、E salaryDROP CONSTRAINT FK_salary_workerGOALTER TABLE workerDROP CONSTRAINT FK_worker_departGO(2) 显示各职工的工资记录和相应的工资小计。usefactorygoselect 职工号”工资 from salaryorder by职工号compute sum(工资)by 职工号go(3) 按性别和部门名的所有组合方式列出相应的平均工资。USE factoryGOSELECT 性别,部门名，AVG（工资）AS 平均工资 FROM worker ,depart,salaryWHERE worker.部门号

20、=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 fact

21、oryGOINSERT INTO worker VALUES （'20','立','55/03/08',1,'75/10/10',4）INSERT INTO depart VALUES （'5','设备处'）GOSELECT职工号”部门名 FROM workerFULL OUTER JOIN depart ON worker.部门号=depart.部门号GODELETE FROM workerWHERE职工号=20GODELETE FROM departWHERE部门号=5GO（5）显示最高工资的职

22、工的职工号、部门名、工资发放日期和工资。USE factoryGOSELECT worker.职工号,worker.,部门名,salary.日期，工资 FROM worker ,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND 工资= （SELECT MAX （工资）FROM salary）GO（6）显示最高工资的职工所在的部门名。USE factoryGOSELECT 部门名 FROM worker, depart, salaryWHERE worker.部门号=depart.部门号 AND wor

23、ker.职工号=salary.职工号 AND 工资= （SELECT MAX （工资）FROM salary）GO（7）显示所有平均工资低于全部职工平均工资的职工的职工号和。USE factoryGOSELECT worker.职工号，worker.FROM worker,salaryWHERE worker.职工号=salary.职工号 AND 工资(SELECT AVG (工资)FROM salary)GO(8) 采用游标方式实现(6)小题的功能。USE factoryGODECLARE s_max CHAR (10)DECLARE st_cursor CURSORFOR SELECT 部

24、门名 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 = 0BEGINPRINT CAST (s_max AS char(10)FETCH NEXT FROM st_cursor INTO s_maxENDCLOSE st_cursorDEALLOCA

25、TE st_cursorGO(9) 采用游标方式实现(7)小题的功能。USE factoryGODECLARE c_no int,s_name varchar(10)DECLARE st_cursor CURSORFOR SELECT worker.职工号,worker. FROM worker ,salaryWHERE worker.职工号=salary.职工号 AND 工资(SELECT AVG (工资)FROM salary)OPEN st_cursorFETCH NEXT FROM st_cursor INTO c_no,s_namePRINT 职工号'+''+

26、''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表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。USE factoryGOSELECT COUNT (*)AS 职工人数 FROM workerGOBEG

27、IN TRANSACTIONINSERT INTO workerVALUES (11,'小明','女','1955/03/08','1','1975/10/10',4)SAVE TRANSACTION TRAN1SELECT COUNT (*)AS 职工人数 FROM workerGOROLLBACK TRANSACTIONSELECT COUNT (*)AS 职工人数 FROM workerGO6、 在factory数据库上，使用 T-SQL语句完成如下各题：(1 )在worker表中的"部门号”列上创

28、建一个非聚集索弓I;若该索引已存在，则删除 后重建。CREATE NONCLUSTERED INDEX bmhndex ON worker (部门号)GO(2) 在salary表的“职工号”和“日期”列创建聚集索弓I,并且强制唯一性。CREATE UNIQUE CLUSTERED INDEX zgh_rq_index ON salary(职工号，日期)GO7、在factory数据库上，使用 T-SQL语句完成如下各题：(1 )建立视图view1，查询所有职工的职工号、部门名和2004年2月份工资，并按部门名顺序排列。USE factoryGOCREATE VIEW view1ASSELECT

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

30、toryGOCREATE VIEW view3ASSELECT 部门名，AVG(工资)AS 平均工资 FROM worker ,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号GROUP BY部门名GO(4) 显示视图view3的定义。USE factoryGOEXEC sp_help view3GO8、在factory数据库上，使用 T-SQL语句完成如下各题：(1) 修改 worker表的结构，添加 “E_mail varchar(20)"字段。USE factoryGOALTER TABLE w

31、orkerADD E_mail varchar(20)GO(2) 将上例中worker表中“E_maiI字段的数据类型改为varchar(30)USE factoryGOALTER TABLE workerALTER COLUMN E_mail varchar(30)GO(3) 删除worker表中的“E_mail字段USE factoryGOALTER TABLE workerDROP COLUMN E_mailGO(4) 将worker表中为 明”的部门号改为101 ”USE factoryGOUPDATE workerSET部门号='101'WHERE ='明&#

32、39;GO(5) 删除工资表salary中为 明”的记录。USE factoryGODELETE FROM workerWHERE ='明'GO(6) 实施worker表的“性别”列默认值为“男”的约束。USE factoryGOALTER TABLE workerADD CONSTRAINT default_xbDEFAULT 男FOR 性别GO(7) 实施salary表的“工资”列值限定在09999的约束。USE factoryGOALTER TABLE salaryADD CONSTRAINT check_gzCHECK (工资 >=0 AND 工资 <=99

33、99)GO(8) 实施depart表的“部门号”列值唯一的非聚集索引的约束。USE factoryGOALTER TABLE departADD CONSTRAINT unique_bmhUNIQUE NONCLUSTERED (部门号)GO(9) 为worker表建立外键“部门号”，参考表 depart的“部门号”列。USE factoryGOALTER TABLE workerADD CONSTRAINT wj_worker_departFOREIGN KEY (部门号)REFERENCES depart(部门号)GO(10) 建立一个规则sex：性别='男'OR性别=&#

34、39;女'，将其绑定到 worker表的“性别”列 上。USE factoryGOCREATE RULE sexAS性别=男'OR性别=女'GOEXEC sp_bindrule 'sex','worker.性别'GO(11) 删除(1)小题所建立的约束。USE factoryGOALTER TABLE workerDROP CONSTRAINT default_xbGO(12) 删除(2)小题所建立的约束。USE factoryGOALTER TABLE salaryDROP CONSTRAINT check_gzGO(12)删除(3)小

35、题所建立的约束。USE factoryGOALTER TABLE departDROP CONSTRAINT unique_bmhGO(14) 删除(4)小题所建立的约束。USE factoryGOALTER TABLE workerDROP CONSTRAINT wj_worker_departGO(15) 解除(5)小题所建立的绑定并删除规则sex。EXEC sp_unbindrule 'worker.性别'GODROP RULE sexGO9、在的factory数据库上，使用 T-SQL语句完成如下各题：(1)创建一个为 worker表添加职工记录的存储过程Addwork

36、er。CREATE PROCEDURE Addworkernumber int,n ame char(8),gen der char(2),birthday datetime,party char(2),starttime datetime,depart number intASBEGININSERT INTO workerVALUES (number,name,gender,birthday ,party,starttime ,departnumber)ENDGO(2)创建一个存储过程Delworker删除worker表中指定职工号的记录。USE factoryGOCREATE PROCED

37、URE Delworkernumber intASBEGINDELETE FROM workerWHERE 职工号=numberENDGO(3) 显示存储过程 Delworker。USE factoryGOEXEC sp_helptext DelworkerGO(4) 删除存储过程 Addworker 和 Delworker。USE factoryGODROP PROCEDURE Addworker ,DelworkerGO10、在factory数据库上，使用 T-SQL语句完成如下各题：(1)在表depart上创建一个触发器 depart_update，当更改部门号时同步更改worker表中

38、对应的部门号。USE factoryGOCREATE TRIGGER depart_update ON departFOR UPDATEASIF UPDATE (部门号)BEGINDECLARE number1 int,number2 intSELECT number1=deleted.部门号,number2 = inserted.部门号FROM deleted,insertedUPDATE workerSET 部门号=number1WHERE 部门号=number2ENDGO(2)在表worker上创建一个触发器 worker_delete，当删除职工记录时同步删除salary表中对应职工的

39、工资记录。USE factoryGOCREATE TRIGGER worker_delete ON workerFOR DELETEASBEGINDELETE FROM salaryWHERE职工号=（SELECT 职工号 FROM deleted）ENDGO（3）删除触发器 depart_update。DROP TRIGGER depart_updateGO（4）删除触发器 worker_delete。DROP TRIGGER worker_deleteGO11、在SQL Server管理控制器中完成如下操作：（1）创建一个登录账号 XYZ/123 （其默认的工作数据库为factory ;其

40、“服务器角色”设置为sysadmin;将“映射到此登录名的用户”设置为Factory，使其具有public权限；设置安全对象LCB-PC服务器具有Connect SQL权限）。CREATE LOGIN XYZWITH PASSWORD ='123',DEFAULT_DATABASE = factoryGOEXEC sp_addsrvrolemember 'XYZ' ,'sysadmin'GOCREATE USER FactoryFOR LOGIN XYZGOGRANT public TO FactoryGOGRANT Connect SQL TO

41、 LCB_PCGO（2）修改（1）中为factory数据库创建的用户账号XYZ的属性，使XYZ登录账号对factory数据库具有db_owner权限。的用户加：前孵用户St认架构0_fitterU23db_nyditw*a'i6rr ihkl.hha5twFtctorjFJb«nad«lnr>*dbKcpui tSei ?ft肚I rt5就肛叮汕pDEnSTUSmhimtsrkenjAnIrtlB己朗(hint #户：ficiwyM聃磅詆卽切i f曲唧(Lt_t icjn-pr.psrat orJ.L_ 哉t 址* hrtorn if血込匕LJ db_dtny

42、j«t v «ti«rdsecuri tya-ln i n / fuKlic ns12、使用SQL Server管理控制器对factory数据库执行完全备份（备份到H:DBFbackup1文件中）和恢复操作。备吩墟片-fxtcry計杏毛帏帝舀n訓辭加A加;0瞒鮒1如珂z购1ssnai).内歆cZ欣复制前Microsoft SQL Server Management Siudio总数IB曲低tfr书裁的少界缶”鮭1替饌：M搁1菇：°天!1' I »13、使用SQL Server管理控制器生成数据库 factory的脚本文件H:DBFfac

43、tory.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 =UNLIMITED , 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 = 100 GOIF (1 = FULLTEXTSE