数据库技术与应用大作业课案

1、1系统背景介绍1.1系统名称学生奖学金管理系统1.2 系统概况随着我国与世界信息高速公路的接轨，信息技术的发展，企事业的办公及管理都将朝着高效、快速、无纸化的方向发展，信息系统替代原始的人工管理将是一个必然趋势。本系统旨在为各大学生院系提供一个可以实现记录更新学生的各科成绩、参与的各种活动情况，统计并按照一定计算方式最终可生成学生们按各种标准的排名情况的系统，为学生奖学金的评比更加快速地提供更加准确、公平的依据。信息系统的优势在于：首先，较之于手工管理，信息系统的可靠性很高，不容易出现一些手工管理经常犯的错误；其次，信息系统节约很多的资源，手工管理需要消耗很多的纸张；再次，信息系统的安全性很高

2、，一般的事故不会导致数据的丢失或者被破坏；最后，信息系统使用起来很方便，无论是增加，删除，修改，还是查询都比手工管理节省了很多的时间。1.3系统信息需求按照从概念模型到关系模型的步骤：甄别实体确定实体属性选择码分析联系，系统的信息需求如下：本系统共有四个实体，学生、课程、院系、活动。 描述学生的信息有：学号、姓名、性别、班级、专业、院系号； 描述课程的信息有：课程号、上课专业、学时、学分； 描述院系的信息有：院系号、院系名、下设专业； 描述活动的信息有：活动编号、活动名称、活动等级、活动加分、获奖人学号、获奖人姓名。2 概念模型设计2.1 E-R图2.1.1实体描述图2-1 学生实体图图2-2

3、课程实体图图2-3院系实体图图2-4活动实体图2.1.2 合成E-R图图2-5 学生奖学金管理系统E-R图2.2 相关语义说明一个学生只能属于一个院系，一个院系有多名学生；一个学生可以选择多门课程，一个课程可以被多名学生选择；一个学生可以参加多个活动，一个活动可以由多名同学参加。本系统中，同一院系同一专业的学生必修的课程是一样的，不同的院系不同专业的学生必修的课程不尽相同。学校所办的活动面向全校的每一个学生。在奖学金评比中，只看每个学生的必修课成绩和所参加活动的获奖情况。3 数据模型设计3.1学生表表3-1 Student表数据项名数据类型宽度及小数位列级完整性描述备注（实际意义）SnoVar

4、char50实体完整性PRIMARY KEY(NOT NULL)学号SnameVarchar50NOT NULL姓名SsexChar10用户自定义完整性（只取“男”或“女”）性别SclassVarchar50NOT NULL班级SmajorVarchar50NOT NULL专业SdeptVarchar50参照完整性FOREIGN KEY院系号主码：学号（Sno）外码：院系号（Sdept）应建索引数据项：学号表级完整性约束：学号的实体完整性也可用此表述，院系号的参照完整性（FOREIGN KEY）3.2院系表表3-2 Department表数据项名数据类型宽度及小数位列级完整性描述备注（实际意义

5、）DnoVarchar50实体完整性PRIMARY KEY(NOT NULL)院系号DnameVarchar50/院系名DeanVarchar50/系主任主码：院系号（Dno）外码：无应建索引数据项：院系号表级完整性约束：院系号的实体完整性也可用此表述3.3课程表表3-3 Course表数据项名数据类型宽度及小数位列级完整性描述备注（实际意义）CnoVarchar50实体完整性PRIMARY KEY(NOT NULL)课程号CnameVarchar50NOT NULL课程名ClasshourInt3/学时CreditInt2/学分主码：课程号（Cno）外码：无应建索引数据项：课程号表级完整性约

6、束：课程号的实体完整性也可用此表述3.4成绩表表3-4 Grade表数据项名数据类型宽度及小数位列级完整性描述备注（实际意义）SnoVarchar50/学号CnoVarchar50/课程号ScoreFloat5/成绩主码：学号（Sno），课程号（Cno）外码：学号（Sno），课程号（Cno）应建索引数据项：学号，课程号表级完整性约束：学号的实体完整性、参照完整性，课程号的实体完整性、参照完整性注：对于多属性构成的码，只能用表级约束定义其完整性3.5活动表表3-5 Activity表数据项名数据类型宽度及小数位列级完整性描述备注（实际意义）AnoVarchar50实体完整性PRIMARY KEY

