数据库第三个大实验报告

1、广州大学学生实验报告开课实验室：计算机科学与工程实验 年 月 日学院计算机科学与网络工程学院年级、专业、班姓名学号实验课程名称数据库原理成绩实验项目名称数据库系统设计综合实验指导老师颜国风实验3数据库系统设计综合实验一、实验目的通过实验，使学生掌握数据库系统设计和开发的一般方法，能够设计并实现简单的数据库系统。二、实验要求熟悉实验室实验环境，掌握实验预备知识，了解实验中故障排除的基本方法。实验中根据实验内容和步骤完成相应的任务，并独立完成实验报告。实验素材可选择数据库原理实验七素材，也可以自行选题，选题要求有一定的难度，设计的数据库系统至少要有5张表。三、实验要求熟悉实验室实验环境，掌握实验预

2、备知识，了解实验中故障排除的基本方法。实验中根据实验内容和步骤完成相应的任务，并独立完成实验报告。四、实验内容和步骤假设有“教师”、“学生”、“课程”三个实体，教师的基本信息包括：工号、姓名、职称、工资，课程的基本信息包括：课程号、课程名、学分数，学生的基本信息包括：学号、姓名、性别、年龄。系统必须满足以下要求：(1) 一门课程只能有一个教师任课，一个教师可以上多门课程；(2) 一个学生可以选修多门课程，一门课程可以由多个学生来选修，记录不同学生选修不同课程的成绩；(3) 设置一个管理员，用于维护（添加、删除和修改等基本任务）学生基本信息、教师基本信息和教师所授课程等工作，此外，管理员添加学生

3、时，为其设置初始密码；当学生选修了某门课程，课程成绩由管理员录入；(4) 学生可以利用学号和密码登录系统，登陆系统后，可以进行选课、修改密码和个人基本信息、查询自己的选课及总学分等操作；(5) 能够统计不同职称的教师的数量、不同职称的教师的平均工资，可以统计每门课程的平均成绩、最高分、最低分，统计每个学生选修课程的总学分；根据上述描述，解答下列问题：(1) 设计并画出E-R 图，要求标注连通词（即联系类型）；(2) 将E-R 图转化为关系模型，并指出各关系的主码和外码；(3) 在MySql、SQL Server、Oracle 中选择一个数据库管理系统，并完成数据库的逻辑设计；五、实验源代码Or

4、acle部分：创建student表，course表，,sc表，teacher表:CREATE TABLE Student( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(3), Sage SMALLINT, Sdept CHAR(20);CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course(Cno);CREATE TAB

5、LE SC( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );CREATE TABLE SC( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) R

6、EFERENCES Course(Cno) );向以上四个表插入数据：insert into Student(sname,ssex,sno, sage, sdept) values(李勇,男,200215121,20,CS);insert into Student(sname,ssex,sno, sage, sdept) values(刘晨,女,200215122,19,CS);insert into Student(sname,ssex,sno, sage, sdept) values(王敏,女,200215123,18,MA);insert into Student(sname,ssex,

7、sno, sage, sdept) values(张立,男,200215125,19,IS); insert into course(cno,cname,cpno,ccredit) values(6,数据处理,null,2);insert into course(cno,cname,cpno,ccredit) values(2,数学,null,2);insert into course(cno,cname,cpno,ccredit) values(7,PASCAL 语言,6,4);insert into course(cno,cname,cpno,ccredit) values(5,数据结构,

8、7,4);insert into course(cno,cname,cpno,ccredit) values(1,数据库,5,4);insert into course(cno,cname,cpno,ccredit) values(3,信息系统,1,4);insert into course(cno,cname,cpno,ccredit) values(4,操作系统,6,3); insert into teacher(tno,tname,tsex,tsalary,tbirthday,dno) values(t001,张三,男,3000，To_date(7-7月-1997,DD-mon-yyyy

9、),d001);insert into teacher(tno,tname,tsex,tsalary,tbirthday,dno) values(t002,李四,女,3600，To_date(21-10月-1979,DD-mon-yyyy),d001);insert into teacher(tno,tname,tsex,tsalary,tbirthday,dno) values(t003,王五,女,5600，To_date(7-7月-1981,DD-mon-yyyy),d002);insert into teacher(tno,tname,tsex,tsalary,tbirthday,dno

10、) values(t004,刘晨,女,5800，To_date(7-7月-1985,DD-mon-yyyy),d002);insert into teacher(tno,tname,tsex,tsalary,tbirthday,dno) values(t005,王二小,男,3500，To_date(7-7月-1981,DD-mon-yyyy),d003);insert into teacher(tno,tname,tsex,tsalary,tbirthday,dno) values(t006,李小龙,男,5687，To_date(7-7月-1990,DD-mon-yyyy),d003);ins

