数据库技专题训练I(2015年春)数据库专题训练------数据库应用系统开发实验报告系别：计算机科学与技术班级：计12-1班姓名：刘杰学号：12101020128成绩：评语：指导教师签字：日期：大学生选课管理系统1、需求分析1.需求说明☐教务处的管理人员录入全校的课程基本信息和本学期的课程授课教师、地点、时间；☐在学生入学的时候，学院的管理人员录入学生基本信息；☐学生每学期自己上网登录系统选课，选课成功后信息存入数据库中，学生自己可以查询选课的情况；☐学生选课不成功的情况有：☐所选课程的先修课还没有记录，系统提示“缺先修课，选课失败”；☐本学期所选课程的上课时间有冲突，系统提示“上课时间有冲突，选课失败”；☐学生一学期所选课程的学分最多不能超18学分☐学生可以注销所选课程。

☐学院管理员可以查询学生前几学期的选课信息、可以查询课程基本信息、学生基本信息；☐当学生退学时，由教务处的管理人注销学生基本信息；☐如果开课之后，学生要求退课，则由教务处的工作人员为学生注销所选课程；☐允许学生休学，教务处为休学的退学做学籍冻结处理；复学后为其办理解冻处理；☐每学期教务处为学生办理学期注册手续；没有办理学期注册的学生不能选课；☐学期末，学院工作人员负责录入学生的成绩。

