Module 3 The Entity Relationship Model

1
Module 3The Entity-Relationship Model

• Teknik Informatika Fakultas Teknik
• Universitas Dr. Soetomo Surabaya

2
Module 3 - Motivation

• Conceptual Design is an important phase in
  designing a successful database application
• Without conceptual models, it is very difficult
  to communicate database designs to (nontechnical)
  users - This lack of communication may result in
  users data requirements being missed or
  incorrect requirements being captured
• The Entity Relationship (ER) Model is one of the
  most widely used method for conceptual design.
• The ER Model can be neatly mapped to a Relational
  Schema

3
Module 3 - Contents

• The Entity-Relationship Model
• Conceptual Design and how conceptual design
  relates to the database design process
• Entity-Relationship (ER) Conceptual Design Method
• Notation Guide
• Example Illustrations

4
Phases of Database Design

• First step of the design process is to identify
  the Universe of Discourse (UoD)
• The database to be built will not model
  everything in the world, but rather some
  mini-world or Universe of Discourse.
• The UoD is the relevant portion of the real world
  to be modeled by the database

5
Phases of Database Design

• Functional Analysis consists of specifying
  operations (retrievals, updates) that will be
  applied to the database
• Various techniques for software design exist,
  such as sequence diagrams, flowcharts, DFDs, etc.
• Refer to Software Engineering Texts

6
Phases of Database Design

• Description of the data requirements of users
• Expressed using a high level model such as
  ENTITY-RELATIONSHIP (ER)
• A database schema in the ER Model can be
  represented pictorially (ER diagrams)
• ER Model contains detailed descriptions of
• What are the entities and relationships in the
  enterprise?
• What information about these entities and
  relationships should we store in the database?
• What are the integrity constraints or business
  rules that hold?

7
Phases of Database Design

• Actual implementation of the database, using a
  commercial DBMS
• Depends on the logical/implementation model of
  the DBMS, for example Relational Database Model,
  Object Oriented Database Model.
• Conceptual Model (ER) is mapped to the
  implementation model (Relational) in this phase

8
Phases of Database Design

• Database is tuned for storage and performance
• Includes specification of storage structures,
  access paths, file organization etc.

9
Phases of Database Design

• Data Structures and Operations are closely linked
• Design of the two is done in parallel and
  subsequent implementation in interdependent

10
Conceptual Design
Defines the UoD, and captures semantics
constraints of the UoD
Database Requirements
Focus of this lecture
CONCEPTUAL DESIGN
Serves as a medium for communicating the UoD
Conceptual Schema (High Level Data Model, e.g.
ER, NIAM, SDM)
11
Module 3 - Contents

• The Entity-Relationship Model
• Conceptual Design and how conceptual design
  relates to the database design process
• Entity-Relationship (ER) Conceptual Design Method
• Notation Guide
• Example Illustrations

12
ER Model Basics

• Entities
• Entities, Entity Sets and Entity Types
• Attributes, Keys and Value Sets
• Relationships
• Relationship Types and Sets
• Relationship Degree
• Roles and Recursive Relationships
• Relationship Constraints
• Attributes of Relationship Types

13
ER Diagram Basics
Relationship
Attributes
14
Entities

• Entity
• Entity Sets
• Entity Type
• Attributes (Types, Keys and Value Sets)

15
Entity

• Real-world object distinguishable from other
  objects
• (e.g a student, car, job, subject, building ...)
• An entity is described using a set of attributes
• The same entity may have different prominence in
  different UoDs
• In the Company database, an employees car is
  of
• lesser importance
• In the Department of Transportations
  registration
• database, cars may be the most important
  concept
• In both cases, cars will be represented as
  entities
• but with different levels of detail

16
Entity Sets

• A collection of similar entities (e.g. all
  employees)
• All entities in an entity set have the same set
  of attributes
• Each entity set has a key
• Each attribute has a domain
• Can map entity set to a relation easily

EMPLOYEES
CREATE TABLE Employees (ssn CHAR(11), name
CHAR(20), sal INTEGER, PRIMARY KEY (ssn))
17
Entity Type

• Defines set of entities that have the same
  attributes (e.g. EMPLOYEE)
• Each Entity Type is described by its NAME and
  attributes
• The Entity Type describes the Schema or
  Intension for a set of entities
