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

实验报告课程名称数据库原理及应用教程》课程名称数据库原理及应用教程》实验学期2011至2012学年第2学期学生所在系部计算机学院年级专业班级学生姓名学号任课教师郭红实验成绩计算机系制数据库原理及应用教程》课程综合性实验报告开课实验室：软件开发实验室（一）2012年6月6日实验题目|《教务成绩管理系统》数据库设计―、实验目的利用一种DBMS作为设计平台，理解并应用课程中关于数据库设计的相关理论，能按照数据库设计步骤完成完整的数据库设计，包括需求分析、概念设计、逻辑设计、物理设计和实施。同时能够正确应用各个阶段的典型工具进行表示。二、设备与环境⑴硬件设备：PC机一台⑵软件环境：安装Windows操作系统，安装数据库管理系统SQLServer2008等。三、实验内容1•需求分析首先，通过调查，教务成绩管理系统中主要有四类用户，即学生用户，老师用户，教务管理员和系统管理员。对应这些用户，其处理要求的主要的功能就是进行一系列的查询和各类数据的管理及维护。其具体的处理要求如下：1）系统管理：实现系统管理人员对系统的管理，包括添加删除用户，更改密码，数据备份，数据还原，注销功能。2）教务管理：实现教务管理人员对系统的管理，包括课程安排，成绩审核，学生成绩管理功能。3）基本信息：实现显示学生和老师以及课程、班级、系别的基本信息。4）查询：包括实现学生查询，老师查询。学生查询包括自己的基本信息，自己所修的课程，成绩，老师查询包括查询自己的信息和自己的所授的课程。5）教师对成绩的写入以及自己信息的查询与维护。其次，该教务管理系统中，结合以上用户种类以及其具体的处理功能要求，教务成绩管理系统要具备以下信息要求：教务管理系统涉及的实体有：教师一一教师号、姓名、性别、年龄、职称、系号；学生一一学号、姓名、性别、年龄、系号；授课关系一一教师号、课程号；选课关系一一学号、课程号、成绩；系系号、系名、电话；课程一一课程号、课程名、学分。其中，这些实体之间的联系如下：一个系有若干名学生和老师，但一名学生或一名老师只能属于一个系。一名教师可以教多门课，一门课可以有几位老师讲授，但不同老师讲的同一门课其课程号是不同的，即对于授课教师或课程来说，课程号是唯一的。

一名同学可以选多门课，一门课可被若干同学选中。本单位学生、教师可能有重名，所以教师号、学号才是唯一的。另外，对于教务成绩管理系统需求分析的安全性及完整性要求，首先，安全性方面，系统应对不同用户设置不同的权限，例如，学生用户不能更改自己的成绩信息，从而保障数据库数据的安全。其次，完整性方面，要求各种信息记录的完整性，关键信息记录内容不能为空；各种数据间相互的联系的正确性；相同的数据在不同记录中的一致性等。2•概念结构设计在概念结构设计中，我采用自底向上设计概念结构的方法。即第一步抽象数据并设计局部视图，第二步是集成局部视图，得到全局的概念结构。该教务管理系统中的局部概念模型(E-R该教务管理系统中的局部概念模型(E-R图)如下:、一个系内的关系模式图:教师号性别年龄学生-屮「〔属于＞〔性别)教师号性别年龄学生-屮「〔属于＞〔性别)学号教师牌龄姓名职称

*姓名4系名系号电话、选课关系与授课关系模式图:年龄学号性别姓名■*学生N课程教师号年龄学号性别姓名■*学生N课程教师号教师M授课紀姓名

性别

年龄•职称■课程号课程名学分该教务管理系统中的全局概念模型为：课程号.课程名：N选修年龄课程教师号＜教授教师学分学生卜丁'属于.ALN选修年龄课程教师号＜教授教师学分学生卜丁'属于.AL系别J4学号性别飞姓名.N1拥有性别

年龄

职称

