Due: Monday, October 13, 2025, before class. 

Grade contribution: 21%

Situation 

The Wilderness Exploration Society (WES) at Aotearoa University wants to develop a database to manage their equipment hire and trip registrations. The WES director has provided the following information about the current situation:

• Customers of WES include Aotearoa University members (students, staff, and alumni) as well as nonuniversity customers (also known as guests). To participate in WES activities, a guest must be sponsored by an Aotearoa University member. A member can sponsor many guests, but each guest has only one sponsor. Once a guest has a sponsor, they may hire equipment and participate in trips just like any other member. Basic information is recorded for all customers and sponsors, including: identification number, name, phone number, and date of birth.
• Customers can take part in a variety of trips such as tramping, kayaking, mountain biking, or camping. Each type of trip has a name and a code (e.g., the “Beginners Kayaking” trip has a trip code “4123”). Trips are also defined by a difficulty level, a fee, and a length (measured in days). Each trip type may be scheduled on multiple dates during the year. For example, the “Beginners Kayaking” trip might be scheduled for 20 April 2023, 20 May 2023, and 12 June 2023. However, a given trip type cannot be scheduled more than once on the same date.
• All registrations for scheduled trips—both past and present—must be recorded. For each trip, it should also be noted whether or not a participant has signed the required insurance waiver.
• Each scheduled trip is led by one WES staff member, with another acting as assistant leader.
• Equipment available for hire includes tents, tramping packs, sleeping bags, and so on. Each type of equipment has three daily hire rates: one for students, one for staff/alumni, and one for guests. For example, a “Macpac Torre 80L” pack may be hired by students for $0.50 per day, by staff and alumni for $1.00 per day, and by guests for $1.50 per day.
• Every individual piece of equipment has a unique number and a condition rating (good, fair, or poor). For instance, WES might have three “Macpac Torre 80L” packs: each has its own number, two are in good condition, and one is in fair condition. Hire costs are determined by equipment type, not by condition (i.e., all three packs have the same cost structure).
• All equipment hires—past and present—must be recorded. A customer may hire one or several items at the same time. When they do, a hire agreement is created. This agreement includes the hire date, the customer’s details, and the list of items hired. The hire date applies to the entire agreement, but each item can have its own expected return date. The actual return date for each item must also be recorded.
• Each hire agreement is managed by a specific WES staff member.
• Finally, note that customers may hire equipment without going on a trip, and they may also go on a trip without hiring equipment (if they have their own gear). These two activities are managed independently.

Request

Using MySQL Workbench, design and implement a database to support the Wilderness Exploration Society (WES) scenario. Your work should include the following:

2. Database Design and Implementation o Create all necessary tables to represent the information provided. o Define appropriate data types, primary keys, and relationships between tables.
   • Ensure referential integrity through the use of foreign keys.
3. Data Population o Insert approximately five rows of sample data into each table so that the database is meaningfully populated.
4. Database Expansion o Propose and implement an extension of the database by introducing a new aspect of the WES operations.
   • Clearly describe the new requirement in writing.
   • Show how this requirement is implemented by creating two to three additional tables, along with their relationships, attributes, and sample data.

Example of a Possible Expansion:  “WES staff can only hold one position at a time (e.g., Trip Coordinator, Equipment Manager). Each position is defined by a job description and a salary band. WES wishes to track the different positions held by staff members over time. For instance, they may need to record that Joe Smith

(employee 111-11-1111) was Marketing Officer from 1 January 2021 to 2 September 2022, and then became

Assistant Director on 3 September 2022 (a role he continues to hold).”

Deliverables

Students must submit the following three items:

1. MySQL Workbench File (.mwb) o Save your completed database design (including your expansion) using File → Save As… and submit the .mwb file.
   • This file must include all tables, attributes, data types, primary and foreign keys, and relationships.
2. Entity-Relationship Diagram (.pdf) o Export your ERD as a PDF using File → Export → Export as PDF in MySQL Workbench.
   • The diagram must be clear, legible, and show all entities and relationships.
3. Documentation (.pdf or Word) o A short report describing:
   • Your key design choices and any assumptions made;
   • How the database supports the WES requirements;
   • A written description of your chosen expansion and why it is relevant (note that this expansion must be fully incorporated into your database design and deliverables above, that is, it should be represented in the .mwb and .pdf files & sample data); Screenshots showing the contents of each table

Marking Schedule