公交查詢系統(tǒng)—數(shù)據(jù)庫(kù)課程設(shè)計(jì)報(bào)告

1、 數(shù)據(jù)庫(kù)課程設(shè)計(jì)報(bào)告 班級(jí)：序號(hào)： 姓名： 地球科學(xué)學(xué)院地信系2013-06-28目錄數(shù)據(jù)庫(kù)課程設(shè)計(jì)1目錄2一、概述31.1背景說(shuō)明31.2開發(fā)環(huán)境3二、需求分析32.1用戶需求32.2主要功能32.3數(shù)據(jù)流圖32.4數(shù)據(jù)字典3三、數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì)4四、數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)24.1 關(guān)系表設(shè)計(jì)24.2 數(shù)據(jù)表關(guān)系圖2五、創(chuàng)建數(shù)據(jù)庫(kù)及其對(duì)象25.1 建數(shù)據(jù)庫(kù)和表25.2 查詢實(shí)現(xiàn)3五、軟件功能設(shè)計(jì)5六、界面設(shè)計(jì)6七、應(yīng)用程序67.1 站點(diǎn)查詢67.2 線路查詢77.3 站站查詢8八、實(shí)驗(yàn)數(shù)據(jù)示例10九、心得11一、概述1.1背景說(shuō)明軟件名稱：公交線路查詢系統(tǒng) 項(xiàng)目提出者： 項(xiàng)目開發(fā)者：

2、 用戶：廣大需要乘坐公交車的人群 1.2開發(fā)環(huán)境操作系統(tǒng)：Windows XP軟件配置：visual stutio2010，Microsoft SQL Server 2008 R2數(shù)據(jù)庫(kù)配置：Microsoft SQL Server 2008 R2在PC機(jī)中新建數(shù)據(jù)庫(kù)，建立各個(gè)數(shù)據(jù)表、關(guān)系圖。二、需求分析2.1用戶需求城市的擴(kuò)張使得公交線路越來(lái)越復(fù)雜，為了使得用戶更加方便地能夠獲得最新公交線路，“公交線路查詢工具”幫助人們解決這個(gè)問(wèn)題。本系統(tǒng)面向的對(duì)象是普通乘客，對(duì)于普通乘客來(lái)說(shuō)，他們最關(guān)心的就是查詢。系統(tǒng)也只對(duì)用戶提供信息查詢功能，并不對(duì)用戶開放對(duì)數(shù)據(jù)的編輯權(quán)限。對(duì)于任何用戶，只要瀏覽本系統(tǒng)

3、的首頁(yè)，就可以根據(jù)自己的需求進(jìn)行查詢。數(shù)據(jù)的修改、刪除、及時(shí)更新工作只能由管理人員實(shí)現(xiàn)并對(duì)系統(tǒng)進(jìn)行定期的維護(hù)，保證其運(yùn)行的穩(wěn)定性。2.2主要功能本系統(tǒng)從用戶的需求出發(fā)，可實(shí)現(xiàn)以下功能： 1、線路具體情況查詢：輸入線路名稱就可得到該線路的途經(jīng)站點(diǎn)，發(fā)車和末班車時(shí)間，票價(jià)等相關(guān)信息。2、經(jīng)過(guò)此站點(diǎn)的所有路線查詢：輸入站點(diǎn)名稱即可得出經(jīng)過(guò)該站點(diǎn)的所有線路名稱。3、站點(diǎn)間的點(diǎn)到點(diǎn)路線查詢：輸入出發(fā)站點(diǎn)和目的站點(diǎn)即可輸出可選擇的線路，包括中間站的轉(zhuǎn)乘。2.3數(shù)據(jù)流圖2.4數(shù)據(jù)字典1、數(shù)據(jù)項(xiàng)名稱數(shù)據(jù)類型長(zhǎng)度字段描述namevarchar5公交車的名稱start_timetime8公交車每天的最早發(fā)車時(shí)間e

