《数据库第二次作业——
实验要求：
1,创建Student数据库，包括Students,Courses,SC表，表结构如下：
Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)
Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)
SC(SNO,CNO,GRADE)
(注：下划线表示主键，斜体表示外键)，并插入一定数据。
答：create?table?Students?(?SNO?varchar(100)?primary?key?,?SNAME?varchar(100)?null,?SEX?varchar(100)?null,?BDATE?datetime?null,?HEIGHT?decimal?null,?DEPARTMENT?varchar(100)?null?)?go?create?table?Courses?(?CNO?varchar(100)?primary?key?,?CNAME?varchar(100)?null,?LHOUR?int?null,?CREDIT?int?null,?SEMESTER?varchar(100)?null?)?go?CREATE?TABLE?[dbo].[SC](?[SNO]?varchar(100)?NOT?NULL,?[CNO]?varchar(100)?NOT?NULL,?[GRADE]?[int]?NULL,?CONSTRAINT?[PK_SC]?PRIMARY?KEY?CLUSTERED?(?[SNO]?ASC,?[CNO]?ASC?)WITH?(PAD_INDEX?)?ON?[PRIMARY]?GO?ALTER?TABLE?[dbo].[SC]?FOREIGN?KEY([CNO])?REFERENCES?[dbo].[Courses]?([CNO])?GO?WITH?CHECK?ADD?CONSTRAINT?[FK_SC_Courses]?=?OFF,?STATISTICS_NORECOMPUTE?=?ON,?ALLOW_PAGE_LOCKS?=?OFF,?IGNORE_DUP_KEY?=?ON)?ON?[PRIMARY]?=?OFF,?ALLOW_ROW_LOCKSALTER?TABLE?[dbo].[SC]?CHECK?CONSTRAINT?[FK_SC_Courses]?GO?ALTER?TABLE?[dbo].[SC]?FOREIGN?KEY([SNO])?REFERENCES?[dbo].[Students]?([SNO])?GO?ALTER?TABLE?[dbo].[SC]?CHECK?CONSTRAINT?[FK_SC_Students]?WITH?CHECK?ADD?CONSTRAINT?[FK_SC_Students]
2．完成如下的查询要求及更新的要求。
（1）查询身高大于1.80m的男生的学号和姓名；
答：select?SNO,SNAME?from?Students?where?HEIGHT>1.8select?CNO,CREDIT?from?Courses?where?SEMESTER=''秋季''
答：select?s.SNAME,SC.CNO,c.CREDIT,SC.GRADE?from?students?s?inner?join?SC?on?sc.SNO=s.SNO?inner?join?Courses?c?on?sc.CNO=c.CNO?where?s.DEPARTMENT=''计算机系''?and?s.SEX=''男''?and?c.SEMESTER=''秋季''
答：select?distinct?s.sname?from?Students?s,sc?where?s.sno=sc.sno?and?s.sex=''?女''?and?sc.cno?like?''EE%''
答：select?count(c.CNO)?as?课程门数,avg(SC.GRADE)?as?总平均成绩from?students?s?inner?join?SC?on?sc.SNO=s.SNO?inner?join?Courses?c?on?sc.CNO=c.CNO?group?by?s.SNO
答：select?cname,count(cno),max(grade),min(grade),avg(grade)?from?students?natural?join?sc?natural?join?courses?group?by?chane;
答：select?sname,sno?from?students?natural?join?(select?sno.min(grade)asmini?from?sc?group?by?sno)where?mini>80?order?by(sno);
select?sname,courses.cno,credit?from?students,courses,sc?where?students.sno=sc.sno?and?courses.cno=sc.cno?and?grade?is?null;
（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；答：select?sname?from?students,courses,sc?where?students.sno=sc.sno?and?courses.cno=sc.cho?and?credit>=3?and?grade<70;
查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。
答：select?smane,avg(grade),sun(credit)?from?students?natural?join?sc?natural?join?courses?where?bdate?between?''1984-00-00''?and?''1987-00-00''?group?by?sname;
在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。
答：delete?sc?where?SNO?like?''%01%''?delete?Students?where?SNO?like?''%01%''
（12）在ＳＴＵＤＥＮＴ关系中增加以下记录：
<0409101何平女1987-03-021.62>
<0408130向阳男1986-12-111.75>
答：insert?into?students?values(''0409101'',''何平'',''女'',''1987-03-02'',1.62,'''')?insert?into?students?values(''0408130'',''向阳'',''男'',''1986-12-11'',1.75,'''')
update?courses?set?credit=3?where?cno=''GS-221''?update?courses?set?credit=60?where?cno=''GS-221''
3．补充题：
(1)统计各系的男生和女生的人数。
答：selectdepartment,
sum(casewgensex=''男''then1else0end),sum(casewgensex=''女''then1else0end),count(sno)fromstudentsgroupbydepartmentorderbydepartment;
列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。
答：selectsnamefromstudentsnaturaljoinscnaturaljoincourseswherecname=''编译原理''orcname=''数据库''orcname=''体系结构''andgrade>90;
列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。
答：selectcount(distincisc.sno)fromcourses,sc.snonotin(selectsc.snofromcourses.cno=sc.cnoandcname=''电子技术'')andsc.cnoin(selectcnofromcourseswherecname=''数字逻辑''orcname=''数字电路'')
按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。
?答：selectdistinctcourses.cno,courses.cname,sno,gradefromcoursesleftjoinscon(courses.cno-sc.cno)groupbycourses.cno,courses.cname,sno,gradeorderbycourses.cno,courses.cname,sno,grade;
(5)列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOPn子句)
答：selectsname,rfrom(selectsname,avg(grade)asfromstudents,scwherestudents.sno=sc.snogroupbysname,students.snoorderbyrdesc)whererownum=1;
4．选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：
1)修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。
2)设计并插入必要的测试数据，完成以下查询：
列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)
注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。
实验二：数据库的安全和完整性约束
实验要求：
1．采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：
1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。
?答：createtableCredits(SNOvarchar(100),SumCreditint,NoPassint)
创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。
?答：createviewStudent_Gradeasselects.SNAME,c.CNAME,SC.GRADEfromstudentssinnerjoinSConsc.SNO=s.SNOinnerjoinCoursesconsc.CNO=c.CNO
在数据库中创建以下触发器：
Upd_Credit
要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。
答：createtriggerUpd_CreditonSCforinsertasdeclare@SNOvarchar(100),@CNOvarchar(100),@GRADEint,@NoPassint,@CREDITintselect@SNO=SNO,@CNO=CNO,@GRADE=GRADE,@NoPass=(casewhenGRADE<60then1else0end)frominsertedselect@CREDIT=CREDITfromCourseswhereCNO=@CNOupdateCreditssetSumCredit=SumCredit+@CREDIT,NoPass=NoPass+@NoPasswhereSNO=@SNO
Upd_StuView（Insteadof触发器）
要求：当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。
如：当执行InsertintoStudent_Gradevalues(‘王刚’，’数据库’，54)
则触发器完成另一插入操作：InsertintoSCvalues(‘980201’，’CS-110’，54)
另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。
?答：createtriggerUpd_StuViewonStudent_Gradeforinsertasdeclare@SNAMEvarchar(100),@CNAMEvarchar(100),@GRADEintselect@SNAME=SNAME,@CNAME=CNAME,@GRADE=GRADEfrominsertedIF(EXISTS(SELECTFROMStudentsWHERESNAME=@SNAME)ANDEXISTS(SELECTFROMCoursesWHERECNAME=@CNAME))BEGINInsertintoSCselect(SELECTTOP1SNOFROMStudentsWHERESNAME=@SNAME),(SELECTTOP1CNOFROMCoursesWHERECNAME=@CNAME),@GRADEEND
PK_SC,FK_SC_SNO,FK_SC_CNO)（选做）
要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。
?答：CREATETRIGGERsecure_studentBEFOREUPDATEORDELETEONdatabaseBEGINIF((selectuserfromdual)=''老师'')THENRAISE_APPLICATION_ERROR(-20506,''您没有权限对学生表进行修改.'')
3．为Student数据库设计安全机制。
要求：在该数据库系统中，有三类用户：
学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何数据。(必做)
只能查询自己的成绩，不能查询别人的成绩。（选做）
2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。
3）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。
答：CREATETRIGGERsecure_studentBEFOREUPDATEORDELETEONdatabaseEXCEPTStudentsBEGINIF((selectuserfromdual)=''教务员'')THENRAISE_APPLICATION_ERROR(-20506,''您没有权限进行修改.'')
要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。
为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。
实验三：SQL编程
实验要求：
1．采用实验一的建库脚本和数据插入脚本创建Student数据库。
2．在数据库中创建以下存储过程：
Add_Student(SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT)
要求：根据输入参数，插入一条学生记录。
答：createprocedureAdd_Student@SNOvarchar(100),@SNAMEvarchar(100),@SEXvarchar(10),@BIRTHDAYdatetime,@HEIGHTdecimal,@DEPTvarchar(100)asinsertintoStudentsvalues(@SNO,SNO,@SNAME,@SEX,@BIRTHDAY,@HEIGHT,@DEPT)
Upd_Grade(SNO,CNO,GRADE)
要求：根据输入参数，修改某学生选课的成绩。
答：createprocedureUpd_Grade@SNOvarchar(100),@CNOvarchar(100),@GRADEINTasUPDATESCsetSNO=@SNO,CNO=@CNO,GRADE=@GRADEwhereSNO=@SNOandCNO=@CNO
Disp_Student(SNO，SUM_CREDIToutput，AVG_GRADEoutput)
要求：根据SNO参数显示该学生的有关信息，包括：
a)学号，姓名，性别，年龄，身高，系别，所有选修的课程及成绩；
b)显示输出参数SUM_CREDIT(表示选修课程的总学分)及AVG_GRADE(表示3学分以上的课程的平均成绩)。
?答：createprocedureUpd_Grade@SNOvarchar(100),@SUM_CREDITINToutput,@AVG_GRADEintoutput
CAL_GPA(SNO，GPAoutput)
要求：根据SNO参数,输出并显示该学生的GPA值。计算方法如下：
GRADE(G)GRADEPOINT(GP)
G>=854
85>G>=753
75>G>=602
60>G1
GPA=(∑GPCREDIT)/∑CREDIT)
答：createprocedureCAL_GPA@SNOvarchar(100),@GPAdecimaloutputasdeclare@SUM_CREDITint,@AVG_GRADEint,@All_CREDITintselect@SUM_CREDIT=SumCreditfromCreditswhereSNO=@SNOselect@AVG_GRADE=avg(casewhenSC.GRADE>=85then4when85>SC.GRADEandSC.GRADE>=75then3when75>SC.GRADEandSC.GRADE>=60then2when60>SC.GRADEthen1end)fromStudentssinnerjoinSConsc.SNO=s.SNOwheres.SNO=@SNOandSC.GRADE>3groupbys.SNOselect@GPA=(@AVG_GRADE@SUM_CREDIT)/@All_CREDIT
3．选做题：
使用其它程序设计语言编程，连接数据库并提交SQL语句，显示查询结果。
要求：实现上题中的第3)小题要求，设计一个图形界面来输入查询的参数SNO，及显示查询的结果。(如采用VC++，VB等)
实验四：事务的管理（选作）
实验要求：
采用实验一的建库脚本和数据插入脚本创建Student数据库。
测试事务隔离级别，要求：
分别设置不同的隔离级别,包括：
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;
SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;
SETTRANSACTIONISOLATIONLEVELREADONLY;
两个并发事务交错执行的程序，能分别显示每种隔离级别下，是否出现丢失更新，脏读，读值不可复现以及幻象四种情况。
3．备份与恢复
备份数据库
答：BACKUPDATABASEtestTOdisk=''c:\test''
删除sc表
答：DROPTABLESC?
恢复到删除之前
?答:USEmasterGORESTOREDATABASEtest_wtFROMdisk=''c:\test_wt''GO
|
|
