数据库-教务管理系统-综合实验报告

华北科技学院计算机系综合性实验实验报告课程名称《数据库原理及应用教程》实验学期2011 至2012 学年第 2 学期学生所在系部计算机学院年级专业班级学生姓名学号任课教师郭红实验成绩计算机系制《数据库原理及应用教程》课程综合性实验报告教师基信息与学生成绩查询视图：4.物理设计该数据库中每一个关系模式的主码分别为系统自动设置的索引，如学生关系模式中的学号，教师关系模式中的教师号，系别中的系号等，同时，通过对实际操作的思考，为了查询某系的老师的方便，我们还需要在Teacher表中建立一个“系号”Dno的索引；又因为查询操作很多都通过学生姓名查找的，所以还需要在Student表姓名列sname上建立一个索引等，这样大大简化查询操作。

5.数据库的实现(1)创建库、表在该教务成绩管理系统中包括了学生表、教师表、选课表、系别表、授课表、课程表六个表。

在此，我以学生表为例进行说明表的创建。

一个完整的表必须包括对实体完整性、参照完整性、用户定义完整性的三种完整性的定义。

在学生表中，首先考虑表的实体完整性要求，在该学生表中学号被设为主码，用PRIMARY KEY定义，且该表含有多个属性，所以学号就是表级约束条件，从而完成了学生表的实体完整性的定义。

其次，对于学生表的参照完整性，系号（Dno）是该学生表的外码，同时它还是班级系别Sdept表的主码，所以对其用FOREING KEY短语进行定义，并用REFERENCES短语指明该外码是参照那些表的主码，从而完成对表的参照完整性的定义。

最后，是表的用户定义完整性，在该学生表中，结合实际情况我们不难想到应该定义一个姓名Sname为非空的约束条件和性别Ssex只能为“男”或“女”二者选其一的约束条件，其中姓名的非空用NOT NULL定义即可，而性别用CHECK语句定义即可。

创建学生表的代码为：CREATE TABLE student(Sno CHAR(10)PRIMARY KEY,Sname CHAR(20)NOT NULL,Ssex CHAR(2)CHECK(Ssex IN('男','女')),Sage SMALLINT,Dno CHAR(10),FOREIGN KEY (Dno)REFERENCES Sdept(Dno));创建的学生表截图如下：(2)创建用户该教务管理系统的主要用户有学生用户，老师用户，教务管理员和系统管理员等四种，所以需要创建这四种类型的用户，并授予其相应的权限。

对于学生用户就只能授予其查询学生基本信息student表的权限，老师相关信息视图V-teacher表的权限，查询学生成绩视图V_student表的权限，以及查询教师授课TC表的权限；教师则具有查询学生基本信息Student表的权限，查询教师授课TC表的权限，还具有修改自己基本信息teacher表的权限，还有登记插入学生成绩信息即修改SC表的权限；教务管理员具有修改本系一切相关信息的权力，包括查询、删除、增添、修改，而系统管理员具有创建各种表、视图、触发器及存储过程并对各个用户进行相应授权的权力。

下面是建立学生(u1)、教师(u2)、教务处(u3)的用户（登陆名）截图：(3)使用触发器、存储过程等相关技术。

在教务成绩管理数据库中，鉴于存在教师的退休及学生退学或毕业的情况，所以必然会有删除这些信息的操作，为了方便起见，所以有必要设计一个“删除教师信息”触发器以及“删除学生信息”触发器。

同时教务成绩管理系统的数据库必然涉及到学生选课退课的问题，所以还需要设计一个关于选课退课系统的“选课”触发器与“删除选课”触发器。

“删除教师信息”与“删除学生信息”触发器的过程大致相同，就“删除教师信息”触发器来说，当删除教师时如果该教师在TC表中有其授课信息，则当删除该教师后必会引起TC表不可用，所以创建的该触发器当所要删除的教师号存在于TC表里时，系统就应该给出“TC表里有该教师的课程，请先删除TC表里的记录！”的提示信息；就“删除学生信息”触发器来说，如果该学生退学时在选课表里仍有其选课记录，则当删除该学生信息后，在SC表里的相关选课记录必然成为无效，所以在此设置了一个判断即将退学的学生是否能删除的触发器，当退学学生在SC表里有该学生的记录时应该给出“SC表里有该学生的选课课程，请先删除SC表里的记录！”的信息提示。

“删除教师信息”触发器操作演示如下所示：“选课”与“删除选课”触发器的过程大致相同，就“选课”触发器来说当所选课程的课程号存在于课程表里时，系统就会将该条的选课记录添加到SC表中，并给出“选课成功”的信息提示，同样删除选课与此类似，当要删除的选课的课程号存在于SC表时，系统就会将该条的选课记录从SC表中删除，并给出“删除选课成功”的信息提示。

演示执行“选课”触发器的操作结果截图如下：--选课触发器create trigger选课on SCfor insertasif exists(select Cno from course wherecno=(select cno from inserted))beginprint'选课成功'end这样在进行这些相关数据的处理时就可以通过触发器来控制，如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生变化。

除了创建相应触发器外，教务成绩管理系统中还用到了数据库的存储过程，在本数据库中我建立了关于指定某个学号的“学生成绩查询”的存储过程，这样学生只需要输入自己的学号就可以查询出自己所选的科目以及其相应的成绩，存储过程的编译及其实现过程如下截图所示：其实，触发器就是一种特殊的存储过程，这样创建完成这些常用功能的存储过程后它们被编译后就能保存在数据库中，它们就像函数一样可以被反复调用，提高运行速度。

四、实验结果及分析这次实验报告我选择的是设计“教务成绩管理系统”的数据库。

通过本次自己独立的设计一个数据库，我更清楚了设计数据库的方法和步骤。

