火車站票務(wù)管理數(shù)據(jù)庫設(shè)計說明書

1、火車站票務(wù)管理系統(tǒng)數(shù)據(jù)庫設(shè)計說明書擬制人李毅丁黎審核人批準(zhǔn)人、問題描述車票銷售時不能超員，并自動修改剩余的座位數(shù)；創(chuàng)建存儲過程統(tǒng)計指定車次指定發(fā)車時間的車票銷售情況；創(chuàng)建存儲過程統(tǒng)計指定日期各業(yè)務(wù)員車票的銷售收入；退票時自動修改相應(yīng)車次的剩余座位數(shù)；創(chuàng)建表間關(guān)系。車次管理：記錄各車次及發(fā)車時間，實現(xiàn)車次的調(diào)度。價格管理：根據(jù)里程記費(fèi)到各站的價格。業(yè)務(wù)員管理：記錄各業(yè)務(wù)員的信息。售票管理：銷售火車票，記錄售票信息。退票管理：實現(xiàn)退票，記錄退票信息二、需求分析現(xiàn)今世界的發(fā)展速度極快，數(shù)據(jù)量不斷增大，人工的管理已經(jīng)不能精確的管 理，或者沒有那么多的時間整理，時?？赡艹霈F(xiàn)大量的錯誤，導(dǎo)致數(shù)據(jù)不一致，

2、數(shù)據(jù)丟失和混亂現(xiàn)象。計算機(jī)在科技的不斷創(chuàng)新和發(fā)展中產(chǎn)生并不斷更新，存儲 量和計算次數(shù)極大，在火車票銷售和火車站的一些管理中，計算機(jī)便在這里能起 到極大的作用，可以幫助人們實現(xiàn)大量的各種數(shù)據(jù)的管理，有效的減輕人們的工 作量，提高管理效率和精確度，防止數(shù)據(jù)丟失和錯誤等，便利了我們的生活節(jié)約 了大量時間和精力。在此，利用計算機(jī)建立火車票務(wù)管理系統(tǒng)顯得由為重要。三、概念模型設(shè)計1火車站票務(wù)管理系統(tǒng)我設(shè)計了九張表來存儲所有有用信息：管理員表（包含屬性：工號，姓名，密碼，聯(lián)系電話）車次表（包含屬性：車次號，發(fā)車時間，座位數(shù)）業(yè)務(wù)員表（包含屬性：工號，姓名，密碼，聯(lián)系電話）火車票表（包含屬性：票號，車次號，

3、發(fā)車時間，座位號，車站號，價 格）乘客表（包含屬性：身份證號，姓名，密碼，聯(lián)系電話）車站表（包含屬性：車站號，站名）售票表（包含屬性：工號，票號，售票時間）退票表（包含屬性：身份證號，票號，退票時間）售票情況（包含屬性：車次，發(fā)車時間，剩余票數(shù)）2表間對應(yīng)的局部和整體ER圖如下:局部E-R圖車次管理售票情況車站剩余票售票管理退票管理局部E-R圖3、數(shù)據(jù)流圖四、邏輯結(jié)構(gòu)設(shè)計1、關(guān)系模式 注（關(guān)系的碼用下橫線標(biāo)出）實體：管理員（工號,姓名，密碼，聯(lián)系電話）車次（車次號，發(fā)車時間,座位數(shù)）業(yè)務(wù)員（工號,姓名，密碼，聯(lián)系電話）火車票（票號,車次號，發(fā)車時間，座位號，價格）乘客（身份證號,姓名，密碼，聯(lián)

4、系方式）車站（車站號，站名）聯(lián)系：售票（工號，票號,售票時間）退票（身份證號，票號，退票時間）售票情況（車次，發(fā)車時間,剩余票數(shù)）2、所有表之間的關(guān)系圖如下:admin母 job_num job_name password te_numstation仙揪 sta_numsta_namepassenger礦揪d_num p_name password te_numticket* tiket_numtr_numd_teseat_num sta_numpne五物理結(jié)構(gòu)設(shè)計各表名稱及所包含的屬性，數(shù)據(jù)類型和長度，主外鍵關(guān)系如下幾張表所示:管理員表(admin)列名說明類型長度允許空主/外鍵job_nu

5、m工號char5主Job_name姓名char8password密碼char6tele_num聯(lián)系電話char11車次表(train_num)列名說明類型長度允許空主/外鍵tr_num車次號char5主d_time發(fā)車時間char12主seat_sum座位數(shù)int業(yè)務(wù)員表(seller)列名說明類型長度允許空主/外鍵job_num工號char10主Job_name姓名char8password密碼char6tele_num聯(lián)系電話char11火車票表(ticket)列名說明類型長度允許空主/外鍵ticket_num票號char12主tr_num車次號char5外d_time發(fā)車時間char12

