factory数据库考核

1、Jiangsu university of sclerrce and technologyDatabase programming 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 图三、关系模型：1. 部门信息（部门编号，部门名称）2. 员工信息（员工编号，姓名，性别，

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

5、owth=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。 其中"职工号”为主键。并用 insert语句在worker表中输入如下记录：职工号姓名性别岀生日期党员否参加工

6、作部门号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刘夫文男I01/11/42否08/10/601026刘欣男10/08/52否01/07/701017余慧男：12/04/80否07/10/021038张旗男11/10/80否07/10/02102use factorygocreate table worker(职工号 int primary key,姓名 char(8),性别 char(2),岀生日期datetime,党员否char(2),参

7、加工作datetime,部门号int,);in sert 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/10/65,'是',07/10/87,102)

8、,(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 ;部门名，cha

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

10、cimal(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 factorygocreate table salary(职工号 int primary key, 姓名 char(8),日期 datetime,工资 decimal(6,1);

11、in sert 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,'余慧',01/04/04,725.0),(8,'张旗',01/04/04,728.0)(4) 建立 worker

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

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

14、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）显示所有平均工资高于 1200 的部门名和对应的平均工资。USE factoryGOSELECT部门名，AVG（工资）A

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

16、部'THEN '市场部门'ENDFROM worker,departWHERE worker. 部门号 =depart. 部门号GO（9）若存在职工号为 10 的职工，则显示其工作部门名称，否则显示相应提示信息。USE factoryGOIF EXISTS（SELECT * FROM workerWHERE职工号=10）BEGINSELECT 部门名 FROM worker,departWHERE worker. 部门号 =depart. 部门号 AND 职工号 =10ENDELSEPRINT' 不存在职工号为 的'员工。 'GO（ 10）求出男

17、女职工的平均工资，若男职工平均工资高出女职工平均工资50%，则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在1.50.8 之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职工的 工资高多了”的信息。USE factoryGOSELECT性另ij ,AVG（工资）AS 平均工资 FROM worker,salaryWHERE worker. 职工号 =salary. 职工号GROUP BY性另【JGODECLARE m decimalDECLARE f decimalSELECT m=AV工资）FROM worker,salaryWHERE

18、 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 ' 男职工跟女职工的工资差不多。'ELSEPRINT ' 女职工比男职工的工资高多了。'GO5、在上机实验题4建立的fact

19、ory数据库上，完成如下各题（所有SELECT语句的查询 结果以文本格式显示）。（ 1 ）删除 factory 数据库上各个表之间建立的关系。ALTER TABLE salaryDROP CONSTRAINT FK_salary_workerGOALTER TABLE workerDROP CONSTRAINT FK_worker_departGO（ 2）显示各职工的工资记录和相应的工资小计。use factorygoselect 职工号 , 姓名 , 工资 from salaryorder by 职工号compute sum（ 工资 ） by 职工号go（ 3）按性另和部门名的所有组合方式列

20、出相应的平均工资。USE factoryGOSELECT性另ij ,部门名，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',4）在 depart 表中使用以下语句插入一个部门

21、记录：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职工号，姓名，部门名FROM workerFULL

22、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工资= （SELECT

23、 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.职工号=salary.职工号

24、AND工资<(SELECT AVG(工资)FROM 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

25、_maxPRINT ' 部门名 'WHILE FETCH_STATUS = 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_name varchar(10)DECLARE st_cursor CURSORFOR SELECT worker. 职工号 ,worker. 姓名 FROM worker,sal

26、aryWHERE worker. 职工号 =salary. 职工号 AND 工资 <(SELECT AVG(工资)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

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

28、'）SAVE 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 表的“职工号”和“日期”列创建聚集索引，并且强制唯一性。CRE

29、ATE UNIQUE CLUSTERED 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

30、.职工号GO（2）建立视图 view2 ，查询所有职工的职工号、姓名和平均工资。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.部门

31、号=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 workerADD E_mail varchar（20）GO（ 2）将上例中 worker 表中 “E_mail ”字段的数据类型改为 varchar（30）USE factoryGOAL

32、TER 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生名='陈明'GO（ 5）删除工资表 salary 中姓名为 “陈明 ”的记录。USE factoryGODELETE FROM workerWHE

33、RE生名='陈明'GO（ 6）实施 worker 表的“性别”列默认值为“男”的约束。USE factoryGOALTER TABLE workerADD CONSTRAINT default_xbDEFAULT '男' FOR 性别GO（7）实施salary表的“工资”列值限定在09999的约束。USE factoryGOALTER TABLE salaryADD CONSTRAINT check_gzCHECK （工资 >=0 AND 工资<=9999）GO（ 8）实施 depart 表的“部门号”列值唯一的非聚集索引的约束。USE factor

34、yGOALTER TABLE departADD CONSTRAINT unique_bmhUNIQUE NONCLUSTERE部（门号）GO（ 9）为 worker 表建立外键“部门号”，参考表 depart 的“部门号”列。USE factoryGOALTER TABLE workerADD CONSTRAINT wj_worker_departFOREIGN KEY （部门号） REFERENCES depart（ 部门号 ）GO（10） 建立一个规则 sex : 性别='男'OR 性别='女'，将其绑定到 worker表的“性 别”列上。USE fact

35、oryGOCREATE 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）小题所建立的约束。USE factor

36、yGOALTER 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 表添加职工记录的存储过程 Addworker 。CREATE

37、 PROCEDURE Addworker number int,name char（8）,gender char（2）,birthday datetime,party char（2）,starttime datetime,departnumber intASBEGININSERT INTO workerVALUES（number,name,gender,birthday,party,starttime,departnumber） ENDGO（ 2）创建一个存储过程 Delworker 删除 worker 表中指定职工号的记录。 USE factoryGOCREATE PROCEDURE Del

38、workernumber 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 ，当更改部门号时同步更改 worke

39、r 表中对应的部门号。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 ，当删除职工记录时同步删除

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

41、 （其默认的工作数据库为factory ;其“服务器角色”设置为 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 Fact

42、oryGOGRANT Connect SQL TO LCB_PCGO（2）修改（1）中为factory 数据库创建的用户账号XYZ的属性，使XYZ登录账号对factory 数据库具有 db_owner 权限。_w 联附$111愷录名的用户加：用户StUSSft0U23db_dflnyditw*fi6fr 1LkhkhKt9t«rFtctorjFJb«诚订n idbKcp'ji tSei ?frhurtSrvtl'ipDBnHIEstunsisii怙 npdbnTutDB己启用Hil 縣户：£ict«ry岬弟色詆身也皿加血y(Lt_t ic

43、jn-pr.psrat orJ.L_ 鮎t 址* hrtorn if血込匕LJ db_dtnyj«t v «ti«rdsecuri tya-ln i n / fuKlic 口何12、使用SQL Server管理控制器对factory 数据库执行完全备份（备份到H:DBFbackup1文件中）和恢复操作。J.心口 15备吩城片-fxtery渤刚.Microsoft SQL Server Management Studioa症数IB曲TKtrr胡劳伯少响”Z欣复制前帥屋曲R搁.在;菲資能I;繼1肛妙liHi咖内歆!1' I »13、使用SQLServ

44、er管理控制器生成数据库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=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 = FULLTEXTSERVICEPROPERTY('IsFullTextInstal