intro to DB: 1NF -> 2NF -> 3NF -> ?

Discussion in 'OT Technology' started by HardTech, Apr 12, 2007.

  1. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    okay. I had an interview where they asked a few database questions and I failed pretty hardcore. One of the questions was: "When would you want to use 2NF over 3NF?"

    I could not come up with an answer. I know there are some instances where you'd use 2NF over 3NF because I completed a project in college that addressed specifically this issue, but I cannot for the life of me remember it.

    I've also tried to find out what the difference between 2NF and 3NF is. Is 2NF where each entry that can be duplicated is put in another table (example, in a Person table, there might be two entries for the same person because of different phone numbers. If I create another table that has the Person and Phone Number as a composite key, would that be in 2NF?)? And 3NF would be where each data that does not relate to the primary or composite key be put in its own table?
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    1NF:
    * eliminate duplicative colums from source table
    * seperate tables for each group of related data w/primary-key

    Basically 1NF is the simplest form of a database, and any dataset should pass 1NF.

    2NF:
    * Remove subsets of data that apply to multiple rows of a table and place them in a seperate table.
    * Create relationships with foreign keys

    Basically 2NF is where you'll start for any production database. If we have a table for "Orders", then we will probably have a column for "Status". There are only a few values for column "Status" that we will use. Probably "Pending" and "Shipped" will be among them. It does not make sense to have a char[] column and write out "Pending" on each pending order. What if we decide to change the name? Take the char[] out and simply use an int() column that stores a foreign key that relates to the primary key of a second table "Status" where we can have our various options for status. This is a simplified example, but it demonstrates 2NF.

    3NF:
    * Remove columns that are not fully dependent upon the primary key.

    Basically if we have a line item with a unit-cost and quantity, we do not need a "total" column because we can simply multiply the cost * quantity; So in this instance we don't need an order total.

    However, as the db becomes larger and querys/second increase with more complex equations needed to generate the redundant column, it may become benefital from a speed standpoint to ingnore the 3NF rule and simply follow 2NF.

    That's my quick-n-dirty understanding of it, however, someone else may have a better example.
     
  3. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    so if we have a Person table with a Gender field like such:

    id: name: age: gender:
    1: Nick: 18: M
    2: Emily: 52: F
    3: John: 29: M
    4: Stacy: 38: F
    5: Adam: 24: M

    Since the M/F appears more than once in the database, is it 1NF and not 2NF?
     
  4. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    technically, yes

    however, you could change that field to a boolean and have it be "isMale" and using True/False you can make it 3NF.

    Keep in mind this is theory, so in the real-world, it's okay to bend the rules when it makes sense to. For example, when you just have M/F a char[1] field makes more sense than being true 2NF/3NF with a seperate table.
     
  5. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    yeah, I know this is all theory. I'm just trying to learn the basics of IT. I was really embarrassed when I was interviewing on Tuesday and my interviewer asked me basic database questions that I had no answer for.

    Thanks man
     
  6. Bruticus

    Bruticus half dead OT Supporter

    Joined:
    Apr 10, 2004
    Messages:
    4,608
    Likes Received:
    0
    Location:
    Melbourne
    It may be basic but you'd seriously be surprised how many students have nfi about them when they are taking the subject.

    A few examples clear things up really, really quickly if they are any use.

    Oh and the next one along would be BCNF, seeing as you put the ? at the end.

    Unless I'd just done some reading up on the definitions (which I would have done before a DB job interview) I wouldn't have been able to give a proper answer for that either.

    As jollyogre says, in the real world this doesn't really apply 100% of the time and you should always have books/internet around to be able to check things.
     
  7. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    yeah, Boyd-Codd Normal Form falls between 2NF and 3NF.

    I'd like to be able to code an entire website, front-end AND back-end. I do a lot of my learning on the job while doing it, but it'd be nice to have the basics covered in all areas of IT.

    And the job wasn't strictly a database position, it was more like an Operations Engineer... so a little bit of everything. I focused more on my soft skills than technical skills :wtc:
     
  8. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    BCNF would come after 3NF. In practice, I generally abstract my designs to 3NF without thinking about it. I find it expressive enough without being too abstract.
     
  9. turbo91

    turbo91 New Member

    Joined:
    Jun 14, 2002
    Messages:
    2,521
    Likes Received:
    0
    Location:
    San Diego, CA
    I can't really imagine why you'd want anything less than 3NF. It's just asking for problems later down the line.
     
  10. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    IME 2NF is usually fine for most things, it often makes writing reports / queries easier to understand when teaching your boss.
    :hsugh:
    I worked in a small organisation for too long.

    Properly understanding 1-3NF is important mostly as they are buzz words which employers / clients love to throw in when ever they can :o
     
  11. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    damn....I had my DB Class last summer and I didn't remember the diff between 2nd and 3rd at all. I know that for most people, if you don't use it you lose it...but I had hoped my brain would remember this shit a little longer.
     
  12. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    Whether you can spout off an academic answer or not, it's common sense, really. Less is more.
     
  13. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    good point
     
  14. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    You're right, Boyce-Codd NF comes between 3NF and 4NF

    When an interviewer asks you "When would you want to use 2NF over 3NF?", I don't think it's a good idea to answer with "I always use 3NF :squint:"
     

Share This Page