2.数据流程图a.顶层数据流图b.分解数据流图3.数据字典角色信息=角色编号+角色名称角色编号=“1” (4)角色名称=2(汉字)6用户信息=用户编号+角色编号+用姓名+用户密码用户编号=2（字母或数字）18角色编号=“1” (4)用户姓名=2（汉字）6用户密码=2（字母或数字）18学生基本信息=学号+姓名+性别+民族+出生年月+学院+系+专业+入学时间+政治面貌+住址+备注学号=（数字）11姓名=2（汉字）6性别=“男”/“女”民族=2（汉字）6出生年月=年+月系=2（字母或数字）11专业=2（字母或数字）11入学时间=年+月+日政治面貌=“党员”/“预备党员”/“团员”/“群众”住址=2（汉字）18备注=0（汉字或字母）100教师基本信息=工号+姓名+密码+性别+年龄+民族+学院+住址+备注工号=2（字母或数字）11姓名=2（汉字）6密码=2（字母或数字）18性别=“男”/“女”年龄=“20“ (60)民族=2（汉字）6学院=2（汉字）12住址=2（汉字）18备注=0（汉字或字母）100课程信息=课程号+课程名称+先修课+学分+学时+课程类别+开课学院课程号=2（字母或数字）11课程名称=2（汉字）15先修课=2（字母或数字）11学分=“1“ (16)学时=“16“ (128)课程类别=“专业选修“/”专业必修“/”公共选修“/”公共必修“开课学院=2（字母或数字）11课程任务=任务编号+课程编号+课程名称+任课教师+课堂容量+选课人数+上课时间段+上课日期+上课地点+合班标志+开课季别+开始周+结束周+课程介绍任务编号=2（字母或数字）11课程编号=2（字母或数字）11课程名称=2（汉字）15任课教师=2（字母或数字）11课堂容量=“0“ (240)选课人数=“0“ (9999)上课时间段=“1-2节“…”11-12节“上课日期=“周一“…”周日“上课地点=2（汉字）15合班标志=2（汉字）15开课季别=“春季学期“/”秋季学期“开始周=“1“ (20)结束周=“8“ (28)课程介绍=0（汉字）100学生选课信息表=学号+课程号+成绩+选课年份+选课季别+中签标志学号=2（字母或数字）11成绩=“0“ (100)选课年份=年选课季别=“春季学期“/”秋季学期“中签标志=“0“/“1“留言=留言编号+发送者+接收者+日期+留言内容留言编号=“0“ (99999999)发送者=2（字母或数字）11接收者=2（字母或数字）11日期=年+月+日留言内容=2（字母或汉字）200文件信息=文件编号+课程编号+文件名称+文件路径文件编号=“0“ (99999)课程编号=2（字母或数字）11文件名称=2（字母，数字或汉字）20文件路径=2（字母或数字）100学院信息=学院编号+学院名称学院编号=2（字母或数字）11学院名称=5（汉字）20专业信息=专业编号+专业名称+所属学院专业编号=2（字母或数字）11专业名称=5（汉字）20所属学院=2（字母或数字）11班级信息=班级编号+班级名称+所属专业班级编号=2（字母或数字）11班级名称=5（汉字）20所属专业=2（字母或数字）114.系统E-R图2、系统设计1.系统模块结构图a.顶层模块结构图b.详细模块结构图2.系统用例图a.学生用例图b.教师用例图c.管理员用例图Query information(from Query for a score,course,personal information)Query information(from Query for a score,course,personal information)(from Pigeonhole)Pigeonhole3.系统数据库详细设计b.表创建create table t_role(roleid int identity(1,1)not null primary key, rolename nvarchar(50)not null unique)create table sdept_inf(sdeptno nvarchar(20)not null primary key,sdeptname nvarchar(50)not null)create table major_inf(majorno nvarchar(20)not null primary key, majorname nvarchar(50)not null,sdeptno nvarchar(20)not nullforeign key(sdeptno)references sdept_inf(sdeptno) )create table user_inf(userid nvarchar(50)not null primary key,roleid int not null,pwd nvarchar(50)not null,foreign key(roleid)references t_role(roleid))create table teacher_inf(tno nvarchar(50)not null primary key,tname nvarchar(50)not null,tpwd nvarchar(50)not null,tsex nvarchar(20)not null,tage int not null,trace nvarchar(20)not null,sdeptno nvarchar(20)not null,taddress nvarchar(60),another nvarchar(100)foreign key(sdeptno)references sdept_inf(sdeptno) )/*学生基本信息*/create table stu_inf(sno nvarchar(50)not null primary key,sname nvarchar(50)not null,spwd nvarchar(50)not null,ssex nvarchar(20)not null,sage int not null,srace nvarchar(20)not null,sdeptno nvarchar(20)not null,class nvarchar(50)not null,smajor nvarchar(50)not null,entrancetime nvarchar(50)not null, graduattime nvarchar(50)not null, politicalstatus nvarchar(20)not null,saddress nvarchar(60),another nvarchar(100)foreign key(sdeptno)references sdept_inf(sdeptno) )/*课程基本信息*/create table course_inf(cno nvarchar(50)not null primary key,pro_cno nvarchar(50),cname nvarchar(50)not null,credit int not null,ctime int not null,course_kind nvarchar(50)not null,sdept nvarchar(50)not null,)/*学院开课信息*/create table course_task(taskno nvarchar(50)not null primary key, cno nvarchar(50)not null,cname nvarchar(50)not null,cteachar nvarchar(50)not null,capacity int not null,classtime nvarchar(50)not null, weekdays nvarchar(30)not null, location nvarchar(50)not null, classflag nvarchar(50)not null,season nvarchar(50)not null, startweek int not null,endweek int null,introduction nvarchar(200),)/*选课记录表*/create table sel_course_record(sno nvarchar(50)not null,cno nvarchar(50)not null,grade int,year int not null,flag int not null,/*中签标志*/season nvarchar(50)not null,primary key(sno,cno))3、系统实现1.系统主要功能界面设计a)登陆界面b)学生操作界面c)教师操作界面d)学院管理员操作界面e)系统管理员操作界面2.系统主要功能界面代码实现a)登陆界面namespace WindowsFormsApplication2{public partial class login : Form{public login(){InitializeComponent();}private void button1_Click(object sender, EventArgs e) //登陆按钮{string userid = textBox1.Text;//用户名string pwd = textBox2.Text; //密码string rolename = comboBox1.Text;string conn_string = "Data Source=MR_LIU\\LIUJIE;Initial Catalog=course_sel;Integrated Security=True; User ID=sa; pwd=liujie260593";SqlConnection con = new SqlConnection();con.ConnectionString = conn_string;try{con.Open();//获取角色编号// Class_role role = new Class_role();SqlCommand cm = new SqlCommand("select roleid from t_role whererolename=@rolename ", con);cm.Parameters.Add(new SqlParameter("@rolename", rolename));Form_main.roleid= (int)cm.ExecuteScalar();con.Close();con.Open();string str_sql ="select count(userid) from user_inf where userid=@userid and pwd=@pwd and roleid=(select roleid from t_role where rolename=@rolename)";//执行的命令SqlSqlCommand cmd = new SqlCommand(str_sql, con);cmd.Parameters.Add(new SqlParameter("@userid", userid));cmd.Parameters.Add(new SqlParameter("@pwd", pwd));cmd.Parameters.Add(new SqlParameter("@rolename", rolename));int flag = (int)cmd.ExecuteScalar();if (flag > 0){Form_erid = textBox1.Text;Form_main f = new Form_main();f.Show();this.Hide();}else{MessageBox.Show("用户名或密码输入不正确！");}}catch (Exception ex){MessageBox.Show(ex.ToString());}finally{con.Close();}}private void login_Load(object sender, EventArgs e){string conn_string = "Data Source=MR_LIU\\LIUJIE;Initial Catalog=course_sel;Integrated Security=True; User ID=sa; pwd=liujie260593";SqlConnection con = new SqlConnection();con.ConnectionString = conn_string;SqlCommand cmd = new SqlCommand();mandText = "select rolename from t_role";cmd.Connection = con;SqlDataReader dr = null;try{con.Open();dr = cmd.ExecuteReader();int count = 0;while (dr.Read()){boBox1.Items.Add(new ListItem(dr["rolename"].ToString(), count));count++;}}catch (Exception ex){MessageBox.Show("数据读取出错！" + ex.ToString());}finally{con.Close();}}}}b)选课操作private void button1_Click(object sender, EventArgs e){int flag = 0;//int gradecount = 0;string sno = Form_erid;string cno = Student_Select_Course.courseno;string cname = boBox3.Text;string majorname = this.textBox1.Text;string weekdays = "";string classtime = "";string conn_string = "Data Source=MR_LIU\\LIUJIE;Initial Catalog=course_sel; User ID=sa; pwd=liujie260593";SqlConnection con1 = new SqlConnection();con1.ConnectionString = conn_string;SqlConnection con2 = new SqlConnection();con2.ConnectionString = conn_string;SqlDataReader dr1 = null;//冲突判断string sql_str1 = "select * from course_task where cno='" + cno + "' ";try{con1.Open();con2.Open();SqlCommand cmd1 = new SqlCommand(sql_str1, con1);dr1 = cmd1.ExecuteReader();while (dr1.Read()){weekdays = dr1["weekdays"].ToString();classtime = dr1["classtime"].ToString();string sql_str2 = "select count(*) from sel_course_record,course_task where weekdays='" + weekdays + "' and classtime='" + classtime + "' and sno='" + sno + "' andcourse_o=sel_course_o";SqlCommand cmd2 = new SqlCommand(sql_str2, con2);flag = (int)cmd2.ExecuteScalar();if (flag > 0){MessageBox.Show("存在选课冲突，请重新选课！");break;}}con1.Close();con2.Close();if (flag == 0)//学分限制{int year = System.DateTime.Today.Year;int month=System.DateTime.Today.Month;string season_now = "秋季学期";if (2<=month && month <8)season_now = "春季学期";SqlConnection con3 = new SqlConnection();con3.ConnectionString = conn_string;con3.Open();SqlCommand cmd3 = new SqlCommand("select sum(credit) sum fromsel_course_record,course_inf where year='"+year+"' and season='"+season_now+"' andcourse_o=sel_course_o and sno='"+Form_erid+"'", con3);dr1=cmd3.ExecuteReader();int coun=0;string strc="";while (dr1.Read()){strc= dr1["sum"].ToString();}if (strc == "")coun = 0;elsecoun = Convert.ToInt32(strc);if (coun<=40){//先修课判断con3.Close();int count = 0;con3.Open();mandText = "select count(*) from sel_course_record wheresno='"+sno+"' and cno='"+Student_Select_Course.pro_cno+"'and not(year='"+year+"' andseason='"+season_now+"')";count = (int)cmd3.ExecuteScalar();con3.Close();if (Student_Select_Course.pro_cno.Equals("")==false && count==0){MessageBox.Show("你还未选修该课程的先修课，不能申请该课程！");}else{//将选课信息加入选课记录表,同时添加课表con3.Open();mandText = "insert into sel_course_record values('" + sno + "','"+ cno + "','" + "" + "','" + year + "','" + "0" + "','" + season_now + "')";cmd3.ExecuteNonQuery();con3.Close();//更新课程任务表sel_person += 1;con3.Open();mandText = "update course_task set sel_person='" +sel_person + "' where cno='" + cno + "'";cmd3.ExecuteNonQuery();con3.Close();MessageBox.Show("申请成功！");}}elseMessageBox.Show("你所选课程学分总数已经超过40分，不能继续选课！");}}catch (Exception ex){MessageBox.Show("数据读取失败！" + ex.Message);}}c)抽签操作private void button1_Click(object sender, EventArgs e){this.listView1.Items.Clear();string cname="";int count = 0;int sel_count = 0;string conn_string = "Data Source=MR_LIU\\LIUJIE;Initial Catalog=course_sel; User ID=sa; pwd=liujie260593";SqlConnection con = new SqlConnection();con.ConnectionString = conn_string;SqlDataReader dr = null;SqlCommand cmd = new SqlCommand();mandText = "select cname,sel_person,capacity from course_task wherecno='"+boBox1.Text+"'";cmd.Connection = con;try{con.Open();dr = cmd.ExecuteReader();while (dr.Read()){cname = dr[0].ToString();sel_count = Convert.ToInt16(dr[1].ToString());count = Convert.ToInt16(dr[2].ToString());}con.Close();con.Open();mandText = "select sno,year,season from sel_course_record where cno='" + boBox1.Text + "'";dr = cmd.ExecuteReader();while (dr.Read()){this.listView1.Items.Add(new ListViewItem(new string[]{ (this.listView1.Items.Count + 1).ToString(), dr[0].ToString(), cname, dr[1].ToString(), dr[2].ToString(), "" }));}con.Close();if (sel_count <= count){for (int i = 0; i < sel_count; i++)this.listView1.Items[i].SubItems[5].Text = "中签";}else{int value;int flag = 0;Random r = new Random();int num = 0;while (num < count){value = r.Next(0, sel_count);for (int j = 0; j < this.listView1.Items.Count; j++){if (Convert.ToInt16(this.listView1.Items[j].SubItems[0].Text) - 1 == value && this.listView1.Items[j].SubItems[5].Text.Equals("中签")){flag = 1;break;}}if (flag == 0){this.listView1.Items[value].SubItems[5].Text = "中签";num++;}flag = 0;}}for (int i = 0; i < this.listView1.Items.Count; i++){if (this.listView1.Items[i].SubItems[5].Text.Equals("中签")){con.Open();mandText = "update sel_course_record set flag='" + 1 + "' where cno='" + boBox1.Text + "' and sno='" + this.listView1.Items[i].SubItems[1].Text + "'";cmd.ExecuteNonQuery();con.Close();}}this.listView1.Items.Clear();mandText = "select sno ,year,season from sel_course_record where cno='" + boBox1.Text + "' and flag='" + 1 + "'";con.Open();dr = cmd.ExecuteReader();while (dr.Read()){this.listView1.Items.Add(new ListViewItem(new string[]{ (this.listView1.Items.Count + 1).ToString(), dr[0].ToString(), cname, dr[1].ToString(), dr[2].ToString(), "中签" }));}con.Close();}catch(Exception ex){MessageBox.Show(ex.ToString());}finally{con.Close();boBox1.Items.Remove(boBox1.SelectedItem);}}d)教师上传课件private void button3_Click(object sender, EventArgs e){//string a = DateTime.Now.ToString("yyyyMMddHHmmss");string FileName = openFileDialog1.SafeFileName;string filename = openFileDialog1.FileName;string desName = Application.StartupPath + "\\upload\\";string b = desName + FileName;File.Copy(filename, b, true);string conn_string = "Data Source=MR_LIU\\LIUJIE;Initial Catalog=course_sel;Integrated Security=True; User ID=sa; pwd=liujie260593";SqlConnection con = new SqlConnection();con.ConnectionString = conn_string;SqlCommand cmd = new SqlCommand();cmd.Connection = con;mandText = "insert into file_up values('" + boBox4.Text + "','" + FileName + "','"+desName+"')";con.Open();cmd.ExecuteNonQuery();con.Close();MessageBox.Show("文件上传成功！");this.dataGridView1.Rows.Clear();load_file();}e)系统管理员统计选课信息private void System_manage_Load(object sender, EventArgs e){string conn_string = "Data Source=MR_LIU\\LIUJIE;Initial Catalog=course_sel;Integrated Security=True; User ID=sa; pwd=liujie260593";SqlConnection con = new SqlConnection();con.ConnectionString = conn_string;SqlCommand cmd = new SqlCommand();mandText = "select distinct cno,cname,sel_person from course_task";cmd.Connection = con;SqlDataReader dr = null;con.Open();dr = cmd.ExecuteReader();while (dr.Read()){this.dataGridView5.Rows.Add(dr[0].ToString(),dr[1].ToString(),dr[2].ToString());}con.Close();int row = this.dataGridView5.Rows.Count - 1;int[] yval=new int[row];string [] xval=new string[row];for (int i = 0; i < row; i++){yval[i] = Convert.ToInt16(this.dataGridView5.Rows[i].Cells[2].Value.ToString());xval[i] = this.dataGridView5.Rows[i].Cells[1].Value.ToString();}this.chart1.Series["课程"].Points.DataBindXY(xval, yval);}4、系统测试1.系统主要功能测试用例2.系统主要功能测试结果1.学生选课2.学院操作3.教师操作4.学院选课统计。