SQL项目——大学生就业咨询系统

1、概述

1.1本设计的背景和意义

设计背景

随着我国高校教育的发展，大学生的数量急剧增加，大学生就业问题也日益凸显，已成为众多媒体和社会各界关注的热点和焦点问题，解决好这个问题，既能有效促进社会发展和社会稳定，也能真正把知识变成生产力，从而推动一个国家的软实力发展。

目前，企业招聘信息众多繁杂且不成系统，令人应接不暇。有效信息的提取匮乏，是导致大学生就业困难的因素之一。建立一个良好的就业咨询系统能够帮组我们有效的解决这个问题。

设计意义

党中央、国务院关于高校毕业生就业工作做的决策部署中，要求不断提升教育系统就业工作服务水平和数字化能力，促进高校毕业生更加充分更高质量就业。本设计旨在为大学生提供一个就业信息查询的线上平台，收录企业招聘的基本信息将其定时发布，实时更新用人单位的级别，同时统计分析历年的毕业需求信息，以便大学生检索公司人才需求信息。

1.2数据库设计开发工具

本设计采用的数据开发工具为SQL Server 2022

2、需求分析

2.1功能需求

大学毕业生生部分：

对用用人单位信息、需求信息进行查询、打印

管理员部分：

1. 可更改数据库所有内容，可用人单位的基本信息进行录入、修改、删除
2. 可维护毕业生专业信息
3. 可定时发布用人单位的毕业生需求信息
4. 可自动变更对用人单位的级别
5. 可统计分析历年的毕业需求信息
6. 可备份恢复数据

2.2数据关系结构

信息需求：

1. 用人单位表：单位编号，单位名称，单位级别，联系电话
2. 地区信息表：地区编号，地区名称
3. 单位所属地区信息表：单位编号，地区编号
4. 专业信息表：专业编号，专业名称
5. 需求信息表：单位编号，专业编号，招聘岗位，学历要求，需求人数，发布时间

安全性与完整性需求

数据信息表：

Company 用人单位表

字段名	字段类型	Not Null	完整性说明
单位编号	nchar(18)	✔	主键
单位名称	varchar(50)	✔
单位级别	varchar(50)	✔
联系电话	varchar(13)	✔

注：单位编号为统一社会信用代码，位数为18。

Eg.成都米友科技股份公司：510109000528224

Region 地区信息表

字段名	字段类型	Not Null	完整性说明
地区编号	varchar(6)	✔	主键
地区名称	varchar(50)	✔

注：地区编号位数为2~6。Eg.北京：11、南充市：6730、南充市顺庆区：511302

Belong 单位所属地区信息

字段名	字段类型	Not Null	完整性说明
单位编号	nchar(18)	✔	主键、外键
地区编号	nchar(6)	✔	主键、外键

Major 专业信息表

字段名	字段类型	Not Null	完整性说明
专业编号	varchar(7)	✔	主键
专业名称	varchar(50)	✔

注：专业编号位数在2~7之间不等。

Eg.教育学：04、中国语言文学类：0501、地理信息学：070504、金融学：020301K

Request 需求信息表

字段名	字段类型	Not Null	完整性说明
单位编号	nchar(18)	✔	主键、外键
专业编号	varchar(7)	✔	主键、外键
招聘岗位	varchar(50)	✔	主键
需求人数	int	✔
学历要求	varchar(50)	✔
发布时间	date	✔

1. 安全性要求：只有管理员及授予了权限的用户有权限对数据库的内容进行操作
2. 完整性需求
3. 实体完整性：单位编号、地区编号、专业编号分别为用人单位基本信息、地区信息、专业信息表的主码；单位所属地区信息表的主码是单位编号+地区编号；需求信息表的主码是“单位编号+专业编号+招聘岗位”
4. 参照完整性：单位所属地区信息表的单位编号、地区编号分别对应着用人单位基本信息表的单位编号、地区信息表的地区编号；需求信息表的单位编号、专业编号分别对应着用人单位基本信息表的单位编号、专业信息表的专业编号。
5. 用户定义的完整性：各表中的属性列均不为空值。

3、概念结构设计

概念结构设计是整个数据库设计的关键，它通过对用户需求进行综合、归纳、抽象，形成一个独立于具体DBMS的概念模型。

根据需求分析，分析得到该系统中存在以下实体：用人单位、专业信息、地区信息

3.1局部ER图

3.2完整ER图

4、逻辑结构设计

根据E-R图向关系模式的转换原则，一个实体型转换为一个关系模式，实体的属性就是关系的属性。

