What is the cardinality of entity relationship between Employee and Department?
Recently, a friend of mine asked me this question about the cardinality between a Department and Employee entities, I replied many to many, then he, my friend, showed me drawings from the internet which says otherwise, one to one. I objected the idea and told him don’t trust everything on the internet. However, after I examined books on Database Concepts I found the one to one relationship that my friend showed to me. As an example I took the following snapshot from “The Morgan Kaufmann Series in Database Management System: Database Modeling and Design 4th Edition”
Fortunately, these guys mapped the entities depicting the foreign key relationship. Suppose after a year another employee is assigned to manage a department previously being managed by another employee. At this point are we going to update the mgr_id column and loose the history of the department and employee relationship?
A Department manages (1, n) Employee And an Employee is managed by (1,1) Department
But relational DB is particularly inappropriate for store an historic. So for your example, if you want store not only current department which manages an employee but also all previous departments the cardinalities are :
A Department manages (1, n) Employee And an Employee is/was managed by (1,n) Department