學(xué)生選課數(shù)據(jù)庫設(shè)計

1、北京理工大學(xué)珠海學(xué)院課程設(shè)計說明書2011 2012 學(xué)年第 1 學(xué)期題目: 學(xué)生選課數(shù)據(jù)庫設(shè)計 學(xué) 院： 商學(xué)院 專業(yè)班級： 學(xué)號姓名： 指導(dǎo)教師： 成 績： 時 間： 2011年 12 月 5 日北京理工大學(xué)珠海學(xué)院課程設(shè)計任務(wù)書 2011 2012 學(xué)年第 1 學(xué)期學(xué)生姓名： 何景豪 專業(yè)班級： 09信管一班 指導(dǎo)教師： 李玉敏 工作部門： 商學(xué)院 一、課程設(shè)計題目學(xué)生選課數(shù)據(jù)庫設(shè)計二、課程設(shè)計內(nèi)容、選課系統(tǒng)的設(shè)計(1) 在選課數(shù)據(jù)庫中，統(tǒng)計出選修課程超過4門以上的學(xué)生信息。(2) 檢索出成績及格的、姓王的女同學(xué)信息。(3) 統(tǒng)計出平均分在80分以上的學(xué)生的信息，并將結(jié)果按平均分升序排列

2、。(4)統(tǒng)計出至少有三名學(xué)生選修的課程的平均分，統(tǒng)計項包含課程編號和平均分。(5)為每個實體表和關(guān)系表創(chuàng)建合適的索引（聚簇索引、復(fù)合索引）、主碼、外碼；(6)創(chuàng)建課程類別取值范圍為管理類、電子類、計算機類、機械類的規(guī)則，并與相應(yīng)的列綁定；(7)在選課聯(lián)系中創(chuàng)建成績默認值為60的默認，并與相應(yīng)的列綁定；(8)創(chuàng)建一個可以通過課程類別和任課教師進行查詢課程的存儲過程；(9)通過觸發(fā)器定義未選課的學(xué)生不能從學(xué)生實體中刪除；(10)建立包括選課學(xué)生的學(xué)號，姓名，系別，所選課程號，課程名，和成績的視圖；三、進度安排1需求分析（4學(xué)時）2概念模型（4學(xué)時）、邏輯模型及關(guān)系數(shù)據(jù)表（4學(xué)時）2建庫及建表（4學(xué)

3、時）、索引（4學(xué)時）、視圖（4學(xué)時）、觸發(fā)器（4學(xué)時）、存儲過程（4學(xué)時）、事務(wù)（4學(xué)時）、輸入初始數(shù)據(jù)（4學(xué)時）， 共40%3統(tǒng)計報表（兩個及以上綜合數(shù)據(jù)統(tǒng)計報表）（4學(xué)時）4答辯（4學(xué)時）5文檔整理（4學(xué)時）四、基本要求（1）概念模型設(shè)計合理、可擴展性好；（2）數(shù)據(jù)合理、符合業(yè)務(wù)場景；（3）數(shù)據(jù)庫對象設(shè)計合理、使用得當；（4）數(shù)據(jù)合理、有效、完整；（5）提交紙制版課程設(shè)計報告，應(yīng)不少于3000字，每人提交1份課程設(shè)計報告（Word文檔和Sql文檔）。 課程負責(zé)人簽名： 年 月 日學(xué)生選課數(shù)據(jù)庫課程設(shè)計摘 要1. 目的和意義在當今信息化越來越快的發(fā)展的環(huán)境下，各學(xué)校無論中學(xué)大學(xué)都爭相采用網(wǎng)上

