Hi,
I have the following tables set up for a bus timetable system.....
in its unnormalised format it is
BUS
(ROUTE_ID,STOP_ID,ATIME,R_ORDER,STOP_DESC,CODE)
I then normalised it by finding functional dependancies....
bus(ROUTE_ID,RUN,STOP_ID,CODE)
*ROUTE_ID is a unique bus number
*RUN is to say that a bus, say the 52 runs its route many times each day ie a 52 at 10:12am,10:22am,10:30am
*STOP_ID is a unique number for the stop. A stop may be used by many ROUTE_ID
*CODE is a number to say that a RUN may not operate on Sundays or bankholidays.
*ATIME is the time a bus(which is the same as ROUTE_ID as they are both unique and mean the same) is at a specified stop.
btime(ROUTE_ID,RUN,STOP_ID,CODE,ATIME)
stop_has_route(ROUTE_ID,STOP_ID)
order(ROUTE_ID,STOP_ID,R_ORDER)
*R_ORDER is an incrementing number which says the order in which stops are travelled.
stop(STOP_ID,STOP_DESC)
There is still alot of duplication in these tables, is this OK?
Anyway my main question is that I am trying to develop a query which can do the following..
"Give me a list of buses and times they run from X lane to Y lane on or after Z time."
Any ideas? I think this is very tough. It is assumed that the person stays on the same bus for the whole journey. It is only for a bus network in one town. What makes it harder is that a person may travel towards town or away from town, I have R_ORDER to try and model this.
If you need anymore information just shout!
Thank-you very much for your time and help!
Laura.
I have the following tables set up for a bus timetable system.....
in its unnormalised format it is
BUS
(ROUTE_ID,STOP_ID,ATIME,R_ORDER,STOP_DESC,CODE)
I then normalised it by finding functional dependancies....
bus(ROUTE_ID,RUN,STOP_ID,CODE)
*ROUTE_ID is a unique bus number
*RUN is to say that a bus, say the 52 runs its route many times each day ie a 52 at 10:12am,10:22am,10:30am
*STOP_ID is a unique number for the stop. A stop may be used by many ROUTE_ID
*CODE is a number to say that a RUN may not operate on Sundays or bankholidays.
*ATIME is the time a bus(which is the same as ROUTE_ID as they are both unique and mean the same) is at a specified stop.
btime(ROUTE_ID,RUN,STOP_ID,CODE,ATIME)
stop_has_route(ROUTE_ID,STOP_ID)
order(ROUTE_ID,STOP_ID,R_ORDER)
*R_ORDER is an incrementing number which says the order in which stops are travelled.
stop(STOP_ID,STOP_DESC)
There is still alot of duplication in these tables, is this OK?
Anyway my main question is that I am trying to develop a query which can do the following..
"Give me a list of buses and times they run from X lane to Y lane on or after Z time."
Any ideas? I think this is very tough. It is assumed that the person stays on the same bus for the whole journey. It is only for a bus network in one town. What makes it harder is that a person may travel towards town or away from town, I have R_ORDER to try and model this.
If you need anymore information just shout!
Thank-you very much for your time and help!
Laura.