數(shù)據(jù)庫課程設(shè)計之學生信息管理系統(tǒng)

1、一、弓1 課程實驗?zāi)康恼n程設(shè)計為學生提供了一個既動手又動腦，獨立實踐的機會，將課本上的理論知識和實際有機的結(jié)合起來，鍛煉學生的分析解決實際問題的能力。提高學生適應(yīng)實踐編程的能力。課程設(shè)計的目的:加深對數(shù)據(jù)庫系統(tǒng)、軟件工程、程序設(shè)計語言的理論知識的理解和應(yīng)用水平;在理論和實驗教學基礎(chǔ)上進一步鞏固已學基本理論及應(yīng)用知識并加以綜合 提高;學會將知識應(yīng)用于實際的方法，提高分析和解決問題的能力，增強動手能力;2為畢業(yè)設(shè)計和以后工作打下必要基礎(chǔ)。課程實驗題目：設(shè)計一個大學教學數(shù)據(jù)庫應(yīng)用。該系統(tǒng)涉及學生、教師、課程、分組、登記、 數(shù)據(jù)。3.課程設(shè)計要求：運用數(shù)據(jù)庫基本理論與應(yīng)用知識，在微機 RDBMS(SQ

2、L Server的環(huán)境上建立一 個數(shù)據(jù)庫應(yīng)用系統(tǒng)。要求把現(xiàn)實世界的事物及事物之間的復雜關(guān)系抽象為信息世界 的實體及實體之間聯(lián)系的信息模型，再轉(zhuǎn)換為機器世界的數(shù)據(jù)模型和數(shù)據(jù)文件，并 對數(shù)據(jù)文件實施檢索、更新和控制等操作。.5.6.7.&9.外碼約束和使用 CHECK RULE實現(xiàn)完整性控制;用E-R圖設(shè)計指定題目的信息模型； 設(shè)計相應(yīng)的關(guān)系模型，確定數(shù)據(jù)庫結(jié)構(gòu)； 分析關(guān)系模式各屬于第幾范式，闡明理由； 設(shè)計應(yīng)用系統(tǒng)的系統(tǒng)結(jié)構(gòu)圖，確定系統(tǒng)功能； 使用對象許可和命令許可、角色控制設(shè)計安全性控制檢查程序； 通過設(shè)計關(guān)系的主碼約束、 為每一參照關(guān)系設(shè)計插入、刪除、修改觸發(fā)器； 實現(xiàn)

3、應(yīng)用程序設(shè)計、編程、優(yōu)化功能； 對系統(tǒng)的各個應(yīng)用程序進行集成和調(diào)試，進一步優(yōu)化系統(tǒng)功能、改善系統(tǒng)用 戶界面完成實驗內(nèi)容所指定的各項要求；10. 分析遇到的問題，總結(jié)并寫出課程設(shè)計報告；11. 自我評價】、用E-R圖設(shè)計選定題目的信息模型令學生實體的E-R圖令教師實體E-R圖令課程實體E-R圖令分組實體E-R圖令教師任課實體E-R圖三、設(shè)計相應(yīng)的關(guān)系模型，確定數(shù)據(jù)庫結(jié)構(gòu)STUDENTSstuden t,stude nt_n ame,address,zi p,city,state,sex)TEACHEReacher,teacher name,phone,salary)COURSEc5urse,co

4、urse name,department,nurc_credits)SECTION(sectio n, teacher,course, num _stude nts)ENROLLScourse,sectio n,stude nt,grade)分析關(guān)系模式各屬于第幾范式，闡明理由;STUDENTSTEACHERSCOURSESENROLLSSECTION屬于BCNF因為其中的每個決定因素都包含了碼屬于BCNF因為其中的每個決定因素都包含了碼屬于BCNF因為其中的每個決定因素都包含了碼屬于BCNF因為其中的每個決定因素都包含了碼屬于BCNF,因為其中的每個決定因素都包含了碼設(shè)計關(guān)系的主碼約束、外碼