姓名(系名系号电话3•逻辑结构设计(1)关系模式的设计与优化E-R图向关系模型的转换：“教师”实体对应的关系模式:教师(教师号，姓名，性别，年龄，职称)代码表示为：Teacher(Tno.Tname.Tsex.Tage.Pro)其中，教师号为其主码，又因为教师与系别之间是n：1的工作关系，经过与系别关系模式的合并后，系别号Dno成为教师关系模式的外码，存在的关系依赖为Tnof(Tname,Tsex,Tage,Prof,Dno),不存在对候选码的部分依赖与传递依赖，所以满足BCNF。“学生”实体对应的关系模式：学生(学号，姓名，性别，年龄)代码表示：Student(Sno,Sname,Ssex,Sage)其中，学号为其候选码，又因为学生与班级之间是n：1的属于关系，存在的关系依赖为Snof(Sname,Ssex.Sage,Dno),不存在对候选码的传递依赖，所以满足BCNF.系(系代号，系名，系办公室电话)代码表示：Sdept(Dno.Dname,Dphone)系代号为候选码，存在的函数依赖为Dnof(Dname，Dphone)，且不存在部分依赖与传递依赖，所以满足BCNFo“选修”联系对应的关系模式：选课(学号，课程号，成绩)代码表示：SC(Sno,Cno.Grade.)此为m：n联系“选修”所对应的关系模式oSno和Cno均为外码。存在的函数依赖为完全函数依赖(Sno，Cno)fgrade，且不存在部分依赖与传递依赖，所以满足BCNFo“课程”实体对应的关系模式：课程(课程号，课名，学时)代码表示：Course(Cno,Cname,Credit)“授课”联系对应的关系模式：授课(课程号，教师号)代码表示：TC(Tno,Cno.)此为m：n联系“授课”所对应的关系模式。Tno和Cno均为外码。(2)设计合适的视图在将E-R图向关系模型转换后，还应根据局部应用的需求，对不同级别的用户定义不同的视图，这样不仅可以在视图中重新定义某些属性名，使用户使用更方便，而且还可以通过视图保证系统的安全性。在本次的教务成绩管理系统数据库中，我针对使用该数据库最频繁的学生和教师用户建立了相关视图，其中包括对系别、教师、课程的分配视图；对学生学号、成绩、等相关信息的查询，教师基信息的查询两个视图，这样，对这些经常使用的查询，用户就可以通过视图来查询，大大简化了用户的使用。创建的三个视图的截图如下：

对象资源管理關7X连按蚩”笑出■r宝教师基信息与学生成绩查询视图:E对象资源管理關7X连按蚩”笑出■r宝教师基信息与学生成绩查询视图:E心cO51点虫_Server1L.01500-cfJ5istuderE)LJ数据库+二尿卿菇库十二註超库快照三I|sride岀口数据库关系国-LJ表出LJ测克表■；±'.二dLo.courseS_dbo.SCH二sdsptHSidbo.Etuderitffl□dbo.lCS二dho.tA^clinr曰」观圏QLJ系妊观國l±l囲dbo.V^studentE因匚disw.V_teacKerm□同义词田lj可編程性!0iPSarvicc>Brokar-_J仔储-.i_J主全性L±JI」直全拄©i_i服其据对竦rai.j鼬s)・昔理fflSQL弘fvar代理EQ~(53))*]seLeet*3cLeet*iroitiVstudeELt-InaneProfDname1［李力|救授2王平InaneProfDname1［李力|救授2王平讲师信总3讲师计算机45张兰SnoSncrrecnamegrade1程序设计3302pF發扒虬理48?3s2数据鲂枸47Q■I323575s2孙珊程库设计375E±3孙珊徴机处唱4707Ij3数飪结釉48L8s4專曰十心程睜设计3339s4净□-d-.'S48510Ps4李思数宇囲显3e?11s5篙武微携处理499囚结果二消言该数据库中每一个关系模式的主码分别为系统自动设置的索引，如学生关系模式中的学号，教师关系模式中的教师号，系别中的系号等，同时，通过对实际操作的思考，为了查询某系的老师的方便，我们还需要在Teacher表中建立一个“系号”Dno的索引；又因为查询操作很多都通过学生姓名查找的，所以还需要在Student表姓名列sname上建立一个索引等，这样大大简化查询操作。5•数据库的实现(1)创建库、表在该教务成绩管理系统中包括了学生表、教师表、选课表、系别表、授课表、课程表六个表。在此，我以学生表为例进行说明表的创建。一个完整的表必须包括对实体完整性、参照完整性、用户定义完整性的三种完整性的定义。在学生表中，首先考虑表的实体完整性要求，在该学生表中学号被设为主码，用PRIMARYKEY定义，且该表含有多个属性，所以学号就是表级约束条件，从而完成了学生表的实体完整性的定义。其次，对于学生表的参照完整性，系号Dno)是该学生表的外码，同时它还是班级系别Sdept表的主码，所以对其用FOREINGKEY短语进行定义，并用REFERENCES短语指明该外码是参照那些表的主码，从而完成对表的参照完整性的定义。最后，是表的用户定义完整性，在该学生表中，结合实际情况我们不难想到应该定义一个姓名Sname为非空的约束条件和性别Ssex只能为“男”或“女”二者选其一的约束条件，其中姓名的非空用NOTNULL定义即可，而性别用CHECK语句定义即可。创建学生表的代码为：CREATETABLEstudent(SnoCHAR(10)PRIMARYKEY,SnameCHAR20)NOTNULL,

