class: bottom, left
background-image: url(images/g.png)

<h2 class="title_headings_sml">COSC210 Database Management Systems</h2>

<h1 class="title_headings_sml"> Lecture 7 - The Enhanced Entity-Relationship Model
</h1>

<h3 class="title_headings_sml"> Dr. Edmund Sadgrove </h3>

---
background-image: url(images/reg_background.png)
# Reading
* Chapter 4 from ***Fundamentals of Database Systems*** by Elmazri and Navathe

---
background-image: url(images/reg_background.png)
## Summary

* ER Model vs EER Model
* EER Model - Object Oriented Approach
* Specialisation
* Generalisation 
* Constraints of Specialisation and Generalisation
* Hierarchies and Lattices
* Modeling Union Types
* UNIVERSITY Example
* Design Choices

---
background-image: url(images/reg_background.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 style="float: right;" width="45%" src="images/lecture_6/slide4.jpg" />

---
background-image: url(images/reg_background.png)
## ER Model vs EER Model

* EER Models include the **concepts** of:
<img style="float: right;" width="45%" src="images/lecture_6/slide5.png" />
    * Superclasses
    * Subclasses
    * Generalisation
    * Specialisation
    * Category/Union-type

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

---
background-image: url(images/reg_background.png)
## EER Model - Object Oriented Approach

* An EER object can be:
	* **Superclass** or ***Supertype***:
<img style="float: right;" width="55%" src="images/lecture_5/employee.png" />
		* 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.

---
background-image: url(images/reg_background.png)

## 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.


---
background-image: url(images/reg_background.png)
## Specialisation

* **Specialisation**:
	* Defines a set of subclasses based on common characteristics.
<img style="float: right;" width="55%" src="images/lecture_5/employee.png" />
	* 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**.

---
background-image: url(images/reg_background.png)
## 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.


---
background-image: url(images/reg_background.png)
## Generalisation

* **Generalisation**:
	* The reverse process of **specialisation**.
<img style="float: right;" width="40%" src="images/lecture_5/vehicle.png" />
	* 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.
---
background-image: url(images/reg_background.png)
## Constraints on Specialisation and Generalisation

* 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:
<img style="float: right;" width="45%" src="images/lecture_5/att_spec.png" />
			* This is called **attribute-defined**.
			* Implies disjointed (next slide).
		* With no conditional membership:
			* This is called **user-defined**.
---
background-image: url(images/reg_background.png)
## Constraints on Specialisation and Generalisation
* 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.
<img style="float: right;" width="50%" src="images/lecture_5/not_disjoint.png" />
* The **total specialisation constraint**:
	* Must be a member (double-line).
* The **partial specialisation constraint**:
	* May be a member (single-line).

---
background-image: url(images/reg_background.png)
## Constraints on Specialisation and Generalisation

* There are four possible **combinations**:

    * Disjoint, total
<img style="float: right;" width="45%" src="images/lecture_6/slide13.png" />
    * 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.


---
background-image: url(images/reg_background.png)
## Constraints on Specialisation and Generalisation

* 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**. 

---
background-image: url(images/reg_background.png)
## Hierarchies and Lattices

* Class relationships can be in the form of *hierarchies* and *lattice* structures.
	* **Hierarchy**:
<img style="float: right;" width="50%" src="images/lecture_5/lattice.png" />
		* 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.
---
background-image: url(images/reg_background.png)
## Hierarchies and Lattices
<img style="float: right;" width="35%" src="images/lecture_5/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.

---
background-image: url(images/reg_background.png)
## Hierarchies and Lattices

* 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.

---
background-image: url(images/reg_background.png)
## Modeling Union Types

* So far all relationship examples have had a single **superclass**.
<img style="float: right;" width="45%" src="images/lecture_5/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.
---
background-image: url(images/reg_background.png)
## Modeling Union Types

* **Union** participation can be *total* or *partial*.
	* **Total participation**:
<img style="float: right;" width="35%" src="images/lecture_6/L6_slide17.png" />
		* 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.
---
background-image: url(images/reg_background.png)
## 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**.

---
background-image: url(images/reg_background.png)
## Customer Banking Example

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

---
background-image: url(images/reg_background.png)
## Summary
 
* ER Model vs EER Model
* EER Model - Object Oriented Approach
* Specialisation
* Generalisation 
* Constraints of Specialisation and Generalisation
* Hierarchies and Lattices
* Modeling Union Types
* UNIVERSITY Example
* Design Choices


---
background-image: url(images/reg_background.png)
class: middle, center

# Questions?

---
background-image: url(images/reg_background.png)

# Next Lecture

* Generating an SQL schema from a ER/EER model..


---
background-image: url(images/reg_background.png)
# Reading

* Chapters 4 from ***Fundamentals of Database Systems*** 
* Chapter 7 from ***Fundamentals of Database Systems*** for Fridays prac session.


---