一个完整的数据库示例

资源描述

《一个完整的数据库示例》由会员分享，可在线阅读，更多相关《一个完整的数据库示例（14页珍藏版）》请在金锄头文库上搜索。

1、、表的结构及完整性约束新建一个数据库jxsk,包括S、C、SC、T、TC五个表，结构如下:C表：日目dbo.cI日口列日D CK_c_ccred it_276E D E B 3口 CK_c_ct_28&302EC? cno (PK, char(5), not null lH cn (char(10), not null) 国 ct (tinyint null) cpno (FK, char(5), null) 国 ccredit (tinyint, null)S表：dboBsQ ：El 口夙? sno (PKj IdnuimifcharfG), not null) 国 sn (char(81

2、not null) 司 sex (charfZ), null) 司 age (tinyint null) 圄 dept (charfW), null) |T| resume (text, null) 目 native (charfOJ, null)J CK_s_a g e_2 67ABA7ASC表:日回dbo.scsno (PKFKIcInumfcharfSJJj not null) cno (PKj FKj charfSJj not null score (tinyintj null)I日Q约束 CK_sc_score_1 BFD2CO7:T曰约束 CK_t_age_2A4B4B5E CK_

3、t_comm _C3393tX) CK_t_sal_2E3F6F97tno (PK Idnum(char(6)X not null) tn (charfS). not null) sex (char(2X null) age (tinyint null) prof (charfW), null) sal (smallint null) comm (smallint null) dept (charflO), null)TC表：d bo.tc日 |日口列? tno (PK, FK. Idnum(char(6), not null) 早 eno (PK, FK, charf5y not null)

4、回 evaluation (char(20), null):、安全性控制及视图机制1、三类角色：depart、teacher、studentdepart的权限：teacher的权限:student的权限:2、有2个院系用户：d_jsj, d_xx，同属于depart角色。有1个教师用户：t,属于teacher角色。邑一角射象器映对规务户全态常服用安状曹曹看否看寒脚本始帮助-新建映射数据库用户默认架构LookI jxsktmastermoilelITlEilbKeportSei-i.rerReportServ erTempDBTeachtempdb映射到此登录名的用户包）:已启用Gugt帐户

6、建计算机系教师视图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,ins

7、ert 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 vie

8、w 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 c after deleteasdelete from scwhere cno in(select cno from deleted)delete from tcwhere cno in(select cno from deleted)go

9、2、为T创建一触发器，当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元，从“副教授”晋升为“教授”时，岗位津贴自动增加900元。 create trigger t_update_trigger on tafter update as if update(prof)begindeclare prof_old char(10),prof_new char(10 select prof_old=prof from deleted select prof_new=prof from insertedif prof_old=讲师and prof_new=副教授update t set co

10、mm=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至【J sexrule规则：exec sp_bindrule sexrule,s.sex exec sp_bindrule sexrule,t.

11、sex四、索引1、索引的分类：聚集索引：primary key自动创建聚集索引非聚集索引2、使用索引的准则：1)适合建索引的属性列主码所在的属性列外码所在的列或在连接查询中经常使用的属性列按关键字的范围值进行搜索的属性列按关键字的排序顺序访问的属性列2）不适合建索引的属性列在查询中很少涉及的属性列包含较少的唯一值更新性能比查询性能更重要的属性列有text、ntext、image数据类型定义的属性列3、为s表在dept属性列上创建索引create index s_dept_index on sidept10 Q素引盖 PK_s_7E6CC920 集）Il s_dept_index 环唯一，

12、非肆期五、自定义数据类型、自定义函数1、自定义数据类型Idnum：学号、教师编号都是char(6)，not null。exec sp_addtype Idnum,char(6),not null2、自定一个标量函数，用于查询某个同学某门课程的成绩。create function score_fun sname chari8),cname char(10) returns tinyintasbegindeclare cj tinyintselect cj=scorefrom s,sc,cwhere s.sno=sc.sno and o=o andsn=sname and cn=cnameretu

13、rn cj end使用该函数，查询李忘选修计算机基础的成绩。select dbo score_fun(李忘，计算机基础)3、创建一个单语句表值函数。create function cname_score(cname char(10) returns tableasreturn(select sn,score from s,sc,c where s.sno=sc.sno and o=o and cn=cname)go使用该函数，查询选修了 “计算机基础”的学生姓名、成绩。select sn 姓名,score 成绩from cname_score (计算机基础)4、创建一个多语句表值函数，根据教师

14、姓名查询该教师所讲授课程名、学生人数、平均成绩、最高成绩、最低成绩。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 cn returnend使用该函数，查询徐红霞

展开阅读全文