網(wǎng)上選課數(shù)據(jù)庫設(shè)計與實現(xiàn)

1、網(wǎng)上選課系統(tǒng)數(shù)據(jù)庫設(shè)計與實現(xiàn)組成員及所完成的工作：組長 曹洪瑞 自動化3班3009203187所完成的工作：數(shù)據(jù)庫整體結(jié)構(gòu)設(shè)計、系統(tǒng)的實現(xiàn)、數(shù)據(jù)庫工作審核組員 周翔電氣4班3009216059所完成的工作：系統(tǒng)需求分析、物理設(shè)計組員 陳興元 電氣4班3009203188所完成的工作：ER圖的繪制、關(guān)系模式的設(shè)計系統(tǒng)名稱：網(wǎng)上選課系統(tǒng)一.系統(tǒng)需求該“學(xué)生管理系統(tǒng)”只是對數(shù)據(jù)庫應(yīng)用技術(shù)的一個樣本數(shù)據(jù)庫的實例，重在對數(shù)據(jù)庫一 些方法的熟悉與掌握，其中包括從數(shù)據(jù)庫的概念模型到ER圖的繪制，再到數(shù)據(jù)庫模 式的建立（即為數(shù)據(jù)庫及其基本表的建立），而后數(shù)據(jù)的錄入（在此從略），最后 完成一些簡單的關(guān)系運(yùn)算表達(dá)

2、式的表達(dá)和相關(guān)的Select查詢語句的使用。系統(tǒng)結(jié)構(gòu)簡述：本系統(tǒng)包括六個實體：學(xué)生；課程；教師；學(xué)院；社團(tuán)；活動實體之間的關(guān)系轉(zhuǎn)換為其他四個表：選修；代課；加入社團(tuán)；組織活動（都是多對 多關(guān)系的轉(zhuǎn)換）實體“學(xué)院”與“學(xué)生”之間一對多的關(guān)系轉(zhuǎn)換到表“學(xué)生”中注：各個基本表中關(guān)鍵字已在關(guān)系模式中說明該數(shù)據(jù)庫需要進(jìn)行如下數(shù)據(jù)處理：要查詢所有學(xué)生的個人信息：學(xué)號，姓名，性別，出生年月，身份證號，學(xué)院名稱學(xué)生要查詢本學(xué)期可選課程詳細(xì)情況，并按“課程類型”分組輸入：學(xué)號輸出：課程名，學(xué)分?jǐn)?shù)，學(xué)時數(shù)，課程類型，上課老師學(xué)生選課：輸入：學(xué)號，課程號輸出：選課是否成功某位教師想查詢自己本學(xué)期所教的課程：輸入：教

3、工號輸出：教工號，姓名，性別，課程名，課程類型，上課人數(shù)二.ER圖已修學(xué)生屬于學(xué)院已修課程取消包含開設(shè)教授登記教工教師已修學(xué)生屬于學(xué)院已修課程取消包含開設(shè)教授登記教工二.關(guān)系模式系統(tǒng)中的實體有：學(xué)生；課程；教師；學(xué)院；已修課程關(guān)系模式如下：學(xué)生（學(xué)號#，姓名，性別，出生年月，身份證號，宿舍號，學(xué)院編號）課程（課程編號#，課程名，課程類型，學(xué)分?jǐn)?shù)，學(xué)時數(shù)）選課（學(xué)號#，課程編號#）教師（教工號#，姓名，性別，學(xué)歷，電話，學(xué)院）學(xué)院（學(xué)院名稱）4.物理設(shè)計5.系統(tǒng)實現(xiàn)1.數(shù)據(jù)庫及其基本表的建立數(shù)據(jù)庫的建立用企業(yè)管理器，基本表的建立用T-SQL語言：數(shù)據(jù)庫名稱為：SM_System建立數(shù)據(jù)庫的T-S

