數(shù)據(jù)庫課程設(shè)計報告完整版

1、word格式文檔數(shù)據(jù)庫課程設(shè)計班 級 物聯(lián)網(wǎng)1202學(xué) 號 3120611027姓 名 楊璐指導(dǎo)老師年軼2014年1月專業(yè)整理一、引言 21 目的 22題目 23要求 2二、系統(tǒng)的分析與設(shè)計 31 概念設(shè)計 32邏輯設(shè)計 33系統(tǒng)功能結(jié)構(gòu) 44完整性設(shè)計 5三、系統(tǒng)的實現(xiàn) 6四、課程設(shè)計小結(jié) 22第 3頁一、引言1 目的課程設(shè)計為學(xué)生提供了一個既動手又動腦，獨立實踐的機(jī)會，將課本上的理論知識和實際有機(jī)的結(jié)合起來，鍛煉學(xué)生的分析解決實際問題的能力。提高學(xué)生適應(yīng)實際，實踐編程的能力。課程設(shè)計的目的：(1) 加深對數(shù)據(jù)庫系統(tǒng)、軟件工程、程序設(shè)計語言的理論知識的理解和應(yīng)用水平；(2) 在理論和實驗教學(xué)

2、基礎(chǔ)上進(jìn)一步鞏固已學(xué)基本理論及應(yīng)用知識并加以綜合提高；(3) 學(xué)會將知識應(yīng)用于實際的方法，提高分析和解決問題的能力，增強(qiáng)動手能力；(4) 為畢業(yè)設(shè)計和以后工作打下必要基礎(chǔ)。2 題目題目 2.設(shè)計一個大學(xué)教學(xué)數(shù)據(jù)庫應(yīng)用系統(tǒng)。該系統(tǒng)涉及學(xué)生、教師、課程、分組、登記。數(shù)據(jù)見附表2。因時間關(guān)系，只要求每個學(xué)生任選 1個題目，如有時間、有興趣，可做另外一題，酌情 加分。3 要求運用數(shù)據(jù)庫基本理論與應(yīng)用知識，在微機(jī)RDBMS(SQL Server) 的環(huán)境上建立一個數(shù)據(jù)庫應(yīng)用系統(tǒng)。要求把現(xiàn)實世界的事物及事物之間的復(fù)雜關(guān)系抽象為信息世界的實體及實體之間聯(lián)系的信息模型，再轉(zhuǎn)換為機(jī)器世界的數(shù)據(jù)模型和數(shù)據(jù)文件，并

3、對數(shù)據(jù)文件實施檢索、更新和控制等操作。(1) 用E-R 圖設(shè)計選定題目的信息模型；(2) 設(shè)計相應(yīng)的關(guān)系模型，確定數(shù)據(jù)庫結(jié)構(gòu)；(3) 分析關(guān)系模式各屬于第幾范式，闡明理由；(4) 設(shè)計應(yīng)用系統(tǒng)的系統(tǒng)結(jié)構(gòu)圖；(5) 通過設(shè)計關(guān)系的主碼約束、外碼約束和使用CHECK 實現(xiàn) 完整性控制；(6) 完成實驗內(nèi)容所指定的各項要求；(7) 分析遇到的問題，總結(jié)并寫出課程設(shè)計報告；(8) 自我評價二、系統(tǒng)的分析與設(shè)計1 .概念設(shè)計第 13 頁2 .邏輯設(shè)計STUDENT(sno,sname,address,zip,state,city,sex)sno 是主碼TEACHER(tno,tname,phone,sa

4、lary)tno 是主碼COURSE(cno,cname,department,credit)cno 是主碼SECTION(secno,cno,tno,sqty)tno是外碼ENROLL(secno,cno,sno,grade)外碼secno 、 tno 、 cnocno,sno 是主碼,主碼，cno、cno,sno 是3.系統(tǒng)功能結(jié)構(gòu)jiij1?: 1數(shù) 蚌 # 理 史 ：L:宕 n 1 ：學(xué)生信息距An =L學(xué)生伯息幗改：:學(xué)+佶JJJW解:£:學(xué)生怡泉聲溝：:救加伯總廊：J 、【鞅加信息力H2':殿3”, ； ,!僚'.:：就前后息刪除3 M：國拴裕息添Al 3

