Hi,

I need help in designing a data model for an underground railway system. Basically there are 3 entities (tables):

STATION:
- stat_id * (the id of the station)
- stat_name (the name of the station)
- type (station type, ie. whether it is an interchange station)

LINE:
- line_id * (the id of the line)
- line_name (the name of the line)

ATTRACTION:
- attr_id * (the id of the attraction)
- attr_name (attraction name)
- near_stat (the station which is nearest to the attraction)
- distance (the distance between the attraction and its nearest station)

Providing the starting station and the attraction to visit, the application will be able to generate the following outputs:
1. the start line to use
2. interchange stations
3. the nearest end station on a particular line
4. the time taken to walk from the tube station to the attraction
5. the total time taken to travel to the attraction

Please help me to improve the quality of my data model to fulfill the requirements. I know there are some attributes and relationships missing in my data model. I am confused about whether to add a new entity called NEXTSTATION or just add two columns in the STATION table showing the line and the next station id.

And how about the two terminals of each of the line? Should I add them as two columns of the LINE table or something else? Please advise.

Thanks a lot!!

Henry

Is the actual assignment (in the teacher's own words) posted anywhere? There appear to be some critical design issues missing from your description, and I'm not bright enough to figure a solution without them.

-PatP

The requirements are as follows:

Input to the system:
1. the starting station
2. the attraction to visit

Output from the system:
1. the start line to use
2. interchange stations
3. the nearest end station on a particular line
4. the time taken to walk from the tube station to the attraction
5. the total time taken to travel to the attraction

I am asked to design the data model to support this system. It is actually a travel planning system for the tourists. I am provided with a tube map (the map for all the station lines), and also a map with the distance of all the attractions to the nearest station.

Also, there are two assumptions:
1. It takes on average 2 minutes to travel between adjacent stations
2. 10 minutes to change lines at an interchange station

From the information above, we can easily calculate the output 4 and 5. But I am not sure how to improve the quality of my data model. Especially how to add the direction attribute of each line, since a line has two directions, pointing to either terminal station. My roughly sketched data model is in my previous message. Please help. Thanks.