Results 1 to 5 of 5

Thread: 4NF help

  1. #1
    Join Date
    Dec 2009
    Posts
    3

    4NF help

    I am trying to visualize how a skills database will be laid out and I am getting confused.
    I want to store skills, certifications, and degrees earned by a member, where a member can have zero to N of each. I also want to store the dates the certifications were earned and dates attended at the school where the degree was attained.
    I have:

    mbr_table:
    -mbr_ID
    -first_name
    yadda yadda...

    skills_table:
    -skill_ID
    -skill_name
    -skill_description
    yadda yadda...

    experience_table:
    -exp_ID
    -exp_lvl
    -years_experience

    I feel I should create a table that stores the skills for each member like this:

    mbr_skills_table:
    -mbr_ID
    -skill_ID
    -exp_ID

    ... is it normalized if I put the certification dates in a table as above? This is where I am having trouble.

    mbr_certs_table:
    -mbr_ID
    -cert_ID
    -cert_date (date certification was earned)

    For education, do I need use a table to track the various schools and degrees obtained? I doubt that there will be duplicates for schools, but I guess if I am creating the database from scratch, it probably won't hurt.
    Is this the right way to look at it?

    degree_type_table:
    -degree_type_ID
    -degree_type (AA,BA, BS, etc...)

    degree_major_table:
    -degree_major_ID
    -major

    school_table:
    -school_ID
    -school_name
    -city
    yadda yadda...

    degree_table:
    -degree_ID
    -degree_type_ID
    -degree_major_ID

    mbr_degrees_table:
    -mbr_ID
    -degree_ID
    -school_ID
    -attended_start
    -attended_end

    I don't feel like the last two tables are right, but I am having trouble grasping why.

    Thanks in advance,
    -C

  2. #2
    Join Date
    Sep 2009
    Posts
    44
    Okay, it's pretty obvious this is a homework assignment. You can't normalize a schema by staring at it. This is a mathematical problem and you have to take it step by step. There's no "feeling" or "intuition," it's either right or wrong.

    Go back to your book, and read the section on normalization. (And you can't read by simply staring at it, either.)

    It will show you a set of steps you have to work through, and the order is actually pretty obvious: you get it in 1NF, then 2NF, 3NF, BCNF and *then* you can go up to higher forms like 4NF or 5NF. Each higher form requires that the schema be in the lower form.

    You're going to have to show the work to turn it in as an assignment anyway, or at least, I hope you will.

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    Sorry to confuse anyone. It is not a homework assignment. It is actually a project that I was pushed into by the officer in charge of the unit I serve with. I am not a database guy, but apparently I am the most qualified out of the shallow pool of people he had to choose from. They want me to design it using Access (which I am not very familiar with) and it is supposed to store members and their associated skills with the ability to filter the list to only members with desired skills for rapid deployment. It is a collateral duty - meaning that I am not being paid extra for this.
    I am using the internet for research. If there are any good sites for examples, etc., I am open to advice. I was hoping to lean on this forum for roadblocks I run into.
    Thanks in advance,
    -C
    Last edited by chrizzis; 12-30-09 at 00:18.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Then why is 4NF so important to you?
    Why is 3NF is not sufficient for your needs?

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    From what I read on the internet, I thought I had a case where I had multivalued dependencies.
    The members in the database can have several skills, certs, and degrees. I thought I had three many-to-many relationships, one between members and skills, one between members and certifications, and one between members and degrees. Under fourth normal form, I read that these relationships should not be represented in a single record such as:
    ___________________________
    ||Member|Skill|Cert|Degree||
    -------------------------------

    and should be instead represented in three records:

    ||Member|Skill||....||Member|Cert|Date||....||Memb er|Degree||


    I also read that these tables were called join tables, and in Access, the PKs for the primary tables must also be FKs in the join tables. Each join table will have two FKs. I am still hazy on the concept of a superset, but I assumed that the superset is the [Member|Skills] key in the case of the first table.

    I guess my initial question could be rephrased to "Is my database layout normalized?"

    I can definitely dumb down the database to get something functional and add to it after the first milestone, but I wanted to get it right and complete the first time.

    Thanks,
    -C
    Last edited by chrizzis; 12-30-09 at 22:04.

Posting Permissions

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