5、1j：iBI呈后息像兇ji: 01箱箱息M除j：讀提梢息告畫： M -jJt分ftl信息就除：;E分fH信息庫因"仆用信超0箱;r分ft!信融率加:2成納 t 信.目管理,：曜祜伯工L捌除x:此hMi.P.刪除工：或餐蛤以喇瞪：佻觸位M制降：t54.完整性設(shè)計/*包含：關(guān)系主碼、外碼、 CHECK約束。并給出相應(yīng)的 SQL語句*/COURSE的完整性約束：cno CHAR (10) PRIMARY KEYSTUDENT的完整性約束：sno CHAR (10) PRIMARY KEYTEACHER的完整性約束：tno CHAR (10) PRIMARY KEYSECTION的完整性約束

6、：PRIMARY KEY(secno , tno , cno ),FOREIGN KEY(tno ) REFERENCESTEACHER (tno ),FOREIGN KEY(cno ) REFERENCES COURSE (cno )ENROLL的完整性約束：PRIMARY KEY(cno ,sno ),FOREIGN KEY(sno ) REFERENCESSTUDENT(sno ),FOREIGN KEY(cno ) REFERENCES COURSE (cno )三、系統(tǒng)的實現(xiàn)題目21 .創(chuàng)建數(shù)據(jù)庫創(chuàng)建名為“ SCTE”的數(shù)據(jù)庫E J SCTE田U數(shù)據(jù)彈關(guān)系圖陽I表叵LJ視圖耳LJ同義

