# SQL Data Integrity Critic Framework (ISO/IEC 9075:2023)

This framework guides the Critic role when evaluating SQL code, database schemas, and data management practices for data integrity, consistency, and reliability. This critic focuses on proper use of constraints, normalization, transactions, and data validation to ensure data correctness and prevent anomalies.

## SQL Data Integrity Evaluation Areas

### 1. Constraint Management and Enforcement
**What to Look For:**
- Proper use of primary keys, foreign keys, unique constraints, and check constraints
- Correct implementation of referential integrity
- Appropriate use of NOT NULL and default constraints
- Consistent constraint naming and documentation
- Proper constraint validation and error handling

**Common Problems:**
- Missing or incorrect primary/foreign key constraints
- Inconsistent constraint enforcement
- Lack of appropriate NOT NULL constraints
- Poor constraint naming conventions
- Missing constraint validation in application code

**Evaluation Questions:**
- Are all tables defined with appropriate primary keys?
- Are foreign key relationships enforced and consistent?
- Are constraints used to prevent invalid data states?
- Are constraint violations handled gracefully?
- Are all constraints properly documented and named?

### 2. Normalization and Schema Design
**What to Look For:**
- Appropriate level of normalization for the use case
- Prevention of data anomalies (insertion, deletion, update anomalies)
- Efficient schema design that balances normalization with performance
- Proper handling of denormalization when necessary
- Clear documentation of normalization decisions

**Common Problems:**
- Lack of normalization leading to data redundancy
- Over-normalization causing performance issues
- Inconsistent normalization across related tables
- Missing documentation of denormalization rationale
- Poor handling of many-to-many relationships

**Evaluation Questions:**
- Is normalization applied appropriately for the use case?
- Are data anomalies prevented through proper schema design?
- Is the schema design efficient and maintainable?
- Are denormalization decisions documented and justified?
- Are many-to-many relationships handled correctly?

### 3. Transaction Management
**What to Look For:**
- Proper use of transactions to ensure atomicity and consistency
- Appropriate isolation levels for different operations
- Consistent transaction boundaries and error handling
- Proper handling of long-running transactions
- Resource cleanup and rollback on errors

**Common Problems:**
- Inconsistent or missing transaction boundaries
- Inappropriate isolation levels causing concurrency issues
- Long-running transactions blocking other operations
- Missing rollback on error conditions
- Resource leaks in transaction handling

**Evaluation Questions:**
- Are transactions used to maintain data integrity?
- Are appropriate isolation levels chosen for operations?
- Are transaction boundaries clearly defined?
- Is error handling and rollback implemented properly?
- Are long-running transactions avoided or managed?

### 4. Data Validation and Quality
**What to Look For:**
- Comprehensive input validation and data type checking
- Proper handling of NULL values and edge cases
- Data quality checks and business rule enforcement
- Consistent data format and encoding standards
- Regular data integrity audits and monitoring

**Common Problems:**
- Insufficient input validation leading to data corruption
- Inconsistent NULL handling across the application
- Missing business rule enforcement
- Poor data format standardization
- Lack of data quality monitoring

**Evaluation Questions:**
- Are all inputs validated before database operations?
- Is NULL handling consistent and appropriate?
- Are business rules enforced at the database level?
- Is data format standardized across the system?
- Are data quality issues monitored and addressed?

### 5. Referential Integrity
**What to Look For:**
- Proper foreign key constraint definitions
- Appropriate cascade options for updates and deletes
- Handling of orphaned records and referential integrity violations
- Consistent referential integrity across related tables
- Proper documentation of relationship constraints

**Common Problems:**
- Missing or incorrect foreign key constraints
- Inappropriate cascade options causing unintended deletions
- Orphaned records due to poor referential integrity
- Inconsistent relationship definitions
- Missing documentation of relationship constraints

**Evaluation Questions:**
- Are all foreign key relationships properly defined?
- Are cascade options appropriate for the business logic?
- Are orphaned records prevented or handled?
- Is referential integrity consistent across the schema?
- Are relationship constraints clearly documented?

## ISO/IEC Standards-Specific Criticism Process

### Step 1: Constraint Analysis
1. **Audit Constraints**: Are primary, foreign, and unique keys used appropriately?
2. **Check Constraint Enforcement**: Are constraints properly enforced and validated?
3. **Evaluate Constraint Naming**: Are constraints named consistently and clearly?
4. **Assess Constraint Documentation**: Are constraints properly documented?

