Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2009
    Posts
    8

    Unanswered: database structure

    hi,

    I just need an idea of what structure to do for the following problem,

    I need to do a london underground database which has different lines and different stations, some stations have multiple lines, just wondering the best way to insert this into a table structure that can be queried for different journies etc..

    cheers

  2. #2
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Did you ask your prof for direction? Smells like homework to me.....Show us what you've come up with so far and we can provide some input.

    --Edit - Happy BDay by the way.

  3. #3
    Join Date
    Feb 2009
    Posts
    8
    hi,

    it is for a project i have got to do, it is going to have a web front end where people can search for palces and get a route that has the different stations, wheelchair access etc. i am ok with the front-end stuff using jsp, jdbc etc.. Just not sure about how to get the data structure right at the backend, it is a database module so i am sure the data structure must be a major factor.

    I was going to use a table for stations, linked to a table for the different lines, which is linked to an attractions table that is linked to the station id, this will work ok for matching the attraction to a specific station.

    I just need an idea for working out the route, line changes and time. I have created quite a few databases but nothing like this, i think also that a station can have multiple lines has baffled me slightly, i do not think it has to be too complex, but i have thought for a while now with no joy, I want to get it right at this stage because it can be a nightmare stepping backwards at a later date.

    I was thinking maybe a seperate table with routes for each line???

    i do not want anyone to do the work, just shove me in the right direction!!!! because i am going round in circles!!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Nice homework instruction.

    A part of the basic table structure will be the tables:
    STATION
    LINE

    A LINE can have (will have) many STATIONS.
    A STATION can be visited by many LINES.
    So you have a many-to-many relationship between LINE and STATION.

    So you have to create a junction table STATION_LINE that holds all the stations a line services.

    That's the easy part. Now the next part. Putting vehicles on the line: visiting stations at certain hour table moments.

    We're not going to make your whole homework. But we're willing to help you out if you present us your thought over solutions, alternatives, ...

    You will need a column NR_SEQUENCE somewhere to store the sequence in which the line connects the stations - first stationA, second stationB, third stationC, ... - In which table would you put that column in? and why?

    Think of some useful attributes for the different tables, like name, location, ...

    Edit: Oops, our replies crossed. I saw it's a project, ... anyway my offer still holds, but you will have to do some thinking.
    Last edited by Wim; 02-24-09 at 11:22.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you need to outline your business requirements, as this will affect the design. For example, there was no mention of route planning in post #1, and you mention attractions in paragraph 2 of post #2.

    One thing - are you sure there isn't a web service you can consume for much of this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2009
    Posts
    8
    hi,

    thanks wim, sound good pointers there, pootle flump the basic requirements are as follows,

    the basic rules are as follows,

    1.) start line to use,
    2.) interchange stations
    3.) nearest end station on a particular line
    4.) time taken to walk from the tube station to the attraction
    5.) total time taken to travel to the attraction, allowing on average 2 minutes for a train to travel between adjacent stations, 10 minutes to change tube lines at an interchange station
    6.) Information for wheel-chair travellers (if requested)

    cheers

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    1.) start line to use
    I think should read "start station to use".

    2.) interchange stations, can be derived from STATION_LINE:
    Code:
    SELECT id_station 
    FROM STATION_LINE SLa, STATION_LINE SLb 
    WHERE SLa.id_station = SLb.id_station AND 
          SLa.id_line <> SLb.id_line
    GROUP BY id_station
    HAVING COUNT(*) > 1
    3.) nearest end station on a particular line
    What is an "end station" on a line? Purpose?

    4.) time taken to walk from the tube station to the attraction
    Another table: ATTRACTION
    and another many-to-many relationship:
    An ATTRACTION can be close to different STATIONs
    Near one STATION can be many ATTRACTIONs.
    Use a junction table STATION_ATTRACTION, with column timeToAttraction?

    5.) total time taken to travel to the attraction, allowing on average 2 minutes for a train to travel between adjacent stations, 10 minutes to change tube lines at an interchange station
    Is this a good (realistic) starting point? If not, I would integrate this information somewhere into the model, giving this column the default value of 2 minutes. So it will be easy to replace with accurate values later.

    6.) Information for wheel-chair travellers (if requested)
    What is this information connected to? To ATTRACTIONs, STATIONs, both?

    Have you thought about the logic needed to calculate the path to go from a start_station A (line1) to a destination_station Z (possibly on another line5), the one nearest to the attraction, taking the fastest route? I think that will be the hardest nut to crack. I would calculate the paths in advance, so your system doesn't have to do it at runtime.
    Path from startStation stationA destinationAttraction AttrA (with stationA nearest theAttraction):
    Code:
    idpath nr_seq idStartStation idLine idDestStation
    23     1      stationA       Line1  stationQ
    23     2      stationQ       Line4  stationM
    23     3      stationM       Line5  stationZ
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Feb 2009
    Posts
    8
    hi wim,

    cheers for the post, i am getting close to a structure now. I think i could rustle up something near what is required apart from (the main logic!!!);

    The only bit that is still a big worry is the route planner, if it is on the same line i could just go from the start point to end point and bind the stations to the page in desc order, along with adding a total of the time column. But this is not practical for going backwards and also changing lines. In the brief it does mention that trains and platforms are labelled as East_bound, West_bound, North_bound or South_bound.

    The other couple of points from your reply,

    3.) The end station is the last station on that particular line, i do not know why this is needed???

    6.) Wheel-chair access comes from the tube map.

    cheers

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    3.) The end station is the last station on that particular line, i do not know why this is needed???
    Possibly to be able to say: take sub in station X direction end_station. But then what about East_bound, West_bound, ...?
    6.) Wheel-chair access comes from the tube map.
    I don't know how the London tube map looks like or what it contains. I guess the wheel-chair info is STATION bound.

    data structure to go from one station to another:
    Code:
    StartStation
    EndStation
    timeOfTrip
    CoDirection IN ('East_bound', 'West_bound', 
             'North_bound', 'South_bound')
    I wouldn't make CoDirection with CHARs, but you get the gist.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    London Tube

    -PatP

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan
    OMG, what a spiders web. I would not even attempt to calculate the subway path to get from start Station to destination Station. Do as pootle flump suggested, contact the London tube company and ask them for some web service you can use.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Feb 2009
    Posts
    8

    lol!!

    hi wim,

    yeah it is a bit of a tricky ask!! but it is coursework so it must be doable. I need to provide all table designs, sql code for creating the tables, queries, jdbc code and html etc.. so i cannot really use web services etc.. I am 100%sure the brains are in the db and all the web front end does is pass the values back and forth. The main sticking part is the route planner, the rest is not overly complex. So i think some crazy query might sort that one out!! If i can get the table structure and information in place, i may be able to sort that at the end!!

    The deadline is less than 2 weeks so i will keep you informed!!! I am going to do a bit at a time and see how far I get!! As of now i have very little concrete evidence, so I need to start getting something together.

    cheers

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For route planning you may want to google something like
    Code:
    "travelling salesman" sql
    I think this is a very difficult request indeed for a beginner in 2 weeks. You're not just designing this - you need it built, running and working in 2 weeks?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2009
    Posts
    8
    hi pootle,

    i have had the work for a couple of months and it is for an Msc, so there are no complaints really. I can do a lot in 2 weeks with this sort of stuff, i justed wanted a good plan in place before i go steamrolling in, I will just do what i normally do, make it up as i am going!!! this link looks quite interesting though,

    SQL Server Forums - Dijkstra's Shortest Path Algorithm

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Peter is an excellent SQL coder. His algorithms are worthy of a very close look, if you have the time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •