数据库课程设计报告(员工培训管理系统)

1、数据库系统课程设计课题 班级 学号 姓名 指导教师 时间：2015.6.152015.6.261 系统需求分析1.1系统功能分析员工培训系统需要实现的主要功能包括：l 企业总体培训课程的设置和安排。l 允许员工根据自己的情况选择合适的课程和上课时间。l 对选课结果进行统计报表。允许员工对最后选课结果的查询。l 培训考核成绩的输入和查询。l 员工培训效果的综合报表。l 员工个人信息的修改。图1、员工培训管理系统功能模块图1.2系统功能模块设计（划分）本系统涉及到员工和培训管理部门之间的交流，因此需要根据用户的不同分成两大功能模块。这两个模块的功能和使用的权限完全不同。本系统功能模块如图1所示。1

2、.3与其它系统的关系图2、培训管理数据流程图员工培训系统可以为员工素质技能的评价提供可靠的依据，是职务评定的一个参考信息源。系统本身需要用到人事管理系统中的员工基本信息和部门信息等辅助资料，这些数据可以通过数据库直接读取。1.4数据流程图员工培训管理系统的数据流程如图2所示。2 数据库设计2.1数据库需求分析根据系统数据流程图，我们可以列出以下系统所需的数据项和数据结构：l 课程设置：编号、名称、简介、所用教材、上课地点、人数、上课时间l 选课结果：记录编号、员工、课程、考核成绩、评价、考核日期。所需的外部数据支持：l 人员信息：员工号、密码、权限、姓名、部门、当前状态等。l 部门设置：部门编

3、号、名称等。2.2数据库概念结构设计图3是本系统所需数据的E-R模型图。图3、培训管理E-R图2.3数据库逻辑结构设计根据E-R图和数据库需求分析，培训管理系统需要创建2个主要的数据表：课程设置表和培训安排表。对应这两个表中的个别代码字段，又需要创建2个代码表：课程状态代码表和考核评价代码表。这4个数据表的结构如表1至表4所示。员工信息和部门信息作为外部数据支持可以使用人事管理系统中建立的数据表，如表5和表6所示。表1 COURSE 课程设置表字段名数据类型是否可空说明ID课程编号NAME课程名TEACHER任课教师（外部关键字PERSON）INTRO课程简介BOOK所用教材CLASSROOM

4、上课地点NUMBER课程上限人数CLASSTIME开课时间STATE状态（外部关键字COURSE_STATE）表2 TRAINING_PLAN培训安排表字段名数据类型是否可空说明ID编号PERSON员工（外部关键字PERSON）COURSE课程SCORE成绩APPRAISEMENT评价（外部关键字APPRAISEMENT）EXAM_DATA考核日期表3 COURSE_STATE课程状态代码表字段名数据类型是否可空说明CODE状态代码DESCRIPTION描述表4 APPRISEMENT考核评价代码表字段名数据类型是否可空说明CODE评价代码DESCRIPTION描述表5 PERSON员工个人信

5、息表字段名数据类型是否可空说明ID员工号（主关键字）PASSWD密码AUTHORITY用户权限NAME姓名SEX性别BIRTHDAY生日DEPARTMENT所在部门JOB职务EDU_LEBEL受教育程度SPECIATY专业技能ADDRESS家庭住址TEL联系电话EMAIL电子信箱STATE当前状态（T-员工，F-非员工）REMARK备注表6 DEPARTMENT部门信息表字段名数据类型是否可空说明ID部门编号NAME部门名称MANAGER部门经理INTRO简介2.4数据库的建立2.4.1数据库的建立2.4.2初始数据的输入本系统中，初始数据包括课程状态代码和评价代码，如表7至表8所示。表7 课

