數(shù)據(jù)庫大作業(yè)火車訂票系統(tǒng)

1、中國地質(zhì)大學(xué)（北京）數(shù)據(jù)庫系統(tǒng)概論火車票網(wǎng)上訂票系統(tǒng)實(shí)驗(yàn)報(bào)告告小組成員：董威、韋飛陽、李建同、劉厚今學(xué)號(hào)：聯(lián)系方式：使用數(shù)據(jù)庫管理系統(tǒng)：SQL server 2012指導(dǎo)老師：宋莉2013年12月28日目錄1. 小組成員及分工 02. 需求分析 02.1 功能需求 02.1.1 查詢 02.1.2 訂票 02.1.3 退票 02.1.4 注冊 02.1.5 用戶登陸 02.1.6 修改密碼 02.1.7 添加車次 02.2 數(shù)據(jù)需求 02.3 性能需求 1錯(cuò)誤! 未定義書簽。錯(cuò)誤! 未定義書簽。錯(cuò)誤! 未定義書簽。2.4 數(shù)據(jù)流圖 12.4.1 頂層數(shù)據(jù)流圖 12.4.2 1 層數(shù)據(jù)流圖 13

2、. 概念結(jié)構(gòu)設(shè)計(jì) 13.1 系統(tǒng)結(jié)構(gòu)簡述 13.2 數(shù)據(jù)處理 13.3生成E-R圖 24邏輯結(jié)構(gòu)設(shè)計(jì) 24.1 關(guān)系模式 25 物理結(jié)構(gòu)設(shè)計(jì) 26數(shù)據(jù)庫實(shí)施 36.1 建立數(shù)據(jù)庫 36.2 建立基本表 46.3 建立索引 56.4 創(chuàng)建視圖 66.5 創(chuàng)建存儲(chǔ)過程 66.6 創(chuàng)建觸發(fā)器 66.7 插入數(shù)據(jù) 76.8 關(guān)系圖 76.9 訂購車票 76.10 退票 77 實(shí)驗(yàn)小結(jié) 81. 小組成員及分工董威：數(shù)據(jù)庫的實(shí)施，即在查詢分析器下編寫代碼建立數(shù)據(jù)庫，創(chuàng)建基本表，建立 基本表之間的關(guān)系、索引，創(chuàng)建存儲(chǔ)過程等工作。韋飛陽：主要負(fù)責(zé)邏輯結(jié)構(gòu)的設(shè)計(jì)，即確定關(guān)系模式。李建同：只要負(fù)責(zé)物理結(jié)構(gòu)的設(shè)計(jì)，

3、即確定各個(gè)基本表之間的 關(guān)系，確定主外鍵。劉厚今：需求分析模塊，主要負(fù)責(zé)尋找需求，繪制數(shù)據(jù)流圖。2. 需求分析2.1功能需求2.1.1查詢：用戶、管理員都可以對(duì)車次進(jìn)行查詢：可以按照發(fā)車車次進(jìn)行查 詢；可以按照出發(fā)地、目的地進(jìn)行查詢；可以按照類車類型、座 位類型查詢。車次信息包括：車次ID，車號(hào)，出發(fā)地，目的地，列車類型，座 位類型，票價(jià)，票數(shù)，車次信息只允許用戶查詢，不能修改。2.1.2 訂票：用戶登陸后，通過訂票系統(tǒng)，根據(jù)自己你的需求找到自己的車次，輸入要預(yù)訂的票數(shù)進(jìn)行訂票。 要求訂票記錄應(yīng)包括：訂票記錄ID， 車次ID，用戶ID，訂票數(shù)量。2.1.3 退票：用戶登錄后，通過退票系統(tǒng)，找到

4、自己的訂票信息，輸入要退的票數(shù)進(jìn)行退票。2.1.4 注冊：想要訂票的用戶必須先注冊然后登陸訂票。用戶信息包括：用戶ID，用戶名，密碼，由E箱。2.1.5 用戶登陸：主要完成用戶和管理員的登陸。用戶想訂票必須登陸后才能進(jìn) 行訂票，如果沒有注冊的用戶應(yīng)該與注冊頁面相連接。2.1.6 修改密碼：用戶和管理員登陸后可以修改個(gè)人信息。2.1.7 添加車次：管理員登陸后可以添加新的車次。2.2數(shù)據(jù)需求信息輸入信息處理信息輸出用戶輸入所需車次檢索系統(tǒng)數(shù)據(jù)庫符合用戶需求的車次及 相關(guān)信息用戶管理員輸入用戶 名、密碼進(jìn)行登錄檢索系統(tǒng)數(shù)據(jù)庫成功登陸后的首頁用戶輸入個(gè)人信息進(jìn)行 注冊將輸入信息存入系統(tǒng)數(shù) 據(jù)庫操作反

