数据库结课作业.docx
- 文档编号:5461408
- 上传时间:2022-12-16
- 格式:DOCX
- 页数:20
- 大小:430.10KB
数据库结课作业.docx
《数据库结课作业.docx》由会员分享,可在线阅读,更多相关《数据库结课作业.docx(20页珍藏版)》请在冰豆网上搜索。
数据库结课作业
1系统背景介绍
1.1系统名称
学生成绩管理系统
1.2系统概况
学生成绩管理系统中的数据库设计主要是要求数据库中包含学生的基本信息,课程的基本信息,教师的基本信息,以及学生所学课程的考试成绩。
该系统要方便学生进行成绩查询,教师针对每门课程给出每个学生的成绩,以及教学管理人员维护教师和学生的基本信息。
学生成绩管理系统的用户包括学生、教师、教学管理人员三类用户。
该系统应当实现如下系统功能:
使得学生的成绩管理工作更加清晰、条理化、自动化;通过用户名和密码登录系统,查询课程基本资料,学生所选课程成绩,修改用户密码等功能,容易地完成学生信息的查询操作;功能安排合理,并且进一步考虑系统在安全性,完整性,并发控制,备份和恢复等方面的功能要求。
1.3信息需求描述
学生通过自己的用户名和密码登录系统,进入系统后可以进行个人基本信息的查看、修改,可以查看自己的课程成绩,可以查看课程的基本信息等;教师学生通过自己的用户名和密码登录系统,进入系统后可以进行个人基本信息的查看、修改,可以针对自己教的课程给出每个学生的成绩,可以查看课程的基本信息等;教学管理人员通过自己的用户名和密码登录系统,维护学生、教师、课程的基本信息。
2概念模型设计
2.1系统分析
根据前面对系统进行的分析,已经初步了解了学生成绩管理系统的数据处理流程,找出与系统有关的各个实体及其相互联系如下:
实体集:
学生、教师、课程、成绩。
经过分析可以知道学生主要由学号、姓名、性别、出生日期、籍贯等属性来描述,其中学生的学号为登录系统的用户名,密码即为登录系统密码;教师主要由职工编号、姓名、性别、出生日期、系别、籍贯等属性来描述,其中职工编号为教师登录系统的用户名,密码即为登录系统密码;课程主要由课程编号、课程名、学分、课时数来描述;成绩主要由分数和学期来描述。
本系统中将教学管理人员看成是特殊的教师。
2.2画出E-R图
学生信息图如下:
图2-1学生实体E-R图
教师信息图如下:
图2-2教师实体E-R图
课程信息图如下:
图2-3课程实体E-R图
成绩信息图如下:
图2-4成绩实体E-R图
3数据模型设计
由分析可知,一个学生可以选多门课程,一门课程可以被多个学生选择;一个教师可以上多门课程,每个课程只能由一个教师上课;每个学生在每门课程中有一个成绩。
数据库的关系模式为:
学生(学号,姓名,性别,出生日期,系别,籍贯,密码)
教师(职工编号,姓名,性别,出生日期,系别,籍贯,密码)
课程(课程编号,课程名,课时数,学分,系别,职工编号)
成绩(学号,课程编号,分数,学期)
学生表
数据项名
数据类型
宽度及小数位
列级完整性描述
学号
varchar
20
主键、唯一、非空
姓名
char
10
非空
性别
char
2
只能取值“男”或“女”
出生日期
datetime
系别
char
20
非空
籍贯
char
10
密码
varchar
20
非空
主码:
学号
外码:
无
应建索引的数据项:
学号
表级完整性约束:
学号是主键、唯一,性别只能取男或者女
教师表
数据项名
数据类型
宽度及小数位
列级完整性描述
职工编号
varchar
20
主键、唯一、非空
姓名
char
10
非空
性别
char
2
只能取值“男”或“女”
出生日期
datetime
系别
char
20
非空
籍贯
char
10
密码
varchar
20
非空
主码:
职工编号
外码:
无
应建索引的数据项:
职工编号
表级完整性约束:
职工编号是主键、唯一,性别只能取男或者女
课程表
数据项名
数据类型
宽度及小数位
列级完整性描述
课程编号
varchar
20
主键、唯一、非空
课程名
varchar
20
非空
课时数
int
非空
系别
char
20
非空
学分
int
非空
职工编号
varchar
20
参照教师表中的职工编号
主码:
课程编号
外码:
职工编号
应建索引的数据项:
课程编号
表级完整性约束:
课程编号是主键、唯一,职工编号是外键
成绩表
数据项名
数据类型
宽度及小数位
列级完整性描述
学号
varchar
20
参照学生表中的学号
课程编号
varchar
20
参照课程表中的课程编号
分数
int
只能取0到100之间的数
学期
char
10
非空
主码:
学号、课程编号
外码:
学号、课程编号
应建索引的数据项:
学号、课程编号
表级完整性约束:
学号和课程编号是主键,学号是外键,课程编号是外键,分数只能取0到100之间的数
4数据库实现
CREATEdatabaseStudentDBonprimary(
/*--数据文件的具体描述--*/
name='StudentDB_data',--主数据文件的逻辑名称
filename='D:
\StudentDB_data.mdf',--主数据文件的物理名称
size=5mb,--主数据文件的初始大小
maxsize=100mb,--主数据文件增长的最大值
filegrowth=15%--主数据文件的增长率
)
logon
(
/*--日志文件的具体描述,各参数含义同上--*/
name='StudentDB_log',
filename='D:
\StudentDB_log.ldf',
size=2mb,
filegrowth=1mb
)
4.1基本表及其完整性定义
CREATETABLEStudent(
S_Snovarchar(20)NOTNULLUNIQUE,
S_Namechar(10)NOTNULL,
S_Genderchar
(2)
CHECK(S_GenderIN('男','女')),
S_Birthdaydatetime,
S_Dnochar(20)NOTNULL,
S_Addresschar(10),
S_Passwordvarchar(20)NOTNULL,
PRIMARYKEY(Sno));
CREATETABLETeacher(
Tea_idvarchar(20)NOTNULLUNIQUE,
T_Namechar(10)NOTNULL,
T_Genderchar
(2)
CHECK(T_GenderIN('男','女')),
T_Birthdaydatetime,
T_Dnochar(20)NOTNULL,
T_Addresschar(10),
T_Passwordvarchar(20)NOTNULL,
PRIMARYKEY(Tea_id));
CREATETABLECourse(
Cou_idvarchar(20)NOTNULLUNIQUE,
C_Namevarchar(30)NOTNULL,
C_NumberintNOTNULL,
C_CreditintNOTNULL,
C_Dnochar(20)NOTNULL,
Tea_idvarchar(20),
PRIMARYKEY(Cou_id),
FOREIGNKEY(Tea_id)REFERENCESTeacher(Tea_id));
CREATETABLEMark(
S_Snovarchar(20)NOTNULL,
Cou_idvarchar(20)NOTNULL,
Scoreint
CHECK(Scorebetween0and100),
Semesterchar(10)NOTNULL,
PRIMARYKEY(S_Sno,Cou_id),
FOREIGNKEY(S_Sno)REFERENCESStudent(S_Sno),
FOREIGNKEY(Cou_id)REFERENCESCourse(Cou_id));
4.2定义视图
1、建立由学号、姓名、课程号、课程名、成绩构成的学生成绩视图StudentMark。
CREATEVIEWStudentMark(S_Sno,S_Name,Cou_id,C_Name,Score)
AS
SELECTS_Sno,S_Name,Cou_id,C_Name,Score
FROMStudents,Coursec,Markm
WHEREs.S_Sno=m.S_SnoANDc.Cou_id=m.Cou_id;
2、建立每个学生平均成绩视图Stu_Mark_Avg,视图组成包括学号、平均成绩。
CREATEVIEWStu_Mark_Avg(S_Sno,Avg_mark)
AS
SELECTS_Sno,AVG(Score)
FROMStudents,Markm
WHEREs.S_Sno=m.S_Sno
GROUPBYS_Sno;
4.3定义索引
CREATEUNIQUECLUSTEREDINDEXstudent_sno
ONStudent(S_Sno);
CREATEUNIQUECLUSTEREDINDEXteacher_id
ONTeacher(Tea_id);
ONMark(S_Sno,Cou_id);
5数据库操作
5.1查询操作
简单查询:
SELECT*FROMStudentWHERES_Address='湖北武汉';
SELECTTea_id,T_Name,T_BirthdayFROMTeacher
WHERE01/01/2015-T_Birthday>50;
SELECTcount(*)as'课程数量'FROMCourse;
嵌套查询
查询选修了C02这门课程的学生的学号和姓名
SELECTS_Sno,S_Name
FROMStudent
WHERES_SnoIN
(SELECTS_Sno
FROMMark
WHERECou_id='C02');
查询成绩在90分以上的学生的学号和姓名
SELECTS_Sno,S_Name
FROMStudent
WHERES_SnoIN
(SELECTS_Sno
FROMMark
WHEREScore>=90);
查询选修了C02这门课程的学生的学号和姓名:
聚合查询
查询除C02以外各门课程的平均成绩并按课程分别输出。
SELECTCou_id,Avg(Score)as'AvgScore'
FROMMark
GROUPBYCou_id
HAVINGCou_id<>'C02';
连接查询
查询学生学号、姓名、课程号、课程名、成绩
SELECTS_Sno,S_Name,Cou_id,C_Name,Score
FROMStudents,Coursec,Markm
WHEREs.S_Sno=m.S_SnoANDc.Cou_id=m.Cou_id;
查询成绩在80分以上的学生的学号、姓名、课程号、课程名、成绩,
SELECTS_Sno,S_Name,Cou_id,C_Name,Score
FROMStudents,Coursec,Markm
WHEREs.S_Sno=m.S_SnoANDc.Cou_id=m.Cou_idANDScore>=80;
查询学生学号、姓名、课程号、课程名、成绩:
5.2更新操作
向Student表中插入一条数据
INSERTINTOStudent
VALUES('20150106','小倩','女',11/22/1990,'管理科学与工程','湖北武汉','liqian');
向Course表中插入一条数据
INSERTINTOCourse
VALUES('C04','数据库',64,4,'计算机','T01');
更新Course表,将课程号为C03的课程的课时改为32,学分改为3
UPDATECourse
SETC_Number=32,C_Credit=3
WHERECou_id='C03';
更新Mark表,将课程号为C03,学号为********的一条记录的成绩改为90
UPDATEMark
SETScore=90
WHERES_Sno='20150101'ANDCou_id='C03';
将课程号为C03的记录从Course表中删除
DELETEFROMCourseWHERECou_id='C03';
将课程名称为C语言的所有记录从Mark表中删除
DELETEFROMMarkWHERECou_id=
(SELECTCou_id
FROMCourse
WHEREC_Name='C语言');
更新Course表,将课程号为C03的课程的课时改为32,学分改为3:
5.3视图上的操作
SELECTS_Sno,S_Name,Score
FROMStudentMark
WHEREC_Name='C语言'ANDScore>=85;
5.4游标
定义游标mark_cursor,读取学号为********在Mark表中的所有记录
DECLAREmark_cursorCURSOR
FORSELECT*FROMMarkWHERES_Sno='20150101'orderbyScoredesc
OPENmark_cursor
FETCHNEXTFROMmark_cursor
CLOSEmark_cursor
DEALLOCATEmark_cursor
6数据库管理及控制
6.1触发器
级联操作,当删除Student表中的某条记录时,根据其学号,自动删除其在Mark表中的相应记录
CREATETRIGGERdeletesnoONStudent
FORDELETE
AS
DELETEMark
FROMMarkmJOINStudents
ONm.S_Sno=s.S_Sno
RETURN
级联操作,当删除Course表中的某条记录时,根据其课程号,自动删除其在Mark表中的相应记录
CREATETRIGGERdeletecouidONCourse
FORDELETE
AS
DELETEMark
FROMMarkmJOINCoursec
ONm.Cou_id=c.Cou_id
RETURN
在Mark表中不能更新学号,当遇到更新操作时,回滚事务
CREATETRIGGERupdatesno
ONMark
FORUPDATE
AS
IFUPDATE(S_Sno)
BEGIN
PRINT'StudentIDcannotbemodified!
'
ROLLBACKTRANSACTION
END
RETURN
在Mark表中不能更新课程编号,当遇到更新操作时,回滚事务
CREATETRIGGERupdateconid
ONMark
FORUPDATE
AS
IFUPDATE(Cou_id)
BEGIN
PRINTCourseIDcannotbemodified!
'
ROLLBACKTRANSACTION
END
RETURN
在Course表中不能更新教师编号,当遇到更新操作时,回滚事务
CREATETRIGGERupdateteaid
ONCourse
FORUPDATE
AS
IFUPDATE(Tea_id)
BEGIN
PRINT'TeacherIDcannotbemodified!
'
ROLLBACKTRANSACTION
END
RETURN
在Mark表中不能更新学号,当遇到更新操作时,回滚事务:
6.2事务
事务是用户定义的一个数据库操作序列,这些操作要么全做,要么全不做,它们是一个不可分割的工作单位。
在SQL语言中,定义事务的语句有:
事务开始是BEGINTRANSACTION;事务结束(提交)为COMMIT;事务结束(回滚)为ROLLBACK。
其中,提交是将事务中所有对数据库的更新写回到磁盘上的物理数据库中,事务正常结束。
回滚是在事务运行过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部撤销,回滚到事务开结时的状态。
6.3授权
首先创建登录名为liucan,密码为liucan123
在liucan的登录名下创建用户user1
CREATEUSERuser1FORLOGINliucan;
创建角色role1
CRAETEROLErole1;
将Mark表中Score列的查询、更新的权限赋给角色role1
GRANTSELECT,UPDATE(Score)
ONTABLEMark
TOrole1;
将role1角色赋给用户user1
GRANTrole1TOuser1;
撤销对role1角色的权限
REVOKEUPDATE(Score)
ONTABLEMark
FROMrole1;
7数据库访问连接
数据库连接类
publicclassdbConnection
{
//数据库链接对象
staticConnectiondbConn=null;
//打开数据库连接,@return数据库链接对象
publicstaticConnectiongetDBConn()
{
StringuserName="sa";//用户名
StringuserPwd="liucan123";//密码
StringdbName="StudentDB";//数据库名
try{
//加载JDBC驱动
StringdriverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
StringdbURL="jdbc:
sqlserver:
//localhost:
1433;DatabaseName="+dbName;
Class.forName(driverName);
//获得数据库链接
dbConn=DriverManager.getConnection(dbURL,userName,userPwd);
}catch(Exceptione){
returnnull;
}
returndbConn;
}
//关闭数据库连接
publicstaticvoiddbClose()
{
try{
if(dbConn!
=null)
{
dbConn.close();
}
}catch(Exceptione){
}
}
}
一个实例
/**
*在Student表中添加一条记录
**/
importjava.io.IOException;
importjava.sql.Connection;
importjava.sql.Statement;
importjavax.servlet.ServletException;
importjavax.servlet.http.HttpServlet;
importjavax.servlet.http.HttpServletRequest;
importjavax.servlet.http.HttpServletResponse;
importjavax.servlet.http.HttpSession;
importdb.dbConnection;
publicclassStudentAddServletextendsHttpServlet
{
//数据库链接对象
privateConnectiondbConn=null;
//SQL语句执行对象
privateStatementst=null;
//返回结果
privateinti=0;
publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)
throwsIOException,ServletException
{
//获得当前Session对象
HttpSessionsession=request.getSession();
try{
//检索条件(学生)
StringselName="";
selName=request.getParameter("selName").toString();
//获得数据库连接
dbConn=dbConnection.getDBConn();
//获得SQL语句执行对象
st=dbConn.createStatement();
//sql语句
StringBuffersb=newStringBuffer();
//组装sql语句
sb.append("
insertinto
Student(S_Sno,S_Name,S_Gender,S_Birthday,S_Dno,S_Address,S_Password)values('");
sb.append(request.getParameter("Sno")).append("','");
sb.append(request.getParameter("name")).append("','");
sb.append(request.getParameter("gender")).append("','");
sb.append(request.getParameter("brithday")).append("','");
sb.append(request.getParameter("dno")).append("','");
sb.append(request.getParameter("address")).append("','");
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 作业