### Step 2: Normalization Assessment
1. **Check Normalization Level**: Is the schema normalized appropriately?
2. **Evaluate Data Anomalies**: Are insertion, deletion, and update anomalies prevented?
3. **Assess Denormalization**: Are denormalization decisions justified and documented?
4. **Review Schema Efficiency**: Is the schema design efficient and maintainable?

### Step 3: Transaction Analysis
1. **Check Transaction Boundaries**: Are transaction boundaries clearly defined?
2. **Evaluate Isolation Levels**: Are appropriate isolation levels chosen?
3. **Assess Error Handling**: Is error handling and rollback implemented?
4. **Review Resource Management**: Are resources properly managed in transactions?

### Step 4: Data Quality Assessment
1. **Check Input Validation**: Are all inputs validated before database operations?
2. **Evaluate NULL Handling**: Is NULL handling consistent and appropriate?
3. **Assess Business Rules**: Are business rules enforced at the database level?
4. **Review Data Monitoring**: Is data quality monitored and addressed?

## ISO/IEC Standards-Specific Criticism Guidelines

### Focus on Data Integrity
**Good Criticism:**
- "The lack of a foreign key constraint allows orphaned records, violating referential integrity."
- "Missing NOT NULL constraint on critical field allows invalid data states."
- "The transaction boundaries are not clearly defined, risking data inconsistency."
- "The schema is not normalized, leading to data redundancy and update anomalies."

**Poor Criticism:**
- "This might cause data problems."
- "The data could be inconsistent."
- "This is not safe."

### Emphasize Constraint and Transaction Management
**Good Criticism:**
- "The foreign key constraint is missing, allowing referential integrity violations."
- "The transaction does not handle rollback on error, risking partial updates."
- "The isolation level is too permissive for this critical operation."
- "The constraint naming is inconsistent, making maintenance difficult."

**Poor Criticism:**
- "This is not robust."
- "This could fail."
- "This is unreliable."

## ISO/IEC Standards-Specific Problem Categories

### Constraint Problems
- **Missing Constraints**: Lack of primary, foreign, or unique keys
- **Incorrect Constraints**: Wrong constraint types or definitions
- **Poor Constraint Naming**: Inconsistent or unclear constraint names
- **Missing Validation**: Lack of constraint validation in application code

### Normalization Problems
- **Poor Normalization**: Schema design leading to data anomalies
- **Over-Normalization**: Excessive normalization causing performance issues
- **Inconsistent Normalization**: Different normalization levels across related tables
- **Missing Documentation**: Lack of documentation for normalization decisions

### Transaction Problems
- **Missing Transactions**: Operations not wrapped in transactions
- **Inappropriate Isolation**: Wrong isolation levels for operations
- **Poor Error Handling**: Missing rollback or error handling in transactions
- **Resource Leaks**: Uncommitted transactions or open cursors

### Data Quality Problems
- **Insufficient Validation**: Lack of input validation leading to data corruption
- **Inconsistent NULL Handling**: Different NULL handling across the application
- **Missing Business Rules**: Lack of business rule enforcement
- **Poor Data Format**: Inconsistent data format and encoding

### Referential Integrity Problems
- **Missing Foreign Keys**: Lack of foreign key constraints
- **Inappropriate Cascades**: Wrong cascade options for updates/deletes
- **Orphaned Records**: Records without valid parent references
- **Inconsistent Relationships**: Different relationship definitions across tables

## ISO/IEC Standards-Specific Criticism Templates

### For Constraint Issues
```
Constraint Issue: [Specific constraint problem]
Problem: [What makes this constraint inadequate or incorrect]
Impact: [Potential data integrity violations or inconsistencies]
Evidence: [Specific schema or constraint examples]
Priority: [Critical/High/Medium/Low]
```

### For Normalization Issues
```
Normalization Issue: [Specific normalization problem]
Problem: [What makes this schema design problematic]
Impact: [Potential data anomalies or performance issues]
Evidence: [Specific schema design examples]
Priority: [Critical/High/Medium/Low]
```

### For Transaction Issues
```
Transaction Issue: [Specific transaction problem]
Problem: [What makes this transaction handling inadequate]
Impact: [Potential data inconsistency or resource issues]
Evidence: [Specific transaction code examples]
Priority: [Critical/High/Medium/Low]
```

### For Data Quality Issues
```
Data Quality Issue: [Specific data quality problem]
Problem: [What makes this data handling inadequate]
Impact: [Potential data corruption or inconsistency]
Evidence: [Specific data handling examples]
Priority: [Critical/High/Medium/Low]
```

### For Referential Integrity Issues
```
Referential Integrity Issue: [Specific referential integrity problem]
Problem: [What makes this relationship definition inadequate]
Impact: [Potential orphaned records or data inconsistency]
Evidence: [Specific relationship examples]
Priority: [Critical/High/Medium/Low]
```

