Transcript ENTITY-RELATIONALSHIP(E

Database Management Systems
(DBMS)
 www.desiamore.com/ifm
B. Computer Science and BSc IT
Year 1
DeSiamore
Powered by DeSiaMore
1
ENTITY-RELATIONALSHIP(E-R)
MODELING
 Outline …
 E-R Model
 Components of an E-R Model
 ERD Conventions
 Relationships
DeSiamore
Powered by DeSiaMore
2
ENTITY-RELATIONALSHIP(E-R)
MODELING
 E-R Model
 The Entity-Relationship (E-R) model is a
high-level conceptual data model
developed to facilitate database design
by (Chen, 1976)
 A conceptual model is a set of concepts
that describe
 The structure of a database
 The associated retrieval and
 Update transactions on the database
DeSiamore
Powered by DeSiaMore
3
E-R Model…
 The main purpose of developing a
high-level data model is
 To support a user’s perception of the
data and
 To conceal the more technical aspects
associated with database design.
 A conceptual data model is
independent of the particular DBMS
and hardware platform that is used to
implement the database
DeSiamore
Powered by DeSiaMore
4
E-R Model…
 The basic concepts of the E-R model
include..
 Entity types
 Relationship types and
 Attributes
DeSiamore
Powered by DeSiaMore
5
E-R Model…
 Entity type
An object or concept that is identified by the
organisation as having an independent existence
 Represents a set of objects in the real world
with the same properties
 Has an independent existence and can be an
object with a physical (real) existence as
employee, student, book, manager, customer,
etc and an object with a conceptual (abstract)
existence such as sale, inspection, satisfaction,
and so on…..

DeSiamore
Powered by DeSiaMore
6
Components of an E-R Model
 The E-R model forms the basis of the
Entity Relationship Diagrams (ERDs)
 The ERD represents the conceptual
view of the database
 The ERDs represent three main
components
 entities
 Attributes
 relationships
DeSiamore
Powered by DeSiaMore
7
Components of an E-R Model…
 Entity
 Is the fundamental item in any data
model
 Is an instance of an entity type that is
uniquely identifiable
 Each uniquely identifiable instance of an
entity type is also referred to as entity
occurrence or entity instance
DeSiamore
Powered by DeSiaMore
8
Components of an E-R Model…
 Attribute
 An attribute is a single atomic of information
that describes something about its named entity
 Example
BOOK (ISBN, Title, Author, Publisher,
Publication_year, price)
STUDENT (Enrolled_No, Name, programme,
stream, Age, sex, etc )
These representations are termed as the logical
description of the entity since each one does
not include the machine format for the entity
and its attribute
DeSiamore
Powered by DeSiaMore
9
Components of an E-R Model…
 Attribute…
 Each Attribute is associated with a set of values
called domain
 The domain defines the potential values that
an attribute may hold,
 Example, if the age of the student in the class is
between 14 and 17, then we can define a set of
values for the age attribute of the student entity
as the set of integers between 14 and 17
 A fully developed data model includes domains
for each attribute in the E-R model
DeSiamore
Powered by DeSiaMore
10
Components of an E-R Model…
 Attributes can be classified as
following





DeSiamore
Simple
Composite
Single-valued
Multi-valued
Derived
Powered by DeSiaMore
11
Components of an E-R Model…
 Simple Attribute
 A simple attribute is an attribute
composed of a single component with an
independent existence, cannot be further
subdivided
 Examples of simple attributes include;
sex, age, salary etc.
 Simple attributes are sometimes called
atomic attribute
DeSiamore
Powered by DeSiaMore
12
Components of an E-R Model…
 Composite Attribute
 Is an attribute composed of multiple
components, each with an independent
existence.
 Some attributes can be further divided to
yield smaller components with an
independent existence of their own
 Example, the address attribute can be
composed of components like street
number, Area, City, Pin_code, and so on
DeSiamore
Powered by DeSiaMore
13
Components of an E-R Model…
 Composite Attribute..
StreetNo
City
………..
Pin_Code
Address
DeSiamore
Powered by DeSiaMore
14
Components of an E-R Model…
 Single-Valued Attribute
 is one that holds a single value for a
single entity
 Example, the classroom entity has a
single value for the Room_Number and
therefore the Room_Number attribute is
referred to as being single-valued
DeSiamore
Powered by DeSiaMore
15
Components of an E-R Model…
 Multi-valued Attribute
 Is one that holds multiple values for a
single entity.
 Example, a student can have multiple
values for the Hobby attribute –reading,
music, movies, and so on.
DeSiamore
Powered by DeSiaMore
16
Components of an E-R Model…
 Derived Attribute
 A derived attribute is one that
represents a value that is derivable from
the value of a related attribute or set of
attributes , not necessarily in the same
entity
 Example, age can be derived from the
date-of-birth attribute and therefore they
are related
DeSiamore
Powered by DeSiaMore
17
E-R Diagram Conventions
 There are conventions for representing
entities and attributes
 These conventions are;
 The entity are represented by a rectangular
box with the name of the entity in the box
 An attribute is shown as an ellipse attached to
a relevant entity by a line and labelled with the
attribute name
 The entity name is written in uppercase where
as the attribute name is written in lowercase
 The primary key (key attributes) are
underline
DeSiamore
Powered by DeSiaMore
18
E-R Diagram Conventions….
 Conventions…
 The attributes are connected using lines to the
entities. If the attribute is simple or single
valued a single line is used
 If the attributes is derived a dotted line is
used
 If it is multi-valued then double line are used
 If the attribute is composite, its component
attributes are shown as ellipse emanating from
the composite attribute
see an example below
DeSiamore
Powered by DeSiaMore
19
E-R Diagram Conventions….
Fax
City
TellNo.
Street
Pin_Code
State
Address
Reg.No
Hobby
Age
STUDENT
Class
DoB
Name
FirstName
LastName
Figure, ERD for the Student entity
DeSiamore
Powered by DeSiaMore
20
Relationships
 A relationship is an association between
entities.
 Each relationship is identified so that its
name is descriptive of the relationships
 Relationships are represented by diamond
shaped symbols with the relationship name
inside the diamond.
 The two sides of the diamond are
connected to the entities it relates
DeSiamore
Powered by DeSiaMore
21
Relationships….
 Example, the figure below shows the
relationship between the entities
PUBLISHER and BOOK
PUBLISHER
publishes
BOOK
PUBLISHER-BOOK Relationship
 A binary relationship exists when two
entities are associated
DeSiamore
Powered by DeSiaMore
22
Relationships….
 Terms associated with entities and
relationships





DeSiamore
Degree
Cardinality
Connectivity
Dependency
Participation
Powered by DeSiaMore
23
Relationships….
 Degree
 The degree of a relationship indicates the
number of associated entities.
 A unary relationship exists when an association
is maintained within a single entity
 Example, consider the entity SUBJECT, consider
a situation where to take a particular subject i.e,
English, Maths etc
 This kind of a relationship is called a unary
relationship as represented below
DeSiamore
Powered by DeSiaMore
24
Relationships….
SUBJECT
Requires
Figure, Unary Relationship
DeSiamore
Powered by DeSiaMore
25
Relationships….
 Ternary Relationship
 a ternary relationship exists when there
are three entities associated,
 Example, the entities TEACHER,
SUBJECT and STUDENT are related using
a ternary relationship called ‘teaches’ as
shown below
DeSiamore
Powered by DeSiaMore
26
Relationships….
 Ternary Relationship…..
TEACHER
SUBJECT
Teaches
STUDENT
Figure, A Ternary Relationship
DeSiamore
Powered by DeSiaMore
27
Relationships….
 A quaternary relationship exists
when there are four entities
associated,
 Example, of a quaternary relationship
is ‘studies’ where four entities are
involved, STUDENT, TEACHER,
COURSE_MATERIAL, and SUBJECT as
shown below
DeSiamore
Powered by DeSiaMore
28
Relationships….
TEACHER
STUDENT
Studies
COURSE_MATERIAL
SUBJECT
Figure, A Quaternary Relationship
DeSiamore
Powered by DeSiaMore
29
Connectivity
 Relationships can be classified as
 One-to-One
 One-to-Many and
 Many-to-Many
 The term Connectivity is used to describe
this relationship classification
 E-R indicates the relationship’s connectivity
by placing a 1, M or N near the related
entities as shown below
DeSiamore
Powered by DeSiaMore
30
Connectivity…..
 Example, the relationship are explained as
follows;
 A MANAGER in the company manages a single
DEPARTMENT (A One-to-One connectivity)
 A DEPARTMENT can have more than one
EMPLOYEE (a One-to-Many connectivity)
 The training department of the company offers
many courses to the employees as a part of the
employee development program. Each
EMPLOYEE can join for more than one COURSE
(a many-to-many connectivity)
DeSiamore
Powered by DeSiaMore
31
Connectivity…..
 (a) A One-to-One Relationship
Figure, One-to-One Relationship
PUBLISHER
MANAGER 1
DeSiamore
publishes
Manages
Powered by DeSiaMore
1
DEPARTMENT
BOOK
32
Connectivity…..
 (b) a One-to-Many Relationship
Figure, One-to-Many Relationship
DEPARTMENT
PUBLISHER 1
DeSiamore
publishes
Manages
Has
Powered by DeSiaMore
N
EMPLOYEE
BOOK
33
Connectivity…..
 (c) A Many-to-Many Relationship
Figure, Many-to-Many Relationship
PUBLISHER
EMPLOYEE N
DeSiamore
publishes
Manages
Joins
Powered by DeSiaMore
N
COURSE
BOOK
34
Cardinality
 Cardinality expresses the specific
number of entity occurrences
associated with one occurrence of the
related entity
 The actual number of associated
entities is governed by the business
rules
DeSiamore
Powered by DeSiaMore
35
Cardinality…..
 Example, cardinality in Relationships
Figure, One-to-Many Relationship
DEPARTMENT
PUBLISHER 1
(0,100)
publishes
Manages
Has
N
(1,1)
EMPLOYEE
BOOK
 One department can have a
maximum of 100 employees
DeSiamore
Powered by DeSiaMore
36
Cardinality…..
 Example…cardinality in Relationships
Figure, Many-to-Many Relationship
EMPLOYEE
PUBLISHER N
(0,2)
publishes
Manages
Joins
N
(0,10)
COURSE
BOOK
 Each employee is allowed to join a
maximum of 2 courses at a time whilst a
company offers 10 courses
DeSiamore
Powered by DeSiaMore
37
Dependency
 Entities are classified as being strong or weak entity
types
 An entity type that is existence-dependent on some
other entity is called a weak entity type or
existence-dependent and
 An entity type that is not existence-dependent on
some other entity type is called a strong entity type
 A weak entity type is dependent on the existence of
another entity
 Weak entities are also referred to as child,
dependent or subordinate entities and strong
entities as parent, owner or dominant entities
DeSiamore
Powered by DeSiaMore
38
Dependency…..
 Example, in the following relationship
PARENT is a weak entity as it needs
the entity EMPLOYEE for its existence
 The entities EMPLOYEE , COMPANY
etc, are strong entities.
 Refer the figure shown below
DeSiamore
Powered by DeSiaMore
39
Dependency…..
 Example…
Figure, Weak and Strong Entities
COMPANY
PUBLISHER
PARENT
DeSiamore
publishes
Manages
Employs
EMPLOYEE
BOOK
Has
Powered by DeSiaMore
40
Participation
 There are two ways an entity can participate
in a relationship as totally or partially.
 The participation is also known as mandatory
or optional.
 The participation is total (mandatory) if an
entity’s existence requires the existence of an
associated entity in a particular relationship
 The participation is said to be optional or
partial, if the occurrence of one entity does
not require the occurrence of another
corresponding entity in a relationship
DeSiamore
Powered by DeSiaMore
41
Participation…..
 Example, in the relationship “EMPLOYEE
joins COURSE” , it is quite possible for an
EMPLOYEE not to take a COURSE, therefore
COURSE is optional to the EMPLOYEE,
 But a COURSE must be attended by an
EMPLOYEE which makes the EMPLOYEE a
mandatory entity
 An optional entity is shown by drawing a
small circle (O) on the side of the optional
entity as shown below
DeSiamore
Powered by DeSiaMore
42
Participation…..
Figure, Optional Entities
EMPLOYEE N
(0,2)
DeSiamore
Joins
Powered by DeSiaMore
N
(0,10)
COURSE
43
E-R Model..
 Example of one-to-one Relationship
between the entities EMPLOYEE and
CONSULTANT.
 Here consultant is a special type of the
entity EMPLOYEE –an ISA relationship
 Similarly, there are one-to-many and
many-to-many relationships
DeSiamore
Powered by DeSiaMore
44
E-R Model..
 Example…
Age
Sex
Name
Emp_No.
EMPLOYEE
Salary
ISA
Emp_No.
CONSULTANT
Designation
Client
Figure, One-to-One Relationship
DeSiamore
Powered by DeSiaMore
45
E-R Model..
 Example, the relationship between
EMPLOYEE and DEPARTMENT is oneto-many relationship (assuming that
an employee works for only one
department and a department can
have more than one employee), as
shown below
DeSiamore
Powered by DeSiaMore
46
E-R Model..
Age
Sex
Name
Emp_No.
EMPLOYEE
Dept_id.
DEPARTMENT
Salary
Name
Figure, One-to-Many Relationship
DeSiamore
Powered by DeSiaMore
47
E-R Model..
 Example of the many-to-many
relationship is the one between
AUTHOR and PUBLISHER.
 An author can have more than one
publisher and a publisher will be
publishing books of more than one
author, as shown below
DeSiamore
Powered by DeSiaMore
48
E-R Model..
Age
Sex
Name
Address
Name
AUTHOR
Phone_No.
PUBLISHER
Editor
Address
Figure, Many-to-Many Relationship
DeSiamore
Powered by DeSiaMore
49
E-R Model..
 Example, consider a relationship
between the EMPLOYEE and the
CONSULTANT entities, CONSULTANT
entity cannot exist without the
EMPLOYEE, the EMPLOYEE can exist
without the CONSULTANT
 See the figure below
DeSiamore
Powered by DeSiaMore
50
E-R Model..
Age
Sex
Emp_No.
Name
EMPLOYEE
Salary
Mandatory
Optional
Emp_No.
CONSULTANT
Designation
Client
Representation of Employee-Consultant Relationship
DeSiamore
Powered by DeSiaMore
51
E-R Model..
 Now if we make up the entity lists of
these entities can be written as
EMPLOYEE (Emp_No., Name, Age, Sex, Salary )
CONSULTANT (Emp_No., Client, Designation)
DEPARTMENT (Dept_id.,name)
AUTHOR (Name, Age, Sex, Address, Phone_No.)
PUBLISHER (Name, Address, Editor)
DeSiamore
Powered by DeSiaMore
52
Review
Any Questions
DeSiamore
Powered by DeSiaMore
53