11、ert into teacher(tno,tname,tsex,tsalary,tbirthday,dno) values(t007,熊猫,男,6000，To_date(7-7月-1980,DD-mon-yyyy),d003);insert into teacher(tno,tname,tsex,tsalary,tbirthday,dno) values(t008,李小小,女,5687，To_date(7-7月-1985,DD-mon-yyyy),d001);创建视图不同职称的教师的数量不同职称的教师的平均工资可以统计每门课程的平均成绩、最高分、最低分统计每个学生选修课程的总学分create

12、view dnosalary(dno,Avgsalary)asselect dno,AVG(tsalary)from teachergroup by dno;create view dnonum(dno,dnonum)asselect dno,count(dno)from teachergroup by dno;create view studentinfoasselect student.sno,student.sname,(select sum(grade) from sc where sno=student.sno) 总分,(select sum(ccredit)from course

13、where cno in (select cno from sc where sno=student.sno) 总学分from student;Create view grademessage(cno,Avggrade,maxgrade,mingrade)asselect cno,avg(grade),max(grade),min(grade)from scgroup by cno;Student表添加属性pwd，作为登录密码，初始化为“123456”。alter table student add pwd varchar2(20) default 123456;本次实验界面使用vs的窗体开发

14、进行，代码如下。主界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace 数据库设计 public partial class MAINFORM : Form public MAINFORM() Initializ

15、eComponent(); private void Form1_Load(object sender, EventArgs e) Frmlogin frm = new Frmlogin(); if(frm.ShowDialog(this)=DialogResult.OK) else Application.Exit(); private void 查询学生ToolStripMenuItem_Click(object sender, EventArgs e) FrmStuQuety frm = new FrmStuQuety(); frm.MdiParent = this; frm.Show(

16、); private void 添加学生ToolStripMenuItem_Click(object sender, EventArgs e) FrmStuInsert frm = new FrmStuInsert(); frm.MdiParent = this; frm.Show(); private void 查询课程ToolStripMenuItem_Click(object sender, EventArgs e) FrmCourseQuety frm = new FrmCourseQuety(); frm.MdiParent = this; frm.Show(); private v

17、oid 添加课程ToolStripMenuItem_Click(object sender, EventArgs e) FrmCourseInsert frm = new FrmCourseInsert(); frm.MdiParent = this; frm.Show(); private void 统计ToolStripMenuItem_Click(object sender, EventArgs e) FrmStastics frm = new FrmStastics(); frm.MdiParent = this; frm.Show(); private void 学生管理ToolSt

18、ripMenuItem_Click(object sender, EventArgs e) private void 查询教师ToolStripMenuItem_Click(object sender, EventArgs e) FrmTeacherQuety frm = new FrmTeacherQuety(); frm.MdiParent = this; frm.Show(); private void 添加教师ToolStripMenuItem_Click(object sender, EventArgs e) FrmTeacherInsert frm = new FrmTeacher

19、Insert(); frm.MdiParent = this; frm.Show(); 登录界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using Oracle.ManagedDataAccess.Client;names

20、pace 数据库设计 public partial class Frmlogin : Form public Frmlogin() InitializeComponent(); private void button3_Click(object sender, EventArgs e) private void button2_Click(object sender, EventArgs e) string sql = string.Format(select *from student where sno=0and pwd=1,this.tbSno.Text,this.tbpwd.Text)

21、; OracleConnection con = new OracleConnection(data source=:1521/orcl;User id=text7;password=ccpassword); try con.Open();/打开连接 OracleCommand cmd = new OracleCommand(sql, con); OracleDataReader odr = cmd.ExecuteReader(); if (odr.HasRows) MessageBox.Show(成功登录); this.DialogResult = DialogResult.OK; this

22、.Close(); else MessageBox.Show(用户名或密码错误); catch (Exception ex) MessageBox.Show(ex.Message); finally con.Close(); private void Frmlogin_Load(object sender, EventArgs e) 查询，更新，删除学生信息界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;u

23、sing System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using Oracle.ManagedDataAccess.Client;using System.Configuration;namespace 数据库设计 public partial class FrmStuQuety : Form public FrmStuQuety() InitializeComponent(); private void button1_Click(object sender, Ev

24、entArgs e) /创建连接对象，打开链接，创建命令对象，执行，关闭连接 string sql = string.Format(select sno,sname,ssex,sage,sdept from student where sname like 0%, this.textBox1.Text); OracleConnection con = new OracleConnection(data source=:1521/orcl;User id=text7;password=ccpassword); try con.Open();/打开连接 OracleCommand cmd = ne

25、w OracleCommand(sql, con); OracleDataReader odr = cmd.ExecuteReader(); if (odr.HasRows) BindingSource bs = new BindingSource(); bs.DataSource = odr; this.dataGridView1.DataSource = bs; else this.dataGridView1.DataSource = null; catch(Exception ex) MessageBox.Show(ex.Message); finally con.Close(); pr

26、ivate void label1_Click(object sender, EventArgs e) private void button2_Click(object sender, EventArgs e) if(this.dataGridView1.SelectedRows.Count=0) MessageBox.Show(请选中再删除); return; DataGridViewRow row = this.dataGridView1.SelectedRows0; string sql = string.Format(delete from student where sno =0,

27、 row.Cells0.Value.ToString(); OracleConnection con = new OracleConnection(data source=:1521/orcl;User id=text7;password=ccpassword); try con.Open();/打开连接 OracleCommand cmd = new OracleCommand(sql, con); OracleDataReader odr = cmd.ExecuteReader(); if (cmd.ExecuteNonQuery() = 1) MessageBox.Show(删除成功);

28、 this.dataGridView1.Rows.Remove(row); else MessageBox.Show(没有找到学生); catch (Exception ex) MessageBox.Show(ex.Message); finally con.Close(); private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) private void dataGridView1_CellDoubleClick(object sender, DataGridViewCel

29、lEventArgs e) if (this.dataGridView1.SelectedRows.Count = 0) return; DataGridViewRow row = this.dataGridView1.SelectedRows0; FrmStuUpdate frm = new FrmStuUpdate(); frm.tbSno.Text = row.Cells0.Value.ToString(); frm.tbSname.Text = row.Cells1.Value.ToString(); frm.tbSsex.Text = row.Cells2.Value.ToStrin

30、g(); frm.tbSage.Text = row.Cells3.Value.ToString(); frm.tbSdept.Text = row.Cells4.Value.ToString(); frm.ShowDialog(this); private void FrmStuQuety_Load(object sender, EventArgs e) 添加学生信息界面：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawin

31、g;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using Oracle.ManagedDataAccess.Client;namespace 数据库设计 public partial class FrmStuInsert : Form public FrmStuInsert() InitializeComponent(); private void button1_Click(object sender, EventArgs e) string sql

32、= string.Format(insert into student(sno,sname,ssex,sage,sdept) values(0,1,2,3,4), tbSno.Text, tbSname.Text, tbSsex.Text, tbSage.Text, tbSdept.Text); OracleConnection con = new OracleConnection(data source = :1521 / orcl; User id = text7; password = ccpassword); try con.Open();/打开链接 OracleCommand cmd

33、 = new OracleCommand(sql, con);/构造命令 if (cmd.ExecuteNonQuery() = 1) MessageBox.Show(插入成功); catch (Exception ex) MessageBox.Show(ex.Message); finally con.Close(); private void FrmStuInsert_Load(object sender, EventArgs e) 课程类：using System;using System.Collections.Generic;using System.Linq;using Syste

34、m.Text;using System.Threading.Tasks;using Oracle.ManagedDataAccess.Client;namespace 数据库设计 public class Course public string Cno get; set; public string Cname get; set; public string Cpno get; set; public int Ccredit get; set; public static List SelectCourse(string cname) string sql = Select cno,cnam

35、e,cpno,ccredit from course where cname like :cname; OracleParameter para = new OracleParameter new OracleParameter(:cname, OracleDbType.Char, 4) ; para0.Value = cname + %; List list = new List(); OracleConnection con=new OracleConnection(data source = :1521 / orcl; User id = text7; password = ccpass

36、word); try con.Open(); OracleCommand cmd = new OracleCommand(sql, con); cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while(odr.Read() Course c = new Course(); c.Cno = odr.GetString(0); c.Cname = odr.GetString(1); if (odr.IsDBNull(2) c.Cpno = null; else c.Cpno = odr.GetS

37、tring(2); c.Ccredit = odr.GetInt32(3); list.Add(c); catch(Exception ex) finally con.Close(); return list; public static int DeleteCourse(String cno) int result = 0; string sql = delete from course where cno=:cno; OracleParameter para = new OracleParameter new OracleParameter(:cno, OracleDbType.Char,

38、4) ; para0.Value = cno; OracleConnection con = new OracleConnection(data source = :1521 / orcl; User id = text7; password = ccpassword); try con.Open(); OracleCommand cmd = new OracleCommand(sql, con); cmd.Parameters.AddRange(para); result = cmd.ExecuteNonQuery(); catch (Exception ex) finally con.Cl

39、ose(); return result; public static int InsertCourse(Course c) int result = 0; string sql = insert into course(cno,cname,cpno,ccredit) values(:cno,:cname,:cpno,:ccredit); OracleParameter para = new OracleParameter new OracleParameter(:cno, OracleDbType.Char, 4), new OracleParameter(:cname,OracleDbTy

40、pe.Char,40),new OracleParameter(:cpno,OracleDbType.Char,4), new OracleParameter(:ccredit, OracleDbType.Int32); para0.Value = c.Cno; para1.Value = c.Cname; para2.Value = c.Cpno; para3.Value = c.Ccredit; OracleConnection con = new OracleConnection(data source = :1521 / orcl; User id = text7; password

41、= ccpassword); try con.Open(); OracleCommand cmd = new OracleCommand(sql, con); cmd.Parameters.AddRange(para); result = cmd.ExecuteNonQuery(); catch (Exception ex) finally con.Close(); return result; public static int UpdataCourse(Course c) int result = 0; string sql = update course set cname=:cname

42、,cpno=:cpno,ccredit=:ccredit where cno=:cno; OracleParameter para = new OracleParameter new OracleParameter(:cname,OracleDbType.Char,40),new OracleParameter(:cpno,OracleDbType.Char,4), new OracleParameter(:ccredit, OracleDbType.Int32),new OracleParameter(:cno, OracleDbType.Char, 4); para0.Value = c.

43、Cname; para1.Value = c.Cpno; para2.Value = c.Ccredit; para3.Value = c.Cno; OracleConnection con = new OracleConnection(data source = :1521 / orcl; User id = text7; password = ccpassword); try con.Open(); OracleCommand cmd = new OracleCommand(sql, con); cmd.Parameters.AddRange(para); result = cmd.Exe

44、cuteNonQuery(); catch (Exception ex) finally con.Close(); return result; 课程查询，更新，删除界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namesp

45、ace 数据库设计 public partial class FrmCourseQuety : Form public FrmCourseQuety() InitializeComponent(); private void button4_Click(object sender, EventArgs e) this.dataGridView1.DataSource=Course.SelectCourse(this.textBox2.Text); private void dataGridView1_CellClick(object sender, DataGridViewCellEventA

46、rgs e) if (this.dataGridView1.Rows.Count = 0) return; if (e.RowIndex 0) return; DataGridViewRow row = this.dataGridView1.Rowse.RowIndex; if(e.ColumnIndex=4) /点击删除按钮 string cno = row.Cells0.Value.ToString(); if (Course.DeleteCourse(cno)=1) MessageBox.Show(删除成功); / this.dataGridView1.Rows.Remove(row);

47、 else MessageBox.Show(没有找到数据); else if(e.ColumnIndex=5) /点击修改按钮 Course c = (Course)row.DataBoundItem; FrmCourseUpdata frm = new FrmCourseUpdata(); frm.tbCno.Text = c.Cno; frm.tbCname.Text = c.Cname; frm.tbCpno.Text = c.Cpno; frm.tbCcredit.Text = c.Ccredit.ToString(); frm.ShowDialog(); private void d

48、ataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) private void FrmCourseQuety_Load(object sender, EventArgs e) 课程添加界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using

49、System.Threading.Tasks;using System.Windows.Forms;namespace 数据库设计 public partial class FrmCourseInsert : Form public FrmCourseInsert() InitializeComponent(); private void button1_Click(object sender, EventArgs e) Course c = new Course(); c.Cno = tbCno.Text; c.Cname = tbCname.Text; c.Cpno = tbCpno.Te

50、xt; c.Ccredit = Convert.ToInt32(tbCcredit.Text); /将课程插入数据库 if (Course.InsertCourse(c) = 1) MessageBox.Show(插入成功); else MessageBox.Show(未成功插入); private void label1_Click(object sender, EventArgs e) private void FrmCourseInsert_Load(object sender, EventArgs e) private void label2_Click(object sender,

51、EventArgs e) private void tbCname_TextChanged(object sender, EventArgs e) private void label3_Click(object sender, EventArgs e) private void tbCpno_TextChanged(object sender, EventArgs e) private void label4_Click(object sender, EventArgs e) private void tbCcredit_TextChanged(object sender, EventArg

52、s e) private void tbCno_TextChanged(object sender, EventArgs e) 课程更新界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace 数据库设计 publi

53、c partial class FrmCourseUpdata : Form public FrmCourseUpdata() InitializeComponent(); private void FrmCourseUpdata_Load(object sender, EventArgs e) private void button1_Click(object sender, EventArgs e) Course c = new Course(); c.Cno = tbCno.Text; c.Cname = tbCname.Text; c.Cpno = tbCpno.Text; c.Ccr

54、edit = Convert.ToInt32(tbCcredit.Text); /将课程插入数据库 if (Course.UpdataCourse(c) = 1) MessageBox.Show(更新成功); else MessageBox.Show(未找到记录); 教师查询界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using S

55、ystem.Threading.Tasks;using System.Windows.Forms;using Oracle.ManagedDataAccess.Client;namespace 数据库设计 public partial class FrmTeacherQuety : Form public FrmTeacherQuety() InitializeComponent(); private void label2_Click(object sender, EventArgs e) private void button1_Click(object sender, EventArgs

56、 e) string sql = string.Format(select tno,tname,dno,tsex,tsalary from teacher where tname like 0%, this.textBox1.Text); OracleConnection con = new OracleConnection(data source=:1521/orcl;User id=text7;password=ccpassword); try con.Open();/打开连接 OracleCommand cmd = new OracleCommand(sql, con); OracleD

57、ataReader odr = cmd.ExecuteReader(); if (odr.HasRows) BindingSource bs = new BindingSource(); bs.DataSource = odr; this.dataGridView1.DataSource = bs; else this.dataGridView1.DataSource = null; catch (Exception ex) MessageBox.Show(ex.Message); finally con.Close(); private void button2_Click(object s

58、ender, EventArgs e) private void FrmTeacherQuety_Load(object sender, EventArgs e) 添加教师信息界面：代码：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;u

59、sing Oracle.ManagedDataAccess.Client;namespace 数据库设计 public partial class FrmTeacherInsert : Form public FrmTeacherInsert() InitializeComponent(); private void label4_Click(object sender, EventArgs e) private void button1_Click(object sender, EventArgs e) string sql = string.Format(insert into teach

60、er(tno,tname,dno,tsex,tsalary,tbirthday) values(0,1,2,3,4,5), tbtno.Text, tbtname.Text, tbdno.Text,tbtsex.Text, tbtsalary.Text, tbtsalary.Text); OracleConnection con = new OracleConnection(data source = :1521 / orcl; User id = text7; password = ccpassword); try con.Open();/打开链接 OracleCommand cmd = n