Chapter 4 Entity Relationship ER Modeling

1
Chapter 4Entity Relationship (E-R) Modeling
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos Coronel
2
Basic Modeling Concepts

• Database design is both art and science.
• A data model is the relatively simple
  representation, usually graphic, of complex
  real-world data structures. It represents data
  structures and their characteristics, relations,
  constraints, and transformations.
• The database designer usually employs data models
  as communications tools to facilitate the
  interaction among the designer, the applications
  programmer, and the end user.
• A good database is the foundation for good
  applications.

3
Figure 4.1 Four Modified (ANSI/SPARC) Data
Abstraction Models
4
Data Models Degrees of Data Abstraction

• The Conceptual Model
• The conceptual model represents a global view of
  the data. It is an enterprise-wide representation
  of data as viewed by high-level managers.
• Entity-Relationship (E-R) model is the most
  widely used conceptual model.
• The conceptual model forms the basis for the
  conceptual schema.
• The conceptual schema is the visual
  representation of the conceptual model.
• The conceptual model is independent of both
  software (software independence) and hardware
  (hardware independence).

5
Tiny College Entities
Figure 4.2
6
A Conceptual Schema for Tiny College
Figure 4.3
7
Data Models Degrees of Data Abstraction

• The Internal Model
• The internal model adapts the conceptual model to
  a specific DBMS.
• The internal model is software-dependent.
• Development of the internal model is especially
  important to hierarchical and network database
  models.

8
Figure 4.4
9
Data Models Degrees of Data Abstraction

• The External Model
• The external model is the end users view of the
  data environment.
• Each external model is then represented by its
  own external schema.
• CREATE VIEW CLASS_VIEW ASSELECT (CLASS_ID,
  CLASS_NAME, PROF_NAME, CLASS_TIME, ROOM_ID)FROM
  CLASS, PROFESSOR, ROOMWHERE CLASS.PROF_ID
  PROFESSOR.PROF_ID AND CLASS.ROOM_ID
  ROOM.ROOM_ID

10
Figure 4.5 The External Models for Tiny College
11
Data Models Degrees of Data Abstraction

• The External Model
• Advantages of Using External Schemas
• It makes application program development much
  simpler.
• It facilitates the designers task by making it
  easier to identify specific data required to
  support each business units operations.
• It makes the designers job easier by providing
  feedback about the conceptual models adequacy.
• It helps to ensure security constraints in the
  database design.

12
Data Models Degrees of Data Abstraction

• The Physical Model
• The physical model operates at the lowest level
  of abstraction, describing the way data is saved
  on storage media such as disks or tapes.
• It requires the definition of both the physical
  storage devices and the access methods required
  to reach the data within those storage devices.
• The physical model is both software and
  hardware-dependent.
• It requires detailed knowledge of hardware and
  software used to implement the database design.

13
The Entity Relationship (E-R) Model

• E-R model is commonly used to
• Translate different views of data among managers,
  users, and programmers to fit into a common
  framework.
• Define data processing and constraint
  requirements to help us meet the different views.
• Help implement the database.

14
The Entity Relationship (E-R) Model

• E-R Model 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.
• Primary keys are underlined.
• Relationships

15
The Attributes of the STUDENT Entity
Figure 4.6
16
Basic E-R Model Entity Presentation
Figure 4.7
17
The CLASS Table (Entity) Components and Contents
Figure 4.8
18
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

19
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.

20
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.9).
• Create a new entity composed of the original
  multivalued attributes components (Figure 4.10).

Table 4.1
21
Splitting the Multivalued Attributes into New
Attributes
Figure 4.9
22
A New Entity Set Composed of Multivalued
Attributes Components
Figure 4.10
23
The Entity Relationship (E-R) Model

• A derived attribute is not physically stored
  within the database instead, it is derived by
  using an algorithm.
• Example AGE can be derived from the data of
  birth and the current date.

Figure 4.11 A Derived Attribute
24
The Entity Relationship (E-R) Model

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

Figure 4.12 An Entity Relationship
25
The Entity Relationship (E-R) Model

• 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.

26
The Implementation of a Ternary Relationship
Figure 4.14
27
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).

Figure 4.15 Connectivity in an ERD
28
The Entity Relationship (E-R) Model

• Cardinality
• Cardinality expresses the specific number of
  entity occurrences associated with one occurrence
  of the related entity.

Figure 4.16 Cardinality in an ERD
29
Figure 4.17
30

• Existence Dependency
• If an entitys existence depends on the existence
  of one or more other entities, it is said to be
  existence-dependent.

Figure 4.18
31
The Entity Relationship (E-R) Model

• Relationship Participation
• 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.

Figure 4.19 An ERD With An Optional Entity
32
Figure 4.20 CLASS is Optional to COURSE
Figure 4.21 COURSE and CLASS in a Mandatory
Relationship
33
The Entity Relationship (E-R) Model

• 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. (Figure 4.22)
• The weak entity inherits all or part of its
  primary key from its strong counterpart.

34
A Weak Entity in an ERD
Figure 4.22
35
An Illustration of the Weak Relationship
Between DEPENDENT and EMPLOYEE
Figure 4.23
36
The Entity Relationship (E-R) Model

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

Figure 4.24 An E-R Representation of Recursive
Relationships
37
Figure 4.25
Figure 4.26
38
The Implementation of the MN Recursive PART
Contains PART Relationship
Figure 4.27
39
Implementation of the MN COURSE Requires
COURSE Recursive Relationship
Figure 4.28
40
Implementation of the 1M EMPLOYEE Manages
EMPLOYEE Recursive Relationship
Figure 4.29
41
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.

42
Converting the MN Relationship Into Two 1M
Relationships
Figure 4.30
43
The MN Relationship Between STUDENT and CLASS
Figure 4.31
44
A Composite Entity in the ERD
Figure 4.32
45
The Entity Relationship (E-R) Model

• Entity Supertypes and Subtypes

Figure 4.33 Nulls Created by Unique Attributes
46
The Entity Relationship (E-R) Model

• Entity Supertypes and Subtypes
• The generalization hierarchy depicts the
  parent-child relationship.
• The supertype contains the shared attributes,
  while the subtype contains the unique attributes.
• A subtype entity inherits its attributes and its
  relationships from the supertype entity.

47
A Generalization Hierarchy
Figure 4.34
48
The Entity Relationship (E-R) Model

• Entity Supertypes and Subtypes
• The supertype entity set is usually related to
  several unique and disjointed (nonoverlapping)
  subtype entity sets.
• The supertype and its subtype(s) maintain a 11
  relationship.

49
The EMPLOYEE/PILOT Supertype/Subtype Relationship
Figure 4.35
50
The Entity Relationship (E-R) Model

• Entity Supertypes and Subtypes
• The generalization hierarchy depicts the
  parent-child relationship. (Figure 4.34)
• The supertype contains the shared attributes,
  while the subtype contains the unique attributes.
• The supertype entity set is usually related to
  several unique and disjointed (nonoverlapping)
  subtype entity sets.
• The supertype and its subtype(s) maintain a 11
  relationship.

51
A Generalization Hierarchy With Overlapping
Subtypes
Figure 4.36
52
Figure 4.37
53
Chapter 4Entity Relationship (E-R) Modeling
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos Coronel
54
Developing an E-R Diagram

• The process of database design is an iterative
  rather than a linear or sequential process.
• It usually begins with a general narrative of the
  organizations operations 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.

55
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. (Figure 4.38)

56
(No Transcript)
57
Developing an E-R Diagram

• Tiny College Database (2)
• 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.

Figure 4.40 The First Tiny College ERD Segment
58
Developing an E-R Diagram

• Tiny College Database (3)
• Each department offers several courses.

Figure 4.41 The Second Tiny College ERD Segment
59
Developing an E-R Diagram

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

Figure 4.42 The Third Tiny College ERD Segment
60
Developing an E-R Diagram

• Tiny College Database (5)
• 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.

Figure 4.43 The Fourth Tiny College ERD Segment
61
Developing an E-R Diagram

• Tiny College Database (6)
• 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.

Figure 4.44 The Fifth Tiny College ERD Segment
62
Developing an E-R Diagram

• Tiny College Database (7)
• 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.

Figure 4.45 The Sixth Tiny College ERD Segment
63
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.

Figure 4.46 The Seventh Tiny College ERD Segment
64
Developing an E-R Diagram

• Tiny College Database (9)
• 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.

Figure 4.47 The Eighth Tiny College ERD Segment
65
Developing an E-R Diagram
Entities for the Tiny College Database

• SCHOOL
• DEPARMENT
• EMPLOYEE
• PROFESSOR

• COURSE
• CLASS
• ENROLL (Bridge between STUDENT and CLASS)
• STUDENT

66
Components of the E-R Model
Table 4.2
67
Figure 4.48
68
Developing an E-R Diagram

• Converting an E-R Model into a Database Structure
• A painter might paint many paintings. The
  cardinality is (1,N) in the relationship between
  PAINTER and PAINTING.
• Each painting is painted by one (and only one)
  painter.
• A painting might (or might not) be exhibited in a
  gallery i.e., the GALLERY is optional to
  PAINTING.

69
Figure 4.49
70
Developing an E-R Diagram

• Summary of Table Structures and Special
  Requirements for the ARTIST database
• PAINTER(PRT_NUM, PRT_LASTNAME, PRT_FIRSTNAME,
  PRT_INITIAL, PTR_AREACODE, PRT_PHONE)
• GALLERY(GAL_NUM, GAL_OWNER, GAL_AREACODE,
  GAL_PHONE, GAL_RATE)
• PAINTING(PNTG_NUM, PNTG_TITLE, PNTG_PRICE,
  PTR_NUM, GAL_NUM)

71
A Data Dictionary for the ARTIST Database
Table 4.3
72
Developing an E-R Diagram

• SQL Commands to Create the PAINTER Table
• CREATE TABLE PAINTER (PTR_NUM CHAR(4) NOT
  NULL UNIQUE,PRT_LASTNAME CHAR(15) NOT
  NULL,PTR_FIRSTNAME CHAR(15),PTR_INITIAL CHAR(1),
  PTR_AREACODE CHAR(3),PTR_PHONE CHAR(8),PRIMARY
  KEY(PTR_NUM))

