数据库实训201310913112谷云龙27页

1、河南工程学院数据库系统原理与应用课程设计成果报告酒店管理系统设计与开发学生学号： 201310913112 学生姓名： 谷云龙 专业班级： 计算机科学与技术1341 学 院： 计算机学院 专业课程： 数据库系统原理与应用 指导教师： 李华 2015年 12月 31日课程设计指导教师评分表课程设计题目酒店管理系统设计与开发成员姓名学号任务分工谷云龙201310913112C+与数据库的连接建立赵非鹏201310913113数据库的创建前的分析与E-R图的绘制蒋永浩201310913126数据库的创建和与数据库相关操作的实现评分项目及分值要素成绩评定项 目最高分评 分 要 素成 绩1学习态度20上

2、机出勤及端正的学习态度、认真刻苦程度，工作量饱满程度等2软件设计质量40采用的算法、设计方案，设计结果，界面友好等方面进行综合评定3答辩过程20能简明扼要地阐述设计的主要内容，能准确流利地回答各种问题4设计报告书写10内容按要求步骤书写，条理清晰，表述清楚、措词得当5实际动手能力10熟练使用开发工具，能够迅速准确的进行调试、纠错和运行；组长全面负责，承担复杂的模块设计分值就高总 成 绩等 级评语目 录一、需求分析1二、概念结构设计1三、逻辑结构设计1四、数据表设计及数据库表3五、数据库设计实现及运行5（一）数据库的创建5（二）数据表的创建（可同时在创建时定义相关的约束）5（三）自行设计各模块中

3、所涉及的操作语句51插入数据操作5六、系统详细设计及实现19七、实训总结及心得体会24I酒店管理系统设计与开发一、 需求分析（一）.用户的需求具体体现在各种信息的提供、保存、更新和查询等方面，这就要求数据库结构能充分满足各种信息的输出和输入。收集基本数据、数据结构以及数据处理的流程，得出本系统所处理的数据流程。（二）.针对一般酒店客房信息管理系统的需求，内容和流程分析，设计下面的数据项和数据结构：（三）.员工标准信息，包括的数据项有：员工编号，姓名，性别，出生日期，工龄，密码，电话，备注等。（四）.客户标准信息，包括的数据项有：客户编号，性名，性别，出生日期，身份证号，手机，备注等。（五）.客

4、房标准信息，包括的数据项有：客房编号，类型编号，状态，备注等。 类型标准信息，包括的数据项有：类型编号，类型名称，费用，总量，剩余量，备注等。（六）.订单标准信息，包括的数据项有：订单编号，员工编号，客户编号，客房编号，时间，押金，备注等。（七）.退房标准信息，包括的数据项有：员工编号，客户编号，客房编号，时间，备注等。二、概念结构设计本系统根据以上的设计规划出的实体有：客房标准信息实体、客房信息实体、订房信息实体、结算信息实体。各个实体具体的描述-图如下：客房标准信息实体标准编号标准名称床位数量客房单价图-1客房标准信息实体-图客房信息实体客房标准客房编号客房位置客房单价图-2客房信息实体-

5、图订房信息实体客房信息顾客信息折 扣入住时间图-3订房信息实体-图结算信息实体客房信息顾客信息结算时间结算金额图-4结算信息实体-图客房标准信息客房登记客房信息管理订 房订房信息管理结算信息管理客房结算图-5实体之间关系-图三、逻辑结构设计根据概念设计的结果,将其转换为关系模型：客房（客房编号，客房位置，客房单价，客房床位数）订房（客房信息，顾客信息，入住时间，折扣）结算信息（客房信息，顾客信息，结算时间，结算金额）四、数据库物理结构设计首先将宾馆管理信息系统的数据库概念结构转化为SQL 2005数据库系统所支持的实际数据模型，即：数据库的物理结构。其各个表格的设计结果如下Rooms表的结构

6、字段名称数据类型长度主键是否为空功能描述RoomNOVarchar50是否房间号RoomtypeVarchar50否否房间类型PositionVarchar50否否房间位置PriceVarchar50否否房间价格PutupVarchar50否否是否预定MemoVarchar50否否备注 Roomtype表的结构 字段名称数据类型长度主键是否为空功能描述TypeidVarchar50否否类型编号TypenameVarchar50是否类型名称AreaVarchar50否否房间面积BednumVarchar50否否床位数HairconditionVarchar50否否是否有空调HtelephoneV

