Transcript Ch no 02 ERD

By ILTAF MEHDI (MCS, MCSE, CCNA)
Distribution of Marks
For
Second Semester
External
Internal Sessional Evaluation
Evaluation
Assignment
/Project
Quizzes
Class
Attendance
Mid-Term
Test
Total
Sessional
Terminal
Exam
Final
Evaluation
10
10
5
25
50
50
100
Remember
1
By ILTAF MEHDI (MCS, MCSE, CCNA)
Course Contents
Chapter No
Chapter Name
1.
Database Foundation OR An Overview of Database
Management System (DBMS)
2.
Semantic Modeling OR Entity Relationship Model (ERD)
3.
An introduction to Relational Database
4.
Normalization
5.
Relational Algebra
6.
An Introduction to Structured Query Language (SQL)
Page No
2
By ILTAF MEHDI (MCS, MCSE, CCNA)
DATABASE-1
Chapter No: 02
Chapter No: 02
COURSE TITLE:
COURSE INSTRUCTOR:
ILTAF MEHDI
3
By ILTAF MEHDI (MCS, MCSE, CCNA)
ENTITY RELATIONAL (E-R) MODEL
E-R Model:
 “An entity Relationship Model is a logical
representation of data for an organization or for
a business area”.
 The E-R Model is expressed in terms of entities
in the business environment, the relationships
or associations among entities and the
attributes of both the entities and relationships.
4
By ILTAF MEHDI (MCS, MCSE, CCNA)
ENTITY RELATIONSHIP DIAGRAM
E-R Diagram:
 An E-R model normally expressed as an entity
relationship diagram. Where “the E-R Diagram
is a graphical representation of an entity
relationship model”.

5
By ILTAF MEHDI (MCS, MCSE, CCNA)
E-R MODEL NOTATIONS
There is no industry standard notation for the
E-R model. But some basic notations combine
most of the desirable features of the different
notations that are commonly used.
 The basic notations used for E-R model are
given on the next slide.

6
By ILTAF MEHDI (MCS, MCSE, CCNA)
E-R MODEL NOTATIONS
Strong Entity
Weak Entity
Relationship
Identifying Relationship
Associative Entity
Attribute
Multi-Valued Attribute
Derived Attribute
7
By ILTAF MEHDI (MCS, MCSE, CCNA)
E-R MODEL CONSTRUCTS

The basic constructs of the E-R model are:
1) Entities
2) Attributes
3) Relationships or Associations
8
By ILTAF MEHDI (MCS, MCSE, CCNA)
E-R MODEL CONSTRUCTS
1. Entity:
“An entity is a person, place, object, event or concept in
the user environment about which the organization
whishes to maintain data”.
 Some examples of each of these types of entities are
given below:
PERSON: Employee, Student, Patient etc
PLACE: City, State, Country etc
OBJECT: Machine, Building, Automobile etc
EVENT: Sale, Registration, Renewal etc
CONCEPT: Account, Course, Work Center etc

9
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ENTITY
Following are the major types of the entity:
a) Entity Type
b) Entity Instance
c) Strong Entity Type
d) Weak Entity Type
10
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ENTITY
Entity Type:
 “A collection of entities that share common properties
or characteristics is called entity type”.
 Entity type is also called Entity Class.
2. Entity Instance:
 “The single occurrence of an entity type is called entity
instance”.
 An entity Type is described just once in a database,
while many instances of that entity type may be
represented by data stored in the database.
For example:
 An entity Type “Employee” has many entity instances in
organization.
1.
11
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ENTITY
3. Strong Entity Type:
 “An entity that exists independently of other
entity types is called Strong Entity Type”.
4. Weak Entity Type:
 “An entity Type whose existence depends on
some other entity type is called Weak Entity
Type”.
12
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ENTITY
Identifying Relationship:
 “The relationship between weak entity type and
its owner is called Identifying Relationship”.
6. Identifying Owner:
 “The entity type on which the weak entity type
depends is called Identifying Owner”.
5.
13
By ILTAF MEHDI (MCS, MCSE, CCNA)
Attributes
Emp-Id
Emp-Name
EMPLOYEE
STRONG ENTITY
OR
IDENTIFYING OWNER
DependentName
Has
IDENTIFYING
RELATIONSHP
Date-Of-Birth
DEPENDENT
WEAK ENTITY
14
By ILTAF MEHDI (MCS, MCSE, CCNA)
E-R MODEL CONSTRUCTS
2. Attribute:
 “A
property or characteristics of an entity type
that is of an interest to the organization is
called attribute”.
 Following are some entity types and their
attributes, (shown by shorthand notation):



STUDENT (st-Id, st-Name, st-F/Name, st-Address etc)
EMPLOYEE (Emp-id, Emp-Name, Emp-Skill etc)
AUTOMOBILE (Vechile-id, Vechile-Color, Vechile-Weight etc )
15
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ATTRIBUTES
Following are the major types of attribute:
1) Simple Attribute
2) Composite Attribute
3) Multi-Valued Attribute
4) Derived Attribute
5) Identifier
6) Composite Identifier
16
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ATTRIBUTES
Simple Attribute:
 “An attribute that can’t be broken down into smaller
