1、<p>　　課 程 設(shè) 計 報 告</p><p>　　課 程 名 稱 數(shù)據(jù)庫系統(tǒng)概論 </p><p>　　設(shè) 計 題 目 學(xué)生管理系統(tǒng) </p><p>　　專 業(yè) 班 級 </p><p>　　姓 名

2、 </p><p>　　2011年 6月 28 日</p><p><b>　　目 錄</b></p><p>　　系統(tǒng)概念結(jié)構(gòu)設(shè)計---------------------------------------------------2</p><p>　　1、需求分析 ------------

3、-------------------------------------------------------------- 2</p><p>　　2、設(shè)計目的 ----------------------------------------------------------------------------2</p><p>　　3、用E-R圖表示學(xué)生管理系統(tǒng)的概念模型------

4、---------------------------------2</p><p>　?、賹嶓w及屬性描述----------------------------------------------------------------2</p><p>　?、趯?yīng)實體及其屬性圖---------------------------------------------------------

5、-3</p><p>　?、蹖嶓w之間的聯(lián)系描述----------------------------------------------------------4</p><p>　?、軐嶓w之間的E-R圖-------------------------------------------------------------4</p><p>　　邏輯結(jié)構(gòu)設(shè)計

6、 -----------------------------------------------------5</p><p>　　物理結(jié)構(gòu)設(shè)計 -----------------------------------------------------6</p><p>　　1、利用SQL語言創(chuàng)建各個表------------------------------------------

7、-----------6</p><p>　　2、創(chuàng)建視圖---------------------------------------------------------------------------10</p><p>　　3、定義索引-------------------------------------------------------------------------

8、11</p><p>　　4、數(shù)據(jù)查詢設(shè)計-------------------------------------------------------------------11</p><p>　　5、數(shù)據(jù)的插入-----------------------------------------------------------------------16</p><

9、;p>　　6、數(shù)據(jù)修改--------------------------------------------------------------------------17</p><p>　　7、數(shù)據(jù)刪除--------------------------------------------------------------------------17</p><p>　　安全

10、性設(shè)計 -----------------------------------------------------17</p><p>　　五、 小組分工 -----------------------------------------------------------18</p><p><b>　　系統(tǒng)概念結(jié)構(gòu)設(shè)計</b></p><

11、;p><b>　　需求分析</b></p><p>　　使用計算機(jī)對學(xué)生信息進(jìn)行管理，擁有手工管理所無法比擬的優(yōu)點(diǎn)。例如：檢索迅速、查找方便、可靠性高、存儲量大、成本低等。這些優(yōu)點(diǎn)能夠極大地提高學(xué)生信息的效率，也是管理科學(xué)化、正規(guī)化的重要支撐。</p><p>　　學(xué)生信息管理系統(tǒng)能夠方便地查詢和變更學(xué)生的基本數(shù)據(jù)（包括學(xué)籍?dāng)?shù)據(jù)和成績數(shù)據(jù)），節(jié)省大量工作時間，有

12、效地提高學(xué)生信息管理的效率。而查詢信息的直觀顯示將有助于系統(tǒng)的用戶一目了然地掌握學(xué)生的情況。</p><p>　　通過需求分析得到的數(shù)據(jù)項與數(shù)據(jù)結(jié)構(gòu)，可以設(shè)計出各種實體以及它們之間的關(guān)系圖，為后面的邏輯設(shè)計打下基礎(chǔ)。這些實體包含各種具體信息，通過相互之間的作用形成數(shù)據(jù)的流動。</p><p><b>　　設(shè)計目的</b></p><p>　?、?/p>

13、 熟悉數(shù)據(jù)庫管理系統(tǒng)的結(jié)構(gòu)與組成</p><p>　?、?熟悉數(shù)據(jù)庫應(yīng)用系統(tǒng)的設(shè)計方法和開發(fā)過程</p><p>　?、?熟悉通過SQL對數(shù)據(jù)庫進(jìn)行操作。</p><p>　　用E-R圖表示學(xué)生管理系統(tǒng)的概念模型</p><p><b>　　實體及屬性描述</b></p><p>　　學(xué)生（學(xué)號，

