# 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.