一个完整的数据库示例--说明

一、表的结构及完整性约束新建一个数据库jxsk，包括S、C、SC、T、TC五个表，结构如下：C表：S表：SC表：T表：TC表：二、安全性控制及视图机制1、三类角色：depart、teacher、studentdepart的权限：teacher的权限：student的权限：2、有2个院系用户：d_jsj，d_xx，同属于depart角色。

有1个教师用户：t ，属于teacher 角色。

有一个学生用户：s，属于student角色。

3、创建计算机系教师视图t_view_jsj、计算机系学生视图s_view_jsj，并授予d_jsj 用户在这两个视图上的select、delete、update、insert权限。

计算机系教师视图t_view_jsj：create view t_view_jsjasselect tno,tn,sex,age,prof,sal,comm,deptfrom twhere dept='计算机'with check option授予d_jsj用户在计算机系教师视图t_view_jsj 上的select、delete、update、insert 权限：grant select,update,delete,insert on t_view_jsj to d_jsj计算机系学生视图t_view_jsj：create view s_view_jsjasselect sno,sn,sex,age,dept,resume,nativefrom swhere dept='计算机'with check option授予d_jsj用户在计算机系学生视图s_view_jsj 上的select、delete、update、insert 权限：grant select,update,delete,insert on s_view_jsj to d_jsj ……4、创建一个视图，显示学号,姓名,院系,课程名,成绩。

create view score_view(学号,姓名,院系,课程名,成绩)asselect s.sno,sn,dept,cn,scorefrom s,sc,cwhere s.sno=sc.sno and o=o三、完整性控制--触发器、规则1、要求当删除C表中某课程信息时，同时删除SC和TC中与此课程相关的记录。

create trigger c_delete_trigger on cafter deleteasdelete from scwhere cno in(select cno from deleted)delete from tcwhere cno in(select cno from deleted)go2、为T创建一触发器，当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元，从“副教授”晋升为“教授”时，岗位津贴自动增加900元。

create trigger t_update_trigger on tafter updateasif update(prof)begindeclare @prof_old char(10),@prof_new char(10)select @prof_old=prof from deletedselect @prof_new=prof from insertedif @prof_old='讲师'and @prof_new='副教授'update t set comm=comm+500 where tno=(select tno from inserted)if @prof_old='副教授'and @prof_new='教授'update t set comm=comm+900 where tno=(select tno from inserted)end3、创建一个规则sexrule，指定变量@sex的取值只能为'男'或'女' create rule sexruleas @sex in('男','女')绑定T表的sex、S表的sex到sexrule规则：exec sp_bindrule'sexrule','s.sex'exec sp_bindrule'sexrule','t.sex'四、索引1、索引的分类：●聚集索引：primary key 自动创建聚集索引●非聚集索引2、使用索引的准则：1）适合建索引的属性列●主码所在的属性列●外码所在的列或在连接查询中经常使用的属性列●按关键字的范围值进行搜索的属性列●按关键字的排序顺序访问的属性列2）不适合建索引的属性列●在查询中很少涉及的属性列●包含较少的唯一值●更新性能比查询性能更重要的属性列●有text、ntext、image数据类型定义的属性列3、为s表在dept属性列上创建索引create index s_dept_index on s(dept)……五、自定义数据类型、自定义函数1、自定义数据类型Idnum：学号、教师编号都是char（6），not null。

exec sp_addtype Idnum,'char(6)','not null'2、自定一个标量函数，用于查询某个同学某门课程的成绩。

create function score_fun(@sname char(8),@cname char(10)) returns tinyintasbegindeclare @cj tinyintselect @cj=scorefrom s,sc,cwhere s.sno=sc.sno and o=o andsn=@sname and cn=@cnamereturn @cjend使用该函数，查询'李忘'选修'计算机基础'的成绩。

select dbo.score_fun('李忘','计算机基础')3、创建一个单语句表值函数。

create function cname_score(@cname char(10))returns tableasreturn(select sn,scorefrom s,sc,cwhere s.sno=sc.sno and o=o and cn=@cname) go使用该函数，查询选修了“计算机基础”的学生姓名、成绩。

select sn 姓名,score 成绩from cname_score('计算机基础')4、创建一个多语句表值函数，根据教师姓名查询该教师所讲授课程名、学生人数、平均成绩、最高成绩、最低成绩。

create function tname_fun(@tname char(10))returns @tname_score_tab table(cn char(10),rs int,pjf numeric(6,1),zgf numeric(6,1),zdf numeric(6,1))asbegininsert into @tname_score_tabselect cn,count(*),avg(score),max(score),min(score)from sc,tc,t,cwhere o=o and t.tno=tc.tno and o=o and tn=@tnamegroup by cnreturnend使用该函数，查询‘徐红霞’教师所授课信息。

select*from tname_fun('徐红霞')六、存储过程和游标1、利用课程名查询选修该课程的学生姓名、系别、成绩。

create procedure cn_score_pro@cname char(10)asselect sn 姓名,dept 系别,score 成绩from s,sc,cwhere s.sno=sc.sno and o=o and cn=@cnamego使用该存储过程，查询选修“计算机基础”的相关信息。

exec cn_score_pro '计算机基础'2、统计某门课程成绩为60以下，60～80（含60、80分），80以上各有多少人。

create procedure cn_tjscore_pro@cname char(10),@n1 int output,@n2 int output,@n3 int outputasdeclare @cj tinyintselect @n1=0,@n2=0,@n3=0declare c cursor forselect scorefrom sc,cwhere o=o and cn=@cnameopen cfetch next from c into @cjwhile@@fetch_status=0beginif @cj<60set @n1=@n1+1elseif @cj<=80set @n2=@n2+1elseset @n3=@n3+1fetch next from c into @cjendclose cdeallocate cgo使用该存储过程，查看“计算机基础”的相关成绩统计信息。

declare @n1 tinyint,@n2 tinyint,@n3 tinyintexec cn_tjscore_pro '计算机基础',@n1 output,@n2 output,@n3 outputselect @n1 '0--60人数',@n2 '60--80人数',@n3 '80--100人数' 3、利用课程名查询选修该课程的学生姓名、系别、成绩。

（使用输出参数）创建存储过程create procedure tn_scoretj_cursor_pro@cname char(10),@c cursor varying outputasset @c=cursor forselect sn,dept,scorefrom sc,s,cwhere o=o and sc.sno=s.sno and cn=@cname open @cgo使用该存储过程，查看“计算机基础”的相关信息declare @myc cursor,@sn char(10),@dept char(10),@score tinyint--调用过程tn_scoretj_cursor_proexec tn_scoretj_cursor_pro '计算机基础',@myc output--打印表头print'姓名系别成绩'fetch next from @myc into @sn,@dept,@scorewhile@@fetch_status=0beginprint @sn++@dept+' '+cast(@score as char(5))fetch next from @myc into @sn,@dept,@score endclose @mycdeallocate @myc七、事务设计1、创建一个事务程序，要求：学生“王蕾”打算选修“图像处理”课程，根据规定，此门课程选修的人数最多为30人，该生是否可以选修此门课程，给出结果提示。