4、選課方式。相比而言，傳統(tǒng)選課方式由于不方便、浪費資源等原因而落后了。學(xué)生選課系統(tǒng)作為現(xiàn)時代的教學(xué)技術(shù)，廣受學(xué)生歡迎。學(xué)生選課系統(tǒng)由于其科學(xué)性、簡便性、效率高、公平性和準確性從而提高學(xué)生選課效率。本課程設(shè)計目的是探索學(xué)生選課的需求分析、概要設(shè)計、邏輯設(shè)計及規(guī)范化、數(shù)據(jù)庫對象設(shè)計、數(shù)據(jù)統(tǒng)計這幾個方面的內(nèi)容。為了更好地在以后的工作中能完成數(shù)據(jù)庫建立和使用等操作。使用SQL Server數(shù)據(jù)庫來設(shè)計數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計，包括數(shù)據(jù)庫，數(shù)據(jù)表，索引、視圖、觸發(fā)器、存儲過程、事務(wù)等，以達到數(shù)據(jù)庫的操作和使用者的要求。關(guān)鍵詞：學(xué)生信息查詢、選課信息、學(xué)生選課信息查詢、數(shù)據(jù)庫、數(shù)據(jù)表 目 錄第一章設(shè)計內(nèi)容與目標2

5、第二章需求分析32.1 需求背景32.2 選課分析3第三章概要設(shè)計43.1 各屬性E-R圖43.2 總 E-R圖5第四章邏輯設(shè)計及規(guī)范化64.1 關(guān)系模型64.2 關(guān)系數(shù)據(jù)表6第五章數(shù)據(jù)庫對象設(shè)計95.1 SQL物理結(jié)構(gòu)設(shè)計95.2 主要查詢功能sql語句135.3 選課系統(tǒng)設(shè)計要求內(nèi)容14第六章數(shù)據(jù)統(tǒng)計20參考文獻21心得體會22第一章 設(shè)計內(nèi)容與目標設(shè)計內(nèi)容：我們組設(shè)計的是學(xué)生選課管理系統(tǒng)，選課管理系統(tǒng)是學(xué)校教務(wù)系統(tǒng)中很龐大、很復(fù)雜的一個數(shù)據(jù)庫系統(tǒng)，在這里我們將其簡化，只選取了部分比較直觀的關(guān)系模式。我們設(shè)計的系統(tǒng)涉及院系信息、學(xué)生信息、教師信息、課程信息、選課信息、還有成績管理及查詢。

6、設(shè)計目標： 所實現(xiàn)的功能有：能記錄院系、學(xué)生、教師以及課程的基本情況，能記錄學(xué)生所選的課程以及相應(yīng)的成績和授課教師。每個學(xué)生和老師都只能被一個院系管理，為了簡化每個教師最多教授一門課程，每個學(xué)生可選多門課程或者不選，每個學(xué)生選修的每門課程有一個成績記載，每個教師教授最多一門課程，學(xué)生在系統(tǒng)中可以有查詢成績的權(quán)限，管理員有修改成績的權(quán)限。 第二章 需求分析2.1：需求背景隨著信息時代科技不斷提高,學(xué)校規(guī)模不斷擴大,教學(xué)質(zhì)量不斷提高,大學(xué)期間有許多公共選修課,以往的選修課方法是課堂報名或者紙上填寫方式報名,這種方法雖然直接,但是造成選課的盲目性,而且學(xué)生選過課程后不好在更改查看,一方面浪費大量的人

7、力，物力資源,另一方面浪費時間以及在認為統(tǒng)計過程中不可避免的出現(xiàn)差錯的情況。這給廣大的老師和學(xué)生帶來諸多的不便,管理起來也相當困難。使用網(wǎng)上選課系統(tǒng)可以提前進行網(wǎng)上選課,并且選課以后學(xué)生還可以在查看和更改,隨意選擇各個老師的課程,老師管理起來也比較方便。鑒于它的重要性,這就迫切需要研制開發(fā)一款功能強大,操作簡單,具有人性化的網(wǎng)上選課系統(tǒng)。2.2：選課分析根據(jù)分析，本系統(tǒng)的實體有學(xué)生、教師、課程和選課信息。描述學(xué)生的屬性有：學(xué)號、院系、姓名、性別、出生日期、入學(xué)時間、專業(yè)、班級。描述教師的屬性有：教師編號、姓名、性別、職稱、專業(yè)。描述課程的屬性有：課程編豪、課程名稱、授課老師、課程類型、上課時間