components is called Simple Attribute”.
For example:
All Attributes associated with AUTOMOBILE are Simple
Attributes.
2. Composite Attribute:
 “An attribute that can be broken down into components
is called Composite Attribute”.
For example:
“Address” attribute is a Composite Attribute which can be
broken down into Street-Add, City-Add, State-Add and
Postal-Code.
1.
17
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ATTRIBUTES
3. Multi-Valued Attribute:
 “An attribute that may take on more than one
value for a given entity instance is known as MultiValued Attribute”.
 Multi-Valued Attribute is indicated by “double
ellipse” in an E-R Diagram.
4. Derived Attribute:
 “An Attribute whose values can be calculated from
related attribute value is known as Derived
Attribute”.
 Derived Attribute is indicated by “ellipse with
dashed line” in an E-R Diagram.
18
By ILTAF MEHDI (MCS, MCSE, CCNA)
Identifier
Emp-Id
Composite Attribute
Emp-Name
Derived Attribute
Year-Employed
Emp-Address
Multi-Valued Attribute
EMPLOYEE
Skill
Date-Employed
19
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ATTRIBUTES
5. Identifier:
 “An Attribute that uniquely identifies individual
instances of an entity type is called Identifier or
Key”.
 Identifier name or key is underlined in E-R
diagram.
6. Composite Identifier:
 “An identifier that consists of a composite attribute
is known as Composite Identifier or key”.
 Composite Identifier or key is underlined while the
components are not underlined in E-R diagram.
SEE THE EXAMPLES ON NEXT SLIDE
20
By ILTAF MEHDI (MCS, MCSE, CCNA)
st-Id
st-Name
Other attributes
STUDENT
In the above E-R diagram, the Identifier for STUDENT entity type is “st-Id” (Student-ID).
Flight-No
Date
Flight-Id
No-of-passengers
FLIGHT
In the above E-R diagram, the composite Identifier for FLIGHT entity type is “Flight-Id” .
21
By ILTAF MEHDI (MCS, MCSE, CCNA)
E-R MODEL CONSTRUCTS
3. Relationship:
 Relationship is also called association.
 Def:“An association is a logical relation between data items”.
OR
 “the meaningful connection between data items is called relationship
or association ”.
OR
 “An association is a relationship between the instances of one or
more entity types that is of an interest to the organization”.
 Diamond symbol is used to show the relationship between entities.
For example:
student
has
Reg-No
Relationship
22
By ILTAF MEHDI (MCS, MCSE, CCNA)
DEGREE OF ASSOCIATION OR RELATIONSHIP
Def: “The number of entity types that
participate in a relationship is known as Degree
of association or relationship”.
 Associations may be:
i.
Unary (Degree 1)
ii. Binary (Degree 2)
iii. Ternary (Degree 3)

23
By ILTAF MEHDI (MCS, MCSE, CCNA)
RELATIONSHIP DEGREE
b. Binary Relationship
a. Unary Relationship
c. Ternary Relationship
24
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF ASSOCIATION AND REVERSE ASSOCIATION
There are three main types of association and
reverse association. Which are given below:
1) One to One Relationship
2) One to Many Relationship (or Many to One)
3) Many to Many Relationship

25
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF RELATIONSHIP
1. One to One Relationship:
 “When one occurrence of entity A can relate to one and only one
occurrence of entity B and reversely the occurrence of entity B can
relate to one and only one occurrence of entity A is called one to one
relationship”.


One to One Relationship can be represented by one-head
arrow.
For example: “Student has only one Reg-no” and reversely “On one
Reg-no there must be only one Student”.
A
Student
Reg-no
B
Similarly,“A Patient can have only one Bed” and reversely “On one Bed
there must be only one Patient”
A
Patient
Bed
B
26
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF RELATIONSHIP
2. One to Many Relationship (or Many to One):
 “When One occurrence of entity A can relate to
many occurrences of entity B then it is called One
to many relationship but reversely when an
occurrence of entity B can relate to only
occurrence of entity A then it is called Many to one
relationship”.
 One to Many Relationship can represented by
double-head arrow.
For Example: “A Mother can have many Childs” but
reversely “a Child can have only one Mother”.
A
Mother
Child
B
27
By ILTAF MEHDI (MCS, MCSE, CCNA)
TYPES OF RELATIONSHIP
3. Many to Many Relationship:
 “An occurrence of entity A can relate to more
occurrences of entity B and reversely an occurrence of
entity B can relate to more occurrences of entity A is
called Many to Many Relationship”.
 For example: “A Student can have many teachers” and
reversely “A Teacher can have many students”.
A
Student
Teacher
B
Similarly, “A Student can take many courses” and reversely
“A Course can be taken by many students”.
A
Student
Course
B
28
By ILTAF MEHDI (MCS, MCSE, CCNA)
RELATIONSHIP CARDINALITY
Mandatory One
Mandatory Many
Optional One
Optional Many
29