数据库案例分析与SQL基础入门

# 数据库案例分析与SQL基础入门 ## 一、区域实验室案例分析 ### 1.1 项目背景 区域实验室（Regional Labs）是一家为其他公司和组织提供研发服务的公司。该公司收集了关于项目以及分配到这些项目的员工的数据，这些数据存储在名为 PROJECT 的表中，表结构为：PROJECT (ProjectID, EmployeeName, EmployeeSalary)。 ### 1.2 功能依赖分析 - **判断功能依赖关系**： 1. ProjectID → EmployeeName：一个项目可能有多个员工，所以 ProjectID 不能唯一确定 EmployeeName，该依赖不成立。 2. ProjectID → EmployeeSalary：同理，ProjectID 不能唯一确定 EmployeeSalary，该依赖不成立。 3. (ProjectID, EmployeeName) → EmployeeSalary：项目 ID 和员工姓名组合起来可以唯一确定员工的薪水，该依赖成立。 4. EmployeeName → EmployeeSalary：同名员工可能有不同的薪水，该依赖不成立。 5. EmployeeSalary → ProjectID：相同薪水的员工可能参与不同项目，该依赖不成立。 6. EmployeeSalary → (ProjectID, EmployeeName)：薪水不能唯一确定项目 ID 和员工姓名，该依赖不成立。 ### 1.3 主键及相关问题 - **主键**：由于 (ProjectID, EmployeeName) 能唯一确定表中的每一行，所以 PROJECT 表的主键是 (ProjectID, EmployeeName)。 - **非键属性依赖**：非键属性 EmployeeSalary 依赖于主键 (ProjectID, EmployeeName)，满足依赖关系。 - **范式判断**：该表存在部分依赖（EmployeeName 部分依赖于主键 (ProjectID, EmployeeName)），所以 PROJECT 表处于第一范式（1NF）。 - **修改异常**： - **插入异常**：如果一个新员工还未分配到项目，就无法插入该员工的信息，因为缺少 ProjectID。 - **删除异常**：如果删除一个项目的所有员工信息，可能会丢失该项目的相关信息。 ### 1.4 决定因素分析 - **ProjectID**：不是决定因素，因为它不能单独确定其他属性。 - **EmployeeName**：不是决定因素，不能单独确定其他属性。 - **(ProjectID, EmployeeName)**：是决定因素，基于 (ProjectID, EmployeeName) → EmployeeSalary 这个功能依赖。 - **EmployeeSalary**：不是决定因素，不能确定其他属性。 ### 1.5 传递依赖与关系重构 - **传递依赖**：该表不存在传递依赖。 - **关系重构**：为了消除修改异常，可以将 PROJECT 表拆分为两个表： - PROJECT_INFO (ProjectID) - EMPLOYEE_ASSIGNMENT (ProjectID, EmployeeName, EmployeeSalary) ## 二、花园荣耀项目分析 ### 2.1 功能依赖假设 根据花园荣耀（Garden Glory）收集的关于房产和服务的数据，假设功能依赖关系如下： - PropertyID → (PropertyName, PropertyType, Street, City, ZIP)：房产 ID 可以唯一确定房产的基本信息。 - (PropertyID, ServiceDate) → (Description, Amount)：房产 ID 和服务日期组合可以确定服务的描述和金额。 ### 2.2 设计方案评价 | 设计方案 | 评价 | | --- | --- | | PROPERTY (PropertyName, PropertyType, Street, City, ZIP, ServiceDate, Description, Amount) | 存在数据冗余，因为房产信息和服务信息混合在一起，每次服务记录都会重复存储房产信息。 | | PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, ZIP, ServiceDate, Description, Amount) | 虽然引入了 PropertyID，但仍然存在数据冗余，服务信息和房产信息未分离。 | | PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, ZIP, ServiceDate) 和 SERVICE (ServiceDate, Description, Amount) | 部分分离了房产信息和服务信息，但 SERVICE 表缺少与房产的关联。 | | PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, ZIP, ServiceDate) 和 SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID) | 较好的设计，分离了房产信息和服务信息，并且通过 PropertyID 建立了关联。 | ### 2.3 新增表的处理 如果花园荣耀决定添加 SERVICE_FEE (PropertyID, ServiceID, Description, Amount) 表，将其添加到上述较好的设计方案中。为了减少数据冗余，可以将 SERVICE 表中的 Amount 字段移到 SERVICE_FEE 表中。假设该设计方案可以处理图中的数据，前提是每个服务记录对应一个唯一的 ServiceID，并且通过 PropertyID 与房产信息关联。 ## 三、安妮女王古玩店项目分析 ### 3.1 功能依赖假设 根据安妮女王古玩店（The Queen Anne Curiosity Shop）的销售和采购数据，假设功能依赖关系如下： - 销售数据： - (LastName, FirstName, Phone) → 客户基本信息 - (InvoiceDate, InvoiceItem) → (Price, Tax, Total) - 采购数据： - PurchaseItem → PurchasePrice - (PurchaseItem, PurchaseDate) → Vendor, Phone ### 3.2 设计方案评价 | 设计方案 | 评价 | | --- | --- | | CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price, Tax, Total) | 存在数据冗余，客户信息和销售信息混合。 | | CUSTOMER (LastName, FirstName, Phone) 和 SALE (InvoiceDate, InvoiceItem, Price, Tax, Total) | 分离了客户信息和销售信息，但缺少关联。 | | CUSTOMER (LastName, FirstName, Phone) 和 SALE (InvoiceDate, InvoiceItem, Price, Tax, Total, LastName, FirstName) | 通过客户姓名建立关联，但可能存在同名问题。 | ### 3.3 设计改进 - **添加代理 ID**：在上述较好的设计方案中添加 CustomerID 和 SaleID 作为代理 ID。这样可以提高数据的唯一性和查询效率，避免因姓名等信息重复导致的问题。 - **拆分 SALE 表**：将 SALE 表拆分为 SALE 和 SALE_ITEM 表。SALE 表存储销售的基本信息，SALE_ITEM 表存储销售的具体商品信息。这样可以进一步减少数据冗余，提高数据的灵活性。 ### 3.4 采购数据设计评价 | 设计方案 |