## ISO/IEC Standards-Specific Criticism Best Practices

### Do's
- **Focus on Data Integrity**: Prioritize data correctness and consistency
- **Emphasize Constraints**: Ensure proper use of database constraints
- **Consider Transactions**: Evaluate transaction boundaries and error handling
- **Assess Normalization**: Review schema design for data anomalies
- **Document Decisions**: Clearly document constraint and design decisions

### Don'ts
- **Ignore Constraint Violations**: Don't overlook missing or incorrect constraints
- **Accept Data Anomalies**: Don't tolerate poor normalization or data redundancy
- **Skip Transaction Management**: Don't ignore transaction boundaries and error handling
- **Overlook Data Quality**: Don't accept poor data validation or NULL handling
- **Forget Documentation**: Don't skip documentation of integrity decisions

## ISO/IEC Standards-Specific Criticism Checklist

### Constraint Assessment
- [ ] Are all tables defined with appropriate primary keys?
- [ ] Are foreign key relationships enforced and consistent?
- [ ] Are constraints used to prevent invalid data states?
- [ ] Are constraint violations handled gracefully?
- [ ] Are all constraints properly documented and named?

### Normalization Assessment
- [ ] Is normalization applied appropriately for the use case?
- [ ] Are data anomalies prevented through proper schema design?
- [ ] Is the schema design efficient and maintainable?
- [ ] Are denormalization decisions documented and justified?
- [ ] Are many-to-many relationships handled correctly?

### Transaction Assessment
- [ ] Are transactions used to maintain data integrity?
- [ ] Are appropriate isolation levels chosen for operations?
- [ ] Are transaction boundaries clearly defined?
- [ ] Is error handling and rollback implemented properly?
- [ ] Are long-running transactions avoided or managed?

### Data Quality Assessment
- [ ] Are all inputs validated before database operations?
- [ ] Is NULL handling consistent and appropriate?
- [ ] Are business rules enforced at the database level?
- [ ] Is data format standardized across the system?
- [ ] Are data quality issues monitored and addressed?

### Referential Integrity Assessment
- [ ] Are all foreign key relationships properly defined?
- [ ] Are cascade options appropriate for the business logic?
- [ ] Are orphaned records prevented or handled?
- [ ] Is referential integrity consistent across the schema?
- [ ] Are relationship constraints clearly documented?

## ISO/IEC Standards-Specific Evaluation Questions

### For Any SQL Code
1. **Are all data integrity constraints properly defined and enforced?**
2. **Is the schema normalized appropriately to prevent data anomalies?**
3. **Are transactions used to ensure atomicity and consistency?**
4. **Is input validation implemented to prevent data corruption?**
5. **Are referential integrity relationships properly maintained?**
6. **Is NULL handling consistent and appropriate?**
7. **Are business rules enforced at the database level?**
8. **Is data quality monitored and maintained?**
9. **Are constraint violations handled gracefully?**
10. **Is the schema design efficient and maintainable?**

### For Database Schemas
1. **Are all tables defined with appropriate primary keys?**
2. **Are foreign key relationships enforced and consistent?**
3. **Is normalization applied appropriately for the use case?**
4. **Are constraints used to prevent invalid data states?**
5. **Are many-to-many relationships handled correctly?**

### For Stored Procedures and Triggers
1. **Are all inputs validated before database operations?**
2. **Are transactions used to ensure atomicity and consistency?**
3. **Is error handling and rollback implemented properly?**
4. **Are business rules enforced consistently?**
5. **Is resource cleanup performed on all code paths?**

### For Data Operations
1. **Are all operations wrapped in appropriate transactions?**
2. **Are isolation levels chosen appropriately for operations?**
3. **Is error handling implemented for constraint violations?**
4. **Are data quality checks performed before operations?**
5. **Is referential integrity maintained during operations?**

## SQL Data Integrity Principles Applied

### "Ensure Data Consistency and Reliability"
- Use constraints to prevent invalid data states
- Implement proper normalization to avoid data anomalies
- Maintain referential integrity across all relationships

### "Maintain Transaction Integrity"
- Use transactions to ensure atomicity and consistency
- Implement proper error handling and rollback
- Choose appropriate isolation levels for operations

### "Validate and Monitor Data Quality"
- Implement comprehensive input validation
- Handle NULL values and edge cases consistently
- Monitor and maintain data quality standards

### "Document and Enforce Business Rules"
- Enforce business rules at the database level
- Document constraint and relationship decisions
- Maintain consistent data format and encoding