学分管理系统数据的设计思想和代码.doc

数据库课程设计报告课程设计题目： 学分管理系统 学生姓名 尹烁恒专 业 软件工程（嵌入式）班 级 1321802学 号 201320180212指导教师 邓薇老师 描述：设计一个学分管理系统假设每位学生必须完成基础课50学分，专业课50学分，选修课24学分，人文类课程8学分和实验性课程20学分才能毕业。实现下列功能：1 录入某位学生的学分信息；2 给定学号，显示某位学生的学分完成情况；3 给定班号，显示该班所有学生的学分完成情况；4 给定学号，修改该学生的学分信息；5 按照某类课程的学分高低进行排序；6 给定学号，对该学生能否毕业进行确定。一自己的分析（需求分析）：1.功能1需要用到触发器和存储过程，触发器的目的是为了在录入一个学号的学分，这个学号必须存在在student表里，否则不允许录入，而存储过程就像是一个功能的集成，其实录入可以直接录入，不一定需要建立存储过程。2.功能2、3、4、6都是利用存储过程，用带参数的存储过程进行实现，S_NO是where中的判断条件；对于毕业的条件就是各个临时表中的total同时大于等于所需学分就可以毕业。3.功能5，因为课程类包括很多，可以建立视图来查看一个课程类，如基础类的信息，但是不能排序，order by不能用于视图，所以建立临时表来查询显示某一课程类的排序。二概念设计E-R模型课程号 学分 学号 课程课程类型 姓名 选课学生课程名 班级关系模式学生（学号，姓名，班级）课程（课程号，课程名，课程类型）选课（学号，课程号，学分）三逻辑设计阶段下面就是3张基本表 Course表 Student表 Grade表四数据库实施阶段1. 创建数据库create database s12. 创建数据表create table student(s_no char(6) primary key,sname char(6) not Null,class_no char (10)create table course(course_no char(10) primary key,course_name char(10) NOT NULL,coursetype char(10) create table grade(s_no char(6),course_no char(10),score int,constraint sc1 primary key (s_no,course_no)3. 录入数据-对于student的数据填充insert into student values(01,彭勇,class01)insert into student values(02,罗鑫,class01)insert into student values(03,郭利新,class02)insert into student values(04,余丽阳,class02)insert into student values(05,汪志强,class03)insert into student values(06,简小慧,class03)-对于course的数据填充insert into course values(course10,语文,基础课)insert into course values(course11,数学,基础课)insert into course values(course12,体育,选修课)insert into course values(course13,形式与政策,选修课)insert into course values(course14,人机交互,专业课)insert into course values(course15,数据结构,专业课)insert into course values(course16,音乐,人文课)insert into course values(course17,美术,人文课)insert into course values(course18,关系数据库,实验课)insert into course values(course19,计算机原理,实验课)-对于学分的填充insert into grade values(01,course10,27)insert into grade values(01,course11,28)insert into grade values(01,course12,13)insert into grade values(01,course13,14)insert into grade values(01,course14,22)insert into grade values(01,course15,29)insert into grade values(01,course16,6)insert into grade values(01,course17,7)insert into grade values(01,course18,11)insert into grade values(01,course19,13)-学号的各科学分insert into grade values(02,course10,26)insert into grade values(02,course11,29)insert into grade values(02,course12,16)insert into grade values(02,course13,11)insert into grade values(02,course14,18)insert into grade values(02,course15,19)insert into grade values(02,course16,5)insert into grade values(02,course17,2)insert into grade values(02,course18,17)insert into grade values(02,course19,19)-学号的各科学分insert into grade values(03,course10,21)insert into grade values(03,course11,24)insert into grade values(03,course12,16)insert into grade values(03,course13,10)insert into grade values(03,course14,21)insert into grade values(03,course15,17)insert into grade values(03,course16,4)insert into grade values(03,course17,8)insert into grade values(03,course18,14)insert into grade values(03,course19,19)-学号的各科学分insert into grade values(04,course10,30)insert into grade values(04,course11,31)insert into grade values(04,course12,21)insert into grade values(04,course13,22)insert into grade values(04,course14,26)insert into grade values(04,course15,28)insert into grade values(04,course16,9)insert into grade values(04,course17,9)insert into grade values(04,course18,18)insert into grade values(04,course19,17)-学号的各科学分insert into grade values(05,course10,10)insert into grade values(05,course11,11)insert into grade values(05,course12,11)insert into grade values(05,course13,12)insert into grade values(05,course14,16)insert into grade values(05,course15,18)insert into grade values(05,course16,5)insert into grade values(05,course17,4)insert into grade values(05,course18,14)insert into grade values(05,course19,15)insert into grade values(06,course10,13)insert into grade values(06,course11,14)insert into grade values(06,course12,15)insert into grade values(06,course13,16)insert into grade values(06,course14,19)insert into grade values(06,course15,12)insert into grade values(06,course16,6)insert into grade values(06,course17,1)insert into grade values(06,course18,12)insert into grade values(06,course19,13)4. 创建视图和5张临时表-五个视图分别显示一个学生的各个课程类，没有排序create view 基础课(s_no,基础类课程总学分)asselect s_no,SUM(score)from grade,coursewhere grade.course_no=course.course_no and coursetype=基础课group by s_noselect*from 基础课create view 专业课(s_no,专业类课程总学分)asselect s_no,SUM(score)from grade,coursewhere grade.course_no=course.course_no and coursetype=专业课group by s_noselect*from 专业课create view 选修课(s_no,选修类课程总学分)asselect s_no,SUM(score)from grade,coursewhere grade.course_no=course.course_no and coursetype=选修课group by s_noselect*from 选修课create view 人文课(s_no,人文类课程总学分)asselect s_no,SUM(score)from grade,coursewhere grade.course_no=course.course_no and coursetype=人文课group by s_noselect*from 人文课create view 实验课(s_no,实验类课程总学分)asselect s_no,SUM(score)from grade,coursewhere grade.course_no=course.course_no and coursetype=实验课group by s_noselect*from 实验课-建立的张临时表用于显示各类的总学分排序select s_no,SUM(score) as totalinto 基础课的学分表from grade,coursewhere grade.course_no=course.course_no and coursetype=基础课group by s_noorder by total select *from 基础课的学分表select s_no,SUM(score) as totalinto 专业课的学分表from grade,coursewhere grade.course_no=course.course_no and coursetype=专业课group by s_noorder by totalselect* from 专业课的学分表select s_no,SUM(score) as totalinto 选修课的学分表from grade,coursewhere grade.course_no=course.course_no and coursetype=选修课group by s_noorder by total select* from 选修课的学分表select s_no,SUM(score) as totalinto 人文课的学分表from grade,coursewhere grade.course_no=course.course_no and coursetype=人文课group by s_noorder by total select* from 人文课的学分表-视图不能排序，创建临时表排序，和查看select s_no,SUM(score) as totalinto 实验课的学分表from grade,coursewhere grade.course_no=course.course_no and coursetype=实验课group by s_noorder by total /*order by 不能用于视图当中*/select* from 实验课的学分表5. 创建触发器-触发器create trigger insertscoreon gradefor insertasif exists(select * from insertedwhere s_no in(select s_no from student )print 添加成功elsebeginprint student表中没有该学号，拒绝插入rollbackend6. 创建存储过程-存储过程-插入一个学生的学分(ok)create procedure insertscore1(s_no char(6), course_no char(10),score int)asinsert into grade values(s_no,course_no,score)exec insertscore1 06,course21,18-根据学号显示该学分（OK）create procedure showscore(s_no char(6),course_no char(10)output,score int output)asselect course_no=course_no,score=scorefrom gradewhere(s_no=s_no )-给一个班号显示所有学生的学分(ok)create procedure showallscore(class_no char(10)as select grade.s_no,course_no,scorefrom grade,studentwhere class_no =class_no and student.s_no=grade.s_no-根据学号修改create procedure updatescore(s_no char(6),course_no char(10),score int)asbeginupdate gradeset score=scorewhere s_no=s_no and course_no=course_no -这里之前我把course_no写到set里了,course_no和s_no是主键，应该做查询，我们只是修改学分而已，只在s