Entity Relationship Diagrams

Entity Notations and Attributes

There are primarily four cases where you would want to use an Entity Relationship Diagram during design, which are;

• Database design
• Database debugging
• Database creation and patching
• Aid in requirmement gathering

ER Diagrams are composed of entities, relationships and attributes. They also depict cardinality, which defines relationships in terms of numbers. As we went through these in level 4, this time we are going to focus on hese with more detailed examples to help you shape your own work.

Entity

Entities are defineable objects that can have some data stored about it. You should think about entities as nouns. The shape of an entity is typically shown as a rectangle.

Entity Attributes

Also known as a column, an attribute is a property or characteristic of the entity that holds it. The attribute has a name that helps to describe the property and a type that describes the kind of attribute it is, such as varchar for a string, or int for an integer.

Figure 4: Example Entity with properties

Primary Key

Sometimes known as PK, the primary key is a special kind of entity attribute that is used to uniquely define a record in a database table . For example, if we have a unique ID for each purchase in a database, but one of the transactions has the same ID as another transaction, and this transaction ID is our PK, then this is an error and shouldn’t be possible, as our PK must always be unique. Lets look at a visual example of this:

Figure 5: Primary Key example

As we mentioned before, this type of ID cannot have a duplicate, so this would be an error that we would need to fix within the database.

Foreign Key

Sometimes known as FK, the foreign key is a reference to a primary key in another table. It is used as a way to identify the relationships between entities. Note that foreign keys don’t need to be unique, unlike primary keys. Multiple records can share the same values. The example below shows an entity with some columns, showing where a reference might take place.

Figure 6: Foreign Key example

ER Relationships

Remember that relationships help to signify the connection between two entities that are associated with each other in some way.

ER Cardinality

This is the definition that defines the numerical attributes between two entities or entity sets. Recall we we have the three main caridnal relationships of one-to-one, one-to-many and many-to-many.

• One-to-one: A one-to-one relationship is mostly used to split an entity in two to provide information concisely and make it more understandable, the example below shows this in practice:
  
• One-to-many: A one-to-many relationship refers to the relationship between two entities X and Y in which an instance of X may be linked to many instances of Y, but an instance of Y is only linked to one instance of X
  
• Many-to-many: Many-to-many refers to the relationship between two entities X and Y in which X may be linked to many instances of Y and vice versa. Note that a many-to-many relationship is split into a pair of one-to-many relationships in a physical ERD.
  

Example ER Diagram

The diagram below shows an example of a good, simple ER diagram showing the Physical Data Model. The Physical Data Model represents the actual design of a relational database. At Level 5, we are expecting you to create good quality ER diagrams, similar to the one below:

Figure 7: Physical Data Model example ER Diagram

Data Models

There are three types of conceptual models when we think about ERDs. They differ in the purposes they are created for and for the audiences they are intended for. At university, you will likely only encounter the Physical Model, but it is useful at Level 6 to know about the other two types: the Conceptual Model and the Logical Model. You can find more in depth information about these models at [1].

Physical Model

The physical model represents the actual design of a relational database. A physical data model elaborates on the logical data model by assigning each column with type, length, nullable etc. Since this kind of model represents how data should be structured and related in a specific DBMS (DataBase Management System), it is importnat to consider the convention and restriction of the actual database system in which the database will be created. It is important to make sure that the column types are supported by your DBMS and reserved words are not used in naming entities and columms.

Figure 1: Physical Data Model example

Below is another, more detailed example of a physical model showing a movie rental system:

Figure 2: ERD Example - Movie Rental System

Conceptual Model

Conceptual ERD models the business objects that should exist in a system and the relationships between them. This type of model is developed to present an overview of the entire system by recognizing the business objects involved as well. It defines what entities exist, NOT which tables. A many to many table may exist in a logical or physical model, but they are just shown as a relationship with no cardinality in the conceptional model.

An example of a conceptual data model can be seen below:

Figure 3: Conceptual Data Model example

One interesting feature of the conceptual model is that it is the only data model that supports generalization. This is because it is used to model a ‘kind of’ relationship, such as a triangle being a kind of shape. This is the only model that can do this.

Logical Model

Logical ERD is essentially just a detailed version of a Conceptual ERD. It is developed to enrich a conceptual model by defining explicitly the columns in each entity and introducing operational and transactional entities. The main difference between transactional data and operational data is that transactional data is the data that describes business events of the organization while operational data is the data that is used to manage the information and technology assets of the organization [3].

An example of a logical data model can be seen below:

Figure 4: Logical Data Model example