Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2007
    Posts
    9

    Red face Normalisation Help

    Hi all,

    Hope ure all doing better than myslef.

    I'm Tim, I'm currently studying web development & am struggling a little with my end of year project particularly the normalisation part of my database design,

    Is there anyone out there that would be willing to give me a hand.

    If so can u either PM me or send me an email at

    Tim.Webster2@Ntlworld.com

    Any help u can offer would be much appreciated.

    Cheers

    Tim

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're not going to get a lot of responses asking for private support on a public forum

    post your questions here and maybe someone will look at them

    note: we won't do your homework for you, but we might answer your questions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    9

    Fair point!

    I see what u mean,

    right, the project involves createing a dyanamic website for a ficticious cruise company. The aim of the site is to allow a member of the cruise line admin staff to update the website without the use of a web developer.

    Thus far I have three forms which I have normalised,

    Crusie date -Contains info about the holidays, cost etc

    Staff data -Contains staff username & password

    Itinerary data- contains more detailed info about the hols & the schedule

    Its really the itinerary data that I'm struggling with,


    UNF 1NF 2NF 3NF

    itineraryID itineraryID itineraryID itineraryID
    heading heading heading heading
    description description description description
    day image image image
    destination
    arrive time itineraryID* itineraryID* itineraryID*
    depart time day day day
    image destination destination destination
    arrive arrive arrive
    depart depart depart


    I'm just not sure if I've normalised this correctly as when I started to gather date it didn't seem right. Above I am left with an Itinerary table n then a Schedule table.

    When I gather data for my schedule table this is what i ended up with

    itineraryID* day destination arrive depart

    1 Tuesday Barbados 8.00pm
    1 Wednesday at sea at sea at sea
    1 Thursday Trinidad 8.00am 6.00pm
    1 Friday Isla Margarita 8.00am 6.00pm
    1 Saturday Grenada 8.00am 6.00pm
    1 Sunday St Lucia 8.00am 6.00pm
    1 Monday St Vincent 8.00am 6.00pm
    1 Tuesday Barbados 7.00am
    2 Tuesday Barbados 8.00pm
    2 Wednesday at sea 8 6
    2 Thursday Tortola 8 7
    2 Friday St Maarten 8 8
    2 Saturday St Kitts 8 8
    2 Sunday Antigua 9 6
    2 Monday Dominica 8 8
    2 Tuesday Barbados 7


    Have I done something wrong during normalisation?????? Or does this look ok??

    Cheers Muchly

    Tim

  4. #4
    Join Date
    Apr 2007
    Posts
    9
    an to think i spend all that time spacing it all out nicely!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Sylvester
    an to think i spend all that time spacing it all out nicely!
    Try popping it in code quotes - they maintain much of your formatting
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will need to go back to basic principles to understand the concept of a primary key

    Code:
    UNF             1NF             2NF            3NF
    
    itineraryID     itineraryID     itineraryID    itineraryID
    heading         heading         heading        heading
    description     description     description    description
    day             image           image          image
    destination   
    arrive time     itineraryID*    itineraryID*   itineraryID*
    depart time     day             day            day
    image           destination     destination    destination
                    arrive          arrive         arrive
                    depart          depart         depart
    Code:
    itineraryID*   day          destination        arrive     depart
    
        1          Tuesday      Barbados                       8.00pm
        1          Wednesday    at sea                at sea    at sea
        1          Thursday     Trinidad           8.00am    6.00pm
        1          Friday       Isla Margarita    8.00am     6.00pm
        1          Saturday     Grenada                  8.00am    6.00pm
        1          Sunday       St Lucia                   8.00am    6.00pm
        1          Monday       St Vincent          8.00am    6.00pm
        1          Tuesday      Barbados                  7.00am 
        2          Tuesday      Barbados                               8.00pm
        2          Wednesday    at sea           8            6
        2          Thursday     Tortola             8            7
        2          Friday       St Maarten        8            8
        2          Saturday     St Kitts             8              8
        2          Sunday       Antigua                    9            6 
        2          Monday       Dominica                   8            8
        2          Tuesday      Barbados                 7
    a primary key identifies a unique row

    looking at your data, i see several rows for itinerary #1

    therefore, it cannot be the primary key



    p.s. formatting is done with [code] stuff [/code]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2007
    Posts
    9
    Thanks,

    In the above example, The itineraryID, day & destination should make up a composite primary key. If that makes any difference?

    Cheers Again

  8. #8
    Join Date
    Apr 2007
    Posts
    9
    With the itineraryId in the schedule table being a foreign key in the itinerary table.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sylvester
    The itineraryID, day & destination should make up a composite primary key. If that makes any difference?
    yes, it does make a difference, but that's not what you had
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2007
    Posts
    9
    lol, I know its cos the Underlines don't copy over when u cut & Paste!

    So with that in mind would u say this is normalised to 3NF correctly???

    & If so I'll be able to link this to a website using php. By this i mean that when i create a recordset in dreamwever will I have a seperate record for each arrival time etc??

    Thanks for all ure help r937

    P.s. where did u get the name r937??

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it looks okay to me

    assumption: an itinerary will never include two stops at the same destination on the same day

    937 is my favourite number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2007
    Posts
    9
    Well why didn't u just say so at the start, lol, )

    Super mate, thanks alot, as I said above i just felt there was summit wrong when I was entering the data & I ended up with 8 itineraryId row 1's

    Yep once they leave on place there headed to another!

    lol most folks have a single digit fav number!

    I'm still a little confused about how I'll pull back the individual times when I come to populate my "update Schedule" form fromt the database but as long as theres no glaring errors I think I'll go get the rest of my data n start to build the site!

    Cheers again man!

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    http://r937.com/what-does-r937-mean

    To answer your PM - click reply -> find the Manage Attachments button. Click it and... manage your attachments
    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
  •