Lets say I have have employees and they all have basic stuff like name and phone
ID: 3885567 • Letter: L
Question
Lets say I have have employees and they all have basic stuff like name and phone number but they also have one or more roles however for one specific role, say salesperson (and no others) a little extra information needs to be stored. I also don't know and can never know all possible roles and new roles could be made at any time.
What is the best way to represent this on an ER diagram? If it wasn't for the salesperson I would just use a multi-valued attribute.
The best ways I can think of are: Overlapping subclasses with 'salesperson' and 'other' as the possibilities. However I don't really think this is a good solution because I have never seen 'other' on an ER diagram before.
I could have a single non total participation subclass for the salesperson that can store the extra information but then otherwise have a multivalued attribute on the employee entity that represents all roles? Still doesn't seem good to me because there is redundancy and information not stored together.
Explanation / Answer
Method 1
In case of the composite attributes further divided in a tree like structure hence connect every node to its attribute. The composite attributes represented by ellipses which are connected with an ellipse.
Example: Name -> first name, middle name, last name
Address -> number, street, city
Method 2
Relationship, interaction between entities
Use Indicator that an attribute of one entity refers to another entity, these are represents references as relationships not attributes.
Relationship type R among n entity types E1, E2, ..., En, defines a set of associations among entities from these entity types
Example: Work on -> project, hours
Method 3
Subclasses: that one or more attributes are characteristics only for some individuals not for others and which indicate that the need of a subclass of the basic class type.
In an incomplete specialization, (partial specialization) only some individuals of the parent class are specialized and the other individuals of the parent class have only the common attributes.
In an overlapping specialization, an individual of the parent class may be a member of more than one of the specialized subclasses.
Example: The entities are members of the EMPLOYEE entity type may be grouped further into SECRETARY, ENGINEER, MANAGER, TECHNICIAN, SLARIED_EMPLOYEE, HOURLY_EMPLOYEE, etc.
3rd is the more suitable method for the same.
=========================================================================
The subclass association with specialization are described along two dimensions such as Incomplete versus Complete, and Disjoint versus Overlapping.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.