7、(NOT NULL)活动编号AnameVarchar50非空活动名称ArankInt2/活动等级AmarkInt3/活动加分AsnoVarchar50参照完整性FOREIGN KEY获奖人学号AtimeDate3/活动时间主码：活动编号（Ano）外码：获奖人学号（Asno）应建索引数据项：活动编号，活动名称，活动时间表级完整性约束：活动编号的实体完整性可以用此描述，获奖人学号的参照完整性3.6活动加分表表3-6 ActivityPoints表数据项名数据类型宽度及小数位列级完整性描述备注（实际意义）SnoVarchar30/学号AnoVarchar30/活动编号DnoVarchar30/院系号

8、AmarkInt3/活动加分主码：学号（Sno），活动编号（Cno），院系号（Dno）外码：学号（Sno），活动编号（Cno），院系号（Dno）应建索引数据项：学号，活动编号，院系号表级完整性约束：学号的实体完整性、参照完整性，活动编号的实体完整性、参照完整性，院系号的实体完整性、参照完整性注：对于多属性构成的码，只能用表级约束定义其完整性4 数据定义语言练习4.1基本表（完整性）定义的SQL语句学生（Student）表：create Table Student(Sno varchar(50) primary key,Sname varchar(50) not null,Ssex char(1

9、0) check(Ssex in (男,女),Sclass varchar(50) not null,Smajor varchar(50) not null,Sdept varchar(50)foreign key references Department(Dno)院系（Department）表：create Table Department(Dno varchar(50) primary key,Dname varchar(50),Dean varchar(50)课程（Course）表：create table Course(Cno varchar(50) primary key,Cnam

10、e varchar(50)not null, Classhour int,Credit int)成绩（Grade）表：create Table Grade(Sno varchar(50) not null,Cno varchar(50) not null,Score float,primary key(Sno,Cno),foreign key(Sno) references Student(Sno),foreign key(Cno) references Course(Cno)活动（Activity）表：create Table Activity(Ano varchar(50) primary

11、 key,Aname varchar(50) not null,Arank int,Amark int,Atime date,Asno varchar(50)foreign key references Student(Sno)活动加分（ActivityPoints）表：create Table ActivityPoints(Sno varchar(50) not null,Ano varchar(50) not null,Dno varchar(50) not null,Amark int,primary key(Sno,Ano,Dno),foreign key(Sno) reference

12、s Student(Sno),foreign key(Ano) references Activity(Ano),foreign key(Dno) references Department(Dno)4.2索引定义的SQL语句create unique index stusno on Student(Sno);create unique index deptdno on Department(Dno);create unique index crscno on Course(Cno);create unique index SCno on Grade(Sno ASC,Cno DESC);cre

13、ate unique index Acano on Activity(Ano);create nonclustered index Acnametime on Activity(Aname,Atime);create unique index SADno on ActivityPoints(Sno ASC,Ano DESC,Dno);4.3视图定义的SQL语句建立管理学院学生活动加分的视图：create view manageActivityPointsASselect Sno,Ano,Dno,Amarkfrom ActivityPointswhere Dno=11with check opt

14、ion;5 数据操作语言练习5.1数据增加实例1操作请求描述增加学生张尧的信息：，张尧，男，1201，工程管理，11T-SQL语句insert into student(Sno,Sname,Ssex,Sclass,Smajor,Sdept)Values(,张尧,男,1201,工程管理,11);执行结果截屏实例2操作请求描述增加一个活动信息：活动编号（6），活动名称（青年成长沙龙），活动等级（1级），活动加分（15），获奖人学号（），活动时间（2014-12-12）T-SQL语句insert into Activity(Ano,Aname,Arank,Amark,Atime,Asno)Value

15、s(6,青年成长沙龙,1,15,2014-12-12,);执行结果截屏5.2数据删除实例1操作请求描述删除学号为“”学生的信息T-SQL语句delete from studentWHERE Sno=;执行结果截屏实例2操作请求描述删除名字叫“崔飞飞”的这个同学的所有成绩T-SQL语句delete from GradeWHERE 崔飞飞=(select Snamefrom studentwhere student.Sno=Grade.Sno)执行结果截屏5.3数据修改实例1操作请求描述将管理学院的系主任由“赵老师”更改为“王老师”T-SQL语句update Departmentset Dean=

16、王老师where Dname=管理学院;执行结果截屏实例2操作请求描述将学号是“”的学生成绩都加2分T-SQL语句update Gradeset Score=Score+2where =(select Snofrom studentwhere student.Sno=Grade.Sno);执行结果截屏5.4数据查询5.4.1简单查询实例1操作请求描述查询举办的所有活动T-SQL语句select Aname from Activity;执行结果截屏实例2操作请求描述查询全体学生的信息T-SQL语句select *from student;执行结果截屏5.4.2嵌套查询实例1操作请求描述查询参加了

17、青年成长沙龙活动的同学的学号、姓名T-SQL语句select Sno,Sname from studentwhere Sno in (select Asnofrom ActivityWHERE Aname=青年成长沙龙);执行结果截屏实例2操作请求描述查询没有参加任何活动的学生学号、姓名T-SQL语句select Sno,Sname from studentwhere Sno not in (select Asnofrom ActivityWHERE Activity.Asno=student.Sno);执行结果截屏5.4.3连接查询实例1操作请求描述查询每个学生的学号、姓名、课程号和成绩以及

18、参加的活动T-SQL语句select Student.Sno,Sname,Cno,Score,Anamefrom student,Grade,Activitywhere student.Sno=Grade.Sno and Grade.Sno=Activity.Asno;执行结果截屏实例2操作请求描述查询每个学生及其课程成绩T-SQL语句select student.*,Grade.*from student,Gradewhere student.Sno=Grade.Sno;执行结果截屏5.4.4聚合查询实例1操作请求描述查询管理学院所有女生的信息T-SQL语句select *from stud

19、entwhere Sdept=11intersectselect *from studentwhere Ssex=女;执行结果截屏实例2操作请求描述查询选择了课程3且成绩大于90的学生学号T-SQL语句select Snofrom Gradewhere Cno=3intersectselect Snofrom Gradewhere Score90;执行结果截屏6 数据库管理及控制6.1触发器6.1.1触发器逻辑表述控制逻辑表述：在成绩（Grade）表上建立一个触发器，当用户试图修改某门课程的成绩score时触发器被激发，以阻止用户的修改行为，并回滚事务。6.1.2触发器SQL语句T-SQL语句

20、：create trigger modify_score on Gradefor updateasif update(score)beginprint无法修改成绩rollback transactionendReturn6.1.3触发器测试测试语句：update Gradeset Score=98where Sno= and Cno=1;测试结果：6.2事务控制逻辑表述：活动加分乘以10%，如果学号为的学生活动加分若超过10分，事务将回滚。T-SQL语句：BEGIN TRANSACTION USE zhushan UPDATE ActivityPoints SET Amark=Amark*0.

21、1 IF (SELECT Amark FROM ActivityPoints WHERE sno= ) 10BEGIN PRINT Transaction rollback back ROLLBACK TRANSACTION END ELSE BEGIN PRINT Transaction commited COMMIT TRANSACTIONEND执行结果：6.3数据库用户6.3.1创建数据库用户操作请求表述：为数据库创建用户RoseT-SQL语句：CREATE LOGIN Rose WITH PASSWORD = ; USE zhushan; CREATE USER zhushan FOR

22、 LOGIN Rose; GO 执行结果：6.3.2创建数据库角色并为其授权操作请求表述：创建一个数据库角色zhu，并授予其查询Student表的权限T-SQL语句：create role zhu;grant select on student to zhu;执行结果：7 数据库访问连接 7.1实现过程本系统采用JDBC数据库连接，使用Java作为编辑语言。下载eclipse安装，下载驱动Microsoft SQL Server JDBC Driver 3.0，打开eclipse新建一个Java Project将sqljdbc4.jar载入。然后输入正确代码，执行即可。7.2访问实例操作请求表

23、述：查询活动（Activity）表中的所有信息Java代码：package run;import java.sql.*;public class SCORPIONpublic static void main(String args)String JDriver = com.microsoft.sqlserver.jdbc.SQLServerDriver;String conURL = jdbc:sqlserver:/localhost:1433;DatabaseName=zhusha;Connection conn;Statement stmt;ResultSet rs;String userName=Rose; String userPwd=;tryClass.forName(JDriver);System.out.println(已成功加载驱动！);catch (java.lang.ClassNotFoundException ex)System.out.println(加载驱动失败！);tryconn = DriverManager.getConnection(conURL,userName,userPwd);System.out.println(已成功连接数据库！);stmt = conn.createStatement();String sql = SELECT *