关系模式转换与SQL函数实现——数据库竞赛真题解析

在2020年的CSP-J1组初赛题目中，主要考察的是数据库设计和关系模式转换。问题的核心在于将实体类型转换为关系模式，并理解不同类型的联系如何体现在关系模型中。以下是关键知识点的解析： 1. **实体类型转换为关系模式**: - 实体类型是现实世界中的基本单位，如教学管理系统的系、教师、课程等。首先，需要识别实体及其属性，例如系的编号、名称和电话，教师的教师号、姓名、工资和聘用日期，课程的课程号、名称、学分和先修课程信息。 - 每个实体的属性将成为关系模式的字段。例如，系的属性将构成关系"系"，教师的属性构成关系"教师"，课程的属性构成关系"课程"。 2. **处理二元联系**: - 二元联系指的是两个实体之间的关系，如系与教师之间的“聘用”关系，教师与课程之间的“讲授”关系，以及课程之间的“先修”关系。这些关系根据题目描述分别是一对一（R1 和 R2）、多对一（R1 和 R4, R3 和 R4），以及可能的多对多联系。 - 对于一对一关系，如R1和R2，通常会通过添加外键来关联两个实体，如A、B和C之间的关系。 - 多对一关系，如R1和R4，涉及一个实体拥有多个其他实体，例如教师讲授多门课程，这时需要在多端实体（如课程表）中添加对方实体的键作为外键。 3. **SQL Server 2008 示例**: - 提供的数据库示例中，商品表和销售表之间存在一对多关系，商品表中的商品号对应销售表中的商品号。用户定义的函数f_Profit需要根据类别查询商品在2012年的销售总利润，这可能涉及到从商品表获取进货单价，从销售表获取销售数量和销售单价，然后计算总利润。 4. **ER图与关系模式转换**: - E-R图（实体-关系图）用于构建概念模型，根据题目描述，系统包含系、教师、课程和它们之间的联系。ER图需遵循3NF（第三范式），这意味着消除部分依赖和传递依赖。 - 转换为关系模式时，一对一和一对多关系通常会转化为独立的关系，而多对多联系则会创建新的中间表，如讲授表，同时保留两端实体的键作为外键。 5. **关系模式的主码和外码**: - 主键标识每个关系中的唯一记录，如系表的系编号、教师表的教师号、课程表的课程号。外码是引用其他关系中的主键，如教师表中的系编号引用系表，先修课程表中的先修课程号引用课程表。 此题旨在测试学生对数据库设计基础理论的理解，包括实体-关系模型、关系模式转换和数据库规范化，以及实际操作中如何利用这些概念解决SQL查询问题。