规范化 a database schema
Normalization reduces 数据 redundancy 和 improves 完整性. Learn 1NF, 2NF, 3NF, 和 BCNF 具有 practical examples.
- 1
Normalization forms: 1NF (atomic values, 否 repeating groups), 2NF (1NF + 否部分 dependencies 开 composite 关键), 3NF (2NF + 否 transitive dependencies), BCNF (3NF + 每个 determinant 是候选键). 每个 form reduces redundancy 和更新 anomalies. 最多 practical applications use 3NF 或 BCNF.
- 2 SQL 格式化器 →
格式化 the schema DDL (CREATE TABLE statements) to review the structure. Look for: repeating groups (violates 1NF), partial key dependencies (violates 2NF), non-key dependencies (violates 3NF). Format makes the schema structure and relationships clear.
- 3 表格编辑与转换器 →
Visualize 表结构和 relationships. 识别冗余数据 (相同信息 stored 在多个 places) 和 functional dependencies (哪个 columns 确定哪个). 此 helps 识别哪个正态 form 模式 satisfies 和什么到 fix.
常见问题
- 应该 I 总是 normalize 到 3NF?
- 为 OLTP (transactional) systems, 是 — 3NF/BCNF reduces redundancy 和 anomalies. 为 OLAP (analytical) systems, denormalization (star/snowflake 模式) 是 preferred — fewer joins, faster queries. 权衡: normalization improves 写入效率和 完整性; denormalization improves 读取性能.
- 什么是 更新 anomalies?
- 在 unnormalized schemas: insertion (cannot 添加 department 具有否 employees), deletion (deleting 最后 employee loses department), 更新 (changing department name requires updating 所有 employee rows). Normalization eliminates 这些通过 separating concerns 为不同 tables.