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

Enterprise Data Governance Knowledge Guide for Database: Formula_1

1. Introduction

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

2. Core Entities & Fields

Business Entities

• Drivers: Individuals participating in races, identified by driverId. Key attributes include forename, surname, date of birth, and nationality.
• Races: Competitive events, identified by raceId. Attributes include year, round, name, and date.
• Circuits: Locations where races are held, identified by circuitId. Attributes include name, location, country, and geographical coordinates (latitude, longitude).
• Constructors: Teams responsible for car design and manufacturing, identified by constructorId. Attributes include name, nationality, and url.

Key Attributes

• driverRef: A unique reference name for drivers, used for identification in various contexts.
• raceId: A unique identifier for races, crucial for linking race-related data across tables.
• q1, q2, q3: Qualifying period results, representing lap times in different qualifying sessions.
• fastestLapTime: The quickest lap time recorded during a race, used to assess driver performance.
• points: Scoring metric for drivers and constructors, reflecting performance in races.
• rank: The driver's ranking within a race based on fastestLapTime; rank 1 indicates the driver who recorded the quickest lap time.

3. Metric Definitions

Key Performance Indicators (KPIs)

• Driver Performance Index: Calculated as the average of points scored by a driver across all races. Formula: AVG(points).
• Constructor Success Rate: Percentage of races where a constructor finishes in the top 3 positions. Formula: DIVIDE(COUNT(raceId WHERE position <= 3), COUNT(raceId)) * 100.
• Fastest Lap Efficiency: Ratio of fastest lap times to total laps completed. Formula: DIVIDE(SUM(fastestLapTime), SUM(laps)).
• Race Completion Rate: Percentage of races completed by drivers without disqualification. Formula: DIVIDE(COUNT(raceId WHERE statusId != 2), COUNT(raceId)) * 100.
• Qualifying Improvement Rate: Change in qualifying times across sessions. Formula: SUBTRACT(q3, q1).

4. Constraints & Conventions

Filtering Criteria

• Temporal Boundaries: Data is typically analyzed within calendar years, with fiscal year adjustments as needed.
• Currency Formatting: Points and other numerical metrics are presented in integer or real formats, with no currency conversion required.
• Unit Conversion: Time metrics are standardized to 'MM:SS.mmm' format for consistency.

Common Conventions

• Driver Identification: Use driverId for unique identification; driverRef for reference in reports.
• Race Identification: Use raceId for linking race data; name for descriptive purposes.

5. Exemplar Use Cases

Example 1: Driver Elimination in Qualifying

• Metric: Identify drivers eliminated in the first qualifying period for a specific race.
• Explanation: For race number 20, list the reference names of drivers with the highest q1 times, indicating elimination.
• SQL: SELECT T2.driverRef FROM qualifying AS T1 INNER JOIN drivers AS T2 ON T2.driverId = T1.driverId WHERE T1.raceId = 20 ORDER BY T1.q1 DESC LIMIT 5

Example 2: Best Lap Time Analysis

• Metric: Determine the driver with the best lap time in the second qualifying period.
• Explanation: For race number 19, identify the driver with the minimum q2 time.
• SQL: SELECT T2.surname FROM qualifying AS T1 INNER JOIN drivers AS T2 ON T2.driverId = T1.driverId WHERE T1.raceId = 19 ORDER BY T1.q2 ASC LIMIT 1

Example 3: Circuit Location Identification

• Metric: Retrieve geographical coordinates for specific Grand Prix events.
• Explanation: For the Australian Grand Prix, extract latitude and longitude of the circuit.
• SQL: SELECT DISTINCT T1.lat, T1.lng FROM circuits AS T1 INNER JOIN races AS T2 ON T2.circuitID = T1.circuitId WHERE T2.name = 'Australian Grand Prix'

Example 4: Driver Age Analysis

• Metric: Identify the oldest driver who completed a specific race.
• Explanation: For race number 592, find the driver with the earliest dob among those who finished the race.
• SQL: SELECT T1.forename, T1.surname FROM drivers AS T1 INNER JOIN results AS T2 ON T2.driverId = T1.driverId WHERE T2.raceId = 592 AND T2.time IS NOT NULL AND T1.dob IS NOT NULL ORDER BY T1.dob ASC LIMIT 1

Example 5: Constructor Points Calculation

• Metric: Calculate the maximum points scored by a constructor in a race.
• Explanation: Determine the highest points value from constructorResults for a given race.
• SQL: SELECT MAX(points) FROM constructorResults WHERE raceId = [specific_raceId]

6. Ambiguity Resolution

Potentially Ambiguous Fields

Potentially Ambiguous Fields

• position vs. positionOrder: Use positionOrder for final race rankings; position for grid or qualifying positions.
• time vs. milliseconds: Use milliseconds for precise calculations; time for formatted display.
• driverRef vs. driverId: Use driverId for unique identification; driverRef for reference in reports.
• rank vs. positionOrder: Use positionOrder for final race finishing order; rank represents the driver's ranking based on fastestLapTime within a race, not the overall finishing position.

Recommended Usage

• Prioritize driverId and raceId for linking data across tables.
• Use fastestLapTime for performance metrics; fastestLapSpeed for speed analysis.

This guide ensures consistent and accurate data usage across the Formula_1 database, supporting enterprise-wide data governance and strategic insights.