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

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

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

3、关系设计插入、删除、修改触发器； 8. 实现应用程序设计、编程、优化功能； 9. 对系统的各个应用程序进行集成和调试，进一步优化系统功能、改善系统用 户界面完成实验内容所指定的各项要求； 10. 分析遇到的问题，总结并写出课程设计报告； 11. 自我评价 1、用E-R图设计选定题目的信息模型 分组实体E-R图 教师任课实体E-R图 三、设计相应的关系模型，确定数据库结构 STUDENTSstuden t,stude nt_n ame,address,zip,city,state,sex) TEACHERSacher,teacher name,phone,salary) COURSESurse,

4、course name,department,nurc_credits) SECTION(sectio n, teacher,course, num _stude nts) ENROLLScourse,sectio n,stude nt,grade) *分析关系模式各属于第几范式，阐明理由； STUDENTS属于BCNF因为其中的每个决定因素都包含了码 TEACHERS属于BCNF因为其中的每个决定因素都包含了码 COURSES属于BCNF因为其中的每个决定因素都包含了码 ENROLLS 属于BCNF因为其中的每个决定因素都包含了码 SECTION 属于BCNF,因为其中的每个决定因素都包含了

5、码 *设计关系的主码约束、外码约束和使用CHEC实现完整性控制; STUDENT信息基本表 create table STUDENTS( stude nt char(8) primary 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) primary key, teacher_ name char(10), phone char(1

6、0), salary char(8); COURSE基本表 create table COURSES( course char(8) primary key, course_ name char(20), departme nt char(20), nu rc_credits char(4); SECTION表 create table SECTION( sect ion char(4), teacher char(8) primary key, course char(8), nu m_stude nts char(4), foreig n key (course) references C

7、OURSES(course); ENROLLS 表 create table ENROLLS( course char(8), secti on char(4), stude nt char(8), grade SMALLINT, primary 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.ComponentModel; using System.Data; using System.Drawing; using Syste m. Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsFo

9、rmsApplication2 public partial class Form1 : Form public Form1() lnitializeComponent(); private void学生 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;lnitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); c

10、onn.Open(); SqlDataAdapter sdr1 = new SqlDataAdapter(select* from STUDENTS, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, STUDENTS); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /学生基本信息 private void教U帀 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Sourc

11、e=qinjia-PC;InitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdrl = new SqlDataAdapter(select* from TEACHERS, conn); Catalog=sjk;Integrated Catalog=sjk;Integrated DataSet ds1 = new DataSet(); sdr1.Fill(ds1, TEACHERS); dat

12、aGridView1.DataSource = ds1.Tables0; conn.Close(); /教师基本信息 private void课程 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;lnitial Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdr1 = new SqlDataAdapte

13、r(select* from COURSES, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, COURSES); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /课程基本信息 private void分组 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;Initial Security=True; SqlConnection conn

14、= new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdr1 = new SqlDataAdapter(select* from SECTION, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, SECTION); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /分组基本信息 private void登记，ToolStripMenultem_Click(object sender, EventArgs e

15、) stringconnectionStr = Data Source=qinjia-PC;Initial Catalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdrl = new SqlDataAdapter(select* from ENROLLS, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, ENROLLS); dataGridView1.Da

16、taSource = ds1.Tables0; conn.Close(); /登记基本信息 private void查询 1 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;lnitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdaptersdr1 = new Sql

17、DataAdapter(select* from COURSESwhere department IN(Math,English), conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, COURSES); dataGridView1.DataSource = ds1.Tables0; conn.Close(); / 检索系名为Math ”和English ” 的课程表信息 private void查询 2 ToolStripMenuItem_Click(object sender, EventArgs e) stringconnectionSt

18、r = Data Source=qinjia-PC;InitialCatalog=sjk;lntegrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdrl =new SqlDataAdapter(select teacher_name,phone from TEACHERS order by teacher_name, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, TEACHER

19、S); dataGridViewl.DataSource = ds1.Tables0; conn.Close(); /按字母顺序列出教师姓名和电话号码 private void查询 3 ToolStripMenuItem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;InitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open()

20、; SqlDataAdaptersdrl = new SqlDataAdapter(select teacher_name,phone from TEACHERWhere phone not like 257%, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, TEACHERS); dataGridViewl.DataSource = ds1.Tables0; conn.Close(); /检索电话号码不是以“257 ”打头的教师姓名和电话号码 private void查询 4 ToolStripMenultem_Click(object

21、sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;lnitialCatalog=sjk;lntegrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdrl = new SqlDataAdapter(select course_name,department,nurc_credits from COURSES where department=Math and

22、 nurc_credits3, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, COURSES); dataGridViewl.DataSource = ds1.Tables0; conn.Close(); /检索数学系所有成绩大于3的课程名、系名、学分 private void查询 5 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;InitialCatalog=sjk;lntegrated Se

23、curity=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdrl = new SqlDataAdapter(select student_name,student from STUDENTS where not exists (select* from ENROLLS where STUDENTS.student=ENROLLS.student), conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, STUDE

24、NTS,ENROLLS); dataGridViewl.DataSource = ds1.Tables0; conn.Close(); /检索没有选修任何课的学生姓名、学号 private void查询 6 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;lnitialCatalog=sjk;lntegrated Security=True; SqlConnection conn = new SqlConnection(connectionStr);

25、conn.Open(); SqlDataAdaptersdrl = new SqlDataAdapter(select student from STUDENTWhere not exists (select*fromENROLLS,COURSES where STUDENTS.student=ENROLLS.student and COURSES.course=ENROLLS.course and course_name=Calculus lv), conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, STUDENTS,ENROLLS,COUR

26、SES); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /检索没有选修课程“ Calculus lv”的学生学号 private void查询 7 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;InitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr)

27、; conn.Open(); SqlDataAdapter sdr1 = new SqlDataAdapter(select distinct student from ENROLLS A where not exists (select* from TEACHERwhere teacher_name=Dr.Lowe and not exists (select * from ENROLLS B where B.course=A.course), conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, TEACHERS,ENROLLS); data

28、GridView1.DataSource = ds1.Tables0; conn.Close(); /检索至少选修教师“ Dr. Lowe ”所开全部课程的学生学号 private void查询 8 ToolStripMenultem_Click(object sender, EventArgs e) Catalog=sjk;Integrated SqlDataAdapter(select SECTION,COURSES where stringconnectionStr = Data Source=qinjia-PC;Initial Security=True; SqlConnection

29、conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdaptersdr1=new COURSES.course,count(num_students),COURSES.course_namefrom COURSES.course=SECTION.course group by COURSES.course_name,COURSES.course , conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, COURSES,SECTION); dataGridView1.DataS

30、ource = ds1.Tables0; conn.Close(); /检索每门课学生登记的人数、相应的课程名、课程号、分组号 private void查询 9 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;lnitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAda

31、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(ds1, STUDENTS,ENROLLS); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /检索选修两门以上课程的学生姓名 private void查询

32、 10 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;InitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdaptersdr1 = new SqlDataAdapter(select distinct course,student_name from STUDEN

33、TS,ENROLLS where ENROLLS.student=STUDENTS.student and sex=M, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, STUDENTS,ENROLLS); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /检索只有男生选修的课程和学生名 private void查询 11 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data S

34、ource=qinjia-PC;InitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdaptersdr1=newSqlDataAdapter(select STUDENTS.student_name,COURSES.course_name,TEACHERS.teacher_name,ENROLLS.gradefrom COURSES,STUDENTS,TEACHERS,ENROLLS,SECTIONvher

35、eSTUDENTS.student=ENROLLS.student and ENROLLS.course=COURSES.courseandSECTION.teacher=TEACHERS.teacherand SECTION.course=COURSES.course, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, STUDENTS,ENROLLS,TEACHERS,COURSES,SECTION); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /检索所有学生选修的课程名、

36、学生名、授课教师名、该生成绩 private void查询 12 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;lnitialCatalog=sjk;lntegrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdaptersdrl = new SqlDataAdapter(selectavg(grade) f

37、rom COURSES,ENROLLS,TEACHERS,SECTION whereCOURSES.course=ENROLLS.courseand TEACHERS.teacher=SECTION.teacher and course_name=english composition and teacher_name like %Engle, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, ENROLLS,TEACHERS,COURSES,SECTION); dataGridViewl.DataSource = ds1.Tables0;

38、conn.Close(); /统计教师“ Engle ”教的英语课的学生平均分 private void查询 13 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;InitialCatalog=sjk;Integrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdrl = new SqlData

39、Adapter(select course_name,count(num_students) from COURSES,SECTION where COURSES.course=SECTION.course group by course_name, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, COURSES,SECTION); dataGridViewl.DataSource = ds1.Tables0; conn.Close(); /统计各门课程的选课人数 private void查询 14 ToolStripMenultem_Cl

40、ick(object sender, EventArgs e) connectionStr Data Source=qinjia-PC;lnitial Catalog=sjk;lntegrated string Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDataAdapter sdr1 = new SqlDataAdapter(select distinct state from STUDENTS, conn); DataSet ds1 = new DataSet(

41、); sdr1.Fill(ds1, STUDENTS); dataGridView1.DataSource = ds1.Tables0; conn.Close(); /统计学生来自省的省名 private void查询 15 ToolStripMenultem1_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;InitialCatalog=sjk;lntegrated Security=True; SqlConnection conn = new SqlConnection(connec

42、tionStr); conn.Open(); SqlDataAdaptersdrl=newSqlDataAdapter(select student_name,COURSES.course_name,teacher_name,gradefrom STUDENTS,TEACHERS,COURSES,SECTION,ENROLLS whereTEACHERS.teacher=SECTION.teacher and SECTION.course=ENROLLS.courseandSTUDENTS.student=ENROLLS.studentand COURSES.course=ENROLLS.co

43、urse;, conn); DataSet ds1 = new DataSet(); sdr1.Fill(ds1, STUDENTS,ENROLLS,TEACHERS,COURSES,SECTION); dataGridViewl.DataSource = ds1.Tables0; conn.Close(); /输出如下报表： 学生名 课程名 教师名 成绩 private void修改 16 ToolStripMenultem_Click(object sender, EventArgs e) stringconnectionStr = Data Source=qinjia-PC;InitialCatalog=sjk;lntegrated Security=True; SqlConnection conn = new SqlConnection(connectionStr); conn.Open(); SqlDat