公交车查询的数据库设计

--一次都不换乘的情况
create view BusLine_NoTransfer
as
select bl2.Line,bl2.stop StartStop,bl1.stop EndStop,bl1.position-bl2.position StopCount
from BusLine bl1 ,BusLine bl2
where bl1.Line = bl2.Line and bl1.position > bl2.position


--一次换乘的情况
create view BusLine_TransferOnce
as
select startline.StartStop StartStop,EndLine.EndStop EndStop,
StartLine.Line firstLine,StartLine.EndStop TransStop,EndLine.Line secondLine,
StartLine.StopCount+EndLine.StopCount StopCount 
from BusLine_NoTransfer startline,BusLine_NoTransfer endline
where startline.line <> endline.line and startline.EndStop = EndLine.StartStop


--二次换乘的情况
create view BusLine_TransferTwice
as
select firstLine.StartStop StartStop,firstLine.Line line1,
firstLine.EndStop TransStop1,secondLine.Line line2,secondLine.EndStop TransStop2,
thirdLine.Line Line3,
thirdLine.EndStop EndStop,firstLine.StopCount+secondLine.StopCount+thirdLine.StopCount StopCount
from BusLine_NoTransfer firstLine,BusLine_NoTransfer secondLine,BusLine_NoTransfer thirdLine
where firstline.EndStop = secondLine.StartStop 
and secondLine.EndStop = thirdLine.StartStop
and firstLine.Line <> secondLine.Line
and firstLine.Line <> thirdLine.Line

and secondLine.Line <> thirdLine.Line