School of Computing

Department of Information
Technology

1
Fundamentals of
Database System
Chapter – Three
Conceptual Database Design- E-R
Modelling

By: Biniam E.(Information Technology )

2
Chapter Outline

 Database Development Life Cycle

 Basic concepts of E-R model
 Problem with E-R models
 Enhanced E-R models

3
 Database Development Life Cycle
 Conceptual modeling is a very important phase in designing a successful
database application.
 a conceptual and representational model of data used to represent the
entity framework infrastructure.
 crucial to creating a good database design.
 used as a high-level logical data model, which is useful in developing a
conceptual design for databases.
• Steps involved in database Development Life cycle:
1. Identifying and defining the entities.
2. Determining all interactions between the entities.
3. Analyzing the nature of interactions/determining the cardinality of
the relationships.
4. Creating the ERD.
 The major part of the database application will require the design,
implementation, and testing of these application programs.
4
 Basic concepts of Entity-Relationship Model/ER Model

 Data model tells how the logical structure of a database is modeled.

 Data Models are fundamental entities to introduce abstraction in

DBMS.
 Data models define how data is connected to each other and how it
will be processed and stored inside the system.
 Entity-Relationship model is based on the notion of real world
entities and relationship among them.
 While formulating real-world scenario into database model,
 ER Model creates entity set, relationship set, general attributes and constraints.

 ER Model is best used for the conceptual design of database.

5
 ER Model Components
 The ER model describes data as entities, relationships, and attributes.

1. Entities :
 The basic object that the ER model represents is an entity
 Entities are specific objects or things in the mini-world that are
represented in the database.
 An entity may be an object with a physical existence.
 Example: person, employee, student or it may be an object
 All entities have some attributes or properties that give them their
identity.

6
 Cont’d…..

2. Attributes:
– Attributes are properties used to describe an entity.

– For example an EMPLOYEE entity may have a Name, SSN, Address, Sex,
BirthDate
– A specific entity will have a value for each of its attributes.
 Example: employee entity may have Name=‘Gelila ', SSN=‘inu001', Address

= Injibara', Sex='M', BirthDate='09-JAN-96‘

– Each attribute has a value set (or data type) associated with it –
 e.g. integer, string, ..

– For example, a student's name cannot be a numeric value.

– It has to be alphabetic. A student's age cannot be negative, etc

7
 Types of Attributes
Several types of attributes occur in the ER model:

1. Simple vs. Composite attributes

 Simple: not divided into sub parts

o e.g. Age, gender

 Composite: Divided into sub parts (composed of other attributes)

o e.g. Name, address

2. Single-valued vs. multi-valued attributes

 Single-valued: has only single value

o E.g. Name, sex, id. No., color_of_eyes (the value may change but has only one
value at one time)
 Multi-valued: takes more than one value

o E.g. Address, qualification

8
 Cont’d….

3. Stored vs. Derived Attribute

 Stored: not possible to derive or compute

o E.g. Name, Address

 Derived: The value may be derived (computed) from the values of other
attributes.
o E.g. Age (current year – year of birth)
o Length of employment (current date- start date)
o Due date (current date + 15 days)
o Profit (earning-cost)
o G.P.A (grade point/credit hours)
9
 Cont’d….
 Complex Attributes:
– In general, composite and multivalued attributes can be nested arbitrarily.

– We can represent arbitrary nesting by grouping components of a

composite attribute between parentheses () and
– separating the components with commas, and
– by displaying multivalued attributes between braces { }.
– Such attributes are called complex attributes.

– Both Phone and Address are themselves composite attributes.

10
ER-diagram Notation For ER Schemas

11
Cont’d…..

12
 Proper Naming of Schema Constructs

 When designing a database schema use;

 names for entity types, attributes, relationship types, and
(particularly) roles is not always straightforward.
 singular names for entity types, rather than plural ones, because the
entity type name applies to each individual entity belonging to that
entity type.
 entity type and relationship type names are uppercase letters,
attribute names have their initial letter capitalized, and role names
are lowercase letters

13
An entity type is represented in ER diagrams Example : CAR
 Rectangular box represent name of entity.
 Attribute names are enclosed in ovals and are attached to their entity type
by straight lines.
 Composite attributes are attached to their component attributes by straight
lines.
 Multivalued attributes are displayed in double ovals.
 Key attributes should be underlined with their name
 Derived attributes are represent by dashed oval shapes

Attrib
ute
Attribute Multivalued Derived
attributes Attributes Key composite
Attributes Attribute
Attrib
ute