7、archar50否否是否有电话HtelevitionVarchar50否否是否有电视HtoiletVarchar50否否是否有卫生间PriceVarchar50否否价格 Bookin表的结构字段名称数据类型长度主键是否为空功能描述BooknoVarchar50否否订房编号CustomnameVarchar50否否客户名CustomIDVarchar50否否身份证RoomnoVarchar50否否房间号IndateVarchar50否否入住日期DiscountVarchar50否否折扣InmemoVarchar50否可以空入住备注CheckdateVarchar50否否结账时间AmmountVa

8、rchar50否否租金 rooms表记录 Room type表记录五、数据库设计实现及运行（一）数据库的创建T-SQL语句：（二）数据表的创建（可同时在创建时定义相关的约束）T-SQL语句：（三）自行设计各模块中所涉及的操作语句1插入数据操作订房查询模块：void CSearchBookInDLG:OnOK() / TODO: Add extra validation hereUpdateData(true);m_sName.TrimRight(" ");m_sRoomNo.TrimRight(" "); if ( !(m_bName|m_bRoomNo

9、|m_bInDate) )AfxMessageBox(_T("请选择查询条件！"), MB_ICONEXCLAMATION);return;if ( m_bName && ""=m_sName )AfxMessageBox(_T("顾客姓名不能为空"), MB_ICONEXCLAMATION); return; if ( m_bRoomNo && ""=m_sRoomNo )AfxMessageBox(_T("客房编号不能为空"), MB_ICONEXCLAMAT

10、ION); return;CString sTime = m_tInDate.Format("%Y-%m-%d");if ( !m_bName ) m_sName = "%"if ( !m_bRoomNo ) m_sRoomNo = "%" _variant_t strQuery;if ( !m_bInDate ) strQuery = "select * from bookin where customname like '"+m_sName+"' and roomno like 

11、9;"+m_sRoomNo+"' and checkdate is NULL" else strQuery = "select * from bookin where customname like '"+m_sName+"' and roomno like '"+m_sRoomNo+"' and indate='"+sTime+"' and checkdate is NULL"CHotel_MISView* p = (CHote

12、l_MISView*)(CMainFrame*)AfxGetMainWnd()->GetActiveView();p->RefreshBookIn(strQuery);增加客房标准的实现代码：void CRoomTypeDLG:OnOK() / TODO: Add extra validation hereUpdateData(true);m_sTypeID.TrimRight(" "); m_sTypeName.TrimRight(" ");m_sArea.TrimRight(" ");m_sBedNo.TrimRigh

