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

一、引言1．課程實(shí)驗(yàn)?zāi)康恼n程設(shè)計(jì)為學(xué)生提供了一個(gè)既動(dòng)手又動(dòng)腦，獨(dú)立實(shí)踐的機(jī)會(huì)，將課本上的理論知識(shí)和實(shí)際有機(jī)的結(jié)合起來(lái)，鍛煉學(xué)生的分析解決實(shí)際問(wèn)題的能力。提高學(xué)生適應(yīng)實(shí)際，實(shí)踐編程的能力。課程設(shè)計(jì)的目的：加深對(duì)數(shù)據(jù)庫(kù)系統(tǒng)、軟件工程、程序設(shè)計(jì)語(yǔ)言的理論知識(shí)的理解和應(yīng)用水平；在理論和實(shí)驗(yàn)教學(xué)基礎(chǔ)上進(jìn)一步鞏固已學(xué)基本理論及應(yīng)用知識(shí)并加以綜合提高；學(xué)會(huì)將知識(shí)應(yīng)用于實(shí)際的方法，提高分析和解決問(wèn)題的能力，增強(qiáng)動(dòng)手能力；為畢業(yè)設(shè)計(jì)和以后工作打下必要基礎(chǔ)。課程實(shí)驗(yàn)題目：設(shè)計(jì)一個(gè)大學(xué)教學(xué)數(shù)據(jù)庫(kù)應(yīng)用。該系統(tǒng)涉及學(xué)生、教師、課程、分組、登記、數(shù)據(jù)。課程設(shè)計(jì)要求：運(yùn)用數(shù)據(jù)庫(kù)基本理論與應(yīng)用知識(shí)，在微機(jī)RDBMS(SQLServer)的環(huán)境上建立一個(gè)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)。要求把現(xiàn)實(shí)世界的事物及事物之間的復(fù)雜關(guān)系抽象為信息世界的實(shí)體及實(shí)體之間聯(lián)系的信息模型，再轉(zhuǎn)換為機(jī)器世界的數(shù)據(jù)模型和數(shù)據(jù)文件，并對(duì)數(shù)據(jù)文件實(shí)施檢索、更新和控制等操作。用E-R圖設(shè)計(jì)指定題目的信息模型；設(shè)計(jì)相應(yīng)的關(guān)系模型，確定數(shù)據(jù)庫(kù)結(jié)構(gòu)；分析關(guān)系模式各屬于第幾范式，闡明理由；設(shè)計(jì)應(yīng)用系統(tǒng)的系統(tǒng)結(jié)構(gòu)圖，確定系統(tǒng)功能；使用對(duì)象許可和命令許可、角色控制設(shè)計(jì)安全性控制檢查程序；通過(guò)設(shè)計(jì)關(guān)系的主碼約束、外碼約束和使用CHECK、RULE實(shí)現(xiàn)完整性控制；為每一參照關(guān)系設(shè)計(jì)插入、刪除、修改觸發(fā)器；實(shí)現(xiàn)應(yīng)用程序設(shè)計(jì)、編程、優(yōu)化功能；對(duì)系統(tǒng)的各個(gè)應(yīng)用程序進(jìn)行集成和調(diào)試，進(jìn)一步優(yōu)化系統(tǒng)功能、改善系統(tǒng)用戶界面完成實(shí)驗(yàn)內(nèi)容所指定的各項(xiàng)要求；分析遇到的問(wèn)題，總結(jié)并寫(xiě)出課程設(shè)計(jì)報(bào)告；自我評(píng)價(jià)用E-R圖設(shè)計(jì)選定題目的信息模型學(xué)生實(shí)體的E-R圖cityzipcityzipaddressStudent_namestateaddressStudent_namestatestudentstudentsexsexSTUDENTSSTUDENTS教師實(shí)體E-R圖phoneTeacher_namephoneTeacher_namesalaryteachersalaryteacherTEACHERSTEACHERS課程實(shí)體E-R圖departmentnurc_creditsCourse_namecoursedepartmentnurc_creditsCourse_namecourseCOURSESCOURSES分組實(shí)體E-R圖teachersectionnum_studentscourseteachersectionnum_studentscourseSECTIONSSECTIONS教師任課實(shí)體E-R圖sectiongradestudentsectiongradestudentcoursecourseENROLLSENROLLS三、設(shè)計(jì)相應(yīng)的關(guān)系模型，確定數(shù)據(jù)庫(kù)結(jié)構(gòu)*STUDENTS(student,student_name,address,zip,city,state,sex)*TEACHERS(teacher,teacher_name,phone,salary)*COURSES(course,course_name,department,nurc_credits)*SECTION(section,teacher,course,num_students)*ENROLLS(course,section,student,grade)分析關(guān)系模式各屬于第幾范式，闡明理由；STUDENTS屬于BCNF，因?yàn)槠渲械拿總€(gè)決定因素都包含了碼TEACHERS屬于BCNF，因?yàn)槠渲械拿總€(gè)決定因素都包含了碼COURSES屬于BCNF，因?yàn)槠渲械拿總€(gè)決定因素都包含了碼ENROLLS屬于BCNF，因?yàn)槠渲械拿總€(gè)決定因素都包含了碼SECTION屬于BCNF,因?yàn)槠渲械拿總€(gè)決定因素都包含了碼設(shè)計(jì)關(guān)系的主碼約束、外碼約束和使用CHECK實(shí)現(xiàn)完整性控制；STUDENTS信息基本表createtableSTUDENTS(studentchar(8)primarykey,student_namechar(20),addresschar(20),zipchar(10),citychar(20),statechar(8),sexchar(2));TEACHERS基本表createtableTEACHERS(teacherchar(8)primarykey,teacher_namechar(10),phonechar(10),salarychar(8));COURSES基本表

