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

一、表的结构及完整性约束 新建一个数据库jxsk，包括S、C、SC、T、TC五个表，结构如下： C表： S表： SC表： T表： TC表： 二、安全性控制及视图机制 1、三类角色：depart、teacher、student depart的权限： 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_jsj as select tno,tn,sex,age,prof,sal,comm,dept from t where 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_jsj as select sno,sn,sex,age,dept,resume,native from s where 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(学号,姓名,院系,课程名,成绩) as select s.sno,sn,dept,cn,score from s,sc,c where s.sno=sc.sno and sc.cno=c.cno 三、完整性控制--触发器、规则 1、要求当删除C表中某课程信息时，同时删除SC和TC中与此课程相关的记录。 create trigger c_delete_trigger on c after delete as delete from sc where cno in (select cno from deleted) delete from tc where cno in (select cno from deleted) go 2、为T创建一触发器，当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元，从“副教授”晋升为“教授”时，岗位津贴自动增加900元。 create trigger t_update_trigger on t after update as if update(prof) begin declare @prof_old char(10),@prof_new char(10) select @prof_old=prof from deleted select @prof_new=prof from inserted if @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) end 3、创建一个规则sexrule，指定变量@sex的取值只能为男或女 create rule sexrule as @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）不适合建索引的属性列 在查询中很少涉及的属性