8、、上課地點、學(xué)時、學(xué)分。描述選課信息的屬性有：學(xué)號、課程編號、考試成績。每個學(xué)生、教師只能屬于一個院系，每個學(xué)生可選多門課程或者不選，每個教師教授最多一門課程。學(xué)生進入查詢系統(tǒng)后只能有查詢功能，管理員進入則可以有修改成績的權(quán)限。用戶登錄時需要有一個用戶名和密碼。第三章 概要設(shè)計3.1各屬性E-R圖學(xué)生 出生日期入學(xué)時間姓名性別班級專業(yè)學(xué)號院系教師教師編號職稱專業(yè)性別姓名課程學(xué)分學(xué)時上課時間上課地點課程類型授課老師課程名稱課程編號 登錄 密碼用戶名選課成績課程號學(xué)號3.2總 E-R圖數(shù)據(jù)庫的E-R圖：學(xué)號學(xué)生姓名m性別選課教師編號2姓名性別職稱專業(yè)出生日期n入學(xué)時間mn院系課程教師講授專業(yè)班級學(xué)

9、分學(xué)時上課地點上課時間課程類型授課教師課程名稱課程編號第四章 邏輯設(shè)計及規(guī)范化 4.1 關(guān)系模型： 登陸（用戶名、密碼）學(xué)生（學(xué)號、姓名、性別、出生日期、入學(xué)時間、院系、專業(yè)、班級）教師（教師編號、姓名、性別、職稱、專業(yè)）課程（課程編號、課程名稱、授課教師、課程類型、上課時間、上課地點、學(xué)時、學(xué)分）選課信息（學(xué)號、課程編號、考試成績）4.2關(guān)系數(shù)據(jù)表: Register屬性名數(shù)據(jù)類型可否為空含義完整性約束條件UsernameChar(10)否用戶名passwordChar(10)否密碼Student屬性名數(shù)據(jù)類型可否為空含義完整性約束Stunumchar(10)否學(xué)號主碼Stunamechar

10、(10)否姓名Stusexchar(2)否性別Stubirthchar(10)否出生日期StuadimChar(10)否入學(xué)時間Stumajorchar(10)否專業(yè)Stucollegechar(10)否院系Stuclasschar(10)否班級Teacher屬性名數(shù)據(jù)類型可否為空含義完整性約束Tenumchar(10)否教師編號主碼Tenamechar(10)否姓名Tesexchar(2)否性別Tetitlechar(10)否職稱Temajorchar(20)否專業(yè)Course屬性名數(shù)據(jù)類型可否為空含義完整性約束Cnochar(10)否教師編號主碼Cnamechar(10)否課程名稱Ctea

11、cherChar(10)否授課教師外碼Ctypechar(10)否課程類型Ctimechar(10)可以上課時間Cplacechar(10)可以上課地點ChoursChar(10)可以學(xué)時CcreditChar(10)可以學(xué)分Select_Course屬性名數(shù)據(jù)類型可否為空含義完整性約束Stunumchar(10)否學(xué)號主碼Cnochar(10)否課程編號resultChar(10)否成績數(shù)據(jù)字典：數(shù)據(jù)項編號數(shù)據(jù)項名數(shù)據(jù)項含義存儲結(jié)構(gòu)別名JS001Stunum學(xué)生編號Char(10)學(xué)號JS002Stucollege學(xué)生學(xué)院Char(20)學(xué)院JS003Stuname學(xué)生姓名Char(10)姓

