Lecture on Table Normalization and Entities

Introduction

• Discussion about structuring data with IDs and entities.
• Concept of parent and child fields in a database table.

Identifying Entities

• Entities include: Beach, Rubbish, District, Cleanups.
• Live entities: Volunteer, Lifeguard (though Lifeguard lacks an ID).
• Non-live entities: District, Beach, Rubbish, etc.

Dependency Concept

• Dependency Definition: A dependent field has the same data for the same ID.
• Examination of fields for dependency on ID.

Example: Volunteer ID

• Non-Dependent Fields:
  • Rubbish collected: different values for same volunteer ID.
  • Number of cleanups: varies for volunteer ID.
• Dependent Fields:
  • Volunteer Mobile: same for volunteer ID.
  • Volunteer Surname: same for volunteer ID.

Managing Tables

• Move dependent fields with ID into a separate table.
• Leave a copy of the ID in the original table for linking.

Example: Beach ID

• Non-Dependent Fields:
  • Rubbish collected: varies with same beach ID.
  • Number of cleanups: not consistent.
• Dependent Fields:
  • Beach Name: matches beach ID.
  • Beach Type: unique to beach ID.

Special Considerations

• Lifeguard appears dependent but actual assignment considered.
• Discussed common sense in assignment.

Example: District ID

• Examination of fields for dependency on district ID.
• Dependent Fields:
  • District Name: matches district ID.
• Management of volunteer ID due to overlap with districts.

Handling Complex Relationships

• Volunteers may belong to multiple districts.
• Importance of maintaining IDs in linking tables.

Lifeguard Assignment

• Considered practical assignment of lifeguards to beaches, not volunteers.
• Use of common sense for logical organization.

Conclusion

• Successfully created four tables (Volunteer, Beach, District, Beach Cleaning).
• Discussed the importance of dependencies and logical organization of data.