数据库课程设计实验报告.doc

课程设计报告课程设计题目：学分管理系统数据库的设计与实现 学生姓名：邬佳娜 专 业：网络工程班 级：10211302 指导教师：游胜玉、黄燕 2011年12月01日东华理工大学一、实验题目：学分管理系统数据库的设计与实现描述：设计一个学分管理系统。假设每位学生必须完成基础课50学分，专业课50学分，选修课24学分，人文类课程8学分和实验性课程20学分才能毕业。实现下列功能：1 录入某位学生的学分信息2 给定学号，显示某位学生的学分完成情况；3 给定班号，显示该班所有学生的学分完成情况；4 给定学号，修改该学生的学分信息；5 按照某类课程的学分高低进行排序；6 给定学号，对该学生能否毕业进行确定。二、课程设计要求：1对各个系统进行系统功能需求分析2. 数据库设计分析阶段，进行详细的数据库需求分析，进行概念数据库的设计，画出数据库的E-R图（局部和整体E-R图）3. 设计出详细的逻辑数据库结构，将各个实体和联系转化为相应的二维表即关系模式，指定各个关系的主关键字和外部关键字，并对各个关系的约束加以限定4. 通过企业管理器或是查询分析器实现各个二维关系（建议最好用SQL代码实现），要求建立相关的索引5. 根据系统功能需求设计相应的查询视图6. 要求根据系统功能需求建立存储过程7. 根据功能需求建立相应的触发器以保证数据的一致性8. 通过建立用户和权限分配实现数据库一定的安全性，考虑数据库的备份与恢复（此内容选作）三、实验思路： 首先，要建立一个数据库来实现系统的要求。 其次，要建立E-R图来建立各个表，下面是E-R图和关系模式： 关系模式： 学生student（学号，姓名，性别，年龄，班级） 课程course（课程名，课程号，课程类型） 系department（系名，系主任） 教师teacher（教师号，性别，姓名） 拥有have（学号，系名） 学习study（学号，课程号，学分） 讲授teach（课程号，教师号） 属于belong（系名，教师号） E-R图： E-R图是根据实验要求建立，要求建立学生的个人信息和学分所得进行管理，同时附加系、任课教师表进行管理。系名系主任教师号姓名性别系属于教师拥有讲授课程选修学生学号姓名性别年龄班级学分课程号课程类型1m1mnmmn课程名最后，通过实验要求的功能进行各种设计的实现。四、实验代码create database 学分管理系统on (name=学分管理系统_data,filename=d:学分管理系统.mdf,size=10,maxsize=50)log on(name=学分管理系统_log,filename=d:学分管理系统.ldf,size=5,maxsize=25)create table student(sno char(20) constraint student_prim primary key,sn char(8), sex char(2),age int,class char(20)create unique index student的索引 on student(sno)insert into student values(1021130104,董益,男,20,10211301)insert into student values(1021130201,邬佳娜,女,21,10211302 )insert into student values(1021130213,汪小青,女,21,10211302)insert into student values(1021130232,施林甫,男,21,10211302)insert into student values(1021130312,袁颖清,女,22,10211303)insert into student values(1021130315,方亮,男,22,10211303)select*from studentcreate table course(cno char(20) primary key not null,cn char(20),score float,type char(20)create unique index course的索引 on course(cno)insert into course values(001,C语言,50,基础课)insert into course values(002,C+,24,选修课)insert into course values(003,高等数学,50,专业课)insert into course values(004,大学英语,50,基础课)insert into course values(005,C+实验,20,实验课)insert into course values(006,现代通信,24,选修课)insert into course values(007,数据库,50,专业课)insert into course values(008,数据库实验,20,实验课)insert into course values(009,近代史,8,人文类)select*from coursecreate table department(系名 char(6)constraint department_prim primary key,系主任 char(6)create unique index department的索引 on department(系名)insert into department values(软件,A) insert into department values(地测,B) insert into department values(土木,C) select * from departmentcreate table teacher(tno char(4)constraint teacher_prim primary key,tn char(6),sex char(2)create unique index teacher的索引 on teacher(tno)insert into teacher values(11,姜林,男)insert into teacher values(22,朱琳,男)insert into teacher values(33,洪小琴,女)insert into teacher values(44,高永平,男)insert into teacher values(55,游胜玉,女)insert into teacher values(66,危琪,女)insert into teacher values(77,蓝贤桂,男)select*from teacher create table have(sno char(20) not null constraint h_prim primary key constraint hh_fore foreign key references student(sno),系名 char(6)constraint h_fore foreign key references department(系名)create unique index have的索引 on have(sno)insert into have values(1021130104,地测)insert into have values(1021130201,软件)insert into have values(1021130213,软件)insert into have values(1021130232,软件)insert into have values(1021130312,土木)insert into have values(1021130315,土木)select*from havecreate table study(sno char(20)not null constraint s_fore foreign key references student(sno),cno char(20)constraint ss_fore foreign key references course(cno),score float,constraint ss_prim primary key(sno,cno)create unique index study的索引 on study(sno,cno)insert into study values(1021130104,001,50)insert into study values(1021130104,002,22)insert into study values(1021130104,003,49)insert into study values(1021130104,004,30)insert into study values(1021130104,005,20)insert into study values(1021130104,006,38)insert into study values(1021130104,007,50)insert into study values(1021130104,008,20)insert into study values(1021130104,009,8)insert into study values(1021130201,001,55)insert into study values(1021130201,002,20)insert into study values(1021130201,003,45)insert into study values(1021130201,004,75)insert into study values(1021130201,005,20)insert into study values(1021130201,006,26)insert into study values(1021130201,007,60)insert into study values(1021130201,008,20)insert into study values(1021130201,009,9)insert into study values(1021130213,001,65)insert into study values(1021130213,002,20)insert into study values(1021130213,003,55)insert into study values(1021130213,004,40)insert into study values(1021130213,005,20)insert into study values(1021130213,006,28)insert into study values(1021130213,007,55)insert into study values(1021130213,008,20)insert into study values(1021130213,009,9)insert into study values(1021130232,001,45)insert into study values(1021130232,002,25)insert into study values(1021130232,003,70)insert into study values(1021130232,004,35)insert into study values(1021130232,005,20)insert into study values(1021130232,006,24)insert into study values(1021130232,007,55)insert into study values(1021130232,008,20)insert into study values(1021130232,009,9)insert into study values(1021130312,001,60)insert into study values(1021130312,002,36)insert into study values(1021130312,003,49)insert into study values(1021130312,004,50)insert into study values(1021130312,005,20)insert into study values(1021130312,006,20)insert into study values(1021130312,007,40)insert into study values(1021130312,008,20)insert into study values(1021130312,009,9)insert into study values(1021130315,001,30)insert into study values(1021130315,002,24)insert into study values(1021130315,003,35)insert into study values(1021130315,004,45)insert into study values(1021130315,005,20)insert into study values(1021130315,006,18)insert into study values(1021130315,007,38)insert into study values(1021130315,008,20)insert into study values(1021130315,009,8)select*from studycreate table teach(cno char(20)constraint t_fore foreign key references course(cno),tno char(4)constraint tt_fore foreign key references teacher(tno),constraint t_prim primary key(tno,cno)create unique index teach的索引 on teach(tno,cno)insert into teach values(001,11)insert into teach values(002,44)insert into teach values(003,22)insert into teach values(004,33)insert into teach values(005,11)insert into teach values(006,77)insert into teach values(007,55)insert into teach values(008,55)insert into teach values(009,66)select*from teachcreate table belong(tno char(4)constraint b_prim primary keyconstraint b_fore foreign key references teacher(tno),系名 char(6)constraint bb_fore foreign key references department(系名)create unique index belong的索引 on belong(tno)insert into belong values(11,软件)insert into belong values(22,土木)insert into belong values(33,地测)insert into belong values(44,软件)insert into belong values(55,软件)insert into belong values(66,土木)insert into belong values(77,地测)select* from belongcreate view showas select study.sno,study.score,student.sn,o,,course.typefrom student,study,coursewhere study.sno=student.sno and o=o/*录入某位学生的学分信息*/create trigger 插入on student for insertas select*from studentinsert into student values(1021130101,徐刚,男,22,10211301)-select*from student/*给定学号，显示某位学生的学分完成情况*/create view 学分完成情况as select*from studywhere sno=1021130315select cno,scorefrom 学分完成情况where sno=1021130315/*给定班号，显示该班所有学生的学分完成情况*/create procedure 班级的学分完成情况as select student.sno,student.sn,student.class,o,study.score,ofrom student,study,coursewhere class=10211302and study.sno=student.sno and o=oexec 班级的学分完成情况/*给定学号，修改该学生的学分信息*/create trigger 修改on studyfor updateas select o,study.scorefrom studywhere study.sno=(select sno from inserted)update studyset score=70where cno=001and sno=1021130315/*update studyset score = score+2where (sno=1021130213and cno=001)select*from study*/*按照某类课程的学分高低进行排序*/create view 专业课学分完成情况as select study.score,study.sno,o,,course.typefrom study,coursewhere o=oselect sno,cno,cn,score,typefrom 专业课学分完成情况where type=专业课order by score desc/*给定学号，对该学生能否毕业进行确定*/create view 学生毕业问题 as select study.sno,student.sn,course.type,sum(study.score)as scorefrom study,student,coursegroup by study.sno,student.sn,course.typecreate procedure pd(sno char(10)asdeclare jc floatdeclare rw floatdeclare sy floatdeclare xx floatdeclare zy floatdeclare x intset x=0select jc=score from 学生毕业问题 where sno = sno and type =基础课if jc=50beginset x=1select