Transcript Entity Relationship (E

Chapter 4
4
Entity Relationship (E-R) Modeling
Database Systems: Design, Implementation, and Management
4th Edition
Peter Rob & Carlos Coronel
Basic Modeling Concepts
 Database design is both art and science.
4
 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.
4
Figure 4.1 Four Modified (ANSI/SPARC) Data Abstraction Models
Data Models: Degrees of Data Abstraction
 The Conceptual Model
4

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).
Tiny College Entities
4
Figure 4.2
A Conceptual Schema for Tiny College
4
Figure 4.3
Data Models: Degrees of Data Abstraction
 The Internal Model
4

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.
4
Figure 4.4
Data Models: Degrees of Data Abstraction
 The External Model
4

The external model is the end user’s view of the
data environment.

Each external model is then represented by its
own external schema.
CREATE VIEW CLASS_VIEW AS
SELECT (CLASS_ID, CLASS_NAME, PROF_NAME,
CLASS_TIME, ROOM_ID)
FROM CLASS, PROFESSOR, ROOM
WHERE CLASS.PROF_ID = PROFESSOR.PROF_ID
AND CLASS.ROOM_ID = ROOM.ROOM_ID;
4
Figure 4.5
The External Models
for Tiny College
Data Models: Degrees of Data Abstraction
 The External Model

4
Advantages of Using External Schemas

It makes application program development much
simpler.

It facilitates the designer’s task by making it easier
to identify specific data required to support each
business unit’s operations.

It makes the designer’s job easier by providing
feedback about the conceptual model’s adequacy.

It helps to ensure security constraints in the
database design.
Data Models: Degrees of Data Abstraction
 The Physical Model
4

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.
The Entity Relationship (E-R) Model
 E-R model is commonly used to:
4

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.
The Entity Relationship (E-R) Model
 E-R Model Components

4
Entities



Attributes






In E-R models an entity refers to the entity set.
An entity is represented by a rectangle containing the
entity’s name.
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 attribute’s set of possible
values.
Attributes may share a domain.
Primary keys are underlined.
Relationships
The Attributes of the STUDENT Entity
4
Figure 4.6
Basic E-R Model Entity Presentation
4
Figure 4.7
The CLASS Table (Entity) Components and Contents
4
Figure 4.8
The Entity Relationship (E-R) Model
 Classes of Attributes

4
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
The Entity Relationship (E-R) Model
 Classes of Attributes

A single-valued attribute can have only a single value.

4

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.
The Entity Relationship (E-R) Model
 Multivalued Attribute in Relational DBMS

4

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 attribute’s
components (Figure 4.9).
Create a new entity composed of the original multivalued
attribute’s components (Figure 4.10).
Table 4.1
Splitting the Multivalued Attributes into New Attributes
4
Figure 4.9
A New Entity Set Composed of Multivalued
Attribute’s Components
4
Figure 4.10
The Entity Relationship (E-R) Model

A derived attribute is not physically stored within the
database; instead, it is derived by using an algorithm.

4
Example: AGE can be derived from the data of birth and
the current date.
Figure 4.11 A Derived Attribute
The Entity Relationship (E-R) Model
 Relationships

4

A relationship is an association between entities.
Relationships are represented by diamond-shaped
symbols.
Figure 4.12 An Entity Relationship
The Entity Relationship (E-R) Model
 A relationship’s degree indicates the number of associated
entities or participants.

4
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.
The Implementation of a Ternary Relationship
4
Figure 4.14
The Entity Relationship (E-R) Model
 Connectivity

4
The term connectivity is used to describe the
relationship classification (e.g., one-to-one, one-tomany, and many-to-many).
Figure 4.15 Connectivity in an ERD
The Entity Relationship (E-R) Model
 Cardinality

4
Cardinality expresses the specific number of entity
occurrences associated with one occurrence of the
related entity.
Figure 4.16 Cardinality in an ERD
4
Figure 4.17
 Existence Dependency

If an entity’s existence depends on the existence of one
or more other entities, it is said to be existencedependent.
4
Figure 4.18
The Entity Relationship (E-R) Model
 Relationship Participation
4

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
Figure 4.20 CLASS is Optional to COURSE
4
Figure 4.21 COURSE and CLASS in a Mandatory Relationship
The Entity Relationship (E-R) Model
 Weak Entities