4、nd_timetime8公交車每天的收班時(shí)間buslength1int2公交線路去程的站點(diǎn)數(shù)buslength2int2公交線路回程的站點(diǎn)數(shù)pt_pricefloat3普通車的票價(jià)gd_pricefloat3高等級(jí)車的票價(jià)disprice_ptfloat3普通車刷卡后的價(jià)格disprice_gdfloat3高等級(jí)車刷卡后的價(jià)格companyIDint1公司編號(hào)company_namevarchar30公司名稱station_idint3站點(diǎn)的編號(hào)station_namevarchar30站點(diǎn)的名稱orderidint2站點(diǎn)在一條線路中的位置typeIDint2線路類型編號(hào)，共12類type_n

5、amevarchar5線路類型名directionchar4公交行駛方向，“去程”或“回程”linevarchar500公交行駛途徑的站點(diǎn)2、數(shù)據(jù)流名稱說(shuō)明數(shù)據(jù)流來(lái)源數(shù)據(jù)流去向用戶查詢公交線路記錄信息記錄公交車線路查詢信息線路+站點(diǎn)查詢結(jié)果輸出3、處理過(guò)程名稱簡(jiǎn)述輸入的數(shù)據(jù)流處理輸出的數(shù)據(jù)流線路查詢根據(jù)公交線路查詢?cè)摼€路上的所有站點(diǎn)公交車編號(hào)根據(jù)編號(hào)查詢查詢出該線路上的所有公交車站點(diǎn)站點(diǎn)查詢根據(jù)站點(diǎn)查詢出經(jīng)過(guò)該站點(diǎn)的所有公交車編號(hào)站點(diǎn)名稱根據(jù)站點(diǎn)查詢公交車的編號(hào)路過(guò)該站點(diǎn)的所有公交車編號(hào)站站查詢根據(jù)兩個(gè)站點(diǎn)查詢出所有線路兩個(gè)不同的站點(diǎn)根據(jù)站點(diǎn)查詢出所有線路輸出查詢出的線路三、數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì)

6、根據(jù)需求分析，可知本系統(tǒng)有以下E-R圖：10 / 15文檔可自由編輯打印1、公交站點(diǎn)E-R圖 3、公交公司E-R圖2、公交車E-R圖 4、公交線路類型E-R圖5、合并E-R圖四、數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)4.1 關(guān)系表設(shè)計(jì)根據(jù)ER模型，對(duì)本系統(tǒng)設(shè)計(jì)出5個(gè)關(guān)系表：1、公交車表bus（name，start_time，end_time，buslength1，buslength2，pt_price， dis_price，gd_price，company_id,type_id）列名數(shù)據(jù)類型NULL約束說(shuō)明namevarchar(5)not nullprimary key公交車的名稱start_timetimen

7、ot null最早發(fā)車時(shí)間end_timetimenot null收班時(shí)間buslength1intnot null去程的站點(diǎn)數(shù)buslength2intnot null回程的站點(diǎn)數(shù)pt_pricefloatnull普通車的票價(jià)disprice_ptfloatnull普通車刷卡gd_pricefloatnull高等級(jí)車的票價(jià)disprice_gdfloatnull高等級(jí)車刷卡companyIDintnot nullforeign key references company(id),typeIDintnot nullForeignkey references bus_type(id)2、公交站

8、點(diǎn)station（id，name）列名數(shù)據(jù)類型NULL約束說(shuō)明idintnot nullprimary key站點(diǎn)的編號(hào)namevarchar(30)not null站點(diǎn)的名稱3、線路與站點(diǎn)關(guān)聯(lián)表bus_station（bus_name，station_id，orderid）記錄公交線路經(jīng)過(guò)站點(diǎn)的信息,并且按順序記錄各站點(diǎn),以此記錄線路與站點(diǎn)的關(guān)聯(lián)信息。列名數(shù)據(jù)類型NULL約束說(shuō)明bus_namevarchar(5)not nullprimary key參照bus(name)station_idintnot null參照station(id)orderidintnot null站點(diǎn)在一條線路中

9、的位置4、公交公司表company(id,name)列名數(shù)據(jù)類型NULL約束說(shuō)明idintNot nullprimary key公司編號(hào)nameVarchar(5)Not null公司名稱5、公交線路類型表bus_type(id,name)列名數(shù)據(jù)類型NULL約束說(shuō)明idintNot nullprimary key線路類型編號(hào)，共12類nameVarchar(10)Not null線路類型名6、公交線路表busline(bus_name,direction,line) 此表主要是為了方便依據(jù)線路來(lái)查詢站點(diǎn)信息。 列名數(shù)據(jù)類型NULL約束說(shuō)明bus_namevarchar(4)Not nullp