6、外seat_num座位號char5sta_num車站號char5price價格int乘客表(passenger)列名說名類型長度允許空主/外鍵id_num身份證號char18主p_name姓名char8password密碼char6tele_num聯(lián)系電話char11車站表(station)列名說名類型長度允許空主/外鍵sta_num車站號char5主sta_name站名char10售票表(sell_ticket)列名說名類型長度允許空主/外鍵job_num工號char10主、夕卜ticket_num票號char12主、夕卜sell_time售票時間char12退票表(return_ticke

7、t)列名說名類型長度允許空主/外鍵id_num身份證號char18主ticket_num票號char12外r_time退票時間char12售票情況表I(remain)列名說名類型長度允許空主/外鍵tr_num車次char5主、夕卜d_time發(fā)車時間char12主、夕卜re_ticket剩余票數(shù)int12六、數(shù)據(jù)庫實施/*數(shù)據(jù)庫表create table train_numtr_numchar 5)not null,d_timechar 12) not null,seat_sum int not null, primary key(tr_num d_time )create table sel

8、ler (job_num char (10 not null, job_name char ( 8 ) not null, password char (6) not null, tele_num char (11) not null, primary key(job_numcreate table ticket(ticket_num char (12 ) not null,tr_numchar(5notnull,d_timechar(12notnull,seat_num char(5notnull,sta_numchar(5notnull,priceintnot null,primaryke

9、y(ticket_num,foreign key(tr_num references train_num(tr_num ), foreign key(d_time) references train_num(d_time)create table passenger (id_num char (18 not null,p_name char (8) not null,password char (6) not null,tele_num char (11) not null,primary key(id_num)create table station(sta_numchar (5) not

10、null,sta_name char (12 not null,primary key(sta_num)create table adminjob_num char (10notnull,job_name char(8notnull,password char (6notnull,tele_num char (11notnull,primary key(job_num )create table sell_ticketjob_num char (10 not null,ticket_num char 12) not null, sell_time char(12) not null, prim

11、ary key(job_num ticket_num , foreign key(job_num references seller job_num ), foreign key(ticket_num) references ticket(ticket_numcreate table return_ticketid_numchar (18) not null,ticket_num char (12) not null,r_timechar (12) not null,primary key(id_num ticket_num ,foreign key(id_num references pas

12、senger(id_num ),foreign key(ticket_num) references ticket ticket_num )create table remain(tr_num char (5) not null,d_time char (12 not null,re_ticket int not null,primary key(tr_num d_time re_ticket),foreign key(tr_num references train_num(tr_num ),foreign key(d_time references train_num(d_time )七、功

13、能代碼1觸發(fā)器use traingo-車票銷售時不能超員，并自動修改剩余的座位數(shù)create trigger tri_ticketon ticket for insertas begindeclare tr_num char (5),-記錄插入的車次dtimchar (12),-該車次的發(fā)車時間seat_suint, sell_suint select tr_num tr_num select d_time d_time select seatsum=seat-記錄該車次的總座位數(shù)-記錄該車次總的售票數(shù)from insertedfrom insertedsum from trainnumsel

14、ect sell sum=count(*)from ticket where tr num tr num and dtime=dtimeif sell_sum seat_sum rollback transactionupdate remain set re_ticket re_ticket 1 where tr_num= tr_num and d_time = d_timeendgo-退票時自動修改相應(yīng)車次的剩余座位數(shù)create trigger tri_return_ticketon return_ticket for insertas begindeclare ticket_num ch

15、ar(12),tr_num char 5),d_time char (12select ticket_num ticket_num from insertedselect tr_num tr_num ,d_time d_timefrom ticketwhere ticket_num=ticket_numupdate remain set re_ticket =re_ticket 1where tr_num tr_num and d_time d_timeend2、存儲過程-指定車次指定發(fā)車時間的車票銷售情況use trainif object_id(pro_selled,p) is not n

16、ulldrop procedure pro_selledgocreate procedure pro_selledtr_num_in char(5),d_time_in char(12),sum_ticket int outputas beginselect count(ticket_numfrom ticketwhere tr_num tr_num_in and d_time = d_time_in endgo-指定日期各業(yè)務(wù)員車票的銷售收入use trainif object_id(pro_income,p) is not nulldrop procedure pro_incomegocreate procedure pro_incomesell_time char 12),job_name char(8) output,money int outputas beginselect job_name , sum(pricefrom seller sell_ticket,ticketwhere seller job_num sell_ticket j