73
Developing an E-R Diagram

• SQL Commands to Create the GALLERY Table
• CREATE TABLE GALLERY (GAL_NUM CHAR(4) NOT
  NULL UNIQUE,GAL_OWNER CHAR(35),GAL_AREACODE CHAR
  (3) NOT NULL,GAL_PHONE CHAR(8) NOT NULL,
• GAL_RATE NUMBER(4,2),PRIMARY KEY(GAL_NUM))

74
Developing an E-R Diagram

• SQL Commands to Create the PAINTING Table
• CREATE TABLE PAINTING (PNTG_NUM CHAR(4) NOT
  NULL UNIQUE,PNTG_TITLE CHAR(35),PNTG_PRICE NUMBE
  R(9,2),PTR_NUM CHAR(4) NOT NULL,GAL_NUM CHAR(4),
  PRIMARY KEY(PNTG_NUM)FOREIGN KEY(PTR_NUM)
  RERERENCES PAINTER ON DELETE RESTRICT ON UPDATE
  CASCADE,FOREIGN KEY(GAL_NUM) REFERENCES
  GALLERY ON DELETE RESTRICT ON UPDATE CASCADE)

75
Developing an E-R Diagram

• General Rules Governing Relationships among
  Tables
• 1. All primary keys must be defined as NOT NULL.
• 2. Define all foreign keys to conform to the
  following requirements for binary relationships.
• 1M Relationship
• Weak Entity
• MN Relationship
• 11 Relationship

76
Developing an E-R Diagram

• 1M Relationships
• Create the foreign key by putting the primary key
  of the one (parent) in the table of the many
  (dependent).
• Foreign Key Rules

77
Developing an E-R Diagram

• Weak Entity
• Put the key of the parent table (strong entity)
  in the weak entity.
• The weak entity relationship conforms to the same
  rules as the 1M relationship, except foreign key
  restrictions
• NOT NULL
• ON DELETE CASCADE
• ON UPDATE CASCADE
• MN Relationship
• Convert the MN relationship to a composite
  (bridge) entity consisting of (at least) the
  parent tables primary keys.

78
Developing an E-R Diagram

• 11 Relationships
• If both entities are in mandatory participation
  in the relationship and they do not participate
  in other relationships, it is most likely that
  the two entities should be part of the same
  entity.

79
Developing an E-R Diagram

• CASE 1 MN, Both Sides MANDATORY

Figure 4.50 Entity Relationships, MN, Both
Sides Mandatory
80
Developing an E-R Diagram

• CASE 2 MN, Both Sides OPTIONAL

Figure 4.51 Entity Relationships, MN, Both
Sides Optional
81
Developing an E-R Diagram

• CASE 3 MN, One Side OPTIONAL

Figure 4.52 Entity Relationships, MN, One Side
Optional
82
Developing an E-R Diagram

• CASE 4 1M, Both Sides MANDATORY

Figure 4.53 Entity Relationships, 1M, Both
Sides Mandatory
83
Developing an E-R Diagram

• CASE 5 1M, Both Sides OPTIONAL

Figure 4.54 Entity Relationships, 1M, Both
Sides Optional
84
Developing an E-R Diagram

• CASE 6 1M, Many Side OPTIONAL, One Side
  MANDATORY

Figure 4.55 Entity Relationships, 1M, Many Side
Optional, One Side Mandatory
85
Developing an E-R Diagram

• CASE 7 1M, One Side OPTIONAL, One Side MANDATORY

Figure 4.56 Entity Relationships, 1M, One Side
Optional, Many Side Mandatory
86
Developing an E-R Diagram

• CASE 8 11, Both Sides MANDATORY

Figure 4.57 Entity Relationships, 11, Both
Sides Mandatory
87
Developing an E-R Diagram

• CASE 9 11, Both Sides OPTIONAL

Figure 4.58 Entity Relationships, 11, Both
Sides Optional
88
Developing an E-R Diagram

• CASE 10 11, One Side OPTIONAL, One Side
  MANDATORY

Figure 4.59 Entity Relationships, 11, One Side
Optional, One Side Mandatory
89
Developing an E-R Diagram

• CASE 11 Weak Entity (Foreign key located in weak
  entity)

Figure 4.60 Entity Relationships, Weak Entity
90
Developing an E-R Diagram

• CASE 12 Multivalued Attributes

Figure 4.61 Entity Relationships, Multivalued
Attributes
91
(No Transcript)
92
The Chen Representation of the Invoicing Problem
Figure 4.63
93
The Crows Foot Representation of the Invoicing
Problem
Figure 4.64
94
Figure 4.65 The Rein85 Representation of the
Invoicing Problem
95
The IDEF1X Representation of the Invoicing Problem
Figure 4.66
96
The Challenge of Database Design Conflicting
Goals

• Conflicting Goals
• Design standards (design elegance)
• Processing speed
• Information requirements
• Design Considerations
• Logical requirements and design conventions
• End user requirements e.g., performance,
  security, shared access, data integrity
• Processing requirements
• Operational requirements
• Documentation