数据库报告
西安电子科技大学
数据库系统
(2020年度)
学
生
管
理
系
统
设
计
报
告
写在前面的话（作为给学弟学妹们的一个参考）
由于coolwx本人也是很菜的一个人，所以发布一下报告给之后的学弟学妹们留作一个参考，算是造福后世吧
本代码已经开源，位于https://github.com/coolwx/database_xdu
使用java+sqlserver，但是本人水平菜鸡了，写的界面也很丑陋，只能说给学弟学妹们一个参考，如果有有心人喜欢的话那就最好了
一、需求分析
1. 设计要求
（1） 画E-R图，并将其转为关系模式，根据关系模式创建数据库。
（2） 创建视图显示学会名和学生数；创建触发器，自动增减班级表和系表的人数字段的值。
（3） 创建函数，实现更改旧班号为新班号，并返还此班人数的功能。
（4） 创建存储过程，使用游标检查系表人数与实际学生人数是否相符，不符就更改系表该字段的值为实际数。
（5） 实现对基本表的增删改查功能。
2. 实体分析
通过设计要求可以设计出各种实体以及它们之间的关系图，这些实体包含各种具体信息，则实体有:班级实体，专业实体，院系实体，学生实体、学生会实体。
一个学院有多个专业，一个专业只能属于一个学院，因此学院与专业之间具有一对多关系。
一个专业有多个班级，一个班级只能属于一个专业，因此专业与班级之间具有一对多关系。
一个班级有多个学生，一个学生只能属于一个班级，因此班级与学生之间具有一对多关系。
一个学生可以加入多个学生会，一个学生会可以被多个学生加入，因此学生与学生会之间是多对多的联系。用入会年份来表示学生和学生会之间联系的属性。
二、设计准备
1.E-R图
2.实体联系图和关系模式
- 学生（学号，姓名，年龄，性别，班级号）
- 班级（班号，专业号，入校年份，人数)
- 专业（专业号，专业名，系号）
- 院系（院系号，系名，系办公室地点，宿舍区，系人数）
- 学生会（学会号，学会名，成立时间，学会地点）
- 学生-学生会（学号，学会号，入会年份）
3.根据关系模式建立数据库
学生表 Student
|
变量 |
变量名规定 |
数据类型 |
完整性及其他规定 |
|
学号 |
Sno |
Int |
主码，学号>0 |
|
姓名 |
Sname |
Varchar（50） |
允许重名 |
|
年龄 |
Sage |
Int |
>0 |
|
性别 |
Ssex |
Varchar（4） |
男或女 |
|
班级号 |
Cno |
Int |
参照完整性 |
实体属性图：
范式分析：
Sno为主码，Student(Sno, Sname, Sage, Sex, Cno)中既没有非主属性对码的部分函数依赖，也没有对码的传递函数依赖，也没有主属性对码的部分和传递函数依赖，故属于BCNF。
班级表 Class
|
变量 |
变量名规定 |
数据类型 |
完整性及其他规定 |
|
班号 |
Cno |
Int |
主码，班号>0 |
|
专业号 |
Mno |
Int |
参照完整性 |
|
入校年份 |
Cyear |
Int |
>1900 <2100 |
|
人数 |
Cpeople |
Int |
>=0 |
实体属性图：
范式分析：
Cno为主码，Class(Cno, Mno, Cyear, Cpeople)中既没有非主属性对码的部分函数依赖，也没有对码的传递函数依赖，也没有主属性对码的部分和传递函数依赖，故属于BCNF。
专业表 Major
|
变量 |
变量名规定 |
数据类型 |
完整性及其他规定 |
|
专业号 |
Mno |
Int |
主码 >0 |
|
专业名 |
Mname |
Varchar（50） |
UNIQUE唯一 |
|
系号 |
Dno |
Int |
参照完整性 |
实体属性图：
范式分析：
Mno为主码，Major(Mno, Mname, Dno) 中既没有非主属性对码的部分函数依赖，也没有对码的传递函数依赖，也没有主属性对码的部分和传递函数依赖，故属于BCNF。
院系表 Department
|
变量 |
变量名规定 |
数据类型 |
完整性及其他规定 |
|
院系号 |
Dno |
Int |
主码 >0 |
|
系名 |
Dname |
Varchar(50) |
UNIQUE 唯一 |
|
系办公室地点 |
Dplace |
Varchar(100) |
|
|
宿舍区 |
Ddorm |
Int |
>0 |
|
系人数 |
Dpeople |
Int |
>=0 |
实体属性图：
范式分析：
Dno为主码，Department (Dno,Dname,Dplace, Ddorm, Dpeople) 中既没有非主属性对码的部分函数依赖，也没有对码的传递函数依赖，也没有主属性对码的部分和传递函数依赖，故属于BCNF。
学会表 Association
|
变量 |
变量名规定 |
数据类型 |
完整性及其他规定 |
|
学会号 |
Ano |
Int |
主码 >0 |
|
学会名 |
Aname |
Varchar(50) |
UNIQUE 唯一 |
|
成立时间 |
Atime |
datetime |
|
|
学会地点 |
Aplace |
Varchar(50) |
实体属性图：
范式分析：
Ano为主码，Association (Ano,Aname, Atime, Aplace) 中既没有非主属性对码的部分函数依赖，也没有对码的传递函数依赖，也没有主属性对码的部分和传递函数依赖，故属于BCNF。
学生-学生会 SA
|
变量 |
变量名规定 |
数据类型 |
完整性及其他规定 |
|
学号 |
Sno |
Int |
参照完整性，主码 |
|
学会号 |
Ano |
Int |
参照完整性，主码 |
|
入会年份 |
SAyear |
Int |
>1900 <2100 |
范式分析：
Sno,Ano为主码，SA (Sno,Ano, SAyear) 中既没有非主属性对码的部分函数依赖，也没有对码的传递函数依赖，也没有主属性对码的部分和传递函数依赖，故属于BCNF。
过程表 Procedure_record
注释：用于最后一个存储过程，记录正确的人数和错误的人数
|
变量 |
变量名规定 |
数据类型 |
完整性及其他规定 |
|
院系号 |
Dno |
Int |
|
|
院系名 |
Dname |
Varchar(50) |
|
|
院系原来的人数（Department表中的人数） |
Doldpeople |
Int |
|
|
院系真正正确的人数 |
Dnewpeople |
Int |
-
后端设计
-
创建视图：辅助视图（计算系人数所用视图，系视图，班级视图）
-
班级视图ClassView
视图ClassView用于显示班号、专业名、入校年份、系名、人数
create view ClassView(Cno,Mname,Cyear,Dname,Cpeople)
as
select Class.Cno,Major.Mname,Class.Cyear,Department.Dname,Class.Cpeople
from Class,Major,Department
where
Class.Mno = Major.Mno and Major.Dno = Department.Dno
-
系人数视图，系人数等于所有该系的班级人数之和（这是这个系真正的人数，而不是Department基本表中Dpeople的值）
--视图DepartmentPeopleView，显示每一个系对应的系号与系内的总人数，系内总人数等于以Dno为分组后，班级总人数之和
create view DepartmentPeopleView(Dno,Dpeople)
as
select Department.Dno,SUM(Class.Cpeople)
from
Class inner join Major on Class.Mno = Major.Mno
inner join
Department on Major.Dno = Department.Dno
group by Department.Dno
-
系视图，用于显示系的各项信息，和0.2所表示视图中系真正的人数
--视图 DepartmentVi用于显示系号、系名、系办公室地点、人数
create view DepartmentView(Dno,Dname,Dplace,Dpeople)
as
select Department.Dno,Department.Dname,Department.Dplace,DepartmentPeopleView.Dpeople
from Department,DepartmentPeopleView
where
Department.Dno = DepartmentPeopleView.Dno
1. 创建视图：学会
- 要求：创建一个视图，能显示每个学会的学会名，学生数（实际不存在，也不能增加）。
- 实现：
- create view AssociationPeopleView(Aname,Apeople)
as
select Association.Aname,COUNT(SA.Sno)
from
Association inner join SA on Association.Ano = SA.Ano
group by
Association.Aname - 说明：采用分组视图，连接SA表获得学生选择学号信息，使用count获得学生数
2. 创建触发器：改变班级表和系表人数
- 要求：创建一个触发器，能根据每个班的学生变动情况自动增减班级表和系表的人数字段的值。
- 实现：
- create trigger Student_insert
on Student
after insert
as
BEGIN
update Class set Cpeople=Cpeople+1 where Cno = (Select Cno from inserted)
update Department set Dpeople=Dpeople+1 where Dname = (Select ClassView.Dname from ClassView where ClassView.Cno =(Select Cno from inserted))
END
create trigger Student_delete
on Student
after delete
as
BEGIN
update Class set Cpeople=Cpeople-1 where Cno = (Select Cno from deleted)
update Department set Dpeople=Dpeople-1 where Dname = (Select ClassView.Dname from ClassView where ClassView.Cno =(Select Cno from deleted))
END
create trigger Student_update
on Student
after update
as
BEGIN
update Class set Cpeople=Cpeople+1 where Cno = (Select Cno from inserted)
update Class set Cpeople=Cpeople-1 where Cno = (Select Cno from deleted)
update Department set Dpeople=Dpeople+1 where Dname = (Select ClassView.Dname from ClassView where ClassView.Cno =(Select Cno from inserted))
update Department set Dpeople=Dpeople-1 where Dname = (Select ClassView.Dname from ClassView where ClassView.Cno =(Select Cno from deleted))
END - 说明：classview里面的信息比基本表更加完整，所以使用视图。
3. 创建储存过程：班号替换
- 要求：给定给定一个班的旧班号和新班号，把所有相关表中此班的旧班号改为新班号，并返回此班的人数。一个班的旧班号和新班号，把所有相关表中此班的旧班号改为新班号，并返回此班的人数。
- 实现：
- create procedure update_Cno
@oldCno int,
@newCno int
As
Begin
declare @return_num int
alter table Student Disable Trigger all
select @return_num=Cpeople from Class where @oldCno = Cno
update Class set Cno = @newCno where Cno = @oldCno
update Student set Cno = @newCno where Cno = @oldCno
alter table Stdudent Enable Trigger all
return @return_num
END - 说明：由于用户自定义函数不能存取数据表，不可以执行拼接的sql语句，也不能使用临时表，所以使用存储过程完成。
4. 使用游标完成储存过程：人数检测
- 要求：确定系表中人数字段的值与实际学生数是否相符。如果不相符，把人数字段的值改为实际数，并返回此系的系号、系名、原人数、实际人数。
- 实现：
- create procedure Check_Dpeople
@Dno int output,
@Dname varchar(50) output,
@Doldpeople int output,
@Dnewpeople int output
as
BEGIN
declare Vernier cursor local scroll for
select Dno,Dname,Dpeople from Department
open Vernier
declare @Tmpno int,@Tmpname varchar(50),@Tmppeople int
fetch next from Vernier into @Tmpno,@Tmpname,@Tmppeople
while(@@FETCH_STATUS=0)
BEGIN
Delete from Procedure_record
declare @realpeople int
Set @realpeople = 0
select @realpeople = Dpeople from DepartmentView where Dno = @Tmpno
if (@realpeople != @Tmppeople)
BEGIN
select @Dno=Dno,@Dname=Dname,@Doldpeople=@Doldpeople,@Dnewpeople=@realpeople from DepartmentView where Dno=@Tmpno
Insert into Procedure_record values(@Dno,@Dname,@Doldpeople,@Dnewpeople)
Update Department set Dpeople = @realpeople where Dno = @Tmpno
END
fetch next from Vernier into @Tmpno,@Tmpname,@Tmppeople
END
close Vernier
END
- 说明:realpeople用来存储使用班级人数计算出来的实际人数,teppeople是表中未更新的人数，注意当系初始创建时，实际人数为0，需要手动设置realpeople为0。并且在游标迭代过程中，每次都需要将realpeople置为0。建立中间过程表procedure方便中间查询。
- 方法是在基本表Department先定义一个游标，然后不断滚动此游标，查找系中的Dpeople与DeparmentView中的Dpeople（真正的系人数）是否相等，如果不等，那就更改此基本表中的系人数。
四、前端设计
1.概述
使用Java，swing实现前端的图形可视化和jdbc连接数据库。
数据库
- 连接： 在连接数据库之前，首先要加载想要连接的数据库的驱动到JVM（Java虚拟机），这通过
- java.lang.Class类的静态方法forName(String className)实现。
- 然后使用DriverManager下的方法请求并获得要连接的数据库对象： getConnection(String url,String username,String password)
- 本设计中url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=StudentInformation
- 协议：jdbc；子协议：sqlserver；localhost地址:127.0.0.1;目标端口：1433；目标库名;
- 执行：建立statement对象来执行sql语句，建立resultset对象来返回结果集
- •要执行SQL语句，必须获得java.sql.Statement实例，Statement实例分为以下3 种类型：
1、执行静态SQL语句。通常通过Statement实例实现。
2、执行动态SQL语句。通常通过PreparedStatement实例实现。
3、执行数据库存储过程。通常通过CallableStatement实例实现。
•具体的实现方式：
Statement stmt = con.createStatement() ;
PreparedStatement pstmt = con.prepareStatement(sql) ;
CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ; - •Statement接口提供了三种执行SQL语句的方法：executeQuery 、executeUpdate和execute
1、ResultSet executeQuery(String sqlString)：执行查询数据库的SQL语句，返回一个结果集（ResultSet）对象。
2、int executeUpdate(String sqlString)：用于执行INSERT、UPDATE或 DELETE语句以及SQL DDL语句，如：CREATE TABLE和DROP TABLE等
3、execute(sqlString):用于执行返回多个结果集、多个更新计数或二者组合的语句。
•具体实现的代码：
ResultSet rs = stmt.executeQuery("SELECT \* FROM ...") ;
int rows = stmt.executeUpdate("INSERT INTO ...") ;
boolean flag = stmt.execute(String sql) ; - 使用结果集（ResultSet）对象的访问方法获取数据：
while(rs.next()){
String name = rs.getString("name") ;
String pass = rs.getString(1) ;
}
可视化
- 登录界面：helloLabel; 主界面：mainlayout; 增删查改界面：***layout。类名与表英文名一致。
- 交互：使用ActionListener类，捕捉actionevent;界面和按钮继承自JFrame类
2.增（以插入学生信息为例）
使用insert语句插入数据
String sql =
"insert into Student values("
+Sno+",'"
+Sname+"',"
+Sage+",'"
+Ssex+"',"
+Cno+")";
使用executeUpdate方法，返回值为插入数据数量，此处为1
int i = stmt.executeUpdate(sql);
if(i==1)
{
JOptionPane.showMessageDialog(null, "插入成功", "消息", JOptionPane.PLAIN_MESSAGE);
}
else
{
JOptionPane.showMessageDialog(null, "插入失败，请检查你的数据", "消息", JOptionPane.WARNING_MESSAGE);
}
3.查（以学号查询学生信息为例）
使用select语句查询，使用executeQuery方法，将光标移至末尾统计数量，并用rs方法返回数据存入string中
String sql = "select * from StudentView where Sno="+SSno;
rs = stmt.executeQuery(sql);
rs.last();
int rows = rs.getRow();
int i=0;
rs.beforeFirst();
System.out.println(rows);
if(rows==0)
{
JOptionPane.showMessageDialog(null, "学号不存在！", "消息", JOptionPane.WARNING_MESSAGE);
}
else
{
rs.next();
int Sno = rs.getInt(1);
String SSSno = String.valueOf(Sno);
String Sname = rs.getString(2);
int Sage = rs.getInt(3);
String SSage = String.valueOf(Sage);
String Ssex = rs.getString(4);
String Dname= rs.getString("Dname");
int Cno = rs.getInt("Cno");
String SCno = String.valueOf(Cno);
int Ddorm = rs.getInt("Ddorm");
String SDdorm = String.valueOf(Ddorm);
String FinalString = "学号:"+SSSno+"\n"
+"姓名:"+Sname+"\n"
+"年龄:"+SSage+"\n"
+"性别:"+Ssex+"\n"
+"系名:"+Dname+"\n"
+"班号:"+SCno+"\n"
+"宿舍:"+SDdorm;
JOptionPane.showMessageDialog(null,FinalString, "结果", JOptionPane.PLAIN_MESSAGE);
}
4.删（以删除学生信息为例）
首先调用查询，以object类接收要删除的表，然后调用getSelectedRow方法获得要删除的行，使用executeupdate执行sql语句.（调用查询过程略，同查询）
int selectedRows=table.getSelectedRow();
Object obj = model.getValueAt(selectedRows, 0);
int x = ((Integer)obj).intValue();
String Sno = String.valueOf(x);
model.removeRow(selectedRows);
String sql = "delete from Student where Sno="+Sno;
int i = stmt.executeUpdate(sql);
5.改(以修改学生信息为例)
更改过程与删除类似，注意在数据库和可视化界面都做出修改。同样调用查询获得表，与删除相比，获得更多选中行的信息（删除只需知道哪一行，而修改需要获得该行所有信息），使用executeupdate执行sql语句
int selectedRows=table.getSelectedRow();
Object obj = model.getValueAt(selectedRows, 0);
String Sno = obj.toString();
obj = model.getValueAt(selectedRows, 1);
String Sname = obj.toString();
obj = model.getValueAt(selectedRows, 2);
String Sage = obj.toString();
obj = model.getValueAt(selectedRows, 3);
String Ssex = obj.toString();
obj = model.getValueAt(selectedRows, 4);
String Cno = obj.toString();
String sql = "update Student set Sname='"+Sname+"'," + "Sage="+Sage+",Ssex='"+Ssex+"',"+"Cno="+Cno+" where Sno="+Sno;
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
五、前后端遇到的问题及其解决过程
1.后端
- 在储存过程中，本来想用表级函数做的，结果sqlserver函数内不能对基本表进行更新，所以不能使用表级函数返回一个表，只能使用存储过程返回一个结果集，返回的结果集是select出来的，最后使用的output输出，同时这里使用了Departmentview内真正的系内人数，里面的系内人数是用班级人数算出来的，而我们插入的Department基本表内的Dpeople可能不是对的值，所以这里使用存储过程对其更新，这里使用的是局部游标，存储过程结束立即丢弃掉，还需要注意的是游标使用前应该打开，使用后关掉!
-
在使用游标完成储存过程中，第一次写代码出现了错误，原因是没有这一行Set @realpeople = 0，这一步将每次的变量都清零，不然，将出现迭代错误，因为select之后，如果Dno不存在我们的departmentview（用来算出来真正此时院系中的人数）@realpeople并不是nul值，而是保持了原来的值，此时出现了迭代错误！
2.前端
- 不熟悉windowbuilder组件，虽然windowbuilder插件帮忙省了很多时间，但是此插件过于不太好用，所以后来都是全部手打Actionlistener代码，反而比windowbuilder插件快很多，毕竟都是绝对布局；
-
一开始写页面跳转的时候没有写JFrame.setvisible(true)，直接导致第一天的进度缓慢；
- 对JTable的不熟悉导致浪费了大量时间；
-
本来直接想先rs.last() 然后rs().getrow()，最后再rs.beforefirst()以为可以直接获得结果集的行数，但是jdbc中默认结果集的游标不能随便滚动，所以必须建立状态的时候必须
stmt= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
开启游标滚动，不然写Jtable类的时侯不能输出结果！
-
写这些代码的时候没有好好考虑java的反射特性和面向对象特性，所以就写了三层的架构，没有写类间的关系和继承，其实应该好好写继承关系的，看起来代码很多，其实很多是相同的代码，完全可以先写基类然后派生，但是考虑到swing组件的交互问题，每一个类内的actionlistener都不同，所以一直没敢下手去写父类和子类，最后每个类都继承自JFrame，对于面向对象来说这不是很好的。
六、结果演示
1.登录
图1.1、1.2 登录界面
图1.3、1.4 登录成功并连接到数据库
图1.5 操作界面
图1.6、1.7、1.8、1.9 增删改查四个页面
2.查询初始信息
图2.1 查询所有学生信息
图2.2 查询所有班级信息
图2.3 查询所有专业信息
图2.4 查询所有院系信息
图2.5 查询所有学会信息
图2.6 查询所有学生选择的学会
3.增加信息
图3.1.1 插入学生10"张三"
图3.1.2 插入"张三"后的学生表
图3.2.1 插入班级1803014
图3.2.2 插入"1803014"班后的学生表
图3.3.1 学生"张三"入会1
图3.2.2学生"张三"入会后的学会信息（人数1->2）
4.更改信息
图4.1将学生张三的学号更改为2
图4.2将学生张三的选择的学会更改为2（1->2）
图4.3、4.4调用存储过程将1803014班改为1803013
图4.5、4.6调用存储过程更正系表人数
5.删除信息
图5.1 删除了学生"张三"
图5.2 删除了班级1803013
图5.3 删除了专业"信息工程"
图5.4 删除了院系"机电工程"
图5.5 删除了专业"信息对抗"
七、总结与思考
通过这次的学生管理系统设计实验，我们对于数据库在生活中的广泛应用有了更深切的体会，对数据库有了更深刻的认识，同时也加深了我们对课本知识的进一步理解，实践能力得到了提高。
