EER Model Design for a Country Club Membership and Guest Management System

 You are working for country club with thousands of members. You have been tasked with designing a database to keep track of the members and their guests.

The club keeps track of mail and telephone contact information, name and membership number. When a member joins the club they can become a social member with pool, racket ball and weight room privilieges or golf member which includes all of the social member privileges plus access to the golf course.

Develop, document and design an EER for this situation

Answer:

To design an Enhanced Entity-Relationship (EER) diagram for a country club that tracks members and their guests, along with membership types and privileges, follow these steps:

---

Entities and Attributes

1. Member

   • MemberID (Primary Key)

   • Name

   • Address

   • Phone

   • Email

   • MembershipType (Social or Golf)

2. Guest

   • GuestID (Primary Key)

   • Name

   • ContactInfo

   • InvitedBy (Foreign Key → MemberID)

3. Membership

   • MembershipType (Social, Golf)

   • Privileges (Pool, Racket Ball, Weight Room, Golf Course)

---

Relationships

• Member can invite many Guests (1-to-Many relationship between Member and Guest)

• Member has a MembershipType (Many-to-1 relationship: Many members can share the same membership type)

• Golf Member is a specialization of Social Member (Inheritance in EER)

---

EER Design Details

• Every member must have personal and contact information.

• Each member has only one membership type: either Social or Golf.

• Social members have access to pool, racket ball, and weight room.

• Golf members inherit all social privileges plus access to the golf course.

• Each guest is associated with the member who invited them.

---

EER Diagram Description

• Member (Superclass)

  • SocialMember (Subclass)

    • Privileges: Pool, Racket Ball, Weight Room

  • GolfMember (Subclass)

    • Inherits all SocialMember privileges

    • Additional Privilege: Golf Course

• Member —invites→ Guest

• Member —has→ Membership

---

EER Diagram (Text Representation)

pgsql
[Member]
| MemberID (PK)
| Name
| Address
| Phone
| Email

   |
   |<Generalization>
   |--------------------------------|
   |                                |
[SocialMember]                  [GolfMember]
Privileges: Pool, Racket Ball,  Privileges: Inherits from SocialMember +
Weight Room                    Golf Course

[Guest]
| GuestID (PK)
| Name
| ContactInfo
| InvitedBy (FK → MemberID)

(Member 1 --- N Guest)

---

Documentation Points

• Generalization/Specialization: SocialMember and GolfMember are subtypes of Member.

• Privileges are assigned based on membership type.

• Guests are related to the member who invited them.

📩 Need a similar solution? Email me: adel455@hotmail.com