SsexCHAR(2)CHECK(SsexIN('男'，'女'))，SageSMALLINT,DnoCHAR(10),FOREIGNKEY(Dno)REFERENCESSdept(Dno))；创建的学生表截图如下：c051.grais-dba.工jQ*n电工*尸2一雪(53)iTjselect.古至roittstudent<—I结果曲消息232345^SnoSnameSageDno\si;赳亦女17disZ钱尔男18d3孙珊女'20d3舍4李再男21d2s=5周武男19di(2)创建用户该教务管理系统的主要用户有学生用户，老师用户，教务管理员和系统管理员等四种,所以需要创建这四种类型的用户，并授予其相应的权限。对于学生用户就只能授予其查询学生基本信息student表的权限，老师相关信息视图V-teacher表的权限，查询学生成绩视图V_student表的权限，以及查询教师授课TC表的权限；教师则具有查询学生基本信息Student表的权限，查询教师授课TC表的权限，还具有修改自己基本信息teacher表的权限，还有登记插入学生成绩信息即修改SC表的权限；教务管理员具有修改本系一切相关信息的权力，包括查询、删除、增添、修改，而系统管理员具有创建各种表、视图、触发器及存储过程并对各个用户进行相应授权的权力。下面是建立学生(ul)、教师(u2)、教务处(u3)的用户(登陆名)截图：onTC学生□O安全性E1onTC学生ngrantseledtonVteacherngrantseledtonVteacher"学生TSJ-3F3ED92r8T7\stTidentNTMT册RITY\置STEMma—二、—二、1•创建教师用f申createloginu.S使用触发器、存储过程等相关技术。在教务成绩管理数据库中，鉴于存在教师的退休及学生退学或毕业的情况，所以必然会有删除这些信息的操作，为了方便起见，所以有必要设计一个“删除教师信息”触发器以及“删除学生信息”触发器。同时教务成绩管理系统的数据库必然涉及到学生选课退课的问题，所以还需要设计一个关于选课退课系统的“选课”触发器与“删除选课”触发器。“删除教师信息”与“删除学生信息”触发器的过程大致相同，就“删除教师信息”触发器来说，当删除教师时如果该教师在TC表中有其授课信息，则当删除该教师后必会引起TC表不可用，所以创建的该触发器当所要删除的教师号存在于TC表里时，系统就应该给出“TC表里有该教师的课程，请先删除TC表里的记录！的提示信息；就“删除学生信息”触发器来说，如果该学生退学时在选课表里仍有其选课记录，则当删除该学生信息后,