13、t(" ");m_sPrice.TrimRight(" ");/ Make sure all needed info is availableCString sWarning=""if ( ""=m_sTypeID ) sWarning=_T("标准编号"); else if ( ""=m_sTypeName ) sWarning=_T("标准名称");else if ( ""=m_sArea ) sWarning=_T("客

14、房面积");else if ( ""=m_sBedNo ) sWarning=_T("床位数量");else if ( ""=m_sPrice ) sWarning=_T("住房单价"); if ( ""!=sWarning ) sWarning += _T("不能为空"); AfxMessageBox(sWarning, MB_ICONEXCLAMATION); return; / Make sure the Number info is valid float

15、fArea = atof(m_sArea);if ( 0=fArea ) AfxMessageBox(_T("客房面积：请输入非零数字"), MB_ICONEXCLAMATION); return;m_sArea.Format("%.2f", fArea);float fPrice = atof(m_sPrice);if ( 0=fPrice ) AfxMessageBox(_T("住房单价：请输入非零数字"), MB_ICONEXCLAMATION); return;m_sPrice.Format("%.2f",

16、 fPrice);if ( atof(m_sPrice)>99999999.99 ) AfxMessageBox(_T("住房单价溢出，请不要超过99999999.99"), MB_ICONEXCLAMATION);return;_variant_t strQuery;if (m_bAppend)/ Judge Room Type is Unique strQuery = "select * from roomtype where typeid='"+m_sTypeID+"'" theApp.ADOExecute

17、(theApp.m_pADOSet, strQuery); int iCount = theApp.m_pADOSet->GetRecordCount(); if ( 0!=iCount ) AfxMessageBox(_T("已经存在此标准编号的记录！"), MB_ICONEXCLAMATION); return; / Judge Room Name is Unique strQuery = "select * from roomtype where typeid!='"+m_sTypeID+"' and typenam

18、e='"+m_sTypeName+"'" theApp.ADOExecute(theApp.m_pADOSet, strQuery); iCount = theApp.m_pADOSet->GetRecordCount(); if ( 0!=iCount ) AfxMessageBox(_T("已经存相同客房标准的记录！"), MB_ICONEXCLAMATION); return;测试结果粘贴：图-6订房信息的实现代码：void CBookInDLG:OnOK() / TODO: Add extra validation

19、 hereUpdateData(true);m_sName.TrimRight(" "); m_sID.TrimRight(" ");m_sDiscount.TrimRight(" ");m_sMemo.TrimLeft(" ");/ Make sure all needed info is availableCString sWarning=""if ( ""=m_sName ) sWarning=_T("顾客姓名"); else if ( "

20、"=m_sID ) sWarning=_T("身份证号码");else if ( ""=m_sDiscount ) sWarning=_T("折扣");if ( ""!=sWarning ) sWarning += _T("不能为空"); AfxMessageBox(sWarning, MB_ICONEXCLAMATION); return;float fDiscount=atof(m_sDiscount);if ( 0=fDiscount ) AfxMessageBox(_T(&qu

21、ot;折扣：请输入非零数字"), MB_ICONEXCLAMATION); return;m_sDiscount.Format("%.2f", fDiscount);if ( fDiscount>100 ) AfxMessageBox(_T("折扣溢出，请不要超过100"), MB_ICONEXCLAMATION);return;_variant_t strQuery;/ Delete Old Recordif ( !m_bAppend )strQuery = "delete from bookin where customID

22、='"+m_sOldID+"' and roomno='"+m_sOldRoomNo+"'" theApp.ADOExecute(theApp.m_pADOSet, strQuery);测试结果粘贴：图-7结算信息的实现代码：void CCheckOutDLG:DoDataExchange(CDataExchange* pDX)CDialog:DoDataExchange(pDX);/AFX_DATA_MAP(CCheckOutDLG)DDX_Text(pDX, IDD_CHECKOUT_DISCOUNT, m_

23、sDiscount);DDX_Text(pDX, IDD_CHECKOUT_ID, m_sID);DDX_Text(pDX, IDD_CHECKOUT_INDATE, m_sInDate);DDX_Text(pDX, IDD_CHECKOUT_MEMO, m_sMemo);DDX_Text(pDX, IDD_CHECKOUT_NAME, m_sName);DDX_DateTimeCtrl(pDX, IDD_CHECKOUT_OUTDATE, m_tOutDate);DDX_Text(pDX, IDD_CHECKOUT_POSITION, m_sPosition);DDX_Text(pDX, I

24、DD_CHECKOUT_PRICE, m_sPrice);DDX_CBString(pDX, IDD_CHECKOUT_ROOMNO, m_sRoomNo);DDX_Text(pDX, IDD_CHECKOUT_TYPE, m_sType);/AFX_DATA_MAPBEGIN_MESSAGE_MAP(CCheckOutDLG, CDialog)/AFX_MSG_MAP(CCheckOutDLG)ON_CBN_SELCHANGE(IDD_CHECKOUT_ROOMNO, OnCheckoutRoomNoSelChange)/AFX_MSG_MAPEND_MESSAGE_MAP()/ CChec

25、kOutDLG message handlersBOOL CCheckOutDLG:OnInitDialog() CDialog:OnInitDialog();/ TODO: Add extra initialization here_variant_t strQuery, Holder;if (m_bAppend)/ Add CheckOut CString sRoomNo; / Init ComboboxstrQuery = "select * from rooms where roomNO in ( select roomno from bookin where checkda

26、te is NULL )" theApp.ADOExecute(theApp.m_pADOSet, strQuery); int iCount = theApp.m_pADOSet->GetRecordCount();theApp.m_pADOSet->MoveFirst();for (int i=0; i<iCount; i+) Holder = theApp.m_pADOSet->GetCollect("roomno"); (CComboBox*)GetDlgItem(IDD_CHECKOUT_ROOMNO)->InsertStr

