Chapter 4 Entity Relationship ER Modeling - PowerPoint PPT Presentation

Loading...

PPT – Chapter 4 Entity Relationship ER Modeling PowerPoint presentation | free to download - id: 130076-ODVlM



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Chapter 4 Entity Relationship ER Modeling

Description:

Database design is both art and science. A data model is the relatively ... related to several unique and disjointed (nonoverlapping) subtype entity sets. ... – PowerPoint PPT presentation

Number of Views:326
Avg rating:3.0/5.0
Slides: 53
Provided by: chan227
Learn more at: http://www.cs.pu.edu.tw
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
About PowerShow.com