根据实体型间联系的转换原则如下：

1. 一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。
2. 一个1：n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。

一个m：n联系可以转换为一个独立的关系模式

4.1建立关系模式

用人单位（单位编号，单位名称，单位级别，联系电话）

地区（地区编号，地区名称）

专业信息（专业编号，专业名称）

属于（单位编号，地区编号）

需求（单位编号，专业编号，招聘岗位，学历要求，需求人数，发布时间）

注：一个专业的大学生在同一家公司可以应聘多个岗位。比方说，数字媒体技术在A公司可以应聘游戏建模也可以应聘游戏编程

4.2关系模式规范化处理

函数依赖分析

• 用人单位（单位编号，单位名称，单位级别，联系电话）

单位编号→单位名称

单位编号→单位级别

单位编号→联系电话

主码：单位编号   外码：无

• 地区（地区编号，地区名称）

地区编号→地区名称

主码：地区编号   外码：无

• 专业信息（专业编号，专业名称）

专业编号→专业名称

主码：专业编号   外码：无

• 需求（单位编号，专业编号，招聘岗位，学历要求，需求人数，发布时间）

（单位编号，专业编号，招聘岗位）→需求人数

（单位编号，专业编号，招聘岗位）→学历要求

（单位编号，专业编号，招聘岗位）→发布时间

主码：单位编号+专业编号+招聘岗位  外码：单位编号、专业编号

• 属于（单位编号，地区编号）

无函数依赖

主码：单位编号和地区编号的组合   外码：单位编号、地区编号

关系模式所满足的范式分析

上述5种关系模式均满足：

1. 关系中每个属性都不可再分（1NF）
2. 不存在非主属性对码的部分函数依赖（2NF）
3. 不存在非主属性对码的传递函数依赖（3NF）

即均满足第三范式

5、物理结构设计

5.1建立数据库

物理设计时为逻辑数据模型选取一个最合适应用环境的物理结构（包括存储结构和存取方法），数据库的物理结构通常分为两步：

(1)确定数据库的物理结构，在关系数据库中主要指存取方法和存储结构；

(2)对物理结构进行评价，评价的重点是时间和空间效率。

创建数据库语句为：

create database 大学生就业咨询系统

5.2数据表创建

create table Company

(单位编号 nchar(18) primary key,

单位名称 varchar(50) not null,

单位级别 varchar(50) not null,

联系电话 varchar(13) not null)



create table Region

(地区编号 varchar(6) primary key,

地区名称 varchar(50) not null)



create table Belong

(单位编号 nchar(18),

地区编号 varchar(6),

primary key(单位编号,地区编号),

foreign key (单位编号) references Company(单位编号),

foreign key (地区编号) references Region(地区编号))



create table Major

(专业编号 varchar(7) primary key,

专业名称 varchar(50) not null)



create table Request

(单位编号 nchar(18) ,

专业编号 varchar(7) ,

招聘岗位 varchar(50),

学历要求 varchar(50) not null,

需求人数 int not null,

发布时间 date not null,

primary key(单位编号,专业编号,招聘岗位),

foreign key (单位编号) references Company(单位编号),

foreign key (专业编号) references Major(号))

5.3数据视图创建

结合用户外模式，完成4种数据视图的设计与实现

create view V_myview

as

select c.单位编号 as Cno1,r1.单位编号 as Cno2,m.专业编号 as Mcno,r2.地区编号 as Rno1,b.单位编号 as Cno3,b.地区编号 as Rno2

from Belong b inner join Request r1 on b.单位编号=r1.单位编号

inner join Region r2 on b.地区编号=r2.地区编号

inner join Major m on r1.专业编号=m.专业编号

inner join Company c on B.单位编号 = c.单位编号 and r1.单位编号=c

5.4存储过程创建

create procedure pro_My01 @单位名称 varchar(50)

as

begin

select 单位名称,联系电话,地区名称,专业名称,学历要求,需求人数,招聘岗位,发布时间

from Belong b,Company c,Region x,Major m,Request y

where 单位名称=@单位名称

and b.单位编号=y.单位编号

and b.单位编号=c.单位编号

and c.单位编号=y.单位编号

and b.地区编号=x.地区编号

and m.专业编号=y.专业编号

end

execute pro_My01 '上海幻电信息科技有限公司'

go

create procedure pro_My02 @地区名称 varchar(50)

as

begin

select 单位名称,联系电话,地区名称,专业名称,学历要求,需求人数,招聘岗位,发布时间

