旅游管理系統(tǒng)課程設(shè)計(jì)報(bào)告.doc

旅游管理系統(tǒng)課程設(shè)計(jì)報(bào)告 班級(jí)：軟件1202班 學(xué)號(hào)：02240120217 姓名：賴(lài)子郁 完成日期：2015-01-09 一、項(xiàng)目功能需求旅游管理系統(tǒng)項(xiàng)目功能：1. 管理員輸入旅游景點(diǎn)信息2. 管理員輸入旅游線(xiàn)路信息3. 管理員選擇旅游線(xiàn)路的旅游景點(diǎn)4. 管理員查看用戶(hù)預(yù)定信息5. 管理員輸入用戶(hù)結(jié)算信息6. 用戶(hù)查看旅游項(xiàng)目信息7. 用戶(hù)預(yù)定旅游線(xiàn)路8. 用戶(hù)查看訂單狀態(tài)二、數(shù)據(jù)庫(kù)設(shè)計(jì)（一）數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)1. 旅游管理系統(tǒng)E-R圖圖1 系統(tǒng)E-R圖2、旅游管理系統(tǒng)數(shù)據(jù)庫(kù)關(guān)系圖圖2 數(shù)據(jù)庫(kù)關(guān)系圖3、旅游管理系統(tǒng)數(shù)據(jù)字典序號(hào)數(shù)據(jù)表名稱(chēng)說(shuō)明備注1area地域2line旅游線(xiàn)路3line_spot旅游線(xiàn)路與景點(diǎn)關(guān)聯(lián)4member會(huì)員5operator員工6spot旅游景點(diǎn)7travelplan旅游計(jì)劃8travelplanorder旅游計(jì)劃預(yù)訂表1 數(shù)字字典序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否ids主鍵，自增列2areaidnvarchar50否地域編碼3name nvarchar50否名稱(chēng)表2 area表序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否編號(hào)主鍵，自增列2namenvarchar200否路線(xiàn)名稱(chēng)3pricedecimal（18,2）否路線(xiàn)價(jià)格4daysint8否備注5isrecommendedint8否是否推薦6spotnumint8否景點(diǎn)數(shù)量7updatetimedatatime8否最近修改時(shí)間8isdeletedint8否是否刪除1：已刪除，0：未刪除表3 line表序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否編號(hào)主鍵，自增列2lineidsint8否路線(xiàn)編號(hào)參照l(shuí)ine（ids）3spotidsint8否景點(diǎn)編號(hào)參照spot（ids）表4 line_spot表序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否編號(hào)主鍵，自增列2memberidnvarchar50否會(huì)員賬號(hào)3membername nvarchar50否會(huì)員名稱(chēng)4passwords nvarchar50否密碼5realnamenvarchar50否真實(shí)姓名6sexint8否性別1：男，2：女7idnumnvarchar50否身份證號(hào)8emailnvarchar50否郵箱9telephone nvarchar50否手機(jī)號(hào)碼表5 member表序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否編號(hào)主鍵，自增列2useridnvarchar50否管理員賬號(hào)3usernamenvarchar50否管理員名稱(chēng)4passwordsnvarchar50否密碼5idnumnvarchar50否身份證號(hào)6isadminint8否是否具有管理員權(quán)限表6 operator表序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否編號(hào)主鍵，自增列2namenvarchar200否景點(diǎn)名稱(chēng)3pricedecimal（18,2）否價(jià)格4areaint8否所屬地域參照area（ids）5imgimage是景點(diǎn)圖片6desvarchar2000是景點(diǎn)描述7isdeletedint8否是否已刪除1：已刪除，0：未刪除表7 spot表序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否編號(hào)主鍵，自增列2planidnvarchar200否計(jì)劃編號(hào)3lineidsint8否旅游線(xiàn)路ids參照l(shuí)ine（ids）4expect_people_num int8否預(yù)訂人數(shù)5actual_people_num int8是實(shí)際預(yù)訂人數(shù)6expect_starttime datetime否計(jì)劃開(kāi)始時(shí)間7actual_starttime datetime是實(shí)際開(kāi)始時(shí)間8updatetimedatetime否最近修該時(shí)間9planstatusint8否計(jì)劃狀態(tài)1：發(fā)布，2：進(jìn)行，3：結(jié)束10isdeletedint8否是否刪除1：已刪除，0：未刪除表8 traveplan表序號(hào)數(shù)據(jù)表名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度是否允許為空字段說(shuō)明備注1idsint8否編號(hào)主鍵，自增列2planidsint8否旅游計(jì)劃ids參照traveplan（ids）3memberidsint8否預(yù)訂用戶(hù)ids參照member（ids）4ordertimedatetime8否預(yù)訂時(shí)間5ispayint8否是否支付1：支付，2：未支付6before_discount_moneydecimal (18,2)否折扣前金額7charge_moneydecimal (18,3)否實(shí)收金額8paytimedatetime8是支付時(shí)間表9 traveplanorder表（二）旅游管理系統(tǒng)視圖1、用戶(hù)查看賬戶(hù)預(yù)訂情況CREATE VIEW memberorderASSELECT member.membername AS 用戶(hù), member.sex AS 性別, AS 路線(xiàn), travelplanorder.ordertime AS 預(yù)訂時(shí)間, travelplanorder.ispay AS 是否支付, travelplanorder.before_discount_money AS 折扣前金額, member.telephone AS 電話(huà), member.email AS 郵箱FROM line INNER JOIN member ON line.ids = member.ids INNER JOINtravelplanorder ON member.ids = travelplanorder.memberids2、管理員查看旅游計(jì)劃的預(yù)訂情況CREATE VIEW operatorCheckorderASSELECT AS 線(xiàn)路, member.membername AS 用戶(hù), travelplanorder.ordertime AS 預(yù)定時(shí)間, travelplanorder.ispay AS 是否支付, travelplanorder.paytime AS 支付時(shí)間, travelplanorder.before_discount_money AS 折扣前金額, travelplanorder.charge_money AS 實(shí)際支付金額FROM line INNER JOIN member ON line.ids = member.ids INNER JOIN travelplan ON line.ids = travelplan.lineids INNER JOINtravelplanorder ON member.ids = travelplanorder.memberids AND travelplan.ids = travelplanorder.planids3、查看旅游線(xiàn)路所包含的景點(diǎn)信息CREATE VIEW line_spot_viewASSELECT AS 線(xiàn)路名, AS 景點(diǎn)名, line.days AS 旅游時(shí)間, spot.area AS 所在地, spot.img, spot.des AS 景點(diǎn)介紹FROM line INNER JOIN line_spot ON line.ids = line_spot.lineids INNER JOIN spot ON line_spot.spotids = spot.ids4、查看景點(diǎn)所在地區(qū)CREATE VIEW dbo.spot_areaASSELECT dbo.spot.ids AS 編號(hào), AS 景點(diǎn)名, dbo.spot.price AS 價(jià)格, AS 地區(qū), dbo.spot.des AS 介紹, dbo.spot.isdeletedFROM dbo.area INNER JOIN dbo.spot ON dbo.area.ids = dbo.spot.area（三）設(shè)計(jì)編程對(duì)象1.觸發(fā)器1) 刪除會(huì)員的同時(shí)，刪除會(huì)員的旅游線(xiàn)路訂單create trigger member_deleteon memberfor updateasDELETE FROM travelplanorderWHERE (travelplanorder. memberids= (select ids from deleted)2) 刪除旅游計(jì)劃的同時(shí)，刪除該旅游計(jì)劃的訂單create trigger traveplan_deleteon travelplanfor updateasDELETE FROM travelplanorder WHERE (travelplanorder.planids = (select ids from deleted)3) 刪除旅游路線(xiàn)的同時(shí)，刪除與該旅游路線(xiàn)相關(guān)的旅游景點(diǎn)關(guān)聯(lián)和旅游計(jì)劃create trigger line_deleteon linefor updateasDELETE FROM travelplanWHERE (travelplan.lineids = (select ids from deleted)DELETE FROM line_spotWHERE (line_spot.lineids = (select ids from deleted)4) 刪除旅游景點(diǎn)的同時(shí)，刪除該旅游景點(diǎn)與旅游線(xiàn)路的關(guān)聯(lián)create trigger spot_deleteon spotfor updateasDELETE FROM line_spotWHERE (line_spot.spotids = (select ids from deleted)2.存儲(chǔ)過(guò)程1) 向member表中插入數(shù)據(jù)create proc memberinsertids int,membername nvarchar(50),passwords nvarchar(50),realname nvarchar(50),sex int,idnum nvarchar(50),email nvarchar(50),telephone nvarchar(50)as insert into member(ids,membername,passwords,realname,sex,idnum,email,telephone) values(ids,membername,passwords,realname,sex,idnum,email,telephone)2) 查詢(xún)景點(diǎn)所屬地區(qū)create proc areaSelectasSELECT ids, areaid, nameFROM area3) 查詢(xún)路線(xiàn)信息create proc lineselectasSELECT ids AS 編號(hào), name AS 路線(xiàn)名, price AS 價(jià)格, days AS 旅游時(shí)間, isrecommended AS 是否推薦, spotnum AS 景點(diǎn)數(shù), updatetime AS 更新時(shí)間, isdeleted AS 是否刪除FROM line4) 插入管理員信息create proc operatorinsertuserid nvarchar(50),username nvarchar(50),passwords nvarchar(50),idnum nvarchar(50),isadmin intasINSERT INTO operator (userid, username, passwords, idnum, isadmin)VALUES (userid,username,passwords,idnum,isadmin)5) 查詢(xún)用戶(hù)信息create proc selecttextas select * from member6) 查詢(xún)景點(diǎn)信息create proc spotinsertasSELECT name, price, area, des, isdeletedFROM spot3.函數(shù)1) 查詢(xún)數(shù)據(jù)庫(kù)中是否有此人create function membersex(name nvarchar(50)returns nvarchar(50)asbegin declare a nvarchar(50) if not exists(select sex from member where membername=name) set a=不存在此人 else set a=存在此人 return aend四、系統(tǒng)設(shè)置（一）系統(tǒng)用例圖1) 管理員用例圖2) 用戶(hù)用例圖（二）系統(tǒng)運(yùn)行結(jié)果1) 登陸：系統(tǒng)用戶(hù)可選擇用戶(hù)類(lèi)型（管理員、普通用戶(hù)）登陸到不同頁(yè)面2) 注冊(cè)：在登陸頁(yè)面可選擇進(jìn)入用戶(hù)注冊(cè)、管理員注冊(cè)頁(yè)面，注冊(cè)信息3)