Entity Relationship Diagram (ER Diagram) SQL

Written by

Carissa O'Connell

November 20, 2022; Updated: Jan 2023

Entity Relationship Diagram (ER Diagram)

An ER diagram is a visual representation of the relationships between entities stored in a database. The diagram acts as a framework of specialized symbols that define the relationship between the database entities. An ER diagram is based on three concepts: entities, attributes, and relationships.

Benefits of an ER Diagram

• Helps to define terms related to entity relationship modeling
• Lets you know which fields need to be embedded for a particular entity
• Provides a preview of how the tables should connect and which fields are on each table
• ER diagrams can be used by database designers as a blueprint for implementing data in specific software applications

Main components and symbols in an ER diagram:

• Rectangles: represents entity types
• Ellipses: represents attributes
• Diamonds: represents relationship types
• Lines: links attributes to entity types and entity types with other relationship types
• Double Ellipses: represents multivalued attributes
• Primary Key: attributes are underlined

Steps to creating an entity diagram:

1. Identify the entities
• An entity can be a person, place, object, event, or concept
• An entity is a table that stores data. A strong entity is a table that has a primary key whereas a weak entity does not
• A weak entity is one that cannot be uniquely identified by its attributes and must use the attributes of another entity, known as its owner, to be uniquely identified
• A strong entity can also be identiified by its own attributes
2. Identify the relationships
• Relationships are identified by verbs or verb phrases
3. Identify cardinality
• One-to-one relationships
• One-to-many relationships
• Many-to-one relationships
• Many-to-many relationships
4. Identify the attributes
• Key attribute - identifies an entity from an entity set
• Composite attribute - is composed of several other attributes
• Multivalued attribute - possess more than one value
• Derived attribute - is derived from other attributes of the entity
5. Create the diagram