factory数据库考核

《factory数据库考核》由会员分享，可在线阅读，更多相关《factory数据库考核（21页珍藏版）》请在装配图网上搜索。

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,filegrowth=

5、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表中输入如下记录：职工号姓名性别岀生日期党员否参加工作部门号1孙华男01/03/52

6、是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),参加工作datetime,部门号i

7、nt,);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),(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)

8、 用create语句建立部门表 depart，其结构为：部门号，int ;部门名，char(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) ; 日 期，datetim

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

10、ert 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、depart和salary 3个表之间的关系。Tl J富工主 4、在建立的factory数据库上，完成如下各题的程序，要求以文本格式显示结果。(1) 显示所有职工的年

11、龄，并按职工号递增排序。USE factoryGOSELECT职工号，丫EAR(参加工作)-YEAR(岀生日期)AS 年龄FROM workerORDER BY职工号 ASCGO(2) 求出各部门的党员人数。USE factoryGOSELECT部门号，COUNT党员否)AS 党员人数 FROM workerGROUP BY部门号GO(3) 显示所有职工的姓名和2004年1月份的工资。USE factoryGOSELECT worker.姓名，工资 FROM worker,salaryWHERE worker.职工号=salary.职工号GO(4) 显示所有职工的职工号、姓名和平均工资。USE

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

13、各部门名和该部门的所有职工平均工资。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 worker. 部门号 =depart. 部门号 AND worker. 职工号 =sala

14、ry. 职工号GROUP BY部门名HAVING AVG工资）1200GO（8）显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属于管理部门， 市场部属于市场部门。USE factoryGOSELECT职工号，姓名，部门类型=CASEWHEN部门名=财务处THEN 管理部门WHEN部门名=人事处THEN 管理部门WHEN部门名=市场部THEN 市场部门ENDFROM worker,departWHERE worker. 部门号 =depart. 部门号GO（9）若存在职工号为 10 的职工，则显示其工作部门名称，否则显示相应提示信息。USE factoryGOIF EXISTS（SEL

15、ECT * FROM workerWHERE职工号=10）BEGINSELECT 部门名 FROM worker,departWHERE worker. 部门号 =depart. 部门号 AND 职工号 =10ENDELSEPRINT 不存在职工号为 的员工。 GO（ 10）求出男女职工的平均工资，若男职工平均工资高出女职工平均工资50%，则显示“男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在1.50.8 之间，则显示“男职工跟女职工的工资差不多”的信息；否则显示“女职工比男职工的 工资高多了”的信息。USE factoryGOSELECT性另ij ,AVG（工资）

16、AS 平均工资 FROM worker,salaryWHERE worker. 职工号 =salary. 职工号GROUP BY性另【JGODECLARE m decimalDECLARE f decimalSELECT m=AV工资）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.

17、8 AND m/f=1.5）PRINT 男职工跟女职工的工资差不多。ELSEPRINT 女职工比男职工的工资高多了。GO5、在上机实验题4建立的factory数据库上，完成如下各题（所有SELECT语句的查询 结果以文本格式显示）。（ 1 ）删除 factory 数据库上各个表之间建立的关系。ALTER TABLE salaryDROP CONSTRAINT FK_salary_workerGOALTER TABLE workerDROP CONSTRAINT FK_worker_departGO（ 2）显示各职工的工资记录和相应的工资小计。use factorygoselect 职工号 ,

18、姓名 , 工资 from salaryorder by 职工号compute sum（ 工资 ） by 职工号go（ 3）按性另和部门名的所有组合方式列出相应的平均工资。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,

19、1,75/10/10,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职工号，姓名，部门名FROM workerFULL OUTER JOIN depart ON worker. 部门号=d

20、epart.部门号GODELETE FROM workerWHERE职工号=20GODELETE FROM departWHERE部门号=5GO（5）显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。USE factoryGOSELECT worker. 职工号 ,worker. 姓名 , 部门名 ,salary. 日期 , 工资 FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND工资= （SELECT MAX工资）FROM salary）GO（6）显示最高工资的职工所

21、在的部门名。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.职工号 AND工资(SELECT AVG(工资)FROM salary)GO