通过一步步的分析、实施、操作，我将理论联系于实际，并把课本上所学的和老师所讲的知识综合的运用起来，这样不仅使我加深了对SQL语句的理解，而且提高了对所学知识的熟练运用程度。

在实验过程中，我了解到创建表时也需要注意一些我们常常容易忽略的问题。

例如：建表中的元组顺序问题，还有一些表格中的属性列的完整性要求是在另一些表的属性列的基础上创建的，所以，这就存在创建表是的一些的顺序问题，如果不按照这个顺序建表，则建表必然会失败。

另外，在创建表的同时还得注意每个属性的数据类型，这个细节问题也是我们常常忽略的问题，对每个属性列都要根据实际情况确定相应的数据类型，并定义一定的长度大小，以提高空间利用率。

我在本次作业中的另一收获是明确了触发器中rollback语句的正确用法，当触发器所设定的操作能正确执行时就不需要添加rollback语句，而当触发器所设定的操作不能完成时需要用rollback语句回滚，并给出相应的提示信息。

这次大作业中，我也看出了我还存在的许多问题，在一些步骤中思考的不够缜密，对某些方面也有些疏漏之处。

所以，这就要求我们查询更多的信息，理论充分联系实际，多多参与在实际数据库的创建中去，在实践中积累经验，不断学习，才能提高自己在数据库方面的能力。

--一、创建表-- 建立系别表CREATE TABLE sdept(Dno CHAR(10)PRIMARY KEY,Dname CHAR(20)UNIQUE,Dphone CHAR(20),);-- 建立教师表create table teacher(Tno CHAR(10)PRIMARY KEY,Tname CHAR(20)not null,Tsex CHAR(2)CHECK(Tsex IN('男','女')),Prof CHAR(4),Tage SMALLINT,Dno CHAR(10),FOREIGN KEY (Dno)REFERENCES Sdept(Dno));-- 建立学生表CREATE TABLE student(Sno CHAR(10)PRIMARY KEY,Sname CHAR(20)NOT NULL,Ssex CHAR(2)CHECK(Ssex IN('男','女')),Sage SMALLINT,Dno CHAR(10),FOREIGN KEY (Dno)REFERENCES Sdept(Dno));-- 建立课程表CREATE TABLE course(Cno CHAR(10)PRIMARY KEY,Cname CHAR(20)UNIQUE,Credit SMALLINT,);--建立授课表CREATE TABLE TC(Cno CHAR(10),Tno CHAR(10),);-- 建立选课表CREATE TABLE SC(Sno CHAR(10),Cno CHAR(10),Grade SMALLINT CHECK(Grade>=0 AND Grade<=100),PRIMARY KEY (Sno,Cno),);------------------------------------------------------------学生的学号、成绩等信息查询的视图create view V_studentasselect student.Sno,Sname,cname,Credit,gradefrom student,SC,coursewhere student.Sno=SC.Sno ando=o--教师基本信息查询的视图create view V_teacherasselect Tname,Prof,Dnamefrom teacher,sdeptwhere teacher.Dno=sdept.Dno------------------------------------------------在教师表中系代号列上建立索引create unique index teadnoon teacher(Tno,Dno);--在学生表姓名列上建立索引create unique index stusnameon student(Sno,sname);-------------------------------------------------创建学生用户create login u1with password='111'use GRADEcreate user学生for login u1--对学生用户进行授权grant selecton studentto学生grant selecton V_studentto学生grant selecton SCto学生grant selecton TCto学生grant selecton V_teacherto学生--创建教师用户create login u2with password='111'use GRADEcreate user教师for login u2--对教师用户进行授权grant selecton studentto教师grant selecton TCto教师grant select,updateon teacherto教师grant select,insert,update(grade) on SCto教师--创建教务管理员用户create login u3with password='111'use GRADEcreate user教务处for login u3--对教务管理员用户进行授权grant select,update,insert,deleteon teacherto教务处grant select,update,insert,deleteon studentto教务处grant select,update,insert,deleteon sdeptto教务处grant select,update,insert,deleteon courseto教务处grant select,update,insert,deleteon sdeptto教务处grant select,update,insert,deleteon courseto教务处grant select,update,insert,deleteon TCto教务处grant select,update,insert,deleteon SCto教务处--------------------------------------------删除教师信息触发器create trigger删除教师信息on teacherfor deleteasif exists(select*from tc where tno=(select tno from deleted)) beginprint'course表里有该教师的课程，请先删除course表里的记录！'rollbackend--演示删除教师信息触发器的操作deletefrom teacherwhere tno='t1'--删除学生信息触发器create trigger删除学生信息on studentfor deleteasif exists(select*from sc where sno=(select sno from deleted))beginprint'SC表里有该学生的选课课程，请先删除SC表里的记录！'rollbackend--演示删除学生信息触发器的操作deletefrom studentwhere sno='s1'--选课触发器create trigger选课on SCfor insertasif exists(select Cno from course where cno=(select cno from inserted)) beginprint'选课成功'end--演示执行“选课”触发器insertinto SC(sno,cno)values('s1','c3')--查询选课结果select*from SC--删除选课触发器create trigger删除选课on SCfor DELETEASif exists(select Cno from sc where cno=(select cno from deleted))beginprint'删除选课成功'end--演示执行“删除选课”触发器deletefrom scwhere sno='s1'and cno='c3'--------------------------------------------------- --查询成绩的存储过程CREATE PROCEDURE查询成绩@sn char(10)asselect student.sno,sname,cname,Credit,gradefrom student,course,SCwhere student.sno=sc.sno ando=o andstudent.sno=@sn--查询学号为“s1”的学生成绩exec查询成绩's1'。