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

1、數(shù)據(jù)庫課程設(shè)計班級物聯(lián)網(wǎng) 1202學(xué)號3120611027姓名楊璐指導(dǎo)老師年軼2014年1 月目 錄一、引言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第 1 頁一、引言1目的課程設(shè)計為學(xué)生提供了一個既動手又動腦，獨立實踐的機(jī)會，將課本上的理論知識和實際有機(jī)的結(jié)合起來，鍛煉學(xué)生的分析解決實際問題的能力。提高學(xué)生適應(yīng)實際，實踐編程的能力。課程設(shè)計的目的：(1) 加深對數(shù)據(jù)庫系統(tǒng)、軟件工程、程序設(shè)計語言的理論知識的理解和應(yīng)用水平；(2) 在理論和實驗教學(xué)基礎(chǔ)上進(jìn)一步鞏固已學(xué)基本理論及應(yīng)用知識并加以綜合提高

2、；(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(SQLServer) 的環(huán)境上建立一個數(shù)據(jù)庫應(yīng)用系統(tǒng)。 要求把現(xiàn)實世界的事物及事物之間的復(fù)雜關(guān)系抽象為信息世界的實體及實體之間聯(lián)系的信息模型，再轉(zhuǎn)換為機(jī)器世界的數(shù)據(jù)模型和數(shù)據(jù)文件，并對數(shù)據(jù)文件實施檢索、更新和控制等操作。(1) 用 E-R

3、 圖設(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)自我評價第 2 頁二、系統(tǒng)的分析與設(shè)計1概念設(shè)計2邏輯設(shè)計STUDENT(sno,sname,address,zip,state,city,sex) sno是主碼TEACHER(tno,tname,phone,salary)tno是主碼COURSE(cno,cname,depar

4、tment,credit)cno是主碼SECTION(secno,cno,tno,sqty)secno、tno 、cno 是主碼， cno、tno是外碼ENROLL(secno,cno,sno,grade)cno,sno 是主碼， cno,sno 是外碼第 3 頁3系統(tǒng)功能結(jié)構(gòu)第 4 頁4完整性設(shè)計/* 包含：關(guān)系主碼、外碼、CHECK約束。并給出相應(yīng)的SQL語句 */COURSE的完整性約束 :cnoCHAR( 10 )PRIMARYKEYSTUDENT的完整性約束:snoCHAR( 10 )PRIMARYKEYTEACHER的完整性約束:tnoCHAR( 10 )PRIMARYKEYSEC

5、TION的完整性約束:PRIMARYKEY( secno, tno , cno ),FOREIGNKEY( tno )REFERENCESTEACHER( tno ),FOREIGNKEY( cno )REFERENCESCOURSE( cno )ENROLL的完整性約束 :PRIMARYKEY( cno , sno ),FOREIGNKEY( sno )REFERENCESSTUDENT ( sno ),FOREIGNKEY( cno )REFERENCESCOURSE( cno )第 5 頁三、系統(tǒng)的實現(xiàn)題目 21. 創(chuàng)建數(shù)據(jù)庫創(chuàng)建名為“ SCTE”的數(shù)據(jù)庫2. 創(chuàng)建各基本表CREATET

6、ABLECOURSE( cnoCHAR( 10 )PRIMARYKEY,cnameCHAR( 30 ),departmentCHAR( 30 ),creditSMALLINT);CREATE TABLESTUDENT( snoCHAR( 10 )PRIMARYKEY,snameCHAR( 30 ),第 6 頁addressCHAR( 30 ),zipCHAR( 10 ),cityCHAR( 20 ),stateCHAR( 10 ),sexCHAR( 2);CREATE TABLETEACHER( tnoCHAR( 10 )PRIMARYKEY,tnameCHAR( 20 ),phoneCHAR

7、( 10 ),salaryNUMERIC ( 10 , 2 );CREATE TABLESECTION( secnoCHAR( 2),tnoCHAR( 10 ),cnoCHAR( 10 ),sqtyINT ,PRIMARYKEY( secno, tno , cno ),FOREIGNKEY( tno )REFERENCES TEACHER( tno ),FOREIGNKEY( cno )REFERENCES COURSE( cno );CREATETABLEENROLL( cnoCHAR( 10 ),seconCHAR( 2),snoCHAR( 10 ),gradeINT ,PRIMARYKE

8、Y( cno , sno ),FOREIGNKEY( sno )REFERENCES STUDENT( sno ),FOREIGNKEY( cno )REFERENCES COURSE( cno );第 7 頁3. 完成數(shù)據(jù)的錄入COURSE表錄入數(shù)據(jù)INSERTINTOCOURSEVALUES ( '450', 'Western Civilization', 'History', '3');INSERTINTOCOURSEVALUES ( '730', 'Calculus Iv', 'Ma

9、th', '4' );INSERTINTOCOURSEVALUES ( '290', 'English Composition', 'English', '3');INSERTINTOCOURSEVALUES ( '480', 'Compiler Writing', 'Computer Science', '3' );SELECT * FROM COURSESTUDENT 表錄入數(shù)據(jù)INSERTINTOSTUDENTVALUES ( '

10、148', 'Susan powell', '534 East RiverDr' , '19041', 'Haverford', 'PA', 'F');INSERTINTOSTUDENTVALUES ( '210', 'Bob Dawson', '120 SouthJefferson', '02891', 'Newport', 'RI', 'M');INSERTINTOSTUDEN

11、TVALUES ( '298', 'Howard Mansfield', '290 WynkoopDrive', '22180', 'Vienna', 'VA', 'M');INSERTINTOSTUDENTVALUES ( '348', 'Susan Pugh', '534 East HamptonDr' , '06107', 'Hartford', 'CN', 'F'

12、);INSERTINTOSTUDENTVALUES ( '349', 'Joe Adams', '73 EmmersonStreet', '19702', 'Newark', 'DE' , 'M');INSERTINTOSTUDENTVALUES ( '354', 'Janet Ladd', '441 10thStreet', '18073', 'Pennsburg', 'PA', 

13、9;F');INSERTINTOSTUDENTVALUES ( '410', 'Bill Jone', '120 South第 8 頁Harrison', '92660', 'Newport', 'CA', 'M');INSERTINTOSTUDENTVALUES ( '473', 'CarolDean' , '983 Park Avenue' , '02169', 'Boston', '

14、MA', 'F');INSERTINTOSTUDENTVALUES ( '548', 'Allen thomas', '238 West OxRoad', '60624', 'Chicago', 'IL', 'M');INSERTINTOSTUDENTVALUES ( '558', 'Val Shipp', '238 WestportRoad', '60556', 'Chicago'

15、;, 'IL', 'F');INSERTINTOSTUDENTVALUES ( '649', 'John Anderson', '473 Emmory Street', '10008', 'NewYork', 'NY' , 'M' );INSERTINTOSTUDENTVALUES ( '654', 'JanetYhomas', '4416thStreet', '16510', '

16、Erie', 'PA', 'F');SELECT * FROM STUDENTTEACHER錄入數(shù)據(jù)INSERTINTOTEACHERVALUES ( '303' , 'Dr.Horn', '257-3049', 27540.00);INSERTINTOTEACHERVALUES ( '290' , 'Dr.Lowe', '257-2390', 31450.00);INSERTINTOTEACHERVALUES ( '430' , 'D

17、r.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 * F

18、ROM TEACHERSECTION 表錄入數(shù)據(jù)INSERT第 9 頁INTOSECTIONVALUES ( '1', '303', '450', 2 );INSERTINTOSECTIONVALUES ( '1', '290', '730', 6 );INSERTINTOSECTIONVALUES ( '1', '430', '290', 3 );INSERTINTOSECTIONVALUES ( '1', '180'

19、, '480', 3 );INSERTINTOSECTIONVALUES ( '2', '560', '450', 2 );INSERTINTOSECTIONVALUES ( '2', '784', '480', 2 );SELECT * FROM SECTIONENROLL 表錄入數(shù)據(jù)INSERTINTOENROLLVALUES ( '730' , '1', '148', '3');INSERTINTOENROLLVA

20、LUES ( '450' , '2', '210', '3');INSERTINTOENROLLVALUES ( '730' , '1', '210', '1');INSERTINTOENROLLVALUES ( '290' , '1', '298', '3');INSERTINTOENROLLVALUES ( '480' , '2', '298', 

21、9;3');INSERTINTOENROLLVALUES ( '730' , '1', '348', '2');INSERTINTOENROLLVALUES ( '290' , '1', '349', '4');INSERTINTOENROLLVALUES ( '480' , '1', '348', '4');第 10 頁INSERTINTOENROLLVALUES ( '480'

22、 , '1', '410', '2');INSERTINTOENROLLVALUES ( '450' , '1', '473', '2');INSERTINTOENROLLVALUES ( '730' , '1', '473', '3');INSERTINTOENROLLVALUES ( '480' , '2', '473', '0');INSERTINTO

23、ENROLLVALUES ( '290' , '1', '548', '2');INSERTINTOENROLLVALUES ( '730' , '1', '558', '3');INSERTINTOENROLLVALUES ( '730' , '1', '649', '4');INSERTINTOENROLLVALUES ( '480' , '1', '649&#

24、39;, '4');INSERTINTOENROLLVALUES ( '450' , '1', '654', '4');INSERTINTOENROLLVALUES ( '450' , '2', '548', '1');SELECT * FROM ENROLLCOURSE第 11 頁STUDENTTEACHERSECTION第 12 頁ENROLL4. 檢索系名為“ Math”和“ English ”的課程信息SELECT*FROM COURSEWHE

25、RE department='Math'OR department='English'5. 按字母順序列出教師姓名和電話號碼SELECTtname , phoneFROM TEACHERORDER BY tname第 13 頁6. 檢索電話號碼不是以“ 257”打頭的教師姓名和電話號碼SELECTtname , phoneFROM TEACHERWHERE phone not like'257%'7. 檢索數(shù)學(xué)系所有成績大于 3 的課程名、系名、學(xué)分SELECTcname , department, creditFROM COURSEWHERE

26、cnoIN( SELECTcnoFROM ENROLLWHERE grade>3AND cno = '730');8. 檢索沒有選修任何課的學(xué)生姓名、學(xué)號SELECTsno , snameFROM STUDENT WHERE NOT EXISTS(SELECT*FROM ENROLLWHERE STUDENT. sno = ENROLL. sno );9. 檢索沒有選修課程“ Calculus Iv ”的學(xué)生學(xué)號SELECTsnoFROM STUDENTWHERE snoNOT IN第 14 頁( SELECTSTUDENT . snoFROM COURSE, STUDE

27、NT , ENROLLWHERE cname = 'Calculus Iv'AND COURSE. cno = ENROLL . cnoAND STUDENT. sno = ENROLL . sno );*10. 檢索至少選修教師“Dr. Lowe ”所開全部課程的學(xué)生學(xué)號SELECTDINSTINCTsnoFROM ENROLLENROLLXWHERE NOT EXISTS( SELECT*FROM ENROLLENROLLYWHERE cnoin(SELECTcnoFROM TEACHER, SECTION ,WHERE tname = 'Dr.Lowe'A

28、ND TEACHER. tno =SECTION . tno )AND NOT EXISTS( SELECT*FROM ENROLLENROLLZWHERE ENROLLZ . sno = ENROLLX . snoAND ENROLLZ . cno =ENROLLY . cno )（修改數(shù)據(jù)驗證，該老師教授課程號為730 和 500，只有學(xué)號148 的學(xué)生同時選擇了這兩門課）SECTION第 15 頁ENROLL結(jié)果11. 檢索每門課學(xué)生登記的人數(shù)、相應(yīng)的課程名、課程號、分組號SELECTDISTINCTsecno, ENROLL . cno , cname , sqtyFROM ENROL

29、L, COURSE, SECTIONWHERE SECTION . cno = ENROLL . cnoAND COURSE. cno = ENROLL . cno12. 檢索選修兩門以上課程的學(xué)生姓名SELECTsnameFROM STUDENTWHERE snoin (SELECTsnoFROM ENROLLGROUP BY snoHAVINGCOUNT(*)>2 )13. 檢索只有男生選修的課程和學(xué)生名SELECTcname , sname第 16 頁FROM COURSE , STUDENT, ENROLLWHERE STUDENT . sno = ENROLL . snoAND

30、 COURSE. cno = ENROLL . cnoAND COURSE. cnoin( SELECTcnoFROM COURSEWHERE cnonotin( SELECTDISTINCTcnoFROM STUDENT, ENROLLWHERE sex= 'F'andSTUDENT . sno = ENROLL . sno )14. 檢索所有學(xué)生都選修的課程名、學(xué)生名、授課教師名、該生成績SELECTcname , sname , tname , gradeFROM STUDENT , TEACHER , SECTION , ENROLL , COURSE WHERE CO

31、URSE. cno =ENROLL . cnoAND STUDENT. sno = ENROLL . snoAND TEACHER. tno = SECTION . tnoAND SECTION . cno = ENROLL . cnoAND SECTION . secno= ENROLL . seconAND COURSE. cno =( SELECTcnoFROMENROLLGROUP BYcnoHAVINGCOUNT (*)=12 )15. 刪去名為“ Joe Adams”的所有記錄DELETEFROM STUDENTWHERE sname = 'Joe Adams'SE

32、LECT*FROM STUDENT第 17 頁16. 把教師“ Scango”的編號改為“ 666”UPGRADE TEACHERSETtno ='666'WHERE tname = 'Dr.Scango 'SELECT*FROM TEACHER17. 統(tǒng)計教師“ Engle ”教的英語課的學(xué)生平均分SELECTAVG( grade) AVGFROM ENROLLWHERE gradein(SELECTgradeFROM TEACHER, SECTION , ENROLLWHERE tname = 'Dr.Engle'AND TEACHER. t

33、no =SECTION . tnoAND SECTION . cno =ENROLL . cnoAND ENROLL . secno= SECTION . secno)18. 統(tǒng)計各門課程的選課人數(shù)SELECTCOURSE. cname , COUNT( ENROLL.sno)FROM ENROLL, COURSEWHERE ENROLL . cno =COURSE. cnoGROUP BY COURSE. cname ;第 18 頁19. 輸出如下報表：學(xué)生名課程名教師名成績SELECT sname 學(xué)生名 , cname 課程名 , tname 教師名 , grade 成績 FROM EN

34、ROLL, STUDENT , COURSE, TEACHER, SECTION WHERE ENROLL . sno =STUDENT . snoAND SECTION . cno=COURSE. cnoAND ENROLL . secno=SECTION . secnoAND ENROLL . cno=SECTION . cnoAND SECTION . tno=TEACHER. tno*20. 定義并驗證觸發(fā)器，當(dāng)?shù)怯洷碓黾右粭l新的記錄時，自動在分組表中更新相應(yīng)屬性。CREATETRIGGERENROLL_1ON ENROLLAFTER INSERTAS第 19 頁UPDATESECTI

35、ONSETsqty=sqty+1FROM SECTION , insertedWHERE inserted. secno= SECTION . secnoANDinserted. cno = SECTION . cno驗證結(jié)果：INSERTINTOENROLLVALUES ( '450', '2', '354', '1')SELECT*FROM SECTION原始數(shù)據(jù)執(zhí)行結(jié)果第 20 頁四、課程設(shè)計小結(jié)在本次課設(shè)之前，我已經(jīng)在前段學(xué)習(xí)期間上機(jī)編寫過類似相關(guān)的SQL 語句來完成對于學(xué)生 -課程 -選課的信息查詢，這次在原先實驗的基礎(chǔ)

36、上添加了分組和教師表，創(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é)果不正確（見截圖 。第 10 題是本次課設(shè)最難的題目，自己也嘗試著去做，但是由于理解的失誤以及數(shù)據(jù)的巧合性，錯誤的SQL 語句得出的結(jié)果卻是正確

37、的。這就導(dǎo)致了自己以為解決了這道難題，以至于在給老師檢查的時候被指出嚴(yán)重的錯誤，感到很羞愧。 后來仔細(xì)分析題意，按照所有的關(guān)鍵字來編寫查詢語句，參照書上關(guān)于not exists 語句以及嵌套循環(huán)的語句，用謂詞演算將題目轉(zhuǎn)換成邏輯運算，等價為“沒有這樣課程y，老師 Dr.Lowe 教授了這門課，而學(xué)生X沒有選”。p 表示“老師教授了課程y”， q 表示“學(xué)生X 選了課 y” ,等價于 y(p q)。參照書上的例題，分層次地編寫功能語句。因為題目的巧合性所以在檢驗時修改了數(shù)據(jù)，添加了該老師新開另一門課， 只有學(xué)號 148 的學(xué)生同時選擇了這門老師的兩門課。 然后再去驗證結(jié)果發(fā)現(xiàn)是正確的。第 13

38、題的題目剛開始理解的不透徹，所以直接查詢了男生選修的課程，得到的結(jié)果有10 個，但照表檢查可以看出有些課程仍是有女生選的，與題目“只有男生選修”的條件不符，因此重新分析， 發(fā)現(xiàn)可以使用嵌套查詢先用子查詢把女生選擇的課程選出來，然后父查詢在子查詢的結(jié)果之外（ not in）查詢，這樣就把女生選修的課程出去，這樣就避免出現(xiàn)選擇的課程男、女生都選修的錯誤。 （見截圖）第 15、 16 題是對于學(xué)生、教師表的修改和刪除。在執(zhí)行過程中程序一直報錯，始終找不出問題所在。后來經(jīng)過詢問同學(xué)，發(fā)現(xiàn) SECTION和 ENROLL表中引用了這兩個表中的 sno 和 tno, 由于其引用關(guān)系，所以無法對于這兩個表進(jìn)

39、行修改。因此需要先刪除外碼的引用關(guān)第 21 頁系，然后才能對其進(jìn)行修改和刪除。第 20 題定義觸發(fā)器這個比較陌生，在學(xué)習(xí)的時候就不是很掌握，因此對于這個題目第一印象就是不想去做， 我不會。但是后來通過翻閱書本并且詢問同學(xué)，并且自己不斷的摸索，雖然起初一直報錯， 但是我并沒有氣餒， 在各種幫助和努力下終于實現(xiàn)了最后一個功能。 不過在給老師檢查的過程中， 發(fā)現(xiàn) UPDATE語句后面自己添加了 FROM的語句， 雖然對于整個執(zhí)行結(jié)果沒有太大的影響， 但這是一個常識性的錯誤， 不應(yīng)該出現(xiàn)如此低級的錯誤， 因為當(dāng)時是想新表需要和 SECTION、ENROLL表進(jìn)行連接, 所以在寫的過程中直接添加了o=S

40、ECTION.cno AND inserted.secno=SECTION.secno的錯誤語句，而且還誤將其中的SECTION寫成了 ENROLL表，以至于在老師詢問的時候，自己也被自己的代碼弄得不知所措。 后來修改的時候索性把所有的條件語句都刪除了，并沒有進(jìn)行臨時新表inserted與 SECTION表的連接，以至于結(jié)果每一行的學(xué)生登記數(shù)量sqty都加一，得到錯誤的結(jié)果。過程比較艱辛但是最后完成的時候內(nèi)心的喜悅確實滿滿洋溢的。果然自己去努力實現(xiàn)的事情可以給自己帶來無限的快樂，自己從中收獲的經(jīng)驗和知識也是更多的。不要因為自己對于某方面不熟悉就直接放棄， 而應(yīng)該努力去嘗試一下。 只有試過了， 才知道自己到底可不可以完成這個看