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

四个表的E-R实体模型图分析：

这四个表的总的实体-关系图：

设计数据表：通过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）、创建一个degree_rule规则

create rule degree_rule

as

@values>0

把此规则绑定到score表中degree列

exec sp_bindrule 'degree_rule','score.degree'

在向成绩表中添加记录时，如果成绩degree<0，则插入不成功。

(2)、创建一个tel_rule规则

create rule tel_rule

as

@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_11

as

select * from score where cno in (select cno from course ,teacher where course.tno=teacher.tno

and depart='计算机系' and tsex='男')

调用此存储过程

Exec student_11

（2）、创建一个带输入参数的存储过程，调用此存储过程时，给出一个学生名，显示出此学生的学号，姓名，所学课程号，课程名称和对应的成绩

create proc student_name

@sname 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 empty' create proc student_email

@sname varchar(10)

as

begin

if (select semail from student where sname=@sname) is null

begin

print'the semail is empty'

end

else

select sname,semail,sno,class from student where sname=@sname

end

调用此存储过程

exec student_email 'super'

当给出姓名的那个学生没有邮箱地址时,则会显示如下内容。

exec student_email dfdf'

11、触发器

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

create trigger studentinsert

on student

after insert

as

if (select semail from inserted where semail in (select semail from student)) is not null begin

print 'inserting fail'

rollback transaction

end

else

print'insering succeed'

向学生信息表中插入一条记录，检验是否成功插入

insert into student values('114','lengbing','女','1985-12-12','11','','一般')

（2）、在成绩表中建立一个触发器，当向表中添加记录时，此学生的成绩都乘以1.2 create trigger scoreupdate on score

after insert

as

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_score

on score

after insert

as

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))