Databases
International Baccalaureate · IB Diploma · Computer Science · SL Topic 3 4:00 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Duplicating a user address in every order can produce conflicting records when the address changes.
在每笔订单中重复存储用户地址，当地址变更时可能会产生冲突记录。
A relational table contains rows and attributes.
关系表包含行和属性。
A primary key identifies a row; a foreign key links to a referenced key in another table.
主键用于标识一行；外键链接到另一张表中被引用的键。
Normalization can reduce avoidable duplication and update anomalies while preserving meaningful relationships.
规范化可以减少可避免的重复和更新异常，同时保留有意义的关系。
Primary key: Attribute set uniquely identifying a table row.
主键：唯一标识表行的属性集。
Foreign key: Attribute set referencing a key in another table.
外键：引用另一张表中键的属性集。
A join combines rows according to a specified condition.
连接操作根据指定条件组合行。
The result count depends on relationship cardinality and filters.
结果数量取决于关系基数和筛选器。
A foreign key constraint enforces a relationship rule; it does not automatically encrypt personal data.
外键约束强制执行关系规则，但不会自动加密个人数据。
In the lab: Design a small synthetic user-order dataset with no real personal information.
在实验环节，设计一个不包含真实个人信息的小型合成用户订单数据集。 声明主键。
Declare keys, test duplicate and missing-reference inserts, then check a query result against a hand-worked expected table.
测试重复插入和缺失引用插入的情况。 然后将查询结果与手工计算出的预期表进行比对。
Known: four customers have respectively two, zero, three, and one orders.
已知：四位客户分别有2、0、3和1个订单。
An inner join of customer to order returns two plus zero plus three plus one equals six rows.
客户到订单的内连接返回2加0加3加1等于6行。
A left join also retains the customer with no order as one null-matched row, giving seven here.
左连接还会保留没有订单的客户作为一行空值匹配行，因此此处结果为7。
Your turn: Three customers have 1, 3 and 2 orders.
轮到你了。 三位客户分别有 1、3 和 2 个订单。
How many rows does the customer-order inner join return?
客户与订单的内连接会返回多少行？
A primary key need not be a person name.
主键不必是人名。
A join is not a simple concatenation of tables.
连接操作不是表的简单拼接。
Use parameterized queries for untrusted input, rather than building SQL from raw strings.
对于不可信输入，应使用参数化查询，而不是用原始字符串构建SQL语句。
Not true: A foreign key automatically encrypts every row.
不正确。 外键不会自动对每一行进行加密。
A foreign key is a referential link between tables - it enforces that the referenced row exists.
外键是表之间的引用链接，用于确保被引用的行存在。
It performs no encryption; protecting row content is a separate security mechanism.
它本身不具备加密功能。 保护行内容属于独立的安全机制。
A relational table contains rows and attributes.
关系表包含行和属性。
A join combines rows according to a specified condition.
连接操作根据指定条件组合行。
Watch for: A primary key need not be a person name.
请注意：主键不一定是人名。
Each order row carries a foreign key that points to one customer primary key.
每一行订单都带有一个外键，指向某个客户的主键。
An inner join keeps only matched pairs, so six rows.
内连接只保留能配对的行，所以是六行。
A left join keeps every customer, so Ben appears once with empty order fields: seven rows.
左连接保留所有客户，所以没有订单的 Ben 也出现一次，订单字段为空：共七行。
Customers A,B,C have 2,0,1 orders.
客户A、B和C分别有2、0和1个订单。
Compare customer-order inner and left-join row counts.
比较客户-订单内连接与左连接的行数。
Why can counting joined rows miscount customers?
为什么统计连接后的行数会导致客户计数错误？
Inner join yields three rows.
内连接产生3行。
Left join yields four, including a null match for B.
左连接产生4行，其中包括B的空值匹配。
Count star counts rows. Count order id excludes nulls. Distinct customer id counts represented customers.
COUNT(*)统计行数；COUNT(order_id)排除空值；DISTINCT customer_id统计有代表性的客户数。
State keys and join conditions.
声明键和连接条件。
The left join retains customer 2 with null order fields.
左连接保留客户2，其订单字段为空值。
COUNT(*) includes that row, while COUNT(o.id) excludes null.
COUNT(*)包含该行，而COUNT(o.id)排除空值。
Primary keys distinguish records; customer count is not order count.
主键用于区分记录；客户数量不等于订单数量。
The interpolated join keyword comes from a fixed internal list, not user input.
插值的连接关键字来自固定的内部列表，而非用户输入。
Retrieve the technical meanings, then vary one input in the original transfer and justify the model condition.
回顾技术含义，然后在原始迁移中改变一个输入，并论证模型条件。