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 9 - Relational Database Design by ER/EER-to-Relational Mapping
</h1>

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


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

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

* ER-to-Relational Mapping Algorithms
* SQL Implementation
* EER-to-Relational Mapping Algorithms


---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms

* Mapping - Step One:
<img style="float: right;" width="45%" src="images/lecture_6/company.png" />
	* **Strong entities**:
		* Entity type *E* is used to create a relation *R*.
		* Includes only:
			* **Simple/single-valued attributes** of *E*.
	* **Primary key**:
		* Selected from key attributes of *E*.
		* Can be a **composite key**.
	* **Candidiate keys**:
		* Other key attributes listed as candidates.
		* Useful for indexing. 


---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms

* Mapping - Step Two:
	* **Weak entity**:
		* Weak entity type *W* is mapped to a relation *R*.
		* Includes only:
			* **Simple/single-valued attributes** of *E*.
<img style="float: right;" width="50%" src="images/lecture_5/L5_slide9.png" />
	* **Primary key**:
		* Combination of owner and **partial key**.
	* **Foreign key**:
		* Owner primary key.
		* Constraints:
			* ON DELETE CASCADE.
			* ON UPDATE CASCADE.

---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms

* Mapping - Step Three:
<img style="float: right;" width="45%" src="images/lecture_4/onetoone.png" />
	* **1:1** relationships *R*:
		* Identify the participating relations *S* and *T*.
	* Mapping has three options:
		* Using **foreign keys**.
		* **Merging** Relations.
		* An **additional** relation.

---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms

* Mapping - Step Three (option 1):
<img style="float: right;" width="45%" src="images/lecture_6/relations.png" />
	* **Foreign key**:
		* Select one relation (say *S*).
		* Include a foreign key that points to primary key of *T*.
	* Select entity with **total participation** for role of *S*
		* Every tuple in *S* references a tuple in *T*.	
		* E.g manager - employee *(S)* references deparment *(T)*.
		* The reverse will cause null values (non managers in *S*).

---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms

* Mapping - Step Three (option 2):
	* **Merging relations**:
		* If both *S* and *T* have total participation in *R*.
		* Merge attributes of *S* and *T* into single relation.

* Mapping - Step Three (option 3):
	* **Additional relation**:
		* Contains tuples that map the keys from *S* to *T*.
		* Option for 1:1, but required for N:M.

---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms

* Mapping - Step Four:
	* **1:N** relationship *R*:
		* In binary a relationship *R*, relation *S* is on the *N-side*.
<img style="float: right;" width="45%" src="images/lecture_6/relations.png" />
	* **Foreign key**:
		* The foreign key is placed on the *N-side*.
		* Tuples in *S* references primary key of *T*.
		* As multiple tuples of *S* point to a single tuple of *T*.
		* The reverse will cause redundancy.

* An alternative approach is to use a **third relation** to map the keys from *S* to *T*.

---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms

* Mapping - Step Five:
<img style="float: right;" width="45%" src="images/lecture_6/relations.png" />
	* **M:N** relationship *R*:
		* Create additional weak relation *W*: 
			* This relation has an existence dependency.	
			* **Primary key** will be a composite key of *S* and *T* primary keys.	
			* Requires **foreign keys** that reference keys of *S* and *T*.

		* **Constraints**:
			* ON DELETE CASCADE.
			* ON UPDATE CASCADE.

---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms
<img style="float: right;" width="45%" src="images/lecture_6/relationships.png" />
* Mapping - Step Six:
 	* **Multi-valued** attribute *A* of relation *T*:
		* Create additional relation *S*: 
			* Contains values from *A* as primary key tuples (single attribute).
	* **Foreign key**:
		* *T* references primary key of new relation *S*. 

* For example: **DEPT_LOCATIONS** 
* http://turing.une.edu.au/~cosc210/workshops/prac_1/data/dept_locations.csv
---
background-image: url(images/reg_background.png)
## ER-to-Relational Mapping Algorithms
<img style="float: right;" width="45%" src="images/lecture_6/supply.png" />
* Mapping - Step Seven (final step):
	* **N-ary** (non-binary) relation *R*.

		* Create a new relation *W*.
		* **Primary key** will be a composite key of participating relation primary keys.
		* **Foreign keys** will reference the primary keys of the participating relations.

* The new relation *W* will be a weak relation.

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

* Mapping in development may still require an implementation model.
	* Such as the unified modelling language (**UML** diagram).
* We will map directly to *PostgreSQL*.
	* We need to think carefully about the operations:
		* **Referential constraints** and Triggers.

* This can de demonstrated through the implementation of the **COMPANY database**

---
background-image: url(images/reg_background.png)
## SQL Implementation
* The EMPLOYEE entity is represented as an SQL table.
* Notice that the composite attribute name has been decomposed into three separate columns

```sql

CREATE TABLE employee (
  fname    varchar(15) NOT NULL, 
  minit    varchar(1),
  lname    varchar(15) NOT NULL,
  ssn      char(9) PRIMARY KEY,
  bdate    date,
  address  varchar(50),
  sex      char,
  salary   decimal(10,2),
  superssn char(9),
  dno      integer,
  foreign key (superssn) references employee(ssn)
        ON DELETE SET NULL ON UPDATE CASCADE,
  foreign key (dno) references department(dnumber)
        ON DELETE SET NULL ON UPDATE CASCADE
);

```

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

* The DEPARTMENT and PROJECT entities can be implemented in a similar fashion.