from Belong b,Company c,Region x,Major m,Request y

where 地区名称=@地区名称

and b.单位编号=y.单位编号

and b.单位编号=c.单位编号

and c.单位编号=y.单位编号

and b.地区编号=x.地区编号

and m.专业编号=y.专业编号

end

execute pro_My02 '成都市武侯区'

go

create procedure pro_My03 @专业名称 varchar(50)

as

begin

select 单位名称,联系电话,地区名称,专业名称,学历要求,需求人数,招聘岗位,发布时间

from Belong b,Company c,Region x,Major m,Request y

where 专业名称=@专业名称

and b.单位编号=y.单位编号

and b.单位编号=c.单位编号

and c.单位编号=y.单位编号

and b.地区编号=x.地区编号

and m.专业编号=y.专业编号

end

execute pro_My03 '数字媒体技术'

1种输入参数和输出参数存储过程，实现统计

go

create procedure pro_My04 @单位名称 varchar(50),@需求总数 int output

as

begin

select @需求总数=sum(需求人数) from Request

where 单位编号=(select 单位编号 from Company where 单位名称=@单位名称)

end

declare @需求人数总数 int

execute pro_My04 '深圳市游科互动科技有限公司',@需求人数总数 output

select @需求人数总数 as 需求人数总数

5.5数据触发器创建

完成1种级联修改的相关触发器设计与实现，至少设计1个触发器

自动变更用人单位级别

create trigger trig_Mytrig02 on Company

after update

as

begin

update Company

set 单位级别=(select 单位级别 from inserted)

where 单位级别 in(select 单位级别 from deleted)

end

update Company set 单位级别='民营企业' where 单位编号='9144030030596015XR'

select * from  Company

查看触发器

exec sp_helptrigger Company

6、统计查询

需有连接查询的相关内容，不少于5类查询统计

插入数据（完整数据过多，在此仅插入一小部分）

Insert Into Company(单位编号,单位名称,单位级别,联系电话) Values('9144030030596015XR','深圳市游科互动科技有限公司','小微企业','0755-29766786')

Insert Into Company(单位编号,单位名称,单位级别,联系电话) Values('915100002018938457','新希望集团有限公司','民营企业','028-85249255')

Insert Into Company(单位编号,单位名称,单位级别,联系电话) Values('91310115067801988G','上海幻电信息科技有限公司','上市公司','15618631843')

Insert Into Company(单位编号,单位名称,单位级别,联系电话) Values('91120116589765101C','天津市好传文化传播有限公司','私企','022-59999088')

Insert Into Company(单位编号,单位名称,单位级别,联系电话) Values('91100000100003962T','中国工商银行股份有限公司','国企','95588')

Insert Into Region(地区编号,地区名称) Values('440305','深圳市南山区')

Insert Into Region(地区编号,地区名称) Values('510107','成都市武侯区')

Insert Into Region(地区编号,地区名称) Values('310115','上海浦东新区')

Insert Into Region(地区编号,地区名称) Values('120116','天津滨海新区')

Insert Into Region(地区编号,地区名称) Values('110102','北京市西城区')

Insert Into Belong(单位编号,地区编号) Values('9144030030596015XR','440305')

Insert Into Belong(单位编号,地区编号) Values('915100002018938457','510107')

Insert Into Belong(单位编号,地区编号) Values('91310115067801988G','310115')

Insert Into Belong(单位编号,地区编号) Values('91120116589765101C','120116')

Insert Into Belong(单位编号,地区编号) Values('91100000100003962T','110012')

Insert Into Major(专业编号,专业名称) Values('080906','数字媒体技术')

Insert Into Major(专业编号,专业名称) Values('120204','财务管理')

Insert Into Major(专业编号,专业名称) Values('0809','计算机类')

Insert Into Major(专业编号,专业名称) Values('1304','美术类')

Insert Into Major(专业编号,专业名称) Values('1305','设计类')

Insert Into Major(专业编号,专业名称) Values('050207','日语')

Insert Into Major(专业编号,专业名称) Values('080904K','信息安全')

