#Practical Session 0 - Entity Relationship Diagrams (ERD)

###By Dr Edmund Sadgrove

### University of New England

---

## Reading
* Chapter 3,4 from ***Fundamentals of Database Systems*** by Elmazri and Navathe

---

## Summary

* Introduction
* The Entity-Relationship Model
* The Entity-Relationship Model - Attributes
* The Entity-Relationship Model - Entities
* The Entity-Relationship Model - Relationships
* The Entity-Relationship Model - Weak Entities
* Higher-Degree Relationships
* ER Diagram Notation
* Transaction Example.

## Introduction
* The Entity Relationship Diagram (ERD) . We will recap some of this weeks lectures and look at a few examples.

## Introduction

<img width="60%" src="images/company_er.png" />

* Database Design
* The Entity-Relationship Model
	* Attributes
	* Entities
	* Relationships
	* Weak Entities
* Higher-Degree Relationships
* ER Diagram Notation
* Example Transaction ERD

## The Entity-Relationship Model

* The **Entity-Relationship(ER) model** is a high-level, **conceptual design** tool.
	* **Entity**:
		* Tangible real-world thing (later tuple in relation or table).
			* E.g. cars, houses, people.
		* **Attributes** - data items that define the entity.
			* E.g. name, DOB, salary.
		* **Values** - listed separately - help describe the attribute. 
		
<img width="45%" src="images/L5_slide5.png" />

## The Entity-Relationship Model - Entities

* With ER models we must define **entity types** with:
	* **Common attributes** - characteristics of a *real-world* entity.
	* Defines an **entity set** - set of entities with common attributes.
	* Can represent a relation or table.
	* Denoted by a shaded rectangle.
	
<img width="55%" src="images/L5_slide9.png" />

## The Entity-Relationship Model - Attributes

* Attributes can be:
	* **Simple**:
		* Atomic - indivisible.
		* Denoted by a single-oval.
	* **Composite** 
		* Multiple data items.
		* Can form heirarchies (with subparts). 
		* E.g. Name in Figure 7.2 (first, middle, last).
		* Denoted by a heirarchy of ovals.
		
<img width="50%" src="images/L5_slide6.png" />

## The Entity-Relationship Model - Attributes

<img width="55%" src="images/L5_slide7.png" />

* Attributes can be:
	* **Single-valued**
		* Not part of a set.
		* Unbounded - not restricted by a set. 
		* E.g. age, name.
		* ERD: single-oval.
	* **Multivalued**:
		* Contained set of values.
		* Upper bound and lower bound.
		* E.g. Car colour (set of colours).
		* Denoted by a double-oval.

## The Entity-Relationship Model - Attributes

<img width="55%" src="images/L5_slide8.png" />

* Attributes may be:
	* **Derived**:
		* Determined from another attribute.
		* Calculated *on-the-fly* (triggers). 
		* E.g. age from birthdate.
		* Denoted by a dashed-oval.
	* **Stored**:
		* Self determined.
		* Inserted *as is*.
		* E.g. birthdate.
		* Denoted by a single-oval.

## The Entity-Relationship Model - Entities

<img width="44%" src="images/car_er.png" />

* Entities of an **entity type** can have a **uniqueness constraint**:
	* Uniquness across all attributes.
	* **Key attribute** - similar to the relational model.
		* Uniquely identifies entities in an entity set.
		* Denoted by **underlining** the attribute name. 
	* **Composite attributes** can be used.
		* Depicts a composite key. 
		* Denoted by underlining the parent attribute.

## The Entity-Relationship Model - Attributes

<img width="38%" src="images/sets_types.png" />

* Like the **relational model** each attribute will have a **domain**.
	* Formally: an attribute *A* of entity set *E* has a value set.
		* *A*: *E* -> *P* (*V*) - where *P* is the power set.
		* The power set is all possible subsets of values.
	* Composite attibute value sets are the cartesian product of power sets.
		* V = *P*(*P*(*V<sub>1</sub>*)`\(\times\)`*P*(*V<sub>2</sub>*)`\(\times\)`...`\(\times\)`*P*(*V<sub>n</sub>*))
	* Typically ERD's do not depict value sets (including datatypes).
		* A UML diagram is used instead.

## The Entity-Relationship Model - Entities

<img width="44%" src="images/company_2.png" />

