[Saya_26]

My problem is with the third normal form.. I normalized a table to 1NF and 2NF. But I found that only in one table, there was a transitive dependency..

The table is STAFFS(S# , SNAME, JOBTYPE, JOBGRADE)

Right now it's in the second normal form..

Here, I believe JOBGRADE depends on JOBTYPE as well as S#, so when changing the table into third normal form, it will be like

STAFFS(S#, SNAME, JOBTYPE)
JOBS(JOBTYPE, JOBGRADE)

Of course the problem here is that JOBTYPE is not unique. There can be many staffs with the same job type. S# is primary key to STAFFS and i believe the parent primary key is not passed down to the JOBS table in the Third Normal form. I'm really confused, anyone have any suggestions?

[e_i_pi]

Is this for an exam, or for personal use? If it was for personal use, I'd say that you might be thinking about the problem a little too hard If it's for an exam, I can understand why you want to get it just right.

The biggest reasons for creating normalised databases (in my opinion) are:

• Ease of use
  
• Ease of updating
  
• Reducing strain on the DB
  
• Extensibility
  
• Separation of data

Saya_26, on 09 December 2011 - 10:17 AM, said:

Here, I believe JOBGRADE depends on JOBTYPE as well as S#, so when changing the table into third normal form, it will be like

If you want to make the table 3NF, you're going to have to be sure about the part I highlighted above. Believing and knowing/understanding are different things.

To determine dependency correctly, and hence create a 3NF schema, ask yourself this:

Can a job type have more than one grade?
...or...
Are there two or more staff members with the same job type, but different job grades?

If you answer "no" to either of these questions, then job grade is dependant on job type, and you should create a JobType table. If the answer is "yes" to either of these questions, even in just one case over maybe thousands, then you should keep the schema design as is.

[Saya_26]

Thank you.. Your explanation was really helpful.