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

旅游管理系統(tǒng)課程設(shè)計(jì)報(bào)告 班級(jí)：軟件1202班 學(xué)號(hào)：02240120217 姓名：賴子郁 完成日期：2015-01-09 一、項(xiàng)目功能需求旅游管理系統(tǒng)項(xiàng)目功能：1. 管理員輸入旅游景點(diǎn)信息2. 管理員輸入旅游線路信息3. 管理員選擇旅游線路的旅游景點(diǎn)4. 管理員查看用戶預(yù)定信息5. 管理員輸入用戶結(jié)算信息6. 用戶查看旅游項(xiàng)目信息7. 用戶預(yù)定旅游線路8. 用戶查看訂單狀態(tài)二、數(shù)據(jù)庫設(shè)計(jì)（一）數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(jì)1. 旅游管理系統(tǒng)E-R圖圖1 系統(tǒng)E-R圖2、旅游管理系統(tǒng)數(shù)據(jù)庫關(guān)系圖圖2 數(shù)據(jù)庫關(guān)系圖3、旅游管理系統(tǒng)數(shù)據(jù)字典序號(hào)數(shù)據(jù)表名稱說明備注1area地域2line旅游線路3line_spot旅游線路與景點(diǎn)關(guān)聯(lián)4member會(huì)員5operator員工6spot旅游景點(diǎn)7travelplan旅游計(jì)劃8travelplanorder旅游計(jì)劃預(yù)訂表1 數(shù)字字典序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否ids主鍵，自增列2areaidnvarchar50否地域編碼3name nvarchar50否名稱表2 area表序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否編號(hào)主鍵，自增列2namenvarchar200否路線名稱3pricedecimal（18,2）否路線價(jià)格4daysint8否備注5isrecommendedint8否是否推薦6spotnumint8否景點(diǎn)數(shù)量7updatetimedatatime8否最近修改時(shí)間8isdeletedint8否是否刪除1：已刪除，0：未刪除表3 line表序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否編號(hào)主鍵，自增列2lineidsint8否路線編號(hào)參照line（ids）3spotidsint8否景點(diǎn)編號(hào)參照spot（ids）表4 line_spot表序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否編號(hào)主鍵，自增列2memberidnvarchar50否會(huì)員賬號(hào)3membername nvarchar50否會(huì)員名稱4passwords nvarchar50否密碼5realnamenvarchar50否真實(shí)姓名6sexint8否性別1：男，2：女7idnumnvarchar50否身份證號(hào)8emailnvarchar50否郵箱9telephone nvarchar50否手機(jī)號(hào)碼表5 member表序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否編號(hào)主鍵，自增列2useridnvarchar50否管理員賬號(hào)3usernamenvarchar50否管理員名稱4passwordsnvarchar50否密碼5idnumnvarchar50否身份證號(hào)6isadminint8否是否具有管理員權(quán)限表6 operator表序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否編號(hào)主鍵，自增列2namenvarchar200否景點(diǎn)名稱3pricedecimal（18,2）否價(jià)格4areaint8否所屬地域參照area（ids）5imgimage是景點(diǎn)圖片6desvarchar2000是景點(diǎn)描述7isdeletedint8否是否已刪除1：已刪除，0：未刪除表7 spot表序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否編號(hào)主鍵，自增列2planidnvarchar200否計(jì)劃編號(hào)3lineidsint8否旅游線路ids參照line（ids）4expect_people_num int8否預(yù)訂人數(shù)5actual_people_num int8是實(shí)際預(yù)訂人數(shù)6expect_starttime datetime否計(jì)劃開始時(shí)間7actual_starttime datetime是實(shí)際開始時(shí)間8updatetimedatetime否最近修該時(shí)間9planstatusint8否計(jì)劃狀態(tài)1：發(fā)布，2：進(jìn)行，3：結(jié)束10isdeletedint8否是否刪除1：已刪除，0：未刪除表8 traveplan表序號(hào)數(shù)據(jù)表名稱數(shù)據(jù)類型長度是否允許為空字段說明備注1idsint8否編號(hào)主鍵，自增列2planidsint8否旅游計(jì)劃ids參照traveplan（ids）3memberidsint8否預(yù)訂用戶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、用戶查看賬戶預(yù)訂情況CREATE VIEW memberorderASSELECT member.membername AS 用戶, member.sex AS 性別, AS 路線, travelplanorder.ordertime AS 預(yù)訂時(shí)間, travelplanorder.ispay AS 是否支付, travelplanorder.before_discount_money AS 折扣前金額, member.telephone AS 電話, 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 線路, member.membername AS 用戶, 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、查看旅游線路所包含的景點(diǎn)信息CREATE VIEW line_spot_viewASSELECT AS 線路名, 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ì)編程對象1.觸發(fā)器1) 刪除會(huì)員的同時(shí)，刪除會(huì)員的旅游線路訂單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) 刪除旅游路線的同時(shí)，刪除與該旅游路線相關(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)與旅游線路的關(guān)聯(lián)create trigger spot_deleteon spotfor updateasDELETE FROM line_spotWHERE (line_spot.spotids = (select ids from deleted)2.存儲(chǔ)過程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) 查詢景點(diǎn)所屬地區(qū)create proc areaSelectasSELECT ids, areaid, nameFROM area3) 查詢路線信息create proc lineselectasSELECT ids AS 編號(hào), name AS 路線名, 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) 查詢用戶信息create proc selecttextas select * from member6) 查詢景點(diǎn)信息create proc spotinsertasSELECT name, price, area, des, isdeletedFROM spot3.函數(shù)1) 查詢數(shù)據(jù)庫中是否有此人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) 用戶用例圖（二）系統(tǒng)運(yùn)行結(jié)果1) 登陸：系統(tǒng)用戶可選擇用戶類型（管理員、普通用戶）登陸到不同頁面2) 注冊：在登陸頁面可選擇進(jìn)入用戶注冊、管理員注冊頁面，注冊信息3)