```sql
CREATE TABLE department (
  dname        varchar(25) NOT NULL,
  dnumber      integer PRIMARY KEY,
  mgrssn       char(9) NOT NULL, 
  mgrstartdate date
);

CREATE TABLE project (
  pname      varchar(25) UNIQUE NOT NULL,
  pnumber    integer PRIMARY KEY,
  plocation  varchar(15),
  dnum       integer NOT NULL,
  FOREIGN KEY (dnum) REFERENCES department(dnumber)
	ON DELETE SET NULL ON UPDATE CASCADE
);
-- Avoids deadlock when adding employee after department.
ALTER TABLE department
ADD CONSTRAINT manager_constraint FOREIGN KEY (mgrssn) REFERENCES employee (ssn)
	ON DELETE SET NULL ON UPDATE CASCADE;

```

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

* The weak entity DEPENDENT is implemented as a table with a foreign key referencing the employee table.
* Notice that the primary key consists of the `essn` and the `dependent_name` - this represents the esi 


```sql

CREATE TABLE dependent (
  essn           char(9),
  dependent_name varchar(15),
  sex            char,
  bdate          date,
  relationship   varchar(8),
  PRIMARY KEY (essn,dependent_name),
  FOREIGN KEY (essn) REFERENCES employee(ssn)
	ON DELETE CASCADE ON UPDATE CASCADE
);

```

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

* The WORKS_ON table implements the M:N relationship between EMPLOYEE and PROJECT.

```sql

CREATE TABLE dept_locations (
  dnumber   integer,
  dlocation varchar(15), 
  PRIMARY KEY (dnumber,dlocation),
  FOREIGN KEY (dnumber) REFERENCES department(dnumber)
	ON DELETE CASCADE ON UPDATE CASCADE
);
```
---
background-image: url(images/reg_background.png)
## SQL Implementation

* The DEPT_LOCATION table implements the multi-valued department location attribute. Note the direction of the relationship.

```sql

CREATE TABLE works_on (
   essn   char(9),
   pno    integer,
   hours  decimal(4,1),
   PRIMARY KEY (essn,pno),
   FOREIGN KEY (essn) REFERENCES employee(ssn)
      ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY (pno) REFERENCES project(pnumber)
      ON DELETE CASCADE ON UPDATE CASCADE
);
```


---
background-image: url(images/reg_background.png)
## EER-to-Relational Mapping Algorithms

* Recall that the EER model is an extension of the ER model.
	* This means we can map relations in the same way.

<img style="float: right;" width="45%" src="images/lecture_5/employee.png" />
* Two mapping algorithms are added:
    * For **Specialisations/Generalisation**.
    * For **union types**.
* These are applied as steps *8* and *9*.

---
background-image: url(images/reg_background.png)
## EER-to-Relational Mapping Algorithms

* Mapping - Step Eight:
	* Mapping entities in **Specialisation/Generalisation**.

* There are a 4 options for representing this relationship:
    * Multiple relations - **superclass and subclasses**.
    * Multiple relations - **subclasses only**.
    * Single relation using a ***type* attribute**.
    * Single relation with multiple ***type* attributes**.

---
background-image: url(images/reg_background.png)
## EER-to-Relational Mapping Algorithms

* Mapping - Step Eight (option 1):
	* Create relations for all super and sub classes.
	* The **superclass** relation *L* will have a **primary key** *PK(L)*.
	* The **subclass** relations *L<sub>1</sub>, L<sub>2</sub>,...,L<sub>n</sub>* will have a primay key *PK(L<sub>i</sub>)*.
		* Such that *PK(L<sub>i</sub>)* = *PK(L)* 
		* This will be a **foreign key** that references *L*.

* This will adhere to superclass/subclass **contraints**.
* This approach is suitable for any specialisation (d,o, total and/or partial)

---
background-image: url(images/reg_background.png)
## EER-to-Relational Mapping Algorithms

* Mapping - Step Eight (option 2):
	* Create a relation for each subclass (only).
<img style="float: right;" width="45%" src="images/lecture_5/lattice.png" /> 
		* **Superclass** attributes are replicated within **subclass** relations.
			* Only works if the relationship is **disjoint**.
			* Not recommended for **overlapping**.
				* This will lead to further redundancy.		

* This approach **involves redundancy**.

---
background-image: url(images/reg_background.png)
## EER-to-Relational Mapping Algorithms

* Mapping - Step Eight (option 3):
	* Create a single relation with a ***type* attribute**.
		* This will indicate the subclass entity that the tuple represents. 
		* This will only work for **disjoint** specialisations.

* This approach will generate **NULL** values within tuples with types where **specific attributes** are not applicable. 


---
background-image: url(images/reg_background.png)
## EER-to-Relational Mapping Algorithm

* Mapping - Step Eight (option 4):
	* Create a single relation, **boolean-type** attribute.
		* Indicates membership with true/false flag. 
* This will work for **overlapping** specialisations.
<img style="float: center;" width="50%" src="images/lecture_6/eer_mapping.png" /> 

---
background-image: url(images/reg_background.png)
## EER-to-Relational Mapping Algorithms

* Mapping - Step Nine (final):
	* **Union types** (recall):
		* A **Subclass** inherits all attributes and relationships from *multiple* **superclasses**.
<img style="float: right;" width="40%" src="images/lecture_5/union.png" /> 
	* **Surrogate key**:
		* A new relation is created with a new key.
		* Surrogate key is added to superclass relations.

<img style="float: right;" width="28%" src="images/lecture_6/union.png" /> 
---
background-image: url(images/reg_background.png)
## Summary

* ER-to-Relational Mapping Algorithms
* SQL Implementation
* EER-to-Relational Mapping Algorithms

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

# Questions?

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

* SQL Two (Query Structure).

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

* Chapters 4 and 9  from ***Fundamentals of Database Systems*** 
* Chapter 7 from ***Fundamentals of Database Systems*** this week's  prac session.


---