10、rimary key公司編號(hào)directionvarchar(4)Not null公交行駛方向，“去程”或“回程”linevarchar(500)Not null公交行駛途徑的站點(diǎn)4.2 數(shù)據(jù)表關(guān)系圖五、創(chuàng)建數(shù)據(jù)庫(kù)及其對(duì)象5.1 建數(shù)據(jù)庫(kù)和表下面是創(chuàng)建數(shù)據(jù)庫(kù)和表的過(guò)程，數(shù)據(jù)通過(guò)手動(dòng)輸入。1、創(chuàng)建數(shù)據(jù)庫(kù)create database buson(name = 'bus_data1', = 'C:Documents and SettingsAdministrator桌面busbus_data1.mdf')log on(name = 'bus_log'

11、, = 'C:Documents and SettingsAdministrator桌面busbus_log.ldf')2、創(chuàng)建表公交公司表create table company(id int primary key not null,name varchar(30) not null)公交線路類型表create table bus_type(id int primary key not null,name varchar(5) not null)公交站點(diǎn)create table station(id int primary key not null,name varchar

12、(15) not null)公交線路表create table busline(bus_name varchar(5),direction char(2) not null,line varchar(200) not null,primary key(bus_name,direction), foreign key(bus_name) references bus(name)公交車表create table bus(name varchar(5) primary key not null,start_time datetime not null,end_time datetime not nu

13、ll,buslength int not null,price float not null,companyID int not null,typeID int not null,foreign key(companyID) references company(id),foreign key(typeID) references bus_type(id)線路與站點(diǎn)關(guān)聯(lián)表create table bus_station(bus_name varchar(5),station_id int,orderid int not null,primary key(bus_name,station_id)

14、, foreign key(bus_name) references bus(name),foreign key(station_id)references station(id)5.2 查詢實(shí)現(xiàn)這里只介紹線路查詢和站站查詢的存儲(chǔ)過(guò)程實(shí)現(xiàn)，其它涉及到的功能通過(guò)SQLCommand或SqlDataAdapter實(shí)現(xiàn)，在后面的應(yīng)用程序設(shè)計(jì)中實(shí)現(xiàn)。1、線路具體情況查詢：if exists(select name from sysobjects where name = 'busline_info')drop procedure busline_infogocreate procedu

15、re busline_info input varchar(5),output varchar(700) outputasbegin select output = line from busline where busline.bus_name = input end go2、站點(diǎn)間的點(diǎn)到點(diǎn)路線查詢：if exists(select name from sysobjects where name = 'station_station')drop procedure station_stationgocreate procedure station_station input1

16、 varchar(30),input2 varchar(30), output1 varchar(5) output,output2 varchar(5) output,output3 varchar(5) output,output4 varchar(30) output,output5 varchar(30) outputasbegin declare stationID1 int,stationID2 int -換乘過(guò)程中的中間站點(diǎn) declare id1 int,id2 int -記錄起點(diǎn)和終點(diǎn)的ID select id1 = id from station where name =

17、input1 select id2 = id from station where name = input2 -查找直達(dá)線路 select output1 = a.bus_name from (select bus_name from bus_station where station_id = id1 ) a, (select bus_name from bus_station where station_id = id2 ) b where a.bus_name = b.bus_name -若不存在直達(dá)線路,則搜尋一次換乘路線 if not exists(select * from bu

18、s where name = output1)begin-查找中間站點(diǎn) select stationID1 = c.station_id from-input1能直達(dá)的站點(diǎn)集合c( select distinct station_id from bus_station where bus_name in(select bus_name from bus_station where station_id = id1 ) c,-input2能直達(dá)的站點(diǎn)集合d( select distinct station_id from bus_station where bus_name in(select

19、bus_name from bus_station where station_id = id2) dwhere c.station_id= d.station_idselect output1 = a.bus_name from(select bus_name from bus_station where station_id = id1 ) a,(select bus_name from bus_station where bus_station.station_id = stationID1 ) bwhere a.bus_name = b.bus_nameselect output2 =

20、 a.bus_name from(select bus_name from bus_station where station_id = id2) a,(select bus_name from bus_station where bus_station.station_id = stationID1 ) bwhere a.bus_name = b.bus_nameend -若不存在直達(dá)和一次換乘線路,則搜尋二次換乘路線 if not exists(select * from bus where name = output1) and not exists(select * from bus

21、where name = output2)begin-查找中間線路select output2 = a.bus_name from(select distinct bus_name from bus_station where station_id in(select station_id from bus_station where bus_name in(select bus_name from bus_station where station_id = id1)a,(select distinct bus_name from bus_station where station_id i

22、n (select station_id from bus_station where bus_name in(select bus_name from bus_station where station_id = id2)bwhere a.bus_name = b.bus_name-查找起點(diǎn)到中間線路的公交車select stationID1 = a.station_id from(select distinct station_id from bus_station where bus_name in(select bus_name from bus_station where stati

23、on_id = id1)a,(select station_id from bus_station where bus_name = output2) bwhere a.station_id = b.station_idselect output1 = c.bus_name from(select bus_name from bus_station where station_id = id1 ) c, (select bus_name from bus_station where station_id = stationID1) d where c.bus_name = d.bus_name

24、-查找中間線路到終點(diǎn)的公交車select stationID2 = a.station_id from(select distinct station_id from bus_station where bus_name in(select bus_name from bus_station where station_id = id2)a,(select station_id from bus_station where bus_name = output2) bwhere a.station_id = b.station_idselect output3 = c.bus_name from

25、(select bus_name from bus_station where station_id = id2 ) c, (select bus_name from bus_station where station_id = stationID2) d where c.bus_name = d.bus_nameendselect output4 = name from station where id = stationID1select output5 = name from station where id = stationID2end 五、軟件功能設(shè)計(jì)軟件共實(shí)現(xiàn)了3個(gè)大功能，即站點(diǎn)

26、查詢、線路查詢、站站查詢，其完成情況如下：（一）：檢查用戶是否輸入了站點(diǎn)或線路信息，如果沒(méi)有錄入，則提示：請(qǐng)輸入站點(diǎn)！或請(qǐng)輸入線路名?。ǘ簷z查用戶是否輸入的站點(diǎn)或線路是否存在，如果不存在，則提示：此站點(diǎn)不存在！或此線路不存在?。ㄈ喝绻畔⒍继顚懻_，則根據(jù)選定的查詢條件進(jìn)行查找，查找的具體實(shí)現(xiàn)為：（1）站點(diǎn)查詢：返回經(jīng)過(guò)此站點(diǎn)的所有線路，并提供一條線路經(jīng)過(guò)的所有站點(diǎn)。如果沒(méi)有結(jié)果，則提示：找不到此站點(diǎn)?。?）線路查詢：返回此線路的類型、最早和最晚時(shí)間、票價(jià)、經(jīng)過(guò)站點(diǎn)等信息。（3）站站查詢：返回從起點(diǎn)到終點(diǎn)的可行的一條線路，此線路可直達(dá)，如果沒(méi)有直達(dá)，則最多換乘兩次，并提示在哪里下車換乘

27、，以及所有車輛的站點(diǎn)信息。六、界面設(shè)計(jì)七、應(yīng)用程序7.1 站點(diǎn)查詢/查詢經(jīng)過(guò)給定站點(diǎn)的公交線路 protected void station_info(string input) SqlConnection myConnection = new SqlConnection(connectionString); myConnection.Open(); SqlCommand cmd = new SqlCommand("select bus_station.bus_name as 公?交?線?路¡¤名?,busline.direction as 方¤?向&#

28、168;°,line as 途ª?徑?站?點(diǎn)Ì? from bus_station,station,busline wher = '" + input + "'and dbo.station.id = bus_station.station_id and bus_station.bus_name = busline.bus_name", myConnection); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; System.Data

29、.DataSet ds = new System.Data.DataSet(); da.Fill(ds); this.GridView1.DataSource = ds.Tables0; GridView1.DataBind(); cmd = null; myConnection.Close(); 7.2 線路查詢/ 查詢給定線路的公交車所經(jīng)過(guò)站點(diǎn) protected string busline_info1(string input) string connectionString = "server=TXJM1FW3L1GYIC6;database = bus;Integrate

30、d Security = true" SqlConnection myConnection = new SqlConnection(connectionString); myConnection.Open(); SqlCommand myCommand = new SqlCommand("busline_info", myConnection); mandType = System.Data.CommandType.StoredProcedure; /創(chuàng)建參數(shù) SqlParameter bus_name = new SqlParameter("input

31、", System.Data.SqlDbType.VarChar, 5); bus_name.Direction = System.Data.ParameterDirection.Input; bus_name.Value = input; myCommand.Parameters.Add(bus_name); /添加輸出參數(shù) SqlParameter Result1 = new SqlParameter("output", System.Data.SqlDbType.VarChar, 700); Result1.Direction = System.Data.P

32、arameterDirection.Output; myCommand.Parameters.Add(Result1); myCommand.ExecuteNonQuery(); string str = Result1.Value.ToString(); return str; 7.3 站站查詢/查詢給定起點(diǎn)和終點(diǎn)的公交車線路 protected void station_station(string input1, string input2) string connectionString = "server=TXJM1FW3L1GYIC6;database = bus;Int

33、egrated Security = true" SqlConnection myConnection = new SqlConnection(connectionString); myConnection.Open(); /利用SQL中已有的存儲(chǔ)過(guò)程實(shí)現(xiàn)站站查詢 SqlCommand myCommand = new SqlCommand("station_station", myConnection); mandType = System.Data.CommandType.StoredProcedure;/創(chuàng)建參數(shù) SqlParameter station_na

34、me1 = new SqlParameter("input1", System.Data.SqlDbType.VarChar, 30); station_name1.Direction = System.Data.ParameterDirection.Input; station_name1.Value = input1; myCommand.Parameters.Add(station_name1); SqlParameter station_name2 = new SqlParameter("input2", System.Data.SqlDbTyp

35、e.VarChar, 30); station_name2.Direction = System.Data.ParameterDirection.Input; station_name2.Value = input2; myCommand.Parameters.Add(station_name2); /添加輸出參數(shù) SqlParameter Result1 = new SqlParameter("output1", System.Data.SqlDbType.VarChar, 5); Result1.Direction = System.Data.ParameterDire

36、ction.Output; myCommand.Parameters.Add(Result1); SqlParameter Result2 = new SqlParameter("output2", System.Data.SqlDbType.VarChar, 5); Result2.Direction = System.Data.ParameterDirection.Output; myCommand.Parameters.Add(Result2); SqlParameter Result3 = new SqlParameter("output3",

37、System.Data.SqlDbType.VarChar, 5); Result3.Direction = System.Data.ParameterDirection.Output; myCommand.Parameters.Add(Result3); SqlParameter Result4 = new SqlParameter("output4", System.Data.SqlDbType.VarChar, 30); Result4.Direction = System.Data.ParameterDirection.Output; myCommand.Param

38、eters.Add(Result4); SqlParameter Result5 = new SqlParameter("output5", System.Data.SqlDbType.VarChar, 30); Result5.Direction = System.Data.ParameterDirection.Output; myCommand.Parameters.Add(Result5); myCommand.ExecuteNonQuery(); string str1, str2, str3, str4, str5; str1 = Result1.Value.To

39、String(); str2 = Result2.Value.ToString(); str3 = Result3.Value.ToString(); str4 = Result4.Value.ToString(); str5 = Result5.Value.ToString(); myConnection.Close(); /界面提示信息 if (str1 = "" && str2 = "" && str3 = "") lab4_text.Text = "沒(méi)有找到合適的路線" el

40、se if (str1 != "" && str2 != "" && str3 = "") lab4_text.Text = "您可以先乘坐 " + str1 + " 在 " + str4 + " 下車，" + " 再乘坐 " + str2; lab5_name.Text = str1 + " 公交線路:" lab5_line.Text = busline_info1(str1); lab6_name.Text = str2 + " 公交線路:" lab6_line.Text = busline_info1(str2); else if (str1 != "" && str2 = "" && str3 = &qu