Seminar1.pdf

💡Recall

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

Example

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

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)

Binary relationship set (degree = 2)

remuneration – descriptive attribute for the Performs_in relationship set

Ternary relationship set (degree = 3)

Mapping cardinalities for binary relationship sets

1:1 (one to one)

t1-g1

t2-g2

t3-g3

or Person → Has ← Passport

1:m (one to many)

s1-p1

s1-p2

s1-p3

s2-p1

m:n (many to many)

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

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

a1-m1

a1-m2

a2-m1

n:1/1:n Relationship set → Relation

• 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

📌 Summary