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.
Best Answer
You'll probably end up with something looking a little like this:
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:
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 entityAis a "container" with "attributes"BandC. It follows that: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 ).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 ).
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.
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:
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: