Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    7

    Unanswered: DB Normalization, help please.

    Hello all,

    I'm working on a bus schedule database project that involves entering a lot of data and quite a bit of it is redundant, but I seem to be having some trouble trying to figure out how to "Normalize" the DB in the best way possible.

    Right now, the DB is actually in an Excel spreadsheet with all the "Required" fields.

    For instance, the fields are:
    Bus Number
    Route_Name
    Stop_Number
    Street_Name
    Intersection/Location
    Direction
    Weekday
    Time
    Details

    The Bus Number and Route Name are directly related, like "15 Sargent"

    Each bus/route can have up to 4 different directions of travel (North, South, East, West)

    Each bus/route has any number of stops, identified by Stop Number & Intersection/Location

    Each Stop has a list of scheduled times of when a bus should arrive at that location.

    The way that the spreadsheet is right now, all of the following are repeated for each "time" that a bus is scheduled to arrive at that particular location:

    Bus Number
    Route_Name
    Stop_Number
    Street_Name
    Intersection/Location
    Direction
    Weekday

    Please see the attached file for more info!

    So, as you can imagine, for a table that has 20 or more scheduled times, the data gets repeated pretty often.

    Also, the scheduled times change for Saturdays, Sundays & Holidays.

    If anyone is willing to take a look at the data that I have collected thus far and help me out with making it a more efficient and "Normalized" DB, it would be greatly appreciated.

    Ultimately, I want to be able to query the DB to get the next available times for a particular bus/route.

    For instance, if it's currently 4:36pm in the evening on a Monday, I want to be able to select/enter a stop number and the result will only display a list of the next 3 available scheduled times for the bus to arrive at that location.

    Did I forget to mention that I'm new to DB design?

    Anyway,
    Thanx for now,
    Cheers,
    ziggy
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The first thing you want to do is identify your objects. In your case, you have routes, stops, buses, days, and possibly intersections. You are on the right track as far as removing the duplicate data. Each record in each table should be unique. You also have to determine how each objects relates to other objects. Ask yourself can one bus be assigned to more than one route? Can one route have more than one stop? Can one stop be included in more than one route? The answers to those questions will help you determine if the relationship is one-to-one, one-to-many, or many-to-many.

    Without knowing all the details of the data, I would begin with a table for Routes and a table for stops (I personally use a naming convention for my tables, tblRoute and tblStop).

    tblRoute
    --------
    RouteNumber
    RoutName

    tblStop
    --------
    StopNumber
    StopStreetName
    StopIntersection

    Then I would add a third table. The reason is I believe you have a many-to-many relationship here. It is obvious that a route can have multiple stops, but it also seems likely that a stop can be on several routes. If I am correct then you will need tblRouteDetail (or somehting like it)

    tblRouteDetail
    -------------
    RouteNumber
    StopNumber
    DayID
    Time
    DirectionID

    You can also break out Day and Direction into separate tables. You can create a DayID which is just and AutoNumber field as well as DirectionID.

    That data would look like:

    tblDay
    ---------
    DayID
    Day

    Sample data might be:

    1-----Mon-Fri.
    2-----Saturday
    3-----Sunday
    4-----Holiday
    5-----Etc.

    Then I would create a table for Buses and a table for Bus Routes.

    tblBus
    -----
    BusID
    BusName (?)

    tblBusRoute
    -----------
    BusID
    RouteNumber

    The last bit depends on how Buses are assigned routes. If they never change then I would include the BusID in the RouteDetails.

    Hope that helps.

  3. #3
    Join Date
    May 2006
    Posts
    7
    Hi DCKunkle,

    I took some of your advice and created a new Database and added some of my own changes as well as some data that will NOT change (ie. Bus # & Route). However, I do think that you're right about the "many-to-many relationship", but I'm not sure how to implement it.

    I've attached the current database with some data, to get a better idea of what's going on.

    Also, here are some of the questions that I would ask (Query) the database:
    What are all the Routes available?
    What are all the Stops available?
    What Direction does this Route travel?
    What are all the Stops on this Route?
    What are all the Routes at this Stop?
    When is the next bus leaving at this Stop?
    If I miss the next available Time, when is the next scheduled Time after that?
    What connections/intersecting buses are available at the destination Stop?

    Could you, or anyone else who's interested, take a look at this DB to see if I'm on the right track to achieve what I want?

    Anyway, Thanx for the quick reply, it's greatly appreciated,
    Cheers,
    ziggy1971
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Arrow

    Hi

    Your DB is looking better than the first one you posted, personally i wouldn't have known where to start, but i may be able to help with the rest of the problems you have.

    When setting up a query, add in ALL neccessary tables, i have noticed you have a table WeekDays where the day is an ID number in other tables, but in here it lists the day. If you in any query related to the day wanted it to display day rather than number add the WeekDay table too, drop the field from WeekDay table rather than from any other table. This will ensure "Monday" is shown istead of 1. ;-)

    Then to set up the queries, using access is more or less straigh forward.

    Add in all relevent tables (as mentioned above), where there are relationships, access will show these, if not create a link yourself by dragging a related field from one table to the field on the other it is related to.

    To be able to show selective information (eg just stops/routes) drag in the field form the table it relates to and click the sigma symbol (a backwards E) then click group by in the new row. This will show just unique rows of information.

    Also, You can perform searches too, i posted on here with a sample. I'll post back with a link.


    Hope this helps

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Hi again;

    I have added 2x new queries to your DB to show you what i meant in my previous post.

    The Query (General_Data) you'll be able to copy and paste and then set criteria for some other queries you wish to run.

    Take a look at the search eaxmaple i mentioned (link coming next post, i forgot to copy and paste the link!) and see how i have structured the search to be able to answer some of the questions below.

    With the search you'll be able to do:

    A) what busses go past this stop;
    and B) My next bus(ses).

    To do a my next bus, use the search example (have one text field) for Bus stop Number. Use this is the query and add in the time field you'll drag in the criteria >Now() which should work!

    Again hope helps!

    Dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  6. #6
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    The link...

    sometimes simple is best.... and i'm just a simple fellow.

  7. #7
    Join Date
    May 2006
    Posts
    7
    Hello all,

    Thanx to danlindley for your advice and examples. However, I have no idea as to what you meant by the following:

    When setting up a query, add in ALL neccessary tables, i have noticed you have a table WeekDays where the day is an ID number in other tables, but in here it lists the day. If you in any query related to the day wanted it to display day rather than number add the WeekDay table too, drop the field from WeekDay table rather than from any other table. This will ensure "Monday" is shown istead of 1.
    Did you mean that I should show "Monday" instead of "1", for example, in the Datasheet view of the "TimeTables" table?

    I also have another question, "How" can I add a lot of data to the DB, I mean like a few thousand records, to the DB without having to type each one in separately. That question may be beyond the scope of this website, as it will probably involve some programming of some kind, but I just thought I'd mention it because there are probably programmers that visit this site that could help out with that.

    The example that I attached in the initial post, the spreadsheet, only has the data for 1 Bus/Route at 1 Stop location for 1 Weekday period (Mon-Fri). The data for that same Bus/Route at the same Stop location for Saturday, Sunday, & Holidays is different. So, as you can see, there is a lot of data to be entered.

    The data is, however, available on the internet, but the format that it is in, quite frankly, stinks.

    At this point you might be asking, "Why would I want to create a database on something that already exists?" Well the answer is quite simple really. When you're standing outside waiting for a bus and it's -30 degrees Celcius outside, you really don't have the resources to look it up on the internet.

    In the end, this database will be converted to an application for the Palm OS environment so that I'll always have access to the schedules and know the quickest way of getting to where I want.

    By the way, just how many records can an Access Database store?

    Thanx again,
    Cheers,
    ziggy1971

  8. #8
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    With reference to your first question it is more for ease of use (especially if other people are using this db) that you display "monday" and other text like st name instead of ID numbers. I mean an exmaple timetable could look like...

    [Bus stop] [Day] [Time] [Street] [Direction]
    3 2 15:21 8 4

    This could be hard to understand.

    Also you can "mass" add data into access. There are a few ways (i'd do this column at a time), personally, i would open the original format, Highlight the column you want, and use old fashioned copy and paste, column by column. You can also import data using the import function in access from any other office program. You'll just need to tell it where to go.

    My preferred method is copy and paste, it would be of benefit to you where you have mutlipe tables and you can keep an eye on what is being pasted etc. Some of the items will need find and replace to change them to the numeric field where it corresponds to the ID assigned to it in your database, other than that you should be fine.

    As for how many fields access holds.......no idea!!!!!

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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