Insert Into Major(专业编号,专业名称) Values('0703','化学类')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('9144030030596015XR','080906','游戏后台开发','本科及以上',10,'2022-05-06')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('9144030030596015XR','080906','UE客户端开发','本科及以上',8,'2022-05-06')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('915100002018938457','120204','产品经理','本科及以上',1,'2022-04-08')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('915100002018938457','0703','化工研发调研员','本科及以上',1,'2022-04-27')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('91310115067801988G','1304','3D动作','本科及以上',3,'2022-05-05')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('91310115067801988G','1305','资深UI设计师','本科及以上',5,'2022-05-05')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('91310115067801988G','050207','日漫业务','本科及以上',8,'2022-04-29')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('91120116589765101C','1304','二维动画师','本科及以上',6,'2022-04-28')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('91100000100003962T','080904K','信息安全技术岗','本科及以上',7,'2022-03-08')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('91100000100003962T','1305','交互视觉设计岗','本科及以上',12,'2022-03-08')

Insert Into Request(单位编号,专业编号,招聘岗位,学历要求,需求人数,发布时间) Values('91100000100003962T','0809','软件研发岗','本科及以上',15,'2022-03-08')

对用人单位的基本信息进行修改、删除操作

更新中国工商银行股份有限公司的联系电话为010-82959453

update Company

set 联系电话='010-82959453' where 单位名称='中国工商银行股份有限公司'

删除北京光线传媒股份有限公司所在行的信息

delete from Company where 单位名称='北京光线传媒股份有限公司'

• 单表查询
• 1.查询需求信息

select * from Request

• 2.查询全部用人单位的名称和级别

select 单位名称,单位级别 from Company

• 3.统计需求专业相同的招聘岗位的数量

select 专业编号,count(*) as 招聘岗位的数量 from Request

group by 专业编号

• 4.查询需求人数为1或8的招聘岗位

select 招聘岗位,需求人数 from Request

where 需求人数 in (1,8)

• 5.查询单位级别中含有“国”的用人单位信息

select * from Company

where 单位级别 like '%国%'

• 连接查询
• 1.查询需求人数大于3人的专业名

select 专业名称,需求人数 from Major m join Request r on m.专业编号=r.专业编号

where 需求人数>3

• 2.查询中国工商银行股份有限公司的招聘岗位，查询结果按需求人数降序排列

select 招聘岗位,需求人数 from Request r join Company c on r.单位编号=c.单位编号

where c.单位名称='中国工商银行股份有限公司'

order by 需求人数 desc

• 嵌套查询

查询需求人数小于交互视觉设计岗的用人单位的地区编号及名称

select distinct c.单位名称,x.地区编号,x.地区名称

from Region x,Request y,Belong b,Company c

where x.地区编号=b.地区编号 and b.单位编号=c.单位编号 and c.单位编号=y.单位编号

and 需求人数<(select 需求人数 from Request where 招聘岗位='交互视觉设计岗')

• 集合查询

查询日语和信息安全的专业编号

select 专业名称,专业编号 from Major where 专业名称='日语'

union

select 专业名称,专业编号 from Major where 专业名称='信息安全'

7、数据库安全设置及数据备份

数据库的安全设置

• 身份验证

采用Windows身份认证模式。用户首先登录到Windows中，然后再登录SQL Server，系统从用户登录Windows时提供的用户名和密码中查找当前用户的登录信息，判断是否为SQL Server的合法用户

• 账号管理

创建服务器登录账号，以实现连接服务器

创建数据库用户账号

• 

• 角色管理

• 权限管理（SQL的DCL实现存取权限管理）

新建一个用户Jackson_Wang。即新建的用户有Mark_Tuan和Jackson_Wang

• 1.把用户Mark_Tuan修改Company表单位编号的权限撤销

revoke update(单位编号) on Company from Mark_Tuan

• 2.把查询Request表的权限授予用户Jackson_Wang

grant select on Request to Jackson_Wang

• 3.拒绝用户Jackson_Wang对存储过程pro_My04的execute的权限

deny execute on pro_My04 to Jackson_Wang

数据库的备份和还原

• 创建备份设备

Use 大学生就业咨询系统

Go

Exec sp_addumpdevice 'disk','备份设备02','D:\学习\计算机专业基础\数据库\期末大作业\backuo02\大学生就业咨询系统.bak'

• 备份数据库

backup Database 大学生就业咨询系统 to 备份设备02

• 还原数据库

数据库已删除

restore database 大学生就业咨询系统 from 备份设备02

数据库未删除

restore database 大学生就业咨询系统 from 备份设备02 with replace

8、系统总结

系统总结：

随着网络的发展，网上应聘成为一种新趋势。针对大学生就业咨询的需求，此系统应运而生。

系统首先从分析用户需求入手，明确了不同身份的对象的需求。在此基础上设计了系统数据流图和数据字典，再以此设计数据库。

系统数据流图：