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

1、中國地質(zhì)大學(xué)（北京） 期末考試論文專用課程名稱：數(shù)據(jù)庫系統(tǒng)概論 班號(hào)：10041111學(xué)號(hào)：1004111120等 姓名：董威等 成績： 中國地質(zhì)大學(xué)（北京）數(shù)據(jù)庫系統(tǒng)概論火車票網(wǎng)上訂票系統(tǒng)實(shí)驗(yàn)報(bào)告小組成員：董威、韋飛陽、李建同、劉厚今學(xué)號(hào)：1004111120、1004111125、1004111119、1004111113聯(lián)系方式董威）使用數(shù)據(jù)庫管理系統(tǒng)：SQL server 2012指導(dǎo)老師：宋莉2013年12月28日任課教師： 宋莉 日期：2013年12月28日目錄1.小組成員及分工12.需求分析12.1 功能需求12.1.1 查詢12.1.2 訂票12.1.

2、3 退票12.1.4 注冊12.1.5 用戶登陸12.1.6 修改密碼12.1.7 添加車次12.2 數(shù)據(jù)需求12.3 性能需求22.3.1準(zhǔn)確性和及時(shí)性22.3.2易用性22.3.3安全性22.4 數(shù)據(jù)流圖22.4.1 頂層數(shù)據(jù)流圖22.4.2 1層數(shù)據(jù)流圖23.概念結(jié)構(gòu)設(shè)計(jì)33.1 系統(tǒng)結(jié)構(gòu)簡述33.2 數(shù)據(jù)處理33.3生成E-R圖34邏輯結(jié)構(gòu)設(shè)計(jì)54.1 關(guān)系模式55 物理結(jié)構(gòu)設(shè)計(jì)56數(shù)據(jù)庫實(shí)施76.1 建立數(shù)據(jù)庫76.2 建立基本表76.3 建立索引106.4 創(chuàng)建視圖116.5 創(chuàng)建存儲(chǔ)過程116.6 創(chuàng)建觸發(fā)器126.7插入數(shù)據(jù)12 6.8 關(guān)系圖13 6.9 訂購車票13 6.1

3、0 退票147 實(shí)驗(yàn)小結(jié)141.小組成員及分工董威：數(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ì)，即確定各個(gè)基本表之間的 關(guān)系，確定主外鍵。劉厚今：需求分析模塊，主要負(fù)責(zé)尋找需求，繪制數(shù)據(jù)流圖。2.需求分析2.1 功能需求 2.1.1 查詢：用戶、管理員都可以對車次進(jìn)行查詢：可以按照發(fā)車車次進(jìn)行查 詢；可以按照出發(fā)地、目的地進(jìn)行查詢；可以按照類車類型、座 位類型查詢。 車次信息包括：車次ID，車號(hào)，出發(fā)地，目的地，列車類型，座 位類型，票價(jià)，票數(shù)，車

4、次信息只允許用戶查詢，不能修改。 2.1.2 訂票：用戶登陸后，通過訂票系統(tǒng)，根據(jù)自己你的需求找到自己的車次， 輸入要預(yù)訂的票數(shù)進(jìn)行訂票。要求訂票記錄應(yīng)包括：訂票記錄ID， 車次ID，用戶ID，訂票數(shù)量。 2.1.3 退票：用戶登錄后，通過退票系統(tǒng)，找到自己的訂票信息，輸入要退的票 數(shù)進(jìn)行退票。 2.1.4 注冊：想要訂票的用戶必須先注冊然后登陸訂票。用戶信息包括：用戶 ID，用戶名，密碼，郵箱。 2.1.5 用戶登陸：主要完成用戶和管理員的登陸。用戶想訂票必須登陸后才能進(jìn) 行訂票，如果沒有注冊的用戶應(yīng)該與注冊頁面相連接。 2.1.6 修改密碼：用戶和管理員登陸后可以修改個(gè)人信息。 2.1.7

