一、基本要求
假设有“教师”、“学生”、“课程”三个实体,教师的基本信息包括:工号、姓名、职称、工资,课程的基本信息包括:课程号、课程名、学分数,学生的基本信息包括:学号、姓名、性别、年龄。系统必须满足以下要求:
(1)一门课程只能有一个教师任课,一个教师可以上多门课程;
(2)一个学生可以选修多门课程,一门课程可以由多个学生来选修,记录不同学生选修不同课程的成绩;
(3)设置一个管理员,用于维护(添加、删除和修改等基本任务)学生基本信息、教师基本信息和教师所授课程等工作,此外,管理员添加学生时,为其设置初始密码;当学生选修了某门课程,课程成绩由管理员录入;
(4)学生可以利用学号和密码登录系统,登陆系统后,可以进行选课、修改密码和个人基本信息、查询自己的选课及总学分等操作;
(5)能够统计不同职称的教师的数量、不同职称的教师的平均工资,可以统计每门课程的平均成绩、最高分、最低分,统计每个学生选修课程的总学分;
实现环境
vs 2017, Oracle 10g, sqldeveloper.
- 相关E-R图
- 关系模式
Student(Sno,Sname,Ssex,Sage,Sdept) Sno---主码。
Course(Cno,Cname,Ccredit) Cno---主码。
Teacher(Tno,Tname,Tsalary,Tpost) Tno---主码。
SC(Sno,Cno) Sno,Cno---主码,Sno---外码,Cno---外码。
TC(Tno,Cno) Tno,Cno---主码,Sno---外码,Cno---外码,取值唯一。
二、创建学生课程管理系统
(1)在sqldeveloper设计数据库物理模式
1.创建学生表
CREATE TABLE Student
(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(3),
Sage SMALLINT,
Sdept CHAR(20)
Pwd VARCHAR2(20)
);
2.创建课程表
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Ccredit SMALLINT,
);
3.创建创建学生选修课程表
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno) on delete cascade,
FOREIGN KEY (Cno) REFERENCES Course(Cno) on delete cascade
);
4.创建教师表
CREATE TABLE Teacher
(
TNO VARCHAR2(20) not null,
TName NVARCHAR2(20) not null,
TSex VARCHAR2(3) not null,
TSalary NUMBER(30,2),
TPost VARCHAR2(20),
constraint PK_TEACHER primary key (TNO)
);
5.创建教师授课表
create table TC(
TNO VARCHAR2(20) not null,
CNO CHAR(4) not null UNIQUE,
constraint PK_TC PRIMARY KEY (TNO,CNO),
constraint FK_TC1 FOREIGN KEY(TNO) REFERENCES teacher(tno) ON delete CASCADE ,
constraint FK_TC2 FOREIGN KEY(CNO) REFERENCES course(cno) on delete cascade
);
6.创建统计视图
-------创建学生成绩视图
create VIEW studenttinfo
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)) 总学分
from student;
-------创建课程统计视图
create view courseinfo
as
select course.cno,course.cname,course.ccredit,
(select count(sno) from sc where cno=course.cno) 选修人数
from course;
-------创建学生选修课程统计视图
create view studentselsction
as
select student.sno,sname,course.cno,cname,grade
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno;
-------创建教师教授课程具体信息统计视图
create view teacherteaching
as
select teacher.tno,teacher.tname,course.cno,course.cname
from teacher,course,tc
where teacher.tno=tc.tno and course.cno=tc.cno;
-------创建教师统计视图
SELECT
distinct teacher.tpost,
(select count(x.tno) from teacher x where x.tpost=teacher.tpost) 教师总数,
(select cast(avg(x.tsalary) as decimal(10,2)) from teacher x where x.tpost=teacher.tpost) 平均工资
FROM teacher
(2)在c#设计系统窗口
创建窗体应用程序后,添加扩展引用Oracle.ManagedDataAccess来使用Oracle数据库。
1.在App.config中添加数据库用户名和密码。
<configuration>
<connectionStrings>
<add name="strCon" connectionString ="data source=服务器ip/数据库名;User id=与用户名;password=密码"/>
2.定义类来储存信息以及插入、删除等函数
-----CommonData.cs
//全局变量用于存放登录后的用户名
namespace CourseSelectSystem
{
class CommonData
{
public static string sno;
public static string passwords;
}
}
-----Course.cs
//课程信息
namespace CourseSelectSystem
{
class Course
{
public string Cno
{
get;
set;
}
public string Cname
{
get;
set;
}
public int Ccredit
{
get;
set;
}
//查询课程信息
public static List<Course> SelectCourse(string cname)
{
string sql = "select cno,cname,ccredit 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();
c.Cno = odr.GetString(0);
//c.Cno = odr["cno"].ToString();
c.Cname = odr.GetString(1);
c.Ccredit = odr.GetInt32(2);
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) values (:cno,:cname,:ccredit)";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":cno", OracleDbType.Char, 4) ,
new OracleParameter(":cname", OracleDbType.Char, 40) ,
new OracleParameter(":ccredit", OracleDbType.Int32)
};
para[0].Value = c.Cno;
para[1].Value = c.Cname;
para[2].Value = c.Ccredit;
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 where cno=:cno";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":cname", OracleDbType.Char, 40) ,
new OracleParameter(":ccredit", OracleDbType.Int32),
new OracleParameter(":cno", OracleDbType.Char, 4)
};
para[0].Value = c.Cname;
para[1].Value = c.Ccredit;
para[2].Value = c.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 List<Course> StudentSelectCourse(string sno)
{
string sql = "select cno,cname,ccredit from course where cno not in(select cno from sc where sno=:sno)";
OracleParameter[] para = new OracleParameter[] {
new OracleParameter(":sno", OracleDbType.Char, 9)
};
para[0].Value = sno + "%";
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();
c.Cno = odr.GetString(0);
//c.Cno = odr["cno"].ToString();
c.Cname = odr.GetString(1);
c.Ccredit = odr.GetInt32(2);
list.Add(c);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return list;
}
//学生选课
public static int StudentChooseCourse(string sno,string cno)
{
int result = 0;
string sql = "insert into sc(sno,cno,grade) values (:sno,:cno,:grade)";
OracleParameter[] para = new OracleParameter[] {
new OracleParameter(":sno", OracleDbType.Char, 9),
new OracleParameter(":cno", OracleDbType.Char, 4),
new OracleParameter(":grade", OracleDbType.Int32)
};
para[0].Value = sno;
para[1].Value = cno;
para[2].Value = 0;
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;
}
}
}
同理,可以设置学生类,教师类等储存相关信息以及创建相关函数实现自己想要的功能。相关代码与上述代码相差不大。
3.设计登录窗口
//对登录按钮添加单击响应事件(管理员账号:Admin 密码:admin)
private void button1_Click(object sender, EventArgs e)
{
string sql = "select * from student where sno=:sno and pwd=:pwd";
OracleParameter[] para = new OracleParameter[] {
new OracleParameter(":sno",OracleDbType.Char,9),
new OracleParameter(":pwd",OracleDbType.Varchar2,20)
};
para[0].Value = this.tb_name.Text;//获取文本框信息引用到sql语句中
para[1].Value = this.tb_passwords.Text;
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(para[0].Value.Equals("Admin")&& para[1].Value.Equals("admin"))
{
MessageBox.Show("管理员登录成功!");
this.Hide();//弹出新窗口隐藏登录窗口
AdminForm admin = new AdminForm();
admin.Closed+= new EventHandler(this.admin_Closed);
admin.Show();
}
else if (odr.HasRows)
{
MessageBox.Show("学生登录成功!");
CommonData.sno = this.tb_name.Text;
CommonData.passwords = this.tb_passwords.Text;
// Console.WriteLine(CommonData.sno);
this.Hide();
StudentForm student = new StudentForm();
student.Closed+= new EventHandler(this.student_Close);
student.Show();
}
else
{
this.tb_name.Text = "";
this.tb_passwords.Text = "";
MessageBox.Show("用户名或密码错误。");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
//管理员界面关闭事件
private void admin_Closed(object sender, EventArgs e)
{
Application.Exit();
}
//学生界面关闭事件
private void student_Close(object sender, EventArgs e)
{
Application.Exit();
}
代码效果
4.(1)设计管理员登录后操作界面
(2)新建相应功能的窗口(例如建立一个添加课程的窗口):
添加响应事件:
private void button1_Click(object sender, EventArgs e)
{
Course c = new Course();
c.Cno = this.tbCno.Text;
c.Cname = this.tbCname.Text;
c.Ccredit = Convert.ToInt32(tbCcredit.Text);
//使用定义好的course类中的InsertCourse函数
if (Course.InsertCourse(c) == 1)
{
MessageBox.Show("添加成功。");
this.Close();
}
}
(3)对于查询功能的窗口,要在数据视图中添加相应数据源
之后在选择已经定义好的类。
(4)在菜单栏中添加相应功能,并且添加打开新窗口的响应事件,例如:
private void 查询学生ToolStripMenuItem_Click(object sender, EventArgs e)
{
AdminStudentSearch adminStudentSearch = new AdminStudentSearch();
adminStudentSearch.MdiParent = this;
adminStudentSearch.Show();
}
(
5.设计学生操作界面
设计方法和设计管理员操作界面相差不大,使用函数时只用把参数改成全局变量CommonData中的相应数据。
三、相应代码
下载好后需要先创建相应的数据库,之后在在App.config文件中添加数据库用户名和密码