Comprehensive Database ER Modeling and Relational Translation Notes

ER Diagram Fundamentals and Entity Rules

• Entity-Relationship Connection Rule: A direct line can never be drawn between two entities. A relationship object (represented by a diamond shape) must always be placed between them (e.g., establishing a relationship between a student entity and a professor entity).
• Relationship-Relationship Connection Rule: Drawing a direct line between two relationships without an intervening entity is strictly invalid and leads to a loss of points. An entity must always sit between two relationships.
• Definition of an Entity: An entity is an object described by a set of attributes (e.g., a Student entity).
• Unique Keys (Primary Keys):
  • An attribute that uniquely identifies a specific entity instance (e.g., Student ID).
  • Represented in Entity-Relationship (ER) diagrams by underlining the attribute name.
  • An entity cannot exist within a database schema without a unique key, which may consist of a single attribute or multiple attributes.
• Employee and Department Attributes Example:
  • Employee Entity Attributes: SSN, Name, Parking Lot. Represented in an ER diagram as a rectangle for the entity connected to attribute nodes.
  • Department Entity Attributes: Number, Name, Budget.
  • Unique Identification: Employee is uniquely identified by SSN; Department is uniquely identified by Number. Department also has Number and Name as candidate keys.
• Data Types and Storage Optimizations for Identifiers:
  • In industrial practice, unique IDs are standardly stored as fixed-length text/character strings rather than numeric integer or floating-point types to optimize storage.
  • Standard integer storage requires $64\,\text{bytes}$ of memory space.
  • Character storage requires $8\,\text{bytes}$ per character.
  • For a 4-character ID string, the total memory footprint is $4 \times 8\,\text{bytes} = 32\,\text{bytes}$, which uses significantly less storage space than a $64\,\text{bytes}$ integer.

Relationships, Attributes, and Table Translation

• Diagrammatic Representation: Relationships between two entities are drawn using diamond shapes and can be given any descriptive name (e.g., works_in).
• Placement of Temporal / Descriptive Attributes:
  • Descriptive temporal attributes (e.g., since to represent John starting work in August 2005) belong on the relationship diamond (works_in), not on the entity rectangle, if they describe the interaction between the entity and a specific department.
  • Placing since on the Employee entity level indicates general employment start date at the company, completely detached from specific department assignments.
• Key Prohibition on Relationships: Relationships can hold descriptive attributes, but a relationship never possesses a primary key or unique identifier. Uniqueness belongs exclusively to entities.
• Translating Relationships into Relational Tables:
  • Employee Entity Table: Stores SSN, Name, Parking Lot.
  • Department Entity Table: Stores Number, Name, Budget.
  • works_in Relationship Table: Formed by combining the primary key of Employee (SSN), the primary key of Department (Number), and the relationship attribute (since).
  • Composite Primary Key: The combination of $\mathbf{(SSN, Number)}$ forms the composite key of the relationship table. A composite key is defined as a primary key constructed from more than one attribute.
• Handling Temporal Ranges (until) and Null Values:
  • An until attribute tracks when an employee ceases working in a department.
  • If an employee is currently active in the department, the until attribute value is set to NULL and populated later upon departure.
• Impact of Organizational Policies on Schema Design:
  • If organizational policy allows employees to switch departments over time, since and until must be maintained at the relationship table level.
  • If organizational policy dictates that an employee can belong to exactly one department throughout their entire employment, the since attribute shifts directly to the Employee entity level.

N-ary, Recursive, and Structural Constraints

• Relationship Directionality: Relationships possess inherent directionality (e.g., an employee working in a department vs. a department employing an employee). Directional constraints dictate how relationships are drawn and constrained.
• N-ary (Ternary) Relationships:
  • Relationships can link more than two entities simultaneously (e.g., a 3-way ternary relationship among Employee, Department, and Locations).
  • Scenario: John works in the Sales department at the Twin Cities campus location.
  • Locations Entity Attributes: Address (unique identifier), Capacity.
  • Rationale for Modeling Location as an Entity: Large enterprises (e.g., Google, Microsoft) maintain identical departments across diverse geographic sites (e.g., Seattle, California, Twin Cities). Modeling location as an independent entity accommodates location-specific properties, capacities, rates, and managerial structures.
• Recursive / Self-Referential Relationships:
  • A relationship where an entity relates to itself (e.g., an Employee acting as a supervisor to another Employee who acts as a subordinate/supervisee).
  • Diagramming: Drawn by extending two separate connecting lines from a single entity rectangle (Employee) to a central relationship diamond (supervises).
  • Role Labels: Each connecting line must be explicitly labeled with its respective role (e.g.,