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

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

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

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

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

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

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

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

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

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

10、mechar(10)否姓名stusexchar(2)否性別stubirthchar(10)否出生日期stuadimchar(10)否入學時間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)否課程

11、名稱cteacherchar(10)否授課教師外碼ctypechar(10)否課程類型ctimechar(10)可以上課時間cplacechar(10)可以上課地點chourschar(10)可以學時ccreditchar(10)可以學分select_course屬性名數(shù)據(jù)類型可否為空含義完整性約束stunumchar(10)否學號主碼cnochar(10)否課程編號resultchar(10)否成績數(shù)據(jù)字典：數(shù)據(jù)項編號數(shù)據(jù)項名數(shù)據(jù)項含義存儲結構別名js001stunum學生編號char(10)學號js002stucollege學生學院char(20)學院js003stuname學生姓名cha

12、r(10)姓名js004stusex學生性別char(2)性別js005stubirth學生出生日期char(10)出生日期js006stuadim學生入學時間char(10)入學時間js007stumajor學生專業(yè)char(10)專業(yè)js008stuclass學生班級char(10)班級js009tenum教師編號char(10)編號js0010tename教師姓名char(10)姓名js0011tesex教師性別char(2)性別js0012tetitle教師職稱char(10)職稱js0013temajor教師專業(yè)char(10)專業(yè)js0014cno課程編碼char(10)課程號js

13、0015cname課程名稱char(20)課程名js0016cteacher授課老師char(10)教師js0017ctype課程類型char(20)類型js0018ctime上課時間char(20)時間js0019cplace上課地點char(20)地點js0020chours上課學時char(10)學時js0021ccredit上課學分char(10)學分js0022result學生課程成績char(10)成績js0023username用戶名char(10)js0024password密碼char(10)第五章 數(shù)據(jù)庫對象設計5.1 sql物理結構設計:1.新建數(shù)據(jù)庫：create da

14、tabase scdatabase2.新建用戶名和密碼表：create table register( username char(10),password2 char(10);3.新建學生表: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

15、,stuadim 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班','商學院')insert into student values ('s0002',

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

17、#39;張琳芃','男','19890509','20080901','信息工程','2班','信息學院')insert into student values ('s0005','陳琳','女','19910201','20090901','英語','1班','外語學院')insert into student values ('s0006',&#

18、39;王小雪','女','19900520','20080901','英語','3班','外語學院')insert into student values ('s0007','李大龍','男','19891202','20080901','英語','4班','外語學院')insert into student values ('s0008','

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

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

21、,'女','19900807','20080901','化學','5班','化工學院')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 c

22、har(10) 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)濟學')insert into teachervalues ('t0005','劉老師','男','教授','物理')insert into teachervalues ('t0006','鄭老師','男','教師&

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

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),

26、chours char(10),ccredit char(10),foreign key(cteacher) references teacher(tenum);insert into course values ('c0001','數(shù)據(jù)庫設計','t0001','計算機類','周一下午、節(jié)','jb105','48','4')insert into course values ('c0002','java基礎','t0002&

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

28、'電子類','周三下午、節(jié)','hh303','48','2')insert into course values ('c0005','機械應用','t0003','機械類','周四下午、節(jié)','hf406','64','3')insert into course values ('c0006','數(shù)據(jù)結構','t0006','計算機

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

30、,'周一下午、節(jié)','jd301','48','3')insert into course values ('c0009','電路板基礎','t0008','電子類','周三下午、節(jié)','hf302','64','4')insert into course values ('c0010','工業(yè)設備應用','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_

34、coursevalues ('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

35、into select_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'

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

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

38、9;,cteacher='',ctype='',ctime='',cplace='',chours='',ccredit='' where cno = ''4、查詢管理1)學生查詢(1)按學號查詢select * from student where stunum =''(2)按姓名查詢select * from student where stuname=''(3)按性別查詢select * from student where stusex=

39、9;'(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

40、)成績查詢select * from select_course,course,student where select_course.stunum ='' and select_course.cno = course.cno and select_course.stunum = student.stunum5.3選課系統(tǒng)設計要求內容：(1) 在選課數(shù)據(jù)庫中，統(tǒng)計出選修課程超過門以上的學生信息。select *from studentwhere student.stunum=(select stunumfrom select_coursegroup by stunum havi

41、ng count(stunum)!<4)(2) 檢索出成績及格的、姓王的女同學信息。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分以上的學生的信息，并將結果按平均分升序排列。select stunum,avg(result)as

42、 平均分 into #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)計出至少有三名學生選修的課程的平均分，統(tǒng)計項包含課程編號和平均分。select cno,avg(result) as 平均分from select_course group by cno having count(stunum)>=3(5)為每個實體

43、表和關系表創(chuàng)建合適的索引（聚簇索引、復合索引）、主碼、外碼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ī)則，并與相應的列綁定；create rule ctype as ctype in ('管理類','電子類','計算機類','機械類')goexec sp_b

44、indrule 'ctype','course.ctype'go(7)在選課聯(lián)系中創(chuà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課程類別c

45、har(20),任課教師char(10)asselect cno as 課程號,cname as 課程名稱,ctype as 課程類別號,cteacher as 教師編號from course where ctype=課程類別andcteacher=任課教師測試：execute procourse 課程類別='電子類',任課教師='t0005'execute procourse 課程類別='計算機類',任課教師='t0001'(9)通過觸發(fā)器定義未選課的學生不能從學生實體中刪除:create trigger sc_studentd

46、elon student 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'未選課的學生不能從學生實體中刪除'end測試：delete from student where stunum= 's0012'delete from student where stunum= 's0010'(10)建立包括選課學生的學號，姓名，系別，所選課程號，課程名，和成績的視圖:crea