27、ing(i, Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder); if ( 0=i )sRoomNo = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder; / Get RoomTypeHolder = theApp.m_pADOSet->GetCollect("roomtype");m_sType = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder; / Get roompositionHo

28、lder = theApp.m_pADOSet->GetCollect("roomposition");m_sPosition = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder; / Get roompriceHolder = theApp.m_pADOSet->GetCollect("roomprice");m_sPrice.Format("%.2f", Holder.dblVal);theApp.m_pADOSet->MoveNext(); (CCom

29、boBox*)GetDlgItem(IDD_BOOKIN_ROOMNO)->SetCurSel(0); / Get customer infostrQuery = "select * from bookin where roomno='"+sRoomNo+"'" theApp.ADOExecute(theApp.m_pADOSet, strQuery); / Get nameHolder = theApp.m_pADOSet->GetCollect("customname"); m_sName = Hold

30、er.vt=VT_NULL?"":(char*)(_bstr_t)Holder;/ Get ID Holder = theApp.m_pADOSet->GetCollect("customID"); m_sID = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder;/ Get InDate COleDateTime time;Holder = theApp.m_pADOSet->GetCollect("indate"); time = Holder;m_sInDat

31、e = time.Format("%Y-%m-%d");/ Get ID Holder = theApp.m_pADOSet->GetCollect("discount"); m_sDiscount.Format("%.2f", Holder.vt=VT_NULL?0:Holder.dblVal); / Get MemoHolder = theApp.m_pADOSet->GetCollect("inmemo"); m_sMemo = Holder.vt=VT_NULL?"":(ch

32、ar*)(_bstr_t)Holder;else/ Alter CheckOut(CComboBox*)GetDlgItem(IDD_CHECKOUT_ROOMNO)->InsertString(0, m_sRoomNo); / Get Room InfostrQuery = "select * from rooms where roomNO='"+m_sRoomNo+"'" theApp.ADOExecute(theApp.m_pADOSet, strQuery); int iCount = theApp.m_pADOSet-&g

33、t;GetRecordCount();theApp.m_pADOSet->MoveFirst(); / Get RoomTypeHolder = theApp.m_pADOSet->GetCollect("roomtype"); m_sType = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder; / Get roompositionHolder = theApp.m_pADOSet->GetCollect("roomposition");m_sPosition = Holde

34、r.vt=VT_NULL?"":(char*)(_bstr_t)Holder; / Get roompriceHolder = theApp.m_pADOSet->GetCollect("roomprice");m_sPrice.Format("%.2f", Holder.dblVal); (CComboBox*)GetDlgItem(IDD_BOOKIN_ROOMNO)->SetCurSel(0);UpdateData(false);return TRUE; / return TRUE unless you set th

35、e focus to a control / EXCEPTION: OCX Property Pages should return FALSEvoid CCheckOutDLG:OnCheckoutRoomNoSelChange() / TODO: Add your control notification handler code hereUpdateData(true);_variant_t strQuery, Holder;/ Update room infostrQuery = "select * from rooms where roomNO='"+m_

36、sRoomNo+"'"theApp.ADOExecute(theApp.m_pADOSet, strQuery); / Get RoomTypeHolder = theApp.m_pADOSet->GetCollect("roomtype");m_sType = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder; / Get roompositionHolder = theApp.m_pADOSet->GetCollect("roomposition");m

37、_sPosition = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder; / Get roompriceHolder = theApp.m_pADOSet->GetCollect("roomprice");m_sPrice.Format("%.2f", Holder.dblVal); / Update customer info strQuery = "select * from bookin where roomno='"+m_sRoomNo+"&

