数据库系统设计综合实验

一、实验目的

通过实验，使学生掌握数据库系统设计和开发的一般方法，能够设计并实现简单的数据库系统。

二、实验原理

本实验的任务是设计并实现一个数据库系统。数据库设计的一般步骤包括：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。

(1) 概念结构设计

了解概念结构设计的基本方法，根据需求分析的结果或实验题目给出的要求，能够准确地用实体联系图来描述实体和实体之间的联系。

(2) 逻辑结构设计

理解逻辑结构设计的基本方法，根据实体联系图的设计，转换成合理的关系模式，每个关系模式至少应该满足第三范式的要求。

(3) 物理结构设计

理解物理结构设计的基本方法，选择合理的索引结构和存储结构，优化数据库的存取。

(4) 数据库实施

选择一门熟悉的面向对象程序设计语言，完成应用程序的开发。

三、使用仪器、材料

Oracle 19c，windows10，visual studio

四、实验步骤

假设有“教师”、“学生”、“课程”三个实体，教师的基本信息包括：工号、姓名、职称、工资，课程的基本信息包括：课程号、课程名、学分数，学生的基本信息包括：学号、姓名、性别、年龄。系统必须满足以下要求：

(1) 一门课程只能有一个教师任课，一个教师可以上多门课程；

(2) 一个学生可以选修多门课程，一门课程可以由多个学生来选修，记录不同学生选修不同课程的成绩；

(3) 设置一个管理员，用于维护（添加、删除和修改等基本任务）学生基本信息、教师基本信息和教师所授课程等工作，此外，管理员添加学生时，为其设置初始密码；当学生选修了某门课程，课程成绩由管理员录入；

(4) 学生可以利用学号和密码登录系统，登陆系统后，可以进行选课、修改密码和个人基本信息、查询自己的选课及总学分等操作；

(5) 能够统计不同职称的教师的数量、不同职称的教师的平均工资，可以统计每门课程的平均成绩、最高分、最低分，统计每个学生选修课程的总学分；

根据上述描述，解答下列问题：

(1) 设计并画出E-R 图，要求标注连通词（即联系类型）；

(2) 将E-R 图转化为关系模型，并指出各关系的主码和外码；

(3) 在MySql、SQL Server、Oracle 中选择一个数据库管理系统，并完成数据库的逻辑设计；

五、实验过程原始记录(实验过程、数据、图表、计算等)

(1) 设计并画出E-R 图，要求标注连通词（即联系类型）；

(2) 将E-R 图转化为关系模型，并指出各关系的主码和外码；

teacher(tno,tname,tposition,tsalary,pwd),其中，tno为teacher表的主码

course(cno,cname,ccredit,tno),其中，cno为course表的主码

student(sno,sname,ssex,sage,pwd),其中，sno为student表的主码

sc(sno,con,grade),其中，(sno,cno)为sc表的主码，sno为外码，参照student(sno),cno为外码，参照course(cno)

(3)选择Oracle数据库管理系统，完成数据库的逻辑设计；

