Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Red face Normalization, I get it... No wait...

    Been given a couple of tasks for normalize to 3NF and I understand it but I'm struggling on this recent task I have at my university, I'm not asking for someone to do it for me just need some pointers.

    Entity: Staff_Project

    Attributes:
    Staff_No - Unique number (PK)
    Proj_ID - ONE of the projects the staff work on (PK)
    Proj_Name - Name of project
    Proj_Location - Location of project
    Staff_Hours - Staff hours per week
    Staff_Name - Staff Surname
    Staff_Address - Postcode

    I have to normalize this to the 3NF

    My first attempt led me to having tables that said 1 staff to 1 project but I'm presuming a need 1 staff to multiple projects, so this is my 2nd attempt

    Here is 1NF... This make sense to anyone?

    Staff_Project-1
    Staff_No (PK)
    Staff_Hours
    Staff_Name
    Staff_Address

    Project-1
    Staff_No (PK)
    Proj_ID (PK)
    Proj_Name
    Proj_Location

    Cheers!
    Last edited by MrMJH; 10-21-10 at 09:33.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    ok
    the staff table looks OK, it has details relevant only to the members of staff
    the project table looks iffy to me.... that suggests you can only ever have one member of staff allcoated to a project, or if you do allow multiple members of staff you have to repeat the project name and location which is a no no.

    presumably there's
    an entity for a project, containing all the stuff about the project (its ID, name, location etc)
    an entity for members of staff , containing all the stuff about the member of staff (StaffNo, Name, Address and so on)
    The main issue is whether you can assign more than one member of staff to a project, or for that matter a member of staff to more than one project.
    if you need the ability to assign a member of staff to more than one project then another entity (an intersection table) that associates a member of staff with a project and stores anything else relevant to that intersection of data, it could be say person X works on this project from blah to blah di blah
    how you treat staff hours depends on the business logic, is the amount of staff hours common accross staff members (ie is it allocated according to say grade, or is it unique to each member of staff. is it specific to a project, eg Staff No 870987098 is allocated for 12.25 hours on project xyz, 10 hours of project ABC
    ... how you define that is up to you and your assignment, you can make whatever choices you see fit, providing the backup documentation tells the story of why you made that decision
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mark - I didn't read everything you wrote but you are ahead of the OP and getting in to 2NF.

    OP - Yes - that is correct for 1NF. Perhaps you should satisfy yourself by listing the conditions of 1NF and checking your design thus far against them.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    quite possibly
    I've never ever done the exercise to go from 1NF to nNf, I'm not sure it was invented, or if it was it wasn't in common use when I was at Uni.
    I don't really 'see' the 1NF to 2NF, I usually "just percieve" 3NF
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Agreed - I think most experienced professionals get most of the way to 3NF in the first cut but the university courses insist on doing it step by step. I suppose it is the equivalent of "showing your working" in maths.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2010
    Posts
    2
    The specification is VERY minimal, but the only thing I can answer is that it one member of staff can work on multiple projects

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case we have to infer dependencies. I assume there is not even example data?

    Next step is 2NF. Why not explain what you understand of 2NF and then explain whether or not you think what you have so far is in 2NF. If not, why not, and what needs to be done to make it 2NF.
    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
  •