排课系统数据库报告

数据库课程设计题目某中学排课管理系统专业班级姓名学号完成日期 2014-11-24目录一、需求分析 (2)1.1数据字典 (2)1.2全系统的数据项 (3)1.3数据流图 (3)二、概念结构设计 (4)2.1E-R图 (4)2.2系统说明书 (5)三、逻辑结构设计 (15)3.1关系模型 (15)3.2参照完整性约束条件 (15)3.3系统结构图 (16)四、数据库实施 (16)4.1关系模式 (16)4.2程序编码（C#语言） (21)五、课程设计的感想和心得体会 (27)一、需求分析学生隶属班级，班级按照课程表上课，课程由老师教授，这些都要有管理人员来管理。

总结出如下需求信息：1)一个班级有多个学生；2)一个学生有多门课，一门课对应多个学生；3)一个教师可以教授多门课，一门课可以由多个教师来教授；4)一个班级对应一张班级课程表，一个教师也对应一张教师课程表；5)一个教师可以教授多个班级；经过对上述系统功能的分析和需求总结，设计总结如下：1.1数据字典2）班级信息表1.2全系统的数据项数据项：1)学生信息：学生ID、姓名、性别、出生日期、所属班级。

2)班级信息：班级ID、所属年级。

3)教师信息：教师ID、姓名、性别、年龄。

4)课程信息：课程ID、课程名称、教师ID。

5)课程表信息：星期、第一节、第二节、第三节、第四节、第五节、第六节、第七节、第八节、班级ID。

1.3数据流图信息文件二、概念结构设计2.1E-R图1)2)班级实体E-R图3)教师实体E-R图4)课程实体E-R图5)课程表实体E-R图6)全局E-R图2.2系统说明书1)系统要求某中学的排课管理系统●实现班级、课程等基本信息的管理；●实现学生、教师信息的管理；●实现班级课程及课程的任课教师和排课管理；●创建存储过程检测指定教师、指定节次是否有课；●创建存储过程生成指定班级的课程表；●创建存储过程生成指定老师的课程表；●建立数据库相关表之间的参照完整性约束。

