Database Fundamentals- Content Analysis

profilewostinabin2
Week5-Lectureslides-20200617.zip
Home>Information Systems homework help>Database Fundamentals- Content Analysis

Chap 5.ppt

Database Principles: Fundamentals of Design, Implementations and Management

CHAPTER 5 Data Modelling

With Entity Relationship Diagrams

*

Objectives

  • In this chapter, you will learn:
  • The main characteristics of entity relationship components
  • How relationships between entities are defined, refined, and incorporated into the database design process
  • How ERD components affect database design and implementation
  • That real-world database design often requires the reconciliation of conflicting goals

*

The Entity Relationship (ER) Model

  • ER model forms the basis of an ER diagram
  • ERD represents conceptual database as viewed by the end user
  • ERDs depict database’s main components:
  • Entities
  • Attributes
  • Relationships

*

Entities

  • Refers to entity set and not to single entity occurrence
  • Corresponds to a table and not to row in relational environment
  • In Chen and Crow’s Foot models, entity is represented by a rectangle with an entity’s name
  • Entity name, a noun, written in capital letters

*

Attributes

  • Characteristics of entities
  • Chen notation: attributes represented by ovals connected to entity rectangle with a line
  • Each oval contains the name of attribute it represents
  • Crow’s Foot notation: attributes written in attribute box below entity rectangle

*

*

Attributes (cont..)

  • Required attribute: must have a value
  • Optional attribute: may be left empty
  • Domain: set of possible values for an attribute
  • Attributes may share a domain
  • Identifiers: one or more attributes that uniquely identify each entity instance
  • Composite identifier: primary key composed of more than one attribute

*

*

Attributes (cont..)

  • Composite attribute can be subdivided
  • Simple attribute cannot be subdivided
  • Single-value attribute can have only a single value
  • Multivalued attributes can have many values

*

Attributes (cont..)

  • M:N relationships and multivalued attributes should not be implemented
  • Create several new attributes for each of the original multivalued attributes components
  • Create new entity composed of original multivalued attributes components
  • Derived attribute: value may be calculated from other attributes
  • Need not be physically stored within database

*

*

Relationships

  • Association between entities
  • Participants are entities that participate in a relationship
  • Relationships between entities always operate in both directions
  • Relationship can be classified as 1:M
  • Relationship classification is difficult to establish if only one side of the relationship is known

*

Connectivity and Cardinality

  • Connectivity
  • Describes the relationship classification
  • Cardinality
  • Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity
  • Established by very concise statements known as business rules

*

*

Existence Dependence

  • Existence dependence
  • Entity exists in database only when it is associated with another related entity occurrence
  • Existence independence
  • Entity can exist apart from one or more related entities
  • Sometimes such an entity is referred to as a strong or regular entity

*

Relationship Strength

  • Weak (non-identifying) relationships
  • Exists if PK of related entity does not contain PK component of parent entity

  • Strong (identifying) relationships
  • Exists when PK of related entity contains PK component of parent entity

*

Weak (Non-Identifying) Relationships

*

Strong (Identifying) Relationships

*

Weak Entities

  • Weak entity meets two conditions
  • Existence-dependent
  • Cannot exist without entity with which it has a relationship
  • Has a primary key that is partially or totally derived from parent entity in relationship
  • Database designer usually determines whether an entity can be described as weak based on business rules

*

Strong Entity

Weak Entity

*

Weak Entities (cont..)

*

Relationship Participation

  • Optional participation
  • One entity occurrence does not require corresponding entity occurrence in particular relationship
  • Mandatory participation
  • One entity occurrence requires corresponding entity occurrence in particular relationship

*

Relationship Participation (cont..)

*

Relationship Participation (cont..)

*

Relationship Degree

  • Indicates number of entities or participants associated with a relationship
  • Unary relationship
  • Association is maintained within single entity
  • Binary relationship
  • Two entities are associated
  • Ternary relationship
  • Three entities are associated

*

Relationship Degree (cont..)

*

Relationship Degree (cont..)

*

Recursive Relationships

  • Relationship can exist between occurrences of the same entity set
  • Naturally found within unary relationship

*

Recursive Relationships (cont..)

*

Recursive Relationships (cont..)

*

Associative (Composite) Entities

  • Also known as bridge entities
  • Used to implement M:N relationships
  • Composed of primary keys of each of the entities to be connected
  • May also contain additional attributes that play no role in connective process

*

Composite Entities (cont..)

*

Composite Entities (cont..)

*

Developing an ER Diagram

  • Database design is iterative rather than linear or sequential process
  • Iterative process
  • Based on repetition of processes and procedures
  • Building an ERD usually involves the following activities:
  • Create detailed narrative of organization’s description of operations
  • Identify business rules based on description of operations
  • Identify main entities and relationships from business rules
  • Develop initial ERD
  • Identify attributes and primary keys that adequately describe entities
  • Revise and review ERD

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Developing an ER Diagram (cont..)

*

Database Design Challenges:
Conflicting Goals

  • Database designers must make design compromises
  • Conflicting goals:
  • design standards,
  • processing speed,
  • information requirements
  • Important to meet logical requirements and design conventions
  • Design of little value unless it delivers all specified query and reporting requirements
  • Some design and implementation problems do not yield “clean” solutions

*

Database Design Challenges: Conflicting Goals (cont.)

*

Summary

  • Entity relationship (ER) model
  • Uses ERD to represent conceptual database as viewed by end user
  • ERM’s main components:
  • Entities
  • Relationships
  • Attributes
  • Includes connectivity and cardinality notations
  • Multiplicities are based on business rules
  • In ERM, M:M relationship is valid at conceptual level
  • ERDs may be based on many different ERMs
  • Database designers are often forced to make design compromises