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

Enterprise Data Governance Knowledge Guide for Database: Superhero

1. Introduction

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

2. Core Entities & Fields

Superhero

• superhero_name (text): The commonly known name of the superhero.
• full_name (text): The complete name of the superhero, if available.
• gender_id (integer): References the gender table to specify the superhero's gender.
• eye_colour_id (integer): References the colour table for the superhero's eye color.
• hair_colour_id (integer): References the colour table for the superhero's hair color.
• skin_colour_id (integer): References the colour table for the superhero's skin color.
• race_id (integer): References the race table to specify the superhero's race.
• publisher_id (integer): References the publisher table to identify the publisher.
• alignment_id (integer): References the alignment table to specify the superhero's moral alignment.
• height_cm (integer): The height of the superhero in centimeters.
• weight_kg (integer): The weight of the superhero in kilograms.

Superpower

• power_name (text): The name of the superpower.

Publisher

• publisher_name (text): The name of the publisher responsible for the superhero's creation or distribution.

Attribute

• attribute_name (text): The name of the attribute (e.g., Strength, Speed).
• attribute_value (integer): The quantified value of the attribute.

Colour

• colour (text): The color description used for eyes, hair, and skin.

3. Metric Definitions

Key Performance Indicators (KPIs)

• Superhero Count by Publisher: COUNT(superhero.id) grouped by publisher_name.
• Average Height of Superheroes: AVG(height_cm).
• Superpower Prevalence: COUNT(hero_power.hero_id) grouped by power_name.
• Attribute Ranking: RANK() OVER (ORDER BY attribute_value DESC) for specific attributes like Strength or Speed.
• Gender Distribution: COUNT(superhero.id) grouped by gender.

Calculation Logic

• Percentage Calculation: MULTIPLY(DIVIDE(SUM(condition), COUNT(total)), 100).
• Difference Calculation: SUBTRACT(SUM(condition1), SUM(condition2)).
• Ratio Calculation: DIVIDE(SUM(condition1), SUM(condition2)).

4. Constraints & Conventions

Filtering Criteria

• Publisher Filtering: Use publisher_name for filtering superheroes by publisher.
• Superpower Filtering: Use power_name for filtering superheroes by superpower.
• Attribute Filtering: Use attribute_name and attribute_value for filtering based on specific attributes.

Temporal Boundaries

• Fiscal Year Format: YYYY-MM-DD for all date-related entries.

Unit Conversions

• Height: Always recorded in centimeters.
• Weight: Always recorded in kilograms.

Currency Formatting

• Not applicable as the database does not contain financial data.

5. Exemplar Use Cases

Example 1: Superpower Prevalence

• Natural Language: Determine how many superheroes possess the superpower "Flight."
• SQL: SELECT COUNT(hero_power.hero_id) FROM hero_power INNER JOIN superpower ON hero_power.power_id = superpower.id WHERE superpower.power_name = 'Flight'

Example 2: Average Height Calculation

• Natural Language: Calculate the average height of superheroes published by Marvel Comics.
• SQL: SELECT AVG(height_cm) FROM superhero INNER JOIN publisher ON superhero.publisher_id = publisher.id WHERE publisher.publisher_name = 'Marvel Comics'

Example 3: Gender Distribution

• Natural Language: Count the number of male superheroes.
• SQL: SELECT COUNT(superhero.id) FROM superhero INNER JOIN gender ON superhero.gender_id = gender.id WHERE gender.gender = 'Male'

Example 4: Attribute Ranking

• Natural Language: Rank superheroes by their strength attribute.
• SQL: SELECT superhero_name, RANK() OVER (ORDER BY attribute_value DESC) AS StrengthRank FROM superhero INNER JOIN hero_attribute ON superhero.id = hero_attribute.hero_id INNER JOIN attribute ON hero_attribute.attribute_id = attribute.id WHERE attribute.attribute_name = 'Strength'

Example 5: Publisher Filtering

• Natural Language: List all superheroes published by DC Comics.
• SQL: SELECT superhero_name FROM superhero INNER JOIN publisher ON superhero.publisher_id = publisher.id WHERE publisher.publisher_name = 'DC Comics'

6. Ambiguity Resolution

Similar Fields

• superhero_name vs. full_name: Use superhero_name for common references and full_name for detailed identification.
• eye_colour_id, hair_colour_id, skin_colour_id: Ensure correct mapping to the colour table for accurate color identification.
• attribute_name vs. power_name: attribute_name refers to inherent qualities like strength, while power_name refers to acquired abilities like flight.

Recommended Usage

• Prioritize superhero_name for general queries and full_name for specific identification.
• Use publisher_name for filtering by publisher context.
• Ensure correct mapping of color IDs to the colour table for accurate color descriptions.

This guide is intended to standardize data usage and interpretation across the enterprise, ensuring consistency and accuracy in all data-driven processes.