Database Systems

Lecture # 8-9

s
Relationship
• Relationship exists from both sides
• Often we only write on direction relationship
Relationship
• In context of relationship we can differentiate
between
• Relationship Type
• Relationship Instance
Relationship Type

Teaches
TEACHER COURSE

Relationship type is a meaningful association

between or among entity types
Relationship Instance
Teacher Teaches Courses
Chen C++
Melton Java
Charles Communication
Davis SQL

• Association between entity instances.

• Where each relationship instance includes
exactly one entity from participating entity types.
Relationships
In Relationships we have following major concepts

• Degree of the Relationship

• Cardinality of Relationship
Degree
OF A REL ATIONSHIP
Degree of Relationships
• The number of different entity sets participating in a
relationship set is called as degree of a relationship
set.
Degree of Relationships
Following are common degrees in ER-Model
1. Unary
2. Binary
3. Ternary
4. Higher Degree(Rarely Encountered)
 Unary Relationship
When there is only ONE entity set participating in
a relation, the relationship is called as unary
relationship. For example, one person is married to
only one person.

Manages
Is_Married EMPLOYEE
Person

One-to-one One-to-many
Binary Relationship
• A relationship between the instances of two entity
types.
• Most common relationship faced.
Relationships

EMPLOYEE
Is_assigned PARKING PLACE

CUSTOMER Receives INVOICE

EMPLOYEE Completes COURSE

 Ternary Relationship
A simultaneous relationship between the instances of three
entities
PART

VENDOR WAREHOUSE
Supplies
 Ternary Relationship
A simultaneous relationship between the instances of three entities

PATIENT
P_ID
Name

PHYSCIAN TREATMENT
PHY_ID T_Code
Name
Treats Description
Cardinality
OF REL ATIONSHIP
Cardinality
Specifies that how many number of instances of
entity A that can (or must be) associated with each
instance of another entity B.
Showing Cardinalities
There are three ways of labeling cardinalities:

0  Zero

|  One

 Many
Cardinalities on ERD
There are two type of cardinalities are represented
on ERD
1. Minimum Cardinality
2. Maximum Cardinality
Minimum/Inside
Cardinality
Minimum number of instances of one entity that
may be associated with each instance of another
entity
It can be 0,1
Maximum/Outside
Cardinality
Maximum number of instances of one entity that
may be associated with each instance of another
entity
It can be 1 or many
Minimum Maximum
Cardinality Cardinality

Mandatory One

Zero or One

Mandatory Many

Optional Many
Example of Cardinality
Discovering Cardinality
• Cardinality of the relationship is sometimes
implement the Rules of the organization
• Discovering Cardinality is tricky
• Two Methods are used for this purpose:
• Question Method
• Entity Instance Method
Question Method
• We ask Two questions
• Each question start with word “one”
 Question Method
• QUESTION: One customer Receives how many invoices
• Answer: One or Many

Customer Receives INVOICE

One-to-Many
• QUESTION:One Invoice Given how many Customers
• ANSWER: One
Cardinality

Has PATIENT
PATIENT
HISTORY
One – to- Many

Mark visit 1
Sarah visit 1
visit 2
Tom visit 1
 Cardinality

Is_assigned
EMPLOYEE PROJECT

Many – to- Many

Rose Database
Pete Telecom.
Tom OOP
Fred Research
Examples
Manages

EMPLOYEE

Is_assigned
EMPLOYEE PARKING PLACE
 CASE (Identify Cardinality)
The products of a company are grouped into several product lines. The
identifier for a product is Product ID, whereas the identifier for a
product line is Product Line ID. We identify the following additional
attributes for product: Product Description, Product Finish, and Product
Standard Price. Another attribute for product line is Product Line Name.
A product line may group any number of products but must group at
least one product. Each product must belong to exactly one product
line.
Customers submit orders for products. The identifier for an order is
Order ID, Order Date, Days Passed since order is placed. A customer
may submit any number of orders, but need not submit any orders.
Each order is submitted by exactly one customer. The identifier for a
customer is Customer ID. Other attributes include Customer Name (First
Name, Last Name), Customer Address (Street No, City, Country), and
Customer Postal Code.
 CASE (Make an ERD)
The identifier for a product is Product ID. We identify the following
additional attributes for product: Product Description, Product Finish, and
Product Standard Price. Each product is assembled from a specified
quantity of one or more raw materials. The identifier for the raw material
entity is Material ID. Other attributes include Unit Of Measure, Material
Name, and Material Standard Cost. Each raw material is assembled into
one or more products, using a specified quantity of the raw material for
each product.
Raw materials are supplied by vendors. The identifier for a vendor is
Vendor ID. Other attributes include Vendor Name and Vendor Address.
Each raw material can be supplied by one or more vendors. A vendor may
supply any number of raw materials or may not supply any raw materials
to Pine Valley Furniture. Supply Unit Price is the unit price a particular
vendor supplies a particular raw material.
Special Cases
Some time we have to handle some special case in ERD such as:
1. Attributes on Relationships
2. Associative Entity
3. Attribute or Relationship
4. Attribute or Entity
5. Multiple Relationships
6. Many to Many Relationships
7. Ternary Relationship
8. Time Dependent Data
Attributes on
Relationships
Sometimes we have to include attribute with
relationships.
Where we include attribute Date_Completed in
following situation.
Date_Completed

EMPLOYEE A B COURSE

Employee ID Completes Course_ID

Employee_Name Course_Title
 Associative Entity
The presence of one or more attributes on a
relationship suggest to the designer that the
relationship should be represented as entity type
This type of entity known as Associative Entity
 Date_Completed

A B
EMPLOYEE COURSE
Employee ID Course_ID
Employee_Name Completes Course_Title

EMPLOYEE A Certificate B COURSE

Employee ID Certificate_No Course_ID
Employee_Name Date_Completed B
Course_Title
Associative Entity
Associative entities sometimes referred as gerunds.
The relationship name(verb) usually converted into
entity name that is noun(or -ing form of the verb)
There is no relationship name on ERD.
Associative Entity in
Visio
When to make Associative
Entity
We will convert a relationship into Associative Entity if
following situation exists:
1. Relationship cardinality is many-to-many.
2. Associative entity has independent meaning for
end user.
3. The associative entity involve in one or more
relationships independent to the entities related to
the associative entities.
4. The relationship has one or more attributes
including identifier.
Exercise 1
• Prepare an ERD reflecting below Data
• Assume that each course is taught by one instructor
Exercise 1