The Department of Computer Science and Information Technology

Problem Description:

One city of council is broadening its Community Garden Initiative to create a network of green spaces that not only encourage sustainable living but also foster education, health, and community engagement. These gardens serve as shared hubs for gardening, workshops, volunteering, and public events, and a new centralised database is required to manage all operations.

Each Garden has a unique name, street address, contact phone number, and a general email account. Every garden is overseen by exactly one garden manager, who must be a member of the staff assigned to that garden. The city employs a variety of staff members. Each staff member has a unique employee number, a Working With Children Check (WWCC) number, and their own phone number and email address. A staff member is assigned to exactly one garden and exactly one staff member in each garden must hold the manager role at any given time.

In addition to staff, the initiative works with professionals such as horticulturists, permaculture experts, and sustainability educators. Each professional has their own unique professional ID, contact phone, email, and multiple qualifications. A professional may be affiliated with more than one garden but can only supervise events at gardens where they are formally affiliated.

Any member of the public can join the initiative by registering as a member of one or more gardens. Members have a unique membership ID, along with their name, address, phone number, email, and date of birth. Each membership is linked to a single garden and records the start date and (if applicable) an end date. A member can have multiple memberships over time, but no more than one active membership per garden.

Each garden contains multiple plots, each identified by a plot number unique within that garden. Plots may be used for different purposes and have different features. They are classified into one of three types: “Vegetable”, “Herb”, or “Flower”. A plot may or may not have an irrigation system, and some plots have a seating capacity because they are suitable for group activities. Plots can be either private plots, which are rentable to members for a season, or shared plots, which cannot be rented and are used for community activities such as training sessions or workshops

Private plots are rented per season (e.g., “Spring” or “Autumn”) and year. Only members with active membership in a garden can rent a private plot there, and a plot can be rented to only one member per season. Rentals have a status (active, completed, or cancelled) and may have maintenance notes such as “Overgrown” or “Needs irrigation repair”.

Gardens host a variety of community events, including composting tutorials, planting workshops, organic farming classes, and sustainability talks. An event has a unique title and description, start and end dates, a weekly time slot, and a target age range. Every event is linked to exactly one garden and takes place in a plot suitable for group activities. Some events are one-off, while others are part of a recurring series. Recurring events consist of multiple event sessions, each with its own date, time, and (if necessary) a different plot location from the main event’s assigned plot.

Every event is supervised by one professional and may have multiple staff members assisting. Event assistance and professional supervision are recorded so that reports can show all contributors.

Members can sign up for event sessions either as participants or as volunteers. Volunteers may be assigned one or more specific tasks (e.g., “Set up chairs”, “Manage registration desk”). Only members who meet the event’s age requirement and have an active membership at the hosting garden on the session date can participate or volunteer. Attendance is recorded for each session, showing whether the member attended, missed, or cancelled.

After attending an event, a member may submit feedback containing a rating (1-5), optional comments, and whether they would recommend the event. A member can only give feedback for an event they actually attended and may submit only one feedback per event.

The database must also respect important rules:

• A plot can be either private or shared, never both.
• A staff member is always assigned to exactly one garden, but a professional may be affiliated with multiple gardens.
• A private plot can be rented by only one member in any given season and year.
• Event supervision is restricted to professionals affiliated with the event’s garden.

City planners have indicated that the system should be ready for future expansions, including:
• Tracking training certifications for members, staff, and professionals.
• Managing a borrowing system for gardening tools.
• Recording harvest yields from plots.

Tasks

You are required to develop an EER model for the above problem description. The EER should contain all necessary information such as entities, attributes, primary keys, relationships (including specialisation/union if any), cardinalities, and participation (including (min, max)). Any assumptions should also be stated clearly. If assumptions are made, it is important to make sure that the assumptions reflect possible real practice for a similar business and do not contradict with the problem description above.

Note: The diagram must not be drawn by hand. Please use the attached EER template to start your design with the online tool https://app.diagrams.net/ (draw.io) You also can use any software to draw figures in your assignment. However, you must follow the notations introduced in the lectures and labs of this subject. Any alternative notations of EER modelling are NOT considered as a valid submission for this assignment. You are NOT required to transform your EER diagram into a set of tables for this assignment.

Assessment Requirements

The task requires students to design an Enhanced Entity–Relationship (EER) model for the city council’s Community Garden Initiative database. The EER diagram must capture all operational requirements while also being adaptable for future expansion.

The key requirements to be addressed in the EER are:

1. Entities and Attributes

   • Gardens (with details like name, address, contact info, etc.)

   • Staff (employee details, WWCC, contact info, manager role)

   • Professionals (unique ID, contact details, qualifications, garden affiliations)

   • Members (membership ID, personal details, date of birth)

   • Memberships (linked to members and gardens with start/end date)

   • Plots (plot number, type, irrigation system, seating capacity, classification as private/shared)

   • Rentals (season, year, member, status, maintenance notes)

   • Events (title, description, dates, time slots, age range, plot allocation)

   • Event sessions (date, time, plot, linkage to recurring events)

   • Supervision & Assistance (professional supervisors, staff assistants)

   • Attendance & Volunteer tasks (attendance records, volunteer roles)

   • Feedback (ratings, comments, recommendations)