7、詞用U可編程性f+ _J Service BrokerE 口I存儲用U安全性2 .創(chuàng)建各基本表COURSE6TEACHERCREATE TABLE COURSE(cno CHAR (10) PRIMARY KEY, cname CHAR (30), department CHAR (30), credit SMALLINT );CREATE TABLE STUDENT(sno CHAR (10) PRIMARY KEY, sname CHAR (30), address CHAR (30), zip CHAR (10), city CHAR (20), state CHAR (10), sex

8、 CHAR (2) );CREATE TABLE TEACHER(tno CHAR (10) PRIMARY KEY, tname CHAR (20), phone CHAR (10), salary NUMERIC (10,2) );CREATE TABLE SECTION (secno CHAR (2), tno CHAR (10), cno CHAR (10), sqty INT,PRIMARY KEY(secno ,tno ,cno ),FOREIGN KEY(tno ) REFERENCES TEACHER (tno ), FOREIGN KEY(cno ) REFERENCES C

9、OURSE (cno ) )； CREATE TABLE ENROLL(cno CHAR (10), secon CHAR (2), sno CHAR (10), grade INT, PRIMARY KEY(cno ,sno ), FOREIGN KEY(sno) REFERENCES STUDENT(sno), FOREIGN KEY(cno ) REFERENCES COURSE (cno );< rii4消息命令已成功完成。日二表用口僦表 口 dbo,COURSE田 3 dbo,ENROLL0 5 dbo,SECTION 田 d bo, STU DE NT0 3 dbo,TtAC

10、HER3 .完成數(shù)據(jù)的錄入COURSE表錄入數(shù)據(jù)INSERTINTO COURSEVALUES ('450' ,'Western Civilization' ,'History' ,'3');INSERTINTO COURSEVALUES ('730' ,'Calculus Iv' ,'Math' ,'4');INSERTINTO COURSEVALUES('290' ,'English Composition' ,'Engli

11、sh' ,'3');INSERTINTO COURSEVALUES ('480' ,'Compiler Writing' ,'Computer Science' ,'3');SELECT*FROM COURSESTUDENT表錄入數(shù)據(jù)INSERTINTO STUDENTVALUES ('148' ,'Susan powell' ,'534 East River Dr' ,'19041' ,'Haverford' ,'PA

12、','F');INSERTINTO STUDENTVALUES ('210' ,'Bob Dawson' ,'120 South Jefferson' ,'02891' ,'Newport' ,'RI','M');INSERTINTO STUDENTVALUES ('298' ,'Howard Mansfield' ,'290 Wynkoop Drive' ,'22180' ,'Vienn

13、a' ,'VA' ,'M' );INSERTINTO STUDENTVALUES ('348' ,'Susan Pugh' ,'534 East Hampton Dr' ,'06107' ,'Hartford' ,'CN' ,'F');INSERTINTO STUDENTVALUES ('349' ,'Joe Adams' ,'73 Emmerson Street' ,'19702'

14、; ,'Newark' ,'DE' ,'M');INSERTINTO STUDENTVALUES ('354' ,'Janet Ladd' ,'441 10th Street' ,'18073' ,'Pennsburg' ,'PA' ,'F');INSERTINTO STUDENTVALUES ('410' ,'Bill Jone' ,'120 South Harrison' ,'

15、92660' ,'Newport' ,'CA' ,'M');INSERTINTO STUDENTVALUES ('473' ,'Carol Dean' ,'983 Park Avenue' ,'02169' ,'Boston' ,'MA' ,'F');INSERTINTO STUDENTVALUES ('548' ,'Allen thomas' ,'238 West Ox Road'

16、; ,'60624' ,'Chicago' ,'IL','M' );INSERTINTO STUDENTVALUES ('558' ,'Val Shipp' ,'238 Westport Road' ,'60556' ,'Chicago' ,'IL','F');INSERTINTO STUDENTVALUES ('649' ,'John Anderson' ,'473 Emmory

17、 Street' ,'10008' ,'New York' ,'NY' ,'M' );INSERTINTO STUDENTVALUES ('654' ,'Janet Yhomas' ,'441 6th Street' ,'16510' ,'Erie' ,'PA','F');SELECT*FROM STUDENTTEACHER錄入數(shù)據(jù)INSERTINTO TEACHERVALUES ('303' ,

18、'Dr.Horn' ,'257-3049' ,27540.00 );INSERTINTO TEACHERVALUES ('290' ,'Dr.Lowe' ,'257-2390' ,31450.00 );INSERTINTO TEACHERVALUES ('430' ,'Dr.Engle' ,'56-4621' ,38200.00 );INSERTINTO TEACHERVALUES ( 180 ,'Dr.Cooke' ,'257-8088

19、9; ,29560.00 );INSERTINTO TEACHERVALUES ( 560 ,'Dr.Olsen' ,'257-8086' ,31778.00 );INSERTINTO TEACHERVALUES ( 784 ,'Dr.Scango' ,'257-3046' ,32098.00 );SELECT*FROM TEACHERSECTION 表錄入數(shù)據(jù)INSERTINTO SECTIONVALUES('1','303' ,'450' ,2);INSERTINTO SECTI

20、ONVALUES('1','290' ,'730' ,6);INSERTINTO SECTIONVALUES('1','430' ,'290' ,3);INSERTINTO SECTIONVALUES('1','180' ,'480' ,3);INSERTINTO SECTIONVALUES('2','560' ,'450' ,2);INSERTINTO SECTIONVALUES('2'

21、,'784' ,'480' ,2);SELECT*FROM SECTIONENROLL 表錄入數(shù)據(jù)INSERTINTO ENROLLVALUES ('730' ,'1','148' ,'3');INSERTINTO ENROLLVALUES ('450' ,'2','210' ,'3');INSERT第 10頁INTO ENROLLVALUES ('730' ,'1','210' ,

22、9;1');INSERTINTO ENROLLVALUES ('290' ,'1','298' ,'3');INSERTINTO ENROLLVALUES ('480' ,'2','298' ,'3');INSERTINTO ENROLLVALUES ('730' ,'1','348' ,'2');INSERTINTO ENROLLVALUES ('290' ,'1'

23、;,'349' ,'4');INSERTINTO ENROLLVALUES ('480' ,'1','348' ,'4');INSERTINTO ENROLLVALUES ('480' ,'1','410' ,'2');INSERTINTO ENROLLVALUES ('450' ,'1','473' ,'2');INSERTINTO ENROLLVALUES ('

24、730' ,'1','473' ,'3');INSERTINTO ENROLLVALUES ('480' ,'2','473' ,'0');INSERTINTO ENROLLVALUES ('290' ,'1','548' ,'2');INSERTINTO ENROLLVALUES ('730' ,'1','558' ,'3');INSERTINTO

25、 ENROLLVALUES ('730' ,'1','649' ,'4');INSERTINTO ENROLLVALUES ('480' ,'1','649' ,'4');INSERTINTO ENROLLVALUES ('450' ,'1','654' ,'4');INSERTINTO ENROLLVALUES ('450' ,'2','548' ,

26、9;1');SELECT*FROM ENROLLCOURSE二結(jié)果消息enocnamedepartmentcredit1na 1=) :290:English ComposjUonEnglish32V;'estem CivilizationHistory334WCompiler WritingComputef Scenes 34730Calc?dus IvMath4STUDENT皓果j消息snosnameaddresszpdtystatesex1i 14S ISusan powell534 East River Dr麗HaveffondPAF2210Bob Dawsan120

27、South Jefferson02831NewportRIM329EHoward Mansfield290 Wynkoop Drive221 BOViennaVAM4Susan Pugh534 East Hampton DrD6107HartfondCNF5349Joe Adams73 Enmerson Street19702NewarkDEM6354Janet Ladd441 10th Street18073Pennsb.PAF7410Bill Jone72D South Hanison32660NewportCAMS473Card Dean9S3 Park AvenueD2169Eosto

28、nMAF9548Alen tharras238 West Ox Road6OS24ChicagoLM10558Vai Shipp23S Westport Road60556ChicagoILF11649Join jVideraon473 Emmery Street1DDDBNew YorkNYM12654 Janet Yhomas 441 Qh Street 16510 Erie PA F3壹詢已成功執(zhí)行.TEACHER結(jié)果_J消息tnotnamephonesalary1ai-i-iui-ibii-i-ii 180 i . JiDr. Cooke2578029560.002290Dr. Low

29、e257239031450 003303Dr. Hom257-3W2754。 DO4430Dr .Engle256462138200.005560Dr. O«en257B0S63177BDD6734Dr. Sea.25730463209s.00SECTION結(jié)果與消息seen 口tnaenosqty111 130 J KJ480321期73063130345024143029035250450262784480218730 1649 44 .檢索系名為“ Math ”和“ English ”的課程信息SELECT*5 .按字母順序列出教師姓名和電話號碼SELECT tname ,ph

30、oneFROM TEACHERORDER BY tname翥果；一3消息tname phoneLkCooke 1 257-8058 MI-IMMIUMI-IUMI-JUI-iE2Dr.Engie2 轉(zhuǎn)46213Dr. Hom257-3M94Dr. Laws257-23905Dr.Olsen25780866Dr.Scango257304$6 .檢索電話號碼不是以“ 257”打頭的教師姓名和電話號碼SELECT tname ,phone FROM TEACHERWHERE phone not like '257%'第 26 頁結(jié)果消息tnamephone1 f'Dr'

31、;&igle-1 256坐217 .檢索數(shù)學(xué)系所有成績大于3的課程名、系名、學(xué)分SELECT cname .department ,credit FROM COURSEWHERE cno IN(SELECTcnoFROM ENROLLWHERE grade >3 AND cno ='730');cnamedepartment credit1 i Calculus Iv i Math 1 1©食道已成功執(zhí)行.8 .檢索沒有選修任何課的學(xué)生姓名、學(xué)號SELECT sno ,sname FROM STUDENTWHERE NOT EXISTSSELECT*FR

32、OM ENROLLWHERE STUDENT.sno =ENROLL .sno );sno sname，rrih，rir，EL1 354 ! Janet Ldd登詢已成功執(zhí)行.9 .檢索沒有選修課程“ Calculus Iv ”的學(xué)生學(xué)號SELECTsnoFROM STUDENTWHERE sno NOT IN(SELECT STUDENT.snoFROM COURSE .STUDENT, ENROLLWHERE cname ='Calculus Iv'AND COURSE.cno =ENROLL.cnoAND STUDENT.sno =ENROLL .sno);結(jié)果心消息&q

33、uot;Baa 一 " *： 1j 288 i-SU I- MUI-1 J234333544410554B6654*10.檢索至少選修教師“ Dr. Lowe ”所開全部課程的學(xué)生學(xué)號SELECTDINSTINCT snoFROM ENROLL ENROLLXWHERE NOT EXISTS(SELECT*FROM ENROLL ENROLLYWHERE cno in(SELECTcnoFROM TEACHER ,SECTION ,WHERE tname ='Dr.Lowe'AND TEACHER.tno =SECTION .tno )AND NOT EXISTS(S

34、ELECT*FROM ENROLL ENROLLZWHERE ENROLLZ.sno = ENROLLX .snoAND ENROLLZ.cno =ENROLLY.cno )二I結(jié)果J消息1! 14B 12l ： - -Ji21Q3348447355586649用查洵已成功執(zhí)行.（修改數(shù)據(jù)驗證，該老師教授課程號為730和500 ,只有學(xué)號148的學(xué)生同時選擇了這兩 門課)SECTIONsecno tno eno sqty1 Pl I 1SQ 480 32129C&0013129C730641M34502514302903256C4502727844802ENROLL13500114S