* Entity types may have multiple key attributes
	* ERD's have no concept of a **primary key**. 
	* With multiple key attributes *each is a key in its own right*.
		* Each key is independently unique.
*  ***Weak* entities** have no key attributes.
	* More on these later on! 

## The Entity-Relationship Model - Relationships

<img width="60%" src="images/company_1.png" />

* Referring to the diagram below:
* The attributes appear to refer to other entity types.
   * This is a **preliminary** design.
   * These will be converted to relationships in the final ERD.

## The Entity-Relationship Model - Relationships

<img width="45%" src="images/L5_slide14.png" />

* An ERD depicts relationships, this includes:
* A ***Relationship Type*** *R* can be defined between ***n*** entity types *E*.
* The is the ***Relationship Set***:
	* *R* among *n* for *E<sub>1</sub>,E<sub>2</sub>,...,E<sub>n</sub>*
	* Where each entity is said to participate in the relationship *R*.
	* Representing an association between these entities.

## The Entity-Relationship Model - Relationships

<img width="35%" src="images/er_instance.png" />

* The **Degree** of the relationship is the number of participating entity types.
	* **Binary**:
		* Common relationships between two entites (degree 2).
	* **Ternary**:
		* Relationships between three entities (degree 3).
* It is possible to represent relationships using attributes.
	* If we represent a **binary relationship**:
		* For example, the **EMPLOYEE entity** with a department attribute
		* Or **DEPARTMENT** with an employee attribute.
		* Both can represent the WORKS_FOR relationship.

## The Entity-Relationship Model - Relationships

<img width="65%" src="images/rec.png" />

* It is possible for relationships to be ***recursive***
* For example, the EMPLOYEE entity has a ***supervision*** relationship with itself. 

## The Entity-Relationship Model - Relationships

<img width="35%" src="images/many.png" />
<img width="35%" src="images/onetoone.png" />

* Relationships will have a **Cardinality**: 
	* This specifies the number of relationship instances that an entity can participate in.
    	* This can be a cardinality of:
		* **1:1, 1:N, N:1 or M:N**.
		* E.g. employee WORKS_FOR department will be 1:N.
* The cardinality of the relationship is displayed on the diamonds near the relationship links.

## The Entity-Relationship Model - Relationships

<img width="45%" src="images/L5_slide14.png" />

* Entity type relationships can be either:
	* ***Partial*** participation:
		* Select entities participate. 
		* E.g. not all employess are managers.
		* Depicted as a single line.
	* ***Totally*** participate:
		* Every entity participates.
		* E.g. all employees work for a department.
		* Depicted as a double line.

## The Entity-Relationship Model - Relationships

<img width="38%" src="images/company_full.png" />

* Relationship types can have attributes.
	* In 1:1 and 1:N relationships:
		* **Attributes** can be **migrated** to one of the participating entities.
	* In a **1:1** relationship:
		* **Attributes** can be sent to either side.
	* In a **1:N** relationship:
		* Attribute need to be sent to the **'N' side** of the relationship.
	* In an **M:N** relationship:
		* Attribute must remain on the relationship.
		* Their value is determined by the combination of the participating entities. 

## The Entity-Relationship Model - Weak Entities

<img width="40%" src="images/L5_slide9.png" />

* **Entity types** can be **weak entities**:
	* They have no key attributes.
	* These entities cannot exist without another entity-type.
		* E.g. dependent entity relies on employee. 
	* Typically have a **Partial Key**:
		* Denoted as a dashed underline.
    	* Weak entities are depicted as a double-square.
		* Relationships are depicted with a double-diamond.
		* Always have total-participation (double-line).

* In **SQL** we would declare a foriegn key with ``` 'ON DELETE CASCADE' ```.

---
## Higher-Degree Relationships

<img width="45%" src="images/supply.png" />

* We have focussed on **binary** relationships.
* It is desirable to have **higher degree** relationships.
* Where more than two entities are related.
* **Higher degree** relationships have different meanings.
* For clarity **higher-degree relationship** are depicted alongside **binary** relationships.

---
## ER Diagram Notation

* Summary of ER diagram notation.

<img width="50%" src="images/note_2.png" />
<img width="50%" src="images/note_1.png" />

## Transaction Example
* Example transaction ERD:
	* Entities:
		* Customer.
		* Account.
		* Third Party.
	* Relationships:
		* Transaction between parties.

