💡Recall

Concepts in the entity-relationship (ER) model. The ER diagram

Screenshot 2022-10-12 at 10.16.33.png

Example

Animals → Owned by → Owners
id        since      oid
name                 oname
color

Screenshot 2022-10-12 at 10.16.46.png

cnp – primary key for the Employee entity set

Degree of a relationship set

degree of a relationship = number of entity sets that participate in the relationship set

Unary relationship set (degree = 1)

Screenshot 2022-10-12 at 10.27.13.png

Binary relationship set (degree = 2)

Screenshot 2022-10-12 at 10.37.47.png

Screenshot 2022-10-12 at 10.37.53.png

remuneration – descriptive attribute for the Performs_in relationship set

Ternary relationship set (degree = 3)

Screenshot 2022-10-12 at 10.31.59.png

Screenshot 2022-10-12 at 10.31.07.png

Mapping cardinalities for binary relationship sets

1:1 (one to one)

Screenshot 2022-10-12 at 10.39.55.png

t1-g1

t2-g2

t3-g3

or Person → Has ← Passport

1:m (one to many)

Screenshot 2022-10-12 at 10.41.15.png

s1-p1

s1-p2

s1-p3

~~s2-p1~~

m:n (many to many)

Screenshot 2022-10-12 at 10.40.55.png

s1-f1

s1-f2

s2-f1

Translation to the Relational Model

Entity set → Relation

name of the entity set → name of the relation
attributes of the entity set → attributes in the relation
primary key of the entity set → primary key of the relation

Screenshot 2022-10-12 at 11.06.59.png

m:n Relationship set → Relation

name of the relationship set → the name of the relation
primary key attributes for each entity set that takes part in the relationship set:
- become attributes in the relation
- are foreign keys in the relation
- can become the primary key of the relation
relationship set descriptive attributes → attributes in the relation

Screenshot 2022-10-12 at 11.12.02.png

a1-m1

a1-m2

a2-m1

n:1/1:n Relationship set → Relation

Screenshot 2022-10-12 at 11.13.37.png

one can avoid creating an additional relation
the Employee entity set is on the n (many) side of the Works_in relationship set, while the Department entity set is on the 1 side
the relationship set data is included in the Employee relation, corresponding to the entity set that lies on the n side of the relationship set - this relation will store every employee's department along with the date when he/she started working there
the key in the Department relation (corresponding to the entity set that lies on the 1 side of the relationship set) becomes a foreign key in the Employee relation