2

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 EditionSnapshot of one to one relationship

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?

  • 1
    It's not clear from your question whether you are referring to one specific scenario or not. There are several possible, which is why you may find a variety on the web. An organisation could allow an employee to work in multiple departments (I do), or a department could have several managers, and an employee might manage multiple departments. – beldaz Jun 24 '16 at 6:40
  • No I want to understand why almost all books/writers consider one single instance of the case without exceptions – nuux Jun 24 '16 at 21:16
  • 2
    Possibly because all books base their examples around the same initial scenario. Chen's seminal 1976 paper "The Entity-Relationship Model-Toward a Unified View of Data" gives the Employee Department relationship as part of its running example. – beldaz Jun 24 '16 at 22:14
3

What is the cardinality of (this) entity relationship between Employee and Department?

Yes, this relationship ("managed-by") is an example of a one-to-one relationship. This doesn't forbid the two entities form having other relationships, (ie. "works-in") that are of different cardinality, one-to-many or many-to-many.

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?

Yes, the department . mgr_id will be updated and the historical info will be lost. If you want to preserve historical data, the tables should be designed with this in mind. Some DBMS provide built-in support for such temporal data. For some more details, see Temporal databases.

  • Does that mean such a history is not needed at all ? Or this depends on business rules that are dictated by the client organization ? – nuux Jun 23 '16 at 22:48
  • 1
    Yes, (depends on business rules), see my edit. – ypercubeᵀᴹ Jun 23 '16 at 22:52
  • Ok, this means we are considering only occurrences at a specified time. If we are to ignore updates that may or may not happen in the future like a student changing his/her name, let us leave such a history for the mercy of, as you've said, Temporal data but for updates we know will happen for sure why not we design the database to keep the history. Forexample, I saw as well a one to one relationship for a person marries_to a person. We know people marries to different people for various reasons more commonly, so why shall we ignore such a fact ? – nuux Jun 23 '16 at 23:09
  • 3
    Simplicity? A temporal database for all information is quite complicated. Almost everything is inserted and never updated or deleted, and every update/delete (in a common database) has to be replaced by an insert. – ypercubeᵀᴹ Jun 23 '16 at 23:13
  • I posted this question on a db forum and I think that said ""slowly changing dimensions" or SCDs is what I was looking for, though may be I didn't express it. – nuux Jun 24 '16 at 22:50

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.