4、QL代碼如下：CREATE DATABASE ChooseCOUrses ON PRIMARY(name = NChooseCourses, filename = NC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAChooseCourses.mdf , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON(name = NChooseCourses_log, filename = NC:Program FilesMicrosoft SQL ServerMSSQL.1

5、MSSQLDATAChooseCourses_log.ldf , size = 1024KB , maxsize = 2048GB , FILEGROWTH = 10%)COLLATE Chinese_PRC_CI_AS建立基本表的T-SQL代碼如下:USE ChooseCourses create table dbo.Students( Studentld varchar(20) NOT NULL, Name nvarchar(50) NOT NULL, Grade int not null, Sex bit NOT NULL, iDCardNo varchar(18) NULL, Room

6、No nvarchar(20) NULL, Birthday datetime null, instituteid int not null, CONSTRAINT PK_Students PRIMARY KEY CLUSTERED (Studentid ASC)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY )ON PRIMARYCREATE TABLE dbo.Courses( CourseId int not null, Name nvarchar(30) NOT NULL, ClassHour smallint not null, Credit tinyi

7、nt NOT NULL, Type nvarchar(20) NOT NULL, InstituteId int not null, constraint PK_courses primary key clustered (Courseid ASC)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY )ON PRIMARYcreate table dbo.Teachers( Teacherid varchar(20) NOT NULL, Name nvarchar(50) NOT NULL, Education nvarchar(50) NOT NULL, Sex b

8、it NOT NULL, Number varchar(20) NOT NULL, MobilePhone varchar(20) NULL, constraint PK_Teachers primary key clustered (Teacherid ASC)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY )ON PRIMARYcreate table dbo.institutes( instituteid int not null, Name nvarchar(15) NOT NULL, constraint PK_institutes primary ke

9、y clustered(instituteid ASC)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY )ON PRIMARYcreate table dbo.CompletedCourses( CompletedCourseid int not null, Studentid varchar(20) NOT NULL, Courseid int not null, constraint PK_completedCourses primary key clustered ( CompletedCourseid ASC)WITH (IGNORE_DUP_KEY =

10、OFF) ON PRIMARY )ON PRIMARYcreate table dbo.Relations( Relationid int not null, Courseid int not null, Studentid varchar(20) NOT NULL, Teacherid varchar(20) NOT NULL, constraint PK_Relations primary key clustered (Relationid ASC)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY )ON PRIMARYALTER TABLE dbo.Stude

11、nts WITH CHECK ADD CONSTRAINT FK_StudentS_InstitUtes FOREIGN KEY(InstitUteId)REFERENCES dbo.Institutes (InstituteId)alter table dbo.Courses with check add constraint FK_Courses_CoursesFOREIGN KEY(Courseld)references dbo.Courses (Courseid)alter table dbo.Courses with check add constraint FK_Courses_i

12、nstitutesFOREIGN KEY(lnstituteld)REFERENCES dbo.lnstitutes (InstituteId)alter table dbo.CompletedCourses WITH check add constraintFK_Completedcourses_Courses foreign KEY(Courseid)references dbo.Courses (Courseid)alter table dbo.CompletedCourses WITH check add constraintFK_Completedcourses_Students f

13、oreign KEY(Studentid)REFERENCES dbo.Students (Studentid)alter table dbo.Relations with check add constraint FK_Relations_Courses foreign KEY(Relationid)references dbo.Courses (Courseid)alter table dbo.Relations with check add constraint FK_Relations_Students foreign KEY(studentid)REFERENCES dbo.Stud

14、ents (Studentid)alter table dbo.Relations with check add constraint FK_Relations_Teachers foreign KEY(Teacherid)REFERENCES dbo.Teachers (Teacherid)輸入數(shù)據(jù)：INSERT INTO dbo.Students (Studentid,Name,Grade,Sex, iDCardNo,RoomNo,Birthday,instituteid)VALUES (1234567,張三,1,0,510123456789012345,101 寢室，1989-01-01

15、,1)insert into dbo.Courses(Name,ClassHour,Credit,Type,instituteid)VALUES(數(shù)學(xué)，36,6,必修，1)insert into dbo.Relations(Courseid,Studentid,Teacherid)VALUES(0,0,0)insert into dbo.Teachers(Name,Education,Sex,Number,MobilePhone)VALUES(李四，大學(xué),0,t-123456,1398888888)INSERT INTO dbo.institutes(Name)VALUES(計算機(jī)學(xué)院)ins

16、ert into dbo.CompletedCourses(StudentidCourseid)VALUES(12345678.0)創(chuàng)建索引(略)創(chuàng)建視圖(略)創(chuàng)建存儲過程create procedure SelectAllstudentsASselect * FROM dbo.StudentsGOcreate procedure SelectableCoursesstudentid varchar(20)ASBEGINRelations r on s Studentid = studentidTeachers t on t Teacherid = r.Teacherid Completedcourses cc on cc Studentid = studentid!= cc.Courseidselect c.Name, c.ClassHour , c.Credit , t.Name from dboRelations r on s Studentid = st