First exams 2027 · SL & HL 2027 年首次开考 · SL 与 HL

A3.2.1 · Database design

A3.2.1 · 数据库设计

Database schemas: conceptual, logical, physical

数据库模式:概念模式、逻辑模式、物理模式

The same database can be described three different ways, each one a real answer to "what does this database look like?", just at a different level of detail.

同一个数据库可以用三种不同的方式来描述,每一种都是对"这个数据库长什么样?"这个问题的真实回答,只是详细程度不同而已。

Core for SL & HL SL 与 HL 核心内容 Often "distinguish between..." · AO1/AO2 常考"区分……(distinguish between)" · AO1/AO2 Command term: describe 命令词:describe(描述)

Explainer

讲解

Imagine you're briefing three different people about a new booking system for Ashworth Driving School. The owner just wants to know "learners book lessons, and every lesson has one instructor." A database designer wants to know exactly which fields each table has and which are keys. The engineer installing the actual server wants to know which fields get indexed, so that pulling up tomorrow's timetable is instant. All three are describing the SAME database, just at three different levels of detail. That's exactly what a database schema is.

想象你要向三个不同的人介绍 Ashworth 驾校的新预订系统。校长只想知道"学员预订课程,每节课都有一位教练"。数据库设计师想准确知道每张表有哪些字段,哪些是键。负责安装实际服务器的工程师想知道哪些字段需要建立索引,以便调出明天的时间表时能瞬间完成。这三个人描述的是同一个数据库,只是详细程度分为三个不同的层级。这正是数据库模式的意义所在。

This statement sits inside A3's own guiding question: what are the principles, structures and operations that form the basis of database systems? A schema IS that structure, made explicit, and describing it at the right level of detail is a skill this statement tests directly.

这条考纲陈述属于 A3 自身的核心引导问题:构成数据库系统基础的原理、结构与操作是什么?模式正是这种结构的明确表达,而以恰当的详细程度描述它,正是这条考纲陈述直接考察的技能。

Df

Before a single row of real data exists, something has to decide what shape that data is even allowed to take, which fields a table has, how one table connects to another. That plan is the database schema. It's the logical view a DBMS (database management system) actually works from, the layer that sits between whatever an application asks for and whatever ends up physically stored.

在真实的数据行存在之前,必须先有某种东西来决定数据可以呈现的形态:一张表有哪些字段,一张表如何与另一张表相连接。这份规划就是数据库模式。它是数据库管理系统(DBMS)实际运作所依据的逻辑视图,是位于应用程序所请求内容与最终物理存储内容之间的一层。

?

A schema is a definition, not the data itself. Lessons(LessonID: INTEGER PRIMARY KEY, ...) is a schema, it says WHAT STRUCTURE every lesson will have. The actual lesson rows sitting in the table, "Lesson #4471, 3 March at 09:00, for Learner #29," are data, not schema. This holds at all three levels: a conceptual schema abstractly defines what entities and relationships exist, a logical schema abstractly defines what tables, fields and keys exist, a physical schema abstractly defines what storage and indexing exist. Each level adds detail, but every level is still a DEFINITION of structure and organization, never the stored values themselves.

模式是一种定义,而不是数据本身。Lessons(LessonID: INTEGER PRIMARY KEY, ...) 是一个模式,它说明的是每一节课将具备的结构。表中实际存在的课程行,例如"课程 #4471,3 月 3 日 09:00,学员 #29",才是数据,而不是模式。这一点在三个层级都成立:概念模式抽象地定义了存在哪些实体与关系,逻辑模式抽象地定义了存在哪些表、字段与键,物理模式抽象地定义了存在哪些存储与索引方式。每一层都增加了细节,但每一层始终是对结构与组织的定义,而不是所存储的实际值本身。

The three schema levels

三种模式层级

The same database gets described three times, each level building on the one before it by adding more detail, never contradicting it.

同一个数据库被描述了三次,每一层都在前一层的基础上添加更多细节,而不与其相矛盾。

1. Conceptual 1. 概念

Pure big-picture thinking: which entities exist, what describes each one, how they relate to each other. Nothing here mentions a table, a data type, or any particular DBMS, and nothing here is specific enough yet to actually build from.

纯粹的宏观思考:存在哪些实体、每个实体由什么描述、它们之间如何关联。这里完全不涉及表、数据类型或任何特定的 DBMS,内容也还不够具体,尚无法据此实际构建系统。

2. Logical 2. 逻辑

A detailed design: entities become tables, attributes get data types and constraints, primary and foreign keys are established, relationships are defined using those keys. Still independent of any specific DBMS product.

详细的设计:实体转变为表,属性获得数据类型与约束,建立主键与外键,关系则通过这些键来定义。仍然独立于任何特定的 DBMS 产品。

3. Physical 3. 物理

Where the logical design finally meets one real, chosen DBMS product: which storage devices hold it, which fields get an index, how it is partitioned and accessed, the physical truth of where every byte actually sits.

逻辑设计最终与一个真实、已选定的 DBMS 产品对接的地方:数据由哪些存储设备保存、哪些字段建立索引、如何分区与访问,也就是每一个字节实际存放位置的物理真相。

Using schemas well, at the right level, improves several things about a database at once:

