Results 1 to 10 of 10

Thread: 3NF question

  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: 3NF question

    Hi! I have a fairly simple database, and have set it up to what I believe
    is the 3NF

    I have split this structure in 3 tables:
    Book
    Author
    Author_Book

    Book:
    (PK)ProductID
    Producttitle
    Published

    Author:
    (PK)AuthorID
    Firstname
    Lastname

    Author_Book:
    ProductID
    AuthorID
    (no PK's in this table)

    So, one book can have many authors, and one author can have many books. It must be a many-many relationship.
    Have I thought correctly, and made a proper 3NF of the table structure, or are there changes I must do?

    In the attached example i have give the books a just a simple number as productid, this will of course be ISBN numbers later.
    Attached Thumbnails Attached Thumbnails example.GIF  

  2. #2
    Join Date
    Oct 2009
    Posts
    19
    Looks good to me. Also you can put a PK on both columns (ProductID and AuthorID) in the table Author_Book
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  3. #3
    Join Date
    Oct 2009
    Posts
    4
    Hi, and thanks for your reply.
    One question arise though.
    If I want to extract all authors who have published a book in 2004, will this
    table structure be able to give me that result.
    And if you could provide me with an example I would be very grateful.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hasse76
    If I want to extract all authors who have published a book in 2004, will this
    table structure be able to give me that result.
    of course
    Code:
    SELECT DISTINCT
           Author.Firstname
         , Author.Lastname
      FROM Book
    INNER
      JOIN Author_Book
        ON Author_Book.ProductID = Book.ProductID
    INNER
      JOIN Author
        ON Author.ProductID = Author_Book.ProductID
     WHERE Book.Published >= '2004-01-01'
       AND Book.Published  < '2005-01-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2009
    Posts
    4
    R937
    With some modification it worked like a charm!! Thank you very much

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what modification???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your dates are not language independent - that's my bet.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    since when did surrogates rate a 3nf?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Oct 2009
    Posts
    4
    R937, I've just needed to modify where the information was residing in the querry.


    Quote: Brett Kaiser since when did surrogates rate a 3nf?
    What does this mean? Does the tablestructure not comply with 3nf?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hasse76
    R937, I've just needed to modify where the information was residing in the querry.
    huh? please, would you show me your query?

    p.s. yes, your tables are in 3NF
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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