5、 添加車次：管理員登陸后可以添加新的車次。2.2 數(shù)據(jù)需求信息輸入信息處理信息輸出用戶輸入所需車次檢索系統(tǒng)數(shù)據(jù)庫符合用戶需求的車次及相關(guān)信息用戶管理員輸入用戶名、密碼進(jìn)行登錄檢索系統(tǒng)數(shù)據(jù)庫成功登陸后的首頁用戶輸入個(gè)人信息進(jìn)行注冊將輸入信息存入系統(tǒng)數(shù)據(jù)庫操作反饋用戶輸入訂票數(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 性能需求 2.3.1準(zhǔn)確性和及時(shí)性 系統(tǒng)應(yīng)能及時(shí)準(zhǔn)確的根據(jù)

6、用戶權(quán)限及所輸入的信息作出響應(yīng)。由于查詢功 能對于用戶來講很重要，因?yàn)檫@直接影響了用戶的訂票決策，所以系統(tǒng)的 準(zhǔn)確性和及時(shí)性很重要。 2.3.2易用性 本系統(tǒng)是針對用戶直接開放的，所以應(yīng)該提供良好的接口。 2.3.3安全性 網(wǎng)上訂票的數(shù)據(jù)是很重要的信息，系統(tǒng)應(yīng)該保證用戶的權(quán)限，對于車次的信息不能更改。同時(shí)還應(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è)計(jì) 3.1 系統(tǒng)結(jié)構(gòu)簡述 本系統(tǒng)包括六個(gè)實(shí)體：訂票人，訂票單信息，車票信息，車次信息，操作員，余票信息。 訂票人與訂票單、訂票單與車票信息這兩個(gè)關(guān)系

7、是一對一的聯(lián)系；車票信息與車次信息、 訂票單信息與操作員這兩個(gè)關(guān)系是1對多的聯(lián)系。 3.2 數(shù)據(jù)處理3.2.1. 查詢所有訂票人信息： 輸入：身份證號(hào) 輸出：訂票單號(hào)，訂票人身份證號(hào)，訂單時(shí)間，付款取票與否，車次號(hào)，車票號(hào) ，票價(jià)，員工號(hào)。3.2.2. 查詢訂票人的已定車票的信息： 輸入：身份證號(hào) 輸出：車票號(hào)，始發(fā)站，車次號(hào)，發(fā)車時(shí)間，終點(diǎn)站，車票種類。3.2.3. 查詢某一車上乘坐的乘客的信息： 輸入：車次號(hào) 輸出：姓名，身份證號(hào)，聯(lián)系電話，目的城市，始發(fā)城市。3.2.4 查詢余票數(shù)量 輸入：乘車區(qū)間和車次號(hào) 輸出：余票數(shù)量。 3.3生成E-R圖始發(fā)站目的站訂票人聯(lián)系電話姓名11n1n11

8、111出發(fā)日期余票張數(shù)出發(fā)站目的站車次號(hào)余票信息查詢終點(diǎn)站車次號(hào)始發(fā)站途徑車站票價(jià)員工號(hào)車次信息屬于車票種類發(fā)車時(shí)間訂單號(hào)車票號(hào)始發(fā)站終點(diǎn)站車廂號(hào)座位號(hào)取票與否車票信息屬于訂票點(diǎn)號(hào)姓名電話性別票價(jià)操作員操作訂單時(shí)間車票號(hào)員工號(hào)身份證號(hào)車次號(hào)訂單號(hào)訂單號(hào)訂票信息屬于身份證號(hào)車票種類車次號(hào)訂票人表（身份證號(hào)，姓名，聯(lián)系電話，始發(fā)城市，目的城市，訂單號(hào)）操作員表（員工號(hào)，訂票點(diǎn)號(hào)，姓名，性別，電話）車次信息表（車次號(hào)，始發(fā)站，終點(diǎn)站，途徑站）訂票單表（訂單號(hào)，身份證號(hào)，訂單時(shí)間，付款取票與否，車次號(hào)，車票號(hào)，票價(jià)，員工號(hào)）車票信息表（車票號(hào)，車次號(hào)，始發(fā)站，發(fā)車時(shí)間，終點(diǎn)站，車票種類，訂單號(hào)，車廂號(hào)

