Relationships of Degree 2017-18
Types of relationships
Entity
An Entity is a object or thing with a physical existence like person, house, employee.
It is represented by rectangular symbol
Student
Entity Type
Its describes the classification of one or more entites having set of attributes.
Attributes
The property that describes an entity is caused as an attribute.It is represented by oval Symbol
Relationship and Relationship Set :
Relationships connect the entities and represent meaningful dependencies between them.It
represents an association among several entities. Relationships sets is a set of relationships
of the same type. It is a mathematical relation on entity sets (n>=2). Relationship set R is a
subset of -
{(r1,r2,r3,....rn)| r1∈E1, r2∈E2, rn∈En}
pg. 1 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
where r1,r2,....rn are called relationships and E1,E2,....En are entity sets. The way in which
two or more entity types are related is called relation type. For example, consider a
relationship type WORKS_FOR between the two entity types EMPLOYEE and
DEPARTMENT, which associates or links each employee with the department the employee
works for. The WORKS_FOR relation type is shown as –
Notation to Represent Relation Type in ER Diagram-
Relation types are represented as diamond shaped boxes.
Degree of a Relationship Type-
The number of participating entity types is known as the degree of relationship type.
Different types of relationships in ER diagram are –
1) Relationships based on degree,
2) Recursive Relationship.
Let us discuss these one by one.
Types of Relationship Type Based on Degree -
Binary Relationship - A relationship type of degree two is called binary relationship.
The WORKS_FOR in above figure is a binary relationship as there are two
participating entities-employee and department.
Ternary Relationship- A relationship type of degree three is a ternary relationship for
example, in the below figure supply relationship connects three entities SUPPLIER,
pg. 2 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
PART AND PROJECT.
The above
diagram can be read as - a supplier supplies the parts to projects
N-ary Relationship Set - A relationship type of degree n is called n ary relationship .
For example
Role Names-
A relationship type has a name which
signifies what role a participating entity
plays in that relationship instance. The
role names helps to explain what the
relationship means. In the first example
WORKS_FOR relationship type,
employee plays the role of worker and
department plays the role of
employee(because a department consists
of a number of employees.
Recursive Relationship
If the same entity type participate more
than once in a relationship type in different roles then such relationship types are called
recursive relationship. For example, in the below figure REPORTS_TO is a recursive
relationship as the Employee entity type plays two roles - 1) Supervisor and 2) Subordinate.
You can also define the above example of recursive relationship as the relationship between
a manager and an employee. An employee is a
manager as well as employee.
This is commonly know as a 'pig's ear'.
pg. 3 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
To implement recursive relationship, a foreign key of the employee's manager number would
be held in each employee record.
Emp_entity( Emp_no,Emp_Fname, Emp_Lname, Emp_DOB, Emp_NI_Number, Manager_no);
Manager no - (this is the employee no of the employee's manager)
Relationship Constraints in DBMS –
There are three Types of Relationship Constraints-
1. Structural Constraints
o Participation Constraints
o Cardinality Ratio
2. Overlap Constraints
3. Covering Constraints
Structural Constraints are applicable for binary relationships and
Overlap and Covering Constraints are applicable for EERD(Extended ER Diagrams).
Participation (or) Optionality Constraints-
Participation concerns with the involvement of entities in a relationship. It specifies whether
the existence of an entity depends on another entity. There are two types of Participation
Constraints –
1. Total/Mandatory Participation
2. Partial/Optional Participation
Notations of Different Types of Participation In ER Diagram –
Total/Mandatory Participation or Existence Dependency-
Participation is said to be total if every entity in E participates in atleast one relationship in R
(or) every entity in entity set must depend on another entity. For example, every department
will have a startup date as a department was created on that startup date (SINCE
ATTRIBUTE of MANAGE relationship). and that department is being handled from that
date through a Manager. So, the participation of DEPARTMENT entity in the “MANAGE”
relationship type is total.Total Participation is also known as Existence Dependency.
In ER Diagram, it is represented as a Double Line, connecting the participating entity to the
relationship.
pg. 4 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
Partial/Optional Participation-
Participation is said to be partial if only some entities in E participate in relationships in R
(or) some entities in entity set are depend on some another entities in entity set. For example,
It is not necessary that all employees manage some department Because all employees may
not be the Manager . So the participation of “EMPLOYEE” entity in the “MANAGES”
relationship type is partial.
Cardinality/Mapping Cardinality-
Cardinality expresses the number of entities to which another entity can be associated via a
relationship set (or)
It specifies the number of relationship instances that an entity can participate in a relation set.
There are 4 types of Cardinality Ratios :
One-to-One Cardinality (1:1)
One-to-Many Cardinality (1:M)
Many-to-One Cardinality (M:1)
Many-to-Many Cardinality (M:N)
pg. 5 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
Notations of Different Types of Cardinality In ER Diagram –
One-to-One Cardinality (1:1) –
An entity in set A is associated with atmost one entity in B, and an entity in B is associated
with atmost one entity in A. This type of cardinality is referred as one to one Cardinality.
For example, an Employee as a Manager manage only one Department and the vice versa is
also true as a department have only one Manager
pg. 6 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
One to Many Cardinality (1:M) –
An entity in A is associated with any number (0 or more) with an entity B, but a entity in B,
however can be associated with atmost one entity in A. For example, An employee as a
Manager can manage more than one Department.
Many to One Cardinality (M:1) –
An entity in A is associated with atmost one entity in B. An entity in B, however, can be
associated with any number (0 or more) of entities in A. For example, An Employee can
pg. 7 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
work only for one Department, But each Department can have 0 or more employees.
Many to Many Cardinality (M:N) –
An entity in A is associated with any number (0 or more) of entities in B, and an entity in B is
associated with any number( 0 or more) of entities in A. For example, An Employee can
works on several Projects and a Project may have several Employees.
pg. 8 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
Identifying and Non Identifying Relationships –
Relationships among the entities may be classified as being either
Identifying Relationships
Non Identifying Relationships
Identifying Relationships –
Definition 1 :In the previous post (Entity and its Types) we define about weak entity types.
These are the entities, which does not have a key attribute. So, To identify it, what we do is to
relate such type of entity with some other entity type.
The weak entity type relate to another entity type in combination with some of their attribute
values.We call this entity type the identifying or Owner Entity Type and the relationship type
which a weak entity type creates with Owner Entity Type is identifying relationship of weak
entity type.
Example :
Create table authored book (
author_id int not null,
book_id int not null,
primary key (author_id, book_id),
foreign key (author_id) references authors(author_id),
foreign key (book_id) references books(book_id));
author_id is a foreign key, but it’s also one of the columns in the primary key. So there is an
identifying relationship of the AuthoredBook with the referenced table Authors.
Likewise it has an identifying relationship with Books as author_id is a foreign key, but it’s
also one of the columns in the primary key. So there is an identifying relationship of this table
with the referenced table Books.
Non Identifying Relationships-
Definition 1:A non-identifying relationship is when the primary key attributes of the parent
entity must not become primary key attributes of the child entity.So, put the primary key
column of the parent into the table of the child, but not in the primary key.
Non Identifying Relationships can be further classified into –
Mandatory Non Identifying Relationship- It exists when the foreign key value in the child
table cannot be null or where a parent is required by setting the parent table cardinality.
Optional (or) Non Mandatory Non Identifying Relationship – An optional (or) non-
mandatory non-identifying relationship exists when the value in the child table can be null or
where a parent is not required.
Example of Non-Identifying Relationship –
Account( AccountID, AccountNum, AccountTypeID )
AccountType( AccountTypeID, Code, Name, Description )
pg. 9 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
The relationship between the Account and AccountType is non-identifying
because each AccountType can be identified without having to exist in the
parent table.
Notation of Identifying Relationship –
In the below figure, both a weak entity types and its identifying relationship are
distinguished by surrounding their boxes and diamonds with double lines as shown in the
figure.
Partial key attributes(e.g. Name) is underlined with a dashed or dotted line.
pg. 10 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
Objective Types:
1) What is Relationship type?
a. One Entity
b. Two or More Entity
c. Only Two Entity
d. None of the above
2) What is the example for Relationship Type?
a. Department
b. Employee
c.Works_for
d. None of the above
3) Types of Relationship based on Degree?
a. binary
b. ternary
c.both a & b
d. None of the above
4) Types of Cardinality
a. one-to-one (1:1)
b. one-to-many (1:M)
c. many-to-one (M:1) & many-to-many (M:N)
pg. 11 Dept. Of Computer science Engg.,
DSIT
Relationships of Degree 2017-18
d. All of above
5) Examples for one-to-one Cardinality
a. emp -------- manage ----------- dept
b. emp --------- works_on --------- dept
c. both a & b
d. None of the above
6) Notation to represent a Relationship?
a.
b.
c. both a & b
d.
7) Notation for Identifying Relationship
a.
b.
c.
d. None of the above
pg. 12 Dept. Of Computer science Engg.,
DSIT