35、3147301148 3結(jié)果國結(jié)果心消息sno11 .檢索每門課學(xué)生登記的人數(shù)、相應(yīng)的課程名、課程號、分組號SELECTDISTINCT secno ,ENROLL.cno ,cname ,sqtyFROM ENROLL ,COURSE ,SECTIONWHERE SECTION .cno =ENROLL.cnoAND COURSE .cno =ENROLL.cnosecno-GIOcnamesqty1 ! n a5 1,'8 290English Composition321450Vestein Gviiization2314S0CbfTipiler Writng3A1730Calc

36、ulus IvE52450'estem CivriiztiDn2624S0Compiler Wnting2,查泡已成功執(zhí)行.12 .檢索選修兩門以上課程的學(xué)生姓名SELECTsnameFROM STUDENTWHERE sno in ( SELECTsnoFROM ENROLLGROUP BY snoHAVING COUNT (*)> 2)sname:Dean 13 .檢索只有男生選修的課程和學(xué)生名SELECT cname ,snameFROM COURSE ,STUDENT,ENROLLWHERE STUDENT.sno =ENROLL.snoAND COURSE .cno =

37、ENROLL.cnoAND COURSE .cno in(SELECT cnoFROM COURSEWHERE cno not in(SELECTDISTINCT cnoFROM STUDENT,ENROLLWHERE sex ='F' and STUDENT.sno =ENROLL .sno)cnamesname1 i English Composition i Howani Mansffeld2 English Composition Joe Adams3 English Composition Allen thornas壹詢已成不執(zhí)行.14.檢索所有學(xué)生都選修的課程名、學(xué)

38、生名、授課教師名、該生成績SELECTcname ,sname ,tname ,gradeFROM STUDENT,TEACHER .SECTION ,ENROLL ,COURSEWHERE COURSE. cno = ENROLL .cnoAND STUDENT.sno =ENROLL .snoAND TEACHER .tno =SECTION .tnoAND SECTION .cno =ENROLL.cnoAND SECTION.secno =ENROLL .seconAND COURSE.cno =( SELECTcnoFROM ENROLLGROUP BY cnoHAVING COUN

39、T (*)= 12)cname sname tname grade3查徇已成功執(zhí)行.15.刪去名為"Joe Adams的所有記錄DELETEFROM STUDENTWHERE sname ='Joe Adams'SELECTFROM STUDENTsnosnarneaddresszipdtyatesex1W Susan poweJI5J4 East River Dr19041HaverfoniPAF2210Bob Dawson120 South Jeffeon02891NewportRIM3258Howard Ma.230 Wynkoop Dfive22180Vien

40、naVAM434sSusan Pugh534 East Hampto.06107HartfoniCNF53&4Janet Ladd441 1Dth Street1B073Pennsb.PAF6410Bill Jane120 South Hamson926G0NewportCAM7473Carol Dean983 Park Avenue02169BostonMAFl jief rrw «ii-r c m1II-«查洵已成功執(zhí)行.la54sAlen thomas2JS /est Ox Rosd6OG24ChicagoILM9558Shpp238 Westpoit Roa

41、d6D556ChicagoILF10&49John Aider.473 EmniCFfy Street10003New YorkNYM11654Janet Yh441 Qh Street1651。BriePAF16 .把教師“ Scango ”的編號改為“ 666 UPGRADE TEACHERSETtno ='666'WHERE tname ='Dr.Scango 'SELECT* FROM TEACHERG 666 Dr Sa. 257-3046 劃98 0017 .統(tǒng)計教師“ Engle ”教的英語課的學(xué)生平均分SELECTAVG (grade )

42、AVGFROM ENROLLWHERE grade in(SELECTgradeFROM TEACHER,SECTION ,ENROLLWHERE tname ='Dr.Engle'AND TEACHER.tno =SECTION .tnoAND SECTION .cno =ENROLL.cnoAND ENROLL.secno =SECTION .secno )AVG18 .統(tǒng)計各門課程的選課人數(shù)SELECTCOURSE .cname ,COUNT (ENROLL.sno )FROM ENROLL,COURSEWHERE ENROLL .cno =COURSE .cnoGROU

43、P BY COURSE .cname ;口結(jié)果心cnameQTY1 j Calcutus Ivj 6、r r rr st r i si r l，r l i r r l i r r l，r m r r l，r rf2 Compiler Writing53 Eng胎h Composition34 Westen Civilization419.輸出如下報表:學(xué)生名課程名教師名成績SELECTsname 學(xué)生名，cname 課程名，tname 教師名，grade 成績 FROM ENROLL ,STUDENT,COURSE ,TEACHER .SECTIONWHERE ENROLL .sno =STU

44、DENT.snoAND SECTION.cno = COURSE .cnoAND ENROLL .secno = SECTION .secnoAND ENROLL .cno = SECTION .cnoAND SECTION.tno = TEACHER.tno學(xué)生名課程名教師名成績1Susan PughCbmpilef WritingDr. Cooke42Bill JoneCompiler WritingDr. Cooke23John Ande Compiler WritingDr. CookeA4Susan po.Calculus IvDr. Lowe35Bob Daws.Cdculus I

45、vDr. Lowe1Susan PughCalculus IvDr. Lowei 2 i7Card DeanCalcdus IvDr. Lowe38Vai ShippCalcdus IvDr. Lowe3學(xué)生名課程名教師名成績9Jahn Ande.Calculus IvDr. LoweA10Carol DeanWestern Cvili.Dr Hom211Janet Vho.Western Qvili.Dr. Hom412l-kwand M .English Comp.”Dr .Engle313Joe AdamsEnglish Camp.Dr EngleA14Alen thomasEnglis

46、h Comp.Dr Bigle215Bob Daws.Western Cvili.Dr. Olsen316Allen thomasWestern Qvili.Dr. Olsen117Howard M.Compiler WritingDr Sea.318Carol DeanCompiler WritingDr Sea.0*20.定義并驗證觸發(fā)器，當(dāng)?shù)怯洷碓黾右粭l新的記錄時，自動在分組表中更新相應(yīng)屬性。CREATE TRIGGER ENROLL_1ON ENROLLAFTER INSERTASUPDATE SECTIONSETsqty=sqty+1FROM SECTION .insertedWHE

47、RE inserted .secno =SECTION .secnoAND inserted .cno =SECTION .cno 驗證結(jié)果：INSERTINTO ENROLLVALUES ('450' ,'2','354' ,'1')SELECT*FROM SECTION原始數(shù)據(jù)執(zhí)行結(jié)果口結(jié)果消息secno tnacno sqtyUldlklUIBIUIUIEIUI._1 I 1! 1804S0 321 290500 1_312M730 6_41303450 251g29C 362560450 3127S44S0 2四、課程設(shè)計

48、小結(jié)在本次課設(shè)之前，我已經(jīng)在前段學(xué)習(xí)期間上機(jī)編寫過類似相關(guān)的SQL語句來完成對于學(xué)生-課程-選課的信息查詢，這次在原先實驗的基礎(chǔ)上添加了分組和教師表，創(chuàng)建數(shù)據(jù)庫、 基本表和錄入數(shù)據(jù)的過程與上機(jī)實驗基本相同，因此很容易就完成了前三個步驟，在這個過程中對于數(shù)據(jù)庫、表的創(chuàng)建以及數(shù)據(jù)的輸入的操作都更加的熟練。題目4-19都是對于表中數(shù)據(jù)進(jìn)行查詢、修改、刪除等操作，并且運用了ORDER BY、GROUP BY等功能短語以及 COUNT、AVG等聚集函數(shù)。前兩題都是基本的查詢，比較簡 單，但由于輸入數(shù)據(jù)時 Dr.Engle中的.多按了一個空格，結(jié)果在調(diào)試的時候出現(xiàn)的是錯。誤的結(jié)果，結(jié)果不正確（見截圖tna