38、#39;"theApp.ADOExecute(theApp.m_pADOSet, strQuery); / Get nameHolder = theApp.m_pADOSet->GetCollect("customname");m_sName = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder;/ Get ID Holder = theApp.m_pADOSet->GetCollect("customID");m_sID = Holder.vt=VT_NULL?"

39、":(char*)(_bstr_t)Holder; / Get InDate COleDateTime time;Holder = theApp.m_pADOSet->GetCollect("indate");time = Holder;m_sInDate = time.Format("%Y-%m-%d"); / Get ID Holder = theApp.m_pADOSet->GetCollect("discount");m_sDiscount.Format("%.2f", Holder.

40、vt=VT_NULL?0:Holder.dblVal); / Get MemoHolder = theApp.m_pADOSet->GetCollect("inmemo");m_sMemo = Holder.vt=VT_NULL?"":(char*)(_bstr_t)Holder; UpdateData(false);void CCheckOutDLG:OnOK() / TODO: Add extra validation here UpdateData(true);/ Get InDate_variant_t strQuery, Holder;C

41、OleDateTime tInDate;strQuery = "select indate from bookin where roomno='"+m_sRoomNo+"'"theApp.ADOExecute(theApp.m_pADOSet, strQuery); Holder = theApp.m_pADOSet->GetCollect("indate");tInDate = Holder;if ( tInDate>m_tOutDate )AfxMessageBox(_T("结帐日期小于入住日

42、期，请重新输入结帐日期！"), MB_ICONEXCLAMATION);return;/ Caculate BillCOleDateTimeSpan timeSpan;timeSpan = m_tOutDate - tInDate;int iDays = timeSpan.GetDays()+1; float fPrice = atof(m_sPrice);float fDiscount = atof(m_sDiscount);float fAmmount = fPrice*iDays*fDiscount/100;CString sAmmount;sAmmount.Format(&q

43、uot;%.2f", fAmmount); CString sTip = _T("金额为：");sTip += sAmmount;sTip += _T(" 结算完毕!！");AfxMessageBox(sTip, MB_ICONINFORMATION);测试结果粘贴：图-8六、系统详细设计及实现1程序的开发环境、运行环境设置。本程序的开发环境是visual C+以及SQLServer2005，其中前端界面选用的是用了MFC的界面显示，后台数据库用SQLServer2005搭建。2系统功能模块图及说明。3每个模块的关键语句、关键代码段及关键技术说

44、明。登录模块：void CLoginDLG:OnOK() / TODO: Add extra validation here/ Check UserName VadilatyUpdateData(true);m_sUSER.TrimRight(" ");if ( ""=m_sUSER )AfxMessageBox(_T("请填写用户名"), MB_ICONEXCLAMATION); return;_variant_t Holder, strQuery; strQuery = "select user_ID, user_PWD

45、 from user_Info where user_ID='"+m_sUSER+"'"theApp.ADOExecute(theApp.m_pADOSet, strQuery);int iCount = theApp.m_pADOSet->GetRecordCount();if ( 0=iCount ) theApp.m_iLoginCount+;if ( theApp.m_iLoginCount>2 ) AfxMessageBox("没有这个用户n三次输入均不正确，请核对后再来", MB_ICONEXCLAMAT

46、ION);CDialog:OnCancel(); return;AfxMessageBox("没有这个用户，请重新输入用户名", MB_ICONEXCLAMATION); return;测试结果粘贴：图-9客房查询模块：void CCheckRoomDLG:OnOK() / TODO: Add extra validation hereUpdateData(true);if ( !m_bNoChecked && !m_bTypeChecked)AfxMessageBox(_T("请选择查询条件！"), MB_ICONEXCLAMATION

47、);return;m_sNo.TrimRight(" ");m_sType.TrimRight(" ");if ( m_bNoChecked ) if ( ""=m_sNo ) AfxMessageBox(_T("客房编号条件不能为空！"), MB_ICONEXCLAMATION);return; else m_sNo="%"if ( !m_bTypeChecked ) m_sType = "%" if ( _T("所有房间类型")=m_sType ) m_sType = "%"/ Refresh Room_variant_t strQuery;strQuery = "select * from rooms where roomNo like '"+m_sNo+"' and roomType like