What is relationship called when an association is maintained within a single entity?

Admin

Slides are here .

Here’s a handy Crow’s Foot quick reference from Vivek M. Chawla. It doesn’t include attributes in the entities, but several authors and some company guidelines do include them when necessary to make a point.

Topics

• Conceptual to Logical to Physical models
• 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

Three models

We begin the database design process with the conceptual model. Various styles of notation are used across the industry. We will use the crows-foot style.

In the conceptual model we will build and ERD diagram which only addresses entities and relationships and cardinality.

The second model is the logical model. In this model we populate the entity representation from the previous model with attributes and their generic types. We also identify the keys to be used.

The third model is the physical model. This is the only one of the three models that actually considers the specific database technology to be used. The first two models could be used with any database technology and, so, are written generically. The physical model specifies the precise data types of the attributes of the entities (relations).

There are tools available to aid these design steps. In particular, MySQLWorkbench can turn a completed ERD directly into the MySQL commands to create the entities and relations. It’s less helpful in the earlier stages.

Entity Relationship Model (ERM)

The basis of an entity relationship diagram (ERD) which depicts the:

• Conceptual database as viewed by end user

• Database’s main components
  - Entities
  - Attributes - Relationships

• Entity refers to the entity set and not to a single entity occurrence

• Chen (of Chen diagrams) described how to generally map between English sentence structures and ERD’s in this table from his ERD paper:

  

He also did the same analysis for Chinese characters.

Attributes

• Characteristics of entities
  • Required attribute: Must have a value, cannot be left empty
  • Optional attribute: Does not require a value, can be left empty
• Domain: Set of possible values for a given attribute
• Identifiers or keys: One or more attributes that uniquely identify each entity instance
• Composite identifier: Primary key composed of more than one 1 attribute
  • (EmployeeNum, CountyCode)
• Composite attribute: Attribute that can be subdivided to yield additional attributes
  • e.g., Address
• Simple attribute: Attribute that cannot be subdivided
  • Most attributes are this way
• Single-valued attribute: Attribute that has only a single value at a time
  • Most attributes are this way

• Multivalued attributes: Attributes that have many values

  • Generally to be avoided unless necessary for performance or to match common app accesses.
  • Slides 2 and 3 A Multivalued Attribute in an Entity (CAR_COLOR)
  • These require creating:
    • Several new attributes, one for each component of the original multivalued attribute
    • A new entity composed of the original multivalued attribute’s components
• Derived attribute: Attribute whose value is calculated from other attributes
  • Derived using a formula or an algorithm
  • Slide 4 - Depiction of a Derived Attribute
  • Slide 5 - Advantages and Disadvantages of Storing Derived Attributes

Relationships

An association between entities, typically meaningful in both directions

• Participants: Entities that participate in a relationship
• Connectivity: Describes the relationship classification

• Cardinality: (optional) Expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity

  • Slide 7 - Connectivity and Cardinality in an ERD

    Strong vs. Weak entities and relationships

Entities can either exist on their own or they can only exist when associated with some other entity type.

• Strong entities
  • A strong entity can be uniquely identified by its own attributes.
  • Therefore, the entity’s existence does not depend on any other entity
  • e.g., a Dormitory can be uniquely identified by its name and location.
  • e.g., a US Bank is uniquely identified by its bank number.
• Weak entities
  • A weak entity cannot be uniquely identified by its own attributes.
  • Thus, you must add attributes to the weak entity to uniquely identify it.
    • This means you must extend the weak entity’s primary key to include one or more attributes from the parent entity as a foreign key.
    • e.g., a Room in a Dormitory needs the Dormitory information as part of its identity.
    • e.g., an Account may be identified by an AccountNumber, but it is meaningless without being associated with a Bank.
  • Alternatively, you could add a surrogate key to the weak entity.
    • These keys are not related to the entity’s real attributes, such as an AUTO INCREMENT , system date and time value, or some other generated value. Adding a surrogate key would turn it into a strong entity.

SLIDE 8

• Relationship strength

  • SLIDE 9
  • A weak or non-identifying relationship exists between two entities when the primary key of one of the related entities does not contain a primary key component of the other related entities.
  • A strong or identifying relationship is when the primary key of the related entity contains the primary key of the “parent”.

• Entity strength

  • Weak Entity

    SLIDE 10

    An entity is weak when two conditions are met:

    1. The entity is existence-dependent on another entity.
    2. The entity gets at least part of its primary key from that other entity.

    Database designers determine whether an entity is weak based on business rules

    • Dependent wouldn’t exist without an Employee
    • the primary key of Dependent is (EMP_NUM, DEP_NUM) which includes the primary key of Employee.

  • Strong entity

    • An entity that is existence-independent.

• Review the Crow’s Foot symbols

  Slide 12
  and two quick examples

  SLIDE 13-14

• Relationship Degree

  • Indicates the number of entities or participants associated with a relationship
  • Unary relationship: Association is maintained within a single entity
  • Recursive relationship: Relationship exists between occurrences of the same entity set
  • Binary relationship: Two entities are associated
  • Ternary relationship: Three entities are associated
  • Slide 15 - Three Types of Relationship Degree
  • Slide 16 - An ER Representation of Recursive Relationships

• Associative Entities

  • Also known as composite or bridge entities
  • Used ONLY in the ERD, they are NOT typically specified in the business rules.
  • Used to represent an M:N relationship between two or more entities
  • Is in a 1:M relationship with the parent entities
  • Composed of the primary key attributes of each parent entity
  • May also contain additional attributes that play no role in connective process
  • Slide 16 - Converting the M:N Relationship into Two 1:M Relationships
  • Slide 17 - A Composite Entity in an ERD

Developing an ER Diagram

1. Create a detailed narrative of the organization’s description of operations
2. Identify business rules based on the descriptions
3. Identify main entities and relationships from the business rules * Develop the initial ERD
4. Identify the attributes and primary keys that adequately describe entities
5. Revise and review ERD

• In class example in Slides 18-26
• Summary of example in Slide 27

Conflicting Goals

Only the simplest databases can achieve all of these guidelines and goals. You will often be faced with conflicting goals. Slide 28

• High update occurrence requirements can conflict with designs including many related entities
• common access patterns may drive changes to the design

The special case of the 1:1 recursive relationship

This kind of relationship occurs often and the first time you see it, it’s rather puzzling as to how to design and implement it. Slide 29

• How would you model these:
  • Patron can checkout 1+ or 0+ books?
  • Library has 1+ books?
  • A book may have been checked out by many patrons
    • (hint: a many to many relationship, so fix with a helper “Checkout Order”