6、程状态代码代码说明0选课中1进行中2已结束表8 考核评价代码代码说明0未考核1不及格2及格3良好4优秀（以下各部分请设计者完成）。3 各功能模块的设计与实现3.1功能说明本管理系统主要分为两大部分：培训管理应用程序和学员选课应用程序。培训管理应用程序主要用于培训中心的管理人员对培训课程和培训情况进行维护。此应用程序主要包括四项功能：课程设置、选课结果查询修改、成绩输入、培训成绩统计报表。另外，系统需要有登录窗口(用于权限认证)和导航窗口(用于连接各项功能)。学员选课应用程序包括个人信息修改、选课和成绩查询三项功能。（一）培训管理管理应用程序功能说明（二）学员选课应用程序功能说明3.2用户界面设

7、计完成数据库创建和功能说明以后，我们可以进行下一步工作，既设计用户界面。1、培训管理应用程序登录窗体的创建2、培训管理应用程序主窗体的创建3、课程设置窗体的创建4、选课结果查询窗体的创建5、学员名单报表窗体的创建6、考核评定结果窗体的创建7、培训统计窗体的创建8、培训成绩报表窗体的创建9、学员选课客户端界面的创建3.3各功能模块的实现1、培训管理应用程序数据模块的创建2、培训管理应用程序登录程序的实现3、课程设置模块的实现4、选课结果查询的实现5、学员名单报表的实现6、考核评定结果的实现7、培训统计的实现8、培训成绩报表的实现9、学员选课客户端应用程序的创建4 系统实现基本方法：通过一个ado

8、控件与数据库进行连接，一个datagrid控件显示表信息。1 edit控件对应表的字段。button按钮对应“查询”、“更新”、“删除”和“添加”。4.1登陆界面:/登陆按钮功能/*1.首先连接数据库2.通过sql语句获得PERSON表的信息，得到与输入账号名相对应的账号密码（sql.Format(select * from person where P_ID = %s and P_PASSWD = %s,this-m_id,this-m_password);）3.判断输入的密码与从数据库中得到的密码是否相一致4. 得到与输入账号名相对应的账号权限5.判断用户的身份，选择进入不同的功能界面*/

9、void CEmploymentDlg:OnButton1() this-UpdateData(true);/连接数据库UpdateData(TRUE);_ConnectionPtr m_pConnection;:CoInitialize(NULL);trym_pConnection.CreateInstance(ADODB.Connection);_bstr_t strConn=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Employee training m

10、anagement system;Data Source=520-002;m_pConnection-Open(strConn,adModeUnknown);catch(_com_error e):CoUninitialize();AfxMessageBox(e.ErrorMessage();return ;/定义sql查询CString sql;variant_t RecordsAffected; _RecordsetPtr m_pRecordset; sql.Format(select * from person where P_ID = %s and P_PASSWD = %s,this

11、-m_id,this-m_password);/执行SQL查询m_pRecordset = m_pConnection-Execute(_bstr_t)sql,&RecordsAffected,adCmdText);/如果找到if(!m_pRecordset-adoEOF) RecordsAffected=m_pRecordset-GetCollect(p_authority);CString authority=(LPCSTR)_bstr_t(RecordsAffected);/int i=(int)RecordsAffected.iVal;if(authority=employee)thi

12、s-ShowWindow(SW_HIDE);id=this-m_id;employer dlg;dlg.DoModal();this-ShowWindow(SW_SHOW);else this-ShowWindow(SW_HIDE);teacher dlg;dlg.DoModal();this-ShowWindow(SW_SHOW);else MessageBox(用户名或密码有误);/退出按钮功能，直接调用exit(0)退出整个程序void CEmploymentDlg:OnButton2() exit(0);/*1.这个界面主要是有三个按钮，用来选择进入不同的功能区*/void emplo

13、yer:OnButton1() this-ShowWindow(SW_HIDE);/当前界面隐藏employer_chioce dlg; /需要添加该C*Dlg.h头文件，否则编译通不过dlg.DoModal();this-ShowWindow(SW_SHOW);void employer:OnButton2() this-ShowWindow(SW_HIDE);/当前界面隐藏employer_score dlg; /需要添加该C*Dlg.h头文件，否则编译通不过dlg.DoModal();this-ShowWindow(SW_SHOW);void employer:OnButton3() t