14、姓名，性別，出生年月，專業(yè)號，班級號，聯(lián)系方式，入學(xué)日期） </p><p>　　教師 （教職工號，姓名，性別，聯(lián)系方式，所屬專業(yè)號）</p><p>　　課程 （課程號， 課程名， 學(xué)分）</p><p>　?、?系 （系號，系名）</p><p>　?、?班級 （班級號，所在系號）</p><p>&

15、lt;b>　　對應(yīng)實體及其屬性圖</b></p><p><b>　　實體之間的聯(lián)系描述</b></p><p>　　根據(jù)各個實體之間的聯(lián)系可以得到如下關(guān)系：</p><p>　　一個系有多個班級，一個班級只屬于一個系，因此班級與系之間是多對一關(guān)系即n:1</p><p>　　一個班級有多個學(xué)生，一個學(xué)

16、生只屬于一個班級，因此班級與學(xué)生之間是一對多關(guān)系即1:n</p><p>　　一個學(xué)生可以有多個老師教，一個老師可以教多個學(xué)生，因此學(xué)生和老師之間是多對多的關(guān)系即m：n</p><p>　　一個學(xué)生可以選多門課，一門課可以有多個學(xué)生選，因此學(xué)生與課程之間是多對多的關(guān)系即m：n</p><p>　　一個教師可以教授多門課程，一門課程可以由多個老師教授，因此教師和課程之

17、間的關(guān)系是多對多的關(guān)系，即m：n</p><p>　　實體之間的E-R圖表示如下（其中屬性只給出主碼）：</p><p><b>　　邏輯結(jié)構(gòu)設(shè)計</b></p><p>　　本階段的任務(wù)是把概念結(jié)構(gòu)轉(zhuǎn)換成SQL Server，數(shù)據(jù)庫管理系統(tǒng)能處理的數(shù)據(jù)模型。在進(jìn)行轉(zhuǎn)換時，是把實體類型和聯(lián)系類型分別轉(zhuǎn)換成一個個關(guān)系模式，這中間存在著多種可能組合

18、，必須從中選擇一個性能好的關(guān)系模式集作為關(guān)系數(shù)據(jù)庫的模式。</p><p>　　學(xué)生管理系統(tǒng)的關(guān)系模式如下：</p><p>　?、?學(xué)生信息表（學(xué)生學(xué)號，姓名，性別，出生年月，專業(yè)號，所在班級號，聯(lián)系方式，入學(xué)日期） </p><p>　　主碼：學(xué)生學(xué)號 外碼：專業(yè)號，班級號</p><p>　?、?任課教師數(shù)據(jù)表 （教職工號，姓

19、名，性別，聯(lián)系方式，所屬專業(yè)號）</p><p>　　主碼： 教職工號 外碼：所屬專業(yè)號</p><p>　?、?課程數(shù)據(jù)表 （課程號， 課程名， 學(xué)分）</p><p><b>　　主碼： 課程號</b></p><p>　?、?系數(shù)據(jù)表 （系號，系名）</p><p><b>

20、;　　主碼： 系號</b></p><p>　　⑤ 學(xué)生成績表 （學(xué)號，課程號，成績）</p><p><b>　　主碼：學(xué)號，課程號</b></p><p>　　⑥ 教師任課表 （課程號，教師工號）</p><p>　　主碼：課程號，教師工號</p><p>　?、?班級數(shù)據(jù)表

21、 （班級號，所在系號）</p><p>　　主碼：班級號 外碼：所在系號</p><p><b>　　三、物理結(jié)構(gòu)設(shè)計</b></p><p>　　物理結(jié)構(gòu)設(shè)計是對給定的基本數(shù)據(jù)模型選擇一個最適合應(yīng)用環(huán)境的物理結(jié)構(gòu)的過程。數(shù)據(jù)庫的物理結(jié)構(gòu)主要是指數(shù)據(jù)庫的存儲記錄格式、存儲記錄安排和存取方法。</p><p>　　1、利

22、用SQL語言創(chuàng)建各個表</p><p>　?。?）各表及屬性如下：</p><p>　　學(xué)生表：Student（Sno , Sna , Ssex , Sbir , Dno , CLno , Stel , Senter）</p><p>　　任課教師表：Teacher (Tno , Tna , Tsex , Ttel , Dno)</p><p&g

