2022年2022年公交路线查询系统--数据库设计 .pdf

1. 公交车路线信息在数据库中的存储方式显然，如果在数据库中简单的使用表bus_route(路线名 ,路线经过的站点,费用 )来保存公交车路线的线路信息，则很难使用查询语句实现乘车线路查询，因此，应该对线路的信息进行处理后再保存到数据库中，考试大使用的方法是用站点-路线关系表stop_route(站点 ,路线名,站点在路线中的位置)来存储公交车路线,例如，如果有以下3 条路线R1:S1-S2-S3-S4-S5 R2:S6-S7-S2-S8 R3:S8-S9-S10 则对应的站点 -路线关系表stop_route 为Stop Route Position S1 R1 1 S2 R1 2 S3 R1 3 S4 R1 4 S5 R1 5 S6 R2 1 S7 R2 2 S2 R2 3 S8 R2 4 S8 R3 1 S9 R3 2 S10 R3 3 注： Stop 为站点名， Route 为路线名， Position 为站点在路线中的位置2.直达乘车路线查询算法基于表 stop_route 可以很方便实现直达乘车路线的查询，以下是用于查询直达乘车路线的存储过程InquiryT0 ：create proc InquiryT0(StartStop varchar(32),EndStop varchar(32) as begin select sr1.Stop as 启始站点 , sr2.Stop as 目的站点 , sr1.Route as 乘坐线路 , sr2.Position-sr1.Position as 经过的站点数from stop_route sr1, stop_route sr2 where sr1.Route=sr2.Route and sr1.Positionsr2.Position and sr1.Stop=StartStop and sr2.Stop=EndStop end 3.查询换乘路线算法名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 8 页 - - - - - - - - - (1)直达路线视图直达路线视图可以理解为一张存储了所有直达路线的表(如果两个站点之间存在直达路线，那么在直达路线视图中就有一行与之相对应) create view RouteT0 as select sr1.Stop as StartStop, -启始站点sr2.Stop as EndStop,-目的站点sr1.Route as Route,-乘坐线路sr2.Position-sr1.Position as StopCount-经过的站点数from stop_route sr1, stop_route sr2 where sr1.Route=sr2.Route and sr1.Positionsr2.Position (2)换乘路线算法显然， 一条换乘路线由若干段直达路线组成，因此， 基于直达路线视图RouteT0 可以很方便实现换乘查询，以下是实现一次换乘查询的存储过程InquiryT1 ：create proc InquiryT1(StartStop varchar(32),EndStop varchar(32) as begin select r1.StartStop as 启始站点 , r1.Route as 乘坐路线 1, r1.EndStop as 中转站点 , r2.Route as 乘坐路线 2, r2.EndStop as 目的站点 , r1.StopCount+r2.StopCount as 总站点数from RouteT0 r1, RouteT0 r2 where r1.StartStop=StartStop and r1.EndStop=r2.StartStop and r2.EndStop=EndStop end 同理可以得到二次换乘的查询语句create proc InquiryT2(StartStop varchar(32),EndStop varchar(32) as begin select r1.StartStop as 启始站点 , r1.Route as 乘坐路线 1, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 8 页 - - - - - - - - - r1.EndStop as 中转站点 1, r2.Route as 乘坐路线 2, r2.EndStop as 中转站点 2, r3.Route as 乘坐路线 3, r3.EndStop as 目的站点 , r1.StopCount+r2.StopCount+r3.StopCount as 总站点数from RouteT0 r1, RouteT0 r2, RouteT0 r3 where r1.StartStop=StartStop and r1.EndStop=r2.StartStop and r2.EndStop=r3.StartStop and r3.EndStop=EndStop end (3).测试exec InquiryT0 S1, S2exec InquiryT1 S1, S8exec InquiryT2 S1, S9运行结果 : 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 8 页 - - - - - - - - - 那么有没有方法可以提高筛选第2段路线的效率呢？答案是肯定的。只需把GRouteT0 改成实表，并创建索引就行了。修改成实表后，就不需要把第2段路线缓存到临时表#R2 中，修改后的 GInquiryT2( 重命名为 GInquiryT2_1)如下：GInquiryT2_1 /* 查询站点 StartStops到站点 EndStops之间的二次换乘乘车路线，多个站点用 / 分开，结果以分组方式给出，如：execGInquiryT2_1站点 1/ 站点 2, 站点 3/ 站点 4 */ CREATEprocGInquiryT2_1( StartStopsvarchar(2048), EndStopsvarchar(2048) ) as begin declaress_tabtable(StopKeyint) declarees_tabtable(StopKeyint) insertss_tabselectdistinctStop.StopKeyfromdbo.SplitString(StartStops,/)sn,Stop wheresn.Value=Stop.StopName insertes_tabselectdistinctStop.StopKeyfromdbo.SplitString(EndStops,/)sn,Stop wheresn.Value=Stop.StopName if(exists(selecttop1*fromss_tabsst,es_tabestwhere名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 8 页 - - - - - - - - - sst.StopKey=est.StopKey) begin raiserror( 起点集和终点集中含有相同的站点,16,1) return end declarestopstable(StopKeyint) insertstopsselectStopKeyfromss_tab insertstopsselectStopKeyfromes_tab print= print筛选出第 1段乘车路线 print- setstatisticstimeon - - 筛选出第 1段乘车路线，保存到临时表#R1 中select* into#R1 fromGRouteT0 whereStartStopKeyin(selectStopKeyfromss_tab) andEndStopKeynotin(SelectStopKeyfromstops) orderbyStartStopKey,EndStopKey - 在临时表 #R1 上创建索引createindexindex1on#R1(StartStopKey,EndStopKey) - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 8 页 - - - - - - - - - setstatisticstimeoff print= print筛选出第 3段乘车路线 print- setstatisticstimeon - - 筛选出第 3段乘车路线，保存到临时表#R3 中select* into#R3 fromGRouteT0 whereEndStopKeyin(selectStopKeyfromes_tab) andStartStopKeynotin(SelectStopKeyfromstops) orderbyStartStopKey,EndStopKey - 在临时表上创建索引createindexindex1on#R3(StartStopKey,EndStopKey) - setstatisticstimeoff print= print二次换乘查询 print- 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 8 页 - - - - - - - - - setstatisticstimeon - - 二次换乘查询selectss.StopNameas起点 , dbo.JoinRoute(res.StartStopKey,res.TransStopKey1)as路线 1, ts1.StopNameas中转站 1, dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2)as路线 2, ts2.StopNameas中转站 2, dbo.JoinRoute(res.TransStopKey2,res.EndStopKey)as路线 3, es.StopNameas终点 , MinStopCount from( - 查询出站点数最少的10 组路线selecttop10 r1.StartStopKeyasStartStopKey, r2.StartStopKeyasTransStopKey1, r2.EndStopKeyasTransStopKey2, r3.EndStopKeyasEndStopKey, (r1.MinStopCount+r2.MinStopCount+r3.MinStopCount)asMinStopCount from#R1r1,GRouteT0r2,#R3r3 wherer1.EndStopKey=r2.StartStopKeyandr2.EndStopKey=r3.StartStopKey orderby(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount)asc 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 8 页 - - - - - - - - - )res, Stopss, Stopes, Stopts1, Stopts2 where res.StartStopKey=ss.StopKeyand res.EndStopKey=es.StopKeyand res.TransStopKey1=ts1.StopKeyand res.TransStopKey2=ts2.StopKey - setstatisticstimeoff print= end名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 8 页 - - - - - - - - -