数据库课程设计报告 完整版

页脚内容页脚内容PRIMARYKEY(secno,tno,cno),FOREIGNKEY(tno)REFERENCESTEACHER(tno),FOREIGNKEY(cno)REFERENCESCOURSE(cno));CREATETABLEENROLL(cnoCHAR(10),seconCHAR(2),snoCHAR(10),gradeINT,PRIMARYKEY(cno,sno),FOREIGNKEY(sno)REFERENCESSTUDENT(sno),FOREIGNKEY(cno)REFERENCESCOURSE(cno));完成数据的录入COURSE表录入数据INSERTINTOCOURSEVALUES('450','WesternCivilization','History','3');INSERTINTOCOURSEVALUES('730','CalculusIv','Math','4');INSERTINTOCOURSEVALUES('290','EnglishComposition','English','3');INSERTINTOCOURSEVALUES('480','CompilerWriting','ComputerScience','3');SELECT*FROMCOURSESTUDENT表录入数据INSERTINTOSTUDENTVALUES('148','Susanpowell','534EastRiverDr','19041','Haverford','PA','F');INSERTINTOSTUDENTVALUES('210','BobDawson','120SouthJefferson','02891','Newport','RI','M');INSERTINTOSTUDENTVALUES('298','HowardMansfield','290WynkoopDrive','22180','Vienna','VA','M');INSERTINTOSTUDENTVALUES('348','SusanPugh','534EastHamptonDr','06107','Hartford','CN','F');INSERTINTOSTUDENTVALUES('349','JoeAdams','73EmmersonStreet','19702','Newark','DE','M');INSERTINTOSTUDENTVALUES('354','JanetLadd','44110thStreet','18073','Pennsburg','PA','F');INSERTINTOSTUDENTVALUES('410','BillJone','120SouthHarrison','92660','Newport','CA','M');INSERTINTOSTUDENTVALUES('473','CarolDean','983ParkAvenue','02169','Boston','MA','F');INSERTINTOSTUDENTVALUES('548','Allenthomas','238WestOxRoad','60624','Chicago','IL','M');INSERTINTOSTUDENTVALUES('558','ValShipp','238WestportRoad','60556','Chicago','IL','F');INSERTINTOSTUDENTVALUES('649','JohnAnderson','473EmmoryStreet','10008','NewYork','NY','M');INSERTINTOSTUDENTVALUES('654','JanetYhomas','4416thStreet','16510','Erie','PA','F');SELECT*FROMSTUDENTTEACHER录入数据INSERTINTOTEACHERVALUES('303','Dr.Horn','257-3049',27540.00);INSERTINTOTEACHERVALUES('290','Dr.Lowe','257-2390',31450.00);INSERTINTOTEACHERVALUES('430','Dr.Engle','56-4621',38200.00);INSERTINTOTEACHERVALUES(180,'Dr.Cooke','257-8088',29560.00);INSERTINTOTEACHERVALUES(560,'Dr.Olsen','257-8086',31778.00);INSERTINTOTEACHERVALUES(784,'Dr.Scango','257-3046',32098.00);SELECT*FROMTEACHERSECTION表录入数据INSERTINTOSECTIONVALUES('1','303','450',2);INSERTINTOSECTIONVALUES('1','290','730',6);INSERTINTOSECTIONVALUES('1','430','290',3);INSERTINTOSECTIONVALUES('1','180','480',3);INSERTINTOSECTIONVALUES('2','560','450',2);INSERTINTOSECTIONVALUES('2','784','480',2);SELECT*FROMSECTIONENROLL表录入数据INSERTINTOENROLLVALUES('730','1','148','3');INSERTINTOENROLLVALUES('450','2','210','3');INSERTINTOENROLLVALUES('730','1','210','1');INSERTINTOENROLLVALUES('290','1','298','3');INSERTINTOENROLLVALUES('480','2','298','3');INSERTINTOENROLLVALUES('730','1','348','2');INSERTINTOENROLLVALUES('290','1','349','4');INSERTINTOENROLLVALUES('480','1','348','4');INSERTINTOENROLLVALUES('480','1','410','2');INSERTINTOENROLLVALUES('450','1','473','2');INSERTINTOENROLLVALUES('730','1','473','3');INSERTINTOENROLLVALUES('480','2','473','0');INSERTINTOENROLLVALUES('290','1','548','2');INSERTINTOENROLLVALUES('730','1','558','3');INSERTINTOENROLLVALUES('730','1','649','4');INSERTINTOENROLLVALUES('480','1','649','4');INSERTINTOENROLLVALUES('450','1','654','4');INSERTINTOENROLLVALUES('450','2','548','1');SELECT*FROMENROLLCOURSEenocnamedepartmentcredit1EnglishCompositionEnglish32WesterriCivilizationHistory33侧CompilerWritingComputerScience34734)匚bIcliIusIvMath4STUDENT

