Journey Planner

[123R 567]

Hiya
It would be much appreciated if any one cold help me solve this problem that I’ve got. First of all my problem is that I need to create an online journey planner for able and disabled persons to tourist attractions in London.
The user must specify what attraction they wish to visit, their starting station and if they are disabled on not. The system should then provide the following information: the start line to use, interchange stations (to change from line to another), the nearest end station on a particular line. I am going to be using servlets but I am a little confused on to how to go about doing the queries to get the correct information. I was thinking if the starting station and the destination station is on the same line then what the system could do is get there hop numbers and just minus one from the other to get the no of hops.
And my idea for the interchange is if the lines of the starting and destination stations do not match then find the next interchange, (an interchange is indicated in the STATION table by having ‘0’ under the hops column and ‘0’ under the line column). Oh if your confused with what hops means its basically the position of that station on that specific line. Once it finds the next interchange it looks to see if the destination stations line can be taken from that station ( this can looked up in the inter_line table). If it can then it can carry on till it finds its destination. I’m really confused on how to do these queries plz help!!!!!!!!!!!
Many thanks

The data model I have come up with so far is as follows:
CREATE TABLE LINE(
LINE_ID INTEGER(6) NOT NULL,
LINE_NAME VARCHAR(50),
PRIMARY KEY (LINE_ID) );



CREATE TABLE STATION(
STATION_ID INTEGER(6) NOT NULL AUTO_INCREMENT,
STATION_NAME VARCHAR(100),
LINE_ID INTEGER(6),
HOPS INTEGER(10),
DISABLED_ACCESS ENUM('0','1') NOT NULL,
PRIMARY KEY (STATION_ID),
INDEX (LINE_ID),
FOREIGN KEY (LINE_ID) REFERENCES LINE(LINE_ID) );


CREATE TABLE INTERCHANGE(
INTERCHANGE_ID INTEGER(6) NOT NULL AUTO_INCREMENT,
INTERCHANGE_NAME VARCHAR(100),
PRIMARY KEY (INTERCHANGE_ID) );


CREATE TABLE INTER_LINE(
INTERCHANGE_ID INTEGER(6),
LINE_ID INTEGER(6),
HOPS INTEGER(10),
PRIMARY KEY (INTERCHANGE_ID,LINE_ID),
INDEX (INTERCHANGE_ID),
FOREIGN KEY (INTERCHANGE_ID) REFERENCES INTERCHANGE(INTERCHANGE_ID),
INDEX (LINE_ID),
FOREIGN KEY (LINE_ID) REFERENCES LINE(LINE_ID) );



CREATE TABLE DISABLED_MEANS(
DISABLED_ID INTEGER(6) NOT NULL AUTO_INCREMENT,
STATION_ID INTEGER(6),
PRIMARY KEY (DISABLED_ID),
INDEX (STATION_ID),
FOREIGN KEY (STATION_ID) REFERENCES STATION(STATION_ID) );

CREATE TABLE ABLE_MEANS(
ABLE_ID INTEGER(6) NOT NULL AUTO_INCREMENT,
STATION_ID INTEGER(6),
PRIMARY KEY (ABLE_ID),
INDEX (STATION_ID),
FOREIGN KEY (STATION_ID) REFERENCES STATION(STATION_ID) );


CREATE TABLE ATTRACTIONS(
ATTRACTION_ID INTEGER(6) NOT NULL AUTO_INCREMENT,
ATTRACTION_NAME VARCHAR(100),
ADDRESS VARCHAR(100),
PRICE VARCHAR(100),
HOURS VARCHAR(200),
DISABLED_ID INTEGER(6),
ABLE_ID INTEGER(6),
PRIMARY KEY (ATTRACTION_ID),
INDEX (DISABLED_ID),
FOREIGN KEY (DISABLED_ID) REFERENCES DISABLED_MEANS(DISABLED_ID),
INDEX (ABLE_ID),
FOREIGN KEY (ABLE_ID) REFERENCES ABLE_MEANS(ABLE_ID) );