Bule Hora University
College of Informatics
Department of Computer Science
: Fundamental of Database
CHAPTER 4:
ENTITY – RELATIONSHIP (E/R) DATA MODEL
Biniyam A.
USING HIGH-LEVEL CONCEPTUAL DATA
MODELS FOR DATABASE DESIGN
Figure 4.1 A simplified diagram to illustrate the main phases of database design.
2
USING HIGH-LEVEL CONCEPTUAL DATA
MODELS FOR DATABASE
DESIGN..CONT’D
Figure 4.1 shows a simplified overview of the database design process.
The first step shown is requirements collection and analysis.
During this step, the database designers interview prospective database
users to understand and document their data requirements.
The result of this step is a concisely written set of users’
requirements.
These requirements should be specified in as detailed and complete a
form as possible.
In parallel with specifying the data requirements, it is useful to specify
the known functional requirements of the application
3
USING HIGH-LEVEL CONCEPTUAL DATA
MODELS FOR DATABASE
DESIGN..CONT’D
These consist of the user defined operations (or transactions) that will
be applied to the database, including both retrievals and updates.
Once the requirements have been collected and analyzed, the next step
is to create a conceptual schema for the database, using a high-level
conceptual data model.
This step is called conceptual design.
The conceptual schema is a concise description of the data
requirements of the users and includes detailed descriptions of the
entity types, relationships, and constraints; these are expressed using
the concepts provided by the high-level data model.
Because these concepts do not include implementation details, they are
usually easier to understand and can be used to communicate with
nontechnical users. 4
USING HIGH-LEVEL CONCEPTUAL DATA
MODELS FOR DATABASE
DESIGN..CONT’D
•The high-level conceptual schema can also be used as a
reference to ensure that all users’ data requirements are met
and that the requirements do not conflict.
•This approach enables database designers to concentrate
on specifying the properties of the data, without being
concerned with storage and implementation details.
•This makes it is easier to create a good conceptual database
design.
5
USING HIGH-LEVEL CONCEPTUAL
DATA MODELS FOR DATABASE
DESIGN..CONT’D
This also serves to confirm that the conceptual schema meets
all the identified functional requirements.
Modifications to the conceptual schema can be introduced if
some functional requirements cannot be specified using the
initial schema.
The next step in database design is the actual implementation
of the database, using a commercial DBMS.
Most current commercial DBMSs use an implementation
data model-such as the relational or the object-relational
database model-so the conceptual schema is transformed
from the high-level data model into the implementation data6
model.
USING HIGH-LEVEL CONCEPTUAL
DATA MODELS FOR DATABASE
DESIGN..CONT’D
This step is called logical design or data model mapping.
The last step is the physical design phase, during which the
internal storage structures, file organizations, indexes, access
paths, and physical design parameters for the database files
are specified.
7
THE ENTITY RELATIONSHIP (E-R) MODEL
It is a model that provides a high-level description of a conceptual
data model.
Data modeling provides a graphical notation for representing such
data models in the form of entity-relationship diagrams (ERD).
The whole purpose of ER modeling is to create an accurate reflection
of the real world in a database.
The entity relationship (ER) data model allows us to describe the
data involved in a real world enterprise in terms of objects called
entities and their relationships
An ER Diagram is a visual representation of different data using
conventions that describe how these data are related to each other.
8
Cont.
The main components of ER Modeling are:
o Entities
Corresponds to entire table, not row
Represented by Rectangle
o Attributes
Represents the property used to describe an entity or a
relationship
Represented by Oval
o Relationships
Represents the association that exist between entities
Represented by Diamond
o Constraints
Represent the constraint in the data
9
DEVELOPING AN E-R DIAGRAM
Designing conceptual model for the database is not a one linear
process but an iterative activity where the design is refined again and
again.
To identify the entities, attributes, relationships, and constraints on the
data, there are different set of methods used during the analysis phase.
These include information gathered by…
Interviewing end users individually and in a group
Questionnaire survey
Direct observation
Examining different documents
Analysis of requirements gathered
Nouns -- prospective entities
Adjectives--prospective attributes
Verbs/verb phrases-prospective relationships
The basic E-R model is graphically depicted and presented for review. 10
GRAPHICAL REPRESENTATIONS IN ER DIAGRAMMING
11
CONT.
12
CONT..
13
Example 1: Build an ER Diagram for the following information:
A student record management system will have the following two basic data object
categories with their own features or properties: Students will have an Id, Name,
Dept, Age, GPA and Course will have an Id, Name, Credit Hours
Whenever a student enroll in a course in a specific Academic Year and
Semester, the Student will have a grade for the course
14
Cont..
Example 2: Build an ER Diagram for the following information:
A Personnel record management system will have the following two basic data object categories
with their own features or properties: Employee will have an Id, Name, DoB, Age, Tel and
Department will have an Id, Name, Location
Whenever an Employee is assigned in one Department, the duration of his stay in the
respective department should be registered.
STRUCTURAL CONSTRAINTS ON RELATIONSHIP
Constraints on Relationship / Multiplicity/ Cardinality Constraints
Multiplicity constraint is the number or range of possible
occurrence of an entity type/relation that may relate to a single
occurrence/tuple of an entity type/relation through a particular
relationship.
Mostly used to insure appropriate enterprise constraints.
One-to-one relationship:
A customer is associated with at most one loan via the relationship borrower
A loan is associated with at most one customer via borrower
Relational Constraints/Integrity Rules
E.g. Relationship Manages between STAFF and BRANCH
The multiplicity of the relationship is:
One branch can only have one manager
One employee could manage either one or no branches
RELATIONAL VIEWS
PURPOSE OF A VIEW
Cont.
THANK YOU!
21