一个完整的数据库示例

1、表的结构及完整性约束新建一个数据库jxsk,包括S C、SC T、TC五个表，结构如下:C表:Id dbo.c列？Eueno (PK, charfS), not null) cn (chartW), not null) 闔 ct (tinyintr null) f epno (FKd char(5)r null) iTl ccredit (tinyintd null)为束B LJm 匚K_c_cc red it_2 76E DE B3 向 CK_c_ct_286302E匚S表:dbo.sE； 口列/ sno (PK, ldnum(char(6J, not null) 圍 sn (char(8)

2、, not null)D sex (charfSJj null)1 age (tinyintj nullJ dept (charflOL null匕CJ釣束口 CK_s_age_267ABA7A_z resume (text, null)J native (char(201, null；SC表:日 dboc日 的束HI C K_sc_s co re_1 B FD 2C 075no PK, FK.:drum char(6R rot null ? eno (PK, FK, char5), not null 3 score (tmyint, null)T 表:日313 列日丄釣東J CK_Oge_2

3、A4B4S5EJ CK_t_comm_2C3393DOj CK_t_5al_2B3F6F97f tno (PKd Id num(charefill not null) ?! tn (char(8)p not null) sex (chr(2). null)2 age (tinyint, null)SI prof (char10, nulln sal (smallint, null)_1 comm (smallint, null；71 dept ichar(lO), null)TC表:dbo.tc日M” tno (PKj FK, Id num(charfb)! not null) ? eno (

4、PKr FKj char(5), not null)!1 evaluation (char(20), null)二、安全性控制及视图机制1、三类角色： depart、teacher、studentdepart的权限：姻FT角色JE性depart- 帮肋* fi'FEn廿诙用邑戟田）去幻揀坦aifti£»-14售齿吞岸笹EE件址雄teacher的权限:3| Jboa dbo£口 dbo二I时tfee逵加炖1.出琮垃;协RAlt«fControldboIa54rt血Qdbodho丁*屯(nrarxLi pdbaUpdAtAdboS'a

5、71;v&j kj gadbaIW團用櫃予具有槓予.柜迪" .- > u V5 3 廉走取聘翼普-rr雲音淳*MiiJV9PIkjttrChatred.1+1444Iniart lri-hfMi4*lStlliTt l4ca ahij> tfpduATa*w iJitanilj an<h回墮回圖口口口氓專审邑:性-tclwr |1血t 4U血丽甬邑綁F；UP 竝裤：tbuhirffllE：1适越炯1«知山* « J!JtWffl IE：1Wt:當student的权限:姻屋抱色*性-iBdent号枫上匡空I口j* ir.Rxtt計血色宕特i

6、jti.z” 口 口 Mana融时算用tai1 «乩CFs"春1 *»fa14新劇用翼） M<Ct>*帀日邀式1?規：衣囲frfs*oiklttrdb;Cwir*ld亦DdbiJb?XmarE-Jl-3uilliSk!£-TtJsv- HUTcljjdbVH<tt4h¥i*r 以岀!iddh2、有2个院系用户：d_jsj, d_xx，同属于depart角色。送掃页r银兰用EM扌 兰妄全悶倉口口口射到出超丸甜舸户瞋射針桶障用户lhA虽构口Z0| jask<Jii»wt«r*C-JjkL 3-ltRaG&

7、gt;eoll!5ib,>afc,Rttw tSarritfTHjJBTTtMpcLO已曰用伽估辖户jrtk曲JlWfl!吹W悄炯jsiikdadidlalj. M_iuuMnDUAyuniltt：XL有1个教师用户：t，属于teacher角色。* Dwjh>基科冒出合抵屯曲ffl户也)wtjBPii讪衲血匚比t匚 AEtMT屈“BlrflhLtfiir tarririMir 已dFVdrTaaipnETvieIife-Mjl3鈣-彌* 律一*1n；rt:x:4j'r= g «J:1Euhl应InhFln 爲s包E E 4*.孔*此孔視皿3HanEifa|J音比卓

8、1J£tn有一个学生用户：s,属于student角色。| | -it.dhtu-BHdjir .dhEhiricir| <L_JmifJbEbj-baX+-刖一Easr| | Jb_Eir«ri kxfli-an3*49住浙£険-(0毗A即針氓圧抽锄RM巳码jEftffAJ：1 H4.F=VAfn|IEIflF由口 网w五讣E9134Fm44，叩盯 凸 5= “ r (t<r » 嚣 TfJHITbBkt泄un ax届沁li *h：m3、创建计算机系教师视图t_viewsj、计算机系学生视图s_view_jsj,并授予d_jsj 用户在这两个

9、视图上的 select、delete、update、insert权限。计算机系教师视图t_view_jsj：create view t_view_jsjasselect tno , tn , sex , age , prof , sal , comm, dept from twhere dept ='计算机 with check opti on 授予 d_jsj 用户在计算机系教师视图 t_view_jsj 上的 select、delete、update、insert 权限：grant select , update , delete , insert on t_view_jsj to

10、 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_jsj4、创建一个视图，显示学号 ,姓名,院系,课程名

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

12、ed )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 deleted select p

13、rof_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 )end 3、创建一个规则 sexrule ，指定变量 sex

14、 的取值只能为 ' 男' 或' 女' 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）适合建索引的属性列主码所在的

15、属性列外码所在的列或在连接查询中经常使用的属性列按关键字的范围值进行搜索的属性列按关键字的排序顺序访问的属性列2）不适合建索引的属性列在查询中很少涉及的属性列包含较少的唯一值更新性能比查询性能更重要的属性列有text、ntext 、image数据类型定义的属性列3、为s表在dept属性列上创建索引createindex s_dept_indexon s （dept ）3 口壽引爲 PK_s_7E6CC92O 陵集）曲s_dept_index込唯一耳藻集）五、自定义数据类型、自定义函数1、自定义数据类型Idnum：学号、教师编号都是 char( 6), not null，exec sp_addt

16、ype Idnum , 'char(6)'，not nuII' 2、自定一个标量函数，用于查询某个同学某门课程的成绩。create functionscore_fun( sname char ( 8), cname char (10)retur nstinyintasbeg indeclare cj tinyintselect cj =scorefrom s , sc , cwhere s . sno =sc . sno and c . eno =sc . eno andsn =s name and cn =c namereturn cj end使用该函数，查询 

17、9;李忘'选修'计算机基础 '的成绩。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 sc . cno =c. cno and cn =cname) go使用该函数，查询选修了“计算机基础”的学生姓名、成绩。s

18、elect sn 姓名 , score 成绩from cname_score ( ' 计算机基础 ' )4、创建一个多语句表值函数，根据教师姓名查询该教师所讲授课程名、学生人 end数、平均成绩、最高成绩、最低成绩。 create returns numerictname_fun ( tname char ( 10)table ( cnfunction tname_score_tab ( 6, 1), zgf numeric ( 6, 1), zdf numericchar ( 10), rsint , pjf( 6, 1)asbegininsert into tname_sc

19、ore_tabselect cn , count (*), avg ( score from sc , tc , t , cwhere sc . cno =c. cno and t . tno and tn =tnamegroup by cnreturn), max( score=tc . tno and), min ( score )c . cno =tc . cno使用该函数，查询徐红霞'教师所授课信息。select * from tname_fun ( ' 徐红霞 ' )六、存储过程和游标1、利用课程名查询选修该课程的学生姓名、系别、成绩。 create proc

20、edure cn_score_procname char ( 10) asselect sn 姓名 , dept 系别, score 成绩from s , sc , cwhere s . sno =sc . sno and sc . cno =c. cno and cn =cname go使用该存储过程，查询选修“计算机基础”的相关信息。exec cn_score_pro ' 计算机基础 '2、统计某门课程成绩为60以下，6080 (含60、80分)，80以上各有多少人 create procedure cn_tjscore_procname char ( 10), n1 in

21、t output , n2 int output , n3 int output asdeclare cj tinyintselect n1=0, n2=0, n3=0declare c cursor forselect scorefrom sc , cwhere sc . cno=c. cno 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

22、from c into cjendclose cdeallocate cgo使用该存储过程，查看“计算机基础”的相关成绩统计信息。 declare n1 tinyint , n2 tinyint , n3 tinyint exec cn_tjscore_pro ' 计算机基础 ' , n1 output , n2 output , n3 outputselect n1 '0-60 人数' , n2 '60-80 人数' , n3 '80-100 人数 '3、利用课程名查询选修该课程的学生姓名、系别、成绩。 (使用输出参数) 创建存

23、储过程create procedure tn_scoretj_cursor_procname char ( 10), c cursor varying output asset c = cursor forselect sn , dept , score from sc , s, cwhere sc . cno =c. cno and sc . sno =s. sno and cn =cname open cgo 使用该存储过程，查看“计算机基础”的相关信息 declare myc cursor , sn char ( 10), dept char ( 10), score tinyint-

24、调用过程 tn_scoretj_cursor_proexec tn_scoretj_cursor_pro ' 计算机基础 ' , myc output- 打印表头print ' 姓名 系别成绩 'fetch next from myc into sn, dept , score while fetch_status =0beginprint sn + dept +' ' +cast ( score as char ( 5) fetch next from myc into sn, dept , score endclose myc dealloc

25、ate myc七、事务设计1、创建一个事务程序，要求：学生“王蕾”打算选修“图像处理”课程，根据 规定，此门课程选修的人数最多为 30 人，该生是否可以选修此门课程，给出结 果提示。begin transactiondeclare num tinyint , cno char ( 5), sno char ( 6)select cno =cno from c where cn =' 图像处理 'select sno =sno from s where sn =' 王蕾 'select num=count (*) from sc where cno =cnoif num<30begininsert into sc ( sno , cno ) values ( sno, cno)commitprint ' 王蕾同学选修图像处理课程注册成功！ 'endelsebeginrollback transactionprint ' 选修图像处理课程的人数已满，王蕾同学不能再选修此课程 ' endgo2、将教师“王平”讲授的“计算机网络 ”转给“徐红霞”