在SC表里的相关选课记录必然成为无效，所以在此设置了一个判断即将退学的学生是否能删除的触发器，当退学学生在SC表里有该学生的记录时应该给出“SC表里有该学生的选课课程，请先删除SC表里的记录！的信息提示。“删除教师信息”触发器操作演示如下所示:jQiL-2O11£1/3VD.GR^DE-r.hoSCgQLQU町钠I-2O._dministratnr(55)]"田匕列■：i在SC表里的相关选课记录必然成为无效，所以在此设置了一个判断即将退学的学生是否能删除的触发器，当退学学生在SC表里有该学生的记录时应该给出“SC表里有该学生的选课课程，请先删除SC表里的记录！的信息提示。“删除教师信息”触发器操作演示如下所示:jQiL-2O11£1/3VD.GR^DE-r.hoSCgQLQU町钠I-2O._dministratnr(55)]"田匕列■：i旳束m二.SSS■)?二至弓IiZdho.SCi二dbo.sdep^〒^3dEKr.siudenttJdha.TC「dbo.teacher十l_J同又词DiI°届^住*Servic-?Smoker出lJ曲3iJ宝全性EJ兰自□nDeachezaazfaxz.BT3selecftcwtiEretno=SElectrxioftomdeleted；&egmy匸好==表里有该戟师的课程，请先嗣除除里的记录I・rollbackend-蓿亍刊除埶帀喑鳥牡炭器的保作leieter:omreacSeTwheretno='tl'沁恙里有技菽师的课裡,请芫郦亦益烹里的逼!事鉛在龜雄駢结妄。鮒:哩已由止°“选课”与“删除选课”触发器的过程大致相同，就“选课”触发器来说当所选课程的课程号存在于课程表里时，系统就会将该条的选课记录添加到SC表中，并给出“选课成功”的信息提示，同样删除选课与此类似，当要删除的选课的课程号存在于SC表时，系统就会将该条的选课记录从SC表中删除，并给出“删除选课成功”的信息提示。演示执行“选课”触发器的操作结果截图如下:4iLJ塞引弔O琳计信忌3二doa.SC3_]doc.sdept3二dba.student-fl□dba.TC刃3dbc.i&ach&r3L观匿3二同殳词3O可驛性SIljSeoiceB^oScer■acaw园立虫全性ibrarysystemUJ|jjReportSeruerhb|jReportServerTempDBO|jjScDreSy'stffm|J学生薮务BLJ盂全性QI[」豈制end-二覆干执行"选课■■傩发器msexLintoSC(sno^.eno}ic31)—查谊选课斂selecr帆fzoni3C4L□结杲li消宜5noCmGrade1siclSO2sic2Z531s11c3NULL4s2c4705s2c557SsJcl751話c270Bs3c485934cl6310c29311s4c3as12sic263—选课触发器createtrigger选课onSCforinsertasifexists(selectCnofromcoursewherecno=(selectcnofrominserted))beginprint'选课成功'end

这样在进行这些相关数据的处理时就可以通过触发器来控制，如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生变化。除了创建相应触发器外，教务成绩管理系统中还用到了数据库的存储过程，在本数据库中我建立了关于指定某个学号的“学生成绩查询”的存储过程，这样学生只需要输入自己的学号就可以查询出自己所选的科目以及其相应的成绩，存储过程的编译及其实现过程如下截图所示：鶴晉埋墨〒11N昕郭窘-y如下截图所示：鶴晉埋墨〒11N昕郭窘-yE■+lJI二dbo.cour5s迅L_j列+_i塔王LJ粧底器+」疾引±一|翅计启旦T口dbo.SC土_]dbo.sdept+.Hdba.studentZ3dbo.TC王口Jbo.teac.her£QLQucry2xqlSOi^dminiitratGr(55))*—査询成绩的存储过理asaelactstudent.sno,anaiDerci-air.aCieditrgradafromst^d=nt.coursepSCwhmizmstudent.sn.D=3C-sno=nduq口Q=ae.enoandstudent_anQ=®3i.43结果二消息—査响学号询-3"的学生成绩亡匸查询成绩1日3>anasnanecnameCredngrade131赳亦哩序设计35D2s1赵亦删处理4&51_阖［总亦3NULL其实，触发器就是一种特殊的存储过程，这样创建完成这些常用功能的存储过程后它们被编译后就能保存在数据库中，它们就像函数一样可以被反复调用，提高运行速度。四、实验结果及分析这次实验报告我选择的是设计“教务成绩管理系统”的数据库。通过本次自己独立的设计一个数据库，我更清楚了设计数据库的方法和步骤。通过一步步的分析、实施、操作，我将理论联系于实际，并把课本上所学的和老师所讲的知识综合的运用起来，这样不仅使我加深了对SQL语句的理解，而且提高了对所学知识的熟练运用程度。在实验过程中，我了解到创建表时也需要注意一些我们常常容易忽略的问题。例如：建表中的元组顺序问题，还有一些表格中的属性列的完整性要求是在另一些表的属性列的基础上创建的，所以，这就存在创建表是的一些的顺序问题，如果不按照这个顺序建表，贝y建表必然会失败。另外，在创建表的同时还得注意每个属性的数据类型，这个细节问题也是我们常常忽略的问题，对每个属性列都要根据实际情况确定相应的数据类型，并定义一定的长度大小，以提高空间利用率。我在本次作业中的另一收获是明确了触发器中rollback语句的正确用法，当触发器所设定的操作能正确执行时就不需要添加rollback语句，而当触发器所设定的操作不能完成时需要用rollback语句回滚，并给出相应的提示信息。这次大作业中，我也看出了我还存在的许多问题，在一些步骤中思考的不够缜密，对某些方面也有些疏漏之处。所以，这就要求我们查询更多的信息，理论充分联系实际，多多参与在实际数据库的创建中去，在实践中积累经验，不断学习，才能提高自己在数据库方面的能力。

