Chapter 3

Conceptual Database Design

and E-R Modeling

1
Topics (Continued)
Topics Subtopics
3. 3.1 Conceptual Database Design
Conceptual 3.1.1.Steps to Build Conceptual Data Model
Database Design 3.1.2.Symbols Used in ER Diagram
and E-R 3.2 Entity-Relationship Diagram Building Blocks
Modeling 3.2.1.Entity Type
3.2.2.Attributes
3.2.3.Relational (cardinality and Degree of
Relationship
3.2.4.Participation Constraints
3.3 Design ER Diagram
3.4 Mapping ER Diagram to Relational Tables
3.4.1. ER Diagram to Table Mapping Algorithms
3.4.2. Mapping Strong Entities to Tables
3.4.3. Mapping Weak Entity to Tables
3.4.4. Mapping Composite Attribute to Tables
3.4.5. Mapping Multivalued Attributes to Tables

2
Topics
Topics Subtopics
3. 3.5 Problem With ER Models
Conceptual 3.6 Enhanced Entity Relationship (EER) Models
Database Design 3.6.1. Superclass and Subclass
and E-R 3.6.2. Relationship between Superclass and
Modeling Subclass
3.6.3. Generalization and Specialization
3.6.4. Constraints on Specialization and
Generalization
3.6.5. Inheritance
3.6.6. Union
3.7 Design EER Diagram
3.8 Mapping EER Model to Relations

3
Conceptual Database Design
 The process of constructing a model of the data used in an

enterprise, independent of all physical considerations.

 The goal of this phase is to produce a conceptual schema

(which includes identification of the important entity

types, relationship types, and attributes) for the database

that is independent of a specific DBMS.

 A commonly-used conceptual model is called an entity-

relationship model.

4
Steps to Build Conceptual Data Model
 Step 1.1 Identify entity types

 Step 1.2 Identify relationship types

 Step 1.3 Identify and associate attributes with entity or

relationship types

 Step 1.4 Determine attribute domains

 Step 1.5 Determine candidate, primary, and alternate key

attributes

5
Symbols Used in ER Diagram

6
Symbols Used in ER Diagram

7
Entity-Relationship Diagram Building
Blocks
 The basic building blocks of Entity- Relationship
diagram are
 Entity,
 Attribute and
 Relationship.

8
Entity-Relationship Diagram Building
Blocks
1. Entity
 An entity can be a real-world object, either
animate or inanimate, that can be easily
identifiable. For example, in a school database,
students, teachers, classes, and courses offered
can be considered as entities.

9
Entity-Relationship Diagram Building
Blocks
 Entity Type
 The entity type is a collection of the entity having similar
attributes. In the above Student table example, we have each
row as an entity and they are having common attributes i.e each
row has its own value for attributes Roll_no, Age, Student_name
and Mobile_no. So, we can define the above STUDENT table as
an entity type because it is a collection of entities having the
same attributes.
 So, an entity type in an ER diagram is defined by a
name(here, STUDENT) and a set of attributes(here, Roll_no,
Student_name, Age, Mobile_no). The table below shows how
the data of different entities( different students) are stored.
10
Entity-Relationship Diagram Building
Blocks
Entity Set
 Entity Set is a collection of entities of the same entity type. In
the above example of STUDENT entity type, a collection of
entities from the Student entity type would form an entity set.
We can say that entity type is a superset of the entity set
as all the entities are included in the entity type.
 Example 1: In the below example, two entities E1 (2, Angel, 19,
8709054568) and E2(4, Analisa, 21, 9847852156) form an
entity set.

11
Entity-Relationship Diagram Building
Blocks
Types of Entity type
o Strong Entity Type
o Weak Entity Type
 Strong Entity Type: Strong entity are those entity types
which has a key attribute. The primary key helps in
identifying each entity uniquely. It is represented by a
rectangle. In the above example, Roll_no identifies each
element of the table uniquely and hence, we can say that
STUDENT is a strong entity type.

12
Entity-Relationship Diagram Building
Blocks
 Weak Entity Type : Weak entity type doesn't have a key
attribute. Weak entity type can't be identified on its own.
 It depends upon some other strong entity for its distinct
identity. This can be understood with a real-life example. There
can be children only if the parent exits. There can be no
independent existence of children. There can be a room only if
building exits. There can be no independent existence of a room.
A weak entity is represented by a double outlined rectangle. The
relationship between a weak entity type and strong entity type
is called an identifying relationship and shown with a double
outlined diamond instead of a single outlined diamond. This
representation can be seen in the diagram below.
13
Entity-Relationship Diagram Building
Blocks
 Example: If we have two tables of
Customer(Customer_id, Name, Mobile_no, Age, Gender)
and Address(Locality, Town, State, Customer_id). Here we
cannot identify the address uniquely as there can be many
customers from the same locality. So, for this, we need an
attribute of Strong Entity Type i.e ‘Customer’ here to
uniquely identify entities of 'Address' Entity Type.

14
Entity-Relationship Diagram Building
Blocks

2. Attributes
 Entities are represented by means of their
properties, called attributes. All attributes have
values. For example, a student entity may have name,
class, and age as attributes.
 There exists a domain or range of values that can be
assigned to attributes. For example, a student's name
cannot be a numeric value. It has to be alphabetic. A
student's age cannot be negative, etc.
15
Entity-Relationship Diagram Building
Blocks

Types of Attributes
• Simple attribute − Simple attributes are atomic
values, which cannot be divided further. For example,
a student's phone number is an atomic value of 10
digits.
• Composite attribute − Composite attributes are made
of more than one simple attribute. For example, a
student's complete name may have first_name and
last_name.
16
Entity-Relationship Diagram Building
Blocks
• Derived attribute − Derived attributes are the attributes that
do not exist in the physical database, but their values are
derived from other attributes present in the database. For
example, average_salary in a department should not be saved
directly in the database, instead it can be derived. For another
example, age can be derived from data_of_birth.
• Single-value attribute − Single-value attributes contain single
value. For example − Social_Security_Number.
• Multi-value attribute − Multi-value attributes may contain more
than one values. For example, a person can have more than one
phone number, email_address, etc.
17
Entity-Relationship Diagram Building
Blocks

3. Relationship
 The association among entities is called a relationship. For
example, an employee works_at a department, a student
enrolls in a course. Here, Works_at and Enrolls are called
relationships.
 Relationship Set
 A set of relationships of similar type is called a
relationship set. Like entities, a relationship too can have
attributes. These attributes are called descriptive
attributes.
18
Relational (cardinality and Degree of
Relationship)

 Degree of Relationship

 The number of participating entities in a

relationship defines the degree of the
relationship.
 Binary = degree 2

 Ternary = degree 3

 n-ary = degree

19
Degree of a Relationship

▪ The following is an example of a ternary relationship.

▪ Recursive Relationship: Relationship type where the

same entity type participates more than once in
different roles. Sometimes these are called unary
relationships. A typical business example arises when
we have a business rule such as "a staff member
supervises other staff members"

20
Relationships, Associations, and Constraints
Mapping Cardinalities

 Cardinality defines the number of entities in one

entity set, which can be associated with the number
of entities of other set via relationship set.

1. One-to-one − One entity from entity set A can be

associated with at most one entity of entity set B
and vice versa.

21
Relationships, Associations, and Constraints

2. One-to-many − One entity from entity set A can be

associated with more than one entities of entity set
B however an entity from entity set B, can be
associated with at most one entity.

22
Relationships, Associations, and Constraints

3. Many-to-one − More than one entities from entity set A can

be associated with at most one entity of entity set B, however
an entity from entity set B can be associated with more than
one entity from entity set A.

4. Many-to-many − One entity from A can be associated with

more than one entity from B and vice versa.

23
Design ER diagrams
▪ We can express the overall logical structure of a database
graphically with an E-R diagram.
▪ Entity-Relationship Diagram (ERD) is a graphical representation of a
Entity-Relationship Model.
▪ ERDs are made from Entities, Attributes, and Relations.
Its components are:
•rectangles representing entity sets.
•ellipses representing attributes.
•diamonds representing relationship sets.
•lines linking attributes to entity sets and entity sets to
relationship sets.

S.S.N Street Note that this is a poor example of a entity, since the name is

name City
represented as one attribute and there is no street number
attribute.
We will consider the problem of designing good entities later,
here we are just concerned with explaining their graphical
representation.

Students Also note that one of the attributes is underlined, we will explain
why later.
24
Design ER diagrams Continued
We can express the overall logical structure of a database graphically
with an E-R diagram. The “since” attribute in this example is called a
descriptive attribute, since it describes the
Its components are: mapping from A to B

•rectangles representing entity sets.

•ellipses representing attributes.
•diamonds representing relationship sets.
•lines linking attributes to entity sets and entity sets to
relationship sets.

S.S.N Street since P.I.D

name
name City

Students advised Professor

25 by
Entity Types
(Naming Guidelines)
Entity type name should be:

 A singular noun and in capital letters.

 Descriptive and specific to the organization.

 Concise.

 Named for the result of the event, not the activity

or process of the event.

26
University Entity-Relationship Diagram

Entity

27
Attributes
(Naming Guidelines)

 An attribute name:
 Should be a noun and capitalize the first letter of each word.
(Example: Student_ID.)
 Should be unique.
 Should follow a standard format. (Example: Student_GPA, not
GPA_of_Student.)

 Similar attributes of different entity types should use

similar but distinguished names.
 Example: Faculty_Residence_City_Name and
Student_Residence_City_Name
28
Relationships
(Naming Guidelines)

 A relationship name should:

➢ Be a verb phrase, such as Is_assigned_to.

➢ Avoid vague names, such as “Has”.

 A relationship definition should Explain:

➢ What action is being taken and why it is important.

➢ If there is any optional participation.

➢ The history that is kept in the relationship.

➢ What any restrictions on participation in the relationship.

➢ For example: An EMPLOYEE may only be able to participate in two PROJECTS.
29
Starting an ERD

1. Define the Entities.

2. Define the Relationships.

3. Add attributes to the relationships.

4. Add cardinality to the relationships.

5. Don’t forget to use proper naming conventions

and symbol representation.

30
Exercise

1. 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, gender, and birth date. 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 number of hours per
week that an employee works on each project. We also keep
track of the direct supervisor of each employee.
31
Exercise

 We want to keep track of the dependents of each employee

for insurance purposes. We keep each dependent's first name,
gender, birth date, and relationship to the employee.

 The previous requirements can be translated into the

following schema represented as “Entity-Relationship (ER)
Schema Diagram”. This ER diagram is shown in the next slide

32
Exercise - ERD
ER diagram for the company Database

33
Mapping ER-models to relational Database
Schema

34
ER Diagram -to-Relational Mapping
Algorithm
 Mapping of Regular Entity Types.
 For each regular (strong) entity type E in the ER
schema, create a relation R that includes all the simple
attributes of E.
 Choose one of the key attributes of E as the primary
key for R.
 If the chosen key of E is composite, the set of simple
attributes that form it will together form the primary
key of R.
 Example: We create the relations
EMPLOYEE,DEPARTMENT, and PROJECT in the
relational schema corresponding to the regular
entities in the ER diagram.
35
ER Diagram -to-Relational Mapping
Algorithm
 SSN, DNUMBER, and PNUMBER are the primary keys for
the relations EMPLOYEE, DEPARTMENT, and PROJECT.
 Mapping of Weak Entity Types
 For each weak entity type W in the ER schema with owner
entity type E, create a relation R & include all simple
attributes (or simple components of composite attributes)
of W as attributes of R.
 Also, include as foreign key attributes of R the primary key
attribute(s) of the relation(s) that correspond to the owner
entity type(s).
 The primary key of R is the combination of the primary
key(s) of the owner(s) and the partial key of the weak
entity type W, if any.
36
ER Diagram -to-Relational Mapping
Algorithm
 Example: Create the relation DEPENDENT in this step
to correspond to the weak entity type DEPENDENT.

 Include the primary key SSN of the EMPLOYEE

relation as a foreign key attribute of DEPENDENT
(renamed to ESSN).

 The primary key of the DEPENDENT relation is the

combination {ESSN, DEPENDENT_NAME} because
DEPENDENT_NAME is the partial key of
DEPENDENT.
37
ER Diagram -to-Relational Mapping
Algorithm
 Mapping of Composite Attribute to Tables.

 Composite attributes are those which can be split into

components.
 Each component of a composite attribute is mapped as a
column of a relation
 The composite attribute itself does not appear in the
mapped relation
 For example: Name as F_Name and L_Name.

38
ER Diagram -to-Relational Mapping
Algorithm
 Mapping of Composite Attribute to Tables.

39
ER Diagram -to-Relational Mapping
Algorithm
 Mapping of Composite Attribute to Tables.

The mapped relation

as presented to a
user in a front-end
application

40
ER Diagram -to-Relational Mapping
Algorithm
 Mapping of Multivalued Attribute to Tables.

 Multi valued attribute are those which can take many values.
 Fore example: Mobile_Number, Hobbies, Skills etc..
 For each multivalued attribute A, create a new relation R.
 This relation R will include an attribute corresponding to A, plus
the primary key attribute K-as a foreign key in R-of the relation
that represents the entity type of relationship type that has A as
an attribute.
 The primary key of R is the combination of A and K. If the
multivalued attribute is composite, we include its simple
components.
41
ER Diagram -to-Relational Mapping
Algorithm
 Example: The relation DEPT_LOCATIONS is
created.
 The attribute DLOCATION represents the multivalued
attribute LOCATIONS of DEPARTMENT, while DNUMBER-
as foreign key-represents the primary key of the
DEPARTMENT relation.

 The primary key of R is the combination of {DNUMBER,

DLOCATION}.

42
Problems with ER models
 Problems may arise when designing an ER model called
connection traps.

 Often due to a misinterpretation of the meaning of certain

relationships.

 Two main types of connection traps are called fan traps and
chasm traps.

43
Problems with ER models
1. Fan trap
 A fan trap my exist where two or more 1:* relationships fan out
(spread out) from the same entity.

 Occurs between related entities that are not directly connected

and the indirect pathway that connects them includes two 1:*
relationships that fan out from a central entity.

 This means that certain entity occurrences that are related can
only be connected using a pathway that can be ambiguous.

44
Example of a fan trap

45
Example of a fan trap

 At which branch office does staff number SG37

work???
46
Restructuring ER model to remove Fan Trap

47
Restructured ER Model with Fan Trap Removed

 SG37 works at branch B003.

48
Problems with ER models
2. Chasm trap

 Occurs between related entities that are not directly

connected and the indirect pathway that connects them
includes partial participation.

 This means that certain entity occurrences that are related

have no means of connection.

49 ©Pearson Education
2009
An Example of a Chasm Trap

50
An Example of a Chasm Trap

 At which branch office is property PA14 available???

51
ER Model restructured to remove Chasm Trap

• To solve the problem of chasm trap we have to identify the

missing r/ship. The missing r/ship is b/n PROPERTFORRENT
and BRANCHES.

52
Restructured ER Model with Chasm Trap Removed

53
The Enhanced Entity-Relationship (EER)
Model
 Enhanced ER (EER) model

 A diagrammatic technique for displaying

specialization/generalizationin an EER diagram using UML.

 Created to design more accurate database schemas

 Reflect the data properties and constraints more precisely

 More complex requirements than traditional applications

 Examples of additional concept of EER model is called

specialization / generalization.
Subclasses, Superclasses, and Inheritance
 EER model includes all modeling concepts of the ER
model

 In addition, EER includes:

 Subclasses and superclasses

 Specialization and generalization

 Category or union type

 Attribute and relationship inheritance

Subclasses, Superclasses, and Inheritance
(cont.)
 Enhanced ER or EER diagrams
 Diagrammatic technique for displaying these concepts in an
EER schema

 Subtype or subclass of an entity type

 Subgroupings of entities that are meaningful

 Represented explicitly because of their significance to the

database application
Subclasses, Superclasses, and Inheritance
(cont.)
 Terms for relationship between a superclass and any one
of its subclasses
 Superclass/subclass

 Supertype/subtype

 Class/subclass relationship

 Type inheritance
 Subclass entity inherits all attributes and relationships of
superclass
Specialization and Generalization
 Specialization
 Process of defining a set of subclasses of an entity type

 Defined on the basis of some distinguishing characteristic of

the entities in the superclass

 Subclass can define:

 Specific attributes

 Specific relationship types

Specialization and Generalization (cont.)
 Certain attributes may apply to some but not all entities
of the superclass

 Some relationship types may be participated in only by

members of the subclass
Generalization
 Reverse process of abstraction

 Generalize into a single superclass

 Original entity types are special subclasses

 Generalization

 Process of defining a generalized entity type from the given

entity types
Constraints and Characteristics of
Specialization and Generalization Hierarchies

 Constraints that apply to a single specialization or a single

generalization

 Differences between specialization/

generalization lattices and hierarchies

Constraints on Specialization and
Generalization
 May be several or one subclass

 Determine entity subtype:

 Predicate-defined (or condition-defined) subclasses

 Attribute-defined specialization

 User-defined
Constraints on Specialization and Generalization
(cont.)
 Disjointness constraint
 Specifies that the subclasses of the specialization must be
disjoint

 Completeness (or totalness) constraint

 May be total or partial

 Disjointness and completeness constraints are

independent
Specialization and Generalization
Hierarchies and Lattices

 Specialization hierarchy

 Every subclass participates as a subclass in only one

class/subclass relationship

 Results in a tree structure or strict hierarchy

 Specialization lattice

 Subclass can be a subclass in more than one class/subclass

relationship
Specialization and Generalization Hierarchies
and Lattices (cont.)

 Multiple inheritance

 Subclass with more than one superclass

 If attribute (or relationship) originating in the same

superclass inherited more than once via different paths in
lattice

 Included only once in shared subclass

 Single inheritance

 Some models and languages limited to single inheritance

Utilizing Specialization and Generalization
in Refining Conceptual Schemas

 Specialization process

 Start with entity type then define subclasses by successive

specialization

 Top-down conceptual refinement process

 Bottom-up conceptual synthesis

 Involves generalization rather than specialization

Modeling of UNION Types Using Categories

 Union type or a category

 Represents a single superclass/subclass relationship with

more than one superclass

 Subclass represents a collection of objects that is a subset of

the UNION of distinct entity types

 Attribute inheritance works more selectively

 Category can be total or partial

 Some modeling methodologies do not have union types

A Sample UNIVERSITY EER Schema,
Design Choices, and Formal Definitions

 The UNIVERSITY Database Example

 UNIVERSITY database

 Students and their majors

 Transcripts, and registration

 University’s course offerings

Design Choices for
Specialization/Generalization

 Many specializations and subclasses can be defined to make the

conceptual model accurate

 If subclass has few specific attributes and no specific

relationships

 Can be merged into the superclass

Design Choices for
Specialization/Generalization (cont.)

 If all the subclasses of a specialization/generalization have

few specific attributes and no specific relationships
 Can be merged into the superclass

 Replace with one or more type attributes that specify the

subclass or subclasses that each entity belongs to
Design Choices for
Specialization/Generalization (cont.)

 Union types and categories should generally be avoided

 Choice of disjoint/overlapping and total/partial constraints on

specialization/generalization

 Driven by rules in miniworld being modeled

Formal Definitions for the EER Model
Concepts
 Class

 Set or collection of entities

 Includes any of the EER schema constructs of group entities

 Subclass

 Class whose entities must always be a subset of the entities

in another class

 Specialization

 Set of subclasses that have same superclass

Formal Definitions for the EER Model Concepts
(cont.)

 Generalization

 Generalized entity type or superclass

 Predicate-defined

 Predicate on the attributes of is used to specify which

entities in C are members of S

 User-defined

 Subclass that is not defined by a predicate

Formal Definitions for the EER Model Concepts
(cont.)
 Category

 Class that is a subset of the union of n defining superclasses

 Relationship type

 Any class can participate in a relationship

79