数据库实验报告.docx

1、数据库实验报告数据库实验报告组长：_ _组员：_班级：_ _指导教师：_ _ 组长：主要任务：1. 分析题意，画出E-R图，将E-R图转换为关系模式并进行模式优化。2. SQL Server 2008环境下编写SQL代码,创建视图、触发器、存储过程和游标。组员：主要任务：1. 根据优化后的关系模式创建基本表，并填充数据。2. 参与优化模式讨论。3. 撰写实验报告。一.题目：设计学生管理系统需求语义： 今要建立关于系、学生、班级、学会等诸信息的一个关系数据库。一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参

2、加某学会有一个入会年份。描述各个实体的属性（加下划线者为主码）如下： 学生：学号、姓名、年龄、系名、班号、宿舍区。 班级：班号、专业名、入校年份、系名、人数。 系：系号、系名、系办公室地点、人数。 学会：学会号、学会名、成立年份、地点。要完成的任务：1. 画出E-R图。2. 把E-R图转为关系模式，并优化。3. 根据关系模式创建数据库。表名和属性名用英文，属性的数据类型根据上面的描述自己定义。4. 创建一个视图，能显示每个学会的学会名，学生数(实际不存在，也不能增加)。5. 创建一个触发器，能根据每个班的学生变动情况自动增减班级表和系表的人数字段的值。6. 创建一个存储过程，实现如下功能：给定

3、一个班的旧班号和新班号，把所有相关表中此班的旧班号改为新班号，并返回此班的人数（使用输出参数）。7. 编写一段脚本，使用游标完成如下功能：确定系表中人数字段的值与实际学生数是否相符。如果不相符，把人数字段的值改为实际数，并在窗口打印此系的系号、系名、原人数、实际人数。二.概念结构设计原始E-R图：uyearunameuplaceunobelongsdnamesdorsclnosnosnamesagelivedeptmajorclassstudentstuunion joinyearsjoin m n pcltotal qclyear 1clmjnamebelongopenclno 1mname

4、cldname 1 1dnoestablish ndnamedorplace 1dwkplacedormitory 1 1 dtotal消除冲突和冗余后的E-R图uyearuplaceunameunostuunionjoinyearsjoin mdorplacedormitorydtotaldnamedwkplacednoestablishmjnameclyeaarcltotalclnoopenblongsnosnamesagelivedeptmajorstudentnp1class11n1 1 1三逻辑结构设计stuunion(uno,uname,uyear,uplace)student(s

5、no,sname,sage,clno)sjoin(uno,sno,joinyear)class(clno,clyear,cltotal)major(mjname,clno,dnl)dept(dno,dname,dwkplace,dtotal)dormitory(dno,dorplace)经检验，以上7个关系模式均满足第三范式要求四应用程序中遇到的问题及解决方法根据设计好的关系模式，即可在SQL Sever 2008环境下编程。前面创建表、视图、触发器的过程都比较简单，创建存储过程的时候遇到了一点问题，班号更改以后无法把相关表中的班号也修改了，最后发现原因是student和major两个从表无法

6、级联更新，于是又给这两个表增添了随着class表级联更新的属性，问题迎刃而解。最后创建游标的过程有点复杂，不过整体还是比较顺利的。五总结 这次上机实验收获还是挺多的，上课的时候学到了理论，当真正把理论运用到实践中的时候发现其实没那么容易。虽然只是设计了一个学生管理系统，这个系统规模不大，数据也不多，但是当真正设计这个数据库的时候过程是比较曲折的。我觉得重点就是根据需求设计E-R图，设计出E-R图后还要消除冲突和冗余，然后将E-R图转换为关系模式，还要对关系模式规范化，达到一个非常好的效果。此次上机实验也锻炼了同学之间的合作能力，我积极和同学探讨问题，也向老师请教问题，学到了好多课本上没有学到的

7、知识。附录：代码:-student 表create table student (sno varchar(8) primary key,sname varchar(20) not null unique,sage int,clno char(6) references class(clno);-class表create table class(clno char(6) primary key,clyear int,cltotal int)-major表create table major(mjname varchar(20) primary key,clno char(6) reference

8、s class (clno),dno char(2) references dept(dno)-dept表create table dept(dno char(2) primary key,dname varchar(20) not null unique,dworkplace varchar(20),dtotal int )-dormitory表create table dormitory (dorplace varchar(20),dno char(2) references dept(dno)-stuunion表create table stuunion(uno char(6) prim

9、ary key,uname varchar(20) not null unique,uyear int,uplace varchar(20)-sjoin表create table sjoin(uno char(6) references stuunion(uno),sno varchar(8) references student(sno),joinyear int,primary key(uno,sno)-创建视图create view uname_utotal(uname,utotal)asselect uname,count(*)from stuunion,sjoin where stu

10、union.uno=sjoin.unogroup by stuunion.unamewith check option;-创建触发器create trigger insert_cltotal_dtotal on studentfor insertas declare a char(6);declare c char(2);set a=(select class.clnofrom class,inserted where class.clno=inserted.clno );update classset cltotal=cltotal+1where clno=a;set c=(select d

11、ept.dnofrom major,dept,inserted where major.dno=dept.dno and major.clno=inserted.clno );update deptset dtotal=dtotal+1where dno=c;create trigger delete_cltotal_dtotal on studentfor deleteas declare a char(6)declare c char(2)set a=(select class.clno;from class,deleted; where class.clno=deleted.clno )

12、;update classset cltotal=cltotal-1where clno=a;set c=(select dept.dnofrom major,dept,deleted where major.dno=dept.dno and major.clno=deleted.clno );update deptset dtotal=dtotal-1where dno=c;-创建存储过程create procedure pro_modifycnooldcno char(6), newcno char(6), classtotal int outputasupdate classset cl

13、no=newcnowhere clno=oldcno;update studentset clno=newcnowhere clno=oldcno;update major set clno=newcnowhere clno=oldcno;set classtotal=(select cltotal from class where clno=newcno)declare x int exec pro_modifycno oldcno=011221,newcno=051221,classtotal=x output print xalter table student add constrai

14、nt c1 foreign key(clno) references class(clno) on update cascadealter table major add constraint c2 foreign key(clno) references class(clno) on update cascade-建立游标declare Dep varchar(10);declare Class varchar(10);drop table Changecreate table Change( dno char(10) primary key, dtotal int);insert into

15、 Change(dno,dtotal) select dept.dno,dept.dtotal from dept declare StuNumCheck cursor for select dno,clno from majoropen StuNumCheckupdate deptset dtotal = 0fetch next from StuNumCheck into Dep,Classwhile FETCH_STATUS = 0beginupdate deptset dtotal =dtotal + ( select COUNT(*) from student where clno = Class )where dept.dno = Dep;fetch next from StuNumCheck into Dep,Classendselect dept.dno,dept.dname,Change.dtotal,dept.dtotalfrom Change,deptwhere Change.dno = dept.dnoclose StuNumCheckdeallocate StuNumCheck