华北科技学院计算机系综合性实验实验报告课程名称《数据库系统A》实验学期2011 至2012 学年第 2 学期学生所在系部计算机学院年级2010 专业班级网络B101班学生姓名学号任课教师实验成绩计算机系制《数据库系统A》课程综合性实验报告一、实验目的利用一种DBMS作为设计平台，理解并应用课程中关于数据库设计的相关理论，能按照数据库设计步骤完成完整的数据库设计，包括需求分析、概念设计、逻辑设计、物理设计和实施。
同时能够正确应用各个阶段的典型工具进行表示。
二、设备与环境(1) 硬件设备：PC机一台(2) 软件环境：安装Windows操作系统，安装数据库管理系统SQL Server2008等。
三、实验内容1.需求分析首先，通过调查，教务管理系统中主要有四类用户，即学生用户，老师用户，教务管理员和系统管理员。
对应这些用户，其处理要求的主要的功能就是进行一系列的查询和各类数据的管理及维护。
其具体的处理要求如下：1）系统管理：实现系统管理人员对系统的管理，包括添加删除用户，更改密码，数据备份，数据还原，注销等功能。
2）教务管理：实现教务管理人员对系统的管理，包括课程安排，成绩审核，学生成绩管理，学生学籍管理等功能。
3）基本信息：实现显示学生和老师以及课程、班级、系别的基本信息（包括学生基本信息，教师基本信息，课程基本信息等）。
4）查询：包括实现学生查询，老师查询。
学生查询包括自己的基本信息，自己的课程，课表，成绩等，老师查询包括查询自己的信息，自己所带班的学生，自己的课表以及学生成绩等。
5）教师对成绩的录入以及自己信息的查询与维护。
其次，该教务管理系统中，结合以上用户种类以及其具体的处理功能要求，教务管理系统要具备以下信息要求：教务管理系统涉及的实体有：教师——工作证号、姓名、电话、工资、邮箱；学生——学号、姓名、性别、年龄、系代号等；班级——班号、最低总学分等；系——系代号、系名和系办公室电话等；课程——课序号、课名、学分、最大人数等；其中，这些实体之间的联系如下：每个学生都属于一个班，每个班都属于一个系，每个教师也都属于一个系。
每个班的班主任都由一名教师担任一名教师可以教多门课，一门课可以有几位主讲老师，但不同老师讲的同一门课其课序号是不同的（课序号是唯一的）。
一名同学可以选多门课，一门课可被若干同学选中。
一名同学选中的课若已学完，应该记录有相应成绩。
本单位学生、教师都有重名，工作证号、学号唯一。
另外，对于教务管理系统需求分析的安全性及完整性要求，首先，安全性方面，系统应对不同用户设置不同的权限，例如，学生用户不能随意更改自己的成绩信息等，从而保障数据库数据的安全。
其次，完整性方面，要求各种信息记录的完整性，关键信息记录内容不能为空；各种数据间相互的联系的正确性；相同的数据在不同记录中的一致性等。
2.概念结构设计在概念结构设计中，我采用自底向上设计概念结构的方法。
即第一步抽象数据并设计局部视图，第二步是集成局部视图，得到全局的概念结构。
该教务管理系统中的局部概念模型（E-R 图）如下： （1）、一个系内的关系模式图：（2）、选课系统的关系模式图：（3）、教师授课系统关系模型图：教班属最低总学分 班号 电话邮箱姓名 工作证号 系系办公室电话系名 系代号 属工学学号 年龄 性别 姓名 工资学课学号 年龄 性别姓名 名额学分课名 课序号 授教电话邮箱姓名工作证号课名额学分课名课序号工资系代号系代号选期中成绩平时成绩 最后成绩总评成绩期末成绩学生成绩查询视图：教师基本信息查询视图：另外，在创建表的同时还要注意有时需要创建两个表之间的级联，例如，此教务管理系统中SC表是参照学生表的学号和课程表的课号建立的，所以应该在SC表中建立其与Student表与Course表的级联;课程表又是参照教师表中的教师工作证号建立的，所以应该建立一个course表与教师teacher表的级联；班级表也是参照教师表中的教师工作证号建立的，所以也应该建立一个class表与教师teacher表的级联，这样当参照表发生改变时能同时在目标表中也发生相应改变，保证数据库中数据的正确有效性。
(2)创建用户该教务管理系统的主要用户有学生用户，老师用户，教务管理员和系统管理员等四种，所以需要创建这四种类型的用户，并授予其相应的权限。
对于学生用户就只能授予其查询学生基本信息student表的权限，老师相关信息视图V-teacher表的权限，查询学生成绩视图V_student表的权限，以及查询教师授课分配图DTC 表的权限；教师则具有查询学生基本信息Student表的权限，查询教师授课的视图DTC表的权限，还具有修改自己基本信息teacher表的权限，还有登记插入学生成绩信息即修改SC表的权限；教务管理员具有修改本系一切相关信息的权力，包括查询、删除、增添、修改，而系统管理员具有创建各种表、视图、触发器及存储过程并对各个用户进行相应授权的权力。
(3)使用触发器、存储过程等相关技术。
在教务管理数据库中，鉴于存在教师的退休及学生退学或毕业的情况，所以必然会有删除这些信息的操作，为了方便起见，所以有必要设计一个“删除教师信息”触发器以及“删除学生信息”触发器。
同时教务管理系统的数据库必然涉及到学生选课退课的问题，所以还需要设计一个关于选课退课系统的“选课”触发器与“删除选课”触发器。
“删除教师信息”与“删除学生信息”触发器的过程大致相同，就“删除教师信息”触发器来说，当删除教师时如果该教师在course表中有其授课信息，则当删除该教师后必会引起course表不可用，所以创建的该触发器当所要删除的教师号存在于course表里时，系统就应该给出“course表里有该教师的课程，请先删除course表里的记录！”的提示信息；就“删除学生信息”触发器来说，如果该学生退学时在选课表里仍有其选课记录，则当删除该学生信息后，在SC表里的相关选课记录必然成为无效，所以在此设置了一个判断即将退学的学生是否能删除的触发器，当退学学生在SC表里有该学生的记录时应该给出“SC表里有该学生的选课课程，请先删除SC表里的记录！”的信息提示。
“删除教师信息”触发器操作演示如下所示：“选课”与“删除选课”触发器的过程大致相同，就“选课”触发器来说当所选课程的课程号存在于课程表里时，系统就会将该条的选课记录添加到SC表中，并给出“选课成功”的信息提示，同样删除选课与此类似，当要删除的选课的课程号存在于SC表时，系统就会将该条的选课记录从SC表中删除，并给出“删除选课成功”的信息提示。
演示执行“选课”触发器的操作结果截图如下：这样在进行这些相关数据的处理时就可以通过触发器来控制，如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生变化。
除了创建相应触发器外，教务管理系统中还用到了数据库的存储过程，在本数据库中我建立了关于指定某个学号的“学生成绩查询”的存储过程，这样学生只需要输入自己的学号就可以查询出自己所选的科目以及其相应的成绩，存储过程的编译及其实现过程如下截图所示：其实，触发器就是一种特殊的存储过程，这样创建完成这些常用功能的存储过程后它们被编译后就能保存在数据库中，它们就像函数一样可以被反复调用，提高运行速度。
四、实验结果及分析这次课程设计我选择的是设计“教务管理系统”的数据库，通过本次自己独立的设计一个数据库，使我更清楚了设计数据库的步骤，通过一步步的分析、操作、实施，理论联系实际，把课堂上所学的知识综合的运用起来，不仅加深了对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,Salary char(10),Tel CHAR(20),Email char(20),Dno CHAR(10) UNIQUE,FOREIGN KEY (Dno) REFERENCES Sdept(Dno));-- 建立班级表CREATE TABLE class(Class CHAR(10) PRIMARY KEY,Ccredit BIGINT,Tno CHAR(10),Dno CHAR(10),FOREIGN KEY (Tno) REFERENCES Teacher(Tno) ON UPDATE CASCADE,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),Class CHAR(10),FOREIGN KEY (Class) REFERENCES Class(Class));-- 建立课程表CREATE TABLE course(Cno CHAR(10) PRIMARY KEY,Cname CHAR(20) UNIQUE,Credit SMALLINT,Tno CHAR(10),Cnum CHAR(10),FOREIGN KEY (Tno) REFERENCES Teacher(Tno)ON UPDATE CASCADE,);-- 建立选课表CREATE TABLE SC(Sno CHAR(10) ,Cno CHAR(10),Grade SMALLINT CHECK(Grade>=0 AND Grade<=100), Daigrade SMALLINT CHECK(Daigrade>=0 AND Daigrade<=100), Midbrade SMALLINT CHECK(Midbrade>=0 AND Midbrade<=100), Lasgrade SMALLINT CHECK(Lasgrade>=0 AND Lasgrade<=100), Fingrade SMALLINT CHECK(Fingrade>=0 AND Fingrade<=100),PRIMARY KEY (Sno, Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADEON UPDATE CASCADE,FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE CASCADEON UPDATE CASCADE,);--二、创建视图--教师授课的分配视图create view DTCasselect sdept.Dno ,Dname,Tname,Cnamefrom sdept,teacher,coursewhere sdept.Dno=teacher.Dno andteacher.Tno=course.Tno--学生的学号、成绩等信息查询的视图create view V_studentasselect student.Sno,Sname,cname,Credit,gradefrom student,SC,coursewhere student.Sno=SC.Sno ando=o--教师基本信息查询的视图create view V_teacherasselect Tname,Dname,Telfrom teacher,sdeptwhere teacher.Dno=sdept.Dno--三、创建索引--在教师表中系代号列上建立索引create unique index teadnoon teacher(Dno);--在学生表姓名列上建立索引create unique index stusnameon student(sname);--四、创建用户并对用户的权限做相应设置--创建学生用户create login u1with password='111'use 教务管理系统create user 学生for login u1--对学生用户进行授权grant selecton studentto 学生grant selecton V_studentto 学生grant selecton DTCto 学生grant selecton V_teacherto 学生--创建教师用户create login u2with password='111'use 教务管理系统create user 教师for login u2--对教师用户进行授权grant selecton studentto 教师grant selecton DTCto 教师grant select,updateon teacherto 教师grant select,insert,update(grade,daigrade,midbrade,lasgrade,fingrade)on SCto 教师--创建教务管理员用户create login u3with password='111'use 教务管理系统create user 教务处for login u3--对教务管理员用户进行授权grant select,update,insert,delete on teacherto 教务处grant select,update,insert,delete on studentto 教务处grant select,update,insert,delete on sdeptto 教务处grant select,update,insert,delete on classto 教务处grant select,update,insert,delete on courseto 教务处--五、创建触发器--删除教师信息触发器create trigger 删除教师信息on teacherfor deleteasif((select tno from deleted) in (select tno from course))beginprint'course表里有该教师的课程，请先删除course表里的记录！' rollbackend--演示删除教师信息触发器的操作deletefrom teacherwhere tno='04004'--删除学生信息触发器create trigger 删除学生信息on studentfor deleteasif((select sno from deleted)in (select sno from sc))beginprint'SC表里有该学生的选课课程，请先删除SC表里的记录！' rollbackend--演示删除学生信息触发器的操作deletefrom studentwhere sno='020312'--选课触发器create trigger 选课on SCfor insertasif((select cno from inserted) in (select Cno from course) ) beginprint'选课成功'end--演示执行“选课”触发器insertinto SC(sno,cno)values('020301','02')--删除选课触发器create trigger 删除选课on SCfor DELETEASif((select cno from deleted) in (select cno from sc)) beginprint'删除选课成功'end--演示执行“删除选课”触发器deletefrom scwhere sno='010117'and cno='03'--六、存储过程--查询成绩的存储过程CREATE PROCEDURE 查询成绩@sn char(10) asselect student.sno,sname,cname,Credit,gradefrom student,course,SCwhere student.sno=sc.sno ando=o andstudent.sno=@sn--查询学号为“010104”的学生成绩exec 查询成绩'010104'。
