kdd-benchmark/input/task_26/context/knowledge.md

Enterprise Data Governance Knowledge Guide for Database: student_club

1. Introduction

This guide serves as a comprehensive semantic layer for the student_club database, providing authoritative definitions, metric formulations, and governance standards. It is designed to ensure consistent data interpretation and usage across the organization, supporting strategic decision-making and operational efficiency.

2. Core Entities & Fields

Members

• first_name, last_name: Represents the full name of a club member. Always use both fields to ensure complete identification.
• position: Indicates the role of the member within the club (e.g., President, Vice President).
• major_name: The academic major associated with the member, linked through the major table.
• t_shirt_size: Preferred T-shirt size of the member, used for event merchandise planning.

Events

• event_name: The title of the event, used for identification and reporting.
• event_date: The date on which the event occurs, formatted as 'YYYY-MM-DD'.
• type: Categorizes the event (e.g., Meeting, Fundraiser, Game).
• status: Indicates the current state of the event (e.g., Open, Closed).

Financials

• amount: Represents monetary values, such as funds received or budgeted amounts. Ensure currency consistency (e.g., USD).
• spent: Total expenditure associated with an event or category.
• expense_description: Details the nature of expenses incurred (e.g., Pizza, Posters).

Locations

• location: Venue or place where the event is held.
• state, county: Geographic identifiers for member addresses, used for demographic analysis.

3. Metric Definitions

Key Performance Indicators (KPIs)

• Event Attendance: COUNT(link_to_event) - Measures the number of members attending an event.
• Budget Utilization: DIVIDE(spent, amount) - Calculates the ratio of spent funds to budgeted amounts.
• Member Participation: COUNT(link_to_event) > X - Evaluates member engagement based on event attendance thresholds.
• Fundraising Effectiveness: SUM(amount WHERE source = 'Fundraising') - Assesses total funds raised through fundraising activities.

Calculation Logic

• Average Cost: AVG(cost) - Computes the mean expense for specified criteria.
• Total Expenditure: SUM(spent) - Aggregates all spending within a defined period or category.
• Yearly Comparison: SUBTRACT(SUM(spent WHERE YEAR(event_date) = 2019), SUM(spent WHERE YEAR(event_date) = 2020)) - Compares annual spending across fiscal years.

4. Constraints & Conventions

Filtering Criteria

• Temporal Boundaries: Use fiscal year format 'YYYY' for annual comparisons.
• Currency Formatting: All monetary values are in USD; ensure consistent representation.
• Unit Conversion: Apply standard conversion rates for non-monetary metrics (e.g., attendance counts).

Common Filters

• Event Type: Filter by type to isolate specific event categories.
• Member Position: Use position to segment data by club roles.

5. Exemplar Use Cases

Example 1: Calculating Event Attendance

• Metric: Event Attendance
• SQL: SELECT COUNT(link_to_event) FROM attendance WHERE event_id = X
• Explanation: This metric calculates the total number of members who attended a specific event, providing insights into engagement levels.

Example 2: Assessing Budget Utilization

• Metric: Budget Utilization
• SQL: SELECT DIVIDE(spent, amount) FROM budget WHERE event_id = X
• Explanation: This formula evaluates how effectively the allocated budget was used, highlighting potential overspending or savings.

Example 3: Comparing Yearly Expenditure

• Metric: Yearly Comparison
• SQL: SELECT SUBTRACT(SUM(spent WHERE YEAR(event_date) = 2019), SUM(spent WHERE YEAR(event_date) = 2020))
• Explanation: This comparison provides a clear view of spending trends across different fiscal years, aiding in budget planning.

Example 4: Determining Fundraising Success

• Metric: Fundraising Effectiveness
• SQL: SELECT SUM(amount) FROM income WHERE source = 'Fundraising'
• Explanation: This metric quantifies the total funds raised through fundraising activities, essential for evaluating campaign success.

Example 5: Calculating Average Member Participation

• Metric: Member Participation
• SQL: SELECT COUNT(link_to_event) FROM attendance WHERE member_id = X GROUP BY member_id HAVING COUNT(link_to_event) > 7
• Explanation: This formula measures member engagement by counting the number of events attended, identifying highly active members.

6. Ambiguity Resolution

Potentially Ambiguous Fields

• name: Always specify whether referring to event_name or member_name to avoid confusion.
• amount: Clarify whether it pertains to budgeted amount, spent amount, or funds received.
• date: Distinguish between event_date, expense_date, and date_received for accurate temporal analysis.

Recommended Usage

• Prioritize context-specific identifiers (e.g., event_name for event-related queries) to ensure clarity and prevent misinterpretation.

This guide is intended to be a living document, updated regularly to reflect changes in business processes and data structures. Adherence to these standards will ensure data integrity and facilitate effective governance across the organization.