Results 1 to 15 of 15

Thread: Normalisation

  1. #1
    Join Date
    Apr 2011
    Posts
    17

    Normalisation

    I've been trying to self-learn normalisation for the last while and I just don't understand it. If you could help me normalise the below, I think I could understand it a little better.

    author, date of birth of author, title, isbn, number of pages, author's nationality, genre, publisher, publisher's city, publisher's phone number

    Zero normal form
    author
    date of birth of author
    title
    isbn
    number of pages
    author's nationality
    genre
    publisher
    publisher's city
    publisher's phone number

    First normal form
    author
    name
    date of birth
    nationality

    book
    title
    isbn
    number of pages
    genre

    publisher
    name
    city
    phone number

    Second normal form
    author
    author_id
    name
    date of birth
    nationality

    book
    book_id
    title
    isbn
    number of pages
    genre

    publisher
    publisher_id
    name
    city
    phone number

    Third normal form
    author
    author_id
    name
    date of birth
    nationality

    book
    book_id
    title
    isbn
    number of pages
    genre

    publisher
    publisher_id
    name
    city
    phone number

    all_id
    author_id
    book_id
    publisher_id

  2. #2
    Join Date
    Apr 2011
    Posts
    17
    Sorry for posting this three times. I don't know why it did that. Probably my browser.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gary223 View Post
    all_id
    author_id
    book_id
    publisher_id
    what's this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2011
    Posts
    17
    Quote Originally Posted by r937 View Post
    what's this?
    Its where I join up all the tables for third normal form.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    o rly?

    and which table is that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2011
    Posts
    17
    Quote Originally Posted by r937 View Post
    o rly?

    and which table is that?
    Its a table that joins the author, book and publisher tables.

    Is it incorrect?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gary223 View Post
    Its a table that joins the author, book and publisher tables.
    what is the table name?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2011
    Posts
    17
    Quote Originally Posted by r937 View Post
    what is the table name?
    Hmm...I don't think I named it. I guess it should be:

    all
    all_id
    author_id
    book_id
    publisher_id

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the "all_id" for?

    more importantly, what do the rows of that table look like for a book that has more than one author?

    hint: it is ~so~ not normalized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2011
    Posts
    17
    Quote Originally Posted by r937 View Post
    what is the "all_id" for?

    more importantly, what do the rows of that table look like for a book that has more than one author?

    hint: it is ~so~ not normalized
    The all_id is the primary key for all table. How would you do it?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gary223 View Post
    The all_id is the primary key for all table.
    you will lose marks for such an ill-advised idea

    Quote Originally Posted by gary223 View Post
    How would you do it?
    fix the books-to-authors relationship first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2011
    Posts
    21
    1NF: identify repeating data from the rest, give it a primary key and establish link between the (now) two tables (carry over primary key and make it a foreign key in the new table)
    2NF: remove partial dependencies (test all the fields in your non repeating data table against its brand new composite key, does this field depend on the primary key? yes? no, how about the foreign key? if partially dependent on one of them separate it into a new table carrying the key with it)
    3NF: remove non partial dependencies

    r937 is that right? (in simple(ish) terms)

    gary223 i remember normalising the same data set last year, it was one of the first few we did. good times.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's pretty much correct, jack

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2011
    Posts
    21
    Quote Originally Posted by r937 View Post
    yes, that's pretty much correct, jack

    sweet

  15. #15
    Join Date
    Jul 2008
    Posts
    7
    I believe you need to normalize the genres field to it's own table for the table to be truly in 3rd normal form.

    Otherwise you might end up having genres like sci-fi, scifi, SYFI etc.

Posting Permissions

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