请用sql语言写出下列要求。学校人力资源管理系统  实现学校部门信息，职务、职称和教职工信息管理；  实现教师的学籍经历管理；  实现教师的家庭关系管理；  实现教师的奖惩信息管理；  创建存储过程查询学校各部门各种职称的教职工数量；  创建触发器，当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数；  创建规则用于保证教职工的E-Mail的输入格式正确；  建立数据库相关表之间的参照完整性约束。

下面是针对学校人力资源管理系统的SQL语句：

1.创建部门信息表

CREATE TABLE department (
  dep_id INT PRIMARY KEY,
  dep_name VARCHAR(50) NOT NULL,
  dep_manager VARCHAR(50) NOT NULL
);

2.创建职务信息表

CREATE TABLE position (
  pos_id INT PRIMARY KEY,
  pos_name VARCHAR(50) NOT NULL
);

3.创建职称信息表

CREATE TABLE title (
  title_id INT PRIMARY KEY,
  title_name VARCHAR(50) NOT NULL
);

4.创建教职工信息表

CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  teacher_name VARCHAR(50) NOT NULL,
  teacher_email VARCHAR(50) NOT NULL,
  teacher_dep_id INT NOT NULL,
  teacher_pos_id INT NOT NULL,
  teacher_title_id INT NOT NULL,
  FOREIGN KEY (teacher_dep_id) REFERENCES department(dep_id),
  FOREIGN KEY (teacher_pos_id) REFERENCES position(pos_id),
  FOREIGN KEY (teacher_title_id) REFERENCES title(title_id)
);

5.创建学籍经历信息表

CREATE TABLE academic (
  academic_id INT PRIMARY KEY,
  academic_name VARCHAR(50) NOT NULL,
  academic_start_date DATE NOT NULL,
  academic_end_date DATE NOT NULL,
  teacher_id INT NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);

6.创建家庭关系信息表

CREATE TABLE family (
  family_id INT PRIMARY KEY,
  family_name VARCHAR(50) NOT NULL,
  family_relation VARCHAR(50) NOT NULL,
  teacher_id INT NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);

7.创建奖惩信息表

CREATE TABLE reward_punish (
  rp_id INT PRIMARY KEY,
  rp_content VARCHAR(50) NOT NULL,
  rp_date DATE NOT NULL,
  teacher_id INT NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);

8.创建存储过程查询学校各部门各种职称的教职工数量

CREATE PROCEDURE teacher_count()
BEGIN
  SELECT dep_name, pos_name, title_name, COUNT(teacher_id) 
  FROM teacher 
  INNER JOIN department ON teacher_dep_id = dep_id 
  INNER JOIN position ON teacher_pos_id = pos_id 
  INNER JOIN title ON teacher_title_id = title_id 
  GROUP BY dep_name, pos_name, title_name;
END;

9.创建触发器，当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数

CREATE TRIGGER teacher_count_update 
AFTER INSERT ON teacher 
FOR EACH ROW 
BEGIN
  UPDATE department 
  SET dep_manager = dep_manager, 
      COUNT = COUNT + 1 
  WHERE dep_id = NEW.teacher_dep_id;
END;

CREATE TRIGGER teacher_count_delete 
AFTER DELETE ON teacher 
FOR EACH ROW 
BEGIN
  UPDATE department 
  SET dep_manager = dep_manager, 
      COUNT = COUNT - 1 
  WHERE dep_id = OLD.teacher_dep_id;
END;

CREATE TRIGGER teacher_count_update 
AFTER UPDATE ON teacher 
FOR EACH ROW 
BEGIN
  UPDATE department 
  SET dep_manager = dep_manager, 
      COUNT = COUNT + 1 
  WHERE dep_id = NEW.teacher_dep_id;
  
  UPDATE department 
  SET dep_manager = dep_manager, 
      COUNT = COUNT - 1 
  WHERE dep_id = OLD.teacher_dep_id;
END;

10.创建规则用于保证教职工的E-Mail的输入格式正确

CREATE RULE email_rule AS 
ON INSERT TO teacher 
WHERE NOT (teacher_email LIKE '%@%.%') 
DO INSTEAD 
  RAISE EXCEPTION 'Invalid email format';

11.建立数据库相关表之间的参照完整性约束

ALTER TABLE teacher 
ADD FOREIGN KEY (teacher_dep_id) REFERENCES department(dep_id) ON DELETE CASCADE;

ALTER TABLE teacher 
ADD FOREIGN KEY (teacher_pos_id) REFERENCES position(pos_id) ON DELETE CASCADE;

ALTER TABLE teacher 
ADD FOREIGN KEY (teacher_title_id) REFERENCES title(title_id) ON DELETE CASCADE;

ALTER TABLE academic 
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;

ALTER TABLE family 
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;

ALTER TABLE reward_punish 
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;