9、，座位號(hào)）說明：車票種類為：“無座”或“硬座”或“硬臥”或“軟臥”車次號(hào)訂票人表（身份證號(hào)，姓名，聯(lián)系電話，始發(fā)城市，目的城市，訂單號(hào)）操作員表（員工號(hào)，訂票點(diǎn)號(hào)，姓名，性別，電話）車次信息表（車次號(hào)，始發(fā)站，終點(diǎn)站，途徑站）訂票單表（訂單號(hào)，身份證號(hào)，訂單時(shí)間，付款取票與否，車次號(hào)，車票號(hào)，票價(jià)，員工號(hào)）車票信息表（車票號(hào)，車次號(hào)，始發(fā)站，發(fā)車時(shí)間，終點(diǎn)站，車票種類，訂單號(hào)，車廂號(hào)，座位號(hào)）說明：車票種類為：“無座”或“硬座”或“硬臥”或“軟臥”車次號(hào)訂票人表（身份證號(hào)，姓名，聯(lián)系電話，始發(fā)城市，目的城市，訂單號(hào)）操作員表（員工號(hào)，訂票點(diǎn)號(hào)，姓名，性別，電話）車次信息表（車次號(hào)，始發(fā)站，終點(diǎn)

10、站，途徑站）訂票單表（訂單號(hào)，身份證號(hào)，訂單時(shí)間，付款取票與否，車次號(hào)，車票號(hào)，票價(jià)，員工號(hào)）車票信息表（車票號(hào)，車次號(hào)，始發(fā)站，發(fā)車時(shí)間，終點(diǎn)站，車票種類，訂單號(hào)，車廂號(hào)，座位號(hào)）說明：車票種類為：“無座”或“硬座”或“硬臥”或“軟臥”車次號(hào)訂票人表（身份證號(hào)，姓名，聯(lián)系電話，始發(fā)城市，目的城市，訂單號(hào)）操作員表（員工號(hào)，訂票點(diǎn)號(hào)，姓名，性別，電話）車次信息表（車次號(hào)，始發(fā)站，終點(diǎn)站，途徑站）訂票單表（訂單號(hào)，身份證號(hào)，訂單時(shí)間，付款取票與否，車次號(hào)，車票號(hào)，票價(jià)，員工號(hào)）車票信息表（車票號(hào)，車次號(hào)，始發(fā)站，發(fā)車時(shí)間，終點(diǎn)站，車票種類，訂單號(hào)，車廂號(hào)，座位號(hào)）說明：車票種類為：“無座”或“硬

11、座”或“硬臥”或“軟臥”4邏輯結(jié)構(gòu)設(shè)計(jì)4.1 關(guān)系模式訂票人表（身份證號(hào)，姓名，聯(lián)系電話，始發(fā)城市，目的城市，訂單號(hào)）操作員表（員工號(hào)，訂票點(diǎn)號(hào)，姓名，性別，電話）車次信息表（車次號(hào)，始發(fā)站，終點(diǎn)站，途徑站）訂票單表（訂單號(hào)，身份證號(hào)，訂單時(shí)間，付款取票與否，車次號(hào)，車票號(hào)，票價(jià)，員工號(hào)）車票信息表（車票號(hào)，車次號(hào)，始發(fā)站，發(fā)車時(shí)間，終點(diǎn)站，車票種類，訂單號(hào)，車廂號(hào)，座位號(hào)）說明：車票種類為：“無座”或“硬座”或“硬臥”或“軟臥”余票信息表（車次號(hào)，車票種類，出發(fā)站，目的站，出發(fā)時(shí)間）5 物理結(jié)構(gòu)設(shè)計(jì)訂票人表字段類型特殊屬性身份證char(18)主鍵,非空姓名char(10)NOT NULL聯(lián)

12、系電話varchar(12)NOT NULL始發(fā)城市varchar(10)NOT NULL目的城市varchar(10)NOT NULL訂單號(hào)char(20)外鍵訂票單表字段類型特殊屬性訂單號(hào)char(20)主鍵身份證號(hào)char(18)外鍵訂票時(shí)間datetimeNOT NULL付款取票與否char(2)NOT NULL，默認(rèn)是或“否”車次號(hào)varchar(10)LIKEA-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í)間date

