数据库设计实例—教学管理系统

数据库课程设计报告教学管理系统数据库设计课程设计题目教学管理系统学院软件学院班级软件技术四班年级2013级姓名彭超李新徐彤（2014 年11月）用5行左右的文字对系统进行简要介绍对教学管理信息统一规范整理，实现各种信息的自动管理。

为便于信息的查询，找出各种信息的关联性，根据各种需求设计出合理的报表。

减轻教学日常信息管理的负担，方便学生、教师查询信息和学校对所有信息的管理。

以简单便捷的操作获取详尽的信息。

一、数据需求分析某学校设计学生教学管理系统。

学生实体包括学号、姓名、性别、生日、民族、籍贯、简历、登记照，每名学生选择一个主修专业，专业包括专业编号、名称和类别，一个专业属于一个学院，一个学院可以有若干个专业。

学院信息要存储学院号、学院名、院长。

教学管理还要管理课程表和学生成绩。

课程表包括课程号、课程名、学分，每门课程由一个学院开设。

学生选修的每门课程获得一个成绩。

另外，为了管理教师教学安排，教师包括编号、姓名、年龄、职称，一个教师只能属于一个学院，一名教师可以上若干门课程，一门课程可以有多名老师来上，每个教师所上的每门课都有一个课堂号和课时数。

本系统数据字典如下：数据项表数据流数据流表二、概念结构设计1．首先确定系统中的实体从以上数据需求可以看出，系统共包括5个实体：学生、专业、学院、教师、课程。

2．再确定系统中实体间的关系根据数据需求描述推出：专业与学生是1对多关系；学生与课程是多对多关系；课程与老师是多对多关系；课程与学院是多对1关系；学院与专业是1对多关系；学院与教师是1对多关系。

3．转化成E-R图图1 实体-属性图图2 教学管理ER图三、逻辑结构设计在转换为关系模型时，一对多的联系都在相应的多方实体的关系中增加一个外键。

本系统由ER模型转换的关系模型是：学生（学号，姓名，性别，生日，民族，籍贯，专业号，简历，登记照）专业（专业号，专业，专业类别，学院号）学院（学院号，学院，院长）课程（课程号，课程名，学分，学院号）成绩（学号，课程号，成绩）教师（编号，姓名，年龄，职称）四、物理结构设计（不写）五、数据库实施基于Access的数据库结构设计如下。

指定数据库文件的名称，并为设计好的关系模型设计表结构。

数据库文件保存在“E:\教学管理\”文件夹中，数据库文件名：教学管理.MDB。

表包括：学院、专业、学生、课程、成绩单。

对应表结构如表1-2至表1-6所示。

表1-1 学院表1-2 专业表1-3 学生表1-4 课程表1-5 成绩单附：教学管理数据库参考数据如表1-1～表1-5所示。

表1-1 学生表表1-2 学院表表1-2 专业表表1-4 课程表表1-5 成绩单六、思考题：当我们进行物理设计时，如果将全校的学生放置在一个关系（表）中，势必带来存储空间大、处理效率低的问题。

怎么解决？由于系统要涉及大量数据的操作，所以索引的建立就成为一种必需。

本系统分别在各Course,Student,Teacher,Dept,Score各个表的主键上建立索引。

