Relationships:Types of Relationships in databases

<< Attributes, The Keys

Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes >>

Database Management System (CS403)

Lecture No. 09

Reading Material

"Database Systems Principles, Design and Implementation"

written by Catherine Ricardo, Maxwell Macmillan.

Overview of Lecture

o Relationships in E-R Data Model

o Types of Relationships

Relationships

After two or more entities are identified and defined with attributes, the participants

determine if a relationship exists between the entities. A relationship is any association,

linkage, or connection between the entities of interest to the business; it is a two-

directional, significant association between two entities, or between an entity and itself.

Each relationship has a name, an optionality (optional or mandatory), and a degree (how

many). A relationship is described in real terms.

Assigning a name, optionality, and a degree to a relationship helps confirm the validity of

that relationship. If you cannot give a relationship all these things, then perhaps there

really is no relationship at all.

Relationship represents an association between two or more entities. An example of a

relationship would be:

Employees are assigned to projects

Projects have subtasks

Departments manage one or more projects

Relationships are the connections and interactions between the entities instances e.g.

DEPT_EMP associates Department and Employee.

A relationship type is an abstraction of a relationship i.e. a set of relationships

instances sharing common attributes.

Database Management System (CS403)

Entities enrolled in a relationship are called its participants.

The participation of an entity in a relationship is total when all entities of that set might

be participant in the relationship otherwise it is partial e.g. if every Part is supplied by a

Supplier then the SUPP_PART relationship is total. If certain parts are available without

a supplier than it is partial.

Naming Relationships:

If there is no proper name of the association in the system then participants' names of

abbreviations are used. STUDENT and CLASS have ENROLL relationship. However, it

can also be named as STD_CLS.

Roles:

Entity set of a relationship need not be distinct. For example

phone

name

city

SSN

manager

employee

works-for

worker

The labels "manager" and "worker" are called "roles". They specify how employee

entities interact via the "works-for" relationship set. Roles are indicated in ER diagrams

by labeling the lines that connect diamonds to rectangles. Roles are optional. They clarify

semantics of a relationship.

Symbol for Relationships:

Shown as a Diamond

Diamond is doubled if one of the participant is dependent on the other

Participants are connected by continuous lines, labeled to indicate cardinality.

In partial relationships roles (if identifiable) are written on the line connecting the

partially participating entity rectangle to the relationship diamond.

Total participation is indicated by double lines

Database Management System (CS403)

Types of Relationships

o Unary Relationship

An ENTITY TYPE linked with itself, also called recursive relationship. Example

Roommate, where STUDENT is linked with STUDENT

Example 1:

Roommate

1:1

Student

Database Management System (CS403)

Example

Sponsored

1:1

Person

o Binary relationship

A Binary relationship is the one that links two entities sets e.g. STUDENT-CLASS.

Relationships can be formally described in an ordered pair form.

Enroll = {(S1001, ART103A), (S1020, CS201A), (S1002, CSC201A)}

Entire set is relationship set and each ordered pair is an instance of the relationship.

M:N

Student

Class

o Ternary Relationship

A Ternary relationship is the one that involves three entities e.g.

STUDENT-CLASS-FACULTY.

Database Management System (CS403)

o N-ary Relationship

Most relationships in data model are binary or at most ternary but we could define a

relationship set linking any number of entity sets i.e. n-ary relationship

Entity sets involved in a relationship set need not be distinct. E.g.

Roommate = {(Student1, Student2) | Student1 ∈ Student Entity Set, Student2 ∈ Student

Entity Set and Student 1 is the Roommate of Student2}

Relationship Cardinalities

The cardinality of a relationship is the number of entities to which another entity can map

under that relationship. Symbols for maximum and minimum cardinalities are:

Maximum

inside

Entity Type

Minimum

Outside

o One-to-One mapping:

A mapping R from X to Y is one-to-one if each entity in X is associated with at most

one entity in Y and vice versa.

o Many-to-One mapping:

A mapping R from X to Y is many-to-one if each entity in X is associated with at

most one entity in Y but each entity in Y is associated with many entities in X.

Database Management System (CS403)

o One-to-Many mapping:

A mapping R from X to Y is one-to-many if each entity in X is associated with many

entities in Y but each entity in Y is associated with one entity in X.

o Many-to-Many mapping:

A mapping R from X to Y is many-to-many if each entity from X is associated with

many entities in Y and one entity in Y is associated with many entities in X.

Table of Contents: