公交查询系统后台数据库设计

[sql]代码库

1、数据库设计
stop_route(站点,路线名,站点在路线中的位置)
例如：如果有以下3条路线
R1: S1->S2->S3->S6->S4
则对应的关系表stop_route为
Stop  Route  Position
S1     R1       1
S2     R1       2
S3     R1       3
S6     R1       4
S4     R1       5

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.Position<sr2.Position
        and sr1.Stop=@StartStop
        and sr2.Stop=@EndStop
end

3.查询换乘路线算法
 (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.Position<sr2.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,
        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