<img width="50%" src="images/transaction_erd.png" />

---
## ER Model vs EER Model

* The Enhanced Entity Relationship (EER) Model:
	* **Includes all** the modelling concepts of the **ER** Model.
	* Introduces the concept of ***object-oriented design***.
	* IN EER Models  ***Object*** is used interchangeably with ***Entity***.

<img width="45%" src="images/slide4.jpg" />


## ER Model vs EER Model

<img width="45%" src="images/slide5.png" />

* EER Models include the **concepts** of:
    * Superclasses
    * Subclasses
    * Generalisation
    * Specialisation
    * Category/Union-type

* These concepts are analogous to the features presented within a typical object-oriented programming language.

## EER Model - Object Oriented Approach

<img width="55%" src="images/employee.png" />

* An EER object can be:
	* **Superclass** or ***Supertype***:
		* Parent entity type.
		* Exists independently.
		* E.g. employee.
	* **Subclass** or ***Subtype***:
		* A **sub-grouping** within the **entity type**.
		* Requires a superclass to exist.
		* E.g. supervisor, engineer, administration.

* Often called an *is-a* relationship.

## EER Model - Object Oriented Approach

* The EER model also uses **type inheritance**. 
* **Recall** that an **entity type** is defined by the **value set**.
	* The powerset of all possible values (attribute domain).
* In the EER model:
	* A **subclass** ***inherits*** the domain of their superclasses attributes.
	* The subclass will also have attributes **unique** to the subclass type.

## Specialisation

<img width="55%" src="images/employee.png" />

* **Specialisation**:
	* Defines a set of subclasses based on common characteristics.
	* Represented by a **grouping circle** with adjoining lines.
		* The *subset* symbol represents direction (arch).
	* Example:
		* EMPLOYEE has two specialisations:
			* Job type.
			* Method of Pay.
	* Unique attributes are called **specific attributes**.

## Specialisation

* Subclass specification:
	* May share the majority of their **attributes**.
	* May only have a small number of **specific attributes**.
	* May participate in different relationships to the super/subclasses.

* Specialisation represents related but distinct object types.

## Generalisation

<img width="40%" src="images/vehicle.png" />

* **Generalisation**:
	* The reverse process of **specialisation**.
	* Common features are consolidated into a **superclass**.
	* Uncommon attributes are included as **specific attributes**.
	* Example:
		* CAR and TRUCK can be generalised into VEHICLE. 
		* With common attributes placed in the superclass.

## Constraints on Specialisation and Generalisation

<img width="45%" src="images/att_spec.png" />

* We can have ***condition-defined* subclasses**:
	* Membership is dependent on a superclass attribute value.
	* Example:
		* Adding attribute *job_type* to our EMPLOYEE supertype.
	* In **specialisation**:
		* All subclasses can have conditional membership:
			* This is called **attribute-defined**.
			* Implies disjointed (next slide).
		* With no conditional membership:
			* This is called **user-defined**.

<img width="50%" src="images/not_disjoint.png" />

* The EER model has inheritance constriants:
* The **disjoint constraint** (**d**):
	* Specifies an entity can be a member of one subclass (only).
* The **overlapping constraint** (**o**):
	* Specifies an entity can be a member of multiple subclasses.
* The **total specialisation constraint**:
	* Must be a member (double-line).
* The **partial specialisation constraint**:
	* May be a member (single-line).

<img width="45%" src="images/slide13.png" />

* There are four possible **combinations**:
    * Disjoint, total
    * Disjoint, partial
    * Overlapping, total
    * Overlapping partial 

* The combinations used will be determined by the application.
* Typically superclasses will have **total participation** as the superclass is generalised from the subclasses.

* Rules apply when manipulating entities in a generalisation/specialisation relationship (think aggregation):
	* **Deleting**:
		* Deleting a **superclass** entity deletes from all **subclasses**.
	* **Inserting** :
		*  Inserting into a **superclass** means that it is inserted into ***predicate-defined* subclasses** (matching attributes).

* **Inserting** into a **superclass** with **total specialisation** implies that it is inserted into at least one **subclass**. 

## Hierarchies and Lattices

<img width="50%" src="images/lattice.png" />

* Class relationships can be in the form of *hierarchies* and *lattice* structures.
	* **Hierarchy**:
		* Implies just one relationship connection.
	* **Lattice**:
		* Allows more than one relationship connection.

