• 表结构分析总结

  • 当前表 emp_department_assignments 以 emp_id 为主键，同时承载了“员工基本信息”“员工-部门隶属”“部门-经理唯一映射”“部门-楼层唯一映射”等多组语义。已有的函数依赖：
    • dept_id -> dept_name, manager_id, manager_name, office_building, office_floor
    • manager_id -> dept_id
    • (office_building, office_floor) -> dept_id
  • 这些决定因素（dept_id、manager_id、office_building+office_floor）在当前表中均不是超键，导致更新/删除/插入异常和大量冗余。

• 规范化问题识别

  • 非主属性对非超键的函数依赖：
    • dept_id -> dept_name, manager_id, manager_name, office_building, office_floor（dept_id 不是超键，违反 BCNF）
    • manager_id -> dept_id（manager_id 不是超键，违反 BCNF）
    • (office_building, office_floor) -> dept_id（(building,floor) 不是超键，违反 BCNF）
  • 传递依赖：
    • emp_id -> dept_id 且 dept_id -> manager_name, office_building, office_floor, dept_name，导致 emp_id -> 这些非主属性（违反 3NF）
  • 冗余与异常：
    • 冗余存储 dept_name、manager_name、office_building、office_floor 于每条员工记录中。
    • 更新异常：调整部门楼层需更新所有该部门员工行。
    • 删除异常：删除最后一名员工可能“丢失”部门与楼层、经理的知识。
    • 插入异常：尚未有员工时，无法先行登记部门与楼层、经理关系。

• 各级范式符合度评估

  • 1NF：满足（字段原子、无重复分组）。
  • 2NF：满足（主键为单列 emp_id，不存在针对主键真子集的部分依赖）。
  • 3NF：不满足（存在 emp_id -> dept_id -> 其他属性的传递依赖）。
  • BCNF：不满足（存在由非超键决定其他属性的函数依赖）。

• 具体的规范化步骤

  • 分解目标与关键点

    • 将“员工”“部门（含唯一楼层）”“部门经理（员工）”分离，令所有非平凡函数依赖的决定因素成为所在关系的候选键，满足 BCNF。
    • 去除 manager_name、dept_name 在员工级的重复存储；manager_name 由经理员工的 emp_name 派生获得。

  • 建议的关系模式（BCNF）

    1. 员工
       • employees(emp_id PK, emp_name, dept_id FK -> departments.dept_id)
       • 语义：每位员工当前隶属一个部门（如允许空缺，可将 dept_id 设为可空）。
    2. 部门（含唯一楼层）
       • departments(dept_id PK, dept_name, office_building, office_floor, UNIQUE(office_building, office_floor))
       • 语义：dept_id -> dept_name, office_building, office_floor；且 (office_building, office_floor) 唯一决定一个部门。
       • 在该关系内，决定因素 dept_id 与 (office_building, office_floor) 均为候选键，满足 BCNF。
    3. 部门经理（唯一映射）
       • department_managers(dept_id PK FK -> departments.dept_id, manager_id UNIQUE FK -> employees.emp_id)
       • 语义：每个部门唯一经理；每位经理仅管理一个部门。由于 dept_id 是主键且 manager_id 唯一，两个决定因素都是候选键，满足 BCNF。

  • PostgreSQL 建表示例（含约束）

    -- 1) 部门
    CREATE TABLE departments (
      dept_id         INT PRIMARY KEY,
      dept_name       VARCHAR(80) NOT NULL,
      office_building VARCHAR(40) NOT NULL,
      office_floor    INT NOT NULL,
      CONSTRAINT uq_department_location UNIQUE (office_building, office_floor),
      CONSTRAINT ck_office_floor_positive CHECK (office_floor >= 0)
    );
    
    -- 2) 员工
    CREATE TABLE employees (
      emp_id   INT PRIMARY KEY,
      emp_name VARCHAR(60) NOT NULL,
      dept_id  INT NOT NULL,
      CONSTRAINT fk_employee_department
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    );
    
    CREATE INDEX idx_employees_dept_id ON employees(dept_id);
    
    -- 3) 部门经理（1:1 的唯一映射）
    CREATE TABLE department_managers (
      dept_id    INT PRIMARY KEY,
      manager_id INT NOT NULL UNIQUE,
      CONSTRAINT fk_dm_dept
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
      CONSTRAINT fk_dm_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(emp_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
    );
    
    CREATE INDEX idx_dm_manager_id ON department_managers(manager_id);
    

    说明：

    • 未在 departments 内直接存 manager_id 是为避免与 employees(dept_id) 的环状外键；使用独立的 department_managers 表同样满足给定 FDs 且实现更简洁的装载顺序。
    • 如业务允许无经理的部门，可将 department_managers.manager_id 设为可空，并用部分唯一索引 UNIQUE(manager_id) WHERE manager_id IS NOT NULL。

  • 向后兼容的只读视图（可选）

    CREATE OR REPLACE VIEW emp_department_assignments_v AS
    SELECT
      e.emp_id,
      e.emp_name,
      d.dept_id,
      d.dept_name,
      dm.manager_id,
      m.emp_name AS manager_name,
      d.office_building,
      d.office_floor
    FROM employees e
    JOIN departments d ON d.dept_id = e.dept_id
    LEFT JOIN department_managers dm ON dm.dept_id = d.dept_id
    LEFT JOIN employees m ON m.emp_id = dm.manager_id;
    

    注：原表中的 manager_name 来自经理员工的 emp_name，通过连接获得，避免冗余。

• 预期改进效果

  • 消除更新/删除/插入异常：部门楼层、经理变更只需更新 departments 或 department_managers 一行；删除员工不再丢失部门与楼层知识。
  • 数据一致性提升：通过唯一约束确保“每楼层唯一部门”“每部门唯一经理、每经理仅管一部门”。
  • 冗余显著减少：manager_name/office信息不再在每个员工行重复存储。
  • 查询可预测：明确的外键关系与索引提升连接性能，典型查询（按部门/楼层/经理检索员工）可通过已建议索引高效执行。
  • 满足 BCNF：各关系内所有决定因素均为候选键。

• 实施建议和注意事项

  • 数据质量核查（迁移前执行）
    • 验证 FD 一致性（如有冲突需先清洗）：

      -- 1) 同一 dept_id 是否对应多个 (building,floor)
      SELECT dept_id, count(DISTINCT office_building, office_floor)
      FROM emp_department_assignments
      GROUP BY dept_id
      HAVING count(DISTINCT office_building, office_floor) > 1;
      
      -- 2) 同一 manager_id 是否映射多个 dept_id
      SELECT manager_id, count(DISTINCT dept_id)
      FROM emp_department_assignments
      GROUP BY manager_id
      HAVING count(DISTINCT dept_id) > 1;
      
      -- 3) 同一 (building,floor) 是否映射多个 dept_id
      SELECT office_building, office_floor, count(DISTINCT dept_id)
      FROM emp_department_assignments
      GROUP BY office_building, office_floor
      HAVING count(DISTINCT dept_id) > 1;
      

    • 如存在结果行，表示与给定约束不一致，需先治理数据。
  • 迁移步骤（建议在事务中执行）
    1. 建立新表（如上 DDL）。
    2. 装载 departments（去重保证一致）：

       INSERT INTO departments (dept_id, dept_name, office_building, office_floor)
       SELECT DISTINCT dept_id, dept_name, office_building, office_floor
       FROM emp_department_assignments;
       

    3. 装载 employees：

       INSERT INTO employees (emp_id, emp_name, dept_id)
       SELECT DISTINCT emp_id, emp_name, dept_id
       FROM emp_department_assignments;
       

    4. 装载 department_managers（去重）：

       INSERT INTO department_managers (dept_id, manager_id)
       SELECT DISTINCT dept_id, manager_id
       FROM emp_department_assignments;
       

    5. 使用校验查询确认计数与约束满足；再根据计划弃用旧表或改为只读视图。
  • 约束与索引
    • 必要唯一性：departments(office_building, office_floor)、department_managers.manager_id（保证“唯一经理/唯一部门”）。
    • 常用连接索引：employees(dept_id)、department_managers(manager_id)。
  • 变更与删除语义
    • 建议 ON DELETE RESTRICT 于 employees.dept_id 与 department_managers.manager_id，避免误删导致部门/经理关系悬挂；departments -> department_managers 采用 ON DELETE CASCADE 可在删除部门时自动清理经理映射。
  • 可选业务性约束
    • 若需“经理必须属于其所管理的部门”，可通过触发器或延迟检查实现，但这属于业务规则，未在给定 FDs 中强制，不在本次 BCNF 约束范围内。
  • 性能评估
    • 常见查询将增加 1-2 次连接，但因冗余减少与索引可控，整体可维持或提升稳定性；写操作更小且一致性更强。

至此，分解后的三张表均满足 BCNF，且通过唯一性与外键完整性约束，将原先由非超键决定的依赖固化为架构内的可验证规则。

表结构分析总结

• 当前表 enrollment 用于记录学生选课与成绩，主键为 (student_id, course_id)。
• 已知函数依赖：
  • student_id → student_name, student_phone
  • course_id → course_name
  • (student_id, course_id) → term, grade
• 现状中学生信息与课程名称随每条选课记录重复，存在明显冗余与更新不一致风险。

规范化问题识别

• 部分依赖（违反第二范式 2NF）：
  • 非主属性 student_name, student_phone 仅依赖候选键的真子集 student_id。
  • 非主属性 course_name 仅依赖候选键的真子集 course_id。
• 潜在的更新异常：
  • 更新异常：学生改名或电话变更需批量更新多条 enrollment 记录；课程改名亦然。
  • 插入异常：新增学生（未选课）或新增课程（未被选）无法单独插入。
  • 删除异常：删除最后一条选课记录可能导致学生或课程信息被意外“删除”。

各级范式符合度评估

• 1NF：满足（原子性字段，无重复组）。
• 2NF：不满足（存在非主属性对组合主键的部分依赖）。
• 3NF：不满足（因未消除上述部分依赖，非主属性通过主键子集间接依赖候选键）。
• BCNF：不满足（存在由非超键决定的依赖：student_id → student_name 等）。

具体的规范化步骤

目标：消除部分依赖，将描述“实体自身属性”的字段移至其各自的实体表；保留“实体间关系属性”在关联表中。最终达到 3NF/BCNF。

1. 分解实体与关系

• 学生表 Students（存放学生的本征属性）
  • student_id PK
  • student_name
  • student_phone
• 课程表 Courses（存放课程的本征属性）
  • course_id PK
  • course_name
• 选课表 Enrollments（存放关联与关系属性）
  • student_id FK → Students.student_id
  • course_id FK → Courses.course_id
  • term, grade
  • PK 建议：
    • 若业务规则保证“同一学生对同一课程最多一条有效选课记录”，可用 (student_id, course_id) 作为主键（与现状一致）。
    • 若允许重修（同一学生可在不同学期多次选修同一课程），应将 term 纳入键或引入独立的开课（CourseOffering）实体。请按实际规则选取：
      • 方案A：PK(student_id, course_id, term)
      • 方案B：单独的 CourseOfferings(offering_id PK, course_id, term, …)，Enrollments 以 (student_id, offering_id) 作为外键/主键。

2. 消除依赖后的验证

• Students：student_id → student_name, student_phone，决定项为主键，满足 BCNF。
• Courses：course_id → course_name，决定项为主键，满足 BCNF。
• Enrollments：
  • (student_id, course_id) → term, grade（或扩展键包含 term 时，键 → 其它属性），决定项为主键，满足 BCNF。
• 分解无损且保持依赖（student 与 course 的依赖在各自表中直接保持；enrollment 的依赖在关联表中保持）。

3. SQL Server 参考建表语句（按“不可重修”场景；可根据实际改为将 term 纳入主键）

CREATE TABLE dbo.Students (
  student_id   INT           NOT NULL PRIMARY KEY,
  student_name NVARCHAR(60)  NOT NULL,
  student_phone VARCHAR(30)  NULL
);

CREATE TABLE dbo.Courses (
  course_id    INT            NOT NULL PRIMARY KEY,
  course_name  NVARCHAR(100)  NOT NULL
);

CREATE TABLE dbo.Enrollments (
  student_id INT          NOT NULL,
  course_id  INT          NOT NULL,
  term       NVARCHAR(20) NOT NULL,
  grade      CHAR(2)      NULL,
  CONSTRAINT PK_Enrollments PRIMARY KEY (student_id, course_id),
  CONSTRAINT FK_Enrollments_Students FOREIGN KEY (student_id)
    REFERENCES dbo.Students(student_id),
  CONSTRAINT FK_Enrollments_Courses FOREIGN KEY (course_id)
    REFERENCES dbo.Courses(course_id)
);

• 若允许重修，将主键改为 (student_id, course_id, term)，并相应调整 PK 声明。

4. 数据迁移步骤（无损分解）

• 建表（新三表）。
• 去重插入维度数据：
  • INSERT INTO Students SELECT DISTINCT student_id, student_name, student_phone FROM enrollment;
  • INSERT INTO Courses SELECT DISTINCT course_id, course_name FROM enrollment;
• 插入关联数据：
  • INSERT INTO Enrollments(student_id, course_id, term, grade) SELECT DISTINCT student_id, course_id, term, grade FROM enrollment;
• 验证计数与主外键一致性后，从原 enrollment 中删除冗余列 student_name, student_phone, course_name，或弃用旧表。

预期改进效果

• 一致性提升：学生与课程的本征属性在各自表中唯一存放，消除更新/插入/删除异常。
• 冗余降低：减少重复存储的姓名、电话、课程名。
• 维护性增强：信息职责单一（单一事实单一存放处），字段变更影响面更小。
• 性能权衡：查询选课列表时需要 JOIN 至 Students/Courses 获取名称信息，读查询会略增一次或两次连接；但更新学生或课程信息时成本显著下降。可通过常用查询建立合适索引缓解连接开销。

实施建议和注意事项

• 主键选择：
  • 明确业务规则后再最终确定 Enrollments 的主键是否包含 term；如存在重修或补考等情形，建议纳入 term（或引入独立开课维度）。
• 约束与数据质量：
  • 为外键启用引用完整性（已在 DDL 中体现）。
  • 可按实际评分与学期编码规范添加 CHECK 约束（例如 grade 取值域、term 格式），以提升数据质量。
• 索引建议（不改变范式）：
  • PK 会自动建立聚集或非聚集索引（取决于设置）。针对常见查询模式，可在 Enrollments 上视需要增加辅助索引（例如按 course_id 或 term 的过滤查询）。
• 字符集与类型：
  • 若涉及多语言姓名/课程名，建议使用 NVARCHAR（示例已采用）。
• 变更与回滚：
  • 在低峰时段执行迁移；迁移前做全量备份；迁移后进行行数校验与抽样业务核对，再切换应用读写路径。