13、timeNOT NULL車廂號(hào)intNOT NULL座位號(hào)intNOT NULL終點(diǎn)站varchar(10)NOT NULL車票種類char(20)IN(軟臥，硬臥，軟座，硬座，站票)訂單號(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)NOT NULL，默認(rèn)男或女電話varchar(

14、12)NOT NULL 余票信息表字段類型特殊屬性車次號(hào)varchar(10)主鍵車票種類char(10)主鍵出發(fā)站varchar(10)NOT NULL目的站varchar(10)NOT 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='E:數(shù)據(jù)庫網(wǎng)上訂票數(shù)據(jù)庫.mdf',size=5MB,FILEGROWTH=10%)log on(name=網(wǎng)上訂票數(shù)據(jù)庫_log, filename='E:數(shù)據(jù)

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

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

17、ry key,身份證號(hào) char(18) references 訂票人表(身份證號(hào)),訂票時(shí)間 datetime not null,付款取票與否 char(2) check(付款取票與否 in('是','否'),車次號(hào) varchar(10) 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) var

18、char(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(車票種類 in('無座','硬座','硬臥','軟臥'),訂單號(hào) char(20) references 訂票單表(訂單號(hào)),車廂號(hào) int not null,座位號(hào) int not null)創(chuàng)建余票信息表： create table 余票信息表 (車次號(hào) varch

19、ar(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 exists (select * from sysindexes where name='pID_index')drop index 訂票人表.pID_indexcreate unique index pID_indexon

20、 訂票人表(身份證號(hào))with fillfactor=30訂票單表：IF exists (select * from sysindexes where name='piaoDanNO_index')drop index 訂票單表.piaoDanNO_indexcreate index piaoDanNO_indexon 訂票單表(訂單號(hào))車票信息表：IF exists (select * from sysindexes where name='piaoNO_index')drop index 車票信息表表.piaoNO_indexcreate index pia

21、oNO_indexon 車票信息表(車票號(hào))車次信息表：IF exists (select * from sysindexes where name='cheCiNO_index')drop index 車次信息表.cheCiNO_indexcreate index cheCiNO_indexon 車次信息表(車次號(hào))操作員表：IF exists (select * from sysindexes where name='yuanGongNO_index')drop index 操作員表.yuanGongNO_indexcreate index yuanGong

22、NO_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 訂票人表.身份證號(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)查詢訂票人信息：cre

23、ate procedure pInfo_procpID char(18)asselect 身份證號(hào),姓名,聯(lián)系電話,始發(fā)城市,目的城市,訂單號(hào)from 訂票人表 where 身份證號(hào)=pID創(chuàng)建存儲(chǔ)過程，通過身份證號(hào)查詢訂票人訂票信息：create procedure ticket_procpID 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 tri

24、gger 余票更新on 車票信息表for insert asdeclare 車次號(hào) varchar(10)select 車次號(hào)=車次號(hào) from insertedupdate 余票信息表set 余票張數(shù)=余票張數(shù)-1where 車次號(hào)=車次號(hào)create trigger 余票更新2on 車票信息表for delete asdeclare 車次號(hào) varchar(10)select 車次號(hào)=車次號(hào) from deletedupdate 余票信息表set 余票張數(shù)=余票張數(shù)+1where 車次號(hào)=車次號(hào)6.7插入數(shù)據(jù)向操作員表中插入數(shù)據(jù)：insert into 操作員表values('20

25、11060001','洛陽','魯金龍','男','#39;)向車次信息表中插入數(shù)據(jù)：insert into 車次信息表values('K31','福州','洛陽','商丘、開封、鄭州')向訂票人表中插入數(shù)據(jù)：insert into 訂票人表values('#39;,'魯金龍','#39;,'民權(quán)', '洛陽','20110624093806000001')向訂票單表中插入數(shù)據(jù)：insert into 訂票單表values('20110624093806000001','#39;,'2011-06-24 14:31:36', '是','K31','20110624093806037901',12.5,'2011060001')向車票信息表中插入數(shù)據(jù)：insert into 車票信息表values('2011062