Data Management and Operations

Automations in a database typically involve processes that are executed automatically to streamline data handling, minimize human error, and reduce manual efforts. These can include:

• Triggers: Automated procedures that are activated by specific database events. For instance:

  • employee_id_generator: Generates unique employee IDs.

  • movie_code_generator: Creates unique identifiers for movies.

  • grant_id_generaator: Auto-generates grant request IDs.

Restrictions are rules or constraints applied to ensure data integrity and enforce business logic. They include:

• Constraints:

  • Primary and Foreign Key Constraints: Ensure unique identification of records and maintain relationships between tables.

  • Unique Constraints: Prevent duplicate entries in specific fields. Constraints are covered in the Data Dictionary section of this documentation.

• Validation Triggers:

  • check_crew_company: Ensures crew members belong to the company producing the film.

  • check_employee_role: Validates roles against existing departments and roles.

Every action that modifies the data within this system—be it an insertion, or a deletion—is logged with precision. This granular tracking extends beyond mere data modifications to include user actions and system events, providing a comprehensive view of all interactions with our database.

In an era where data governance and compliance are paramount, the Logging and Auditing system stands as a testament to commitment to these principles. These logs provide the bedrock for audits, both internal and regulatory, ensuring that adherence to the highest standards of data stewardship.

• Triggers such as log_grant_insert, log_shareholder_delete, and others, which automatically record events post-operations on respective tables.

• The activity_log_auto Stored Procedure, a central component in the logging strategy, standardizes the creation of log entries, ensuring consistency and reliability in the way we record data interactions.

• The activity_log table contains detailed records of who did what and when, which are vital for security audits, troubleshooting, and understanding the evolution of the data over time. 

As you explore this section, you will find detailed explanations of the triggers and stored procedures, insights into logging strategies, and an overview of how these elements come together to create a cohesive and comprehensive Logging and Auditing system. Whether you're a database administrator, a developer, or an auditor, this documentation is designed to provide you with a deep understanding of how I ensure the integrity and transparency of data.