• Collection of all entities of a particular entity
  type at a given point in time is called the
  Entity Set or Extension of an Entity Type
• Entity Type and Entity Set are customarily
  referred to by the same name

18
Attributes

• Key Attributes
• Value Sets of Attributes
• Null Valued Attributes
• Attribute Types
• Composite Vs. Simple Attributes
• Single-valued Vs. Multi-valued Attributes
• Derived Vs. Stored Attributes

Notation
19
Key Attributes

• Key (or uniqueness) constraints are applied to
  entity types
• Key attributes values are distinct for each
  individual entity in the entity set
• A key attribute has its name underlined inside
  the oval
• Key must hold for every possible extension of the
  entity type
• Multiple keys are possible

SSN
EMPLOYEE
20
Value Sets of Attributes

• Value sets specify the set of values that may be
  assigned to a particular attribute of an entity
• Employee Age Integers between 21 65
• Vehicle Registration Number String of 3
• alphabets followed by 3 integers
• Value sets map to relational domains
• Value sets are not displayed on the ER diagram

21
Null Valued Attributes

• A particular entity may not have an applicable
  value for an attribute
• Tertiary-Degree Not applicable for a person
• with no university education
• Home-Phone Not known if it exists
• Height Not known at present time
• Type of Null Values
• Not Applicable
• Unknown
• Missing

22
Composite Vs. Simple Attributes

• Composite attributes can be divided into smaller
  parts which represent simple attributes with
  independent meaning
• Simple Attribute Aircraft-Type
• Complex Attribute Aircraft-Location which is
  comprised of
• Aircraft-Latitude
• Aircraft-Longitude
• Aircraft-Altitude

Notation
There is no formal concept of
composite attribute in the relational model
23
Single Vs. Multivalued Attributes

• Simple attributes can either be single-valued
• or multi-valued
• Single-valued Gender F
• Notation
• Multivalued Degree BSc, MInfTech
• Notation
• An attribute in the relational model is
  always
• single valued - Values are atomic!

24
Derived Vs. Stored Attributes

• Some attribute values can be derived from
• related attribute values
• Age Date - B-day
• Y-Sal 12 M-Sal

25
Derived Vs. Stored Attributes

• Some attribute values can be derived from
  attributed values of related entities
• total-value sum (qty price)

26
Representing Attributes

• Parenthesis ( ) for composite attributes
• Brackets for multi-valued attributes
• Assume a person can have more than one residence
  and each residence can have multiple telephones
• AddressPhone
• ( Phone ( AreaCode,PhoneNum ) ,
• Address (StreetAddresss (Number,
  Street, AptNo),
• City,State,PostalCode) )

27
Formally ...

• An attribute A is defined as function
• A AE _P(V)
• where E is the Entity Type
• V is the Value Set
• P(V) is the Power Set (set of all subsets) of
  V
• Value of attribute A for entity e is A(e)
• where e is an entity of type E

28
Formally ...

• A is a Simple Attribute where A(e) is
• A singleton (set with one element) for
  single-valued
• attributes
• A set with multiple elements for
  multi-valued
• attributes
• An empty set for null valued attributes
• A is a Composite Attribute when the value set
• V is the Cartesian product of sets
• P(V1), P(V2),...,P(Vn) where V1,V2,...,Vn are
• value sets for the simple component attributes
• that form A V P(V1) X P(V2) X ... X P(Vn)

29
ER Model Basics

• Entities
• Entities, Entity Sets and Entity Types
• Attributes, Keys and Value Sets
• Relationships
• Relationship Types and Sets
• Relationship Degree
• Roles and Recursive Relationships
• Relationship Constraints
• Attributes of Relationship Types

30
Relationships

• Relationship Types and Sets
• Relationship Degree
• Entity Roles and Recursive Relationships
• Relationship Constraints
• Attributes of Relationship Types

31
Relationship Types and Sets

• A Relationship is an association among two or
  more entities (e.g John works in Pharmacy
  department)
• A Relationship Type defines the relationship, and
  a Relationship Set represents a set of
  relationship instances
• A Relationship Type thus defines the structure of
  the Relationship Set
• Relationship Type and corresponding Set are
  customarily referred to by the same name

32
Formally ...

• A Relationship Type R
• defines a set of
• associations among n
• entity types
• E1, E2, En
• that is, R is a subset of
• the Cartesian product
• E1 X E2 X X En
• Each Ei is said to
• participate in the
• relationship type

• A Relationship Set R
• is a set of relationship
• instances ri, where
• each ri associates n
• individual entities
• (e1, e2, en)
• Each ei is said to
• participate in the
• relationship instance

33
Relationship Degree

• The degree of a relationship type is the number
  of participating entity types
• 2 entities Binary Relationship
• 3 entities Ternary Relationship
• n entities N-ary Relationship
• Same entity type could participate in
• multiple relationship types

34
Entity Roles

• Each entity type that
• participates in a relationship
• type plays a particular role
• in the relationship type
• The role name signifies the
• role that a participating
• entity from the entity type
• plays in each relationship
• instance, i.e. it explains what
• the relationship means

35
Recursive Relationships

• Same entity type can participate more than once
  in the same relationship type under different
  roles
• Such relationships are called
• Recursive Relationships

36
Relationship Constraints

• What are Relationship Constraints ?
• Constraints on relationships are determined by
  the UoD, which these relationships are describing
• Constraints on the relationship type limit the
  possible combination of entities that may
  participate in the corresponding relationship set

37
Kinds of Constraints

• What kind of constraints can be defined in the ER
  Model?
• Cardinality Constraints
• Participation Constraints
• Together called Structural Constraints

Constraints are represented by specific notation
in the ER diagram
38
Cardinality Ratio

• The Cardinality Ratio for a binary relationship
  specifies the number of relationship instances
  that an entity can participate in
• Works-In is a binary relationship
• Participating entities are
• DEPARTMENT EMPLOYEE
• One department can have
• Many employees -
• Cardinality Ratio is 1 N

39
Possible Cardinality Ratios

• 1-to-1 (1 1)
• Both entities can participate in
• only one relationship instance

40
Possible Cardinality Ratios

• 1to-1 (1 1)
• Both entities can
• participate in only one
• relationship instance
• 1-to-Many, Many-to-1
• (1 N, N 1)
• One entity can
• participate in many
• relationship instances

41
Possible Cardinality Ratios

• 1-to-1 (1 1)
• Both entities can participate in
• only one relationship instance
• 1-to-Many, Many-to-1
• (1 N, N 1)
• One entity can participate in
• many relationship instances
• Many-to-Many (N M)
• Both entities can participate in
• many relationship instance

42
Example Cardinality Constraints

• How many Employees can work in a Department?
• One employee can work in only one department
• How many Employees can be employed by a
  Department?
• One department can employ many employees
• How many managers can a department have?
• One department can have only one manager
• How many departments can an employee manage?
• One employee can have manage only one
  department

43
Representing Cardinality

• One employee can work in only one department
• One department can employ many employees
• One department can have only one manager
• One employee can manage only one department

44
Existence Dependency

• Existence dependency indicates whether the
  existence of an entity depends on its
  relationship to another entity via the
  relationship type
• Every employee must work for
• a department - EMPLOYEE is
• existentially dependent on
• DEPARTMENT via the Works-In
• relationship type

45
Kinds of participating constraints

• TOTAL Participation (Existence Dependency)
• Constraint Every employee must work for a
  department
• PARTIAL Participation
• Constraint Not every employee is a manager

46
Representing Participation

• Every employee must work for a department
• Every department must have a manager
• Every department must have employees
• Not every employee is a manager

47
Attributes of Relationship Types

• Relationship Types can also have attributes just
  as entity types

48
Attributes of 11 or 1N

• Attributes of 11 or 1N relationship types can
  be migrated to one of the participating entity
  types
• Since of Manages can be an attribute of
  EMPLOYEE or
• DEPARTMENT
• StartDate of Works-In can be an attribute
  EMPLOYEE
• (only N-side of the relationship)

49
Attributes of MN

• Attributes of MN relationship types cannot be
  migrated to one of the participating entity
  types.
• In addition to (descriptive) attributes,
  attributes that identify the participating
  entities must also be specified (next Module)
• Qty of Keeps can only be
• determined by the combination of
• STORE and PRODUCT

50
Weak Entities

• Entity types that do not have key attributes
• of their own are called Weak Entities
• Notation
• A weak entity can be identified uniquely
• only by considering the primary key of
• another Owner entity
• The relationship type that relates a weak
• entity to its owner is called the Identifying
• relationship
• Notation