5、約束和使用 CHEC實現(xiàn)完整性控制;STUDENT信息基本表 create table STUDENTS( stude nt char(8) p rimary key, stude nt_n ame char(20), address char(20), zip char(10), city char(20), state char(8), sex char(2);TEACHERS 基本表 create table TEACHERS( teacher char(8) p rimary key, teacher_ name char(10), phone char(10), salary cha

6、r(8);COURSE基 本表 create table COURSES( course char(8) p rimary key, course_ name char(20), dep artme nt char(20), nu rc_credits char(4);SECTION表create table SECTION(sect ion char(4),teacher char(8) p rimary key,course char(8),nu m_stude nts char(4),foreig n key (course) references COURSES(course);ENR

7、OLLS 表create table ENROLLS( course char(8), secti on char(4), stude nt char(8), grade SMALLINT, p rimary key(course,sectio n,stude nt), foreig n key (course) refere nces COURSES(course), foreig n key (stude nt) refere nces STUDENTS(stude nt);為參照關(guān)系設(shè)計插入、刪除、修改觸發(fā)器;實現(xiàn)應(yīng)用程序設(shè)計、編程、優(yōu)化功能;對系統(tǒng)的各個應(yīng)用程序進行集成和調(diào)試，進一步優(yōu)

8、化系統(tǒng)功能、改善系統(tǒng)用戶界面完成實驗內(nèi)容所指定的各項要求;四、源程序代碼清單using System;using System.Collections.Generic;using System.Co mpo nentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;names pace WindowsFormsA pp Iication2p ublic p artial cl

9、ass Form1 : Formp ublic Form1()InitializeCo mpo nent();p rivate voidstring學生 ToolStri pMenultem_Click(object sender, EventArgs e)connectionStr"DataSource=qinjia-P C;lnitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pte

10、r sdr1 = new SqlDataAda pter("select* from STUDENTS", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "STUDENTS");dataGridView1.DataSource = ds1.Tables0;conn.Close();II學生基本信息p rivate void教師 ToolStri pMenuItem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=q

11、injia-P C;lnitialCatalog=sjk;lntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter("select* from TEACHERS", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "TEACHERS");dataGridView1.DataSource

12、= ds1.Tables0;conn.Close();/教師基本信息p rivate void課程 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;lntegrated"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connect

13、ionStr);con n.Op en();Security=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter("select* from COURSES", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "COURSES");dataGridView1.DataSource = ds1.Tables0;conn.

14、Close();/課程基本信息p rivate void分組 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter("selec

15、t* from SECTION", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "SECTION");dataGridView1.DataSource = ds1.Tables0;conn.Close();/分組基本信息p rivate void登1己 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStrSqlDataAda pter sdr1 = new SqlDataAda pter("select* fro

16、m ENROLLS", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "ENROLLS");dataGridView1.DataSource = ds1.Tables0;conn.Close();/登記基本信息p rivate void查詢 1 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;lnitialCatalog=sjk;lntegratedstringco

17、nnectionStr"DataSource=qinjia-P C;lnitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);Security=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda ptersdr1new SqlDataAdapter("select*fromCOURSESwhere dep

18、artmentIN('Math','English')", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "COURSES");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /p rivate void檢索系名為"Math ”和"English ” 的課程表信息查詢 2 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectio

19、nStr"DataSource=qinjia-P C;lnitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 =new SqlDataAda pter("selectteacher_name ,p honefrom TEACHERSorder by teacher_name", conn);DataSet ds1 = new DataSet();sd

20、r1.Fill(ds1, "TEACHERS");dataGridView1.DataSource = ds1.Tables0;conn.Close();按字母順序列出教師姓名和電話號碼p rivate void查詢 3 ToolStri pMenultem_Click(object sender, EventArgs e)sdr1 = new SqlDataAdapter("selectteacher_name,phone from TEACHERwherecon n.Op en();SqlDataAda pter phone not like '257

21、%"', conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "TEACHERS");dataGridViewl.DataSource = ds1.Tables0;conn.Close(); / p rivate void檢索電話號碼不是以“ 257 ”打頭的教師姓名和電話號碼查詢 4 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;lnitialCatalog=

22、sjk;lntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter("select course_name,de partment,nurc_creditsfrom COURSES where dep artment='Math' and nurc_credits>'3'", conn);DataSet ds1 = ne

23、w DataSet();sdr1.Fill(ds1, "COURSES");dataGridViewl.DataSource = ds1.Tables0;conn.Close(); /檢索數(shù)學系所有成績大于 3的課程名、系名、學分p rivate void查詢 5 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;lnitialCatalog=sjk;lntegratedSecurity=True"SqlConnect

24、ion conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter("select student_name,student from STUDENTSwhere not exists (select* from ENROLLS where STUDENTS.student=ENROLLS.student)", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "STUDENTS,EN

25、ROLLS");dataGridViewl.DataSource = ds1.Tables0;conn.Close();/檢索沒有選修任何課的學生姓名、學號P rivate void查詢 6 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;lnitialCatalog=sjk;lntegratedSecunty=True"SqlConnection conn = new SqlConnection(connectionStr)

26、;con n.Op en();SqlDataAda ptersdr1 = new SqlDataAdapter("selectstudent from STUDENTwhere not exists(select*fromENROLLS,COURSES whereSTUDENTS.student=ENROLLS.studentandCOURSES.course=ENROLLS.course and course_name='Calculus Iv')", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, &qu

27、ot;STUDENTS,ENROLLS,COURSES");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /p rivate void檢索沒有選修課程“ Calculus Iv ”的學生學號查詢 7 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn

28、 = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter("select distinct student from ENROLLS A where not exists (select* from TEACHERwhere teacher_name='Dr.Lowe' and not exists (select * from ENROLLSB where B.course=A.course)", conn);DataSet ds

29、1 = new DataSet();sdr1.Fill(ds1, "TEACHERS,ENROLLS");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /p rivate void檢索至少選修教師“ Dr. Lowe ”所開全部課程的學生學號查詢 8 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecuri

30、ty=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda ptersdr1newSqlDataAda pter("selectCOURSES.course,count(num_students),COURSES.course_namefromSECTION,COURSES whereCOURSES.course=SECTION.course group by COURSES.course_name,COURSES.course ", conn);Da

31、taSet ds1 = new DataSet();sdr1.Fill(ds1, "COURSES,SECTION");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /p rivate void檢索每門課學生登記的人數(shù)、相應(yīng)的課程名、課程號、分組號查詢 9 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSec

32、urity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda ptersdr1 = new SqlDataAdapter("select student_name from STUDENTSwhere student in(select student from ENROLLS group by student having count(*)>2 )", conn);DataSet ds1 = new DataSet();sdr1.Fill(

33、ds1, "STUDENTS,ENROLLS");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /檢索選修兩門以上課程的學生姓名p rivate void查詢 10 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlCo

34、nnection(connectionStr);con n.Op en();SqlDataAda ptersdr1 = new SqlDataAdapter("selectdistinctcourse,student_name fromSTUDENTS,ENROLLS where ENROLLS.student=STUDENTS.student and sex='M'", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "STUDENTS,ENROLLS");dataGridView1.D

35、ataSource = ds1.Tables0;檢索只有男生選修的課程和學生名 /p rivate voidconn.Close();查詢 11 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda p

36、tersdr1newSqlDataAda pter("selectfromSTUDENTS.student_name,COURSES.course_name,TEACHERS.teacher_name,ENROLLS.gradeCOURSES,STUDENTS,TEACHERS,ENROLLS,SECTIONvhereSTUDENTS.student=ENROLLS.studentandENROLLS.course=COURSES.courseandSECTION.teacher=TEACHERS.teacherandp rivate void查詢 14 ToolStri pMenu

37、ltem_Click(object sender, EventArgs e)SECTION.course=COURSES.course", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "STUDENTS,ENROLLS,TEACHERS,COURSES,SECTION");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /p rivate void檢索所有學生選修的課程名、學生名、授課教師名、該生成績查詢 12 ToolStri pMenultem_Cl

38、ick(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda ptersdr1new SqlDataAda pter("selectavg(grade)fromCOURSES,ENROLLS,TEACHERS,SECTION whereCOU

39、RSES.course=ENROLLS.courseandTEACHERS.teacher=SECTION.teacher and course_name='englishcomposition'and teacher namelike'%Engle'", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "ENROLLS,TEACHERS,COURSES,SECTION");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /p

40、 rivate void統(tǒng)計教師“ Engle ”教的英語課的學生平均分查詢 13 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter(

41、"select course_name,count(num_students) fromCOURSES,SECTION where COURSES.course=SECTION.course group by course_name", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "COURSES,SECTION");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /統(tǒng)計各門課程的選課人數(shù)stringconnectionStr"Dat

42、aSource=qinjia-P C;InitialCatalog=sjk;lntegratedSecunty=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda pter sdr1 = new SqlDataAda pter("select distinct state from STUDENTS", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "STUDENTS");dat

43、aGridView1.DataSource = ds1.Tables0;統(tǒng)計學生來自省的省名 /p rivate voidconn.Close();查詢 15 ToolStri pMenultem1_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSqlDataAda ptersdr1 = new SqlDataAdapter("updateTEACHERSset teacher='666' wher

44、eSecurity=True"SqlConnection conn = new SqlConnection(connectionStr);con n.Op en();SqlDataAda ptersdr1newSqlDataAda pter("selectstudent_name,COURSES.course_name,teacher_name,gradefromSTUDENTS,TEACHERS,COURSES,SECTION,ENROLLS whereTEACHERS.teacher=SECTION.teacher andSECTION.course=ENROLLS.c

45、ourseandSTUDENTS.student=ENROLLS.studentandCOURSES.course=ENROLLS.course;", conn);DataSet ds1 = new DataSet();sdr1.Fill(ds1, "STUDENTS,ENROLLS,TEACHERS,COURSES,SECTION");dataGridView1.DataSource = ds1.Tables0;conn.Close(); /輸出如下報表：_學生名課程名教師名成績p rivate void修改 16 ToolStri pMenultem_Click(object sender, EventArgs e)stringconnectionStr"DataSource=qinjia-P C;InitialCatalog=sjk;IntegratedSecurity=True"