Entity-Relationship Model In DBMS

The basis of an entity relationship Model (ERM) which depicts the:

• Conceptual database as viewed by end user
• Database’s main components
  - Entities
  - Attributes - Relationships
• Chen (of Chen diagrams) described how to generally map between English sentence structures and ERD’s in this table from his ER Diagram paper:

He also did the same analysis for Chinese characters.

Attributes

• Identifiers or keys: One or more attributes that uniquely identify each entity instance
• 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 3A Multivalued Attribute in an Entity (CAR_COLOR)
  • These require creating:
    • Several new attributes, one for each
    • component of the original multivalued attribute
  • 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

Entities: Strong vs. Weak

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
  • 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.
    • g., a Room in a Dormitory needs the Dormitory information as part of its identity.
    • g., an Account may be identified by an Account Number, but it is meaningless without being associated with a Bank.
  • Alternatively, you could add a surrogatekey to the weak entity.
    • These keys are not related to the entity’s real attributes, such as an AUTO INCREMENTor other generated value. Adding a surrogate key would turn it into a strong entity

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 6- Connectivity and Cardinality in an ERD
• Existence Dependence/Independence
  • Some Entities exists in the database only when associated with another entity occurrence
  • Others exist on their own without dependence on other entities existence
• Relationship strength
  • A weak or non-identifyingrelationship 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.
  • Slide 7- A Weak (Non-Identifying) Relationship between COURSE and CLASS
  • A strong or identifyingrelationship is when the primary key of the related entity contains the primary key of the “parent”.
    • Slide 8- A Strong (Identifying) Relationship between COURSE and CLASS
  • Entity strength
    • WeakEntity
      1. The entity is existence-dependent on another entity.
      2. The entity gets at least part of its primary keyfrom that other entity.

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

• Slide 9- A Weak Entity in an ERD
• Slide 10- A Weak Entity in a Strong Relationship
  • Dependentwouldn’t exist without an Employee
  • the primary key of Dependentis (EMP_NUM, DEP_NUM) which includes the primary key of Employee.
• Strongentity
  • An entity that is existence-independent.
• Review the Crow’s Foot symbols
  • Slide 11
  • Slides 12 & 13
• Relationship Degree
  • Binary relationship: Two entities are associated
  • Ternary relationship: Three entities are associated
  • Slide 14- Three Types of Relationship Degree
  • Slide 15- An ER Representation of Recursive Relationships
• Associative Entities
  • Also known as composite or bridge entities
  • Used ONLYin the ERD, they are NOT typically specified in the business rules.
  • 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. Identify business rules based on the descriptions, ER Diagram.
2. Identify main entities and relationships from the business rules * Develop the initial ERD
3. 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

Slide 28 The special case of the 1:1 recursive relationship

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

Discuss these sample implementations

Example (time permitting): Library book catalog

• Patron can checkout 1+ or 0+ books?
• Library has 1+ books?
• A book may have been checked out by many patrons
  • (thus, a many to many)
  • fix with a helper “Checkout Order”

Read more – Use My Notes