Chapter 4 Entity Relationship (E-R) Modeling

1
Chapter 4Entity Relationship (E-R) Modeling
Database Systems Design, Implementation, and
Management Peter Rob Carlos Coronel
2
In this chapter, you will learn

• How relationships between entities are defined
  and refined, and how such relationships are
  incorporated into the database design process
• How ERD components affect database design and
  implementation
• How to interpret the modeling symbols for the
  four most popular ER modeling tools
• That real-world database design often requires
  that you reconcile conflicting goals

3
The Entity Relationship (E-R) Model

• ER model forms the basis of an ER diagram
• ERD represents the conceptual database as viewed
  by end user
• Main Components
• Entities
• In E-R models an entity refers to the entity set.
• An entity is represented by a rectangle
  containing the entitys name.
• Attributes
• Attributes are represented by ovals and are
  connected to the entity with a line.
• Each oval contains the name of the attribute it
  represents.
• Attributes have a domain -- the attributes set
  of possible values.
• Attributes may share a domain.
• Relationships

4
The Attributes of the STUDENT Entity
5
Primary Keys

• Underlined in the ER diagram
• Key attributes are also underlined in frequently
  used table structure shorthand
• Ideally composed of only a single attribute
• Possible to use a composite key
• Primary key composed of more than one attribute

6
The CLASS Table (Entity) Components and Contents
7
The Entity Relationship (E-R) Model

• Classes of Attributes
• A simple attribute cannot be subdivided.
• Examples Age, Sex, and Marital status
• A composite attribute can be further subdivided
  to yield additional attributes.
• Examples
• ADDRESS ??Street, City, State, Zip
• PHONE NUMBER ? Area code, Exchange number

8
The Entity Relationship (E-R) Model

• Classes of Attributes
• A single-valued attribute can have only a single
  value.
• Examples
• A person can have only one social security
  number.
• A manufactured part can have only one serial
  number.
• Multivalued attributes can have many values.
• Examples
• A person may have several college degrees.
• A household may have several phones with
  different numbers
• Multivalued attributes are shown by a double line
  connecting to the entity.

9
Multivalued attributes in an Entity
10
The Entity Relationship (E-R) Model

• Multivalued Attribute in Relational DBMS
• The relational DBMS cannot implement multivalued
  attributes.
• Possible courses of action for the designer
• Within the original entity, create several new
  attributes, one for each of the original
  multivalued attributes components ( Figure 4.4).
• Create a new entity composed of the original
  multivalued attributes components ( Figure 4.5).

11
Splitting the Multivalued Attribute into New
Attributes
12
A New Entity Set Composed of Multivalued
Attributes Components
13
The Entity Relationship (E-R) Model

• A derived attribute
• may be calculated (derived) from other attributes
• Need not be physically stored within the database
• Can be derived by using an algorithm
• Example AGE can be derived from the data of
  birth and the current date.

14
Depiction of a Derived Attribute
15
The Entity Relationship (E-R) Model

• Relationships
• A relationship is an association between
  entities.
• Relationships are represented by diamond-shaped
  symbols.

16
The Entity Relationship (E-R) Model

• Connectivity
• The term connectivity is used to describe the
  relationship classification (e.g., one-to-one,
  one-to-many, and many-to-many).

17
The Entity Relationship (E-R) Model

• Cardinality
• Cardinality expresses the specific number of
  entity occurrences associated with one occurrence
  of the related entity.
• The minimum and maximum number of entity
  occurrences

18
Connectivity and Cardinality in an ERD
19
Relationship Strength

• Existence Dependent
• If an entitys existence depends on the existence
  of one or more other entities, it is said to be
  existence-dependent.
• CLASS is existence-dependent on COURSE (parent
  entity)
• EMPLOYEE claims DEPENDENTDEPENDENT is
  existence-dependent on EMPLOYEE
• Existence independent
• Entity can exist apart from one or more related
  entities
• Example
• some of parts are produced in-house and other
  parts are bought from vendors.At least some of
  the parts are not supplied by a vender.
• PART is existence-independent from VENDOR

20
Relationship Strength

• Weak (non-identifying) relationship
• One entity is not existence-independent on
  another entity
• PK of related entity doesnt contain PK component
  of parent entity
• COURSE( CRS_CODE, )
• CLASS( CLASS_CODE, )
• Strong (identifying) relationship
• One entity is existence-dependent on another
• PK of related entity contains PK component of
  parent entity
• COURSE( CRS_CODE, )
• CLASS( CRS_CODE, CLASS_SECTION, )

21
A Weak Relationship Between COURSE and CLASS
22
A Strong (Identifying) Relationship Between
COURSE and CLASS
23
The Entity Relationship (E-R) Model

• Relationship Participation
• Optional
• The participation is optional if one entity
  occurrence does not require a corresponding
  entity occurrence in a particular relationship.
