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

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

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

3、關系設計插入、刪除、修改觸發(fā)器； 8. 實現(xiàn)應用程序設計、編程、優(yōu)化功能； 9. 對系統(tǒng)的各個應用程序進行集成和調(diào)試，進一步優(yōu)化系統(tǒng)功能、改善系統(tǒng)用 戶界面完成實驗內(nèi)容所指定的各項要求； 10. 分析遇到的問題，總結(jié)并寫出課程設計報告； 11. 自我評價 1、用E-R圖設計選定題目的信息模型 分組實體E-R圖 教師任課實體E-R圖 三、設計相應的關系模型，確定數(shù)據(jù)庫結(jié)構(gòu) 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實現(xiàn)完整性控制; 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); -為參照關系設計插入、刪除、修改觸發(fā)器； -實現(xiàn)應用程序設計、編程、優(yōu)化功能

8、； -對系統(tǒng)的各個應用程序進行集成和調(diào)試，進一步優(yōu)化系統(tǒng)功能、 改善系統(tǒng)用戶界面完成實驗內(nèi)容所指定的各項要求； 四、源程序代碼清單 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(); /檢索數(shù)學系所有成績大于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(); /檢索每門課學生登記的人數(shù)、相應的課程名、課程號、分組號 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(); /統(tǒng)計教師“ 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(); /統(tǒng)計各門課程的選課人數(shù) 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(); /統(tǒng)計學生來自省的省名 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