1、创建用户bb，密码为lpw12345create user bb identified by lpw12345;Grant connect ,resource,unlimited tablespace to bb;Grant create view to bb;2、在用户bb下创建表student，teacher，course，sc，视图studentinfo，courseinfo，teacherinfo1，teacherinfo2create table student(sno varchar2(10) primary key,sname varchar2(20) not null unique,ssex char(3) check(ssex in('男','女')),sage smallint not null,pwd varchar(20) not null);create table teacher(tno varchar(7) primary key,tname varchar(20) not null unique,tposition varchar(20) not null,tsalary smallint not null,pwd varchar(20) not null);create table course(cno varchar(4) primary key,cname varchar(40) not null unique,ccredit smallint not null,tno varchar(7) not null,foreign key (tno) references teacher(tno));create table sc(sno varchar(10) not null,cno varchar(4) not null,grade smallint not null,primary key(sno,cno),foreign key (sno) references student(sno),foreign key (cno) references course(cno));insert into student(sname,ssex,sno, sage, pwd) values('李天明','男','1906100067',21,'123456');insert into student(sname,ssex,sno, sage, pwd) values('陈小红','女','1906100068',19,'123456');insert into student(sname,ssex,sno, sage, pwd) values('王明白','女','1906100069',18,'123456');insert into student(sname,ssex,sno, sage, pwd) values('张小黑','男','1906100070',19,'123456');insert into student(sname,ssex,sno, sage, pwd) values('林与','男','1906100071',21,'123456');insert into teacher(tno,tname, tposition, tsalary, pwd) values('t001','李杰出','教授',8000,'123456');insert into teacher(tno,tname, tposition, tsalary, pwd) values('t002','郑好','专任教师',5000,'123456');insert into teacher(tno,tname, tposition, tsalary, pwd) values('t003','刘光明','教授',7000,'123456');insert into teacher(tno,tname, tposition, tsalary, pwd) values('t004','何小兵','副教授',6000,'123456');insert into teacher(tno,tname, tposition, tsalary, pwd) values('t005','黄小白','副教授',6500,'123456');insert into teacher(tno,tname, tposition, tsalary, pwd) values('t006','林峰地','教授',7500,'123456');insert into course values('6','高等数学',6,'t003');insert into course values('2','计算机组成原理',3,'t004');insert into course values('7','java语言',4,'t003');insert into course values('5','操作系统',3,'t005');insert into course values('1','数据库',2,'t001');insert into course values('3','计算机网络',4,'t002');insert into course values('4','数据结构',5,'t002');insert into sc values('1906100067','1',92);insert into sc values('1906100068','2',85);insert into sc values('1906100069','4',88);insert into sc values('1906100070','2',90);insert into sc values('1906100071','5',80);create view courseinfo as select o 课程编号,ame 课程名称,teacher.tname 任课老师,redit 课程学分,countnum 选修人数,avg_grade 平均分,max_grade 最高分,min_grade 最低分 from teacher,course left outer join (select cname, count(*) countnum,avg(grade) avg_grade,max(grade) max_grade,min(grade) min_grade from sc,course where o=o group by cname)a1 on (ame=ame) where teacher.tno=course.tno;create view studentinfo as select student.sno 学号,student.sname 姓名,(select sum(grade) from sc where sno=student.sno) 总分 ,(select sum(ccredit) from course where cno in (select cno from sc where sno=student.sno)) 总学分 ,(select max(grade) from sc where sc.sno in (student.sno)) 最高分,(select min(grade) from sc where sc.sno in (student.sno)) 最低分 from student;create view teacherinfo1 as select tposition 职位,count(tno) 在任人数,avg(tsalary) 平均工资 from teacher group by tposition;create view teacherinfo2 as select tno 教师工号,tname 教师名称,(select count(student.sno) from student,sc,course where student.sno=sc.sno and o=o and course.tno=teacher.tno) 授课学生总人数 from teacher;Commit;

（4）选择C#语言，完成系统开发

