2

Scenario: I am being asked to create an HR training tracker for employees of a business. in this database I am using two tables:

EMPLOYEE(EMPLID, LastName, FirstName, PrimaryEmail) with EMPLID as the PK, and 

SUPERVISOR(SupEMPLID) with SupEMPLID as the FK/PK.

enter image description here Every instance of SUPERVISOR relates to one and only one instance of EMPLOYEE (because every supervisor is an employee of the company), but not every instance of EMPLOYEE exists as an instance of SUPERVISOR (because not every employee is a supervisor).

I initially defined the relationship between EMPLOYEE and SUPERVISOR as one-to-zero-or-one [1:0,1], because again, each EMPLOYEE relates to zero or one instances of SUPERVISOR. My professor claims it is a one-to-one relationship.

Who is right here?

-------------------------------------------------[Update 1]-------------------------------------------------

  1. Removed SUPERVISOR entity.
  2. Created One to One-or-Many relationship between EMPLOYEE and POSITION.

enter image description here

[LINK TO CURRENT LUCIDCHART]

Improve this question
14
  • There is no "supervisor" entity - a supervisor is an Employee who has subordinates. This is usually modeled as a self-referencing relationship (with a single top employee as their own supervisor). In your diagram, if you're going to go with the "position" route, it would be a property of the Position, not the Employee and you'd have to take care to ensure no Employee has two positons.
    – user212533
    Commented Nov 11, 2022 at 17:39
  • according to the excel file i was given, every EMPLOYEE reports to one other person. No EMPLOYEE reports to no one. The two top positions are the President, and the Dir. Planning & Ops, and both report to one another. I felt a recursive relationship would be insufficient here. Yet, the relationship is still recursive in a way.
    – Nathan Willett
    Commented Nov 11, 2022 at 17:48
  • also yes, no employees in this db will have more than one position. Does this change anything? i still believe my diagram is correct based on these assumptions.
    – Nathan Willett
    Commented Nov 11, 2022 at 17:53
  • 1. Your diagram is lacking the keys to enforce that requirement. 2. Having two employees manage each other is going to cause problems - recursive relationships need to remain acyclic otherwise you'll have all sorts of problems trying to query things like "who is the top supervisor for Employee X" and getting a the top 2 officers until the maximum number of loops is reached.
    – user212533
    Commented Nov 11, 2022 at 17:58
  • I am purely going off the data i was provided and adhering to their logic. If every employee reports to one other employee, and no employee reports to zero employees, then that is what i must accomodate for, even if it means it is logically flawed/poorly designed. This db will not grant assignment privileges based on organizational heirarchy, and while that may be obviously flawed, it adheres to the logic of the excel file, and thus the class assignment. In other words, training modules can only be assigned by each employees direct supervisor. It will not accomodate skip level assigning.
    – Nathan Willett
    Commented Nov 11, 2022 at 18:14

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.