1、大型數據庫課程設計設計報告題 目：火車站票務管理系統后臺數據庫學 號： gggg 學生姓名: ddd 指導教師： fgg 提交時間： 2013-11-23 目錄第1章需求分析21.1需求調查21.2系統功能分析21.3面對用戶需求分析3第2章面向對象分析和設計4類和對象設計如下：4第3章邏輯結構設計63.1類和對象向關系模式轉換9第4章數據庫物理結構設計94.1存取方法設計94.2存儲結構設計94.3物理設計9第5章數據庫完整性設計95.1主鍵及唯一性索引125.2參照完整性設計125.3Check約束135.4Default約束135.5觸發器設計13第6章 數據庫視圖設計13第7章 數據庫

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

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

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

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

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

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

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

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

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

11、級char(10),primary key (員工編號)-5、建立車次表: create table 車次表(車次號char(5) not null primary key,座位數int,發車時間char(5) null , 車種 char (4) )-6、建立車站表:Create table 車站表(車次號char(5) not null references 車次表(車次號),始發站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 車次表(車次號),發車時間datetime not null,座位編號char(5) not null,價格float not null,座位種類char(4)-8、建立售票表:create table 售票表( 員工編號char(6) not null references 業務員(員工編號),車票編號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,剩余票數int not null,發車時間datetime,foreign key (車次號) references 車次表(車次號)第5章數據庫完整性設計5.1主鍵及唯一性索引表名主鍵建立唯一性索引車次表(車次號)create unique index 車次表on 車次表(車次號 asc)業務員(員工編號)create unique index 業務員on 業務員(員工編號 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 參照完整性設計5.3Check約束1、 業務員表中將性別進行check約束：alter table 業務員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、售票表中售票日期默認值設為：2013-11-19 00:00。default('2013-11-19 00:00')2、業務員

17、表中將員工性別族默認值設為：女。default('女')3、員工獎勵表中將獎勵等級默認值設為：null。default('null')5.5 觸發器設計-1、車票銷售時不能超員，并自動修改剩余的座位數 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=剩余票數from 余票表update 余票

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

19、edupdate 員工售票情況set 售出票數=售出票數+1 where 員工編號=員工編號update 員工獎勵表set 獎勵等級='一等獎勵' where 員工編號=員工編號end go-觸發檢驗:insert into 售票表values('111002','20106','S223','2013/11/19 12:00:00')-3、退票時自動修改相應車次的剩余座位數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=剩余票數from 余票表update 余票表set 剩余票數=剩余票數+1where 車次號=t_num end-觸發檢驗:insert into 退票表values('20136','1234','2013-11-13 12:00')-4、退票時,根據退票時間判斷是否允許退票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=發車時間from 車票表where 車票編號=p_numif f_num-t_time>=2print'退票成功'elseprint'不能退票'end-觸發檢驗: insert into 退票表 values('20116','S213','2013-11-19 12:00') 第6章 數據庫

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

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

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

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

26、beginset nocount onselect * from 車票銷售視圖where 車次號=車次號Endexec 按車次查詢 'T146'-3、指定車次指定發車時間的車票銷售情況 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 發車時間=d_time_ingroup by

27、 車次號go-執行存儲過程exec p_selld 'G574', '2013/11/19 15:53:00','count(*)'-4、指定日期各業務員車票的銷售收入 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-執行存儲過程exec rp_selled '111002','2013/2/10 0:00:00','sum(價格)'-5、指定員工編號查詢售出票數create procedure 編號查詢員工編號char(6) output,售出票數char(6) outputasselect 員工編號,count(車票編號) as '售出票數'from 售票表where 員工編號=員工編號group by 員工編號goexec 編號查詢'111002','count(車票編號)'-6、改變指定

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

30、視圖.車次號and 車票表.車票編號=乘客表.車票編號and 身份證號=idgo-執行存儲過程exec s_tri1 第8章 權限設計-1、創建一級管理員的登錄名及用戶，并給一級管理員授權create login 一級管理員with password='123456',default_database=火車站票務管理數據庫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、創建二級管理員的登錄名及用戶，并給二級管理員授權create login 二級管理員with password='123456',default_database=火車站票務管理數據庫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、創建數據庫角色業務員并給業務員授權create role 業務員authorization dbogrant select on 車票表to 業務員grant select on 車次表to 業務員grant insert ,update,delete on 售票表to 業務員grant insert ,update,delete on 退票表to 業務員-4、創建數據庫角色乘客并給乘客授權create role 乘客authorization dbogrant select on 車票表to 乘客grant select on 車次表to 乘客grant select on 車站表to 乘客 總結通過這么多天的數據庫課程設計的學習，我受益匪淺，從中學到了許多新知識，這些知識是在課堂中不能學到或者說很難學到的。并且對sql server這一門課程有了更深一步的理解