23、t;　　課程數(shù)據(jù)表：Course（Cno , Cna , Ccredit）</p><p>　　系數(shù)據(jù)表：Dept（Dno , Dna）</p><p>　　學(xué)生成績表：SC(Sno , Cno , Cgrade)</p><p>　　教師任課表：TC（Cno , Tno）</p><p>　　班級數(shù)據(jù)表：Class( CLno , Dno)

24、</p><p>　?。?）數(shù)據(jù)結(jié)構(gòu)：設(shè)置每一張數(shù)據(jù)表的屬性和屬性名，類型和寬度</p><p>　　班級數(shù)據(jù)表Class</p><p>　　學(xué)生表Student</p><p>　　任課教師數(shù)據(jù)表Teacher</p><p>　　課程數(shù)據(jù)表Course</p><p><b>　

25、　系數(shù)據(jù)表Dept</b></p><p><b>　　學(xué)生成績表SC</b></p><p><b>　　教師任課表TC</b></p><p>　?。?）利用SQL語言創(chuàng)建基本表</p><p>　　系統(tǒng)執(zhí)行CREATE TABLE語句后，就在數(shù)據(jù)庫中建立一個新的空的學(xué)生表，并將有

26、關(guān)表及有關(guān)約束條件存放在數(shù)據(jù)字典中。</p><p><b>　　學(xué)生表：</b></p><p>　　CREATE TABLE Student</p><p>　?。⊿no Char (9) PRIMARY KEY，</p><p>　　Sna Char (20) UNIQUE，</p><p>

27、;　　Ssex Char (2)，</p><p>　　Sbir Char (10)，</p><p>　　Dno Char (3)，</p><p>　　CLno Char (3)，</p><p>　　Stel Char (11)，</p><p>　　Senter Char (8)，</p><

28、;p>　　FOREIGN KEY Dno REFERENCES Dept(Dno)</p><p>　　FOREIGN KEY CLno REFERENCES Class(CLno)</p><p><b>　　)；</b></p><p><b>　　教師表：</b></p><p>　

29、　CREATE TABLE Teacher</p><p>　?。═no Char (9) PRIMARY KEY，</p><p>　　Tna Char (20) UNIQUE，</p><p>　　Tsex Char (2)，</p><p>　　Ttel Char (11)，</p><p>　　Dno Char

30、 (3)，</p><p>　　FOREIGN KEY Dno REFERENCES Dept(Dno)</p><p><b>　　)；</b></p><p><b>　　課程表：</b></p><p>　　CREATE TABLE Course</p><p>　　

31、（Cno Char (9) PRIMARY KEY，</p><p>　　Cna Char (20) UNIQUE，</p><p>　　Ccredit Smallint</p><p><b>　?。?</b></p><p><b>　　系數(shù)據(jù)表：</b></p><p>

32、;　　CREATE TABLE Dept</p><p>　?。―no Char (9) PRIMARY KEY，</p><p>　　Dna Char (20) UNIQUE</p><p><b>　　）；</b></p><p><b>　　選課表：</b></p><p&

33、gt;　　CREATE TABLE SC</p><p>　?。⊿no Char (9)，</p><p>　　Cno Char (9)，</p><p>　　Cgrade Smallint，</p><p>　　PRIMARY KEY Sno AND Cno</p><p><b>　?。?；</b&g

34、t;</p><p><b>　　教師任課表：</b></p><p>　　CREATE TABLE TC</p><p>　　( Cno Char (9)，</p><p>　　Tno Char (9)，</p><p>　　PRIMARY KEY Tno AND Cno</p>

35、<p><b>　?。?；</b></p><p><b>　　班級數(shù)據(jù)表：</b></p><p>　　CREATE TABLE CLno</p><p>　　( CLno Char (9) PRIMARY KEY，</p><p>　　Dno Char (9)，</p>&

36、lt;p>　　FOREIGN KEY Dno REFERENCES Dept(Dno)</p><p><b>　　)；</b></p><p><b>　　2、創(chuàng)建視圖</b></p><p>　　視圖是從一個到幾個基本表導(dǎo)出的表，它與基本表不同，是一個虛表。數(shù)據(jù)庫只存放視圖的定義，而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)