12、名JS004Stusex學(xué)生性別Char(2)性別JS005Stubirth學(xué)生出生日期Char(10)出生日期JS006Stuadim學(xué)生入學(xué)時間Char(10)入學(xué)時間JS007Stumajor學(xué)生專業(yè)Char(10)專業(yè)JS008Stuclass學(xué)生班級Char(10)班級JS009Tenum教師編號Char(10)編號JS0010Tename教師姓名Char(10)姓名JS0011Tesex教師性別Char(2)性別JS0012Tetitle教師職稱Char(10)職稱JS0013Temajor教師專業(yè)Char(10)專業(yè)JS0014Cno課程編碼Char(10)課程號JS0015Cn

13、ame課程名稱Char(20)課程名JS0016Cteacher授課老師Char(10)教師JS0017Ctype課程類型Char(20)類型JS0018Ctime上課時間Char(20)時間JS0019Cplace上課地點Char(20)地點JS0020Chours上課學(xué)時Char(10)學(xué)時JS0021Ccredit上課學(xué)分Char(10)學(xué)分JS0022result學(xué)生課程成績Char(10)成績JS0023Username用戶名Char(10)JS0024password密碼Char(10)第五章 數(shù)據(jù)庫對象設(shè)計5.1 SQL物理結(jié)構(gòu)設(shè)計:1.新建數(shù)據(jù)庫：Create database

