A3.2.4 · Database design
A3.2.4 · 数据库设计
Constructing tables for relational databases
构建关系数据库中的表
Two students called "Alex" look identical in a badly built table, a well-constructed one never lets that ambiguity happen in the first place.
在一张设计糟糕的表中,两个都叫"Alex"的学生看起来一模一样;而一张构建良好的表从一开始就不会让这种模糊情况发生。
Explainer
讲解
A spreadsheet with a "Name" column and nothing else can't tell two same-named students apart, and can't reliably link a student to their club or teacher either. Constructing a proper table fixes both problems at once: it decides exactly which fields exist, what data type each one holds, and, critically, which field or fields make every row unique and how it connects to other tables.
一个只有"Name"列、别无其他的电子表格,无法区分两个同名的学生,也无法可靠地把学生与其社团或老师关联起来。构建一张合适的表能同时解决这两个问题:它精确决定了存在哪些字段、每个字段保存什么数据类型,以及至关重要的一点:哪个或哪些字段能让每一行都唯一,以及它如何与其他表相连。
Constructing a table means deciding its fields (each with a data type, see A3.2.3), then deciding which field or fields act as the primary keyUniquely identifies every record in a table, no two rows can share one. First introduced in A3.1.1., and adding a foreign keyA field that points at another table's primary key, that pointer is what actually forms the link between the two. First introduced in A3.1.1. wherever this table needs to reference another one.
构建一张表意味着决定它的字段(每个字段都有一个数据类型,参见 A3.2.3),然后决定哪个或哪些字段充当主键唯一标识表中的每一条记录,任何两行都不能共享同一个值。首次在 A3.1.1 中介绍。,并在这张表需要引用另一张表时添加外键一个指向另一张表主键的字段,这个指向关系正是两张表之间联系的真正来源。首次在 A3.1.1 中介绍。。
Why this matters for data integrity. A well-defined primary key guarantees every record can be told apart, no ambiguity. A well-defined foreign key is what gives a database referential integrity: the DBMS itself refuses to let that field point at a row that doesn't actually exist, so a dangling reference to nothing simply can't happen. Properly defined tables are also what makes an accurate ERD (A3.2.2) possible in the first place, the diagram is only as correct as the tables it's drawn from.
为什么这对数据完整性很重要。一个定义良好的主键能保证每一条记录都可以被区分,不存在任何模糊。一个定义良好的外键则赋予数据库参照完整性:DBMS 本身会拒绝让该字段指向一个实际上并不存在的行,因此指向虚无的悬空引用根本不可能发生。定义良好的表同样是使准确的 ERD(A3.2.2)成为可能的前提:该图的准确程度,取决于绘制它所依据的表本身有多准确。
Not every table gets a single obvious key, though. Sometimes no individual field is unique on its own, and there's a genuine design choice between a composite keyTwo or more fields kept as separate columns, checked together for uniqueness. First introduced in A3.1.1., a new surrogate ID field, or a concatenated keySeveral attributes' VALUES merged into one new single field. First introduced in A3.1.1. that merges values into one field entirely. The worked example below hits all three.
不过,并非每张表都会有一个单一、显而易见的键。有时没有任何一个单独的字段本身是唯一的,这时就存在一个真正的设计选择:是使用复合键两个或更多字段作为独立的列保留,一起被检验是否唯一。首次在 A3.1.1 中介绍。、一个新增的代理 ID 字段,还是使用拼接键多个属性的值合并成一个新的单一字段。首次在 A3.1.1 中介绍。把多个值完全合并到一个字段中。下面的示例讲解将依次涉及这三种情况。
Worked example
示例讲解
A cooking competition: three tables
一场烹饪比赛:三张表
CHEF, ENTRY and JUDGE_SCORE, constructed one at a time, each one raising a different key decision, the same three decisions any real design runs into, just with fresh data this time.
CHEF、ENTRY 和 JUDGE_SCORE,依次逐一构建,每一张表都引出一个不同的键设计决策,正是任何真实设计都会遇到的那三种决策,只是这次换成了全新的数据。
Check your understanding
检验你的理解
JUDGE_SCORE's primary key field, JudgeDish, holds values like "Elena Marsh-Charred Octopus", one single field, not two. What kind of key is this?
JUDGE_SCORE 的主键字段 JudgeDish 保存着像"Elena Marsh-Charred Octopus"这样的值,是一个单一字段,而不是两个。这是哪种键?
Common mistake
常见错误
A3.2.4
考官报告了 A3.2.4 中出现的这一模式
RecurringWhen a table genuinely has more than one valid key design, like ENTRY below, candidates often name only ONE option and stop, without justifying why that option was chosen over the alternatives. Full marks require the justification, not just a label.
反复出现当一张表确实存在不止一种有效的键设计时,例如下面的 ENTRY,考生常常只说出一种方案就停下,没有说明为什么选择这个方案而不是其他方案。满分需要有理有据的说明,而不仅仅是一个名称。
What a weak answer looks like: "ENTRY's primary key is a composite key of ChefID and DishName." States a valid answer, but doesn't explain why a composite key was chosen over a new EntryID field, or why no single existing field works alone.
薄弱答案的样子: "ENTRY 的主键是 ChefID 和 DishName 组成的复合键。" 给出了一个有效的答案,但没有解释为什么选择复合键而不是新增 EntryID 字段,也没有说明为什么没有任何一个现有字段可以单独使用。
What it should say: "Neither ChefID nor DishName is unique alone, since a chef submits several dishes and a category has several dishes across chefs, so ENTRY needs either a composite key of both fields together, or a new EntryID field added purely to act as a single-column key." Justifies the choice AND names the realistic alternative.
应该这样作答: "ChefID 和 DishName 单独都不是唯一的,因为一位厨师会提交多道菜品,而一个类别下也有多位厨师的多道菜品,所以 ENTRY 需要将两个字段组合成复合键,或者新增一个纯粹用作单列键的 EntryID 字段。" 既说明了选择的理由,也提出了现实中的替代方案。
Revision questions
复习题
Past-paper style. Write an answer, then reveal the marking points and mark yourself.
采用历年真题风格。写下答案,然后展开评分要点给自己打分。
[2]State the primary key you would use for the CHEF table, with a reason.
[2]陈述你会为 CHEF 表使用的主键,并说明理由。
Marking points
评分要点
- A ChefID field (a surrogate key).
- Reason: it is guaranteed unique and never null, and unlike a name it does not change and is not shared by two chefs.
- 一个 ChefID 字段(代理键)。
- 理由:它保证唯一且永不为空,而且与姓名不同,它不会改变,也不会被两位厨师共用。
[3]The ENTRY table has no single field that is unique on its own. State two different valid ways a primary key could be defined for ENTRY, and briefly explain each.
[3]ENTRY 表没有任何单一字段本身是唯一的。陈述两种可以为 ENTRY 定义主键的不同有效方式,并对每种方式作简要说明。
Marking points
评分要点
- Use a composite primary key of ChefID and CompetitionID (or ChefID and Round): the pair is unique even though neither field is unique alone.
- Or add a surrogate EntryID that auto-increments and use that as the primary key.
- Either way the key must be unique for every row and never null.
- 使用 ChefID 与 CompetitionID(或 ChefID 与 Round)的复合主键:即便单个字段都不唯一,这个组合是唯一的。
- 或者增加一个自增的代理键 EntryID,并用它作主键。
- 无论哪种方式,键都必须对每一行唯一且永不为空。
[4]Explain why properly defining the primary and foreign keys in the CHEF, ENTRY and JUDGE_SCORE tables is important for the data integrity of this competition's database.
[4]解释为什么在 CHEF、ENTRY 和 JUDGE_SCORE 表中正确定义主键和外键,对这个比赛数据库的数据完整性很重要。
Marking points
评分要点
- Primary keys stop duplicate CHEF, ENTRY or JUDGE_SCORE rows being created.
- Foreign keys with referential integrity stop a JUDGE_SCORE referring to an ENTRY that does not exist, or an ENTRY referring to a missing CHEF.
- They ensure every score can be traced to a real entry and a real chef, so results cannot be corrupted by dangling links.
- They also control deletion: a chef with entries cannot simply vanish and leave orphaned scores.
- 主键阻止创建重复的 CHEF、ENTRY 或 JUDGE_SCORE 行。
- 带参照完整性的外键阻止某条 JUDGE_SCORE 指向不存在的 ENTRY,或某条 ENTRY 指向缺失的 CHEF。
- 它们确保每个分数都能追溯到真实的参赛项和真实的厨师,所以结果不会被悬空链接破坏。
- 它们还控制删除:有参赛记录的厨师不能就那样消失并留下孤立的分数。
Flashcards
记忆卡片
Active recall for this statement's key terms.
针对本条考纲陈述关键术语的主动回忆练习。
Keep this statement's practice with you
把这条考纲陈述的练习保存下来
Free account: unlock every revision question and flashcard for A3.2.4, and every other IB CS 2027 statement.
免费账户:解锁 A3.2.4 以及所有其他 IB CS 2027 考纲陈述的全部复习题和记忆卡片。