5、饋用戶輸入訂票數(shù)量檢索系統(tǒng)數(shù)據(jù)庫已得到 訂票記錄，添加用戶請(qǐng) 求的訂票記錄，更新系 統(tǒng)數(shù)據(jù)庫操作反饋用戶輸入退票數(shù)量檢索系統(tǒng)數(shù)據(jù)庫已得到 訂票記錄，刪除用戶請(qǐng) 求的訂票記錄，更新系操作反饋統(tǒng)數(shù)據(jù)庫用戶管理員輸入新密碼更新系統(tǒng)數(shù)據(jù)庫操作反饋管理員輸入添加車次信 息將新信息存入數(shù)據(jù)庫操作反饋2.3性能需求系統(tǒng)應(yīng)能及時(shí)準(zhǔn)確的根據(jù)用戶權(quán)限及所輸入的信息作出響應(yīng)。由于查詢功 能對(duì)于用戶來講很重要，因?yàn)檫@直接影響了用戶的訂票決策，所以系統(tǒng)的 準(zhǔn)確性和及時(shí)性很重要。本系統(tǒng)是針對(duì)用戶直接開放的，所以應(yīng)該提供良好的接口。網(wǎng)上訂票的數(shù)據(jù)是很重要的信息，系統(tǒng)應(yīng)該保證用戶的權(quán)限，對(duì)于車次的 信息不能更改。同時(shí)還應(yīng)該保

6、證管理人員進(jìn)行正常的維護(hù)、更新等工作， 保證數(shù)據(jù)的一致性。2.4數(shù)據(jù)流圖2.4.1頂層數(shù)據(jù)流圖242 1層數(shù)據(jù)流圖3. 概念結(jié)構(gòu)設(shè)計(jì)3.1系統(tǒng)結(jié)構(gòu)簡述本系統(tǒng)包括六個(gè)實(shí)體：訂票人，訂票單信息，車票信息，車次信息，操作員，余票信息。訂票人與訂票單、訂票單與車票信息這兩個(gè)關(guān)系是一對(duì)一的聯(lián)系；車票信息與車次信息、訂票單信息與操作員這兩個(gè)關(guān)系是 1對(duì)多的聯(lián)系。3.2 數(shù)據(jù)處理輸入：身份證號(hào)輸出：訂票單號(hào)，訂票人身份證號(hào)，訂單時(shí)間，付款取票與否，車次號(hào)，車票號(hào)，票價(jià)，員工號(hào)。輸入：身份證號(hào)輸出：車票號(hào)，始發(fā)站，車次號(hào)，發(fā)車時(shí)間，終點(diǎn)站，車票種類。輸入：車次號(hào)輸出：姓名，身份證號(hào)，聯(lián)系電話，目的城市，始發(fā)城

7、市324查詢余票數(shù)量輸入：乘車區(qū)間和車次號(hào) 輸出：余票數(shù)量。3.3生成E-R圖訂票份證車次號(hào)姓名，4邏輯結(jié)構(gòu)設(shè)計(jì)&車票號(hào)關(guān)系模式屬于（員工號(hào)，訂表（身份證號(hào)員操作員車次信息表（_ _站,次訂厶訂單號(hào) 訂票單表字段 訂單號(hào) 身份證號(hào) 訂票時(shí)間券息表林1車票信I茶息表（車票號(hào)，車次號(hào)，車廂號(hào)，座位號(hào)）7生名目的城市，操作.發(fā)城市，別，電話）,途徑站操作付款取票與否，車次號(hào)，車票號(hào)，票價(jià),發(fā)車時(shí)河，座”或“硬座次號(hào)于車票種類發(fā)出發(fā)時(shí)，目的站，票車票種類，訂單J員工號(hào)臥（終點(diǎn)站）口發(fā)站varchar(12)archar(i0)varchar(10)ar(20)類型毛票h張(20) fchar(18)e