二结果消息sncisnameaddresscitystatesex114SSusanpowell554EastRiverDr1S041HaverfondPAF221DBobDawson120SouthJeFfersanNewportRlM3asHowandMansfield2S0WynkoopDrive221SCViennaVAM434SSusanPugh5-34EastHamptonDr06107HartfordC：NF5345JoeAdams73EmmersonStreet13702NewarkDEMG354JanetL^dd4411DthStreet1W3Pennsb...PAF7410BillJone120SouthHanisonNewportCAMS473CarolDean&S3ParkAvenueD2169BostonMAF954SAllenthornas23SWestObcRoadG0G24ChicagoILM1fl55SValShipp23SWestpartRoadG055€ChiciagoILF11G4SJohnAnderaon473EmmoiyStneert1DD&SNewYorkMYMTEACHERtnotnanephonemmlmryJ_130Dr.Cooke257-Bi}8S-29醐｝.噩2290Dr.Lowe257-239031450.DD33iJ3Dr.Horn257-3W27W.'SD4430Dr.Engle25t4C21iS200.DD5Dr.Olsen257-Bi^3177S.3DG734Dr.Sea...257-3MCJ2(m.'DDSECTIONsecnotnoenosqty1画32129073063130324143&3525GD2627B44W2ENROLLCHOsecnosnograde12301爾32290134S43230154S2445022103545014732G450254S174501654434S02眈3CT1Osecnosnograde£4SD229S334S0134S4104SD141D2114E02473D124W1閒413730114S314730121D115730121673U1473317730155B373U14检索系名为“Math”和“English”的课程信息SELECT*FROMCOURSEWHEREdepartment='Math'ORdepartment='English'J结果,3消息enocnarnedepartmentcnedrtEnglish匚ompositionEnglish3273CCalculusIvMath4按字母顺序列出教师姓名和电话号码SELECTtname,phoneFROMTEACHERORDERBYtname检索电话号码不是以“257”打头的教师姓名和电话号码SELECTtname,phoneFROMTEACHERWHEREphonenotlike'257%'检索数学系所有成绩大于3的课程名、系名、学分SELECTcname,department,creditFROMCOURSEWHEREcnoIN(SELECTcnoFROMENROLLWHEREgrade>3ANDcno='730');cname1CalculuslvdepartmentcreditMath4检索没有选修任何课的学生姓名、学号SELECTsno,snameFROMSTUDENTWHERENOTEXISTS(SELECT10.10.检索至少选修教师“Dr.Lowe”所开全部课程的学生学号SELECTDINSTINCTsnoFROMENROLLENROLLXWHERENOTEXISTS(SELECT*FROMENROLLENROLLYWHEREcnoin(SELECTcnoFROMTEACHER,SECTION,WHEREtname='Dr.Lowe'ANDTEACHER.tno=SECTION.tno)ANDNOTEXISTS(SELECT*FROMENROLLWHERESTUDENT.sno=ENROLL.sno);snameJJanetLadd曾童向已成功执行°检索没有选修课程“CalculusIv”的学生学号SELECTsnoFROMSTUDENTWHEREsnoNOTIN(SELECTSTUDENT.snoFROMCOURSE,STUDENT,ENROLLWHEREcname='CalculusIv'ANDCOURSE.cno=ENROLL.cnoANDSTUDENT.sno=ENROLL.sno);

FROMENROLLENROLLZWHEREENROLLZ.sno=ENROLLX.snoANDENROLLZ.cno=ENROLLY.cno))（修改数据验证，该老师教授课程号为730和500，只有学号148的学生同时选择了这两门课）SECTIONsecnotnocnosc#y1184J4SD321SOD1312MJ烦641血450251434}沏362MU4502727342ENROLLli5呼1314114&3结果检索每门课学生登记的人数、相应的课程名、课程号、分组号SELECTDISTINCTsecno,ENROLL.cno,cname,sqtyFROMENROLL,COURSE,SECTIONWHERESECTION.cno=ENROLL.cnoANDCOURSE.cno=ENROLL.cnosecnoenocnarnesqty1250EnglishComposrtion3Z45DWestenGvilization2314BCCompilerWriting34173CCaletiluEIv652450WestenGvilizartion2£24SCCompil&rWriting2检索选修两门以上课程的学生姓名SELECTsnameFROMSTUDENTWHEREsnoin(SELECTsnoFROMENROLLGROUPBYsnoHAVINGCOUNT(*)>2)sname1[CppqID*日n：检索只有男生选修的课程和学生名SELECTcname,snameFROMCOURSE,STUDENT,ENROLLWHERESTUDENT.sno=ENROLL.snoANDCOURSE.cno=ENROLL.cnoANDCOURSE.cnoin(SELECTcnoFROMCOURSEWHEREcnonotin(SELECTDISTINCTcnoFROMSTUDENT,ENROLLWHEREsex='F'andSTUDENT.sno=ENROLL.sno))cnamesnarne1]EnglishCompositionHowardMansfield2EnglishCcmpasitionJoeAdams-EnglishCompositionAllenthonnas检索所有学生都选修的课程名、学生名、授课教师名、该生成绩SELECTcname,sname,tname,gradeFROMSTUDENT,TEACHER,SECTION,ENROLL,COURSEWHERECOURSE.cno=ENROLL.cnoANDSTUDENT.sno=ENROLL.snoANDTEACHER.tno=SECTION.tnoANDSECTION.cno=ENROLL.cnoANDSECTION.secno=ENROLL.seconANDCOURSE.cno=(SELECTcnoFROMENROLLGROUPBYcnoHAVINGCOUNT(*)=12)cnamesnametnamegrade-国查询已成功执丘删去名为“JoeAdams”的所有记录DELETEFROMSTUDENTWHEREsname='JoeAdams';SELECT*FROMSTUDENTsnoanameaddresscipcitystatesex114SSusanps^jell534EastRiverDr19D41HaverfandPAF2210BobDawson12DSouthJeHeraon02S31NewportRlH3298HawardMa...2SDWynkoopDrive221Sd)ViennaVAM434SSusanPugh534EastHampto...06107HartfordCNF5354JanetLadd4411EthStreet18073Pennsb...PAF410BillJone12)0SouthHarrisanNewportCAM7473CarolDean9S3ParkAvenueBostonMAFn.rjnAII■1rrn.4ilrni—'■LLC~«Ti■!-354SAllenthanas脚WestOxHoad®K24ChicagoILH9558Vai5hipp23SWestportRoadChicagoILF10649JohnAider...473EmmaryStreet10M8NewYorkNYH11654JanetYho...441QhStreet1GS10EriePAF把教师“Scango”的编号改为“666”UPGRADETEACHERSETtno='666'WHEREtname='Dr.Scango'SELECT*FROMTEACHER6666Dr.5ca...257-304^320&8.DD统计教师“Engle”教的英语课的学生平均分SELECTAVG(grade)AVGFROMENROLLWHEREgradein(SELECTgradeFROMTEACHER,SECTION,ENROLLWHEREtname='Dr.Engle'ANDTEACHER.tno=SECTION.tnoANDSECTION.cno=ENROLL.cnoANDENROLL.secno=SECTION.secno)1「3!I■=统计各门课程的选课人数SELECTCOURSE.cname,COUNT(ENROLL.sno)FROMENROLL,COURSEWHEREENROLL.cno=COURSE.cnoGROUPBYCOURSE.cname;19.输出如下报表：学生名课程名教师名成绩SELECTsname学生名，cname课程名，tname教师名，grade成绩FROMENROLL,STUDENT,COURSE,TEACHER,SECTIONWHEREENROLL.sno=STUDENT.snoANDSECTION.cno=COURSE.cnoANDENROLL.secno=SECTION.secnoANDENROLL.cno=SECTION.cnoANDSECTION.tno=TEACHER.tno

