学生信息管理系统数据库设计(sql server）

项目名称

学生信息管理系统数据库

项目概述

本项目旨在开发一个综合性的学生信息管理系统数据库，该系统将为教育机构提供一个集中的平台，用于存储、管理和分析学生数据。系统将支持学生注册、成绩跟踪、课程安排、教师和家长沟通等功能。

技术栈

• 数据库管理系统：SQL Server 2008R
• 开发工具：SQL Server Management Studio (SSMS), Visual Studio
• 编程语言：T-SQL

数据库设计

数据库设计包括以下关键实体和关系：

• 学生个人信息表：包含学生的个人信息、联系方式和注册信息。
• 课程表：存储课程的详细信息，如课程编号、名称、描述等。
• 成绩表：记录学生在各个课程中的成绩。
• 班级表：记录学生·的班级
• 专业表：记录学生·的专业
• 学院表：存储学校不同部门的信息。
• 视图：提供学生、教师和课程的综合视图。
• 存储过程：实现数据的增删改查操作。

功能特点

• 课程和教师信息管理
• 成绩录入和查询
• 学生选课和课程安排
• 报告生成：提供学生成绩报告、课程统计等

一.班级表

二.课程表

三.学院表

四.成绩表

五.专业表

六.学生个人信息表

七.数据库代码

1.创建表和插入数据

use StuMange --使用数据库
-- 院系表 
create table Department(
   did varchar(30) primary key not null, -- 院系编号主键
   dname varchar(30) not null
);
-- 专业表 
create table Major(
   mid varchar(30) primary key not null, -- 专业编号主键
   did varchar(30) not null,
   mname varchar(30) not null,
   foreign key (did) references Department (did) -- 外键
);
-- 班级表 
create table Class(
   cid varchar(30) primary key not null, -- 班号为主键
   mid varchar(30) not null,
   did varchar(30) not null,
   cname varchar(30) not null,
   cnumber int not null,
   foreign key (did) references Department (did),
   foreign key (mid) references Major (mid)
);
-- 学生表
create table stu(
   id varchar(30) primary key not null, -- 学号主键
   name varchar(30) not null, -- 姓名
   sex char(2) not null check(sex in('男','女')), -- 性别
   cid varchar(30) not null,
   mid varchar(30) not null,
   did varchar(30) not null,
   nation varchar(20) not null, -- 民族
   age int not null, -- 年龄
   birthday date not null, -- 生日
   location varchar(20) not null, -- 地址
   foreign key (cid) references Class (cid),
   foreign key (mid) references Major (mid),
   foreign key (did) references Department (did)
);
-- 课程表
create table Course(
   kid varchar(30) primary key not null, -- 课程编号主键
   kname varchar(30) not null, -- 课程名称
   kcredit int not null, -- 课程学分
   kperiod int not null, -- 课程时间
);

-- 课程成绩表
create table Grade(
   kid varchar(30) not null,
   id varchar(30) not null,
   ggrade int not null, -- 课程成绩
   primary key(kid, id), -- 课程编号为主键
   foreign key (kid) references Course (kid),
   foreign key (id) references stu (id)
);


select * from Department
insert into Department values('RJ111','软件学院')
insert into Department values('XC222','新闻与传播学院')
insert into Department values('JSJY333','教师教育学院')
insert into Department values('Y444','医学院')
insert into Department values('ST555','数学与统计学院')
insert into Department values('WGY666','外国语学院')
insert into Department values('DQ777','电气与机械工程学院')


select * from Major
insert into Major values('RJ11','RJ111','软件工程')
insert into Major values('JSJ22','RJ111','计算机科学与技术')

insert into Major values('GG11','XC222','广告学')
insert into Major values('XW22','XC222','新闻学')

insert into Major values('XQ11','JSJY333','学前教育')
insert into Major values('XX22','JSJY333','小学教育')

insert into Major values('LC11','Y444','临床医学')
insert into Major values('JP22','Y444','解剖学')

insert into Major values('JR11','ST555','金融工程')
insert into Major values('YT22','ST555','应用统计学')

select * from Class
insert into Class values('RG1','RJ11','RJ111','软工一班',6)
insert into Class values('JK1','JSJ22','RJ111','计科一班',6)

insert into Class values('GG1','GG11','XC222','广告一班',5)
insert into Class values('XW1','XW22','XC222','新闻一班',7)

insert into Class values('CJ1','XQ11','JSJY333','初教一班',6)
insert into Class values('XJ1','XX22','JSJY333','小教一班',5)

insert into Class values('LC1','LC11','Y444','临床一班',6)
insert into Class values('JP1','JP22','Y444','解剖一班',6)

insert into Class values('JR1','JR11','ST555','金融一班',5)
insert into Class values('YT1','YT22','ST555','应统一班',5)


select * from stu
insert into stu values('202300406084','王昭君','女','RG1','RJ11','RJ111','汉族',20,'2004-06-12','广西百色')
insert into stu values('202300406085','刘备','男','RG1','RJ11','RJ111','汉族',21,'2003-02-22','广西梧州')
insert into stu values('202300406086','关羽','男','RG1','RJ11','RJ111','回族',19,'2005-03-14','广西钦州')
insert into stu values('202300406087','杨玉环','女','RG1','RJ11','RJ111','汉族',20,'2004-01-09','广西北海')
insert into stu values('202300406088','曹操','女','RG1','RJ11','RJ111','汉族',20,'2004-02-03','广西梧州')
insert into stu values('202300406089','孙权','男','RG1','RJ11','RJ111','汉族',19,'2005-04-03','云南大理')

insert into stu values('202300406090','李清照','女','JK1','JSJ22','RJ111','汉族',20,'2004-02-11','云南昆明')
insert into stu values('202300406091','貂蝉','女','JK1','JSJ22','RJ111','汉族',20,'2004-02-02','云南丽江')
insert into stu values('202300406092','李白','男','JK1','JSJ22','RJ111','回族',19,'2005-04-01','浙江绍兴')

insert into stu values('202300406093','杜甫','男','GG1','GG11','XC222','汉族',20,'2004-01-10','四川成都')
insert into stu values('202300406094','朱元璋','男','GG1','GG11','XC222','汉族',21,'2003-02-22','河南郑州')

select * from Course
insert into Course values('YY1','大学英语',4,72)
insert into Course values('GS2','高等数学',4,120)
insert into Course values('SJK3','数据库',4,72)
insert into Course values('SJ4','数据结构',4,72)
insert into Course values('LC5','Linux操作系统',3,68)

select * from Grade
insert into Grade values('YY1','202300406084',99)
insert into Grade values('LC5','202300406085',96)
insert into Grade values('GS2','202300406086',94)
insert into Grade values('SJK3','202300406087',100)
insert into Grade values('SJ4','202300406088',99)
insert into Grade values('YY1','202300406089',99)
insert into Grade values('LC5','202300406090',96)
insert into Grade values('GS2','202300406091',94)
insert into Grade values('SJK3','202300406092',79)
insert into Grade values('SJ4','202300406093',99)
insert into Grade values('SJ4','202300406094',89)

2.对数据的删改查以及视图的建立

use StudentOD
-----------------------------查询操作

--查询年龄小于20的学生的基本信息
select * from stu where age < 21

--查询选修了“SJK3”课程的学生的学号、姓名、成绩，其中按照成绩从大到小的顺序排列
select Stu.id,name,Ggrade from Stu,Grade
where Stu.id = Grade.id and Grade.Kid = 'SJK3' 
order by Ggrade desc

--查询选修“大学英语”课程所有学生的平均成绩
select avg(Ggrade) 平均成绩 from Grade,Course 
where Grade.Kid = Course.Kid 
and Course.Kname = '大学英语'


-----------------------------更改操作
--将课程“高数"的课程时间更改为130
select * from Course where Kname = '高等数学'
update Course set Kperiod = 130 where Kname = '高等数学' 

--将家庭地址为“云南昆明”的学生的家庭地址更改为“云南怒江”
select * from stu where location = '云南昆明'
select * from stu where location = '云南怒江'
update stu set location = '云南昆明' where location = '云南怒江'

---------------------------------删除操作
--删除学号为“202300406084”学生信息
select * from stu
delete from stu where id = '202300406084'

----------------------------------创建视图
--创建学生成绩视图，实现查询各个学生的学号、姓名、课程、成绩
create view B_Grade as
select 
    stu.id as id,
    stu.name as name,
    Course.Kname as Kname,
    Grade.Ggrade as Ggrade
from
    stu
join 
    Grade on stu.id = Grade.id
join 
   
    Course on Grade.Kid = Course.Kid;
    
    
    -----------------------创建触发器
    --当增加学生班级信息时自动修改相应班级学生人数
create trigger insert_stu
on stu for insert
as
 begin
  declare @cid varchar(30)
  select @cid=Cid from inserted
  update Class set Cnumber = Cnumber + 1 where Cid = @cid
 end
 
 select * from Class where Cname = '软件233'
 insert into stu values('202300406084','王阳明','男','RG1','RJ11','RJ111','汉族',20,'2004-02-03','广西贵港')
 select * from Class where Cname = '软件233'

--创建触发器，当删除学生班级信息时自动修改相应班级学生人数
create trigger delete_stu
on stu for delete
as
 begin
  declare @cid varchar(30)
  select @cid=Cid from deleted
  update Class set Cnumber = Cnumber - 1 where Cid = @cid
 end
 
 select * from Class where Cname = '软件233'
 delete from stu where name = '王阳明'
 select * from Class where Cname = '软件233'
 
 --创建触发器，当修改学生班级信息时自动修改相应班级学生人数
create trigger update_stu
on stu for update
as
 begin
  declare @cid1 varchar(30)
  declare @cid2 varchar(30)
  --存放更新前的数据
  select @cid1=Cid from deleted
  --存放更新后的数据
  select @cid2=Cid from inserted
  update Class set Cnumber = Cnumber - 1 where Cid = @cid1
  update Class set Cnumber = Cnumber + 1 where Cid = @cid2
 end
 
  select * from stu where name = '李白'
  select * from Class where Cname = '软件233'
  select * from Class where Cname = '英语一班'
  update stu set Cid = 'YY1' where name = '李白'
  

3.数据库关系图