createtableCOURSES(coursechar(8)primarykey,course_namechar(20),departmentchar(20),nurc_creditschar(4));SECTION表createtableSECTION(sectionchar(4),teacherchar(8)primarykey,coursechar(8),num_studentschar(4),foreignkey(course)referencesCOURSES(course));

ENROLLS表createtableENROLLS(coursechar(8),sectionchar(4),studentchar(8),gradeSMALLINT,primarykey(course,section,student),foreignkey(course)referencesCOURSES(course),foreignkey(student)referencesSTUDENTS(student));

為參照關(guān)系設(shè)計(jì)插入、刪除、修改觸發(fā)器；實(shí)現(xiàn)應(yīng)用程序設(shè)計(jì)、編程、優(yōu)化功能；對(duì)系統(tǒng)的各個(gè)應(yīng)用程序進(jìn)行集成和調(diào)試，進(jìn)一步優(yōu)化系統(tǒng)功能、改善系統(tǒng)用戶界面完成實(shí)驗(yàn)內(nèi)容所指定的各項(xiàng)要求；四、源程序代碼清單usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.SqlClient;namespaceWindowsFormsApplication2{publicpartialclassForm1:Form{publicForm1(){InitializeComponent();}privatevoid學(xué)生ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("select*fromSTUDENTS",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//學(xué)生基本信息privatevoid教師ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("select*fromTEACHERS",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"TEACHERS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//教師基本信息privatevoid課程ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("select*fromCOURSES",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"COURSES");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//課程基本信息privatevoid分組ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("select*fromSECTION",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"SECTION");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//分組基本信息privatevoid登記ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("select*fromENROLLS",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"ENROLLS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//登記基本信息privatevoid查詢1ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("select*fromCOURSESwheredepartmentIN('Math','English')",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"COURSES");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索系名為“Math”和“English”的課程表信息privatevoid查詢2ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectteacher_name,phonefromTEACHERSorderbyteacher_name",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"TEACHERS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//按字母順序列出教師姓名和電話號(hào)碼privatevoid查詢3ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectteacher_name,phonefromTEACHERSwherephonenotlike'257%'",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"TEACHERS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索電話號(hào)碼不是以“257”打頭的教師姓名和電話號(hào)碼privatevoid查詢4ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectcourse_name,department,nurc_creditsfromCOURSESwheredepartment='Math'andnurc_credits>'3'",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"COURSES");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索數(shù)學(xué)系所有成績(jī)大于3的課程名、系名、學(xué)分privatevoid查詢5ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectstudent_name,studentfromSTUDENTSwherenotexists(select*fromENROLLSwhereSTUDENTS.student=ENROLLS.student)",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS,ENROLLS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索沒(méi)有選修任何課的學(xué)生姓名、學(xué)號(hào)privatevoid查詢6ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectstudentfromSTUDENTSwherenotexists(select*fromENROLLS,COURSESwhereSTUDENTS.student=ENROLLS.studentandCOURSES.course=ENROLLS.courseandcourse_name='CalculusIv')",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS,ENROLLS,COURSES");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索沒(méi)有選修課程“CalculusIv”的學(xué)生學(xué)號(hào)privatevoid查詢7ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectdistinctstudentfromENROLLSAwherenotexists(select*fromTEACHERSwhereteacher_name='Dr.Lowe'andnotexists(select*fromENROLLSBwhereB.course=A.course))",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"TEACHERS,ENROLLS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索至少選修教師“Dr.Lowe”所開(kāi)全部課程的學(xué)生學(xué)號(hào)privatevoid查詢8ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectCOURSES.course,count(num_students),COURSES.course_namefromSECTION,COURSESwhereCOURSES.course=SECTION.coursegroupbyCOURSES.course_name,COURSES.course",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"COURSES,SECTION");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索每門(mén)課學(xué)生登記的人數(shù)、相應(yīng)的課程名、課程號(hào)、分組號(hào)privatevoid查詢9ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectstudent_namefromSTUDENTSwherestudentin(selectstudentfromENROLLSgroupbystudenthavingcount(*)>2)",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS,ENROLLS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索選修兩門(mén)以上課程的學(xué)生姓名privatevoid查詢10ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectdistinctcourse,student_namefromSTUDENTS,ENROLLSwhereENROLLS.student=STUDENTS.studentandsex='M'",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS,ENROLLS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索只有男生選修的課程和學(xué)生名privatevoid查詢11ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectSTUDENTS.student_name,COURSES.course_name,TEACHERS.teacher_name,ENROLLS.gradefromCOURSES,STUDENTS,TEACHERS,ENROLLS,SECTIONwhereSTUDENTS.student=ENROLLS.studentandENROLLS.course=COURSES.courseandSECTION.teacher=TEACHERS.teacherandSECTION.course=COURSES.course",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS,ENROLLS,TEACHERS,COURSES,SECTION");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//檢索所有學(xué)生選修的課程名、學(xué)生名、授課教師名、該生成績(jī)privatevoid查詢12ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectavg(grade)fromCOURSES,ENROLLS,TEACHERS,SECTIONwhereCOURSES.course=ENROLLS.courseandTEACHERS.teacher=SECTION.teacherandcourse_name='englishcomposition'andteacher_namelike'%Engle'",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"ENROLLS,TEACHERS,COURSES,SECTION");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//統(tǒng)計(jì)教師“Engle”教的英語(yǔ)課的學(xué)生平均分privatevoid查詢13ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectcourse_name,count(num_students)fromCOURSES,SECTIONwhereCOURSES.course=SECTION.coursegroupbycourse_name",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"COURSES,SECTION");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//統(tǒng)計(jì)各門(mén)課程的選課人數(shù)privatevoid查詢14ToolStripMenuItem_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectdistinctstatefromSTUDENTS",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//統(tǒng)計(jì)學(xué)生來(lái)自省的省名privatevoid查詢15ToolStripMenuItem1_Click(objectsender,EventArgse){stringconnectionStr="DataSource=qinjia-PC;InitialCatalog=sjk;IntegratedSecurity=True";SqlConnectionconn=newSqlConnection(connectionStr);conn.Open();SqlDataAdaptersdr1=newSqlDataAdapter("selectstudent_name,COURSES.course_name,teacher_name,gradefromSTUDENTS,TEACHERS,COURSES,SECTION,ENROLLSwhereTEACHERS.teacher=SECTION.teacherandSECTION.course=ENROLLS.courseandSTUDENTS.student=ENROLLS.studentandCOURSES.course=ENROLLS.course;",conn);DataSetds1=newDataSet();sdr1.Fill(ds1,"STUDENTS,ENROLLS,TEACHERS,COURSES,SECTION");dataGridView1.DataSource=ds1.Tables[0];conn.Close();}//輸出如下報(bào)表：學(xué)生名課程名教師名成績(jī)privatevoid修改16ToolStripMenuItem_Click(objectsender,EventArgse){stringconnecti