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

數(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目的課程設(shè)計為學(xué)生提供了一個既動手又動腦，獨立實踐的機會，將課本上的理論知識和實際有機的結(jié)合起來，鍛煉學(xué)生的分析解決實際問題的能力。提高學(xué)生適應(yīng)實際，實踐編程的能力。課程設(shè)計的目的：(1）加深對數(shù)據(jù)庫系統(tǒng)、軟件工程、程序設(shè)計語言的理論知識的理解和應(yīng)用水平;(2）在理論和實驗教學(xué)基礎(chǔ)上進一步鞏固已學(xué)基本理論及應(yīng)用知識并加以綜合提高；(3)學(xué)會將知識應(yī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)用知識,在微機RDBMS(SQL Server）的環(huán)境上建立一個數(shù)據(jù)庫應(yīng)用系統(tǒng)。要求把現(xiàn)實世界的事物及事物之間的復(fù)雜關(guān)系抽象為信息世界的實體及實體之間聯(lián)系的信息模型，再轉(zhuǎn)換為機器世界的數(shù)據(jù)模型和數(shù)據(jù)文件，并對數(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è)計 2邏輯設(shè)計STUDENT(sno，sname,address,zip，state,city，sex) sno是主碼TEACHER（tno,tname，phone，salary） tno是主碼COURSE（cno，cname,department，credit） cno是主碼SECTION(secno，cno,tno,sqty) secno、tno、cno是主碼，cno、tno是外碼ENROLL(secno，cno，sno,grade) cno，sno是主碼，cno,sno是外碼3系統(tǒng)功能結(jié)構(gòu)4完整性設(shè)計/* 包含：關(guān)系主碼、外碼、CHECK約束.并給出相應(yīng)的SQL語句*/COURSE的完整性約束： cno CHAR(10) PRIMARY KEYSTUDENT的完整性約束: sno CHAR(10) PRIMARY KEYTEACHER的完整性約束: tno CHAR(10） PRIMARY KEYSECTION的完整性約束: PRIMARY KEY（secno,tno,cno），F(xiàn)OREIGN KEY（tno) REFERENCES TEACHER（tno),FOREIGN KEY（cno) REFERENCES COURSE（cno）ENROLL的完整性約束： PRIMARY KEY（cno，sno），F(xiàn)OREIGN KEY(sno) REFERENCES STUDENT（sno），F(xiàn)OREIGN KEY(cno） REFERENCES COURSE（cno）三、系統(tǒng)的實現(xiàn)題目2 1。創(chuàng)建數(shù)據(jù)庫創(chuàng)建名為“SCTE”的數(shù)據(jù)庫2.創(chuàng)建各基本表CREATE 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 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 COURSE（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) );3.完成數(shù)據(jù)的錄入COURSE表錄入數(shù)據(jù)INSERTINTO COURSEVALUES('450'，'Western Civilization'，'History'，'3);INSERTINTO COURSEVALUES('730，'Calculus Iv'，'Math'，4'）;INSERTINTO COURSEVALUES（290'，English Composition,English，3）;INSERTINTO COURSEVALUES(480，'Compiler Writing',Computer Science'，3）；SELECT*FROM COURSESTUDENT表錄入數(shù)據(jù)INSERTINTO STUDENT VALUES(148'，'Susan powell，534 East River Dr'，19041'，'Haverford,PA',F）;INSERTINTO STUDENT VALUES（210',Bob Dawson,'120 South Jefferson,02891',Newport，'RI'，'M');INSERTINTO STUDENT VALUES(298'，'Howard Mansfield'，'290 Wynkoop Drive'，22180,'Vienna'，VA,M');INSERTINTO STUDENT VALUES（'348，'Susan Pugh'，534 East Hampton Dr'，06107，Hartford,CN','F）；INSERTINTO STUDENT VALUES('349','Joe Adams，'73 Emmerson Street,'19702',Newark,DE'，M')；INSERTINTO STUDENT VALUES('354'，Janet Ladd,441 10th Street','18073'，'Pennsburg，PA','F);INSERTINTO STUDENT VALUES（410','Bill Jone,'120 South Harrison'，'92660','Newport，CA，M）；INSERTINTO STUDENT VALUES(473，Carol Dean,'983 Park Avenue',02169','Boston,'MA','F')；INSERTINTO STUDENT VALUES（'548',Allen thomas，'238 West Ox Road','60624',Chicago','IL',M)；INSERTINTO STUDENT VALUES(558',Val Shipp,'238 Westport Road，'60556'，'Chicago','IL','F)；INSERTINTO STUDENT VALUES（649，'John Anderson,'473 Emmory Street'，'10008',New York',NY'，M')；INSERTINTO STUDENT VALUES('654，Janet Yhomas','441 6th Street，'16510，'Erie,PA，'F)；SELECT*FROM STUDENTTEACHER錄入數(shù)據(jù)INSERTINTO TEACHERVALUES（303',Dr.Horn，2573049,27540.00)；INSERTINTO TEACHERVALUES(290'，Dr。Lowe','257-2390,31450.00);INSERTINTO TEACHERVALUES(430，Dr.Engle,564621，38200.00）;INSERTINTO TEACHERVALUES（180，'Dr.Cooke,'2578088，29560。00）;INSERTINTO TEACHERVALUES(560,'Dr.Olsen，'257-8086,31778.00);INSERTINTO TEACHERVALUES（784，Dr。Scango','257-3046，32098.00）；SELECTFROM TEACHERSECTION表錄入數(shù)據(jù)INSERTINTO SECTION VALUES('1,'303,450'，2）;INSERTINTO SECTION VALUES('1','290,'730,6);INSERTINTO SECTION VALUES（'1'，'430，290'，3）;INSERTINTO SECTION VALUES(1','180'，'480，3)；INSERTINTO SECTION VALUES（2，560,'450,2)；INSERTINTO SECTION VALUES(2','784'，'480，2);SELECT*FROM SECTIONENROLL表錄入數(shù)據(jù)INSERTINTO ENROLLVALUES（'730，'1','148',3'）；INSERTINTO ENROLLVALUES（'450','2','210','3'）；INSERTINTO ENROLLVALUES（'730,1,210，'1');INSERTINTO ENROLLVALUES（290，1，298',3')；INSERTINTO ENROLLVALUES（'480，2,298，3'）；INSERTINTO ENROLLVALUES（'730,'1'，'348'，'2'）；INSERTINTO ENROLLVALUES（'290,1',349,4）；INSERTINTO ENROLLVALUES（480','1','348,4)；INSERTINTO ENROLLVALUES（480'，1，410，2');INSERTINTO ENROLLVALUES（450，1,'473，'2);INSERTINTO ENROLLVALUES（730',1',473，3'）；INSERTINTO ENROLLVALUES(480',2，'473,0')；INSERTINTO ENROLLVALUES（'290，1'，548，'2）；INSERTINTO ENROLLVALUES（'730,'1,558,'3')；INSERTINTO ENROLLVALUES('730',1，'649,4）;INSERTINTO ENROLLVALUES（'480,1，'649，4）；INSERTINTO ENROLLVALUES（450'，1,'654','4')；INSERTINTO ENROLLVALUES（'450,'2',548'，'1)；SELECTFROM ENROLLCOURSESTUDENTTEACHERSECTIONENROLL4.檢索系名為“Math”和“English”的課程信息SELECT *FROM COURSEWHERE department = 'Math OR department = 'English'5。按字母順序列出教師姓名和電話號碼SELECT tname,phoneFROM TEACHERORDER BY tname6.檢索電話號碼不是以“257"打頭的教師姓名和電話號碼SELECT tname,phoneFROM TEACHERWHERE phone not like '2577。檢索數(shù)學(xué)系所有成績大于3的課程名、系名、學(xué)分SELECT cname，department，creditFROM COURSEWHERE cno IN（SELECT cno FROM ENROLL WHERE grade3 AND cno='730)；8.檢索沒有選修任何課的學(xué)生姓名、學(xué)號SELECT sno,sname FROM STUDENTWHERE NOT EXISTS（SELECT FROM ENROLL WHERE STUDENT。sno=ENROLL.sno)；9.檢索沒有選修課程“Calculus Iv”的學(xué)生學(xué)號SELECT snoFROM STUDENTWHERE sno NOT IN (SELECT STUDENT。snoFROM COURSE，STUDENT,ENROLLWHERE cname=Calculus Iv AND COURSE.cno=ENROLL.cno AND STUDENT.sno=ENROLL.sno);10。檢索至少選修教師“Dr。 Lowe"所開全部課程的學(xué)生學(xué)號SELECT DINSTINCT snoFROM ENROLL ENROLLXWHERE NOT EXISTS(SELECT FROM ENROLL ENROLLYWHERE cno in( SELECT cno FROM TEACHER，SECTION, WHERE tname=Dr。Lowe' AND TEACHER.tno=SECTION.tno） AND NOT EXISTS （SELECT *FROM ENROLL ENROLLZ WHERE ENROLLZ.sno=ENROLLX。sno AND ENROLLZ。cno=ENROLLY。cno）)（修改數(shù)據(jù)驗證，該老師教授課程號為730和500，只有學(xué)號148的學(xué)生同時選擇了這兩門課）SECTIONENROLL結(jié)果11。檢索每門課學(xué)生登記的人數(shù)、相應(yīng)的課程名、課程號、分組號SELECT DISTINCT secno,ENROLL。cno,cname，sqtyFROM ENROLL,COURSE,SECTIONWHERE SECTION。cno=ENROLL。cno AND COURSE.cno=ENROLL.cno12。檢索選修兩門以上課程的學(xué)生姓名SELECT snameFROM STUDENTWHERE sno in (SELECT sno FROM ENROLL GROUP BY sno HAVING COUNT(*）>2)13.檢索只有男生選修的課程和學(xué)生名SELECT cname，snameFROM COURSE ，STUDENT ，ENROLL WHERE STUDENT.sno=ENROLL.sno AND COURSE。cno=ENROLL。cno AND COURSE。cno in （SELECT cno FROM COURSEWHERE cno not in （SELECT DISTINCT cnoFROM STUDENT ，ENROLL WHERE sex ='F' and STUDENT.sno=ENROLL.sno）14。檢索所有學(xué)生都選修的課程名、學(xué)生名、授課教師名、該生成績SELECT cname,sname,tname，gradeFROM STUDENT ，TEACHER ，SECTION ,ENROLL ,COURSE WHERE COURSE。cno=ENROLL.cno AND STUDENT。sno=ENROLL.sno AND TEACHER.tno=SECTION。tno AND SECTION.cno=ENROLL。cnoAND SECTION。secno=ENROLL.seconAND COURSE.cno=(SELECT cno FROM ENROLL GROUP BY cno HAVING COUNT (*）=12)15.刪去名為“Joe Adams”的所有記錄DELETE FROM STUDENTWHERE sname=Joe Adams'SELECT FROM STUDENT16。把教師“Scango”的編號改為“666”UPGRADE TEACHERSET tno=666WHERE tname='Dr.Scango SELECT FROM TEACHER17。統(tǒng)計教師“Engle”教的英語課的學(xué)生平均分SELECT AVG(grade)AVGFROM ENROLLWHERE grade in(SELECT gradeFROM TEACHER,SECTION，ENROLLWHERE tname='Dr.Engle' AND TEACHER.tno=SECTION.tno AND SECTION。cno=ENROLL.cnoAND ENROLL.secno=SECTION.secno）18.統(tǒng)計各門課程的選課人數(shù)SELECT COURSE.cname,COUNT（ENROLL。sno)FROM ENROLL，COURSEWHERE ENROLL.cno=COURSE.cno GROUP BY COURSE。cname；19。輸出如下報表：學(xué)生名課程名教師名成績SELECT sname 學(xué)生名,cname 課程名，tname 教師名，grade 成績FROM ENROLL,STUDENT，COURSE,TEACHER，SECTIONWHERE ENROLL。sno=STUDENT.sno AND SECTION。cno = COURSE。cno AND ENROLL.secno = SECTION.secno AND ENROLL。cno = SECTION.cno AND SECTION。tno = TEACHER。tno20.定義并驗證觸發(fā)器，當(dāng)?shù)怯洷碓黾右粭l新的記錄時，自動在分組表中更新相應(yīng)屬性.CREATE TRIGGER ENROLL_1ON ENROLLAFTER INSERTASUPDATE SECTIONSET sqty=sqty+1FROM SECTION,insertedWHERE inserted。secno=SECTION。secnoAND o=SECTION。cno驗證結(jié)果:INSERTINTO ENROLLVALUES（'450，'2，'354','1'）SELECT FROM SECTION原始數(shù)據(jù)執(zhí)行結(jié)果四、課程設(shè)計小結(jié)在本次課設(shè)之前，我已經(jīng)在前段學(xué)習(xí)期間上機編寫過類似相關(guān)的SQL語句來完成對于學(xué)生課程-選課的信息查詢，這次在原先實驗的基礎(chǔ)上添加了分組和教師表，創(chuàng)建數(shù)據(jù)庫、基本表和錄入數(shù)據(jù)的過程與上機實驗基本相同，因此很容易就完成了前三個步驟,在這個過程中對于數(shù)據(jù)庫、表的創(chuàng)建以及數(shù)據(jù)的輸入的操作都更加的熟練.題目4-19都是對于表中數(shù)據(jù)進行查詢、修改、刪除等操作，并且運用了ORDER BY、GROUP BY等功能短語以及COUNT、AVG等聚集函數(shù)。前兩題都是基本的查詢，比較簡單,但由于輸入數(shù)據(jù)時Dr。Engle中的.多按了一個空格,結(jié)果在調(diào)試的時候出現(xiàn)的是錯誤的結(jié)果，結(jié)果不正確（見截圖。第10題是本次課設(shè)最難的題目,自己也嘗試著去做，但是由于理解的失誤以及數(shù)據(jù)的巧合性，錯誤的SQL語句得出的結(jié)果卻是正確的。這就導(dǎo)致了自己以為解決了這道難題，以至于在給老師檢查的時候被指出嚴(yán)重的錯誤，感到很羞愧。后來仔細分析題意，按照所有的關(guān)鍵字來編寫查詢語句，參照書上關(guān)于not exists語句以及嵌套循環(huán)的語句，用謂詞演算將題目轉(zhuǎn)換成邏輯運算,等價為“沒有這樣課程y,老師Dr。Lowe教授了這門課，而學(xué)生X沒有選”.p表示“老師教授了課程y”,q表示“學(xué)生X選了課y”,等價于y(pq).參照書上的例題，分層次地編寫功能語句。因為題目的巧合性所以在檢驗時修改了數(shù)據(jù)，添加了該老師新開另一門課，只有學(xué)號148的學(xué)生同時選擇了這門老師的兩門課。然后再去驗證結(jié)果發(fā)現(xiàn)是正確的。第13題的題目剛開始理解的不透徹，所以直接查詢了男生選修的課程,得到的結(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)系，所以無法對于這兩個表進行修改。因此需要先刪除外碼的引用關(guān)系，然后才能對其進行修改和刪除。第20題定義觸發(fā)器這個比較陌生，在學(xué)習(xí)的時候就不是很掌握,因此對于這個題目第一印象就是不想去做,我不會。但是后來通過翻閱書本并且詢問同學(xué)，并且自己不斷的摸索，雖然起初一直報錯，但是我并沒有氣餒，在各種幫助和努力下終于實現(xiàn)了最后一個功能。不過在給老師檢查的過程中，發(fā)現(xiàn)UPDATE語句后面自己添加了FROM的語句，雖然對于整個執(zhí)行結(jié)果沒有太大的影響，但這是一個常識性的錯誤，不應(yīng)該出現(xiàn)如此低級的錯誤，因為當(dāng)時是想新表需要和SECTION、ENROLL表進行連接，所以在寫的過程中直接添加了inserted。cno=SECTION。cno AND inserted。secno=SECTION。secno的錯誤語句，而且還誤將其中的SECTION寫成了ENROLL表，以至于在老師詢問的時候，自己也被自己的代碼弄得不知所措。后來修改的時候索性把所有的條件語句都刪除了,并沒有進行臨時新表inserted與SECTION表的連接,以至于結(jié)果每一行的學(xué)生登記數(shù)量sqty都加一，得到錯誤的結(jié)果。 過程比較艱辛但是最后完成的時候內(nèi)心的喜悅確實滿滿洋溢的。果然自己去努力實現(xiàn)的事情可以給自己帶來無限的快樂，自己從中收獲的經(jīng)驗和知識也是更多的。不要因為自己對于某方面不熟悉就直接放棄,而應(yīng)該努力去嘗試一下.只有試過了，才知道自己到底可不可以完成這個看似困難的問題！附表2課程（COURSE)課程號（cno）課程名（cname）系名（department）學(xué)分（credit)450Western CivilizationHistory3730Calculus IvMath4290English CompositionEnglish3480Compiler WritingComputer Science3學(xué)生（STUDENT）學(xué)號（sno)姓名(sname）地址（address）郵政編碼(zip）市（city)省(state)性別(sex)148Susan powell534 East River Dr19041HaverfordPAF210Bob Dawson120 South Jefferson02891NewportRIM298Howard Mansfield290 Wynkoop Drive22180ViennaVAM348Susan Pugh534 East Hampton Dr06107HartfordCNF349Joe Adams473 Emmerson Street19702NewarkDEM354Janet Ladd441 10th Street18073PennsburgPAF410Bill Jone120 South Harrison92660NewportCAM473Carol Dean983 Park Avenue02169BostonMAF548Allen thomas238 West Ox Road60624ChicagoILM558Val Shipp238 Westport Road60556ChicagoILF649John Anderson473 Emmory Street10008New YorkNYM654Janet Yhomas441 6th Street16510EriePAF教師（TEACHER）教師編號(tno)姓名（tname)電話號碼(phone）工資(salary）303Dr. Horn257-304927540。00290Dr。 Lowe257-239031450.00430Dr 。Engle256462138200.00180Dr. Cooke257-808829560。00560Dr. Olsen257-808631778.00784Dr。 Scango257-304632098。00分組（SECTION)組號（secno）教師編號(tno）課程號（cno）學(xué)生數(shù)量(sqty）130345021290 730614302903118048032560450227844802登記(ENROLL)課程號(cno)組號（secno）學(xué)生學(xué)號（sno）評分(grade）730114834502210373012101290129834802298373013482290134944801348448014102450147327301473348024730290154827301558373016494480164944501654445025481第14頁