1SusanPughCompilerWritingEJr.Cooke42BillJone匚arripilerWriting□r.Cooke23JohnAnde...CompilerWritingDr.Cooke44Susanpo...CalculusIv□r.Lowe35BobDaws...CalculLis1军Dr.LoweSusanPughCalculusIvEJr.Lawe:7CarolDeanCalculLis\vDr.LowemVbIShippCalculusIvDr.Lawe3学生容课程営埶师名成绩9JohnAnde...Caluulu吕IvDr.Lqw@410CarolDeanWestenDvili...Dr.Hom211JanetYho...WesternOvili...Dr.Hom412HowardM...EnglishCamp...Dr.Engle313JaeAdamsEnglishComp...Dr.Engle斗14AllenthomasEnglishCximp...Dr.Engle215BobDaws...WesternGniL.Dr.Olsen316AllerthamasWesternDvili...Dr.Olsen117HowardM...CompilerWritingDr.Sea...1SCaralDeanCompilerWritingDr.Sea...*20.定义并验证触发器，当登记表增加一条新的记录时，自动在分组表中更新相应属性。CREATETRIGGERENROLL_1ONENROLLAFTERINSERTASUPDATESECTIONSETsqty=sqty+1FROMSECTION,insertedWHEREinserted.secno=SECTION.secnoANDo=SECTION.cno验证结果：INSERTINTOENROLLVALUES('450','2','354','1')SELECT*FROMSECTION原始数据