• An optional entity is shown by a small circle on
  the side of the optional entity.
• Mandatory
• Entity occurrence requires corresponding
  occurrence in related entity
• If no optionality symbol is shown on ERD, it is
  mandatory

24

• CLASS is Optional to COURSE
• COURSE is Mandatory to CLASS

• COURSE and CLASS in a Mandatory Relationship

25
Relationship Strength and Weak Entities

• Weak Entities
• A weak entity is an entity that
• Is existence-dependent and
• Has a primary key that is partially or totally
  derived from the parent entity in the
  relationship.
• The existence of a weak entity is indicated by a
  double rectangle.
• The weak entity inherits all or part of its
  primary key from its strong counterpart.

26

• A Weak Entity in an ERD
• EMPLOYEE( EMP_NUM, EMP_LNAME, EMP_FNAME,
  EMP_INITIAL, EMP_DOB )
• DEPENDENT( EMP_NUM, DEP_NUM, DEP_FNAME, DEP_DOB
  ) Primary Key

DEP_NUM
27
Weak entity in a Strong Relationship
Between DEPENDENT and EMPLOYEE
( EMP_NUM )
( EMP_NUM DEP_NUM )
28
Weak entity in a Strong Relationship

• Weak relationship
• One entity is not existence-independent on
  another
• PK of related entity doesnt contain PK component
  of parent entity
• COURSE( CRS_CODE, )
• CLASS( CLASS_CODE, )
• Strong relationship
• One entity is existence-dependent on another
• PK of related entity contains PK component of
  parent entity
• COURSE( CRS_CODE, )
• CLASS( CRS_CODE, CLASS_SECTION, )
• In any case, CLASS is always existence-dependent
  on COURSE, whether or not it is defined to be
  weak.

Not Weak entity
29
Relationship Degree

• A relationships degree indicates the number of
  associated entities or participants.
• A unary relationship exists when an association
  is maintained within a single entity.
• A binary relationship exists when two entities
  are associated.
• A ternary relationship exists when three entities
  are associated.

30
The Implementation of a Ternary Relationship

• ? FUND(FUND_ID,FUND_NAME) ,?? CFR
  ??????????????,? FUND(FUND_ID,FUND_NAME,CONTRIB_I
  D,FUND_AMOUNT) ????,??F2 ?? C3 10,000
  ???,?????? RECIPIENT

Researchers
31
The Entity Relationship (E-R) Model

• Recursive relationship
• A recursive relationship is one in which a
  relationship can exist between occurrences of the
  same entity set.
• A recursive entity is found within a unary
  relationship.

32

• 11 Recursive relationship
• EMPLOYEE is married to EMPLOYEE

33

• 1M Recursive relationship PART contains PART
• each part is used to create only one rotor
  assembly
• C130 4 AA21-6 2 AB-121

34

• MN Recursive relationship
• PART Contains PART
• A part_ can be used to create several different
  kinds of other parts
• A part_ is itself composed of many parts.

PART
PART
PART
contains
contains
PART
PART
COMPONENT
35
(No Transcript)
36

• MN Recursive relationship
• COURSE Requires COURSE

37
Implementation of the 1M EMPLOYEE Manages
EMPLOYEE Recursive Relationship
38
The Entity Relationship (E-R) Model

• Composite Entities
• A composite entity is composed of the primary
  keys of each of the entities to be connected.
• The composite entity serves as a bridge between
  the related entities.
• The composite entity may contain additional
  attributes.

39
The Entity Relationship (E-R) Model

• Composite Entities

40

• Converting the MN Relationship Into Two 1M
  Relationships

41

• At the start of registration
• A class may exist even though it contains no
  students at all
• A student has not yet signed up for any classes.

42

• A Composite Entity in the ERD

43
The Entity Relationship (E-R) Model

• Entity Supertypes and Subtypes
• Describing the different types of employees
  within a single entity would be awkward at best.
• Example Aviation business ( Figure 4.27)the
  special pilot characteristics (EMP_LICENCE,
  EMP_RATING, EMP_MED_TYPE) would cause a large
  number of nulls for other employees who are not
  pilots.

44
Nulls Created by Unique Attributes
45
The Entity Relationship (E-R) Model

• Generalization hierarchy
• Depicts relationships between higher-level
  supertype and lower-level subtype entities.
• Supertype contains the shared attributes
• Subtype contains the unique attributes.
• A subtype entity inherits its attributes and
  its relationships from the supertype entity.

46
A Generalization Hierarchy

• Disjoint relationships are indicated by G

47
The Entity Relationship (E-R) Model

• Disjoint Supertypes
• Also known as non-overlapping subtypes
• Subtypes that contain a subset of the supertype
  entity set
• Each entity instance (row) of the supertype can
  appear in only one of the disjoint subtypes.
• The supertype and its subtype(s) maintain a 11
  relationship.