51
Weak Entities

• Owner and weak entity must participate in a 1N
  relationship type
• Weak entity must have total participation in the
  identifying relationship set (existence
  dependency)
• Weak entities normally have a Partial Key,
  which is a set of attributes that uniquely
  identify weak entities related to the same owner
  entity
• Not every existence dependency results in a weak
  entity !

52
Example Weak Entity
53
Weak Entity?
54
Specialization and Generalization

• Specialisation
• Define a number of subclasses of an entity
  type.
• Each subclass contains a subset entities of
  the
• superclass.
• A subclass is defined based on more specific
• distinguishing characteristic on entities of
  the super
• class.
• Generalisation
• Opposite process to specialisation.
• Abstraction process of ignoring differences
  amongst
• some entity types (subclasses) and generalise
  them
• into a superclass.

55
Extended ER (EER)

• Entity Type is called class in EER
• Class can be Superclass, Subclass
• Entities in the same class have the same
  attributes
• Attributes of a superclass are inherited by the
  subclasses.
• Subclass can have its now specific attributes
• Subclass can have its now specific relationships
• Every entity in a subclass is a member of its
  super class(es)

56
Sub/Super Classes

• Entity types refined into sub-classes and
• super-classes
• Notation
• Subclass entity types inherit
• attributes
• relationships
• from superclass entity type

57
Specialization/Generalization

• Top Down vs Bottom Up
• Sub classes are specializations of superclass
• Superclass is generalization of subclasses
• Allows us to model
• attributes only applicable to entity
  subclasses
• relationships only played by entity subclasses

58
Constraints

• Specialization may be
• total
• partial
• Subclass sets may be
• overlapping
• disjoint
• Subclass participation may be
• attribute-defined
• user-defined

59
Module 3 - Contents

• The Entity-Relationship Model
• Conceptual Design and how conceptual design
  relates to the database design process
• Entity-Relationship (ER) Conceptual Design Method
• Notation Guide
• Example Illustrations

60
Notation Guide

• ENTITY TYPE
• WEAK ENTITY TYPE
• RELATIONSHIP TYPE
• IDENTIFYING RELATIONSHIP TYPE

61
Notation Guide

• ATTRIBUTE
• KEY ATTRIBUTE
• MULTIVALUED ATTRIBUTE
• DERIVED ATTRIBUTE
• COMPOSITE ATTRIBUTE

62
Notation Guide

• TOTAL PARTICIPATION OF E2 IN R
• CARDINALITY RATIO 1N FOR E1E2 IN R
• STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION
  OF E IN R (Alternative Notation)

63
Notation Guide

• E1 IS A SUBCLASS OF E2
• E1 and E2 ARE SUBCLASSES OF E3
• Overlapping specialization
• Disjoint specialization

64
Module 3 - Contents

• The Entity-Relationship Model
• Conceptual Design and how conceptual design
  relates to the database design process
• Entity-Relationship (ER) Conceptual Design Method
• Notation Guide
• Example Illustrations

65
Example Illustrations

• Project Management
• Course Administration
• Recruitment
• Company Database
• Note how different (interpretations of) semantics
  change the ER Diagram

66
Project Management (a)
67
Project Management (b)
68
Project Management (c)
69
Course Administration
70
Recruitment
71
(No Transcript)
72
(No Transcript)
73
Company Schema (Relational)

• EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
  Address, Sex, Salary , SuperSsn
• DEPARTMENT Dnumber, Dname MGRSSN, MgrStart
• PROJECT Pno, PName, Plocation, Dnum
• DEPENDENT ESSN,DepName, Sex, BirthDate,
  Relationship
• WORKS_ON ESSN, PNo, Hours
• DEPT_LOCS DNumber, Dlocation

74
Module 3 - Review

• Conceptual modeling is the first step towards
  achieving a good database design
• ER diagrams are a graphical tool for conceptual
  modeling of the UoD
• The ER Model describes the UoD as
• A set of entities and relationships
• Entity roles, structural constraints and weak
• entities add more semantics to the ER Model
• ER diagrams can be mapped to a Relational Schema
  (next Module)

75
Recommended Readings

• Elmasri Navathe
• Chapter 3,4(4.1-4.3, 4.7)

76
Next ...

• Module 4
• ER to Relational
• Mapping