1匸［1SD1匸［1SD4802131沏73041303454}51O)290■62450727S44S0执行结果四、课程设计小结在本次课设之前，我已经在前段学习期间上机编写过类似相关的SQL语句来完成对于学生-课程-选课的信息查询，这次在原先实验的基础上添加了分组和教师表，创建数据库、基本表和录入数据的过程与上机实验基本相同，因此很容易就完成了前三个步骤，在这个过程中对于数据库、表的创建以及数据的输入的操作都更加的熟练。题目4-19都是对于表中数据进行查询、修改、删除等操作，并且运用了ORDERBY、GROUPBY等功能短语以及COUNT、AVG等聚集函数。前两题都是基本的查询，比较简单，但由于输入数据时Dr.Engle中的'.'多按了一个空格，结果在调试的时候出现的是错误的结果，结｝。果不正确(见截图｝。第10题是本次课设最难的题目，自己也尝试着去做，但是由于理解的失误以及数据的巧合性，错误的SQL语句得出的结果却是正确的。这就导致了自己以为解决了这道难题，以至于在给老师检查的时候被指出严重的错误，感到很羞愧。后来仔细分析题意，按照所有的关键字来编写查询语句，参照书上关于notexists语句以及嵌套循环的语句，用谓词演算将题目转换成逻辑运算，等价为“没有这样课程y,老师Dr.Lowe教授了这门课，而学生X没有选”p表示“老师教授了课程y”q表示“学生X选了课y”，等价于「3y(pAnq)。参照书上的例题，分层次地编写功能语句。因为题目的巧合性所以在检验时修改了数据，添加了该老师新开另一门课，只有学号148的学生同时选择了这门老师的两门课。然后再去验证结果发现是正确的。二结果消息sno1ius■第13题的题目刚开始理解的不透彻，所以直接查询了男生选修的课程，得到的结果有10个，但照表检查可以看出有些课程仍是有女生选的，与题目“只有男生选修”的条件不符，因此重新分析，发现可以使用嵌套查询先用子查询把女生选择的课程选出来，然后父查询在子查询的结果之外(notin)查询，这样就把女生选修的课程出去，这样就避免出现选择的课程男、女生都选修的错误。(见截图)-3结果消息cnamesnarne1[EnglishCompositionHmradMansfield2EnglishComposition■JoeAdams3Engli呂hComposrtiorijMlenthomas4WesternDvilizaticnBobDawson5WesternDrilizationjyienthomas6CompilerWritingHowandMansfield7CompilerWritin口BillJane涉直询已成功执行.第15、16题是对于学生、教师表的修改和删除。在执行过程中程序一直报错，始终找不出问题所在。后来经过询问同学，发现SECTION和ENROLL表中引用了这两个表中的sno和tno,由于其引用关系，所以无法对于这两个表进行修改。因此需要先删除外码的引用关系，然后才能对其进行修改和删除。第20题定义触发器这个比较陌生，在学习的时候就不是很掌握，因此对于这个题目第一印象就是不想去做，我不会。但是后来通过翻阅书本并且询问同学，并且自己不断的摸索，虽然起初一直报错，但是我并没有气馁，在各种帮助和努力下终于实现了最后一个功能。不过在给老师检查的过程中，发现UPDATE语句后面自己添加了FROM的语句，虽然对于整个执行结果没有太大的影响，但这是一个常识性的错误，不应该出现如此低级的错误，因为当时是想新表需要和SECTION、ENROLL表进行连接,所以在写的过程中直接添加了o二SECTION.cnoANDinserted.secno二SECTION.secno的错误语句，而且还误将其中的SECTION写成了ENROLL表，以至于在老师询问的时候，自己也被自己的代码弄得不知所措。后来修改的时候索性把所有的条件语句都删除了，并没有进行临时新表inserted与SECTION表的连接，以至于结果每一行的学生登记数量sqty都加一，得到错误的结果。过程比较艰辛但是最后完成的时候内心的喜悦确实满满洋溢的。果然自己去努力实现的事情可以给自己带来无限的快乐，自己从中收获的经验和知识也是更多的。不要因为自己对于某方面不熟悉就直接放弃，而应该努力去尝试一下。只有试过了，才知道自己到底可不可以完成这个看似困难的问题！附表2课程（COURSE）课程号（eno）课程名（cname）系名(department)学分(credit)450WesternCiviliza