数据库实验报告
计1305李云实验15
1gra
tselecto
stude
tstopublic2gra
tselectupdateo
coursestopublic3gra
tselectupdatesalaryo
teacherstouser1withgra
toptio
4gra
tselectupdatescoreo
choicetouser25createviewtvasselecttidt
ameemailsalaryfromTEACHERSgra
tselecto
tvtouser26gra
tselecto
teacherstouser2withgra
toptio
7gra
tselecto
teacherstouser3withgra
toptio
gra
tselecto
teacherstouser2withgra
toptio
正常执行8revokeselecto
teachersfromuser1cascade操作成功，可以实现删除的级联操作，权限全部取消9revokeselectupdateo
coursesfromuser1user2
133009
实验21
1建一张表USESCHOOLCREATETableClassClass_idvarchar4
amevarchar10deparme
tvarchar20co
strai
tPK_ClassPrimarykeyClass_id2创建事务T3，嵌套创建T4USEschoolBegi
Tra
sactio
T3i
serti
toclassvalues0000101CSCCSBegi
Tra
sactio
T4i
serti
toclassvalues0000101CSCCSCommitTra
sactio
T4commitTra
sactio
T3
T4插入违法，T4终止，整个事务回滚，T3中的插入也被终止实验22
f1结果：
o
deleterestrict不允许级联操作，数据库不允许删除stude
ts表及course表中的对应元组
2结果：
o
deleteSetNULL是将要删除的对应元组的外键置空，而c
o和s
o是SC表的主键，主键不能为空，所以数据库不允许删除操作3创建学生互助表createtablehelpsidchar8s
amevarchar20help_idchar8
ot
ullco
strai
tPK_helpprimarykeysidaltertablehelpaddco
strai
tFK_helpforeig
keyhelp_idrefere
ceshelphelp_id4每个部门一个部长，每个部长领导多个部员，每个部有一个部员有评测部长的权利createtableleadersidchar9s
amevarchar20myleaderchar9co
strai
tPK_leaderprimarykeysidcreatetablemo
itorsidchar9s
amevarchar20mymo
itorchar9co
strai
tPK_mo
itorprimarykeysidco
strai
tFK_mo
itorforeig
keymymo
itorrefere
cesleadersidaltertableleaderaddco
strai
tFK_leaderforeig
keymyleaderrefere
cesmo
itorsid
实验231加入约束U3，令sage的值0
useschoolaltertableworkeraddco
strai
tU3checksage02加入规则R2，确保插入sage值在1100之间，并绑定sage属性上useschoolgocreaterulerule_sageasvaluebetwee
1a
d100go
fexecsp_bi
drulerule_sageworkersage
实验24
1触发器T4，使插入sage值比表中最大sage值大useschoolgocreatetriggerT4o
workerfori
sertasifselectsagefromi
sertedselectmaxsagefromworkerbegi
pri
tThesageofcouplemustbemoretha
theexistedcouplessagerollbacktra
sactio
e
d2触发器T5，使更新sage值比表中最大sage值大useschoolgocreatetriggerT6o
workerforupdateasifselectsagefromi
sertedselectsagefromworkerbegi
pri
tThesageofcouplemustbemoretha
thesageofoldcouplerollbacktra
sactio
e
d
实验31
1创建帐号，密码，授予数据库访问权execsp_addlogi
r