学生信息管理系统(SQL数据库技术).doc

四个表的E-R实体模型图分析：教师学生 学教 1 N N M这四个表的总的实体-关系图：班级号学号号学生信息表出生日期号学号号性别号出生日期号教师姓名号成绩表号部门号 成绩号教师信息表课程名称号课程号号 职称号教师编号号课程表设计数据表：通过E-R图分析，现在已经对数据库有一个很清楚的认识了。 在此学生成绩信息中有4个表需要建立学生信息表（student）包括学号（sno）、姓名（sname）、性别（Ssex）、班级（class）、出生日期（sbirth）教师信息表( teacher)包括教师编号（tno）、姓名（tname）、性别（Tsex）、部门（depart）、职称（prof）、出生日期（tbirth）成绩表（score）包括学号（sno）、课程号（cno）、成绩（degree）课程信息表（course）包括课程号（cno）、课程名称（cname）、教师编号（tno） 五、表结构 在teacher表中，以教师编号tno为主键，对其进行惟一性约束。在Course表中，以课程号为主键，对其进行惟一性约束。 1、Student表：snoChar(8)不允许为空snameChar(10)不允许为空SsexChar(2)不允许为空sbirthDatetime(8)允许为空classChar(4)不允许为空typeChar(7)允许为空score表结构：snoChar(8)不允许为空cnoChar(5)不允许为空degreeFloat(8)不允许为空Course表：以课程号为主键，通过这个主键来对这个表进行唯一性约束。cno（主键）Char(5)不允许为空cnamevarchar(10)不允许为空tnochar(3)不允许为空Teacher表结构：Tno（主键）Char(5)不允许为空tnamevarchar(10)不允许为空departVarchar(8)不允许为空tsexChar(2)不允许为空tbirthdatatime允许为空profChar(6)允许为空创建规则（1）、创建一个degree_rule规则create rule degree_rule asvalues0把此规则绑定到score表中degree列exec sp_bindrule degree_rule,score.degree在向成绩表中添加记录时，如果成绩degree0，则插入不成功。(2)、创建一个tel_rule规则create rule tel_ruleas value like 0-9 0-9 0-9 0-9 0-9 0-9 0-9 0-9把此规则绑定到teacher表中tel列exec sp_bindrule tel_rule,teacher.tel在向教师表中添加记录时，如果电话号码不是0-9的数字，则插入不成功。10、创建存储过程（1）、创建一个存储过程，来显示成绩表中的课程号在课程表中并且所任教师性别为男、所在部门是计算机系的成绩表中的列create proc student_11as select * from score where cno in (select cno from course ,teacher where course.tno=teacher.tno and depart=计算机系 and tsex=男)调用此存储过程Execstudent_11 （2）、创建一个带输入参数的存储过程，调用此存储过程时，给出一个学生名，显示出此学生的学号，姓名，所学课程号，课程名称和对应的成绩create proc student_namesname varchar(10)as select student.sno,sname,o,degree cname from student,score,course where student.sno=score.sno and o=o and sname=sname调用此存储过程，（此例是输出姓名为历史的学生的信息）exec student_name 历史（3）、创建一个存储过程，在执行此存储过程时，如果没有给出参数（学生姓名），则输入全部的学生的学号，姓名，班级，任课教师编号及其姓名，所学课程名称和成绩，如果有，则显示此学生的以上信息。exec student_teacher（没有实参） exec student_teacher 历史 （查询姓名为历史的学生的选课信息和成绩）（4）、创建一个存储过程，传递一个学生姓名，先判断此学生是否有邮箱，如果有，则显示此学生的姓名，邮箱地址，学号，班级；如果没有的话，输出此句话the semail is emptycreate proc student_emailsname varchar(10)asbegin if (select semail from student where sname=sname) is nullbegin printthe semail is empty endelse select sname,semail,sno,class from student where sname=sname end调用此存储过程 exec student_email super当给出姓名的那个学生没有邮箱地址时,则会显示如下内容。 exec student_email dfdf11、触发器 （1）、创建一个触发器，来检查学生的邮箱地址是否相同，如果相同，输出inserting fail，并且回滚事务；如果不相同，则插入成功。create trigger studentinserton studentafter insertas if (select semail from inserted where semail in (select semail from student) is not nullbegin print inserting fail rollback transactionendelse printinsering succeed向学生信息表中插入一条记录，检验是否成功插入insert into student values(114,lengbing,女,1985-12-12,11,,一般)（2）、在成绩表中建立一个触发器，当向表中添加记录时，此学生的成绩都乘以1.2create trigger scoreupdate on scoreafter insertas update score set degree=degree*1.2 from score where sno in (select sno from inserted )向表中插入一条记录，检验触发器是否有用。insert into score values(108,01,56)（3）、在成绩表建立一个触发器，在向表中插入记录时，检验插入的课程号是否在课程表中的课程号的范围之内。如果在，则插入成功；否则，提示信息没有这门课程，回滚事务。create trigger course_scoreon scoreafter insertas if (select cno from inserted where cno in(select cno from course) is null begin print没有这门课程 rollback transaction end向表中添加一条记录，进行验证。insert into score values(108,06,60)12、自定义函数（1）、创建一个用户自定义函数，输出与指定的学生同班的学生个数create function studentcount(sno char(5)returns intbegineclare counter intselect counter=count(*) from student where class=(select class from student where sno=sno)return counterend调用此自定义函数（本例是查找与学号102同班的学生个数）declare a intset a=dbo.studentcount(102)print convert(char(3),a)（2）、创建一个用户自定义函数，用于输出同一个班级中的学生信息create function studentclass(class char(5)returns tablereturn(select * from student where class=class)调用自定义函数（本例是输出12班的学生信息）select * from studentclass(12)（3）、创建一个自定义函数，把某一学生所学课程名称，课程号及其成绩插入一个临时表中显示出来。create function studentscore(sno varchar(5)returns student_score table(sno char(5), sname varchar(10), cno char(5), cname varchar(10), degree float)begin insert student_scoreselect student.sno,sname,o,cname,degree from student,score,coursewhere student.sno=score.sno and o=o and student.sno=sno returnend查看学号为103的学生的各科成绩。select * from studentscore(103)13、建立索引（1）、在学生信息表邮箱列上创建一个惟一性的非聚簇索引create u