2)方案和概图a)设计方案b)系统概图用户登录界面错误提示界面排课系统主界面教师信息主界面教师信息查询报错学生信息查询主界面学生信息查询报错课程信息查询主界面课程信息查询班级信息查询报错课表信息查询班级课程表教师课程表查询学生课程表查询三、逻辑结构设计3.1关系模型1）学生（学生ID，姓名，性别，出生日期，班级ID）主键：学生ID 外键：班级ID2）班级（班级ID，班级名称）主键：班级ID3）教师（教师ID，姓名，性别，年龄，）主键：教师ID4）课程（课程ID，课程名称，教师ID）主键：课程名称外键：教师ID5）课程表1（星期，第一节，第二节，第三节，第四节，第五节，第六节，第七节，第八节）主键：星期外键：第一节，第二节，第三节，第四节，第五节，第六节，第七节，第八节6）课程表2（星期，第一节，第二节，第三节，第四节，第五节，第六节，第七节，第八节，课程名称）主键：星期外键：第一节，第二节，第三节，第四节，第五节，第六节，第七节，第八节3.2参照完整性约束条件学生——班级学生.班级ID=班级.班级ID教师——课程教师.课程ID=课程.课程ID课程表——班级课程表.班级ID=班级.班级ID课程表——教师课程表.教师ID=教师.教师ID3.3系统结构图四、数据库实施4.1关系模式1）class表CREATE TABLE [dbo].[class]([classID] [int] NOT NULL,[classname] [nchar](20) NOT NULL,CONSTRAINT [PK_class] PRIMARY KEY CLUSTERED([classID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]2）course表CREATE TABLE [dbo].[course]([courseID] [int] NOT NULL,[coursename] [nchar](20) NOT NULL,[teacherID] [int] NULL,CONSTRAINT [PK_course] PRIMARY KEY CLUSTERED([coursename] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[course] WITH CHECK ADD CONSTRAINT [FK_course_teacher1] FOREIGN KEY([teacherID])REFERENCES [dbo].[teacher] ([teacherID])ALTER TABLE [dbo].[course] CHECK CONSTRAINT [FK_course_teacher1]3）student表CREATE TABLE [dbo].[student]([studentID] [int] NOT NULL,[name] [nchar](10) NOT NULL,[sex] [nchar](2) NULL,[birthday] [datetime] NULL,[classID] [int] NULL,CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED([studentID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [FK_student_class] FOREIGN KEY([classID])REFERENCES [dbo].[class] ([classID])ALTER TABLE [dbo].[student] CHECK CONSTRAINT [FK_student_class]4）teacher表CREATE TABLE [dbo].[teacher]([teacherID] [int] NOT NULL,[name] [nchar](10) NULL,[sex] [nchar](2) NULL,[age] [int] NULL,CONSTRAINT [PK_teacher] PRIMARY KEY CLUSTERED([teacherID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]5）courselist1表CREATE TABLE [dbo].[courselist1]([第一节] [nchar](20) NULL,[第二节] [nchar](20) NULL,[第三节] [nchar](20) NULL,[第四节] [nchar](20) NULL,[第五节] [nchar](20) NULL,[第六节] [nchar](20) NULL,[第七节] [nchar](20) NULL,[第八节] [nchar](20) NULL,[星期] [nchar](20) NULL) ON [PRIMARY]ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course] FOREIGN KEY([第一节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course] ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course1] FOREIGN KEY([第二节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course1] ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course2] FOREIGN KEY([第三节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course2] ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course3] FOREIGN KEY([第四节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course3] ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course4] FOREIGN KEY([第五节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course4] ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course5] FOREIGN KEY([第六节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course5] ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course6] FOREIGN KEY([第七节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course6] ALTER TABLE [dbo].[courselist1] WITH CHECK ADD CONSTRAINT[FK_courselist1_course7] FOREIGN KEY([第八节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist1] CHECK CONSTRAINT [FK_courselist1_course7] 6）courselist2表CREATE TABLE [dbo].[courselist2]([第一节] [nchar](20) NULL,[第二节] [nchar](20) NULL,[第三节] [nchar](20) NULL,[第四节] [nchar](20) NULL,[第五节] [nchar](20) NULL,[第六节] [nchar](20) NULL,[第七节] [nchar](20) NULL,[第八节] [nchar](20) NULL,[星期] [nchar](20) NULL) ON [PRIMARY]ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course] FOREIGN KEY([第一节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course] ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course1] FOREIGN KEY([第二节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course1] ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course2] FOREIGN KEY([第三节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course2] ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course3] FOREIGN KEY([第四节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course3] ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course4] FOREIGN KEY([第五节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course4] ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course5] FOREIGN KEY([第六节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course5] ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course6] FOREIGN KEY([第七节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course6] ALTER TABLE [dbo].[courselist2] WITH CHECK ADD CONSTRAINT[FK_courselist2_course7] FOREIGN KEY([第八节])REFERENCES [dbo].[course] ([coursename])ALTER TABLE [dbo].[courselist2] CHECK CONSTRAINT [FK_courselist2_course7] 7）用户user表CREATE TABLE [dbo].[users]([username] [varchar](50) NOT NULL,[password] [varchar](50) NOT NULL,CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED([username] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]8）创建存储过程生成指定班级的课程表create proc up_class(@classID int)asselect * from courselist1 where courselist1.classID=@classIDexecute up_class '1101'9）创建存储过程生成指定老师的课程表create proc up_teacher(@teacherID int)asselect * from courlist1 where courselist1.teacherID=@teacherIDexecute up_teacher '1'10)创建存储过程检测指定教师、指定节次是否有课create proc up_teacheer(@teacherID int,@星期 nvarchar(50)) asselect * from coueselist1 where courselist1.teacherID=@teacherID andcourselist1.星期=@星期execute up_teacher '1','第一节'4.2程序编码（C#语言）添加头文件：using System.Data.SqlClient;1）用户登录关键代码if (textBox1.Text == ""){MessageBox.Show("用户名不能为空！");return;}if (textBox2.Text == ""){MessageBox.Show("密码不能为空！");return;}string constr = "server=.;database=中学排课系统;uid=sa;pwd=sa";SqlConnection conn = new SqlConnection(constr);conn.Open();try{string login = "select * from users where username = '" + textBox1.Text.Trim() + "' and password = '" + textBox2.Text + "'";SqlCommand sqlCmd = new SqlCommand(login, conn);SqlDataReader sqlReader = sqlCmd.ExecuteReader();sqlReader.Read();if (sqlReader.HasRows){sqlReader.Close();start start = new start();start.Show();this.Hide();}else{MessageBox.Show("用户名或密码错误！");}}catch (Exception ex){MessageBox.Show(ex.Message);MessageBox.Show("用户名或密码错误！");}finally{conn.Close();}2）查询关键代码（以教师信息查询为例）private void chaxun_Click(object sender, EventArgs e){if (ID.Text.Trim() == ""){MessageBox.Show("请填写教师编号！");return;}string constr = "server=.;database=中学排课系统;uid=sa;pwd=sa";SqlConnection conn = new SqlConnection(constr);conn.Open();string str = "select * from teacher whereteacherID="+ID.Text.Trim();SqlCommand command = new SqlCommand(str, conn);SqlDataAdapter adapter = new SqlDataAdapter();adapter.SelectCommand = command;DataSet dataSet = new DataSet();adapter.Fill(dataSet, "type");dataGridView1.DataSource = dataSet.Tables[0];conn.Close();ID.Text = "";}3）添加关键代码（以教师信息添加为例）private void tianjia_Click(object sender, EventArgs e){if (ID.Text.Trim() == "" || na.Text.Trim() == "" ||se.Text.Trim()=="" || ag.Text.Trim()==""){MessageBox.Show("请填写完整信息！");return;}string constr = "server=.;database=中学排课系统;uid=sa;pwd=sa";SqlConnection conn = new SqlConnection(constr);conn.Open();try{string str = "insert into teacher values ('" + ID.Text.Trim() + "', '" + na.Text.Trim() + "', '" + se.Text.Trim() + "', " + ag.Text.Trim() + ")";SqlCommand sqlCommand = new SqlCommand();sqlCommand.Connection = conn;mandText = str;mandType = CommandType.Text;sqlCommand.ExecuteNonQuery();MessageBox.Show("添加成功！");}catch (Exception ex){MessageBox.Show("添加失败！");}finally{conn.Close();}teacherinformation_Load(sender, e);}4）删除关键代码（以教师信息删除为例）private void shanchu_Click(object sender, EventArgs e){if (ID.Text.Trim() == ""){MessageBox.Show("请填写教职工号！");return;}if (MessageBox.Show("确定删除吗？", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK){string constr = "server=.;database=中学排课系统;uid=sa;pwd=sa";SqlConnection conn = new SqlConnection(constr);conn.Open();try{string str = "delete from teacher where teacherID=" + ID.Text.Trim();SqlCommand sqlCommand = new SqlCommand();sqlCommand.Connection = conn;mandText = str;mandType = CommandType.Text;sqlCommand.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show("删除失败！");}finally{conn.Close();}teacherinformation_Load(sender, e);}}5）修改关键代码（以教师信息修改为例）在下边的文本框中显示选中的行string id = "";string mingzi = "";string xingbie = "";string nianling = "";private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e){id = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();mingzi =dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();xingbie =dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();nianling =dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();na.Text = mingzi;se.Text = xingbie;ag.Text = nianling;}信息修改private void xiugai_Click(object sender, EventArgs e){string constr = "server=.;database=中学排课系统;uid=sa;pwd=sa";SqlConnection conn = new SqlConnection(constr);conn.Open();try{string str = "update teacher set name = '" + na.Text.Trim() + "', sex = '" + se.Text.Trim() + "', age = " + ag.Text.Trim() + " where teacherID = "+ id;SqlCommand sqlCommand = new SqlCommand();sqlCommand.Connection = conn;mandText = str;mandType = CommandType.Text;sqlCommand.ExecuteNonQuery();}catch (Exception ex){MessageBox.Show("修改失败！");}finally{conn.Close();}teacherinformation_Load(sender, e);}五、课程设计的感想和心得体会在着手做程序之初，对VS的了解基本为零，对自己能做出的东西深感怀疑。