Summarized Extra Guidance on ER Diagramming

Summarized Extra Guidance on ER Diagramming

 

Hi all, one of the main focuses this week is studying and practicing writing Entity Relationship Diagrams (ERD).  So far, the class has created an ERD by primarily duplicating what they see in guided video tutorials. This involves little problem-solving. This week the class has its first ERD Homework Assignment that involves more problem solving than the Practice ERD Assignments.  To assist the class, I have included below in this supplement announcement a separate attached PDF file, that includes a set of helpful guidance information that can be used while planning, designing, and checking the ERD Homework Assignment and any future ERD Homework Assignments.

 

Below is a copy of the guidance information and there is a PDF copy provided as a courtesy as an attachment to this announcement.

 

  

 

 

Summarized Extra Guidance on ER Diagramming

 

I thought it would be useful to provide some preliminary class-wide guidance for creating ER Diagram.  Some students have gone in their directions following the guided tutorials, I encourage this.  However, in most cases, the base requirements in the guided videos must be maintained. Additionally, the framework described and illustrated for creating and mapping foreign keys and Primary Keys must be maintained.  Without following these guidelines, you may not achieve the implementation of a functional relational database later in the course.   

 

Some things to keep in mind.

First, the primary key should be an attribute or collection of attributes that can uniquely identify any one instance in the table that the Entity may represent.

 

Here are some guidelines to follow for mapping Foreign Keys correctly in the ER Diagram from its companion Primary Key

In this course, Foreign Keys should have an FK suffix identifying the attribute as a Foreign Key. Primary Keys must be underlined in the Chen Diagram.  In Crowfoot modeling, the Primary Key must have a PK in front of the attributes representing the Primary Key of the Entity.

 

In One – Many Relationships

When there is 1-M cardinality, the primary key on the one side becomes a foreign key on the many sides. In this course, Foreign Keys should have an FK suffix. Primary Keys must be underlined in the Chen Diagram. In Crowfoot modeling, the Primary Key must have a PK in front of the attributes representing the Primary Key of the Entity. In Crowfoot modeling, the Foreign Key must also have a PK in front of the attributes representing the Foreign Keys of the Entity.

 

 

 In Many – Many Relationships

When the cardinality is M-N, the primary keys from the entities connected to the relationship get mapped into the relationship symbol or relationship entity as foreign keys. In M-N Relationships the Relationship symbol or relationship entity will become a table.  In Chen's modeling, the diamond relationship symbol is used to represent a relationship.  In Crowfoot modeling, a relationship entity should be used.  In both cases, the relationship will become a table during translation to a relational schema. A primary key for the relationship table must be selected. The relationship table will have two or more foreign keys and a primary key identified.  In Chen modeling The attribute or attributes that represent the Primary Key must be underlined.   In Crowfoot modeling the Primary Key must have a PK in front of the attribute name in the Entity.  In all cases, the relationship will become a table in M-N Relationships. In this course, Foreign Keys should have an FK suffix connected to the attribute name. Primary Keys must be underlined in the Chen Diagram.  Primary Keys must have a PK Prefix that is in Crows Foot diagramming. In Crows Foot diagramming, the prefix does not have to be connected to the attribute name.

 

In One–One Relationships

If the cardinality is 1 to 1, there are choices.  In the design, the two entities can be combined into a single entity. However, this ultimately may create a table that contains attributes that will contain multiple instances of null information in most of the instances in the table.  The benefit is that the performance of queries may increase due to less cross-referencing between tables.

 

 If the decision is made not to combine the two entities, one of the entities must be selected as the Primary Key Entity in the relationship. The Primary Key in that entity will be mapped into the other entity as a Foreign Key.  The Primary key from the Primary Key Entity is mapped to the other entity as a Foreign Key.  The decision on which entity to select as the Primary Key Entity and which Entity will receive that key as a Foreign Key is typically made by selecting the entity that will be accessed the most as the Primary Key Entity in the relationship. This will increase the performance of the database based on less cross-referencing to instances in the Foreign Key table once the relational database is implemented into production. The reason behind this is that it can increase the performance of the database. In this course, Foreign Keys should have an FK suffix. Primary Keys must be underlined in the Chen Diagram

 Discovering Entities and Attributes during the design process

 

  Typically, if one cannot discover one or more attributes for an entity, it should not become an entity.   It should become an attribute of another entity.  Additionally, if you do not think an entity will have more than one instance or occurrence in that Entity, it should not become an Entity. Its existence can be implied.  This way a table will not exist just to accommodate a single instance row/record in the table.

 

