大型數(shù)據(jù)庫課程設(shè)計---火車站票務(wù)管理系統(tǒng)

1、大型數(shù)據(jù)庫課程設(shè)計設(shè)計報告題 目：火車站票務(wù)管理系統(tǒng)后臺數(shù)據(jù)庫學(xué) 號： gggg 學(xué)生姓名: ddd 指導(dǎo)教師： fgg 提交時間： 2013-11-23 目錄第1章需求分析21.1需求調(diào)查21.2系統(tǒng)功能分析21.3面對用戶需求分析3第2章面向?qū)ο蠓治龊驮O(shè)計4類和對象設(shè)計如下：4第3章邏輯結(jié)構(gòu)設(shè)計63.1類和對象向關(guān)系模式轉(zhuǎn)換9第4章數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計94.1存取方法設(shè)計94.2存儲結(jié)構(gòu)設(shè)計94.3物理設(shè)計9第5章數(shù)據(jù)庫完整性設(shè)計95.1主鍵及唯一性索引125.2參照完整性設(shè)計125.3Check約束135.4Default約束135.5觸發(fā)器設(shè)計13第6章 數(shù)據(jù)庫視圖設(shè)計13第7章 數(shù)據(jù)庫

2、存儲過程設(shè)計15第8章 權(quán)限設(shè)計17總結(jié)19參考文獻：20教務(wù)管理系統(tǒng)后臺數(shù)據(jù)庫第1章需求分析通過對火車站客運量、旅客和業(yè)務(wù)員的調(diào)查，該火車站票務(wù)管理系統(tǒng)有如下需求：1) 系統(tǒng)管理：實現(xiàn)系統(tǒng)管理人員對系統(tǒng)的管理，包括添加刪除用戶，更改密碼， 數(shù)據(jù)備份，數(shù)據(jù)還原，注銷等功能。2) 票務(wù)管理：實現(xiàn)對火車運行站點及時間的管理、對業(yè)務(wù)員的管理、對余票的更新及退票的管理。3) 基本信息：實現(xiàn)顯示火車及業(yè)務(wù)員的基本信息。4) 售票：在有剩余座位的情況下自動更新剩余的座位數(shù)，控制不超員。5) 查詢：包括實現(xiàn)車次查詢，業(yè)務(wù)員查詢。可以查詢火車的車種、編號、車廂數(shù)等；查詢售票員工作的車站名，及其年齡、性別等；6

3、) 服務(wù)器配置：對它進行配置可以使得在其他電腦上也照常使用。這樣不必每次都到數(shù)據(jù)庫中去更改。7) 幫助系統(tǒng)：幫助系統(tǒng)為用戶指明方向。1.2系統(tǒng)功能分析1、車次管理：用于對火車基本信息的錄入、查詢、修改、維護、刪除等常用功能。2、車次及價格管理（含到各站的價格）：可以按照車次或始發(fā)站、終點站兩種方式進行查詢，只需要輸入關(guān)鍵信息即可查到所需的車次的有關(guān)信息，包括車的類型、所剩票的類型、數(shù)量、票價等。3、實現(xiàn)業(yè)務(wù)員管理：用于對業(yè)務(wù)員基本信息的錄入、查詢、修改、維護、刪除等常用功能，并提供工作地點變動、獎懲登記等功能。4、實現(xiàn)車票銷售管理：車票銷售時不能超員，并自動修改剩余的座位數(shù)（用觸發(fā)器實現(xiàn)）；5

4、、創(chuàng)建存儲過程統(tǒng)計指定車次指定發(fā)車時間的車票銷售情況；6、創(chuàng)建存儲過程統(tǒng)計指定日期各業(yè)務(wù)員車票的銷售收入； 7、實現(xiàn)退票管理：由于一些原因，可能造成客戶要求退票，系統(tǒng)根據(jù)具體 的情況判定是否可以退票，進行退票。退票時自動修改相應(yīng)車次的剩余座位數(shù)。1.3面對用戶需求分析在火車站票務(wù)管理系統(tǒng)中，最主要的功能就是進行一系列的查詢和各類數(shù)據(jù)的管理。因此，可以將火車站票務(wù)管理系統(tǒng)分為管理系統(tǒng)（有數(shù)據(jù)變化）和查詢系統(tǒng)兩個子系統(tǒng)。而在所設(shè)計的火車站票務(wù)管理系統(tǒng)中，主要有三類用戶，即旅客用戶、業(yè)務(wù)員用戶、系統(tǒng)管理員。各類用戶在該系統(tǒng)中的需求不同，權(quán)限也不同。因此，為了更明確，更系統(tǒng)的了解用戶需求，我們還可以將

5、管理系統(tǒng)再細(xì)分為旅客管理系統(tǒng)，業(yè)務(wù)員管理系統(tǒng)，系統(tǒng)管理員管理系統(tǒng)。同樣，將查詢系統(tǒng)也按用戶職能進行細(xì)分。這樣細(xì)分后，看似把系統(tǒng)需求繁雜化了。其實不然，細(xì)分后使各用戶需求更加明確了，并且能更好的把握系統(tǒng)需求。下面從細(xì)分后的各個子系統(tǒng)分析子系統(tǒng)的需求： 火車站票務(wù)管理系統(tǒng)車次管理子系統(tǒng)車次及價格管理子系統(tǒng)業(yè)務(wù)員管理子系統(tǒng)車票銷售管理子系統(tǒng)退票管理子系統(tǒng)1） 車次管理系統(tǒng)，旅客可以通過該系統(tǒng)查詢相關(guān)車次的基本信息，火車途經(jīng)站點、出發(fā)時間、車的種類等。2） 車次及價格管理系統(tǒng)，旅客可以按照相關(guān)車次及車站查詢相對票價及時間。3）業(yè)務(wù)員管理系統(tǒng)，該系統(tǒng)中，主要顯示業(yè)務(wù)員的各項基本信息以及對業(yè)務(wù)員信息的管理

6、。 4）車票銷售管理系統(tǒng)，該系統(tǒng)主要是可以自動更新各個車次的剩余票數(shù)。 5) 退票管理系統(tǒng)，對由于各種原因需要退票的旅客提供退票服務(wù)，系統(tǒng)根據(jù)具體的情況判定是否可以退票，進行退票。退票時自動修改相應(yīng)車次的剩余座位數(shù)。第2章 面向?qū)ο蠓治龊驮O(shè)計類和對象設(shè)計如下： 第3章邏輯結(jié)構(gòu)設(shè)計局部ER圖1、 車次管理終點站終點站車次號開車時間到達時間始發(fā)站終點站 車站經(jīng)過火車始發(fā)站1n 途徑車站座位數(shù)發(fā)車時間相對距離2、售票員管理業(yè)務(wù)員始發(fā)站工作姓名業(yè)務(wù)員車站性別員工編號n1年齡途徑車站號終點站3、 車票銷售系統(tǒng)售票日期座位種類票價車票退票售票業(yè)務(wù)員性別年齡員工編號姓名車次號nm座位號發(fā)車時間nm退票日期車

7、票編號4、車票銷售超員管理車票編號號車次號座位剩余數(shù)終點站 座位種類票價發(fā)車時間對應(yīng)座位號號車票火車1n車次號發(fā)車時間始發(fā)站時座位數(shù)車種座位號車廂號號總體ER圖車種車次號到達時間開車時間始發(fā)站終點站 終點站車站經(jīng)過火車m始發(fā)站1 途徑車站名座位數(shù)1發(fā)車時間相對距離1車廂號對應(yīng)工作座位剩余數(shù)座位號 n售票日期n票價車票編號員工編號售票姓名車次號號 n車票業(yè)務(wù)員mn座位號退票性別 m n座位種類年齡發(fā)時退票日期3.1類和對象向關(guān)系模式轉(zhuǎn)換車次信息（車次號、座位數(shù)、發(fā)時、車種）業(yè)務(wù)員信息（員工編號、姓名、性別、年齡）員工獎勵表（員工編號、工作站點、獎勵等級）車站信息（車次號、始發(fā)站、途徑車站、途徑車

8、站、終點站）車票信息（車票編號、車次號、座位號、價格、發(fā)車時間、座位種類）退票信息（車票編號、車次號、退票日期）售票信息（員工編號、車次號、車票編號、售票日期）余票信息（車次號、發(fā)車時間、剩余票數(shù)）第4章數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計4.1 存取方法設(shè)計數(shù)據(jù)庫系統(tǒng)是多用戶共享的系統(tǒng)，對同一個關(guān)系要建立多條存儲路徑才能滿足多用戶的多種應(yīng)用要求。對于火車站票務(wù)管理系統(tǒng)來說，為了提高某些屬性(如：車票編號、車次號、座位號、座位類型等)的查詢速度，可以選擇聚簇存取的方法，即把這些屬性上具有相同值的元組集中放在連續(xù)的物理塊上。這樣在查詢時就會大大提高查詢速度。因此，該系統(tǒng)中選擇聚簇存取方法。4.2存儲結(jié)構(gòu)設(shè)計火車站

9、票務(wù)管理系統(tǒng)是一個大型復(fù)雜的計算機網(wǎng)絡(luò)信息系統(tǒng)，采用基于瀏覽器/服務(wù)器（B/S），客戶端/服務(wù)器（C/S）混合的應(yīng)用體系結(jié)構(gòu)來建設(shè)教務(wù)管理系統(tǒng)。數(shù)據(jù)庫管理系統(tǒng)采用Microsoft 公司推出的SQL Server 2000 或以上版本，并用SQL進行數(shù)據(jù)庫的建立和數(shù)據(jù)庫中數(shù)據(jù)的維護和查詢。4.3物理設(shè)計實現(xiàn)該設(shè)計的環(huán)境為Windows XP Professional + MS SQL Server 2005 或以上版本。一：建立火車站車票管理數(shù)據(jù)庫create database 火車站車票管理數(shù)據(jù)庫-1、創(chuàng)建管理員表:create table 管理員( 管理員編號char(6) not nul

10、l primary key, 姓名char(8) , 級別char(10)-2、創(chuàng)建乘客表:create table 乘客表( 身份證號char(18) not null primary key, 姓名char(8) , 車票編號char(5), )-3、建立業(yè)務(wù)員表:create table 業(yè)務(wù)員( 員工編號char(6) not null primary key, 姓名char(8) , 性別bit, 年齡int,)-4、創(chuàng)建員工獎勵表create table 員工獎勵表(員工編號char(6) not null references 業(yè)務(wù)員(員工編號),工作站點char(20),獎勵等

11、級char(10),primary key (員工編號)-5、建立車次表: create table 車次表(車次號char(5) not null primary key,座位數(shù)int,發(fā)車時間char(5) null , 車種 char (4) )-6、建立車站表:Create table 車站表(車次號char(5) not null references 車次表(車次號),始發(fā)站char(20) not null,途徑車站char(20) not null,途徑車站char(20) not null,途徑車站char(20) null,途徑車站char(20) null,途徑

12、車站char(20) null,終點站char(20) not null,primary key (車次號)-7、建立車票表:CREATE TABLE 車票表( 車票編號char(5) not null primary key ,車次號char(5) not null references 車次表(車次號),發(fā)車時間datetime not null,座位編號char(5) not null,價格float not null,座位種類char(4)-8、建立售票表:create table 售票表( 員工編號char(6) not null references 業(yè)務(wù)員(員工編號),車票編號c

13、har(5) not null references 車票表(車票編號),車次號char(5) not null references 車次表(車次號),售票日期datetime ,primary key (員工編號,車票編號) )-9、建立退票表:create table 退票表 ( 車票編號char(5) not null primary key,車次號char(5) not null references 車次表(車次號),退票時間datetime null , foreign key (車票編號) references 車票表(車票編號) )-10、建立余票表:create table

14、 余票表(車次號char(5) not null primary key,剩余票數(shù)int not null,發(fā)車時間datetime,foreign key (車次號) references 車次表(車次號)第5章數(shù)據(jù)庫完整性設(shè)計5.1主鍵及唯一性索引表名主鍵建立唯一性索引車次表(車次號)create unique index 車次表on 車次表(車次號 asc)業(yè)務(wù)員(員工編號)create unique index 業(yè)務(wù)員on 業(yè)務(wù)員(員工編號 asc)車站表(車站號)create unique index 車站表on 車站表(車站號 asc)售票表(車票號)create unique i

15、ndex 售票表on 售票表(車票號 asc)車票表(車票編號，車次號)create unique index 車票表on 車票表(車票編號 asc,車次號 asc)退票表(車票號)create unique index 退票表on 退票表 (車票號 asc)余票表(車次號)create unique index 余票表on 余票表(車次號 asc)5.2 參照完整性設(shè)計5.3Check約束1、 業(yè)務(wù)員表中將性別進行check約束：alter table 業(yè)務(wù)員add constraint sex check(性別in('1','0')2、 車票表中將座位編號進

16、行check約束：alter table 車票表add constraint zum check (座位編號>=12000 and 座位編號<=15000) 3、 車次表中車種進行check約束：alter table 車次表add constraint ccz check (車種in('普快','快車','特快','動車','高鐵')5.4Default約束1、售票表中售票日期默認(rèn)值設(shè)為：2013-11-19 00:00。default('2013-11-19 00:00')2、業(yè)務(wù)員

17、表中將員工性別族默認(rèn)值設(shè)為：女。default('女')3、員工獎勵表中將獎勵等級默認(rèn)值設(shè)為：null。default('null')5.5 觸發(fā)器設(shè)計-1、車票銷售時不能超員，并自動修改剩余的座位數(shù) create trigger tri_ticket on 售票表after insert as begindeclare p_num char(5),seat_sum int,t_num char(5)select p_num=車票編號,t_num=車次號from insertedselect seat_sum=剩余票數(shù)from 余票表update 余票

18、表set 剩余票數(shù)=剩余票數(shù)-1where 車次號=t_num endgo  -觸發(fā)檢驗:    insert into 售票表values('111011','20136','1234','12:00')-2、業(yè)務(wù)員售出車票后,自動更新員工售票情況表和員工獎勵表create trigger 業(yè)務(wù)員售票觸發(fā)器on 售票表after insertas begindeclare 員工編號char(5)select 員工編號='員工編號' from insert

19、edupdate 員工售票情況set 售出票數(shù)=售出票數(shù)+1 where 員工編號=員工編號update 員工獎勵表set 獎勵等級='一等獎勵' where 員工編號=員工編號end go-觸發(fā)檢驗:insert into 售票表values('111002','20106','S223','2013/11/19 12:00:00')-3、退票時自動修改相應(yīng)車次的剩余座位數(shù)create trigger tp_ticket on 退票表after insert as begindeclare p_num char(

20、5),seat_sum int,t_num char(5)select p_num=車票編號,t_num=車次號from insertedselect seat_sum=剩余票數(shù)from 余票表update 余票表set 剩余票數(shù)=剩余票數(shù)+1where 車次號=t_num end-觸發(fā)檢驗:insert into 退票表values('20136','1234','2013-11-13 12:00')-4、退票時,根據(jù)退票時間判斷是否允許退票create trigger t_p on 退票表instead of insertasbegin de

21、clare p_num char(5),t_time datetime,f_num datetimeselect p_num=車票編號,t_time=退票時間from insertedselect f_num=發(fā)車時間from 車票表where 車票編號=p_numif f_num-t_time>=2print'退票成功'elseprint'不能退票'end-觸發(fā)檢驗: insert into 退票表 values('20116','S213','2013-11-19 12:00') 第6章 數(shù)據(jù)庫

22、視圖設(shè)計-1、創(chuàng)建業(yè)務(wù)員表視圖create view 業(yè)務(wù)員表視圖with encryptionas select 業(yè)務(wù)員.員工編號,姓名,工作站點,獎勵等級from 業(yè)務(wù)員,員工獎勵表where 業(yè)務(wù)員.員工編號=員工獎勵表.員工編號with check option-2、創(chuàng)建車次管理視圖create view 車次管理視圖asselect 車次表.車次號,座位數(shù),發(fā)車時間 ,車種,始發(fā)站,終點站from 車次表,車站表where 車次表.車次號=車站表.車次號- 、創(chuàng)建車票表視圖create view 車票表視圖asselect 車票編號,車次號,發(fā)車時間,座位編號,價格,座位種

23、類from 車票表-4、創(chuàng)建車票銷售視圖create view 創(chuàng)建車票銷售視圖asselect 車次管理視圖.車次號, 車次管理視圖.發(fā)車時間 ,車種,始發(fā)站,終點站,價格,座位種類,剩余票數(shù)from 車次管理視圖, 車票表,余票表where 余票表.車次號=車票表.車次號and 車次管理視圖.車次號=余票表.車次號-5、創(chuàng)建車票銷售視圖create view 車票銷售視圖asselect 員工編號,售票表.車票編號,價格,售票日期from 車票表,售票表where 售票表.車票編號=車票表.車票編號-  6、創(chuàng)建售票表視圖create view 售票表視圖asselec

24、t 員工編號,車票編號,車次號,售票日期from 售票表 - 7、創(chuàng)建退票表視圖create view 退票表視圖asselect 車票編號,車次號,退票時間from 退票表-8、創(chuàng)建余票表視圖create view 余票表視圖asselect 車次號,剩余票數(shù),發(fā)車時間from 余票表-9、創(chuàng)建余票情況視圖1create view 余票情況視圖1asselect 余票表.車次號,發(fā)車時間from 余票表,售票表where 余票表.車次號=售票表.車次號-10、創(chuàng)建余票情況視圖2create view 余票情況視圖2asselect 售票表.車次號,發(fā)車時間from 余票表

25、,售票表where 余票表.車次號=售票表.車次號第7章 數(shù)據(jù)庫存儲過程設(shè)計-1、指定始發(fā)站和終點站查詢車次號、車種、價格、座位種類、剩余票數(shù)等信息create procedure btend 始發(fā)站char(20),終點站char(20)asbeginset nocount onselect * from 車票銷售視圖where 始發(fā)站=始發(fā)站and 終點站=終點站end-執(zhí)行存儲過程exec btend '鄭州','北京'-2、指定車次查詢始發(fā)站、終點站、車種、價格、座位種類、剩余票數(shù)等信息create procedure 按車次查詢車次號char(5)as

26、beginset nocount onselect * from 車票銷售視圖where 車次號=車次號Endexec 按車次查詢 'T146'-3、指定車次指定發(fā)車時間的車票銷售情況 create procedure p_selld t_num_in char(5),d_time_in datetime,sum_ticket char(6) outputasselect 車次號,count(*) as 'sum_ticket'from 余票情況視圖where 余票情況視圖.車次號=t_num_in and 發(fā)車時間=d_time_ingroup by

27、 車次號go-執(zhí)行存儲過程exec p_selld 'G574', '2013/11/19 15:53:00','count(*)'-4、指定日期各業(yè)務(wù)員車票的銷售收入 create procedure rp_selled y_num_in char(6) output,d_time_in datetime,money char(6) outputasselect 員工編號,sum(價格) as 'money'from 車票銷售視圖where 員工編號=y_num_in and 售票日期=d_time_ingroup b

28、y 員工編號go-執(zhí)行存儲過程exec rp_selled '111002','2013/2/10 0:00:00','sum(價格)'-5、指定員工編號查詢售出票數(shù)create procedure 編號查詢員工編號char(6) output,售出票數(shù)char(6) outputasselect 員工編號,count(車票編號) as '售出票數(shù)'from 售票表where 員工編號=員工編號group by 員工編號goexec 編號查詢'111002','count(車票編號)'-6、改變指定

29、員工的工作站點create procedure 工作站點變更員工編號char(6),工作站點char(20) asupdate 員工獎勵表set 工作站點=工作站點where 員工編號=員工編號goexec 工作站點變更 '111002','北京'-7、根據(jù)身份證號碼查詢乘客的車票信息create procedure s_tri1 id char(18)asselect 身份證號,姓名,乘客表.車票編號,車次管理視圖.車次號,車種,始發(fā)站,終點站,車票表.發(fā)車時間,座位編號,價格,座位種類from 乘客表,車次管理視圖,車票表where 車票表.車次號=車次管理

30、視圖.車次號and 車票表.車票編號=乘客表.車票編號and 身份證號=idgo-執(zhí)行存儲過程exec s_tri1 第8章 權(quán)限設(shè)計-1、創(chuàng)建一級管理員的登錄名及用戶，并給一級管理員授權(quán)create login 一級管理員with password='123456',default_database=火車站票務(wù)管理數(shù)據(jù)庫create role 一級管理員authorization dbocreate user 一級管理員for login 一級管理員with default_schema=dbogrant insert ,update,delete on 退票表to 一級管理

31、員grant insert ,update,delete on 車次表to 一級管理員grant insert ,update,delete on 車站表to 一級管理員-2、創(chuàng)建二級管理員的登錄名及用戶，并給二級管理員授權(quán)create login 二級管理員with password='123456',default_database=火車站票務(wù)管理數(shù)據(jù)庫create role 二級管理員authorization dbocreate user 二級管理員for login 二級管理員with default_schema=dbogrant insert ,update,de

32、lete on 退票表to 二級管理員grant insert ,update,delete on 車站表to 二級管理員-3、創(chuàng)建數(shù)據(jù)庫角色業(yè)務(wù)員并給業(yè)務(wù)員授權(quán)create role 業(yè)務(wù)員authorization dbogrant select on 車票表to 業(yè)務(wù)員grant select on 車次表to 業(yè)務(wù)員grant insert ,update,delete on 售票表to 業(yè)務(wù)員grant insert ,update,delete on 退票表to 業(yè)務(wù)員-4、創(chuàng)建數(shù)據(jù)庫角色乘客并給乘客授權(quán)create role 乘客authorization dbogrant select on 車票表to 乘客grant select on 車次表to 乘客grant select on 車站表to 乘客 總結(jié)通過這么多天的數(shù)據(jù)庫課程設(shè)計的學(xué)習(xí)，我受益匪淺，從中學(xué)到了許多新知識，這些知識是在課堂中不能學(xué)到或者說很難學(xué)到的。并且對sql server這一門課程有了更深一步的理解