Entity relationship modelling

They provide an understanding or model of that data that designers programmers and end users can share between each other.

They provide an accurate and useful translation of requirements for that final draft.

An example of an approach to designing the database:

• Begin by identifying the main types of data and the relationships between entities
• Put in more details such as the attributes that define entities and relationships and any constraints on the entities, relationships and attributes

ER models are visualised by using a diagrammatic notation known as UML, short for Unified Modelling Language

Entity = Set of objects that share similar properties and are identified by a user or organisation as having an independent existence

Entity occurence = Uniquely identifiable object within a set

The independent existence of an entity can either be:

• Physical
• Conceptual

Entities are represented by a recantangle and the first letter of each word is upper case. There are also no spaces between two words in an entity.

Relationship = Set of meaningful associations between participating entities.

Example: Member makes a wish

Relationship occurence = A uniquely identifiable association

Example: Joe Bloggs makes Forrest Gump his Wish

Relationships are shown as a line connecting associated entities as well as a label stating the relationship name.

The first word is upper case, very much like entities

All associations are directed, as they only make sense in one direction

There are 4 types of relationship degree:

• Binary - two entities involved
• Ternary - three entities involved
• Quaternary - four entities involved
• N-ary - >4 entities involved.

Binary degrees are the most common, with ternary and quaternary degrees being less common.

It is also possible for unary relationships to exist, where only one entity is involved. This is known as a recursive relationship

Attribute = A property of an entity or a relationship

Attributes hold values that describe each occurence of an entity or relationship, and represent the main source of data stored in the database.

Attributes are named in lowercase on the first word and start in uppercase in words following. However, no spaces are used.

Attributes can be classified as:

• Simple or composite
• Single or multi-valued
• Derived

Single attribute = Attribute composed of a single, irreducible component

Composite component = Attribute that can be divided into smaller, independent elements

Single-valued = Attributes that hold only one value for each time an entity occurs

Multi-valued = Attributes that hold >= 1 values for each entity occurence.

Derived = Attributes that represent a value that can be derivable (calculable) from the value of one or more related attributes. Example includes 'age' being derived from 'dateOfBirth'.
Values may be derived from different entitied or more than one entity.

Superkey = Attribute(s) that uniquely identify each entity occurrence

Candidate key = Superkey that has the minimum number of attributes necessary for unique identification of each entity occurrence.

Primary key = Candidate key that is selected to identify each entity occurrence

Alternate key = Candidate keys that are not selected as the primary key of the entity

The entity rectangle is split in two:

• Top section = Entity name
• Bottom section = Attribute names

PK = primary key

PPK = part of composite key

AK = Alternate key

Indentation is used to show the elements of composite attributes

Multi-valued attributes have their ranges specified, for example:

• [1..*] = one to many
• [1..4] = one to four

Derived attributes are shown with a '/'

Strong entity = Entity that has a primary key that is independent of other entities.
Example: PK of Staff is staffNo, which identifies each staff occurrence in the table

Weak entity = Entity that is reliant on at least one other entity.

These represent the number of occurrences of an entity that may relate to a single occurrence of an associated entity

It's often seen as an integrity constraint and is reliant on the policies of the organisation

Multiplicity can be determined by:

• Examining documentation
• Consulting users

It's vital that all of the sample data represents all potential circumstances.

The most common form of relationship degree is binary, and that can be referred to as:

• one-to-one (1:1)
• one-to-many (1:)
• many-to-many (:*)

Cardinality = Description of the number of potential relationships between each entity involved

Participation contraint = Describes whether all or only some entity occurrences participate in a relationship.

In an ER model, cardinality is found from the maximum values in the multiplicity ranges either side of the relationship.

Participation on the other hand, is found from the minimum value in each range.

It is possible for problems to arise with designing an ER model. These problems are known as connection traps.

They often occur when there is a misinnterpretation of the meaning of certain relationships.

Two main types of traps include:

• Fan traps
• Chasm traps

Occur between related entities that are not directly connected and the indirect pathway that connect them includes two 1:* relationships that fan out from a central entity.

What this means is that certain entity occurrences that are related can only be connected by using a pathway that can become ambiguous

Occurs between related entities that are not directly connected and the indirect pathway that connects them includes partial participation.

The means that certain entity occurrences that are related have no means of connection