Attribute - Entity – Relationship Connection Guidelines

In Chen Diagraming, all Entities must connect to a Diamond Relationship symbol. Entities cannot directly connect to another Entity Symbol in Chen Diagraming.  In Chen diagramming, Relationships must connect to an Entity.  In Chen's notation, Diamond Relationship symbols cannot directly connect to another Diamond Relationship symbol.  In Chen ER Diagramming, the Oval Attribute Symbol must connect to a Rectangular Entity symbol or a Diamond Relationship symbol. Attributes cannot be unconnected. Entities cannot be unconnected. Relationships cannot be unconnected. Once again, in Chen ER Diagramming and Crowfoot Diagramming, Foreign Keys must be identified.   Foreign Keys are designated and identified by placing an FK subfix extension on the attribute’s name that is representing the Foreign Key.

In Chen ER Diagramming, Primary Keys must be underlined.  Every Entity must have an underlined Primary Key. 

 

In Chen ER Diagramming, attributes are represented as ovals in Chen Diagram Notation

 

Primary Key – Foreign Key Guidelines

Primary Keys must be identified.  In Chen ER Diagramming Primary Keys are designated and identified by underlining the attribute name or attribute names composing the Primary Key.  In this course, in Chen Modeling and Crowfoot Modeling, Foreign Keys must be identified with an FK suffix on the attribute name, for example (name_FK).  In Crows Foot Diagramming, Primary Keys must be identified, and Foreign Keys must be identified. In Crowfoot Modeling Foreign Key Attributes must also have an FK in the column in front of the Foreign Key Attribute.

 

Crowfoot notation does not require that the Primary Key is underlined.  In Crowfoot notation, it is only required to label the row or rows of the attribute or attributes comprising the primary key with the PK label in the column proceeding the attribute or attributes.

In Crowfoot, ER Diagramming a Primary Key is designated and identified by placing a PK in the column proceeding to the column containing the attribute names for the Entity. A PK prefix must be adjacent but not connected to the attribute name or names representing the Primary Key of the Entity.   

In Crows Foot ER Diagramming, An FK prefix must be adjacent but not connected to the attribute name or names representing the Foreign Keys of the Entity.  An FK subfix is also required in this course to be attached to the names of the attributes representing Foreign Keys in the Entity.

  

Illustrating cardinality Guidelines

Finally, cardinality in the relations must be illustrated. Cardinality in a relationship is expressed as 1-1, 1-M, and M-N. In Chen ER Diagramming, the symbols to illustrate Cardinality are 1-1, 1-M, and M-N. 

 

In Crows Foot Modeling, the Cardinality must be illustrated using the appropriate symbols listed in the textbook and the guided video tutorial on Crows Foot Modeling. These symbols are difficult to illustrate in a regular Word Processor.

 

 

 

Do not mix notations between the two modeling notations of Crowfoot and the modeling notations of Chen ER Diagramming.  If using Chen notation, stick with the proper notation associated with Chen.  If using Crows Foot Notation, only use the correct Crowfoot Notation. 

 

Students should use these guidelines as a checklist before submitting Final Versions of ER Diagramming Assignments.

 

Components of ER Diagrams must not be spread across multiple pages erratically.  This makes the model very difficult to read and understand.  Additionally, it makes the model look amateurish and unprofessional.