System Development Life Cycle (SDLC): Entity-Relationship Diagram
INTRODUCTION:
I recently developed a database system for a sports club following the System Development Lifecycle and learnt about the ERD. The Entity Relationship Diagram (ERD) serves as a crucial tool for understanding and representing the relationships between entities within a database.
By using an ERD, I can understand the relationships between entities better and design efficient SQL queries to extract important information for analysis in the sports club's data model. It is a pivotal tool in the database’s design and implementation, ensuring a coherent and efficient structure that underpins the system’s functionality and data connections.
Sports Club Database Overview:
Entities:
- Club Members: Unique membership numbers, personal details (name, address, contact info, DOB, medical conditions).
- Staff: Unique staff numbers, names, roles, contact numbers.
- Sports Classes: Unique class codes, details (title, day, time).
- Non-Class Activities: Booking rules (weekly booking limits, restrictions, time slots).
- Attendance: Recording for staff.
- Administrators: Manage system functionality.
- Bookings: Members’ activity bookings.
System Features:
- Activity search.
- Personal details management.
- Staff attendance recording.
- Viewing activity bookings for members.
The Sports Club database aims to efficiently manage member and staff data, sports class and non-class activity information, attendance, and bookings to provide a seamless experience for club members and staff.
The Entity Relationship Diagram (ERD):
Cardinalities:
The relationships and cardinalities in the entity relationship diagram (ERD) are designed based on the many-to-one and one-to-many relationship sets.
The many-to-one relationship is formed when multiple elements of an entity are related to a single element of another entity. On the other hand, one-to-many relationship is formed when a single element of an entity is associated with multiple elements of another entity
Attendance: This entity relates to a specific class, indicating a many-to-one relationship, where multiple attendance records can be linked to one class. Staff members and instructors can record attendance for multiple instances, establishing a one-to-many relationship.
Classes: Staff members teach classes, creating a many-to-one relationship, where one staff member can teach multiple classes. A class can have multiple attendance records and bookings, representing a one-to-many relationship. Club members can offer multiple classes, establishing a one-to-many relationship.
Activities: Instructors teach activities, indicating a many-to-one relationship, where one instructor can teach multiple activities. Club members can offer multiple activities, establishing a one-to-many relationship. Activities can have multiple bookings and attendances, representing one-to-many relationships.
Bookings: Club members can make multiple bookings, establishing a many-to-one relationship. Administrators can view and manage multiple bookings, creating a one-to-many relationship. Classes and activities can have multiple bookings, indicating one-to-many relationships.
Club Member: Club members are associated with activities, classes, bookings, and attendance. This entity has one-to-many relationships with all other entities, as club members can make multiple bookings, have multiple attendance records, and offer multiple classes and activities.
Staff: Staff members can teach multiple classes, representing a one-to-many relationship. Club staff members can also record attendance for multiple classes.
Instructor: Instructors can teach multiple classes, establishing a one-to-many relationship.
Administrator: The club administrator can manage multiple bookings and attendance records. This entity has a one-to-many relationship with attendance and booking entities.
INSIGHTS AND KEY TAKEAWAYS:
The established relationships and cardinalities guarantee accurate associations among system entities. This streamlined data management enables efficient tracking of attendance, classes, activities, bookings, members, and staff within the system. The Entity Relationship Model serves as a robust foundation, fostering a thorough grasp of data relationships that can be seamlessly implemented through SQL queries. This, in turn, empowers effective data management and operations for the sports club system.
THANK YOU!
Let’s connect on LinkedIn:
