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

四个表的E-R 实体模型图分析：1 NN 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表：sno Char(8) 不允许为空sname Char(10) 不允许为空Ssex Char(2) 不允许为空sbirth Datetime(8) 允许为空class Char(4) 不允许为空type Char(7) 允许为空score表结构：sno Char(8) 不允许为空cno Char(5) 不允许为空degree Float(8) 不允许为空Course表：以课程号为主键，通过这个主键来对这个表进行唯一性约束。

cno（主键）Char(5) 不允许为空cname varchar(10) 不允许为空tno char(3) 不允许为空Teacher表结构：Tno（主键）Char(5) 不允许为空tname varchar(10) 不允许为空depart Varchar(8) 不允许为空tsex Char(2) 不允许为空tbirth datatime 允许为空prof Char(6) 允许为空创建规则（1）、创建一个degree_rule规则create rule degree_ruleas@values>0把此规则绑定到score表中degree列exec sp_bindrule 'degree_rule','score.degree'在向成绩表中添加记录时，如果成绩degree<0，则插入不成功。

(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_11asselect * from score where cno in (select cno from course ,teacher where course.tno=teacher.tnoand depart='计算机系' and tsex='男')调用此存储过程Exec student_11（2）、创建一个带输入参数的存储过程，调用此存储过程时，给出一个学生名，显示出此学生的学号，姓名，所学课程号，课程名称和对应的成绩create proc student_name@sname varchar(10)asselect student.sno,sname,o,degree cname from student,score,coursewhere student.sno=score.sno and o=o and sname=@sname调用此存储过程，（此例是输出姓名为历史的学生的信息）exec student_name '历史'（3）、创建一个存储过程，在执行此存储过程时，如果没有给出参数（学生姓名），则输入全部的学生的学号，姓名，班级，任课教师编号及其姓名，所学课程名称和成绩，如果有，则显示此学生的以上信息。

exec student_teacher（没有实参）exec student_teacher '历史' （查询姓名为历史的学生的选课信息和成绩）（4）、创建一个存储过程，传递一个学生姓名，先判断此学生是否有邮箱，如果有，则显示此学生的姓名，邮箱地址，学号，班级；如果没有的话，输出此句话'the semail is empty' create proc student_email@sname varchar(10)asbeginif (select semail from student where sname=@sname) is nullbeginprint'the semail is empty'endelseselect sname,semail,sno,class from student where sname=@snameend调用此存储过程exec student_email 'super'当给出姓名的那个学生没有邮箱地址时,则会显示如下内容。

exec student_email dfdf'11、触发器（1）、创建一个触发器，来检查学生的邮箱地址是否相同，如果相同，输出'inserting fail'，并且回滚事务；如果不相同，则插入成功。

create trigger studentinserton studentafter insertasif (select semail from inserted where semail in (select semail from student)) is not null beginprint 'inserting fail'rollback transactionendelseprint'insering succeed'向学生信息表中插入一条记录，检验是否成功插入insert into student values('114','lengbing','女','1985-12-12','11','lengbingssh@','一般') （2）、在成绩表中建立一个触发器，当向表中添加记录时，此学生的成绩都乘以1.2 create trigger scoreupdate on scoreafter insertasupdate 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 insertasif (select cno from inserted where cno in(select cno from course)) is nullbeginprint'没有这门课程'rollback transactionend向表中添加一条记录，进行验证。

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)begininsert @student_scoreselect student.sno,sname,o,cname,degree from student,score,coursewhere student.sno=score.sno and o=o and student.sno=@snoreturnend查看学号为103的学生的各科成绩。