22、( 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 = 0BE

23、GINPRINT 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,salaryWHERE worker. 职工号 =salary. 职工号 AND 工资 =0 AND 工

24、资=9999）GO（ 8）实施 depart 表的“部门号”列值唯一的非聚集索引的约束。USE factoryGOALTER 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 性

25、别=女，将其绑定到 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）小题所建立的约束。USE factoryGOALT

26、ER 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 PROCEDURE Addwo

27、rker 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 Delworkernumber int

28、ASBEGINDELETE 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 表中对应的部门号。USE f

29、actoryGOCREATE 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 表中对应职工的工资

30、记录。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 （其默认的工作数据库为fact

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

32、ctory 数据库创建的用户账号XYZ的属性，使XYZ登录账号对factory 数据库具有 db_owner 权限。_w 联附$111愷录名的用户加：用户StUSSft0U23db_dflnyditw*fi6fr 1LkhkhKt9trFtctorjFJb诚订n idbKcpji tSei ?frhurtSrvtlipDBnHIEstunsisii怙 npdbnTutDB己启用Hil 縣户：ictry岬弟色詆身也皿加血y(Lt_t icjn-pr.psrat orJ.L_ 鮎t 址* hrtorn if血込匕LJ db_dtnyjt v tirdsecuri tya-ln i n / fuKl

33、ic 口何12、使用SQL Server管理控制器对factory 数据库执行完全备份（备份到H:DBFbackup1文件中）和恢复操作。J.心口 15备吩城片-fxtery渤刚.Microsoft SQL Server Management Studioa症数IB曲TKtrr胡劳伯少响”Z欣复制前帥屋曲R搁.在;菲資能I;繼1肛妙liHi咖内歆!1 I 13、使用SQLServer管理控制器生成数据库factory 的脚本文件H:DBFfactory.sql并显示该文件的内容。USE masterGO/* Object: Database factory Script Date: 12/23

34、/2015 12:51:34*/CREATE DATABASE factory ON PRIMARY(NAME= Nfactory, FILENAME= NG:DBFfactory.mdf, SIZE = 3072KB , MAXSIZE=UNLIMITED, FILEGROWTH = 5120KB )LOG ON(NAME= Nfactory_log, FILENAME= NG:DBFfactory_log.ldf, SIZE = 1024KB ,MAXSIZE = 2048GB , FILEGROWTH = 1024KB )GOALTER DATABASE factory SET COMP

35、ATIBILITY_LEVEL = 100 GOIF (1 = FULLTEXTSERVICEPROPERTY(IsFullTextInstalled) beginEXEC factory.dbo.sp_fulltext_database action = enable endGOALTER DATABASE factory SET ANSI_NULL_DEFAULT OFFGOALTER DATABASE factory SET ANSI_NULLS OFFGOALTER DATABASE factory SET ANSI_PADDING OFFGOALTER DATABASE factor

36、y SET ANSI_WARNINGS OFFGOALTER DATABASE factory SET ARITHABORT OFFGOALTER DATABASE factory SET AUTO_CLOSE OFFGOALTER DATABASE factory SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE factory SET AUTO_SHRINK OFFGOALTER DATABASE factory SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE factory SET CURSOR_C

37、LOSE_ON_COMMIT OFF GOALTER DATABASE factory SET CURSOR_DEFAULT GLOBALGOALTER DATABASE factory SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE factory SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE factory SET QUOTED_IDENTIFIER OFF GOALTER DATABASE factory SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE fa

38、ctory SET DISABLE_BROKER GOALTER DATABASE factory SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE factory SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE factory SET TRUSTWORTHY OFFGOALTER DATABASE factory SET ALLOW_SNAPSHOT_ISOLATION OFFGOALTER DATABASE factory SET PARAMETERIZATION SIM

39、PLEGOALTER DATABASE factory SET READ_COMMITTED_SNAPSHOT OFFGOALTER DATABASE factory SET HONOR_BROKER_PRIORITY OFFGOALTER DATABASE factory SET READ_WRITEGOALTER DATABASE factory SET RECOVERY FULLGOALTER DATABASE factory SET MULTI_USERGOALTER DATABASE factory SET PAGE_VERIFY CHECKSUMGOALTER DATABASE factory SET DB_CHAINING OFF GO感谢下载!欢迎您的下载，资料仅供参考