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

1、中國地質(zhì)大學(xué)（北京）數(shù)據(jù)庫系統(tǒng)概論火車票網(wǎng)上訂票系統(tǒng)實(shí)驗(yàn)報告小組成員：董威、韋飛陽、李建同、劉厚今學(xué)號：聯(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 退票 12.1.4 注冊 12.1.5 用戶登陸 12.1.6 修改密碼 12.1.7 添加車次 12.2 數(shù)據(jù)需求 12.3 性能需求 2錯誤!未定義書簽。錯誤!未定義書簽。錯誤 !未定義書簽。2.4數(shù)據(jù)流圖 32.3.1 頂層數(shù)據(jù)流圖 32.3.2 1層數(shù)據(jù)流圖 33 . 概

2、念結(jié)構(gòu)設(shè)計 33.1 系統(tǒng)結(jié)構(gòu)簡述 33.2 數(shù)據(jù)處理 33.3 生成 E-R圖 44 邏輯結(jié)構(gòu)設(shè)計 44.1 關(guān)系模式 45 物理結(jié)構(gòu)設(shè)計 56 數(shù)據(jù)庫實(shí)施 106.1 建立數(shù)據(jù)庫 106.2 建立基本表 116.3 建立索引 146.4 創(chuàng)建視圖 166.5 創(chuàng)建存儲過程 166.6 創(chuàng)建觸發(fā)器 176.7 插入數(shù)據(jù) 196.8 關(guān)系圖 206.9 訂購車票 206.10 退票 217 實(shí)驗(yàn)小結(jié) 211. 小組成員及分工董威：數(shù)據(jù)庫的實(shí)施，即在查詢分析器下編寫代碼建立數(shù)據(jù)庫，創(chuàng)建基本表，建立基本表之間的關(guān)系、索引，創(chuàng)建存儲過程等工 作。韋飛陽：主要負(fù)責(zé)邏輯結(jié)構(gòu)的設(shè)計，即確定關(guān)系模式。李建同

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

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

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

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

7、：姓名，身份證號，聯(lián)系電話，目的城市，始發(fā)城市。3.2.4查詢余票數(shù)量輸入：乘車區(qū)間和車次號輸出：余票數(shù)量。3.3 生成E-R圖車次信息表（車次號，始發(fā)站，終點(diǎn)站，途徑站）訂票單表（訂單號，身份證號，訂單時間，付款取票與否，車次號，車票號，票價，員工號）車票信息表（車票號，車次號,始發(fā)站，發(fā)車時間，終點(diǎn)站，車票種類，訂 單號，車廂號，座位號）說明：車票種類為：“無座”或“硬座”或“硬臥”或“軟 臥”余票信息表（車次號，車票種類，出發(fā)站,目的站，出發(fā)時間）5物理結(jié)構(gòu)設(shè)計訂票人表字段特殊屬性身份證char(18)主鍵,非空姓名char(10)NOT NULL聯(lián)系電話varchar(12)NOT N

8、ULL始發(fā)城巾varchar(10)NOT NULL目的城巾varchar(10)NOT NULL訂單號char(20)外鍵訂票單表字段特殊屬性訂單號char(20)主鍵身份證號char(18)外鍵訂票時間datetimeNOT NULL付款取票與否char(2)NOT NULL 默認(rèn)'是'或“否”車次號varchar(10)LIKE ' A-Z% '車票號char(20)外鍵票價intNOT NULL員工號char(20)外鍵車票表字段字段特殊屬性車票號char(20)主鍵車次號varchar(10)外鍵始發(fā)站varchar(10)NOT NULL發(fā)車時間da

9、tetimeNOT NULL車廂號intNOT NULL座位號intNOT NULL終點(diǎn)站varchar(10)NOT NULL車票種類char(20)IN（'軟臥，硬臥，'軟座，硬座，站票）訂單號char(20)NOT NULL車次表字段特殊屬性車次號char(20)主鍵始發(fā)站varchar(10)NOT NULL終點(diǎn)站varchar(10)NOT NULL途徑站varchar(10)NOT NULL操作員表字段特殊屬性員工號char(20)主鍵訂票點(diǎn)號char(20)NOT NULL姓名varchar(10)NOT NULL性別char(2)NOT NULL 默認(rèn)'

10、男'或'女'電話varchar(12)NOT NULL余票信息表字段特殊屬性車次號varchar(10)主鍵車票種類char(10)主鍵出發(fā)站varchar(10)NOT NULL目的站varchar(10)NOT NULL出發(fā)時間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 =5MBFILEGROWTH0%）log on（ name=網(wǎng)上訂票數(shù)據(jù)庫_log ,f

11、ilename數(shù)據(jù)庫 網(wǎng)上訂票數(shù)據(jù)庫_log.ldf'size =5MB,FILEGROWT=H2MB)6.2 建立基本表創(chuàng)建訂票人表：create table 訂票人表( 身份證號 char ( 18) not null primary key,姓名 varchar ( 10) not null,聯(lián)系電話varchar( 12)notnull,始發(fā)城市varchar( 10)notnull,目的城市varchar( 10)notnull,訂單號 char ( 20)創(chuàng)建操作員表：create table 操作員表( 員工號 char ( 10) primary key,訂票點(diǎn) var

12、char ( 10) not null,姓名 varchar ( 10) not null,性別 char ( 2) check ( 性別 in( ' 男' , ' 女' ),電話 varchar ( 12) not null)創(chuàng)建車次信息表：create table 車次信息表(車次號varchar(10)check ( 車次號LIKE 'A-Z%' ) primary key始發(fā)站varchar(10)notnull,終點(diǎn)站varchar(10)notnull,途經(jīng)站varchar(100) )創(chuàng)建訂票單表：create table 訂票單表

13、( 訂單號 char (20) primary key,身份證號 char ( 18) references 訂票人表 ( 身份證號 ),訂票時間 datetime not null,付款取票與否 char （2） check（付款取票與否in（'是，否）,車次號 varchar ( 10) check (車次號 LIKE 'A-Z%' ),車票號 char ( 20) not null,票價 int not null,員工號 char ( 10) references 操作員表 ( 員工號 )創(chuàng)建車票信息表：create table 車票信息表( 車票號char (2

14、0) primary key,車次號 varchar ( 10) check (車次號 LIKE 'A-Z%' ),始發(fā)站varchar( 10)notnull,發(fā)車時間 datetime not null,終點(diǎn)站varchar( 10)notnull,車票種類 char(4) check (車票種類 in( ' 無座 ' , ' 硬座 ' , ' 硬臥 ' , '軟臥 ' ),訂單號 char ( 20) references 訂票單表 ( 訂單號 ),車廂號 int not null,座位號 int not n

15、ull)創(chuàng)建余票信息表：create table 余票信息表( 車次號varchar ( 10) check ( 車次號LIKE 'A-Z%' ) primary key,出發(fā)站varchar ( 10) not null,目的站 varchar ( 10) not null,余票張數(shù)int notnull,車票種類char ( 20)primarykey,出發(fā)日期datatime)6.3 建立索引訂票人表 ：IF exists ( select * from sysindexes where name='pID_index' )drop index 訂票人表

16、. pID_indexcreate unique index pID_indexon 訂票人表 ( 身份證號 )with fillfactor =30訂票單表 ：IF exists ( select * from sysindexes where name='piaoDanNO_index' )drop index 訂票單表 . piaoDanNO_indexcreate index piaoDanNO_indexon 訂票單表 ( 訂單號 )車票信息表：IF exists ( select * from sysindexes where name='piaoNO_in

17、dex' )drop index 車票信息表表. piaoNO_indexcreate index piaoNO_indexon 車票信息表( 車票號 )車次信息表：IF exists ( select * from sysindexes where name='cheCiNO_index' )drop index 車次信息表. cheCiNO_indexcreate index cheCiNO_indexon 車次信息表( 車次號 )操作員表 ：IF exists ( select * from sysindexes where name='yuanGongN

18、O_index' )drop index 操作員表 . yuanGongNO_indexcreate index yuanGongNO_indexon 操作員表 （ 員工號 ）6.4 創(chuàng)建視圖create view allInfo_viewasselect 訂票人表 . 姓名 , 訂票人表 . 身份證號 , 訂票單表 . 車次號 ,車票信息表. 車票號 , 車票信息表. 發(fā)車時間 , 車票信息表. 始發(fā)站,車票信息表. 終點(diǎn)站 , 訂票單表 . 票價from 訂票人表 inner join 訂票單表on 訂票人表 . 身份證號 = 訂票單表 . 身份證號 inner join 車票信息

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

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

21、 deleteasdeclare 車次號 varchar(10)select 車次號=車次號from deletedupdate 余票信息表set 余票張數(shù) =余票張數(shù) +1where 車次號=車次號6.7 插入數(shù)據(jù)向操作員表中插入數(shù)據(jù)：insert into 操作員表values（, ' 洛陽 ' , ' 魯金龍 ' , ' 男' ,）向車次信息表中插入數(shù)據(jù)：insert into 車次信息表values（ 'K31' , ' 福州 ' , ' 洛陽 ' , ' 商丘、開封、鄭州 ' ）向訂票人表中插入數(shù)據(jù)：insert into 訂票人表values（, ' 魯金龍 ' , ' 民權(quán) ' ,' 洛陽 ' ,）insert into 訂票單表values(, '2011-06-24 14:31:36',' 是' ,'K31' , 12.5,)向車票信息表中插入數(shù)據(jù)：insert into 車票信息表values(, 'K31' , ' 民權(quán) ' , '2011-06-24 14:31:36','