48
The EMPLOYEE/PILOT Supertype/Subtype Relationship
49
A Generalization Hierarchy with Overlapping
Subtypes

• Overlapping relationships are indicated by Gs

50
A Comparison of ER Modeling Symbols
51
SUMMARY
52
The Chen Representation of the Invoicing Problem
53
The Crows Foot Representation of the Invoicing
Problem
54
The Rein85 Representation of the Invoicing
Problem
55
The IDEF1X Representation of the Invoicing
Problem
56
Developing an E-R Diagram

• The process of database design is an iterative
  rather than a linear or sequential process.
• Based on repetition of processes and procedures.
• The basic E-R model is graphically depicted and
  presented for review.
• The process is repeated until the end users and
  designers agree that the E-R diagram is a fair
  representation of the organizations activities
  and functions.

57
Developing an E-R Diagram

• Tiny College Database (1)
• Tiny College (TC) is divided into several
  schools. Each school is administered by a dean.
• A 11 relationship exists between DEAN and
  SCHOOL.
• Each dean is a member of a group of
  administrators (ADMINISTRATOR).
• Deans also hold professorial rank and may teach a
  class ( PROFESSOR).
• Administrators and professors are also Employees.

58
A Supertype/Subtype Relationship
59
Developing an E-R Diagram

• Tiny College Database (0)
• Most DBMS do not support supertype/subtype
  relationship directly.
• At the implementation level,designers convert it
  into a 11 relationship.
• A PROFESSOR is an EMPLOYEE.
• An EMPLOYEE is not required to be a PROFESSOR.
• PROFESSOR is optional to EMPLOYEE.
• PROFESSOR is existence-dependent on EMPLOYEE, and
  it inherits its PK from EMPLOYEE. Therefore the
  relationship between EMPLOYEE and PROFESSOR is
  strong, while is PROFESSOR a weak entity.

60
A Supertype/Subtype Relationship in an ERD
61
Developing an E-R Diagram

• Tiny College Database (1)
• Each school is composed of several departments.
• The smallest number of departments operated by a
  school is one,
• and the largest number of departments is
  indeterminate (N).
• Each department belongs to only a single school.

62
Developing an E-R Diagram

• Tiny College Database (2)
• Each department offers several courses.
• courses is optional to department.(Some
  departments are research only.)

63
Developing an E-R Diagram

• Tiny College Database (3)
• A department may offer several classes of the
  same course.
• A 1M relationship exists between COURSE and
  CLASS.
• CLASS is optional to COURSE

64
Developing an E-R Diagram

• Tiny College Database (4)
• Each department has many professors assigned to
  it.
• One of those professors chairs the department.
  Only one of the professors can chair the
  department.
• DEPARTMENT is optional to PROFESSOR in the
  chairs relationship.

65
Developing an E-R Diagram

• Tiny College Database (5)
• Each professor may teach up to four classes, each
  one a section of a course.
• A professor may also be on a research contract
  and teach no classes.

66
Developing an E-R Diagram

• Tiny College Database (6)
• A student may enroll in several classes, but
  (s)he takes each class only once during any given
  enrollment period.
• Each student may enroll in up to six classes and
  each class may have up to 35 students in it.
• STUDENT is optional to CLASS.

67
Developing an E-R Diagram

• Tiny College Database (8)
• Each department has several students whose major
  is offered by that department.
• Each student has only a single major and
  associated with a single department.
• It is possible, at least for a while, for a
  student not to declare a major filed of study.
  DEPARTMENT is optional to STUDENT.

68
Developing an E-R Diagram

• Tiny College Database (8)
• Each student has an advisor in his or her
  department each advisor counsels several
  students.
• An advisor is also a professor, but not all
  professors advise students.

69
Components of the ER Model
70

• Diagram from 6th edit.
• Not yet updated.

71
The Completed Tiny College ERD
72
The Challenge of Database Design Conflicting
Goals

• Database design must conform to design standards
• High processing speeds are often a top priority
  in database design
• Conflicting Goals
• Design standards (design elegance)
• Processing speed (high-transaction-speed)
• require design compromises
• Example 11 supertype/subtype relationship
• Two tables (avoid nulls)
• a single table (high speed)

73
MAR_DATE
74
The Challenge of Database Design Conflicting
Goals

• A recursive 11 relationship yields many
  different solutions.
• Your job as a database designer is to use your
  professional judgment to yield a solution that
  meets the requirements.

75
Summary

• Entity relationship (ER) model
• Uses ER diagrams to represent conceptual database
  as viewed by the end user
• Three main components
• Entities
• Relationships
• Attributes
• Includes connectivity and cardinality notations
• Connectivities and cardinalities are based on
  business rules

76
Summary (continued)

• ER symbols are used to graphically depict the ER
  models components and relationships
• ERDs may be based on many different ER models
• Entities can also be classified as supertypes and
  subtypes within a generalization hierarchy
• Database designers are often forced to make
  design compromises