* In both cases a subclass **inherits** the attributes from all predecessors superclasses .
* A ***leaf node*** is a class that has no subclasses.

<img width="35%" src="images/uni.png" />

* In a **lattice** structure:

	* Possible for a subclass to **inherit** the same attributes twice. 
	* Example:
		* STUDENT_ASSISTANT entity will inherit the PERSON attributes twice.
	* In this situation the attributes are only inherited once.

* Lattices and Hierarchies can be generated through: a top-down or bottom-up refinement process.

	* **Top-down** approach:

		* Generalise the superclasses and specialise down to the subclasses.
	* **Bottom-up** approach:
		* Specialise as subclasses and generalise up into common attribute superclasses.

* These processes can create identical arrangements of superclasses and subclasses.

## Modeling Union Types

* So far all relationship examples have had a single **superclass**.

<img width="45%" src="images/union.png" />

* It may be necessary to use multiple **superclasses**:

	* This means a superclass will represent a subset of the subclass entity.
	* This is called a **union** relationship (**U**).
	* Inherits a set of attributes depending on the subset.
	* Example:
		* Person, bank or company can be a vehicle owner.
		
<img width="35%" src="images/L6_slide17.png" />

* **Union** participation can be *total* or *partial*.
	* **Total participation**:
		* A subclass holds the ***union*** of all entities in its superclass
	* **Partial Participation**:
		* A subclass can hold a **subset** of the superclasses.

 * **Total** participation is denoted using the **double line** between the subclass and the grouping circle.

## Design Choices

* **Conceptual database design** is an iterative process of *refinement*. 
	* Some guidelines include:
		* Only represent **subclasses** when necessary.
		* Subclasses can be **merged** into a superclass. 
			* If no specific relationship or few local attributes.
		* Avoid using **union types**:
			* Can be complex and difficult to implement.
			* Will become separate relations.

    * The choice of **constraints** (e.g. disjoint, overlapping and total, partial) is driven by the mini-world. The **default** will usually be **overlapping-partial**.

---
## Customer Banking Example

* Example banking EERD:
	* Entities:
		* Bank branch.
		* Customer.
		* Customer account.
			* Student.
			* Investor.
			* Standard.
		* Customer loan.
			* Car.
			* House.

<img width="50%" src="images/bank_account_eerd.png" />

---

## Exercises for You
For these questions, feel free to make assumptions about the key attributes for functional dependencies, there may be more than one result, but there is typically a most efficient result, if you can find it. Make sure that you use the Chen's ERD notation, as detailed above and not crows foot.

1. Consider the following scenario, 
	* An auto repair workshop manages customers, their vehicles, work orders (jobs), mechanics, parts, and suppliers.
		* A Customer owns one or more Vehicles.
		* Each WorkOrder is for exactly one Vehicle; each WorkOrder has one or more LineItems (labor or parts).
		* A Mechanic can work on many WorkOrders; each WorkOrder can involve many Mechanics, with hours tracked per assignment.
		* Parts are supplied by Suppliers.
	* Entity / Relations - attributes:
		* Customer:
			* customer_id,
			* name,
			* email,
			* Address{street, city, postcode},
			* PhoneNumber{… multivalued}
			
		* Vehicle:
			* vin,
			* make,
			* model,
			* year
			
		* WorkOrder:
			* order_id,
			* opened_date,
			* closed_date,
			*  status
			
		* WorkOrderLine 
			* line_no (weak?),
			* kind {‘LABOR’|‘PART’}, 
			* quantity, 
			* unit_price, 
			* description
			
		* Mechanic:
			* mechanic_id,
			* name
			* grade

		* Part:
			* part_id,
			* name, 
			* unit_price

		* Supplier:
			* supplier_id,
			* name, 
			* contact_email
			
	Construct an ERD to match the above description, to draw the ERD you have multiple options; you can draw it on paper, you can use [draw.io](https://app.diagrams.net/), or you can use [graphvis](https://magjac.com/graphviz-visual-editor/) that  uses the DOT language to generate ERDs from text.
	
2. Add additional  specialisations and constructs an EERD based on the ERD in exercise 1, including for:
	* Person - > Customer,  Mechanic.
	* Vehicle - > Truck, Motorcycle, Car.

	Feel free to leave out attributes as these are already defined in the ERD, but you can include type attributes and sub  attributes.