数据库综合实验.doc

开课实验室：软件开发实验室（一） 2012 年 6月14日实验题目教务管理系统一、实验目的 利用一种DBMS作为设计平台，理解并应用课程中关于数据库设计的相关理论，能按照数据库设计步骤完成完整的数据库设计，包括需求分析、概念设计、逻辑设计、物理设计和实施。同时能够正确应用各个阶段的典型工具进行表示。二、设备与环境(1) 硬件设备：PC机一台(2) 软件环境：安装Windows操作系统，安装数据库管理系统SQL Server2008等。三、实验内容1.需求分析教务管理系统中包含的实体有：学生（学号，姓名，性别）教师（教工号，姓名，）课程（课程号，课程名，学分，学时）系部（系部号，系部名）班级（班级号，班级名，人数）实体之间的联系有：一个学生都有一个所班级；一个班级都有一个系部；一个学生可以选修多的课程；一个教师都有一个系部；一个教师可以同时教若干名同学；一个老师可以教授多门课程；每门课程能被多名学生选择；每门课程都有一个期末成绩功能：1. 学生用户：在教务管理系统中学生主要是用来进行选课，查询选课，查询成绩，计算学分等功能，学生不能够修该自己的成绩和信息。2. 教师用户：教师用户主要是为自己所教科目上传成绩，并且查询自己所教的科目，还有个人的信息状况3. 管理员；管理员则是进行的系统的数据的维护，同时还要录入学生和教师的基本信息，学生的奖罚纪录，同时也能对学生的成绩进行修改，也可以对老师的授课进行调整系部系号系部名包含就职11nmnn1mmn学生选课性别学分成绩班级 教师班级名班级号姓名教师号包含讲授课程学号课程名姓名课程号学时人数年龄2.概念结构设计3.逻辑结构设计(1)关系模式的设计与优化学生:学号，姓名，性别，年龄,班号Students(sno,sname,ssex,sage,bno)函数依赖：Sno(sname,ssex,sage,bno)在学生表中因为学生与班级的关系式一个班级有多个学生，所以在学生表中应该有班级表中的主码来做为外码，由函数依赖可以看出是BCNF 班级包含学生1n班级号班级人数学号姓名性别年龄教师:教工号，姓名，系部号Teacher(tno,tname,dno)函数依赖：tno(tname,dno)系部就职教师1n系部号系部名教工号姓名教师与系部的关系是一个系部内有多个老师就职，所以此表中应该包含系部号，在此关系模式中 不存在任何属性对码的传递依赖和部分依赖所以是BCNF课程:课程号，课程名，学分，学时Course(cno,cname,ccredit,chour)函数依赖：cno(cname,ccredit,chour)在此关系模式中 不存在任何属性对码的传递依赖和部分依赖所以是BCNF系部:系部号，系部名Dept(dno,dname)函数依赖：dnodname在此关系模式中 不存在任何属性对码的传递依赖和部分依赖所以是BCNF班级:班级号，班级名，系部号,班级人数Banji(bno,bname,dno,bnumber)函数依赖：bno(bname,dno,bnumber)系部包含班级1n系号系部名班号班级名班级与系部的关系是一个系里面有多个班级，所以要把系部的主码作为此表的外码，在此关系模式中 不存在任何属性对码的传递依赖和部分依赖所以是BCNF成绩：学号，课程号，成绩Xuanke(sno,cno,grade)函数依赖：snograde,cnograde在此关系模式中 不存在任何属性对码的传递依赖和部分依赖所以是BCNF学生选课教师mn成绩学号姓名年龄性别教工号姓名授课:教工号，课程号Shouke(tno,cno)课程讲授教师nm课号课程名教工号姓名在此关系模式中 不存在任何属性对码的传递依赖和部分依赖所以是BCNF (2)设计合适的视图-定义一个学生姓名,课程的名称,分数，学分构成的视图CREATE VIEW V1ASSELECT students.Sno,Sname,Cname,grade,XUANKE.CcreditFROM students,course,XUANKEWHERE (students.Sno=XUANKE.Sno and course.Cno=XUANKE.Cno)学生查询成绩时主要看的是自己的科目成绩，所以建立了这个视图怎可以是学生更好的直观的观察自己的成绩，同时在大学中需要计算分，在视图中加入学分，则更方便学生对自己学分的计算-定义一个老师的信息和讲授课程CREATE VIEW V2ASSELECT teacher.Tno,Tname,Dname,CnameFROM teacher,course,dept,shoukeWHERE (teacher.Tno=shouke.Tno and course.Cno=shouke.Cno and dept.Dno=teacher.Dno);这个视图的目的是方便学生查询自己选择的课的老师和信息，同时而方便教师查询所教的课程的信息，则样就省去了学生在知道课程在花费时间寻找老师的时间。CREATE VIEW V3ASSELECT Bname,Sno,SnameFROM BANJI,studentsWHERE BANJI.Bno=students.Bno此视图是为了方便老师查询班级的内的同学的姓名与学号。4.物理设计数据库系统的在每个表上的主码都会自动生成一个索引5.数据库的实现 (1)创建库、表 CREATE TABLE XUANKE (Sno char(20) , Cno char(20) , grade CHAR (10), Ccredit CHAR(10), primary key(sno,cno), foreign key(sno)references students(sno) ON DELETE CASCADE ON UPDATE CASCADE, foreign key(cno)references course(cno) ON DELETE CASCADE ON UPDATE CASCADE, ); 这是建立课程成绩的表，在表中sno和cno是主码，同时sno和cno也是这个表中的外码，因为sno和cno分别是教师表和课程表的主码，它们从教师表和课程表里参照引用的，所以也定义了此表的参照完整性，on delete cascade 和on update cascade则是为了建立级联关系，当删除或更新时级连删除或修改此表中的SNO和CNO，这样就不会造成数据的不统一，不会是数据混乱。 CREATE TABLE students (Sno CHAR(20) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(20) CHECK(Ssex IN (男,女), Sage CHAR(20), Bno CHAR(10) , foreign key(Bno)references BANJI(Bno) )； 这是学生表的建立,主码为Sno，Sname的值不能为空，同时定义了性别的值只能为男或女，因为学生表和班级表的关系式n：1所以在学生表中Bno则作为了外码，这样就确定了表的完整性。(2)创建用户公共的权限是用户对教务管理系统中个表信息的查寻 。1.我所创建了三个用户，第一个是S1，这个代表的是学生的用户，在教务管理系统查询分数中，学生只能进行成绩的查询不能够有其他的操作，所以在权限的方面学生只有公共的查询权限。2.第二个是T1，代表的是老师的用户，在教务管理系统中老师需要把自己所教科目的学生的成绩输入到选课表中，所以我赋给教师用户的权限除公共的查询权限外还有对XUNKE表中的grade的数据的修改权限。3.我所创建的第三个用户是T2，代表的是管理员，管理员的权限相对于普通老师的权限要大的多，因为他负责系统数据的管理，所以我将所有表格的所有权限赋予了管理员。(3)恰当使用触发器、存储过程和游标等相关技术 (至少使用一种技术) 。Create Trigger t1On studentsAFTER INSERT,DELETEASUPDATE banji set bnumber=bnumber+1 where bno=(select bno from inserted)CREATE TRIGGER T2ON studentsA