数据库课程设计之学生信息管理系统

1、一、弓1 课程实验目的课程设计为学生提供了一个既动手又动脑，独立实践的机会，将课本上的理论知识和实际有机的结合起来，锻炼学生的分析解决实际问题的能力。提高学生适应实践编程的能力。课程设计的目的:加深对数据库系统、软件工程、程序设计语言的理论知识的理解和应用水平;在理论和实验教学基础上进一步巩固已学基本理论及应用知识并加以综合 提高;学会将知识应用于实际的方法，提高分析和解决问题的能力，增强动手能力;2为毕业设计和以后工作打下必要基础。课程实验题目：设计一个大学教学数据库应用。该系统涉及学生、教师、课程、分组、登记、 数据。3.课程设计要求：运用数据库基本理论与应用知识，在微机 RDBMS(SQ

2、L Server的环境上建立一 个数据库应用系统。要求把现实世界的事物及事物之间的复杂关系抽象为信息世界 的实体及实体之间联系的信息模型，再转换为机器世界的数据模型和数据文件，并 对数据文件实施检索、更新和控制等操作。.5.6.7.&9.外码约束和使用 CHECK RULE实现完整性控制;用E-R图设计指定题目的信息模型； 设计相应的关系模型，确定数据库结构； 分析关系模式各属于第几范式，阐明理由； 设计应用系统的系统结构图，确定系统功能； 使用对象许可和命令许可、角色控制设计安全性控制检查程序； 通过设计关系的主码约束、 为每一参照关系设计插入、删除、修改触发器； 实现

3、应用程序设计、编程、优化功能； 对系统的各个应用程序进行集成和调试，进一步优化系统功能、改善系统用 户界面完成实验内容所指定的各项要求；10. 分析遇到的问题，总结并写出课程设计报告；11. 自我评价】、用E-R图设计选定题目的信息模型令学生实体的E-R图令教师实体E-R图令课程实体E-R图令分组实体E-R图令教师任课实体E-R图三、设计相应的关系模型，确定数据库结构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)分析关系模式各属于第几范式，阐明理由;STUDENTSTEACHERSCOURSESENROLLSSECTION属于BCNF因为其中的每个决定因素都包含了码属于BCNF因为其中的每个决定因素都包含了码属于BCNF因为其中的每个决定因素都包含了码属于BCNF因为其中的每个决定因素都包含了码属于BCNF,因为其中的每个决定因素都包含了码设计关系的主码约束、外码

5、约束和使用 CHEC实现完整性控制;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);为参照关系设计插入、删除、修改触发器;实现应用程序设计、编程、优化功能;对系统的各个应用程序进行集成和调试，进一步优

8、化系统功能、改善系统用户界面完成实验内容所指定的各项要求;四、源程序代码清单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(); /检索数学系所有成绩大于 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检索每门课学生登记的人数、相应的课程名、课程号、分组号查询 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统计教师“ 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(); /统计各门课程的选课人数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;统计学生来自省的省名 /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"