14
 Relationship
 Relationship : The association among entities is called relationship.

 Relationships are represented by diamond-shaped box.

 Name of the relationship is written inside the diamond-box.

 All the entities (rectangles) participating in a relationship are connected to it by a line.

 For example, employee entity has relation works_for with department.
 student who enrolls in some course. Here, Works_at and Enrolls are called relationship.

15
 Relationship Types
 Degree of relationship: is the process of determining the number of
entity that associates with in a relationship.
 It may be:
 Unary relationship
 Binary relationship
 Tertiary relationship
 N-ry Relationship
 Cardinality defines the number of entities in one entity set which
can be associated to the number of entities of other set via
relationship set.
 List of Cardinality types
 One-to-one(1:1)
 One-to-many(1:N)
 Many-to-one(N:1)
 Many-to-many(M:N) 16
 One-to-One
 When only one instance of an entity is associated with the
relationship, it is marked as '1:1'.
 The following image reflects that only one instance of each entity
should be associated with the relationship.
 It depicts one-to-one relationship.

17
 One-to-Many:
 When more than one instance of an entity is associated with a relationship, it
is marked as '1:N'.
 The following image reflects that only one instance of entity on the left and
more than one instance of an entity on the right can be associated with the
relationship.
 It depicts one-to-many relationship.

18
 Many-to-One:
 When more than one instance of entity is associated with the relationship,
it is marked as 'N:1'.
 More than one instance of an entity on the left and only one
instance of an entity on the right can be associated with the
relationship.

19
 Many-to-Many

 The following image reflects that more than one instance of an

entity on the left and more than one instance of an entity
on the right can be associated with the relationship.
 It depicts many-to-many relationship.

21
 Participation Constraints

 Total Participation: Each entity is involved in the relationship.

– Total participation is represented by double lines.

 Partial participation: Not all entities are involved in the relationship.

23
 Entity-set and Keys

 Key is an attribute or collection of attributes that uniquely identifies an entity

among entity set.
 Example: roll_number of a student makes her/him identifiable among
students.
 Super Key: Set of attributes (one or more) that collectively identifies an entity
in an entity set.
 Candidate Key: Minimal super key is called candidate key that is, supers
keys for which no proper subset are a superkey.
 An entity set may have more than one candidate key.

 Primary Key: This is one of the candidate key chosen by the database
designer to uniquely identify the entity set. 24
 Weak Entity Types

 Weak entity is an entity that does not have a key attribute

 A weak entity must participate in an identifying relationship type with an

owner or identifying entity type
 Entities are identified by the combination of:

– A partial key which is the attribute that can uniquely identify weak
entities that are related to the same owner entity.
– The particular entity they are related to in the identifying entity type
 In ER diagrams, both a weak entity type and its identifying relationship are
distinguished by surrounding their boxes and diamonds with double lines .
 The partial key attribute is underlined with a dashed or dotted line.

25
 Entity-Relationship (ER) Model for COMPANY Database

 The COMPANY database keeps track of a company’s employees,

departments, and projects.
 The company is organized into departments.
 Each department has a unique name, a unique number, and a particular
employee who manages the department.
 We keep track of the start date when that employee began managing the
department.
 A department may have several locations.
 A department controls a number of projects, each of which has a unique
name, a unique number, and a single location.
 We store each employee’s name, Social Security number, address, salary,
sex (gender), and birth date.

26
 Cont’d…
 An employee is assigned to one department, but may work on several
projects, which are not necessarily controlled by the same department.
 We keep track of the current number of hours per week that an employee
works on each project.
 We also keep track of the direct supervisor of each employee (who is another
employee).
 We want to keep track of the dependents of each employee for insurance
purposes.
 We keep each dependent’s first name, sex, birth date, and relationship to the
employee.
 This database application can be displayed by means of the graphical notation
known as ER diagrams.
 We describe the step by-step process of deriving this schema from the stated
requirements and explain the ER diagrammatic notation as we introduce the ER
model concepts.

27
Refining the ER design for the Company Databases..

28
 Relationship types
 In our example, we specify the following relationship types:

1. MANAGES, a 1:1 relationship type between EMPLOYEE and

DEPARTMENT.

2. WORKS_FOR, a 1:N relationship type between

DEPARTMENT and EMPLOYEE. Both participations are total.

3. CONTROLS, a 1:N relationship type between DEPARTMENT

and PROJECT.

4. SUPERVISION, a 1:N relationship type between EMPLOYEE

