IS201: Database Theory - Entity Relationship modelling

ERM (entity relationship model)

• Forms basis of ERD (Entity relationship diagram)

• Represents the conceptual database viewed by end user

ERD Main components

• Entities

• Attributes

• Relationships

Required attribute

An attribute that must have a value and cannot be left empty

Optional attribute

Does not require a value and can be left empty

Single-valued attribute

An attribute that has only a single value

Multivalued attribute

Attributes that have many values

Derived attribute

An attribute whose value is calculated from other attributes

Connectivity

describes the relationship classification (1:1, 1:M, M:N)

Cardinality

• Expresses the min and max number of entity occurrences associated with one occurrence of the related entity

  • Format: x (min), y (max)

Existence dependence (weak entity)

• An entity that can exist in the database only when associated with another related entity

• entity has a mandatory foreign key

Weak (non-identifying) relationship

• Represented by dashed line

• Exists if primary key of related entity does not contain primary key component of parent entity

• occurs between two strong entities

Strong (identifying) relationship

• Represented by solid line

• Primary key of related entity contains a primary key component of the parent entity

• occurs between one strong and one weak entity

Conditions for an entity to be classified as weak

• Entity is existence-dependent

• Has a primary key partially or totally derived from the parent entity

Types of relationship participation

Optional and mandatory participation

Optional participation

One entity occurrence does not require a corresponding entity occurrence in a relationship

Mandatory participation

One entity occurrence requires a corresponding entity occurrence

Relationship degrees

Indicates the number of entries/participants associated with a relationship

Types of relationship degrees

• Unary relationship

• Binary relationship

• Ternary relationship

• Recursive relationships (where it maintains multiple versions of itself basically)

Associative (composite/bridge) entity

• Used to represent an M:N relationship by breaking it into 2 1:M relationships

• Composed of the primary key attributes of each parent entity