4
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.
A Weak Entity in an ERD
4
Figure 4.22
An Illustration of the Weak Relationship Between
DEPENDENT and EMPLOYEE
4
Figure 4.23
The Entity Relationship (E-R) Model
 Recursive Entities
4

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
Figure 4.25
4
Figure 4.26
The Implementation of the M:N Recursive
“PART Contains PART” Relationship
4
Figure 4.27
Implementation of the M:N “COURSE Requires COURSE”
Recursive Relationship
4
Figure 4.28
Implementation of the 1:M “EMPLOYEE Manages EMPLOYEE”
Recursive Relationship
4
Figure 4.29
The Entity Relationship (E-R) Model
 Composite Entities
4

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.
Converting the M:N Relationship Into Two 1:M Relationships
4
Figure 4.30
The M:N Relationship Between STUDENT and CLASS
4
Figure 4.31
A Composite Entity in the ERD
4
Figure 4.32
The Entity Relationship (E-R) Model
 Entity Supertypes and Subtypes
4
Figure 4.33 Nulls Created by Unique Attributes
The Entity Relationship (E-R) Model
 Entity Supertypes and Subtypes
4

The generalization hierarchy depicts the parentchild 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.
A Generalization Hierarchy
4
Figure 4.34
The Entity Relationship (E-R) Model
 Entity Supertypes and Subtypes
4

The supertype entity set is usually related to
several unique and disjointed (nonoverlapping)
subtype entity sets.

The supertype and its subtype(s) maintain a 1:1
relationship.
The EMPLOYEE/PILOT Supertype/Subtype Relationship
4
Figure 4.35
The Entity Relationship (E-R) Model
 Entity Supertypes and Subtypes
4

The generalization hierarchy depicts the parentchild 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 1:1
relationship.
A Generalization Hierarchy With Overlapping Subtypes
4
Figure 4.36
4
Figure 4.37
Chapter 4
4
Entity Relationship (E-R) Modeling
Database Systems: Design, Implementation, and Management
4th Edition
Peter Rob & Carlos Coronel
Developing an E-R Diagram
 The process of database design is an iterative rather
than a linear or sequential process.
4
 It usually begins with a general narrative of the
organization’s 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 organization’s activities and
functions.
Developing an E-R Diagram
 Tiny College Database (1)
4

Tiny College (TC) is divided into several schools.
Each school is administered by a dean. A 1:1
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)
4
Developing an E-R Diagram
 Tiny College Database (2)

4
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
Developing an E-R Diagram
 Tiny College Database (3)

Each department offers several courses.
4
Figure 4.41 The Second Tiny College ERD Segment
Developing an E-R Diagram
 Tiny College Database (4)

4


A department may offer several sections (classes) of
the same course.
A 1:M relationship exists between COURSE and CLASS.
CLASS is optional to COURSE
Figure 4.42 The Third Tiny College ERD Segment
Developing an E-R Diagram
 Tiny College Database (5)

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.
Figure 4.43 The Fourth Tiny College ERD Segment
Developing an E-R Diagram
 Tiny College Database (6)

4

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
Developing an E-R Diagram
 Tiny College Database (7)

4


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
Developing an E-R Diagram
 Tiny College Database (8)

4

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
Developing an E-R Diagram
 Tiny College Database (9)

4

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
Developing an E-R Diagram
Entities for the Tiny College Database
4
 SCHOOL
 COURSE
 DEPARMENT
 CLASS
 EMPLOYEE
 ENROLL (Bridge between
STUDENT and CLASS)
 PROFESSOR
 STUDENT
Components of the E-R Model
4
Table 4.2
4
Figure 4.48
Developing an E-R Diagram
 Converting an E-R Model into a Database Structure
4

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.
4
Figure 4.49
Developing an E-R Diagram
 Summary of Table Structures and Special