2. Relationships and Constraints

   • One garden has many staff, but one is always the manager.

   • Staff are linked to exactly one garden.

   • Professionals may be affiliated with multiple gardens.

   • Members may join multiple gardens but only one active membership per garden at a time.

   • Private plots can be rented to only one member per season/year.

   • Events are linked to one garden and a plot suitable for group activities.

   • Recurring events → multiple event sessions.

   • Every event supervised by one professional (must be affiliated with garden).

   • Multiple staff can assist in events.

   • Members can be participants or volunteers (with age + membership checks).

   • Feedback linked to attended events.

   • Key constraints: private/shared plot exclusivity, membership rules, rental exclusivity.

3. Specialisation/Generalisation

   • Plots (specialisation into Private Plot and Shared Plot).

   • Events (specialisation into One-off and Recurring).

4. Participation and Cardinalities

   • (min, max) constraints, e.g.:

     • Garden → Staff (1, n); Staff → Garden (1, 1).

     • Garden → Manager (1, 1).

     • Professional → Garden (0, n).

     • Private Plot → Rental (0, n).

     • Rental → Member (1, 1).

     • Event → Professional (1, 1).

     • Event → Staff Assistance (0, n).

     • Member → Feedback (0, 1 per event).

5. Assumptions

   • Every garden always has at least one staff (mandatory participation).

   • Manager assignment changes over time but exactly one active at any moment.

   • Qualifications of professionals are stored as multivalued attributes.

   • Membership start/end dates help enforce “one active per garden” rule.

   • Age range constraints verified during event registration.

6. Future Expansion Considerations

   • Tracking certifications for staff/professionals/members.

   • Adding tool borrowing system.

   • Recording harvest yields from plots.

Academic Mentor’s Step-by-Step Approach

Step 1: Understanding the Problem Domain

The mentor first helped the student break down the problem description into business rules and data requirements. They highlighted the importance of distinguishing between:

• Core entities (gardens, staff, members, plots, events).

• Supporting entities (memberships, rentals, event sessions, feedback).

• Future requirements (certifications, tools, harvests).

This step ensured the student fully understood what data the system must capture.

Step 2: Identifying Entities and Attributes

The mentor guided the student to scan the description for unique identifiers (like Employee Number, Membership ID, Plot Number, Professional ID). These became the primary keys. Attributes like contact details, dates, and notes were then attached to the relevant entities.

Step 3: Establishing Relationships

The student was shown how to translate narrative constraints into relationships, such as:

• “Each garden has exactly one manager” → 1:1 relationship.

• “Professionals can be affiliated with many gardens” → M:N relationship.

• “Plots can be private or shared” → specialisation.

The mentor stressed the importance of (min, max) notation to clarify optional vs mandatory participation.

Step 4: Handling Specialisation/Constraints

Specialisation was applied to plots (Private vs Shared) and events (One-off vs Recurring). The exclusivity rules (plot can’t be both private & shared) were mapped using disjoint constraints.

Step 5: Incorporating Business Rules

The student was reminded to embed business rules into the model:

• Only members with active memberships can rent plots or join events.

• Private plots can only be rented to one member per season/year.

• Event supervision restricted to affiliated professionals.

Step 6: Drawing the EER Diagram

The mentor then guided the student in drawing the diagram (using draw.io template). Entities were rectangular, relationships as diamonds, attributes oval, and multivalued attributes double-oval. Specialisation was shown using ISA notation.

Step 7: Reviewing for Completeness

The student and mentor checked that:

• All entities and attributes from the problem description were captured.

• All constraints (cardinalities, participation, rules) were explicitly represented.

• The model was future-proof (certifications, tools, harvest).

Final Outcome

• A complete EER model capturing all entities, attributes, relationships, and constraints of the Community Garden Initiative.

• Clear mapping of specialisation (plots, events) and business rules.

• A robust conceptual design ready for transformation into relational tables in future coursework.

Learning Objectives Covered

By completing this assessment, the student achieved:

• Understanding of EER modelling principles (entities, attributes, relationships).

• Ability to capture real-world rules into data models.

• Critical thinking in applying (min, max) cardinalities and participation.

• Practical modelling skills using software tools like draw.io.

• Preparedness for future database design tasks such as relational schema mapping.

Need Extra Help with Your Assignment?

You can download the sample solution provided on this page to better understand the structure, formatting, and approach required for your academic tasks. This sample is designed to guide you with ideas and references for your own writing.

Important Note: The sample is strictly for reference only. Submitting it directly as your own work may lead to plagiarism issues and academic penalties. Use it as a learning tool to strengthen your writing and research skills.

If you need a plagiarism-free, custom-written solution tailored to your specific topic and requirements, our team of professional academic writers is here to help. With a fresh assignment solution, you get:

• 100% original and plagiarism-free work.

• Written according to your university guidelines.

• Thoroughly researched with credible references.

• Delivered on time, even with urgent deadlines.

Don’t risk your grades by submitting copied content—get expert help that ensures quality and originality.