教师评价评定项目ABCD评定项目ABCD需求分析清楚安全性设计概念结构符合需求游标或存储过程的使用逻辑结构设计操作熟练索引设计文字流畅完整性设计报告规范其他：评价教师签名：年月曰--一、创建表--建立系别表CREATETABLEsdept(DnoCHAR(10)PRIMARYKEY,DnameCHAR(20)UNIQUE,DphoneCHAR(20),);--建立教师表createtableteacher(TnoCHAR(10)PRIMARYKEY,TnameCHAR(20)notnull,TsexCHAR(2)CHECK(TsexIN('男','女'))ProfCHAR(4),TageSMALLINT,DnoCHAR(10),FOREIGNKEY(Dno)REFERENCESSdept(Dno));--建立学生表CREATETABLEstudent(SnoCHAR(10)PRIMARYKEY,SnameCHAR(20)NOTNULL,SsexCHAR(2)CHECK(SsexIN('男','女')),SageSMALLINT,DnoCHAR(10),FOREIGNKEY(Dno)REFERENCESSdept(Dno));--建立课程表CREATETABLEcourse(CnoCHAR(10)PRIMARYKEY,CnameCHAR(20)UNIQUE,CreditSMALLINT,);--建立授课表CREATETABLETC(CnoCHAR(10),TnoCHAR(10),);--建立选课表CREATETABLESC(SnoCHAR(10),CnoCHAR(10),GradeSMALLINTCHECK(Grade>=0ANDGrade<=100),PRIMARYKEY(Sno,Cno),);--学生的学号、成绩等信息查询的视图createviewV_studentasselectstudent.Sno,Sname,cname,Credit,gradefromstudent,SC,coursewherestudent.Sno=SC.Snoandcourse.Cno=SC.Cno--教师基本信息查询的视图createviewV_teacherasselectTname,Prof,Dnamefromteacher,sdeptwhereteacher.Dno=sdept.Dno--在教师表中系代号列上建立索引createuniqueindexteadnoonteacher(Tno,Dno);--在学生表姓名列上建立索引createuniqueindexstusnameonstudent(Sno,sname);--创建学生用户createloginu1withpassword='111useGRADEcreateuser学生forloginu1--对学生用户进行授权grantselectonstudentto学生grantselectonV_studentto学生grantselectonSCto学生grantselectonTCto学生grantselectonV_teacherto学生--创建教师用户createloginu2withpassword='111'useGRADEcreateuser教师forloginu2--对教师用户进行授权grantselectonstudentto教师grantselectonTCto教师grantselect,updateonteacherto教师grantselect,insert,update(grade)onSCto教师--创建教务管理员用户createloginu3withpassword='111'useGRADEcreateuser教务处forloginu3--对教务管理员用户进行授权grantselect,update,insert,deleteonteacherto教务处grantselect,update,insert,deleteonstudentto教务处grantselect,update,insert,deleteonsdeptto教务处grantselect,update,insert,deleteoncourseto教务处grantselect,update,insert,deleteonsdeptto教务处grantselect,update,insert,deleteoncourseto教务