数据库第二次作业.docx
《数据库第二次作业.docx》由会员分享，可在线阅读，更多相关《数据库第二次作业.docx（15页珍藏版）》请在冰豆网上搜索。
数据库第二次作业
《数据库第二次作业——上机实验题》
实验一：
交互式SQL的使用
实验要求：
1,创建Student数据库，包括Students,Courses,SC表，表结构如下：
Students（SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT）
Courses（CNO,CNAME,LHOUR,CREDIT,SEMESTER）
SC（SNO,CNO,GRADE）
（注：
下划线表示主键，斜体表示外键），并插入一定数据。
答：
createtableStudents
（
SNOvarchar（100）primarykey,
SNAMEvarchar（100）null,
SEXvarchar（100）null,
BDATEdatetimenull,
HEIGHTdecimalnull,
DEPARTMENTvarchar（100）null
）
go
createtableCourses
（
CNOvarchar（100）primarykey,
CNAMEvarchar（100）null,
LHOURintnull,
CREDITintnull,
SEMESTERvarchar（100）null
）
go
CREATETABLE[dbo].[SC]（
[SNO]varchar（100）NOTNULL,
[CNO]varchar（100）NOTNULL,
[GRADE][int]NULL,
CONSTRAINT[PK_SC]PRIMARYKEYCLUSTERED
（
[SNO]ASC,
[CNO]ASC
）WITH（PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON）ON[PRIMARY]）ON[PRIMARY]
GO
ALTERTABLE[dbo].[SC]WITHCHECKADDCONSTRAINT[FK_SC_Courses]
FOREIGNKEY（[CNO]）
REFERENCES[dbo].[Courses]（[CNO]）
GO
ALTERTABLE[dbo].[SC]CHECKCONSTRAINT[FK_SC_Courses]
GO
ALTERTABLE[dbo].[SC]WITHCHECKADDCONSTRAINT[FK_SC_Students]
FOREIGNKEY（[SNO]）
REFERENCES[dbo].[Students]（[SNO]）
GO
ALTERTABLE[dbo].[SC]CHECKCONSTRAINT[FK_SC_Students]
2．完成如下的查询要求及更新的要求。
（1）查询身高大于的男生的学号和姓名；
答:
selectSNO,SNAMEfromStudentswhereHEIGHT>
（2）查询计算机系秋季所开课程的课程号和学分数；
（3）答：
（4）selectCNO,CREDITfromCourseswhereSEMESTER=’秋季’
（5）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；
（6）答：
（7）select,,,fromstudentss
（8）innerjoinSCon=
（9）innerjpinCoursescon=c,CNO
（10）where=’计算机系’and=’男’and=’秋季’
（11）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；
（12）答：
（13）selectdistinctfromStudentss,scwhere=and=’女’andlike’EE%’
（14）查询每位学生已选修课程的门数和总平均成绩；
（15）答：
（16）selectcountas课程门数,avgas总平均成绩fromstudentss
（17）innerjoinSCon=
（18）innerjoinCoursescon=
（19）group by
（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；
答：
select cname,count（cno）,max（grade）,min（grade）,avg（grade）
from students natural join sc natural join courses
group by chane;
（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；
答：
select sname,sno from students natural join （select （grade）asminifrom sc group by sno）where mini>80 order by（sno）;
（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；
答：
selectsname,,credit
fromstudents,courses,sc
where=and=andgradeisnull;
（9）查询有一门以上（含一门）三个学分以上课程的成绩低于70分的学生的姓名；
（10）答：
（11）selectsnamefromstudents,courses,sc
（12）where=and=andcredit>=3andgrade<70;
（10）查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。
答：
selectsname,avg（grade）,sum（credit）
fromstudentsnaturaljoinscnaturaljoincourses
wherebdatebetween‘1984-00-00’and‘1987-00-00’
groudbysname
（11）在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。
答：
deletescwhereSNOlike’%01%’
deleteStudentswhereSNOlike’%01%’
（12）在ＳＴＵＤＥＮＴ关系中增加以下记录：
<0409101何平 女 1987-03-02 >
<0408130向阳 男 1986-12-11 >
答：
insertintostudentsvalues<’0409101’,’何平’,’女’,’1987-03-03’,’’’>
insertintostudentsvalues<’0408130’,’向阳’,’男’,’1986-12-11’,’’’>
（13）将课程CS-221的学分数增为３，讲课时数增为６０
（14）答：
（15）updatecoursessetcredit=3wherecno=’CS-211’
（16）updatecoursesset1hour=60wherecno=’CS-211’
3．补充题：
（1）统计各系的男生和女生的人数。
答:
selectdepartment,
sum（casewgensex=’男’then1else0end）,
sum（casewgensex=’女’then1else0end）,
count（sno）fromstudentsgroupbydepartmentorderbydeparment;
（2）列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。
答：
selectsnme
fromstudentsnaturaljoinscnaturaljoincourses
wherecname=’编辑原理’orcname=’体系结构’andgrade>90
（3）列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。
答：
selectcount（distincifromcourses,notin（selectfrom=andcname=’电子技术’）andin（selectcnofromcourseswherecname=’数学逻辑’orcname=’数字电路’）
（4）按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。
答：
selectdistinct,,sno,gradefromcoursesleftjoinscon
groupby,,sno,grade
groupby,,sno,grade;
（5）列出平均成绩最高的学生名字和成绩。
（SELECT句中不得使用TOPn子句）
答：
selectsname,r
from（selectsname,avg（grade）asfromstudents,scwhere=groupbysname,orderbyrdesc）
whererownum=1;
4．选做题：
对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。
要求：
1）修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。
2）设计并插入必要的测试数据，完成以下查询：
列出有资格选修数据库课程的所有学生。
（该学生已经选修过数据库课程的所有先修课，并达到合格成绩。
）
注意：
须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。
实验二：
数据库的安全和完整性约束
实验要求：
1．采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：
1）新增表Credits（SNO,SumCredit,NoPass），表示每学生已通过选修课程的合计学分数，以及不及格的课程数。
答：
createtableCredits（SNOvarchar（100）,SumCreditint,NoPassint）
2）创建视图Student_Grade（Sname,Cname,Grade），表示学生选修课程及成绩的详细信息。
答：
createviewStudent_Grade
as
select,,fromstudentss
innerjoinSCon=
innerjoinCoursescon=
2.在数据库中创建以下触发器：
1）Upd_Credit
要求：
当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。
答:
createtriggerUpd_CreditonSCforinsert
as
declare@SNOvarchar（100）,@CNOvarchar（100）,@GRADEint,@NoPassint,@CREDITint
select@SNO=SNO,@CNO=CNO,@GRADE=GRADE,@NoPass=（casewhenGRADE<60then1else0end）frominserted
updateCredits
setSumCredit=SumCredit+@CREDIT,
NoPass=NoPass+@NoPass
whereSNO=@SNO
2）Upd_StuView （Insteadof触发器）
要求：
当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。
如：
当执行InsertintoStudent_Gradevalues（‘王刚’，’数据库’，54）
则触发器完成另一插入操作：
InsertintoSCvalues（‘980201’，’CS-110’，54）
另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。
答:
createtriggerUpd_StuViewonStudent_Gradeforinsert
as
declare@SNAMEvarchar（100）,@CNAMEvarchar（100）,@GRADEint
select@SNAME=SNAME,@CNAME=CNAME,@GRADE=GRADEfrominserted
IF（EXISTS（SELECT*FROMStudentsWHERESNAME=@SNAME）ANDEXISTS（SELECT*FROMCoursesWHERECNAME=@CNAME））
BEGIN
InsertintoSC
select（SELECTTOP1SNOFROMStudentsWHERESNAME=@SANME）,
（SELECTTOP1CNOFROMCoursesWHERECNAME=@CNAME）,
@GRADE
END
3）PK_SC,FK_SC_SNO,FK_SC_CNO） （选做）
要求：
首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。
3．为Student数据库设计安全机制。
要求：
在该数据库系统中，有三类用户：
1）学生，权限包括：
查询所有的课程信息，根据学号和课程号来查询成绩。
但不允许修改任何数据。
（必做）
只能查询自己的成绩，不能查询别人的成绩。
（选做）
2）老师：
权限包括：
查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。
答:
CREATETRIGGRTsecure_student
BEFOREUPDATEORDELETEONdatabase
BEGIN
IF（（selectuserfromdual）=’老师’）
THEN
RAISE_APPLICATION_ERROE（-20506,
‘您没有权限对学生表进行修改。
’）
3）教务员：
权限包括：
查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。
答:
CREATETRIGGERsecure_student
BEFOREUPDATEORDELETEONdatabaseEXCEPTStudents
BEGIN
IF（（selectuserfromdual）=’教务员’）
THEN
RAISE_APPLICATION_ERROR（-20506,
‘您没有权限进行修改。
’）
要求：
安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。
为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。
实验三：
SQL编程
实验要求：
1．采用实验一的建库脚本和数据插入脚本创建Student数据库。
2．在数据库中创建以下存储过程：
1）Add_Student（SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT）
要求：
根据输入参数，插入一条学生记录。
答:
createprocedureAdd_Student
@SNOvarchar（100）,
@SNAMEvarchar（100）,
@SEXvarchar（10）,
@BIRTHDAYdatetime,
@HEIGHTdecimal,
@DEPTvarchar（100）
as
insertintoStudents
values（
@SNO,
SNO,
@SNAME,
@SEX,
@BIRTHDAY,
@HEIGHT,
@DEPT
）
2）Upd_Grade（SNO,CNO,GRADE）
要求：
根据输入参数，修改某学生选课的成绩。
答:
createprocedureUpd_Grade
@SNOvarchar（100）,
@CNOvarchar（100）,
@GRADEINT
AS
UPDATESC
setSNO=@SNO,
CNO=@CNO,
GRADE=@GRADE
whereSNO=@SNOandCNO=@CNO
3）Disp_Student（SNO，SUM_CREDIToutput，AVG_GRADEoutput）
要求：
根据SNO参数显示该学生的有关信息，包括：
a）学号，姓名，性别，年龄，身高，系别，所有选修的课程及成绩；
b）显示输出参数SUM_CREDIT（表示选修课程的总学分）及AVG_GRADE（表示3学分以上的课程的平均成绩）。
答:
createprocedureUpd_Grade
@SNOvarchar（100）,
@SUM_CREDITINToutput,
@AVG_GRADEintoutput
as
select*fromStudentss
innerjoinSCon=
innerjoinCoursescon=
where=@SNO
select@SUM_CREDIT=SumCreditfromCredits
whereSNO=@SNO
select@AVG_GRADE=avgfromStudentss
innerjoinSCon=
where=@SNOand>3
groupby
4）CAL_GPA（SNO，GPAoutput）
要求：
根据SNO参数,输出并显示该学生的GPA值。
计算方法如下：
GRADE（G）GRADEPOINT（GP）
G>=854
85>G>=753
75>G>=602
60>G1
GPA=（∑GP*CREDIT）/∑CREDIT）
答:
createprocedureCAL_GPA
@SNOvarchar（100）,
@GPAdecimaloutput
as
declare@SUM_CREDITint,@AVG_GRADEint,@ALL_CREDITint
select@SUM_CREDIT=SumCreditfromCredits
whereSNO=@SNO
select@AVG_GRADE=avg（casewhen>=85then4when85>and>=75then3when75>and>=60then2when60>then1end）fromStudentss
innerjoinSCon=
where=@SNOand>3
groupby
select@GPA=（@AVG_GRADE*@SUM_CREDIT）/@ALL_CREDIT
3．选做题：
使用其它程序设计语言编程，连接数据库并提交SQL语句，显示查询结果。
要求：
实现上题中的第3）小题要求，设计一个图形界面来输入查询的参数SNO，及显示查询的结果。
（如采用VC++，VB等）
实验四：
事务的管理（选作）
实验要求：
1．采用实验一的建库脚本和数据插入脚本创建Student数据库。
2．测试事务隔离级别，要求：
分别设置不同的隔离级别,包括：
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;
SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;
SETTRANSACTIONISOLATIONLEVELREADONLY;
两个并发事务交错执行的程序，能分别显示每种隔离级别下，是否出现丢失更新，脏读，读值不可复现以及幻象四种情况。
3．备份与恢复
备份数据库
答:
BACKUPDATABASEtest
TOdisk=‘c:
\test’
删除sc表
答:
DROPTABLESC
恢复到删除之前
答:
USEmaster
GO
RESTOREDATABASEtest_wt
FROMdisk=‘c:
\test_wt’
GO
升级会员 