37、據(jù)仍讓存放在與原來的基本表中。所以基本表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也就隨之改變了，從這個意義上講，試圖就像是一個窗口，透過它可以看到數(shù)據(jù)庫中自己感興趣的數(shù)據(jù)及其變化</p><p>　　創(chuàng)建信息系學(xué)生的視圖</p><p>　　CREATE VIEW IS_Student</p><p><b>　　AS</b></p>

38、<p>　　SELECT Sno,Sname,Sage</p><p>　　FROM Student</p><p>　　WHERE Sdept=’IS’</p><p>　　WITH CHECK OPTION;</p><p>　　建立信息系選修了1號課程的學(xué)生的視圖</p><p>　　CREATE

39、VIEW IS_S1(Sno,Sname.Grade)</p><p><b>　　AS</b></p><p>　　SELECT Student.Sno,Sname,Grade</p><p>　　FROM Student,SC</p><p>　　WHERE Sdept =’IS’ AND</p>&l

40、t;p>　　Student.Sno=SC.Sno AND</p><p>　　SC.Cno=’1’; </p><p><b>　　3、定義索引：</b></p><p>　　為基本表中的Student，Course，SC3各表建立索引。其中Student表按學(xué)號升序建立唯一索引，Course表按課程號升序建唯一索引，SC表按學(xué)號升序和課

41、程號降序建唯一索引。</p><p>　　CREATE UNIQUE INDEX Stusno ON Student（Sno）；</p><p>　　CREATE UNIQUE INDEX CouCno ON Course（Cno）；</p><p>　　CREATE UNIQUE INDEX SCno ON SC（Sno ASC，Cno DESC）；</p

42、><p>　　如果需要刪除Stusno索引則DROP INDEX Stusno。</p><p><b>　　4、數(shù)據(jù)查詢設(shè)計</b></p><p><b>　?。?）確定集合查詢</b></p><p>　　如：從上述學(xué)生表中查詢計算機(jī)科學(xué)系（CS）、數(shù)學(xué)系（MA）和信息系（IS）學(xué)生的姓名和性別。

43、</p><p>　　SELECT Sna，Ssex</p><p>　　FROM Student</p><p>　　WHERE Sdept IN （‘CS’，‘ MA’，‘IS’）；</p><p>　　查詢既不是計算機(jī)科學(xué)系，數(shù)學(xué)系也不是信息系的學(xué)生的姓名和性別</p><p>　　SELECT Sname,Ss

44、ex</p><p>　　FROM Student</p><p>　　WHERE Sdept NOT IN(‘CS’,’MA’,’IS’);</p><p><b>　　（2）字符匹配查詢</b></p><p>　　查詢學(xué)號為200215121的學(xué)生的詳細(xì)情況</p><p><b>

45、;　　SELECT *</b></p><p>　　FROM Student</p><p>　　WHERE Sno LIKE ‘200215121’;</p><p>　　查詢所有姓劉的學(xué)生的姓名，性別和學(xué)號</p><p>　　SELECT Sname,Sno,Ssex</p><p>　　

46、FROM Student</p><p>　　WHERE Sname LIKE ‘劉%’；</p><p>　　查詢姓“歐陽”且全名為3個漢字的學(xué)生的姓名</p><p>　　SELECT Sname</p><p>　　FROM Student</p><p>　　WHERE Sname LIKE ’歐陽_’;&l

47、t;/p><p>　　查詢名字中第二個字為‘陽’的學(xué)生的姓名和學(xué)號</p><p>　　SELECT Sname,Sno</p><p>　　FROM Student</p><p>　　WHERE Sname LIKE ‘__陽’；</p><p>　　查詢所有不姓劉的學(xué)生姓名</p><p>　

48、　SELECT Sname</p><p>　　FROM Student</p><p>　　WHERE Sname NOT LIKE ‘劉%’；</p><p>　　查詢DB_Design課程的課程號和學(xué)分</p><p>　　SELECT Cno ,Ccrediet</p><p>　　FROM Course&l

49、t;/p><p>　　WHERE Cname LIKE ‘DB\_%i__’ESCAPE’\’;</p><p>　　查詢以“DB_”開頭，且倒數(shù)第三個字符為i的課程的詳細(xì)信息情況</p><p><b>　　SELECT *</b></p><p>　　FROM Course</p><p>　　W

