Skip to content

Phase 3 — Mapping from EER to Relational Model

This section explains how the conceptual EER model from Phase 2 was transformed into the relational schema.

EER Diagram Data Dictionary Normalization

1. Mapping Strategy

The mapping process followed standard EER-to-relational rules:

  • Each strong entity became a base relation.
  • 1:N relationships were mapped with foreign keys on the N-side.
  • Domain restrictions were captured with ENUM columns.
  • Integrity and validity rules were captured with PK/FK/CHECK constraints.

2. Entity-to-Relation Mapping

The following entities were mapped to relations:

  • User -> USER
  • Clinic -> CLINIC
  • HealthCondition -> HEALTH_CONDITION
  • AwarenessContent -> AWARENESS_CONTENT
  • FamilyMember -> FAMILY_MEMBER
  • MedicalHistory -> MEDICAL_HISTORY
  • HealthEvent -> HEALTH_EVENT
  • RiskAlert -> RISK_ALERT
  • Appointment -> APPOINTMENT

3. Relationship Mapping

3.1 Core 1:N Relationships

  • FAMILY_MEMBER.user_id -> USER.user_id
  • MEDICAL_HISTORY.member_id -> FAMILY_MEMBER.member_id
  • MEDICAL_HISTORY.condition_id -> HEALTH_CONDITION.condition_id
  • RISK_ALERT.member_id -> FAMILY_MEMBER.member_id
  • APPOINTMENT.user_id -> USER.user_id
  • APPOINTMENT.clinic_id -> CLINIC.clinic_id

3.2 Extended Event Mapping

HEALTH_EVENT supports optional linkage to both family members and conditions:

  • HEALTH_EVENT.member_id -> FAMILY_MEMBER.member_id (nullable)
  • HEALTH_EVENT.condition_id -> HEALTH_CONDITION.condition_id (nullable)

This supports both linked and partially specified event records.

4. Constraint Mapping

Relational constraints preserve conceptual rules:

  • Entity integrity: primary keys on all relations
  • Referential integrity: foreign keys across dependent relations
  • Domain integrity: ENUM constraints for controlled statuses/categories
  • Validation integrity: CHECK constraints for business rules

5. Normalization Readiness

The mapping separates independent subjects into their own relations. For example, clinic details are stored in CLINIC, health condition details are stored in HEALTH_CONDITION, and dependent records reference them through foreign keys. This supports 1NF, 2NF, and 3NF by avoiding repeating groups, partial dependencies, and transitive dependencies.

6. Implementation Readiness

The resulting relational schema is normalized, dependency-safe, and ready for MySQL DDL implementation in Phase 4.