附录A：SQL脚本：1.创建数据库TeachingSystemGOCREATE DATABASE TeachingSystemON(NAME=TeachingSystem_dat,FILENAME="E:\MyProjects\SQL Server\TeachingSystem.Mdf",SIZE=30,FILEGROWTH=20%)LOG ON(NAME=StudentSystem_log,FILENAME="E:\MyProjects\SQL Server\TeachingSystem.Ldf",SIZE=3,FILEGROWTH=20%)GO2.创建各主表GOCREATE TABLE Dept(DeptNo varchar(10)NOT NULLCHECK(DeptNo LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), DeptName varchar(20)NOT NULL,Info varchar(256)NOT NULL,PRIMARY KEY(DeptNo));GOCREATE TABLE Student(StudentNo varchar(10)NOT NULLCHECK(StudentNo LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), StudentName varchar(20)NOT NULL,Gender varchar(4)NOT NULL CHECK(Gender='男'OR Gender='女'), Birthday datetime NOT NULL,EntryScore float(8)NOT NULL CHECK(EntryScore>= 0),DeptNo varchar(10)NOT NULL,PRIMARY KEY(StudentNo),FOREIGN KEY(DeptNo) REFERENCES Dept(DeptNo)ON DELETE NO ACTIONON UPDATE CASCADE);GOCREATE TABLE Teacher(TeacherNo varchar(10)NOT NULLCHECK(TeacherNo LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), TeacherName varchar(20)NOT NULL,Gender varchar(4)NOT NULL CHECK(Gender='男'OR Gender='女'), Birthday datetime NOT NULL,ProTitle varchar(20)NOT NULL,Specialized varchar(20)NOT NULL,DeptNo varchar(10)NOT NULL,PRIMARY KEY(TeacherNo),FOREIGN KEY(DeptNo) REFERENCES Dept(DeptNo)ON DELETE NO ACTIONON UPDATE CASCADE);GOCREATE TABLE Course(CourseNo varchar(10)NOT NULLCHECK(CourseNo LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), CourseName varchar(20)NOT NULL,TeacherNo varchar(10)NOT NULL,CPeriod int NOT NULL,Credit int NOT NULL,BeginDate datetime NOT NULL,CAddress varchar(20)NOT NULL,ExamDate datetime NOT NULL,PRIMARY KEY(CourseNo),FOREIGN KEY(TeacherNo) REFERENCES Teacher(TeacherNo)ON DELETE NO ACTIONON UPDATE CASCADE);GOCREATE TABLE Score(StudentNo varchar(10)NOT NULL REFERENCES Student(StudentNo), CourseNo varchar(10)NOT NULL REFERENCES Course(CourseNo), OrdScore float(8)NOT NULL CHECK(OrdScore>=0 AND OrdScore<=100), ExamScore float(8)NOT NULL CHECK(ExamScore>=0 AND ExamScore<=100), SumScore AS (OrdScore*0.3+ExamScore*0.7),PRIMARY KEY(StudentNo,CourseNo));GO附录B//----------------------------------------------------------------------------- //所属类：TSDatabase//功能：取得应用程序与数据库系统的连接public class TSDatabase{// Attributes Definitionprivate SqlConnection _Con = null;private String _ConStr = null;private bool _IsConnected = false;//private User _CurrentUser = null;private String _ErrorMsg = "";// Methods Definition///<summary>/// TSDatabase constructor///</summary>public TSDatabase(){}///<summary>/// Connect to Teaching System Database/// Default connection definite as App.config///</summary>///<returns></returns>public bool Connect(){// Get connection string from App.config_ConStr = ConfigurationManager.ConnectionStrings["TSConnection"].ConnectionString;// Current connection is nulltry{if (_Con == null){_Con = new SqlConnection(_ConStr);_IsConnected = true;_ErrorMsg = "";}return true;}catch (System.Exception ex){_ErrorMsg = ex.ToString();_Con = null;_ConStr = null;_IsConnected = false;return false;}}///<summary>/// Disconnect to Teaching System Database///</summary>///<returns></returns>public bool Disconnect(){try{if (_Con != null){if (_Con.State != ConnectionState.Closed){_Con.Close();}_Con.Dispose();_Con = null;}_ConStr = null;_ErrorMsg = "";return true;}catch (System.Exception ex){_Con = null;_ConStr = null;_IsConnected = false;_ErrorMsg = ex.ToString();return false;}}//----------------------------------------------------------------------------- //所属类：User//功能：完成各查询功能的函数，即包含不同类型用户的操作public class User{// Attributes Definitionbool _IsLogin = false;String _UserName = ""; // Save current user number insteadUserType _UserType = 0;static String _ErrorMsg = "";// Methods Definitionpublic User(String UserName, UserType UT){this._UserName = UserName;this._UserType = UT;}// User's virtual functions for different type of user to overridepublic virtual DataTable Select(String QueryString, QueryType QT, TSDatabase TSDB) { return null; }public virtual bool Update(String QueryString, QueryType QT, TSDatabase TSDB) { return fals e; }public virtual bool Insert(String QueryString, QueryType QT, TSDatabase TSDB) { return false; }public virtual bool Delete(String QueryString, QueryType QT, TSDatabase TSDB) { return false ; }#region Get and set user's attributes……..….#endregion///<summary>/// According to the user, query type and offered strings to build a query string///</summary>///<param name="CurrentUser"></param>///<param name="QT"></param>///<param name="Strings">All strings needed to build querystring</param>///<returns></returns>public static String GetQueryString(User CurrentUser, QueryType QT, String[] Strings){String QueryString = "";try{switch (QT){……….…………case QueryType.StudentSelect:{switch (erType){case UserType.Student:QueryString = "SELECT * FROM Student WHERE StudentNo = '" + CurrentUser .UserName + "';";break;case UserType.Teacher:QueryString = "SELECT Student.StudentNo AS StudentNo,"+ "StudentName AS StudentName,"+ "Student.Gender AS Gender, "+ "EntryScore AS EntryScore, "+ "DeptName AS Department, "+ " AS Infomation "+ "FROM Student, Dept, Course, Score "+ "WHERE Course.TeacherNo = " + erName + " " + "AND Course.CourseNo = Score.CourseNo "+ "AND Score.StudentNo = Student.StudentNo "+ "AND Student.DeptNo = Dept.DeptNo;";break;…………….…………………………}}//-----------------------------------------------------------------------------//所属类：Student,Teacher,Administrator//功能：均继承User类，具体实现SELECT,UPDATE,INSERT,DELETE函数内容// (以下用Student类中的重写SELECT作为例子)///<summary>/// Select operation for Student/// Permissible operations(QueryType): All type of select///</summary>///<param name="QueryString">Transact-SQL statement</param>///<param name="QT">Select query type</param>///<param name="TSDB">Teaching system database</param>///<returns>Selected DataTable</returns>public override DataTable Select(String QueryString, QueryType QT, TSDatabase TSDB){SqlConnection con = null;DataTable DT = new DataTable();this.ErrorMessage = "";// If it's offlineif (!TSDB.IsConnected){// If fail to connectif (!TSDB.Connect()){this.ErrorMessage += TSDB.ErrorMessage;return null;}}try{con = TSDB.Connection;// If connection is closed, open firstif (con.State == ConnectionState.Closed){con.Open();}// Different query typeswitch (QT){case QueryType.StudentSelect:case QueryType.CourseSelect:case QueryType.TeacherSelect:case QueryType.ScoreSelect:case QueryType.DeptSelect:{SqlDataAdapter SDA = new SqlDataAdapter();SqlCommand SC = new SqlCommand(QueryString, con);mandType = CommandType.Text;SDA.SelectCommand = SC;SDA.Fill(DT);}break;default:{this.ErrorMessage = this.ErrorMessage + "\nPermission Deny: " + QT.ToString(); DT = null;}break;}}catch (System.Exception ex){this.ErrorMessage = this.ErrorMessage + "\n" + ex.ToString();DT = null;}finally{// Close connection to databaseif (con.State != ConnectionState.Closed){con.Close();}}return DT;}//----------------------------------------------------------------------------- //所属类：TeachingSystem//功能：整个教学系统主要界面操作函数//说明：以显示系统界面ShowTS与加载数据库数据表到列表显示函数LoadDataGrid为例///<summary>/// Show components for different type of user and set this visible///</summary>public void ShowTS(){switch (erType){case UserType.Student:{this.tabControl1.Controls.Add(this.Page_SAbout);this.tabControl1.Controls.Add(this.Page_Teacher);this.tabControl1.Controls.Add(this.Page_Course);this.tabControl1.Controls.Add(this.Page_Score);this.button_TLoad.Enabled = true;this.button_TAdd.Enabled = false;this.button_TRemove.Enabled = false;this.button_TEdit.Enabled = false;this.button_CLoad.Enabled = true;this.button_CAdd.Enabled = false;this.button_CRemove.Enabled = false;this.button_CEdit.Enabled = false;this.button_SCLoad.Enabled = true;this.button_SCAdd.Enabled = false;this.button_SCRemove.Enabled = false;this.button_SCEdit.Enabled = false;}break;……………………….InitAboutMyself();LoadAllDataGrid();this.Visible = true;}///<summary>/// Load database related data to a specific datagridview/// With user type switch///</summary>private void LoadDataGrid(GridViewType GVT){String QueryString = null;switch (GVT){………………………..case GridViewType.Student:{switch (erType){case UserType.Student:break;case UserType.Teacher:……..break;case UserType.Administrator:{QueryString = User.GetQueryString(CurrentUser, QueryType.StudentSelect, n ull);BindingSource BS = new BindingSource();BS.DataSource = ((Administrator)CurrentUser).Select(QueryString, QueryTyp e.StudentSelect, TSDB);dataGridView_Student.DataSource = BS;foreach (DataGridViewColumn col in dataGridView_Student.Columns){col.HeaderCell = new DataGridViewAutoFilterColumnHeaderCell(col.Heade rCell);}}break;default: { }break;}}break;……………..…………………………….default: { }break;}}break;default: { }break;}}//-----------------------------------------------------------------------------//配置文件:App.config//功能：包含数据库连接字段<?xml version="1.0"encoding="utf-8" ?><configuration><connectionStrings><add name="TSConnection"connectionString="Data Source=JALIN-PC;Initial Catalog=Teaching System;Persist Security Info=False;Integrated Security=SSPI"/></connectionStrings></configuration>//-----------------------------------------------------------------------------//所属类：Login//功能：登录界面及过程实现public partial class Login : Form{// Attributes Definitionprivate TSDatabase TSDB = new TSDatabase();private User CurrentUser = null;private TeachingSystem TS = null;private UserType UT = 0;private String ErrorMsg = "";// MethoDT Definitionpublic Login(){InitializeComponent();}///<summary>/// Login click/// Check input text and find user in user table, then login or not///</summary>///<param name="sender"></param>///<param name="e"></param>private void button_Login_MouseClick(object sender, MouseEventArgs e){if (textBox_User.Text != ""){// Loginif (FindLogin(textBox_User.Text, textBox_Password.Text)){GetUserType();CreateLoginedUser(textBox_User.Text, UT);this.Visible = false;LoadTS();}// Failed to loginelse{MessageBox.Show("Couldn't login.\nMaybe causes:\nYour username and password in valid\n."+ErrorMsg, "Login Failed", MessageBoxButtons.OK);textBox_Password.Text = "";}}else{MessageBox.Show("Please input your username and password first.", "Warning", MessageBoxButtons.OK);textBox_User.Text = "";textBox_Password.Text = "";}}///<summary>/// Login user according to user's input/// In essence, test if it matches to any row in user table record///</summary>///<param name="UserName"></param>///<param name="Password"></param>///<returns></returns>private bool FindLogin(String UserName, String Password){……………..}///<summary>/// Create Logined User///</summary>///<param name="UserName"></param>///<param name="UT"></param>///<returns>User</returns>private User CreateLoginedUser(String UserName, UserType UT){………………}///<summary>/// Judge which user type has been selected;///</summary>///<returns></returns>private UserType GetUserType(){if (radioButton_Student.Checked) UT = UserType.Student;if (radioButton_Teacher.Checked) UT = UserType.Teacher;if (radioButton_Admin.Checked) UT = UserType.Administrator;return UT;}///<summary>/// Create TeachingSystem and show///</summary>private void LoadTS(){// TS exists alreadyif (TS != null)TS.Dispose();TS = new TeachingSystem(CurrentUser, TSDB);TS.ShowLoginEvent += new TeachingSystem.ShowLoginHandler(ShowLogin); TS.CloseLoginEvent += new TeachingSystem.CloseLoginHandler(CloseLogin); TS.ShowTS();}///<summary>/// Set this visible, reset attibutes and clear password///</summary>public void ShowLogin(){…………….}///<summary>/// Close Login Form///</summary>public void CloseLogin(){this.Close();}}//-----------------------------------------------------------------------------//所属类：Query//功能：详细查询界面实现//说明：以动态查询相关的函数为例public partial class Query : Form{private GridViewType GVT = 0;private DataGridView DGV = null;private String Filter = "";private String SelectedColumn = "";public delegate void UpdateStatusHandler(DataGridView DGV);public UpdateStatusHandler UpdateStatusEvent;public Query(DataGridView DGV, GridViewType GVT){this.DGV = DGV;this.GVT = GVT;InitializeComponent();Filter = ((DataTable)((BindingSource)DGV.DataSource).DataSource).DefaultView.RowFilter; SelectedColumn = DGV.Columns[DGV.CurrentCell.ColumnIndex].Name;bel_Query.Text = SelectedColumn + ":";boBox1.SelectedIndex = 0;SetHighlight();this.Visible = true;DGV.Enabled = false;}private void SetHighlight(){if (DGV.Rows.Count > 0){DGV.CurrentCell = DGV.Rows[0].Cells[DGV.Columns[SelectedColumn].Index];DGV.SelectionMode = DataGridViewSelectionMode.FullColumnSelect;DGV.CurrentCell.Selected = true;}}private void GetFilter(){String Symbol = "";if (checkBox_Overlap.Checked == true){if (Filter != ""){Filter += " AND ";}// Exchange symbolSymbol = comboBox1.SelectedItem.ToString();if (Symbol == "Contain"){Symbol = "=";}// Build Filter Stringif (DateTime.Equals(((DataTable)((BindingSource)DGV.DataSource).DataSource).Columns [SelectedColumn].DataType, (new DateTime()).GetType())){Filter += SelectedColumn + " " + Symbol + " "+ "'" + Convert.ToDateTime(textBox_Query.Text) + "'";}else if (Single.Equals(((DataTable)((BindingSource)DGV.DataSource).DataSource).Column s[SelectedColumn].DataType, (new Single()).GetType())){Filter += SelectedColumn + " " + Symbol + " "+ "'" + Convert.ToSingle(textBox_Query.Text) + "'";}else if (Int32.Equals(((DataTable)((BindingSource)DGV.DataSource).DataSource).Column s[SelectedColumn].DataType, (new Int32()).GetType())){Filter += SelectedColumn + " " + Symbol + " "+ "'" + Convert.ToSingle(textBox_Query.Text) + "'";}else{if (comboBox1.SelectedItem.ToString() == Symbol){Filter += SelectedColumn + " " + Symbol + " "+ "'" + textBox_Query.Text + "'";}else{// comboBox1.SelectedText is "Contain"Filter += SelectedColumn + " LIKE " + "'*" + textBox_Query.Text + "*'";}}}else{// No overlap filter code…………..…………..}}private void textBox_Query_TextChanged(object sender, EventArgs e){// Query while inputtingif (checkBox_Dynamic.Checked == true){try{GetFilter();((DataTable)((BindingSource)DGV.DataSource).DataSource).DefaultView.RowFilter = Fi lter;SetHighlight();}catch (System.Exception ex){// NO ACTIONS for exception//MessageBox.Show(ex.ToString());}}}Recently,in the school at the beginning of the semester ，there is a symptoms called post-holidays syndrome spreading in high schools. The students who suffered from this symptoms can't adapt to the new environment，and they also can't put themselves in their study.There are many reasons for this phenomenon,i think the main reason is that they can't change themselves from the holiday in time.Beyond that,some student are too modesty and they don't know how to adjust to the new environment. They have a comfortable life at home in the vacation,and the life in school is boring and hard.In this case,i have some suggestion.First, student should learn to adjust their。