14、his-ShowWindow(SW_HIDE);/当前界面隐藏employer_information dlg; /需要添加该C*Dlg.h头文件，否则编译通不过dlg.DoModal();this-ShowWindow(SW_SHOW);void employer:OnButton4() exit(0);/*点击显示个人课程，会显示出个人课程这部份功能主要有：1.查找2.增加3.删除 */增加选择课程/*1.连接数据库2.定义sql1，sql2用于获得信息，和添加课程sql.Format(select * from course where C_ID = %s,this-m_add_id);

15、sql2.Format(SELECT C_NAME,COUNT(C_ID) as num FROM TRAINING_PLAN,PERSON,COURSE WHERE P_ID=T_PERSON and C_ID=T_COURSE GROUP BY C_NAME);3.差错控制选课人数与选课上限人数4.其余的功能和增加功能只是sql语句的差别，就不详细描述了*/void employer_chioce:OnButton1() UpdateData(TRUE);_ConnectionPtr m_pConnection;:CoInitialize(NULL);trym_pConnection.Cr

16、eateInstance(ADODB.Connection);_bstr_tstrConn = Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Employee training management system;Data Source=520-002;m_pConnection-Open(strConn,adModeUnknown); catch(_com_error e):CoUninitialize();AfxMessageBox(e.ErrorMessag

17、e();return;/定义sql查询CString sql,sql2;variant_t RecordsAffected,RecordsAffected2;_RecordsetPtr m_pRecordset,m_pRecordset2; sql.Format(select * from course where C_ID = %s,this-m_add_id);sql2.Format(SELECT C_NAME,COUNT(C_ID) as num FROM TRAINING_PLAN,PERSON,COURSE WHERE P_ID=T_PERSON and C_ID=T_COURSE

18、GROUP BY C_NAME);/执行SQL查询m_pRecordset = m_pConnection-Execute(_bstr_t)sql,&RecordsAffected,adCmdText);m_pRecordset2 = m_pConnection-Execute(_bstr_t)sql2,&RecordsAffected2,adCmdText);RecordsAffected=m_pRecordset-GetCollect(C_NUMBER);CString number=(LPCSTR)_bstr_t(RecordsAffected);RecordsAffected2=m_p

