数据库设计与实体关系模型转换指南

### 数据库设计与实体关系模型转换指南 在数据库开发领域，设计一个高效且合理的数据库是至关重要的。本文将深入探讨数据库设计的多个方面，包括不同项目的需求分析、实体关系（E - R）数据模型的创建与扩展，以及如何将 E - R 数据模型转换为关系数据库设计。 #### 不同项目的需求与 E - R 模型设计 1. **MPIS 系统** - **需求**：MPIS 系统需要记录学生在高线大学的入学日期、毕业日期和所获学位，以及顾问与学生、顾问与导师、导师与学生的分配开始和结束日期。 - **操作**：扩展和修改之前创建的 E - R 数据模型，使用 IE 乌鸦脚 E - R 模型绘制 E - R 图，并对最小和最大基数的决策进行合理说明。 2. **Writer’s Patrol 交通罚单案例** - **绘制 E - R 模型**：基于交通罚单表格绘制 E - R 数据模型，使用五个实体，创建标识符（注意可能需要的复合标识符，必要时可使用代理标识符），并根据表格上的数据项指定实体的属性。 - **确定关系**：明确实体之间的关系，命名关系，指定关系类型和基数，并说明关于最小和最大基数的决策，指出哪些基数可以从表格数据中推断，哪些需要与系统用户确认。 3. **Garden Glory 项目** - **原始需求**：公司希望扩展数据库应用，除记录物业服务外，还需跟踪设备、设备在服务中的使用情况和设备维修，员工使用某些设备前需要培训，且服务要与子属性相关联。 - **设计步骤** - 为 Garden Glory 数据库架构绘制 E - R 数据模型，使用 IE 乌鸦脚 E - R 模型，并说明最小和最大基数的决策。 - 扩展和修改 E - R 数据模型以满足新需求，创建适当的标识符和属性，并再次说明基数决策。 - 描述验证修改后模型的方法。 4. **The Queen Anne Curiosity Shop 项目** - **需求变化**：公司希望扩展数据库应用，包括修改库存处理方式和简化客户与员工数据的存储。 - **设计过程** - 为现有数据库架构绘制 E - R 数据模型，使用 IE 乌鸦脚 E - R 模型并说明基数决策。 - 仅添加库存系统需求扩展和修改 E - R 数据模型，创建适当的标识符和属性并说明基数决策。 - 仅添加客户和员工数据存储需求扩展和修改 E - R 数据模型，同样创建标识符和属性并说明基数决策。 - 合并上述两个扩展后的 E - R 数据模型以满足所有新需求，并进行必要的额外修改。 - 描述验证最终数据模型的方法。 #### E - R 数据模型转换为关系数据库设计 1. **数据库设计阶段** - 系统分析和设计书籍通常将设计分为概念设计、逻辑设计和物理设计三个阶段。我们讨论的数据库设计基本等同于逻辑设计，它是概念设计为在特定 DBMS 产品中实现而进行的修改。 - 数据库设计是一组可在 DBMS 中实际实现为数据库的规范，与通用的、非 DBMS 特定的数据模型不同，它是针对特定 DBMS 产品的设计。 2. **转换步骤** - **创建实体表** - 为数据模型中的每个实体创建一个表，指定主键（必要时考虑使用代理键）。 - 指定每个列的属性，包括数据类型、空状态、默认值（如有）和数据约束（如有）。 - 验证表是否正确规范化。 - **创建关系**：通过放置外键来创建关系，包括强实体关系（1:1、1:N、N:M）、ID 依赖和非 ID 依赖的弱实体关系、子类型和递归关系（1:1、1:N、N:M）。 #### 实体的关系模型表示示例 1. **ITEM 实体** - 将 ITEM 实体表示为表时，创建名为 ITEM 的表，将实体的属性作为列，ItemNumber 作为主键。 - 考虑列属性： - 数据类型：根据具体情况选择合适的数据类型。 - 空状态：仅将 ListPrice 设置为 NULL，其他列设置为 NOT NULL。 - 默认值：为 QuantityOnHand 指定默认值 0。 - 数据约束：添加 (ListPrice > Cost) 约束以确保销售价格不低于成本。 - 验证规范化：ITEM 表只有一个候选键 ItemNumber，且无其他函数依赖，因此已规范化到 Boyce - Codd 范式（BCNF）。 2. **CUSTOMER 实体** - 初始转换后的表为 CUSTOMER (CustomerNumber, CustomerName, StreetAddress, City, State, ZIP, ContactName, Phone)。 - 发现函数依赖：ZIP → (City, State) 和 ContactName → Phone，表明该表未规范化。 - 规范化处理：将依赖属性分离到新表中，得到 CUSTOMER (CustomerNumber, CustomerName, StreetAddress, ZIP, ContactName)、ZIP (ZIP, City, State) 和 CONTACT (ContactName, Phone)，并设置参照完整性约束。 - 考虑去规范化：将 ZIP、City 和 State 保留在 CUSTOMER 表中，虽然会导致规范化问题，但可使设计更易用且不会造成实际的修改问题。 3. **SALES_COMMISSION 实体** - 初始表为 SALES_COMMISSION (SalespersonNumber, SalespersonLastName, SalespersonFirstName, Phone, CheckNumber, CheckDate, CommissionPeriod, TotalCommissionSales, CommissionAmount, BudgetCategory)。 - 存在函数依赖：SalespersonNumber → (SalespersonLastName, SalespersonFirstName, Phone, BudgetCategory)、CheckNumber → CheckDate 和 (SalespersonNumber, CommissionPeriod) → (TotalCommissionSales, CommissionAmount, CheckNumber, CheckDate)。 - 规范化处理：将依赖属性分离到新表中，得到 SALESPERSON、SALES_COMMISSION 和 COMMISSION_CHECK 三个表，并设置参照完整性约束。 - 评估去规范化：在这种情