50、HERE Cname LIKE ‘DB\_%i__’ESCAPE’\’;</p><p>　　(3) 聚集函數(shù)查詢</p><p><b>　　查詢學(xué)生的總?cè)藬?shù)</b></p><p>　　SELECT COUNT(*)</p><p>　　FROM Student;</p><p>　　查詢選修

51、課課程的學(xué)生人數(shù)</p><p>　　SELECT COUNT(DISTINCT Sno)</p><p><b>　　FROM SC;</b></p><p>　　計算1號課程的學(xué)生平均成績</p><p>　　SELECT AVG(Grade)</p><p><b>　　FROM

52、SC</b></p><p>　　WHERE Cno=’1’;</p><p>　　查詢選修1號課程的學(xué)生的最高分</p><p>　　SELECT MAX(Grade)</p><p><b>　　FROM SC</b></p><p>　　WHERE Cno=’1’;</p&

53、gt;<p>　　查詢學(xué)生200215012選修課的總學(xué)分?jǐn)?shù)</p><p>　　SELECT SUM (Ccrediet)</p><p>　　FROM SC,Course</p><p>　　WHERE Sno=’200215012’ AND SC.Cno = Course.Cno;</p><p><b>　　(

54、4) 分組查詢</b></p><p>　　求各個課程號及相應(yīng)的選課人數(shù)</p><p>　　SELECT Cno,COUNT(Sno)</p><p><b>　　FROM SC</b></p><p>　　GROUP BY Cno；</p><p>　　查詢選修課3門課以上課程的學(xué)

55、生學(xué)號</p><p>　　SELECT Sno</p><p><b>　　FROM SC</b></p><p>　　GROUP BY Sno</p><p>　　HAVING COUNT(*) > 3;</p><p><b>　　(5) 連接查詢</b><

56、/p><p>　　查詢每個學(xué)生及其選修課程的情況</p><p>　　SELECT Student.*,SC.*</p><p>　　FROM Student,SC</p><p>　　WHERE Student.Sno=SC.Sno;</p><p><b>　　若改為自然連接</b></p&

57、gt;<p>　　SELECT Student,Sno,Sname,Ssex,Sage,Sdept,Cno,Grade</p><p>　　FROM Student ,SC</p><p>　　WHERE Student .Sno = SC.Sno;</p><p><b>　　若用外連接</b></p><

58、p>　　SELECT Student, Sno,Sname,Ssex,Sage,Sdept,Cno,Grade</p><p>　　FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);</p><p>　　查詢選修2號課程且成績在90分以上的所有學(xué)生</p><p>　　SELECT Student,Sn

59、o,Sname</p><p>　　FROM Student,SC</p><p>　　WHERE Student.Sno=SC.Sno AND</p><p>　　SC.Cno=’2’ AND SC.Grade>90</p><p>　　(6) 帶有ANY或者ALL謂詞查詢</p><p>　　查詢其他系中比計

60、算機(jī)科學(xué)系某一學(xué)生年齡小的學(xué)生姓名和年齡</p><p>　　SELECT Sname,Sage</p><p>　　FROM Student</p><p>　　WHERE Sage<ANY(SELECT Sage</p><p>　　FROM Student</p><p>　　WHERE Sdept = ‘

61、CS’)</p><p>　　AND Sdept<>’CS’;</p><p>　　查詢其他系中比計算機(jī)科學(xué)系所有學(xué)生年齡小的學(xué)生姓名和年齡</p><p>　　SELECT Sname,Sage</p><p>　　FROM Student</p><p>　　WHERE Sage<ALL(SELE

62、CT Sage</p><p>　　FROM Student</p><p>　　WHERE Sdept = ‘CS’)</p><p>　　AND Sdept<>’CS’;</p><p>　　(7) 帶有EXISTS或NOT EXISISTS謂詞查詢</p><p>　　查詢所有選修了一號課的學(xué)生姓名&

63、lt;/p><p>　　SELECT Sname</p><p>　　FROM Student</p><p>　　WHERE EXISTS</p><p><b>　　(SELECT *</b></p><p><b>　　FROM SC</b></p><

64、p>　　WHERE Sno=Student.Sno AND Cno=’1’);</p><p>　　查詢沒有選修了一號課的學(xué)生姓名</p><p>　　SELECT Sname</p><p>　　FROM Student</p><p>　　WHERE NOT EXISTS</p><p><b>