恰当地在正确层级上使用模式,能同时改善数据库的多个方面:

What a good schema improves

良好模式能带来哪些改善

  • Data organization: every table's shape is fixed and known ahead of time, instead of being reinvented row by row. 数据组织:每张表的形态都是预先固定且已知的,而不是逐行重新构想。
  • Data security: a view or a permission can be scoped to exactly the fields one user should see, no more. 数据安全:视图或权限可以精确限定在某个用户应该看到的字段范围内,不多也不少。
  • Data integrity: a constraint written into the schema catches a bad value at the door, before it ever becomes a stored row. 数据完整性:写入模式中的约束会在数据尚未成为存储行之前,在入口处就拦截不合法的值。
  • Performance: a query only runs fast if the schema it's querying was actually designed with that query in mind. 性能:只有当模式在设计时就已考虑到某个查询,该查询才能真正高效运行。
  • Scalability: the physical layer can be reworked, a new index added, storage moved, without the logical schema above it, or the application built on that, ever needing to change. 可扩展性:物理层可以被重新调整、新增索引、迁移存储,而其上层的逻辑模式,以及基于该逻辑模式构建的应用程序,都无需随之改变。

Worked example

示例讲解

One booking system, described three ways

一个预订系统,三种描述方式

The school needs a database where learners book lessons, and every lesson is taught by exactly one instructor. Click through and watch the SAME system get more detailed at every step, conceptual first, then logical, then physical.

驾校需要一个数据库,让学员能够预订课程,而每节课都由恰好一位教练授课。逐步点击,观察同一个系统在每一步变得更加详细:先是概念层,再是逻辑层,最后是物理层。

Step 1 of 3 Conceptual

Check your understanding

检验你的理解

A design lists Lessons(LessonID: INTEGER PRIMARY KEY, LessonDate: DATE, LearnerID: INTEGER FOREIGN KEY), with no mention of which DBMS will run it. Which schema level is this?

某设计列出了 Lessons(LessonID: INTEGER PRIMARY KEY, LessonDate: DATE, LearnerID: INTEGER FOREIGN KEY),但没有提及将由哪个 DBMS 运行。这属于哪一个模式层级?

Common mistake

常见错误

Markers reported this pattern for A3.2.1 考官在 A3.2.1 中报告了这一模式

RecurringQuestions that ask you to separate one schema level from the next are a recurring style for this statement, and candidates often answer it by describing what EACH schema contains without ever stating what actually separates them: the level of implementation detail, and whether a specific DBMS is assumed. Two lists of features side by side isn't a distinction until the difference itself is named.

反复出现要求区分不同模式层级的问题,是这条考纲陈述的常见题型,而考生常常只是分别描述每种模式包含什么内容,却从未说明真正区分它们的关键:实现细节的层级,以及是否假定了特定的 DBMS。把两份特征清单并排列出,并不构成一种区分,除非明确指出差异本身。

✕

What a weak answer looks like: "Conceptual schema has entities and relationships. Logical schema has tables and keys." True, but it never says WHY logical has more detail, or what conceptual deliberately leaves out.

薄弱答案的样子: "概念模式有实体和关系。逻辑模式有表和键。" 这没错,但它从未说明为什么逻辑模式有更多细节,也没说明概念模式刻意省略了什么。

✓

What it should say: "The conceptual schema stops at entities and relationships, on purpose, nothing about implementation belongs there yet. The logical schema is the one that fills that gap in: data types, constraints, primary and foreign keys, while still staying independent of whichever DBMS product eventually runs it." Names the actual boundary between the two levels, not just their contents.

应该这样表述: "概念模式有意止步于实体与关系,任何与实现相关的内容此时都不应出现。逻辑模式则填补了这个空白:数据类型、约束、主键与外键,同时仍然独立于最终运行它的任何 DBMS 产品。" 明确指出了两个层级之间真正的界限,而不只是它们各自的内容。

Revision questions

复习题

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

采用历年真题风格。写下答案,然后展开评分要点给自己打分。

Question 1 · 1 mark 第 1 题 · 1 分

[1]Using the booking system above, state one piece of information that appears in the logical schema but not in the conceptual schema.

[1]参照上文的预订系统,说出一项出现在逻辑模式中但不出现在概念模式中的信息。

Question 2 · 3 marks 第 2 题 · 3 分

[3]The school owner and the database designer describe the same booking system differently. Explain what separates their two views, referring to implementation detail.

[3]校长与数据库设计师对同一个预订系统的描述不同。请解释是什么将他们的两种视角区分开来,并提及实现细节。

Question 3 · 4 marks 第 3 题 · 4 分

[4]A library database stores Books and Members, where one member can borrow many books. Describe what the physical schema for this system would add on top of its logical schema.

[4]某图书馆数据库存储 Books 与 Members,其中一位会员可以借阅多本书。请描述该系统的物理模式会在其逻辑模式基础上增加哪些内容。

Flashcards

记忆卡片

Active recall for this statement's key terms.

针对本条考纲陈述关键术语的主动回忆练习。

Prototype render. Content grounded in the real IB CS 2027 syllabus for A3.2.1 (all three schema levels and the benefits of a schema); the worked example and practice questions are original. Not yet wired to live routes.