This is an example of a database schema that uses triggers to enforce data integrity. The schema includes several tables: employees, departments, job_hierarchies, and department_employees.

Here’s a breakdown of the tables and their relationships:

• Employees Table

  • The table has columns for employee ID, name, department ID, job title, and start date.
  • The column names are EmployeeID, Name, DepartmentID, JobTitle, and StartDate.

• Departments Table

  • The table has columns for department ID and department name.
  • The column names are DepartmentID and DepartmentName.

• Job Hierarchies Table

  • The table has columns for job hierarchy ID, parent job title, and child job title.
  • The column names are JobHierarchyID, ParentJobTitle, and ChildJobTitle.

• Department Employees Table

  • The table has columns for department employee ID, department ID, employee ID, and hire date.

Here’s a breakdown of the triggers:

1. Check Constraint on Department ID in Employees Table

   • The trigger checks that the value in the DepartmentID column matches an existing value in the Departments table.
   • If it doesn’t match, the update operation fails.

2. Update Trigger on Department Employees Table

   • When data is inserted into the DepartmentEmployees table, a corresponding record is also created in the JobHierarchies table to maintain the hierarchical structure of job titles.
   • The new department employee ID becomes the parent ID for its respective child job title.

3. Update Trigger on Department Employees Table (Continued)

   • When data is updated in the DepartmentEmployees table, the corresponding records are also updated in the JobHierarchies table to maintain the hierarchical structure of job titles.
   • The updated department employee ID becomes the parent ID for its respective child job title.

4. Delete Trigger on Department Employees Table

   • When a record is deleted from the DepartmentEmployees table, the corresponding records are also deleted from the JobHierarchies table to maintain the hierarchical structure of job titles.
   • The removed department employee ID becomes NULL for its respective child job title.

5. Delete Trigger on Employees Table

   • When a record is deleted from the Employees table, all corresponding records are also deleted from the DepartmentEmployees table and the JobHierarchies table to maintain data consistency.

These triggers ensure that:

• Only valid department IDs can be used in the Employees table.
• The hierarchical structure of job titles is maintained through the use of JobHierarchyID, ParentJobTitle, and ChildJobTitle columns.
• When employees are added or removed, their corresponding records are also updated or deleted from other tables to maintain data consistency.

Last modified on 2024-03-19