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

一、表的结构及完整性约束

新建一个数据库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 o=o

三、完整性控制--触发器、规则

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）不适合建索引的属性列

●在查询中很少涉及的属性列

●包含较少的唯一值

●更新性能比查询性能更重要的属性列

●有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 tinyint

as

begin

declare @cj tinyint

select @cj=score

from s,sc,c

where s.sno=sc.sno and o=o and

sn=@sname and cn=@cname

return @cj

end