8、time屬于特殊車性次信主鍵,非空卞LNU|L NULLNOT外帀殊屬性4韋主鍵外鍵OT NUL付款取票與否char(2)NOTNULL默認(rèn)是 或“否”車次號(hào)varchar(IO)LIKE A-Z% 車票號(hào)char(20)外鍵票價(jià)intNOT NULL員工號(hào)char(20)外鍵車票表字段字段特殊屬性車票號(hào)char(20)主鍵車次號(hào)varchar(10)外鍵始發(fā)站varchar(10)NOT NULL發(fā)車時(shí)間datetimeNOT NULL車廂號(hào)intNOT NULL座位號(hào)intNOT NULL終點(diǎn)站varchar(10)NOT NULL車票種類char(20)IN（軟臥，硬臥，軟 座，硬座，站

9、票）訂單號(hào)char(20)NOT NULL車次表字段類型特殊屬性車次號(hào)char(20)主鍵始發(fā)站varchar(10)NOT NULL終點(diǎn)站varchar(10)NOT NULL途徑站varchar(10)NOT NULL操作員表字段類型特殊屬性員工號(hào)char(20)主鍵訂票點(diǎn)號(hào)char(20)NOT NULL姓名varchar(10)NOT NULL性別char(2)NOTNULL默認(rèn)男 或女電話varchar(12)NOT NULL余票信息表字段類型特殊屬性車次號(hào)varchar(10)主鍵車票種類char(10)主鍵出發(fā)站varchar(10)NOT NULL目的站varchar(10)N

