ENTITY-RELATIONALSHIP(E
Download
Report
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