factory数据库考核

Database programming with RDBMS 课程设计课程设计 职工工资管理统 指导老师 郭小芳指导老师 郭小芳 学号 学号 14419041031441904103 姓名 姓名 高嘉威高嘉威 Database Database programmingprogramming withwith RDBMS RDBMS 考查命题考查命题 1 根据周围的实际情况 自选一个小型的数据库应用项目 并深入到应用项目中调研 进行分析和设计 例如可选择职工工资管理系统 人事管理系统 教材管理系统和小型超 市商品管理系统和图书管理系统等 要求写出数据库设计报告 一 功能需求一 功能需求 系统的基本流程是 用户登入 主界面 选择各项子系统 模模块块设设计计和和功功能能说说明明 一一 权权限限说说明明 对于不同的用户可以对其赋予不同的权限级别 部门主管拥有最高的权限 能够 对员工的资料进行增删 对部门的信息进行增删 1 用户登入 填入用户名称 填写正确的密码 选择用户类型 2 用户登入后 可以对自己的密码进行修改 3 用户可以查看个人资料 以下是各部门主管所特有的权限 其他员工均没有该权限 不能实现一下的 操作 4 部门主管可以对员工信息进行浏览 查询 编辑 插入 删除 5 部门主管可以对该公司的部门信息进行浏览 查询 编辑 插入 删除 二二 系系统统的的要要求求 职工工资管理系统开发时 应该满足以下几点 1 方便 容易操作 快捷的原则 2 对于不同的用户 应该有不同的使用权限 3 各主管的任务是管理部门 职工 工资信息 即时更新 登记 发布信 息 普通职工可以查询 核对自己的基本信息和工资信息 整整个个系系统统模模块块划划分分如如下下图图 职工工资管理系统 系统管理信息管理信息查询 用 户 登 入 退 出 系 统 修 改 密 码 部门信息 管理 员工信息 管理 基本信息 管理 薪资信息 管理 基本信 息查询 薪资信 息查询 系系统统管管理理 1 用户登录 普通用户登录 只可查询信息 不可插入 修改 删除 主管登录 可插入 修改 查询 删除信息 2 修改密码 凡是该系统的用户都可以修改自己的密码 3 退出系统 信信息息管管理理 1 部门信息管理 主管可以插入 删除 更改部门信息 2 员工信息管理 主管可以插入 删除 更改员工的基本信息和薪资信息 信信息息查查询询 普通员工可以查询自己的基本信息和薪资信息 2 E R 图图 3 关系模型 关系模型 1 部门信息 部门编号 部门名称 2 员工信息 员工编号 姓名 性别 学历 出生日期 职务 部门编号 密码 3 薪资信息 账号 员工编号 基本工资 福利 缺勤时间及原因 惩罚金额 应缴 税 费 总薪资 2 如创建职工工资管理系统中 一个名称为 factory 的数据库 要求 1 将主数据库文件 factory mdf 放置在 H DBF 文件夹中 其文件大小自动增长为按 5 MB 增长 2 将事务日志文件 factory log ldf 放置在 H DBF 文件夹中 其文件大小自动增长 为按 1 MB 增长 create database factory on name factory FILENAME H DBF factory mdf size 3mb filegrowth 5mb log on name factory log FILENAME H DBF factory 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是10 10 70101 2陈明男05 08 45否01 01 65102 3程西女06 10 80否07 10 02101 4孙天奇女03 10 65是07 10 87102 5刘夫文男01 11 42否08 10 60102 6刘欣男10 08 52否01 07 70101 7余慧男12 04 80否07 10 02103 8张旗男11 10 80否07 10 02102 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 是 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 语句建立部门表 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 日期 datetime 工资 decimal 6 1 其中 职工号 和 日期 为主键 并用 insert 语句在 salary 表中输入如下记录 职工号姓名日期工资 1孙华01 04 041201 5 2陈明01 04 041350 6 3程西01 04 04750 8 4孙天奇01 04 04900 0 5刘夫文01 04 042006 8 6刘欣01 04 041250 0 7余慧01 04 04725 0 8张旗01 04 04728 0 use factory go create table salary 职工号 int primary key 姓名 char 8 日期 datetime 工资 decimal 6 1 insert into 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 04 728 0 4 建立 worker depart 和 salary 3 个表之间的关系 4 在建立的factory数据库上 完成如下各题的程序 要求以文本格式显示结果 1 显示所有职工的年龄 并按职工号递增排序 USE factory GO SELECT 职工号 YEAR 参加工作 YEAR 出生日期 AS 年龄 FROM worker ORDER BY 职工号 ASC GO 2 求出各部门的党员人数 USE factory GO SELECT 部门号 COUNT 党员否 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 职工号 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 平均工资 FROM 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 工资 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 EXISTS SELECT FROM worker WHERE 职工号 10 BEGIN SELECT 部门名 FROM worker depart WHERE worker 部门号 depart 部门号 AND 职工号 10 END ELSE PRINT 不存在职工号为 的员工 GO 10 求出男女职工的平均工资 若男职工平均工资高出女职工平均工资 50 则显 示 男职工比女职工的工资高多了 的信息 若男职工平均工资与女职工平均工资比率在 1 5 0 8 之间 则显示 男职工跟女职工的工资差不多 的信息 否则显示 女职工比男 职工的工资高多了 的信息 USE factory 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 性别 女 IF 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 CONSTRAINT 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 职工号 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 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 姓名 部门名 salary 日期 工资 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 工资 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 部门名 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 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 工资 0 AND 工资 9999 GO 8 实施 depart 表的 部门号 列值唯一的非聚集索引的约束 USE factory GO ALTER TABLE depart ADD CONSTRAINT unique bmh UNIQUE NONCLUSTERED 部门号 GO 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 删除 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 worker 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 char 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 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 TRIGGER 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 表中对应职工的工资记录 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 创建一个登录账号 XYZ 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 GO GRANT Connect SQL TO LCB PC GO 2 修改 1 中为 factory 数据库创建的用户账号 XYZ 的属性 使 XYZ 登录账号对 factory 数据库具有 db owner 权限 12 使用 SQL Server 管理控制器对 factory 数据库执行完全备份 备份到 H DBF backup1 文件中 和恢复操作 13 使用 SQL Server 管理控制器生成数据库 factory 的脚本文件 H DBF factory sql 并显示该文件的内容 USE master GO Object Database factory Script Date 12 23 2015 12 51 34 CREATE DATABASE factory ON PRIMARY NAME N factory FILENAME N G DBF factory mdf SIZE 3072KB MAXSIZE UNLIMITED FILEGROWTH 5120KB LOG ON NAME N factory log FILENAME N G DBF factory log ldf SIZE 1024KB MAXSIZE 2048GB FILEGROWTH 1024KB GO ALTER DATABASE factory SET COMPATIBILITY LEVEL 100 GO IF 1 FULLTEXTSERVICEPROPERTY IsFullTextInstalled begin EXEC factory dbo sp fulltext database action enable end GO ALTER DATABASE factory SET ANSI NULL DEFAULT OFF GO ALTER DATABASE factory SET ANSI NULLS OFF GO ALTER DATABASE factory SET ANSI PADDING OFF GO ALTER DATABASE factory SET ANSI WARNINGS OFF GO ALTER DATABASE factory SET ARITHABORT OFF GO ALTER DATABASE factory SET AUTO