(in the supervisor role) and EMPLOYEE (in the supervisee
role).

29
 Relationship types…
5. WORKS_ON, determined to be an M:N relationship type with
attribute Hours, after the users indicate that a project can have several
employees working on it. Both participations are determined to be
total.

6. DEPENDENTS_OF, a 1:N relationship type between EMPLOYEE

and DEPENDENT,

 Which is also the identifying relationship for the weak entity type
DEPENDENT.

 The participation of EMPLOYEE is partial, whereas that of

DEPENDENT is total.

30
 Mapping ER Diagram to Relational Tables
 The are three basic rules to convert ER diagrams into tables or
relations:
̶ For a relationship with One-to-One Cardinality
o All the attributes are merged into a single table.

̶ For a relationship with One-to-Many Cardinality:

o Post the primary key or candidate key from the “one” side
as a foreign key attribute to the “many” side.

̶ For a relationship with Many-to-Many Cardinality

o Create a new table (which is the associative entity) and
post primary key or candidate key from each entity as
attributes in the new table along with some additional
attributes 31
 Problems in E-R Modelling
 While designing the ER model one could face a problem on the
design which is called a connection traps.
 Connection traps are problems arising from misinterpreting of
certain relationships.
 Two main types of connection traps are:

1. Fan Traps and

2. Chasm Traps.
 1. Fan Trap
 It occurs where a model represents a relationship between entity
types, but the pathway between certain entity occurrences is
ambiguous.
 A fan trap occurs when one to many relationships fan out from a
single entity.
 May exist where two or more one-to-many (1:M) relationships fan
out from an entity.
 The problem could be avoided by restructuring the model so that
there would be no 1:M relationships fanning out from a singe entity
and all the semantics of the relationship is preserved.
 Cont’d…
Example

Problem: Which car (Car1 or Car3 or Car5) is used by Employee 6 Emp6 working in Branch
1 (Bra1)? Thus from this ER Model one cannot tell which car is used by which staff since a
branch can have more than one car and also a branch is populated by more than one employee.
Thus we need to restructure the model to avoid the connection trap.
 Cont’d…
 To avoid the Fan Trap problem we can go for restructuring of the E-R Model.
 This will result in the following E-R Model.
 2. Chasm Trap
 It occurs where a model suggests the existence of a relationship
between entity types, but the path way does not exist between
certain entity occurrences.
 It occurs where there is a relationship with partial participation,
which forms part of the pathway between entities that are related.
 Example:

 If we have a set of projects that are not active currently then we can
not assign a project manager for these projects. So there are project
with no project manager making the participation to have a
minimum value of zero.
 Problem:
 How can we identify which BRANCH is responsible for which
PROJECT?
 we have a minimum participation of zero between employee and
PROJECT we can’t identify the BRANCH responsible for each
PROJECT.
 The solution for this Chasm Trap problem is to add another relationship
between the extreme entities (BRANCH and PROJECT)
Enhanced Entity-Relationship(EER) Model
 Enhanced ER diagrams are high level models that represent the
requirements and complexities of complex database.
 In addition to ER model concepts EE-R includes:
1. Subclasses and Super classes.
2. Specialization and Generalization.
3. Category or union type.
4. Aggregation.
 Subclasses and Superclass
 Super class is an entity that can be divided into further subtype.
 For example: consider Shape super class.

 Super class shape has sub groups: Triangle, Square and Circle.
 Sub classes are the group of entities with some unique attributes
 Sub class inherits the properties and attributes from super class.
 Generalization & Specialization
 Generalization is the process of defining a general entity type from a
collection of specialized entity types.
 A generalized class is called a superclass
 It is a Bottom up process i.e. consider we have 3 sub entities
Car, Truck and Motorcycle.
 Now these three entities can be generalized into one super
class named as Vehicle.
 Generalization & Specialization…
 Specialization is a process of identifying subsets of an entity that
share some different characteristic.
 It is a top down approach in which one entity is broken down into
low level entity.
 Specialized classes are often called subclass
 A sub-class is best understood by “IS-A analysis”.
 Example: Vehicle entity can be a Car, Truck or Motorcycle.
 Category or Union
 Relationship of one super or sub class with more than one super
class.

 Owner is the subset of two super class: Vehicle and House.

 Aggregation
 Represents relationship between a whole object and its component.

 Consider a ternary relationship Works_On between Employee, Branch and

Manager.
 Now the best way to model this situation is to use aggregation.
 Thank
You!!!
Question???
44