49、mephone16藐Q " , 5M621i r rir w m le， r r u m m a e m " h2 Dr Cooke257 8083第10題是本次課設(shè)最難的題目，自己也嘗試著去做，但是由于理解的失誤以及數(shù)據(jù)的 巧合性，錯誤的 SQL語句得出的結(jié)果卻是正確的。這就導(dǎo)致了自己以為解決了這道難題， 以至于在給老師檢查的時候被指出嚴(yán)重的錯誤，感到很羞愧。后來仔細(xì)分析題意，按照所有的關(guān)鍵字來編寫查詢語句，參照書上關(guān)于not exists語句以及嵌套循環(huán)的語句，用謂詞演算將題目轉(zhuǎn)換成邏輯運算，等價為“沒有這樣課程y,老師Dr.Lowe教授了這門課，而學(xué)生X沒有選”。p表

50、示“老師教授了課程y”，q表示“學(xué)生X選了課y"，等價于3 y（p An q）。 參照書上的例題，分層次地編寫功能語句。因為題目的巧合性所以在檢驗時修改了數(shù)據(jù)，添加了該老師新開另一門課，只有學(xué)號148的學(xué)生同時選擇了這門老師的兩門課。然后再去驗證結(jié)果發(fā)現(xiàn)是正確的。結(jié)果.以消息1|148 !第13題的題目剛開始理解的不透徹，所以直接查詢了男生選修的課程，得到的結(jié)果有10個，但照表檢查可以看出有些課程仍是有女生選的，與題目“只有男生選修”的條件不符，因此重新分析，發(fā)現(xiàn)可以使用嵌套查詢先用子查詢把女生選擇的課程選出來，然后父查詢在子查詢的結(jié)果之外（not in ）查詢，這樣就把女生選修的課

