1

I have an ER-Diagram containing the entity employees. When employees have high skills they may supervise lower skilled employees. How can I model that in an ER-Diagram?

My initial approach of was to have a disjunction of high skilled and low-skilled employees. But that appears to be rather inconvenient.

enter image description here

  • When employees have high skills they may supervise lower skilled employees. It is a process (calculated/dynamic relation), not a relation or attribute. It has no static mirror in a diagram. – Akina Oct 17 at 10:43
  • @Akina so the way I did it (added picture to question) does not work? – BlackPearl Oct 17 at 10:44
  • It MAY work... but you'll need to use special techniques (triggers) to provide real-time data consistency - each skill may alter causing to recalculate admin rights and add/remove records into relational table. Whereas the actual data you want to store may be easily obtained by one simple query. – Akina Oct 17 at 10:46
  • I find the diagram just fine, expressing the scenario you describe correctly. – ypercubeᵀᴹ Oct 17 at 10:49
1

Here's one idea:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
);

CREATE TABLE Supervising
( supervisor_empno ... not null references Employees (empno)
, empno ... not null references Employees (empno)
, primary key (supervisor_empno, empno)
);

If you want to guarantee the skilllevel in supervising:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
, unique (skilllevel, empno)
, check (skillevel in ('High', 'Low'))
);

CREATE TABLE Supervising
( supervisor_empno ... not null 
, supervisor_skillevel ... not null
,    foreign key (supervisor_skillevel, supervisor_empno)
     references Employees (skillevel, empno)    
, empno ... not null references Employees (empno)
, empno_skillevel ... not null
,    foreign key (skillevel, empno)
     references Employees (skillevel, empno)    
, check (supervisor_skillevel = 'High')
, check (skillevel = 'Low')
, primary key (supervisor_empno, empno)
);

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.