14、 SCdatabase2.新建用戶名和密碼表：Create table register( username char(10),password2 char(10);3.新建學(xué)生表:Create table student(Stunum char(10) NOT NULL primary key ,Stuname char(10) NOT NULL,Stusex char(2) NOT NULL default '男' check(Stusex='男' or Stusex='女'),Stubirth char(10) NOT NULL,Stuad

15、im char(10) NOT NULL,Stumajor char(10) NOT NULL,Stuclass char(10) NOT NULL,Stucollege char(10)NOT NULL)insert into student values ('s0001','景天','男','19890101','20080901','信管','1班','商學(xué)院')insert into student values ('s0002','云

16、天河','男','19900102','20080901','信管','2班','商學(xué)院')insert into student values ('s0003','唐雪見','女','19900901','20090901','信息工程','1班','信息學(xué)院')insert into student values ('s0004','張琳

17、芃','男','19890509','20080901','信息工程','2班','信息學(xué)院')insert into student values ('s0005','陳琳','女','19910201','20090901','英語','1班','外語學(xué)院')insert into student values ('s0006','王小雪

18、','女','19900520','20080901','英語','3班','外語學(xué)院')insert into student values ('s0007','李大龍','男','19891202','20080901','英語','4班','外語學(xué)院')insert into student values ('s0008','何天

19、9;,'男','19890915','20070901','數(shù)學(xué)','1班','數(shù)理學(xué)院')insert into student values ('s0009','盧巧音','女','19910712','20090901','數(shù)學(xué)','2班','數(shù)理學(xué)院')insert into student values ('s0010','卓不凡'

20、,'男','19891023','20090901','化學(xué)','1班','化工學(xué)院')insert into studentvalues ('s0011','林達','女','19900805','20080901','化學(xué)','5班','化工學(xué)院')insert into studentvalues ('s0012','林易','

21、女','19900807','20080901','化學(xué)','5班','化工學(xué)院')select *from student4.新建教師表：Create table teacher(Tenum char(10) NOT NULL primary key,Tename char(10) NOT NULL,Tesex char(2) NOT NULL default '男' check(Tesex='男' or Tesex='女'),Tetitle char(10

22、) NOT NULL,Temajor char(20) NOT NULL)insert into teachervalues ('t0001','李老師','女','教師','信管')insert into teachervalues ('t0002','張老師','女','教師','信息工程')insert into teachervalues ('t0003','何老師','男',&#

23、39;教授','物理')insert into teachervalues ('t0004','陳老師','女','教師','管理經(jīng)濟學(xué)')insert into teachervalues ('t0005','劉老師','男','教授','物理')insert into teachervalues ('t0006','鄭老師','男','教師',&

24、#39;信管')insert into teachervalues ('t0007','王老師','女','教師','信息工程')insert into teachervalues ('t0008','黃老師','男','教授','物理')insert into teachervalues ('t0009','祝老師','女','教授','管理經(jīng)濟學(xué)'

25、;)insert into teachervalues ('t0010','古老師','男','教師','物理')select *from teacher5.新建課程表：Create table course(Cno char(10) NOT NULL primary key,Cname char(20) NOT NULL,Cteacher char(10) NOT NULL,Ctype char(10) NOT NULL,Ctime char(20) NOT NULL,Cplace char(10),Chours

26、 char(10),Ccredit char(10),foreign key(Cteacher) references teacher(Tenum);insert into course values ('c0001','數(shù)據(jù)庫設(shè)計','t0001','計算機類','周一下午、節(jié)','JB105','48','4')insert into course values ('c0002','java基礎(chǔ)','t0002',&

27、#39;計算機類','周二上午、節(jié)','JB305','64','3')insert into course values ('c0003','電子商務(wù)','t0004','管理類','周三下午、節(jié)','JD201','48','2')insert into course values ('c0004','數(shù)字控制技術(shù)','t0005','電

28、子類','周三下午、節(jié)','HH303','48','2')insert into course values ('c0005','機械應(yīng)用','t0003','機械類','周四下午、節(jié)','HF406','64','3')insert into course values ('c0006','數(shù)據(jù)結(jié)構(gòu)','t0006','計算機類'

29、,'周二下午、節(jié)','JB103','48','4')insert into course values ('c0007','c語言基礎(chǔ)','t0007','計算機類','周四上午、節(jié)','JB405','48','3')insert into course values ('c0008','電子證務(wù)','t0009','管理類','

30、周一下午、節(jié)','JD301','48','3')insert into course values ('c0009','電路板基礎(chǔ)','t0008','電子類','周三下午、節(jié)','HF302','64','4')insert into course values ('c0010','工業(yè)設(shè)備應(yīng)用','t0010','機械類','周四下午、

31、節(jié)','HF406','64','3')select *from course6.新建選課表：Create table Select_Course(Stunum char(10),Cno char(10),Result Int,Primary key(Stunum,Cno),Foreign key(Stunum)references student (Stunum),Foreign key(Cno)references course(Cno)insert into Select_Coursevalues ('s0001',

32、'c0001','90')insert into Select_Coursevalues ('s0002','c0002','91')insert into Select_Coursevalues ('s0003','c0001','92')insert into Select_Coursevalues ('s0003','c0002','95')insert into Select_Coursevalues ('

33、;s0003','c0003','87')insert into Select_Coursevalues ('s0003','c0005','85')insert into Select_Coursevalues ('s0004','c0003','88')insert into Select_Coursevalues ('s0005','c0004','90')insert into Select_Course

34、values ('s0006','c0005','89')insert into Select_Coursevalues ('s0007','c0001','76')insert into Select_Coursevalues ('s0008','c0003','83')insert into Select_Coursevalues ('s0009','c0004','79')insert into S

35、elect_Coursevalues ('s0010','c0005','86')insert into Select_Coursevalues ('s0010','c0003','93')select *from Select_Course5.2主要查詢功能sql語句：1、登陸管理利用用戶和密碼登陸檢驗用戶名和密碼是否存在和正確select *from register where username='abc' and password2='123456'2、學(xué)生信

36、息管理1)刪除學(xué)生信息delete from student where stunum ='s0012'2)修改學(xué)生信息update student set stunum = '', stucollege = '', stuname = '',stusex = '', stubirth ='', stuadim = '', stumajor ='', stuclass = ''where snum = ''3)增加學(xué)生信息insert

37、into student values ('','','','','','','','')3、課程管理1)增加課程insert into course values ('',' ','',' ',' ','','','')2)課程修改update course set Cno ='' ,Cname='',Cte

38、acher='',Ctype='',Ctime='',Cplace='',Chours='',Ccredit='' where cno = ''4、查詢管理1)學(xué)生查詢(1)按學(xué)號查詢select * from student where stunum =''(2)按姓名查詢select * from student where stuname=''(3)按性別查詢select * from student where stusex=''

39、;(4)按專業(yè)查詢select * from student where Stumajor=''2)課程查詢(1)按課程號查詢select * from Select_Course,course where Select_Course.Stunum ='' and Select_Course.Cno = course.Cno(2)按授課教師查詢select * from Select_Course, teacherwhere Select_Course. Result ='' and teacher.Tenum=''3)成績查詢s

40、elect * from Select_Course,course,student where Select_Course.stunum ='' and Select_Course.Cno = course.Cno and Select_Course.Stunum = student.Stunum5.3選課系統(tǒng)設(shè)計要求內(nèi)容：(1) 在選課數(shù)據(jù)庫中，統(tǒng)計出選修課程超過門以上的學(xué)生信息。select *from studentwhere student.Stunum=(select Stunumfrom Select_Coursegroup by Stunum having cou

41、nt(Stunum)!<4)(2) 檢索出成績及格的、姓王的女同學(xué)信息。select * from studentwhere student.Stunum in(select Select_Course.Stunumfrom Select_Coursewhere Select_Course.Result!<60 and student.Stuname like'王%' and student.stusex='女')(3) 統(tǒng)計出平均分在80分以上的學(xué)生的信息，并將結(jié)果按平均分升序排列。select Stunum,avg(Result)as 平均分 i

42、nto #avgt from Select_Course group by Stunum having avg(Result)>=80select student.*,平均分 from student join #avgt on student.Stunum=#avgt.Stunumorder by 平均分 asc(4) 統(tǒng)計出至少有三名學(xué)生選修的課程的平均分，統(tǒng)計項包含課程編號和平均分。select Cno,avg(Result) as 平均分from Select_Course group by Cno having count(stunum)>=3(5)為每個實體表和關(guān)系表創(chuàng)

43、建合適的索引（聚簇索引、復(fù)合索引）、主碼、外碼Create unique index stunum On student(Stunum);Create unique index cno ON course(Cno);Create unique index tenum ON teacher(Tenum);(6)創(chuàng)建課程類別取值范圍為管理類、電子類、計算機類、機械類的規(guī)則，并與相應(yīng)的列綁定；create rule Ctype as Ctype in ('管理類','電子類','計算機類','機械類')goexec sp_bindrul

44、e 'Ctype','Course.Ctype'go(7)在選課聯(lián)系中創(chuàng)建成績默認值為的默認，并與相應(yīng)的列綁定；create default df_Result as 60alter table Select_CourseADD CONSTRAINT pk_Results DEFAULT(60) FOR Resultgoexec sp_bindefault df_Result,'Select_Course.Result'(8)創(chuàng)建一個可以通過課程類別和任課教師進行查詢課程的存儲過程:create proc proCourse課程類別char(20

45、),任課教師char(10)asselect Cno as 課程號,Cname as 課程名稱,Ctype as 課程類別號,Cteacher as 教師編號from course where Ctype=課程類別andCteacher=任課教師測試：EXECUTE proCourse 課程類別='電子類',任課教師='t0005'EXECUTE proCourse 課程類別='計算機類',任課教師='t0001'(9)通過觸發(fā)器定義未選課的學(xué)生不能從學(xué)生實體中刪除:create trigger SC_studentdelon s

46、tudent instead of deleteasbegindeclare temp char(10)select temp=Stunum from deletedif (select count(Select_Course.Stunum) from Select_Course where Select_Course.Stunum=temp)=0print'未選課的學(xué)生不能從學(xué)生實體中刪除'end測試：delete from student where Stunum= 's0012'delete from student where Stunum= 's0010'(10)建立包括選課學(xué)生的學(xué)號，姓名，系別，所選課程號，課程名，和成績的視圖:crea