1

I have to achieve 3NF with following specifications: Database where I will determine priority skills for specific jobs and track the employees ability to complete them. Train employees.

Example of job position and employee: JP Constructor / needed skill AUTOCAD - excellent Name Surname / JP constructor / skill AUTOCAD - not trained

Catalog of skills(group, code, title), groups of skills, for example: -general skills (foreign languages, administrative skills,...) -specialized software (AutoCAD, CADSS,...)

ID in catalog of skills is generated according to model:

Specialized skills..   X(group of skill)
RBR                    1001
ID                     X.1001

For job position skill and employee, there are 3 levels: -familiar -intermmediate -excellent

Track skills for job position in department.

Output documents: -overview of employees with specific skill -comparing of skills for job position in department and skills of employees on that job position, to get the plan of acquiring knowledge for specific skills (list the employees who have skills with LEVEL not sufficient enough that job position requires)


I wrote detailed info above so you get the concept idea.

This is done in Oracle. I have .csv file with rows and data which I'll import but they're not in 3NF. Now my attempt to apply this to tables/ER model:

I already have DEPARTMENT table pre-generated, so thats done. I thought about creating 3 tables to achieve model:

Primary key will be marked inside [], and foreign with ()

EMPLOYEES([ID], Name, Last_Name)
JOB_POSITION([ID], (EMP_ID), JP_Title, Group, [Department])
SKILLS([ID], (EMP_ID), SK_Title, Level)

Now, this is what I got as suggestion on my first model. And another, that I put the rest as materialized views. But somehow I don't know if this is the right way to do it.

In short, I'll have to track employees on certain job positions with certain skills(if they are sufficient or not).

I get kind of blurry on realization.

I'd really appreciate some direction here, thank you.

  • Consider adding a SKILL_LEVEL table as well, to get you closer to 3NF. I'll do up an answer for you later ( busy ATM ) as well if my hint doesn't help and nobody else gets you anywhere. – Avarkx Jan 22 '14 at 17:49
  • OK, thank you, and I will do just that with SKILL_LEVEL table, it makes clearer sense. Looking forward for further discussion on best solutions. A side note, I don't know if I should have separate table of EMPLOYEES or not. – lidagon Jan 22 '14 at 18:21
  • Amazing explanation, thanks a lot. No worries, I'm able to read it all and that's what I needed in order to make the model clear and not expose myself to errors. So, problem solved here. – user33289 Jan 23 '14 at 19:10
4

You'll probably end up with something looking a little like this: Schema This may be a little more involved than you first expected, but I'll take you through the whys and hows of it all ( doing this is generally much easier than explaining it, by the way ), starting with identifying objects. Using "Noun-Verb" analysis, which is a fairly common OOAD technique, we start picking apart your requirements:

Right away, it becomes evident that at least Employee, Position / Job and Skill are needed. Further along, a need for Department and Skill Group emerges, then finally Skill Level. From here, things like Employee Surname are noted as well.

