Common Errors when Developing an Enhanced Entity Relationship Diagram (EERD)

 

Common Errors when Developing an Enhanced Entity Relationship Diagram (EERD)

 

One of the most common and catastrophic errors that occur is forgetting to add Subtype Discriminator Attributes.  This is an attribute that contains a data value that directs a Supertype Entity where to locate the rest of the information associated with a specific instance in its table.    One version of this is that the Disjointed Model is missing a single Subtype Discriminator Attribute in the Super Type Entity. In an EERD, the Super Type Entity is the Entity at the top level in the Inheritance Hierarchy of the diagram.   In a Disjointed Model, the  Subtype Discriminator Attribute is used to indicate which of the Sub Type Entities an instance/row of the Supertype Entity is connected to.  In a Disjointed Model, the Super Type Entity must only have one Subtype Discriminator Attribute.  In a Disjointed Model, an instance/row in the Super Type Entity can only connect to only one of the Subtype Entities in the hierarchy connected to it.    

 

Another issue that can occur is that the Model or Relationship in the Model does not indicate the type of Inheritance that the model represents.  The two types that are explored in the course are Disjointed and Overlapping Inheritance. 

 

 In an EERD, the letter D inside of a small circle is used to indicate that the Inheritance is Disjointed.  In a Disjointed Inheritance Relationship, an instance/row in the Super Type Entity can only connect to one of the Subtype Entities in the Inheritance Tree directly under it.  A common mistake is that the developer of the Diagram may neglect to use the letter D inside of the small circle to visually indicate the relationship between the Supertype Entity and Subtype Entities below it has a Disjointed Relationship.

 

There is a similar problem that may occur when one is illustrating an Overlapping Model or an Overlapping Relationship within the EERD.  The problem that sometimes can happen and should be checked for is that Subtype Discriminator Attributes are placed in the Super Type Entity involved in the Overlapping Relationship.

 

In an Overlapping Relationship, there must be two or more Subtype Discriminator Attributes in the Supertype Entity. In an Overlapping Model, there must be a different Subtype Discriminator Attribute for each of the possible Sub Type Entities.  If there are three Subtype Entities connected and under the Supertype Entity, there must be three Subtype Discriminator Attributes added to the Supertype Entity. When indicating that the relationship is Overlapping, it implies that an instance /row in the Supertype Entity may connect to a row in all three of the Subtype Entities.  The Subtype Discriminator Attributes are Boolean data types that can hold either TRUE or FALSE values.  TRUE would indicate that the instance/row in Supertype Entity is associated with Subtype Entity that Subtype Discriminator Attribute represents.

 

In an EERD, the letter O inside of a small circle is used to indicate that the Inheritance is Overlapping.  In an Overlapping Inheritance Relationship, an instance/row in the Super Type Entity can connect to one or all of the Subtype Entities in the Inheritance Tree directly under it.  A common mistake is that the developer of the Diagram may neglect to use the letter O inside of the small circle to visually indicate the relationship between the Supertype Entity and Subtype Entities below it has an Overlapping Relationship.

 

Another problem that students will lose points on is forgetting to Identify Primary Keys and Foreign Keys in the Entities or Relationships in the EERD. 

 

 

Some Typical Errors when illustrating the design to Track the History of a segment of data or information in an EERD.

 

One of the pieces of data that can commonly be tracked is the Salary History or Payrate History of an Employee in an organization.  The framework for this is idescribed and illustrated in Chapter.  Often a student will fumble this one on their first try. This is handled similarly to how a repeating group or multivalued attribute is handled when placing a Relational Schema in the First Normal Form. However, this is handled at the ER Diagram level. At the ER Diagram level, a new Entity is created for the Salary History entries. A 1_M relationship is illustrated between what is usually the Employee Entity and the newly created Salary History Entity.  A basic framework is that a salary attribute is created and a start date attribute is created. The Primary Key of the Entity on the 1 side is placed into the Salary History Entity as a Foreign Key.

 

Another piece of data that can commonly be tracked is the Address History. This is also handled similarly to how a repeating group or multivalued attribute is handled when placing a Relational Schema in First Normal Form. However, this is handled at the ER Diagram level. At the ER Diagram level, a new Entity is created for the Address History entries. An address attribute or collection of address attributes is created.   This typically includes a start date attribute for the address indicating the date the address was changed or added. Once again, the Primary Key of the Entity on the 1 side of the 1_M relationship is placed into the Salary History Entity as a Foreign Key. The Address History Entity becomes the entity on the many side of the 1_M relationship. This establishes a framework where a single person can have many addresses in the Address History Entity.

Finally, for the database, multiple email addresses for a person can also be set up using the same framework. This is also handled similarly to how a repeating group or multivalued attribute is handled when placing a Relational Schema in First Normal Form. However, this is handled at the ER Diagram level. At the ER Diagram level, a new Entity is created for the email entries. An email attribute is created. The Primary Key of the Entity on the 1 side is placed into the Email Entity as a Foreign Key.