65、　　(SELECT *</b></p><p><b>　　FROM SC</b></p><p>　　WHERE Sno=Student.Sno AND Cno=’1’);</p><p><b>　　5、數(shù)據(jù)的插入</b></p><p>　　SQL的數(shù)據(jù)插入語句INSERT通常有兩

66、種形式。一種是插入一個元組另一種是插入子查詢結(jié)構(gòu)。</p><p><b>　　插入元組</b></p><p>　　將一個新學(xué)生元組（200901050424，王青，女，1990-2-2，06（專業(yè)號），02（班級號），150*****，09-9-5）插入Student表中。</p><p><b>　　INSERT</b&g

67、t;</p><p>　　INTO Student（Sno，Sna，Ssex，Sbir，Dno，CLno，Stel，Serter）</p><p>　　VALUES（‘200901050424’，‘王青’， ‘王青’， ‘女’， ‘1990-2-2’， ‘06’，‘02’， ‘150*****’，‘ 09-9-5’）；</p><p><b>　　插入子查

68、詢結(jié)果</b></p><p><b>　　INSERT</b></p><p>　　INTO <表名>[<屬性列>]</p><p><b>　　子查詢；</b></p><p><b>　　6、數(shù)據(jù)修改</b></p>&l

69、t;p>　　其功能是修改制定表中滿足WHERE子句條件的元組。其中set語句給出表達(dá)式的值用于取代相應(yīng)的屬性列值。如果省略WHERE子句，則表示要修改表中的所有元組。</p><p>　　將學(xué)生200901050423的年齡改為22歲，如下：</p><p>　　UPDATE Student</p><p>　　SET Sage=22</p>&

70、lt;p>　　WHERE Sno=‘200901050423’；</p><p><b>　　7、數(shù)據(jù)的刪除：</b></p><p>　　DELETE語句的功能是從指定表中刪除滿足WHERE子句條件的所有元組，但表的定義仍在字典中。也就是所，DELETE語句刪除的是表中的數(shù)據(jù)，而不是關(guān)于表的定義。</p><p>　　刪除學(xué)號為2009

71、01050423的記錄，如下：</p><p><b>　　DELETE </b></p><p>　　FROM Student</p><p>　　WHERE Sno=‘200901050423’；</p><p><b>　　四、安全性設(shè)計</b></p><p>　　數(shù)

72、據(jù)庫的安全性是指保護(hù)數(shù)據(jù)庫以防止不合法的適用所造成的數(shù)據(jù)泄露、更改或破壞。</p><p>　　安全性問題不是數(shù)據(jù)庫系統(tǒng)所獨(dú)有的，所有計算機(jī)系統(tǒng)都有這個問題。只是在數(shù)據(jù)庫系統(tǒng)中大量數(shù)據(jù)集中存放，而且為許多最終用戶直接共享，從而使安全性問題更為突出。系統(tǒng)安全保護(hù)措施是否有效時數(shù)據(jù)庫系統(tǒng)的重要技術(shù)指標(biāo)之一。</p><p>　　數(shù)據(jù)庫的安全性和計算機(jī)系統(tǒng)的安全性，包括計算機(jī)硬件，操作系統(tǒng)，網(wǎng)絡(luò)

73、系統(tǒng)等的安全性，是緊密聯(lián)系，相互支持的，一次在討論數(shù)據(jù)庫的安全性之前實現(xiàn)討論計算機(jī)系統(tǒng)安全性的一般問題。</p><p>　　例如用授權(quán)的方式來區(qū)分用戶的等級，從而保護(hù)數(shù)據(jù)庫系統(tǒng)的安全性</p><p>　　例如把查詢表Student的權(quán)限授給用戶U1</p><p>　　GRANT SELECT</p><p>　　ON TABLE Stu

74、dent</p><p><b>　　TO U1；</b></p><p>　　把查詢Student表和修改學(xué)生學(xué)號的權(quán)限授給用戶U2，并允許將此權(quán)限在授予其他用戶</p><p>　　GRANT UPDATE（Sno），SELECT</p><p>　　ON TABLE Student</p><p