Requirements for the ARTIST database
4
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)
A Data Dictionary for the ARTIST Database
4
Table 4.3
Developing an E-R Diagram
SQL Commands to Create the PAINTER Table
4
CREATE TABLE PAINTER (
PTR_NUM
CHAR(4)
PRT_LASTNAME
CHAR(15)
PTR_FIRSTNAME CHAR(15),
PTR_INITIAL
CHAR(1),
PTR_AREACODE
CHAR(3),
PTR_PHONE
CHAR(8),
PRIMARY KEY(PTR_NUM));
NOT NULL UNIQUE,
NOT NULL,
Developing an E-R Diagram
SQL Commands to Create the GALLERY Table
4
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));
Developing an E-R Diagram
SQL Commands to Create the PAINTING Table
4
CREATE TABLE PAINTING (
PNTG_NUM
CHAR(4)
NOT NULL UNIQUE,
PNTG_TITLE
CHAR(35),
PNTG_PRICE
NUMBER(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);
Developing an E-R Diagram
 General Rules Governing Relationships
among Tables
4
1. All primary keys must be defined as NOT NULL.
2. Define all foreign keys to conform to the following
requirements for binary relationships.

1:M Relationship

Weak Entity

M:N Relationship

1:1 Relationship
Developing an E-R Diagram
 1:M Relationships

4

Create the foreign key by putting the primary key of the
“one” (parent) in the table of the “many” (dependent).
Foreign Key Rules:
Null
On Delete
On Update
If both sides are
MANDATORY
NOT NULL
RESTRICT
CASCADE
If both sides are
OPTIONAL
NULL
ALLOWED
SET NULL
CASCADE
If one side is
OPTIONAL and
the other
MANDATORY
NULL
ALLOWED
SET NULL
or
RESTRICT
CASCADE
Developing an E-R Diagram
 Weak Entity

4

Put the key of the parent table (strong entity) in the
weak entity.
The weak entity relationship conforms to the same
rules as the 1:M relationship, except foreign key
restrictions:
NOT NULL
ON DELETE CASCADE
ON UPDATE CASCADE
 M:N Relationship

Convert the M:N relationship to a composite (bridge)
entity consisting of (at least) the parent tables’ primary
keys.
Developing an E-R Diagram
 1:1 Relationships

4
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.
Developing an E-R Diagram
 CASE 1: M:N, Both Sides MANDATORY
4
Figure 4.50 Entity Relationships, M:N, Both Sides Mandatory
Developing an E-R Diagram
 CASE 2: M:N, Both Sides OPTIONAL
4
Figure 4.51 Entity Relationships, M:N, Both Sides Optional
Developing an E-R Diagram
 CASE 3: M:N, One Side OPTIONAL
4
Figure 4.52 Entity Relationships, M:N, One Side Optional
Developing an E-R Diagram
 CASE 4: 1:M, Both Sides MANDATORY
4
Figure 4.53 Entity Relationships, 1:M, Both Sides Mandatory
Developing an E-R Diagram
 CASE 5: 1:M, Both Sides OPTIONAL
4
Figure 4.54 Entity Relationships, 1:M, Both Sides Optional
Developing an E-R Diagram
 CASE 6: 1:M, Many Side OPTIONAL, One Side
MANDATORY
4
Figure 4.55
Entity Relationships, 1:M, Many Side Optional, One Side Mandatory
Developing an E-R Diagram
 CASE 7: 1:M, One Side OPTIONAL, One Side
MANDATORY
4
Figure 4.56
Entity Relationships, 1:M, One Side Optional, Many Side Mandatory
Developing an E-R Diagram
 CASE 8: 1:1, Both Sides MANDATORY
4
Figure 4.57 Entity Relationships, 1:1, Both Sides Mandatory
Developing an E-R Diagram
 CASE 9: 1:1, Both Sides OPTIONAL
4
Figure 4.58 Entity Relationships, 1:1, Both Sides Optional
Developing an E-R Diagram
 CASE 10: 1:1, One Side OPTIONAL, One Side
MANDATORY
4
Figure 4.59
Entity Relationships, 1:1, One Side Optional, One Side Mandatory
Developing an E-R Diagram
 CASE 11: Weak Entity (Foreign key located in weak
entity)
4
Figure 4.60 Entity Relationships, Weak Entity
Developing an E-R Diagram
 CASE 12: Multivalued Attributes
4
Figure 4.61 Entity Relationships, Multivalued Attributes
4
The Chen Representation of the Invoicing Problem
4
Figure 4.63
The Crow’s Foot Representation of the Invoicing Problem
4
Figure 4.64
4
Figure 4.65 The Rein85 Representation of the Invoicing Problem
The IDEF1X Representation of the Invoicing Problem
4
Figure 4.66
The Challenge of Database Design:
Conflicting Goals
 Conflicting Goals
4



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