Lab 12

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Challenge 2

Normalization Issues

• Officer and trainer info is repeated
  • officer_name and officer_rank depend on officer_id rather than the entire key
  • trainer_name and trainer_rank only depend only trainer_id
• Multiple values are packed together
  • qualification_name and qualification_level together are used to describe a qualification type, not just for this specific assignment

Proper Normalization

• To properly normalize the database, we should break it down into separate entities
  • TRAINERS(trainer_id, name, rank)
  • QUALIFICATION(qualification_id, name, level)
  • OFFICER_QUALIFICATIONS (junction table to link officers/qualifications/trainers)

ERD Diagram

---
config:
  layout: elk
---

erDiagram
    OFFICERS {
        VARCHAR(10) officer_id PK
        VARCHAR(100) name
        VARCHAR(50) rank
        VARCHAR(50) specialization
        INT years_service
    }

    TRAINERS {
        VARCHAR(10) trainer_id PK
        VARCHAR(100) name
        VARCHAR(50) rank
    }

    QUALIFICATIONS {
        VARCHAR(10) qualification_id PK
        VARCHAR(100) name
        VARCHAR(50) level
    }

    OFFICER_QUALIFICATIONS {
        VARCHAR(10) officer_qualification_id PK
        VARCHAR(10) officer_id FK
        VARCHAR(10) qualification_id FK
        VARCHAR(10) trainer_id FK
        DATE date_acquired
    }

    OFFICERS ||--o{ OFFICER_QUALIFICATIONS : earns
    QUALIFICATIONS ||--o{ OFFICER_QUALIFICATIONS : includes
    TRAINERS ||--o{ OFFICER_QUALIFICATIONS : awards

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───