Notes on Entity Relationship Modelling and Diagrams

Database Creation Overview

  • Creating a table is straightforward, and so is creating multiple tables.
  • The complexity arises when deciding what data belongs in each table.
  • Entity Relationship Models (ERM) and Entity Relationship Diagrams (ERD) help design the structure of database tables.

Entity Relationship Model & Diagram

Entity Relationship Model (ERM)

  • A logical representation of data needed by an organization.
  • Utilizes entities to symbolize people, objects, events, etc.
  • Identifies relationships between various entities.
  • Based on the business rules of the organization.

Entity Relationship Diagram (ERD)

  • A graphical representation of the ERM.
  • ER diagrams do not store data; they serve as a blueprint for database design.

ERDs vs Access Relationship Diagrams

  • An ERD uses natural keys (real-world identifiers) without surrogate keys.
  • An Access Relationship Diagram visualizes existing database tables with surrogate keys if used.
  • ERDs are created prior to database construction; Access diagrams arise post-construction.

E-R Diagrams Components

Entities

  • Main components in the E-R Diagram include entities representing objects like "Subject" and "Lecturer".

Attributes

  • Attributes describe the properties of entities. For example, for Subject and Lecturer:
    • Subject Attributes: SubjectCode, Title, CreditPoints.
    • Lecturer Attributes: LecId, LecName, Age.

Relationships

  • Relationships depict connections between entities. For example, there is a clear relationship between 'Subject' and 'Lecturer'.

E-R Diagrams & Sample Data

  • When designing an ER Diagram, consider the actual data:
    • Subject Data:
    • INF10002: Database, Analysis & Design, Credits: 12.5
    • INF10003: Intro to Business Info Systems, Credits: 12.5
    • Lecturer Data:
    • LecId: 207, Name: John Smith, Age: 37
    • LecId: 119, Name: Jane Pitt, Age: 26

E-R Diagrams & Business Rules

  • Business Rules examples:
    • Students must enroll in one course at a time.
    • A Student can enroll in multiple subjects.
    • A subject can have only one convenor.
    • An employee must have one tax file number.
  • Some rules are common across organizations, while others are unique.
  • Business rules should be documented as they guide data requirements.

Discovering Business Rules

  • Challenges in discovering business rules include:
    • No individual knows all rules.
    • Individuals may be unaware of some rules or not disclose them due to fear or distrust.
    • Lack of documentation can hinder rule identification.
    • Business needs and rules evolve over time.

Cardinality Constraints

  • Cardinality denoted how many instances of one entity relate to another.
  • Examples for lecturers and subjects:
    • One lecturer convenes one subject (drawn as ONE).
    • One subject can have many lecturers (drawn as MANY or in a crow's foot).

Determining Cardinality

  • Convened By Relationship:
    • LEFT end: For a specific subject, only one lecturer can convene it (ONE).
    • RIGHT end: A lecturer can convene multiple subjects (MANY).
  • Read diagrams correctly to frame sentences:
    • "One Subject is convened by ONE Lecturer".
    • "One Lecturer convenes MANY Subjects".

Summary of Cardinality Constraints

  • A single Subject is convened by ONE Lecturer, and ONE Lecturer can convene MANY Subjects.
  • Initiate sentences with "ONE" to maintain clarity.

Entities and Instances

  • An entity instance indicates a specific set of attribute values.
    • Example: One instance could be LecId: 207, LecName: John Smith, Age: 37.

E-R Diagrams – Identifiers

  • Identifiers uniquely identify an entity instance (similar to a Primary Key).
  • Rules for identifiers include:
    • Every entity must possess an identifier.
    • Unique identifiers for entity instances – no duplicates.
    • Identifiers must not be empty or null.
  • Identifiers are underlined in the ERD.