Database Programming :: Lessons :: ER Diagrams
Entity-Relationship Model
A data model is a way of describing the structure of data. Two general types of data models include conceptual data models and logical data models. An Entity-Relationship Model is a type of conceptual data model and is independent of a DBMS so it can be applied to any system. Below is an example of an ER diagram following the Entity-Relationship Model.
An entity is an important object about which information is kept. An entity is a noun so it can be a person, place, thing, or event. In an ER diagram an entity is denoted with a rectangle with the singular name inside.
A relationship is an associate among one or more entities and is typically a verb. It is represented as a diamond with the name inside and cannot exist without entities.
The degree of a relationship is the number of entities associated with a relationship. The degree can be binary (two entities), ternary (three entities), or n-ary (more than three entities).
The cardinality or a relationship is the constraint on the mapping of the connected entities. It can be written as (min, max) or indicated through lines. The minimum is zero or one (most common) and the maximum is one or many (most common).
A one-to-one relationship associates a single entity with another single entity. A one-to-one relationship is indicated with a line with two vertical lines at the entity end or the text (1, 1). The vertical line furthest from the entity can be changed to a circle for an optional relationship.
A one-to-many relationship associates a single entity with at least one of another entity. The many part of a one-to-many relationship is indicated with three small branching lines at the end of the line at the entity end or the text (1, 1) on the one side and (1, m) on the many side.
A weak entity is an entity that cannot exist without another entity. It is indicated with a double-border around the relationship diamond and the entity that is dependent upon another entity.
An mandatory many-to-many can be indicated with (1, m) or with a many-to-many line with a vertical line before the three branching lines. An optional many-to-many can be indicated with (0, m) or a circle before the three branching lines.
An attribute is a characteristic of an entity or relationship. Sometimes an attribute is attached to an entity or relationship using a line and an oval, but many ER diagrams have a lot of entities so this can become crowded. In a crowded ER diagram attributes are not included. They are instead included in a second document called an attribute list.
In the diagram below, the Student ID attribute is underlined indicating it is a key, which will be defined later; name is a descriptive of the entity Students; majors is a multivalued descriptive that has more than one value; and address is a composite attribute that has its own attributes.
An attribute on a relationship can only be associated with a many-to-many relationship. In the relationship below you must know the Student and the Course to access the Grade attribute.
Recursive Relationships
A recursive relationship occurs when an entity is related to itself. A many-to-many recursive relationship is known as a network. In the example below, a person can have many relatives (or no relatives).
A one-to-many recursive relationship is known as a tree. In the example below, a department reports to a single department. A department may have zero, one, or more departments reporting to it.
Inheritance and Aggregation
Entities can have supertypes and subtypes just like classes in object-oriented programming languages like Java. A generalization subtype means an instance of the supertype cannot exist without being related to at least one instance of the subtype. The supertype is the union of the subtypes. In the example below, a person MUST be a Faculty, Student, or Staff.
A specialization subtype specializes the supertype so an instance of the supertype can exist without being related to any subtype. In the example below, a person CAN be a Faculty, Student, or Staff, but doesn't have to be any of them.
With overlapping subtype entities an instance of the supertype can be related to one or more of the subtypes. In the example below, a Person CAN be any combination of Faculty, Student, and Staff.
With disjoint subtype entities the supertype can only be an instance of ONE of the subtypes.
Combinations let you combine the inheritance subtypes:
- G/O: Generalization with overlapping subtypes
- G/D: Generalization with disjoint subtypes
- S/O: Specialization with overlapping subtypes
- S/D: Specialization with disjoint subtypes
In the example to the right, a Person MUST be ONE and ONLY ONE of either Faculty, Staff, or Student.
An aggregation is an is-part-of relationship, or ispo relationship. In the example below, a Car is made up of one hood, four wheels, and two doors.