19、Recordset2-GetCollect(num);CString num=(LPCSTR)_bstr_t(RecordsAffected2);if(numExecute(strAdd,&ra,adCmdText);m_pConnection-Close();/以下显示当前数据CString strSQL=SELECT P_NAME,C_ID,C_NAME,C_TEACHER,C_CLASSROOM,C_CLASSTIME,C_STATE,T_EXAM_DATE FROM TRAINING_PLAN,PERSON,COURSE,APPRISEMENT WHERE P_ID=T_PERSON

20、AND C_ID=T_COURSE AND A_CODE=T_APPRISEMENT and T_PERSON= +id;m_adodc2.SetRecordSource(strSQL);m_adodc2.Refresh();m_datagrid2.ShowWindow(SW_SHOW);AfxMessageBox(选课成功);UpdateData(FALSE);else AfxMessageBox(选课失败,选课人数已达到上限!);/显示个人选择课程void employer_chioce:OnButton4() / TODO: Add your control notification h

21、andler code hereUpdateData(TRUE);CString tmpStr,strSQL=SELECT P_NAME,C_ID,C_NAME,C_TEACHER,C_CLASSROOM,C_CLASSTIME,C_STATE,T_EXAM_DATE FROM TRAINING_PLAN,PERSON,COURSE,APPRISEMENT WHERE P_ID=T_PERSON AND C_ID=T_COURSE AND A_CODE=T_APPRISEMENT and T_PERSON= +id;m_adodc2.SetRecordSource(strSQL);m_adod

22、c2.Refresh();m_datagrid2.ShowWindow(SW_SHOW);UpdateData(FALSE);/删除选择课程void employer_chioce:OnButton2() / TODO: Add your control notification handler code hereUpdateData(TRUE);_ConnectionPtr m_pConnection;:CoInitialize(NULL);trym_pConnection.CreateInstance(ADODB.Connection);_bstr_tstrConn = Provider=

23、SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Employee training management system;Data Source=520-002;m_pConnection-Open(strConn,adModeUnknown); catch(_com_error e):CoUninitialize();AfxMessageBox(e.ErrorMessage();return;/以下插入数据_variant_t ra;_bstr_t strAdd=delete fro

24、m training_plan where t_person =+id+ and t_course = +m_delete_id+;/AfxMessageBox(strAdd); return;m_pConnection-Execute(strAdd,&ra,adCmdText);m_pConnection-Close();/以下显示当前数据CString tmpStr,strSQL=SELECT P_NAME,C_ID,C_NAME,C_TEACHER,C_CLASSROOM,C_CLASSTIME,C_STATE,T_EXAM_DATE FROM TRAINING_PLAN,PERSON,

25、COURSE,APPRISEMENT WHERE P_ID=T_PERSON AND C_ID=T_COURSE AND A_CODE=T_APPRISEMENT and T_PERSON= +id;m_adodc2.SetRecordSource(strSQL);m_adodc2.Refresh();m_datagrid2.ShowWindow(SW_SHOW);AfxMessageBox(删除课程成功);UpdateData(FALSE);/查找课程IDvoid employer_chioce:OnButton5() UpdateData(TRUE);CString str=select

26、* from course where c_name like %+m_name+%;m_adodc1.SetRecordSource(str);m_adodc1.Refresh();UpdateData(FALSE);void employer_chioce:OnButton6() exit(0);void employer_chioce:OnButton7() this-ShowWindow(SW_HIDE);employer dlg;dlg.DoModal();this-ShowWindow(SW_SHOW);void employer_information:OnButton1() U

27、pdateData(TRUE);CString strSQL=SELECT * from PERSON where P_ID= +id;m_adodc1.SetRecordSource(strSQL);m_adodc1.Refresh();m_datagrid1.ShowWindow(SW_SHOW);UpdateData(FALSE);void employer_information:OnButton2() MessageBox(更新个人信息成功!);UpdateData(TRUE);_variant_t ra;_bstr_t strAdd=UPDATE PERSON SET ;strAd

28、d=strAdd+ p_passwd = +m_passwd+,;strAdd=strAdd+ P_NAME = +m_name+,;strAdd=strAdd+ p_sex = +m_sex+,;strAdd=strAdd+ p_birahday = +m_birahday+,;strAdd=strAdd+ p_edu_level = +m_edu_level+,;strAdd=strAdd+ p_speciaty = +m_speciaty+,;strAdd=strAdd+ p_address = +m_address+,;strAdd=strAdd+ p_tel = +m_tel+,;s

29、trAdd=strAdd+ p_email = +m_email+,;strAdd=strAdd+ p_remark = +m_pemark+;strAdd=strAdd+ where+ P_ID = +id+;m_adodc1.SetRecordSource(strAdd);m_adodc1.Refresh();MessageBox(更新个人信息成功!);UpdateData(FALSE);BEGIN_EVENTSINK_MAP(employer_information, CDialog) /AFX_EVENTSINK_MAP(employer_information)ON_EVENT(em

30、ployer_information, IDC_DATAGRID1, -601 /* DblClick */, OnDblClickDatagrid1, VTS_NONE)/AFX_EVENTSINK_MAPEND_EVENTSINK_MAP()void employer_information:OnDblClickDatagrid1() UpdateData(TRUE);int ColNum = 2;CString colVal;CColumns cols; /要包含的文件头COlumns.hCColumn col; /要包含的文件头COlumn.hVARIANT v_ColNum,v_Va

31、lue;cols = m_datagrid1.GetColumns(); /调用GetColumns 返回CDataGrid对应的CColumns对象v_ColNum.vt = VT_I2;v_ColNum.iVal = 1; /设置列编号col = cols.GetItem(v_ColNum);/根据列编号返回CColumn 对象v_Value = col.GetValue();/读取列值m_passwd = v_Value.bstrVal;v_ColNum.iVal = 3;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_na

32、me = v_Value.bstrVal;v_ColNum.iVal = 4;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_sex = v_Value.bstrVal;v_ColNum.iVal = 5;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_birahday = v_Value.bstrVal;v_ColNum.iVal = 8;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_edu_leve

33、l = v_Value.bstrVal;v_ColNum.iVal = 9;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_speciaty = v_Value.bstrVal;v_ColNum.iVal = 10;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_address = v_Value.bstrVal;v_ColNum.iVal = 11;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_tel

34、 = v_Value.bstrVal;v_ColNum.iVal = 12;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_email = v_Value.bstrVal;v_ColNum.iVal = 14;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_pemark = v_Value.bstrVal;UpdateData(FALSE);void employer_information:OnButton4() this-ShowWindow(SW_HIDE);e

35、mployer dlg;dlg.DoModal();this-ShowWindow(SW_SHOW);void employer_information:OnButton6() exit(0);void teacher:OnButton1() this-ShowWindow(SW_HIDE);tercher_course dlg;dlg.DoModal();this-ShowWindow(SW_SHOW);void teacher:OnButton2() this-ShowWindow(SW_HIDE);tercher_score dlg;dlg.DoModal();this-ShowWind

36、ow(SW_SHOW);void teacher:OnButton3() this-ShowWindow(SW_HIDE);tercher_result dlg;dlg.DoModal();this-ShowWindow(SW_SHOW);void teacher:OnButton4() this-ShowWindow(SW_HIDE);tercher_all dlg;dlg.DoModal();this-ShowWindow(SW_SHOW);void teacher:OnButton5() exit(0);/新增void tercher_course:OnButton1() UpdateD

37、ata(TRUE);_ConnectionPtr m_pConnection;:CoInitialize(NULL);trym_pConnection.CreateInstance(ADODB.Connection);_bstr_tstrConn=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Employee training management system;Data Source=520-002;m_pConnection-Open(strConn,adMo

38、deUnknown);catch(_com_error e):CoUninitialize();AfxMessageBox(e.ErrorMessage();return;_variant_t ra;_bstr_t strAdd=INSERT INTO COURSE VALUES(;strAdd=strAdd+m_c_id+,;strAdd=strAdd+m_c_name+,;strAdd=strAdd+m_c_teacher+,;strAdd=strAdd+m_c_intro+,;strAdd=strAdd+m_c_book+,;strAdd=strAdd+m_c_classroom+,;s

39、trAdd=strAdd+m_c_number+,;strAdd=strAdd+m_c_classtime+,;strAdd=strAdd+m_c_state+);/清空输入框m_c_id= ;m_c_name= ;m_c_teacher= ;m_c_intro= ;m_c_book= ;m_c_classroom= ;m_c_number= ;m_c_classtime= ;m_c_state= ;m_pConnection-Execute(strAdd,&ra,adCmdText);m_pConnection-Close();m_adodc1.SetRecordSource(select

40、* from COURSE);m_adodc1.Refresh();UpdateData(FALSE);/修改void tercher_course:OnButton2() UpdateData(TRUE);_ConnectionPtr m_pConnection;:CoInitialize(NULL);/在ADO操作中建议语句中要常用到try.catch()来捕获错误信息/因为它有时会经常出现一些想不到的错误trym_pConnection.CreateInstance(ADODB.Connection);_bstr_tstrConn = Provider=SQLOLEDB.1;Integr

41、ated Security=SSPI;Persist Security Info=False;Initial Catalog=Employee training management system;Data Source=520-002;m_pConnection-Open(strConn,adModeUnknown); catch(_com_error e):CoUninitialize();AfxMessageBox(e.ErrorMessage();return;/以下插入数据_variant_t ra;_bstr_t strAdd=UPDATE COURSE SET ;strAdd=s

42、trAdd+ C_NAME = +m_c_name+,;strAdd=strAdd+ C_TEACHER = +m_c_teacher+,;strAdd=strAdd+ C_INTRO = +m_c_intro+,;strAdd=strAdd+ C_BOOK = +m_c_book+,;strAdd=strAdd+ C_CLASSROOM = +m_c_classroom+,;strAdd=strAdd+ C_NUMBER = +m_c_number+,;strAdd=strAdd+ C_CLASSTIME = +m_c_classtime+,;strAdd=strAdd+ C_STATE =

43、 +m_c_state+;strAdd=strAdd+ where+ C_ID = +m_c_id+;m_pConnection-Execute(strAdd,&ra,adCmdText);m_pConnection-Close();/以下显示当前数据m_adodc1.SetRecordSource( SELECT * FROM COURSE );m_adodc1.Refresh();AfxMessageBox(成功更新数据);UpdateData(FALSE);/删除void tercher_course:OnButton4() UpdateData(TRUE);_ConnectionPtr

44、 m_pConnection;:CoInitialize(NULL);trym_pConnection.CreateInstance(ADODB.Connection);_bstr_tstrConn=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Employee training management system;Data Source=520-002;m_pConnection-Open(strConn,adModeUnknown);catch(_com_er

45、ror e):CoUninitialize();AfxMessageBox(e.ErrorMessage();return ;_variant_t ra;_bstr_t strAdd=delete from COURSE where C_ID=;strAdd=strAdd+m_c_id;m_pConnection-Execute(strAdd,&ra,adCmdText);m_pConnection-Close();m_adodc1.SetRecordSource(select * from COURSE);m_adodc1.Refresh();m_c_id= ;m_c_name= ;m_c_

46、teacher= ;m_c_intro= ;m_c_book= ;m_c_classroom= ;m_c_number= ;m_c_classtime= ;m_c_state= ;UpdateData(FALSE);BEGIN_EVENTSINK_MAP(tercher_course, CDialog) /AFX_EVENTSINK_MAP(tercher_course)ON_EVENT(tercher_course, IDC_DATAGRID1, -601 /* DblClick */, OnDblClickDatagrid1, VTS_NONE)/AFX_EVENTSINK_MAPEND_

47、EVENTSINK_MAP()void tercher_course:OnDblClickDatagrid1() / TODO: Add your control notification handler code hereUpdateData(TRUE);int ColNum = 2;CString colVal;CColumns cols; /要包含的文件头COlumns.hCColumn col; /要包含的文件头COlumn.hVARIANT v_ColNum,v_Value;cols = m_datagrid1.GetColumns(); /调用GetColumns 返回CDataG

48、rid对应的CColumns对象v_ColNum.vt = VT_I2;v_ColNum.iVal = 0;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_c_id = v_Value.bstrVal;v_ColNum.iVal = 1;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_c_name = v_Value.bstrVal;v_ColNum.iVal = 2;col = cols.GetItem(v_ColNum);v_Value = col.GetValu

49、e();m_c_teacher = v_Value.bstrVal;v_ColNum.iVal = 3;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_c_intro = v_Value.bstrVal;v_ColNum.iVal = 4;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_c_book = v_Value.bstrVal;v_ColNum.iVal = 5;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_c_classroom = v_Value.bstrVal;v_ColNum.iVal = 6;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_c_number = v_Value.bstrVal;v_ColNum.iVal = 7;col = cols.GetItem(v_ColNum);v_Value = col.GetValue();m_c