Some liberties are then taken here ( which is not abnormal in any way, considering the quality of the list of requirements you'd normally be extracting from a client ), but the noun list to be used for the general structure, in no particular order, is as follows:

Department ( D )

Department Code ( DC )

Department Name ( DN )

Position ( P )

Position Code ( PC )

Position Title ( PT )

Skill Group ( G )

Skill Group Code ( GC )

Skill Group Name ( GN )

Skill ( S )

Skill Code ( SC )

Skill Name ( SN )

Employee ( E )

Employee Code ( EC )

Employee First Name ( EF )

Employee Surname ( EL )

Level ( L )

Level Code ( LC )

Level Name ( LN )

Using these nouns, on a relatively simple level, relationships between them can be established using "is-a / has-a," that is, "Noun A is a Noun B" or "Noun A has a Noun B." This is particularly useful for classifying our nouns into two categories, being an "attribute", an atomic entity, later called a column, or a "container," which is a collection, later called a table. During the first round of this "is-a / has-a," the goal is generally to simplify the list, effectively removing the attributes from the picture, grouping them to their containers.

The naming convention used above goes a fairly long way to making this process simple. For instance, an Employee ( E ) has an Employee First Name ( EF ) and an Employee Last Name ( EL ), forming one-to-one relationships between Employee ( E ) and the Employee First Name ( EF ) and Employee Last Name ( EL ) entities. In this way, Employee ( E ) can be classified as a container, whereas Employee First Name ( EF ) and Employee Last Name ( EL ) become attributes. Focusing on this reduction of nouns via one-to-one relationships, we can reduce the list above to the following, where the notation ( A: B, C ) means that entity A is a "container" with "attributes" B and C. It follows that:

Department ( D: DC, DN )

Position ( P: PC, PT )

Skill Group ( G: GC, GN )

Skill ( S: SC, SN )

Employee ( E: EC, EF, EL )

Level ( L: LC, LN )

With this reduction, the one-to-many relationships can now be considered. A Department ( D ) may have any ( a pluralized has a ) number of Positions ( P ) available, implicitly indicating a one-to-many relationship. Extending the above notation, in addition to the attribute list, we can now add "compound" attributes. More simply, a compound attribute is basically a container treated as an attribute. This can be written as ( P: PC, PT, ( D: DC, DN ) ), which begins to highlight the places where foreign keys will materialize. Similarly, Skill Group ( G ) may refer to a number of differing Skills ( S ).

( P: PC, PT, ( D: DC, DN ) )

( S: SC, SN, ( G: GC, GN ) )

The many-to-many relationships can then be addressed. Position ( P ) may require any number of Skills ( S ), each with it's own Skill Level ( L ). Conversely, a specific Skill ( S ) can be required for any number of Positions ( P ), at any particular Skill Level ( L ). These associations manifest as a ( is a ) Position Skill ( PS ), effectively a "surrogate" container, having a compound attribute of Skill Level ( L ). To use the phrasing above, each of these newly established Position Skills ( PS ) has a required Skill Level ( L ). To maintain this many-to-one relationship between Position Skill ( PS ) and Level ( L ), a unique key is to be placed on the foreign keys Position ( P ) and Skill ( S ).

( PS: ( { U1 }( P: PC, PT, ( D: DC, DN ) ), { U1 }( S: SC, SN, ( G: GC, GN ) ), ( L: LC, LN ) )

A quick note: The { Un } is used to indicate both attributes ( themselves containers ) are part of a single unique key. If the attributes were independently unique, we could use { U1 } and { U2 }.

Next, an Employee ( E ) can occupy any number of Positions ( P ), indicating that there is a Employee Position noun to be added to our list. This particular relationship manifests over some period of time, thus having ( has a ) Start Date ( T1 ) and End Date ( T2 ). However, there is no way to assert that a normally part-time Employee ( E ) could not be serving under two separate Positions ( P ) in a full-time manner simultaneously in the interim before a new hire, nor that a previous Employee ( E ) could be re-hired or otherwise return to a Position ( P ) previously held by that Employee ( E ). This dilemma results in a true many-to-many relationship, with Start Date ( T1 ) and End Date ( T2 ) as attributes. As Start Date ( T1 ) and End Date ( T2 ) are not functionally dependent on anything other than the relationship between Employee ( E ) and Position ( P ), this structure is not in violation of 3NF.

( EP: ( E: EC, EF, EL ), ( P: PC, PT, ( D: DC, DN ) ), T1, T2 )

The final piece comes in the form of the relationship between an Employee's ( E ) specific term of employment, Employee Position ( EP ), the relevant Position Skills ( PS ) and the quality at which those specific Position Skills ( PS ) are realized, or simply, the achieved Level ( L ). Much like the relationship established between Position ( P ) and Skill ( S ), there is a Employee Position Position Skill ( EPPS ) which has a compound attribute of Level ( L ) in a many-to-one relationship. This again requires a unique key, leaving our noun list ( now expanded ) in the following state:

( EPPS:

    { U2 }( EP:

        ( E: EC, EF, EL ),

        ( P: PC, PT, ( D:

            DC, DN ) ),

        T1, T2 ),

    { U2 }( PS:

        ( { U1 }( P:

            PC, PT, ( D:

                DC, DN ) ),

        { U1 }( S:

            SC, SN, ( G:

                GC, GN ) ),

        ( L:

            LC, LN ) ),

    ( L:

        LC, LN ) )

That, of course, is a bit on the crazy side to read, so we extract the definitions we've reduced the problem to in an actionable way. Here, I've added additional unique keys simply for the purpose of completeness. Note that there are no soon-to-be tables which contain more than one independently unique key, so { U1 } is used throughout, per table:

( D: { U1 }DC, DN )

( P: { U1 }D, { U1 }PC, PN )

( G: { U1 }GC, GN )

( S: { U1 }G, { U1 }SC, SN )

( L: { U1 }LC, LN )

( PS: { U1 }P, { U1 }S, L )

( E: { U1 }EC, EF, EL )

( EP: { U1 }E, { U1 }P, T1, T2 }

( EPPS: { U1 }EP, { U1 }PS, L }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.