学生类student.csusing Oracle.ManagedDataAccess.Client;using System;using System.Collections.Generic;using System.Configuration;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace byd001_app{class student{public string sno {get; set; }public string sname {get; set; }public string ssex {get; set; }public int sage {get; set; }public string pwd {get; set; }public static List<student> Selectstudent(string sname){string sql = "select sno,sname,ssex,sage,pwd from student where sname like :sname";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sname", OracleDbType.Char, 20) };para[0].Value = sname + "%";//MessageBox.Show(tname+"%");List<student> list = new List<student>();//创建链接，打开连接，创建命令对象，执行命令，关闭连接OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();while (odr.Read()){student s = new student();//o = odr.GetString(0); //ord["cno'].s.sno = odr["sno"].ToString();//t.tno = odr.GetString(0);s.sname = odr.GetString(1);s.ssex= odr.GetString(2);s.sage = odr.GetInt32(3);if (common.ID == "0")s.pwd = odr.GetString(4);elses.pwd = "******";list.Add(s);}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return list;}public static int Deletestudent(string sno){int result = 0;string sql = "delete from student where sno=:sno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno", OracleDbType.Char, 10) };para[0].Value = sno;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Insertstudent(student s){int result = 0;string sql = "insert into student(sno,sname,ssex,sage,pwd) values(:sno,:sname,:ssex,:sage,:pwd)";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno",OracleDbType.Char,10),new OracleParameter(":sname",OracleDbType.Char,20),new OracleParameter(":ssex",OracleDbType.Char,3),new OracleParameter(":sage",OracleDbType.Int32),new OracleParameter(":pwd",OracleDbType.Char,20)};para[0].Value = s.sno;para[1].Value = s.sname;para[2].Value = s.ssex;para[3].Value = s.sage;para[4].Value = s.pwd;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Updatestudent(student s){int result = 0;string sql = "update student set sname=:sname,ssex=:ssex,sage=:sage,pwd=:pwd where sno=:sno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sname",OracleDbType.Char,20),new OracleParameter(":ssex",OracleDbType.Char,20),new OracleParameter(":sage",OracleDbType.Int32),new OracleParameter(":pwd",OracleDbType.Char,20),new OracleParameter(":sno",OracleDbType.Char,10)};para[0].Value = s.sname;para[1].Value = s.ssex;para[2].Value = s.sage;para[3].Value = s.pwd;para[4].Value = s.sno;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}}}课程类course.csusing Oracle.ManagedDataAccess.Client;using System;using System.Collections.Generic;using System.Configuration;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace byd001_app{class course{public string cno {get; set; }public string cname {get; set; }public int ccredit {get; set; }public string tno {get; set; }public static List<course> Selectcourse(string cname){string sql = "select cno,cname,ccredit,tno from course where cname like :cname";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":cname", OracleDbType.Char, 40) };para[0].Value = cname + "%";List<course> list = new List<course>();//创建链接，打开连接，创建命令对象，执行命令，关闭连接OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();while (odr.Read()){course c = new course();//o = odr.GetString(0); //ord["cno'].o = odr["cno"].ToString();ame = odr.GetString(1);//c.Cpno = odr.GetString(2);redit = odr.GetInt32(2);c.tno = odr.GetString(3);list.Add(c);}}catch (Exception ex){MessageBox.Show(ex.Message);}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, 4) };para[0].Value = cno;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Insertcourse(course c){int result = 0;string sql = "insert into course(cno,cname,ccredit,tno) values(:cno,:cname,:ccredit,:tno)";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":cno",OracleDbType.Char,4),new OracleParameter(":cname",OracleDbType.Char,40),new OracleParameter(":ccredit",OracleDbType.Int32),new OracleParameter(":tno",OracleDbType.Char,7)};para[0].Value = o;para[1].Value = ame;para[2].Value = redit;para[3].Value = c.tno;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Updatecourse(course c){int result = 0;string sql = "update course set cname=:cname,ccredit=:ccredit,tno=:tno where cno=:cno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":cname",OracleDbType.Char,40),new OracleParameter(":ccredit",OracleDbType.Int32),new OracleParameter(":tno",OracleDbType.Char,7),new OracleParameter(":cno",OracleDbType.Char,4)};para[0].Value = ame;para[1].Value = redit;para[2].Value = c.tno;para[3].Value = o;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}}}教师类teacher.csusing System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Configuration;using Oracle.ManagedDataAccess.Client;using System.Windows.Forms;namespace byd001_app{class teacher{public string tno {get; set; }public string tname {get; set; }public string tposition {get; set; }public int tsalary {get; set; }public string pwd {get; set; }public static List<teacher> Selectteacher(string tname){string sql = "select tno,tname,tposition,tsalary,pwd from teacher where tname like :tname";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":tname",OracleDbType.Char, 20) };para[0].Value = tname + "%";//MessageBox.Show(tname+"%");List<teacher> list = new List<teacher>();//创建链接，打开连接，创建命令对象，执行命令，关闭连接OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();while (odr.Read()){teacher t = new teacher();//o = odr.GetString(0); //ord["cno'].t.tno = odr["tno"].ToString();//t.tno = odr.GetString(0);t.tname = odr.GetString(1);t.tposition = odr.GetString(2);t.tsalary = odr.GetInt32(3);if (common.ID == "0")t.pwd = odr.GetString(4);elset.pwd = "******";list.Add(t);}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return list;}public static int Deleteteacher(string tno){int result = 0;string sql = "delete from teacher where tno=:tno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":tno", OracleDbType.Char, 7) };para[0].Value = tno;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Insertteacher(teacher t){int result = 0;string sql = "insert into teacher(tno,tname,tposition,tsalary,pwd) values(:tno,:tname,:tposition,:tsalary,:pwd)";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":tno",OracleDbType.Char,7),new OracleParameter(":tname",OracleDbType.Char,20),new OracleParameter(":tposiition",OracleDbType.Char,20),new OracleParameter(":tsalary",OracleDbType.Int32),new OracleParameter(":pwd",OracleDbType.Char,20)};para[0].Value = t.tno;para[1].Value = t.tname;para[2].Value = t.tposition;para[3].Value = t.tsalary;para[4].Value = t.pwd;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Updateteacher(teacher t){int result = 0;string sql = "update teacher set tname=:tname,tposition=:tposition,tsalary=:tsalary,pwd=:pwd where tno=:tno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":tname",OracleDbType.Char,20),new OracleParameter(":tposition",OracleDbType.Char,20),new OracleParameter(":tsalary",OracleDbType.Int32),new OracleParameter(":pwd",OracleDbType.Char,20),new OracleParameter(":tno",OracleDbType.Char,7)};para[0].Value = t.tname;para[1].Value = t.tposition;para[2].Value = t.tsalary;para[3].Value = t.pwd;para[4].Value = t.tno;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}}}sc类sc.csusing Oracle.ManagedDataAccess.Client;using System;using System.Collections.Generic;using System.Configuration;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace byd001_app{class sc{public string sno {get; set; }public string cno {get; set; }public int grade {get; set; }public static List<sc> Selectsc(string sno){string sql = "select sc.sno,cno,grade from sc,student where student.sno=sc.sno and student.sno like :sno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno", OracleDbType.Char, 10)};para[0].Value = sno + "%";List<sc> list = new List<sc>();//创建链接，打开连接，创建命令对象，执行命令，关闭连接OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();while (odr.Read()){sc s_c = new sc();//o = odr.GetString(0); //ord["cno'].s_c.sno = odr["sno"].ToString();o = odr.GetString(1);//c.Cpno = odr.GetString(2);s_c.grade = odr.GetInt32(2);list.Add(s_c);}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return list;}public static int Deletesc(string sno, string cno){int result = 0;string sql = "delete from sc where sno=:sno and cno=:cno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno", OracleDbType.Char, 10),new OracleParameter(":cno", OracleDbType.Char, 4)};para[0].Value = sno;para[1].Value = cno;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Insertsc(sc s_c){int result = 0;string sql = "insert into sc(sno,cno,grade) values(:sno,:cno,:grade)";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno", OracleDbType.Char, 10),new OracleParameter(":cno", OracleDbType.Char, 4),new OracleParameter(":grade", OracleDbType.Int32)};para[0].Value = s_c.sno;para[1].Value = o;para[2].Value = s_c.grade;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}public static int Updatesc(sc s_c){int result = 0;string sql = "update sc set grade=:grade where sno like :sno and cno like :cno";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":grade",OracleDbType.Int32),new OracleParameter(":sno",OracleDbType.Char,10),new OracleParameter(":cno",OracleDbType.Char,4) };para[0].Value = s_c.grade;para[1].Value = s_c.sno;para[2].Value = o;OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);result = cmd.ExecuteNonQuery(); }catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return result;}}}学生视图类studentinfo.csusing Oracle.ManagedDataAccess.Client;using System;using System.Collections.Generic;using System.Configuration;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace byd001_app{class studentinfo{public string 学号 {get; set; }public string 姓名 {get; set; }public int 总学分 {get; set; }public int 总分 {get; set; }public int 最高分 {get; set; }public int 最低分 {get; set; }public static List<studentinfo> Selectstudentinfo(string sno){string sql = "select *from studentinfo where 学号 like :学号";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":学号", OracleDbType.Char, 10) };para[0].Value = sno + "%";//MessageBox.Show(tname+"%");List<studentinfo> list = new List<studentinfo>();//创建链接，打开连接，创建命令对象，执行命令，关闭连接OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();while (odr.Read()){studentinfo s_infon = new studentinfo();//o = odr.GetString(0); //ord["cno'].s_infon.学号 = odr["学号"].ToString();//t.tno = odr.GetString(0);s_infon.姓名 = odr.GetString(1);if (odr.IsDBNull(2))s_infon.总学分 = 0;elses_infon.总学分 = odr.GetInt32(2);if (odr.IsDBNull(3))s_infon.总分 = 0;elses_infon.总分 = odr.GetInt32(3);if (odr.IsDBNull(4))s_infon.最高分 = 0;elses_infon.最高分 = odr.GetInt32(4);if (odr.IsDBNull(5))s_infon.最低分 = 0;elses_infon.最低分 = odr.GetInt32(5);/*c_infon.选修人数 = odr.GetInt32(4);c_infon.平均分 = odr.GetInt32(5);c_infon.最高分 = odr.GetInt32(6);c_infon.最低分 = odr.GetInt32(7);*/list.Add(s_infon);}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return list;}}}课程视图类courseinfo.csusing Oracle.ManagedDataAccess.Client;using System;using System.Collections.Generic;using System.Configuration;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace byd001_app{class courseinfo{public string 课程编号 {get; set; }public string 课程名称 {get; set; }public string 任课老师 {get; set; }public int 课程学分 {get; set; }public int 选修人数 {get; set; }public int 平均分 {get; set; }public int 最高分 {get; set; }public int 最低分 {get; set; }public static List<courseinfo> Selectcourseinfo(string cno){string sql = "select *from courseinfo where 课程编号 like :课程编号";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":课程编号", OracleDbType.Char, 4) };para[0].Value = cno + "%";//MessageBox.Show(tname+"%");List<courseinfo> list = new List<courseinfo>();//创建链接，打开连接，创建命令对象，执行命令，关闭连接OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();while (odr.Read()){courseinfo c_infon = new courseinfo();//o = odr.GetString(0); //ord["cno'].c_infon.课程编号 = odr["课程编号"].ToString();//t.tno = odr.GetString(0);c_infon.课程名称 = odr.GetString(1);c_infon.任课老师 = odr.GetString(2);c_infon.课程学分 = odr.GetInt32(3);if (odr.IsDBNull(4))c_infon.选修人数 = 0;elsec_infon.选修人数 = odr.GetInt32(4);if (odr.IsDBNull(5))c_infon.平均分 = 0;elsec_infon.平均分 = odr.GetInt32(5);if (odr.IsDBNull(6))c_infon.最高分 = 0;elsec_infon.最高分 = odr.GetInt32(6);if (odr.IsDBNull(7))c_infon.最低分 = 0;elsec_infon.最低分 = odr.GetInt32(7);/*c_infon.选修人数 = odr.GetInt32(4);c_infon.平均分 = odr.GetInt32(5);c_infon.最高分 = odr.GetInt32(6);c_infon.最低分 = odr.GetInt32(7);*/list.Add(c_infon);}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return list;}}}身份类common.csusing System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace byd001_app{class common{private static string id;private static string name;public static string ID{get {return id; }set {id = value; }}public static string Name{get {return name; }set {name = value; }}}}登录界面实现代码（三种身份的登录）using Oracle.ManagedDataAccess.Client;using System;using System.Collections.Generic;using ponentModel;using System.Configuration;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace byd001_app{public partial class Form_Login : Form{public Form_Login(){InitializeComponent();}private void Form_Login_Load(object sender, EventArgs e){}private void label1_Click(object sender, EventArgs e){}private void textBox1_TextChanged(object sender, EventArgs e){}private void button1_Click(object sender, EventArgs e){if (radioButton1.Checked == true) //学生{string sql = "select *from student where sno=:sno and pwd=:pwd";OracleParameter[] para = new OracleParameter[]{new OracleParameter(":sno",OracleDbType.Varchar2,10),new OracleParameter(":pwd",OracleDbType.Varchar2,20)};para[0].Value = this.tbno.Text;para[1].Value = this.tbpwd.Text;//this.Close();OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();if (odr.HasRows){MessageBox.Show("登录成功"); // 可以记录用户的登录信息this.DialogResult = DialogResult.OK;common.Name = this.tbno.Text;common.ID = "2";this.Close();}elseMessageBox.Show("用户名或者密码错误");}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}}else if (radioButton2.Checked == true){// 教师string sql = "select *from teacher where tno=:tno and pwd=:pwd";OracleParameter[] para = new OracleParameter[]{new OracleParameter(":tno",OracleDbType.Varchar2,7),new OracleParameter(":pwd",OracleDbType.Varchar2,20)};para[0].Value = this.tbno.Text;para[1].Value = this.tbpwd.Text;//this.Close();OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);try{con.Open();OracleCommand cmd = new OracleCommand(sql, con);cmd.Parameters.AddRange(para);OracleDataReader odr = cmd.ExecuteReader();if (odr.HasRows){MessageBox.Show("登录成功"); // 可以记录用户的登录信息this.DialogResult = DialogResult.OK;common.Name = this.tbno.Text;common.ID = "1";this.Close();}elseMessageBox.Show("用户名或者密码错误");}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}}else{if (this.tbno.Text == "admin" && this.tbpwd.Text == "123"){MessageBox.Show("登录成功"); // 可以记录用户的登录信息this.DialogResult = DialogResult.OK;common.Name = "admin";common.ID = "0";this.Close();return;}elseMessageBox.Show("用户名或者密码错误");}}}}

七、实验心得体会

通过本次实验，对使用vs窗体开发与oracle数据库的结合有了进一步的了解和更深体会，也在实践中对数据库原理有了更深层次的领悟。本次实验难度较大，我也是在网上寻找了许多的资料，最后勉强完成了实验。对数据库进行简单应用开发，需要熟悉和深刻理解数据库相关知识，才可以从复杂的关系中选择出自己想要的数据。