10、OT NULL出發(fā)時(shí)間datatimeNOT NULL余票張數(shù)intNOT NULL6數(shù)據(jù)庫實(shí)施6.1建立數(shù)據(jù)庫create database 網(wǎng)上訂票數(shù)據(jù)庫on primary( name=網(wǎng)上訂票數(shù)據(jù)庫, filename = 數(shù)據(jù)庫 網(wǎng)上訂票數(shù)據(jù)庫 .mdf , size =5MB,FILEGROWT=1H0%)log on( name=網(wǎng)上訂票數(shù)據(jù)庫og , filename = 數(shù)據(jù)庫 網(wǎng)上訂票數(shù)據(jù)庫 _log.ldf , size =5MB,FILEGROWT=2HMB)6.2 建立基本表創(chuàng)建訂票人表 ：create table 訂票人表( 身份證號(hào) char(18) not nu

11、ll primary key,姓名 varchar (10)notnull,聯(lián)系電話varchar(12)notnull,始發(fā)城市varchar(10)notnull,目的城市varchar(10)notnull,訂單號(hào) char ( 20)創(chuàng)建操作員表 ：create table 操作員表( 員工號(hào) char(10) primary key, 訂票點(diǎn) varchar (10) not null, 姓名 varchar (10) not null, 性別 char (2) check(性別 in(男,女 ), 電話 varchar (12) not null)創(chuàng)建車次信息表 ：create t

12、able 車次信息表(key ,車次號(hào) varchar (10) check (車次號(hào) LIKE A-Z% ) primary 始發(fā)站 varchar (10) not null, 終點(diǎn)站 varchar (10) not null,途經(jīng)站 varchar (100) ) 創(chuàng)建訂票單表 ：create table 訂票單表( 訂單號(hào) char(20) primary key,身份證號(hào) char(18) references 訂票人表(身份證號(hào)), 訂票時(shí)間 datetime not null,付款取票與否 char(2) check(付款取票與否in(是，否), 車次號(hào) varchar (10

13、) check (車次號(hào) LIKE A-Z% ), 車票號(hào) char(20) not null,票價(jià) int not null, 員工號(hào) char ( 10) references 操作員表 (員工號(hào)) 創(chuàng)建車票信息表 ：create table 車票信息表 ( 車票號(hào) char ( 20) primary key,車次號(hào) varchar (10) check ( 車次號(hào) LIKE A-Z% ), 始發(fā)站 varchar (10) not null, 發(fā)車時(shí)間 datetime not null,終點(diǎn)站 varchar (10) not null,車票種類char (4) check(車票種類

14、in(無座，硬座,硬臥，軟臥), 訂單號(hào) char(20) references 訂票單表(訂單號(hào)), 車廂號(hào) int not null, 座位號(hào) int not null)創(chuàng)建余票信息表 ：create table 余票信息表(車次號(hào) varchar (10) check (車次號(hào) LIKE A-Z% ) primary key, 出發(fā)站 varchar (10) not null,目的站 varchar (10) not null, 余票張數(shù) int not null, 車票種類 char(20) primary key, 出發(fā)日期 datatime )6.3 建立索引訂票人表 ：IF e

15、xists ( select * from sysindexes where name=pID_index ) drop index 訂票人表 . pID_index create unique index pID_indexon 訂票人表 ( 身份證號(hào) ) with fillfactor =30 訂票單表 ：IF exists ( select * from sysindexes where name=piaoDanNO_index ) drop index 訂票單表 . piaoDanNO_index create index piaoDanNO_indexon 訂票單表 ( 訂單號(hào) )

16、車票信息表 ：IF exists ( select * from sysindexes where name=piaoNO_index ) drop index 車票信息表表 .piaoNO_indexcreate index piaoNO_indexon 車票信息表 (車票號(hào) ) 車次信息表 ：IF exists ( select * from sysindexes where name=cheCiNO_index ) drop index 車次信息表 . cheCiNO_index create index cheCiNO_indexon 車次信息表 ( 車次號(hào) ) 操作員表 ：IF ex

17、ists ( select * from sysindexes where name=yuanGongNO_index) drop index 操作員表 . yuanGongNO_indexcreate index yuanGongNO_indexon 操作員表 (員工號(hào))6.4 創(chuàng)建視圖create view allInfo_viewasselect 訂票人表.姓名, 訂票人表 .身份證號(hào) ,訂票單表.車次號(hào), 車票信息表 .車票號(hào),車票信息表.發(fā)車時(shí)間,車票信息表 .始發(fā)站, 車票信息表 .終點(diǎn)站, 訂票單表. 票價(jià)from 訂票人表 inner join 訂票單表on 訂票人表 . 身份

18、證號(hào) = 訂票單表 . 身份證號(hào) inner join 車票信息表on 訂票單表 . 車票號(hào) = 車票信息表 . 車票號(hào) inner join 車次信息表on 車票信息表 . 車次號(hào) = 車次信息表 . 車次號(hào)6.5 創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程 ，通過身份證號(hào)查詢訂票人信息 ：create procedure pInfo_procpIDchar ( 18)asselect 身份證號(hào) ,姓名, 聯(lián)系電話 ,始發(fā)城市 , 目的城市 ,訂單號(hào)from 訂票人表 where 身份證號(hào) =pID創(chuàng)建存儲(chǔ)過程 ，通過身份證號(hào)查詢訂票人訂票信息 ：create procedure ticket_procpID

19、char ( 18)asselect 姓名,訂票單表 .訂單號(hào), 訂票人表 .身份證號(hào) ,訂票時(shí)間 , 付款取票與 否,車次號(hào), 車票號(hào),票價(jià), 員工號(hào)from 訂票單表 inner join 訂票人表on 訂票人表.身份證號(hào)= 訂票單表 .身份證號(hào)where 訂票單表 . 身份證號(hào) =pID6.6 創(chuàng)建觸發(fā)器create trigger 余票更新on 車票信息表for insertasdeclare 車次號(hào) varchar(10)select 車次號(hào) =車次號(hào) from insertedupdate 余票信息表set 余票張數(shù) =余票張數(shù) -1where 車次號(hào) =車次號(hào)create tri

20、gger 余票更新 2on 車票信息表for deleteas declare 車次號(hào) varchar(10) select 車次號(hào) =車次號(hào) from deleted update 余票信息表 set 余票張數(shù) =余票張數(shù) +1 where 車次號(hào) =車次號(hào)6.7 插入數(shù)據(jù)向操作員表中插入數(shù)據(jù) ： insert into 操作員表 values(, 洛陽,魯金龍,男 ,) 向車次信息表中插入數(shù)據(jù) ：insert into 車次信息表 values( K31 ,福州 ,洛陽 , 商丘、開封、鄭州 ) 向訂票人表中插入數(shù)據(jù) ：insert into 訂票人表 values(, 魯金龍 , 民權(quán) , 洛陽 ,) 向訂票單表中插入數(shù)據(jù) ： insert into 訂票單表 values(, 2011-06-24 14:31:36 , 是 , K31 , 12.5 ,