51、程出去，這樣就避免出現(xiàn)選擇的課程男、女生都選修的錯誤。（見截圖）snani 目12j Enghsh CompositiCMi J English CompositionHoward MansfieldJoe Adams3English CocnpositionAllen thomas4Western Gvili rationBob Dawson5Western CivilizationAllen thomas«Compiler WritingHoward Mansfidd7Campiter WritinaBil Jone第15、16題是對于學(xué)生、教師表的修改和刪除。在執(zhí)行過程中程序一直報錯，始終找不出問題所在。后來經(jīng)過詢問同學(xué)，發(fā)現(xiàn)SECTION和ENROLL表中引用了這兩個表中的sno和 tno, 由于其引用關(guān)系，所以無法對于這兩個表進(jìn)行修改。因此需要先刪除外碼的引用關(guān)系，然后才能對其進(jìn)行修改和刪除。第 20 題定義觸發(fā)器這個比較陌生，在學(xué)習(xí)的時候就不是很掌握，因此對于這個題目第一印象就是不想去做，我不會。 但是后來通過翻閱書本并且詢問同學(xué)，并且自己不斷的摸索，雖然起初一直報錯，但是我并沒有氣餒，在各種幫助和努力下終于實現(xiàn)了最后一個功能。不過在給老師檢查的過程中，發(fā)現(xiàn)UPDATE 語句后面自己添加了FR