Databases & the Relational Model
A-Level Computer Science Topic 8 17:17 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Before databases, each program kept its own flat files — one file per program. Picture a shop.
在数据库出现之前，每个程序都各自保存自己的文件。
The sales program, the billing program, and the shipping program each store the customer's address — the same address, written three times.
想象一家商店。 销售程序、 账单程序和发货程序，各自都存了客户的地址——同一个地址，被写了三遍。
Then Alice moves house.
后来爱丽丝搬家了。
One file is updated. The others are not.
一个文件更新了，其他的没有。
Now three files disagree, and nobody knows which one is right.
现在三个文件互相矛盾，没人知道哪个是对的。
This is the mess that databases were invented to fix.
这就是数据库被发明出来要解决的乱局。
Today we replace that mess with one shared database, built on the relational model.
今天我们用一个共享的数据库来取代那种乱局，它建立在关系模型之上。
We'll meet tables and keys, entity diagrams, normalisation, and the software that runs it all.
我们会认识表和键、实体图、规范化，以及运行这一切的软件。
Let's begin.
让我们开始吧。
File-based storage has three deep limits.
基于文件的做法有三个深层问题。
Redundancy: the same data sits in many files, wasting space.
冗余：同一份数据存在许多文件里，浪费空间。
Inconsistency: those copies are updated separately, so they drift apart — exactly what happened to Alice's address.
不一致：这些副本各自被更新，于是彼此背离——正是爱丽丝地址遇到的情况。
And dependence: each program is tied to its file's format, so changing the format means rewriting every program.
还有依赖：每个程序都被绑在它文件的格式上，所以改动格式就意味着重写每一个程序。
A single database fixes all three.
一个统一的数据库能一次解决这三个问题。
Here is the shape of the problem.
问题的形状就在这里。
The payroll program has its own data file, and the sales program has its own separate data file.
工资程序有它自己的数据文件，销售程序也有它自己单独的数据文件。
Look at the staff number: it is stored twice, once in each.
看那个员工编号：它被存了两次，两边各一份。
That single fact is the root of everything.
这一个事实就是一切问题的根源。
It is data redundancy, and the moment one program updates its copy the two disagree, which is data inconsistency.
这叫数据冗余，而只要有一个程序更新了自己那一份，两边就对不上了，这就是数据不一致。
There is a third problem too, called data dependence: each program has the file's layout built into its code, so changing the format means rewriting every program that touches it.
还有第三个问题，叫数据依赖：每个程序都把文件的格式写死在代码里， 所以一旦格式改变，凡是用到它的程序都得重写。
The relational database fixes all three at once by inverting the picture.
关系数据库把这三个问题一次解决，办法是把整幅图反过来。
There is now one shared database, and one piece of software in front of it: the database management system, the DBMS.
现在只有一个共享的数据库， 前面站着一个软件：数据库管理系统，简称 DBMS。
Both applications go through it.
两个应用程序都要经过它。
Because there is a single copy of each fact, redundancy is gone and inconsistency cannot arise.
因为每个事实只存一份，冗余没有了，不一致也就无从产生。
And because the DBMS holds the table designs, the validation rules and the access rights, the programs no longer need to know how anything is stored — that is data independence.
又因为表的设计、验证规则和访问权限都由 DBMS 掌管， 程序不再需要知道任何东西是怎么存的——这就是数据独立性。
A relational database stores data in tables.
关系数据库把数据存放在表中。
Each row is a record, also called a tuple — one customer, or one order.
每一行是一条记录——一个客户，或一个订单。
Each column is a field — one piece of information.
每一列是一个字段——一条信息。
Every table needs a primary key: a field that uniquely identifies each record, never null and never duplicated, like a customer's ID.
每个表都需要一个主键：一个对每一行都唯一的字段， 比如客户编号。
And a foreign key links two tables: the Order table holds each customer's ID, which matches the primary key over in the Customer table.
而外键把两个表连接起来：订单表里存着每个客户的编号， 它与客户表中的主键相匹配。
That link is how tables connect — the data itself is never copied.
这种连接就是表与表相连的方式——数据本身从不被复制。
The syllabus wants more than just primary and foreign.
考纲要求的不只是主键和外键。
A composite key is a primary key made of two or more fields together, used when no single field is unique on its own.
复合键是由两个或更多字段合起来构成的主键， 用在没有任何单个字段能独自唯一标识记录的时候。
A candidate key is any field, or combination, that could serve as the primary key — you pick one of the candidates and the rest stand by.
候选键是任何一个、 或任何一组本来就可以充当主键的字段——你从这些候选里挑一个，其余的候着。
A secondary key is a non-primary field that you index because you search on it often, and indexing simply builds a look-up structure so searches and joins run faster.
次键是一个非主键字段，因为你经常按它查找，所以给它建了索引； 而索引不过就是建立一个查找结构，让检索和连接跑得更快。
And the rule that holds it all together is referential integrity: every foreign-key value must match a primary key that actually exists, so you can never have an orphan record pointing at nothing.
把这一切绑在一起的规则是参照完整性：每一个外键值都必须对应一个真实存在的主键， 这样就绝不会出现指向虚无的孤立记录。
This is what the link actually looks like.
这条连接实际长什么样，就在这里。
The CUSTOMER table has CustomerID as its primary key — unique for every row, and never empty.
CUSTOMER 表以 CustomerID 作为主键—— 每一行都唯一，而且从不为空。
The ORDER table has its own primary key, OrderID, and then a second field also called CustomerID.
ORDER 表有自己的主键 OrderID， 然后还有一个同样叫 CustomerID 的字段。
That one is the foreign key: its value is a CustomerID that already exists over in CUSTOMER.
那一个就是外键： 它的值是一个已经存在于 CUSTOMER 表里的 CustomerID。
That is the whole mechanism.
整套机制就是这么简单。
No pointers, no addresses, no duplicated customer details in the ORDER table — just a value that matches a value.
没有指针，没有地址，ORDER 表里也没有重复的客户资料——只是一个值对上另一个值。
Exams ask you to write a design down, and there is a shorthand notation for it.
考试会要求你把设计写下来，而这有一套速记写法。
Write the table name, then its fields in brackets.
先写表名，再把字段放进括号里。
Underline the primary key — that is how you show which field it is, and leaving the underline off costs the mark.
给主键加下划线——这就是你表明哪个字段是主键的方式，漏掉下划线就要丢分。
Then mark the foreign key, usually with an arrow or a note naming the table it points to.
然后标出外键，通常用一个箭头，或者写一句注明它指向哪张表。
So CUSTOMER, with CustomerID underlined, then Name and Phone; and ORDER, with OrderID underlined, then CustomerID marked as the foreign key to CUSTOMER, then OrderDate.
于是就是 CUSTOMER，CustomerID 加下划线，然后是 Name 和 Phone； 再是 ORDER，OrderID 加下划线，然后 CustomerID 标为指向 CUSTOMER 的外键，最后是 OrderDate。
To design a database, we draw an entity-relationship diagram.
要设计一个数据库，我们会画一张实体关系图。
Each entity — say Student and Course — is a box, joined by a line.
每个实体——比如学生和课程——是一个方框， 用一条线连起来。
The line's ends show the cardinality: one-to-one, one-to-many — one class, many students — or many-to-many, like students and courses.
线的两端标出基数：一对一、一对多——一个班有许多学生—— 或者多对多，比如学生和课程。
A many-to-many link cannot be stored directly.
多对多的关系无法直接存储。
We break it with a link table in the middle, turning one many-to-many into two one-to-many relationships.
我们用一张中间的连接表把它拆开，把一个多对多变成两个一对多的关系。
The symbols on the ends of the relationship line are called crow's-foot notation, and they are worth learning properly because a question can turn on one of them.
关系线两端的那些符号叫做鸦爪记号，值得认真记住，因为一道题可能就卡在其中一个上。
A single bar means one.
一条竖杠表示一。
A three-pronged foot means many.
一个三叉的爪表示多。
A bar with a bar means one and only one.
两条竖杠表示有且只有一个。
A circle with a bar means zero or one, so the relationship is optional.
一个圆圈加一条竖杠表示零个或一个，也就是这个关系是可选的。
A crow's foot with a bar means one or many, and a crow's foot with a circle means zero or many.
鸦爪加一条竖杠表示一个或多个，鸦爪加一个圆圈表示零个或多个。
The circle always means the relationship may not exist at all, and the bar always means exactly one.
圆圈永远表示这个关系可以完全不存在，而竖杠永远表示恰好一个。
A many-to-many relationship cannot be stored directly in a relational database, and this is how you get around it.
多对多关系无法直接存进关系数据库，而这就是绕过它的办法。
Students take many courses, and courses have many students.
学生选很多门课，课程也有很多学生。
You put a third table in the middle — a link table, here called ENROLMENT — which holds StudentID and CourseID as two foreign keys, and usually a little extra data of its own, such as the enrolment date.
你在中间放第三张表—— 连接表，这里叫 ENROLMENT——它保存 StudentID 和 CourseID 这两个外键， 通常还带一点属于它自己的额外数据，比如选课日期。
Notice what happened to the arrows: one many-to-many became two one-to-many relationships, and every one of those the database can store.
注意箭头发生了什么变化： 一个多对多变成了两个一对多，而这两个数据库都存得下。
When each fact is stored in just one place, we call the design normalised.
当每一个事实都只存放在一个地方时，我们就说这个设计是规范化的。
We reach it step by step, through normal forms.
我们一步一步地达到它， 经过一系列范式。
First normal form, 1NF, removes repeating groups.
第一范式去掉重复的组。
Second, 2NF, removes fields that depend on only part of the key.
第二范式去掉只依赖键的一部分的字段。
Third, 3NF, removes fields that depend on another non-key field.
第三范式去掉依赖于另一个非键字段的字段。
The goal is simple: store every fact exactly once.
目标很简单：每个事实只存一次。
Then updating Alice's address means changing one row — never three.
这样一来，更新爱丽丝的地址就只需改动一行——而不是三处。
Normalisation is the process of organising tables to cut redundancy and inconsistency, and you go through the normal forms in order.
规范化就是重新组织表以减少冗余和不一致的过程，而你要按顺序走过这些范式。
First normal form: every field holds a single atomic value, there are no repeating groups, and the table has a primary key.
第一范式：每个字段只保存一个不可再分的原子值，没有重复组，而且表有主键。
Second normal form: it is in first normal form, and every non-key field depends on the whole primary key — which only ever bites when the key is composite.
第二范式：它已经满足第一范式，并且每个非键字段都依赖于完整的主键—— 这一条只有在主键是复合键时才会真正起作用。
Third normal form: it is in second normal form, and every non-key field depends only on the primary key and not on another non-key field.
第三范式：它已经满足第二范式， 并且每个非键字段只依赖主键，而不依赖另一个非键字段。
Aim for third normal form.
目标是做到第三范式。
Each fact is then stored exactly once, so insert, update and delete anomalies simply cannot happen.
这样每个事实恰好只存一次，插入、更新和删除异常就根本不可能发生。
The price is more tables and more joins.
代价是表更多，连接也更多。
Here it is on real data.
用真实数据看看它是什么样。
On the left, one table where every order carries the customer's name and phone number along with it.
左边是一张表，每一笔订单都把客户的姓名和电话一起带着。
If a customer places ten orders, their name sits in the table ten times — and if they change their phone number, you have ten rows to update, and any one you miss makes the database contradict itself.
如果一位客户下了十笔订单，他的名字就在表里出现十次—— 而一旦他换了电话号码，你就有十行要改，只要漏掉任何一行，数据库就自相矛盾了。
On the right, the same information split in two: an ORDER table and a CUSTOMER table, joined by the customer's ID.
右边是同样的信息拆成两份：一张 ORDER 表和一张 CUSTOMER 表，用客户编号连接起来。
Now the name is stored exactly once, and there is only one row to change.
现在名字只存了一次，要改也只有一行。
Let us normalise one.
我们来规范化一张表。
The table ORDER holds OrderID, CustomerID, CustomerName, ProductID and Quantity, and the composite primary key is OrderID together with ProductID.
ORDER 表里有 OrderID、CustomerID、CustomerName、ProductID 和 Quantity，复合主键是 OrderID 加上 ProductID。
The method is always the same: test each non-key field against the key and ask what it really depends on.
方法永远一样： 拿每个非键字段去对照主键，问它到底依赖什么。
Quantity depends on both OrderID and ProductID — you need to know which order and which product before you know how many.
Quantity 同时依赖 OrderID 和 ProductID—— 你得先知道是哪一笔订单、哪一件商品，才知道数量是多少。
That is fine.
这没问题。
But CustomerID depends on OrderID alone, which is only part of the composite key.
可是 CustomerID 只依赖 OrderID，而那只是复合主键的一部分。
That is a partial dependency, and a partial dependency breaks second normal form.
这就是部分依赖，而部分依赖破坏了第二范式。
So split it in two: ORDER_LINE holding OrderID, ProductID and Quantity, and ORDER holding OrderID, CustomerID and CustomerName.
所以把它拆成两张： ORDER_LINE 保存 OrderID、ProductID 和 Quantity，ORDER 保存 OrderID、CustomerID 和 CustomerName。
We are not finished.
还没完。
Test the new table for third normal form.
拿新的这张表去检验第三范式。
In that ORDER table the key is OrderID, and CustomerName depends on CustomerID — which is not the key.
在那张 ORDER 表里，键是 OrderID， 而 CustomerName 依赖的是 CustomerID——那并不是键。
A non-key field depending on another non-key field is a transitive dependency, and that breaks third normal form.
一个非键字段依赖另一个非键字段，这叫传递依赖，它破坏了第三范式。
So split again: ORDER keeps OrderID and CustomerID, and a new CUSTOMER table holds CustomerID and CustomerName.
所以再拆一次：ORDER 保留 OrderID 和 CustomerID，新的 CUSTOMER 表保存 CustomerID 和 CustomerName。
Here is the exam point that decides the marks.
下面这一点决定了你能不能拿到分。
You must name the dependency that breaks each form — partial breaks second, transitive breaks third.
你必须说出是哪一种依赖破坏了哪一个范式——部分依赖破坏第二范式，传递依赖破坏第三范式。
Writing "it has repeated data" describes the symptom, and earns nothing.
写"它有重复数据"只是在描述症状，一分也拿不到。
If a question just says "produce a third-normal-form design", follow this recipe.
如果题目只说"给出一个第三范式的设计"，就照这个流程走。
Find the entities and their attributes.
找出实体和它们的属性。
Choose a primary key for each one.
给每一个实体挑一个主键。
Split repeating or non-atomic fields out, and you are in first normal form.
把重复的或者不是原子的字段拆出去，你就到了第一范式。
Split fields that depend on part of a composite key, and you are in second.
把只依赖复合键一部分的字段拆出去，你就到了第二范式。
Split fields that depend transitively on the key, and you are in third.
把传递依赖于键的字段拆出去，你就到了第三范式。
Finally, add the foreign keys that put the relationships back.
最后，加上外键，把各个关系重新连起来。
One piece of software manages the whole database.
有一款软件管理着整个数据库。
It stores each fact once, and controls who may see what.
它把每个事实只存一次，并控制谁能看到什么。
It lets many people work at the same time, keeps the data correct with integrity rules, takes a backup so the data can be recovered, and groups changes into transactions that all succeed or all fail together.
它让许多人同时工作，用完整性规则保持数据正确，为数据做备份， 并把一组改动打包成事务——它们要么全部成功，要么全部失败。
Programs talk to it using a query language.
程序用一种查询语言与它对话。
That language has two halves: one to build the structure — the tables and keys — and one to work with the data: insert, update, and search.
这种语言有两个部分：一部分用来搭建结构——表和键——另一部分用来处理数据：插入、更新和查询。
Now the DBMS itself, feature by feature, because each one answers a file-based limitation.
现在逐条看 DBMS 本身，因为每一项功能都在回答文件式存储的一个缺陷。
A data dictionary describes every table, field, type and key, so programs ask it rather than hard-coding the structure — that is what buys data independence.
数据字典描述了每一张表、每一个字段、类型和键，于是程序去问它， 而不是把结构写死在代码里——这正是数据独立性的来源。
Concurrent access control uses locks so many users can work at once without corrupting each other's changes.
并发访问控制用锁让许多用户同时工作，又不会破坏彼此的修改。
A transaction is a group of operations that all succeed or all fail together, so money never leaves one account without arriving in the other.
事务是一组要么全部成功、要么全部失败的操作， 所以钱绝不会从一个账户离开却没有到达另一个账户。
Views are virtual tables that show each user only their slice of the data, which is per-field security.
视图是虚拟表，只让每个用户看到属于他的那一部分数据，这就是字段级的安全。
And the whole design is held as a logical schema — the logical structure, independent of how the bytes are physically stored; producing it is data modelling.
而整个设计以逻辑模式的形式保存——它是逻辑结构，与字节在物理上怎么存无关； 把它做出来，就是数据建模。
Two more parts to name: a query processor runs the queries, and a developer interface gives the tools and APIs applications are built with.
还有两个部分要点名：查询处理器负责执行查询， 开发者接口提供工具和 API，应用程序就是用它们搭起来的。
SQL, the Structured Query Language, splits into two halves, and exams love asking you which is which.
SQL，也就是结构化查询语言，分成两半，而考试很喜欢问你哪个是哪个。
DDL basics first: the Data Definition Language builds and changes the structure — tables, keys and constraints.
数据定义语言，简称 DDL，负责建立和修改结构：表、键和约束。
Then DML basics: the Data Manipulation Language works with the data inside that structure — inserting, updating, deleting and querying.
数据操纵语言，简称 DML，负责处理这个结构里面的数据：插入、更新、删除和查询。
A quick test — CREATE TABLE is DDL because it makes a structure; INSERT is DML because it puts a row into one.
一个快速的判断法——CREATE TABLE 是 DDL，因为它造出一个结构； INSERT 是 DML，因为它往结构里放进一行。
Here is DDL in practice.
来看 DDL 的实际用法。
CREATE TABLE names the table and then lists each field with its data type.
CREATE TABLE 给出表名，然后逐个列出字段及其数据类型。
Constraints go right beside the field they apply to: PRIMARY KEY and NOT NULL are the two you will write most.
约束就写在它所作用的字段旁边：PRIMARY KEY 和 NOT NULL 是你写得最多的两个。
To join a table to another, add the FOREIGN KEY clause naming the field, and REFERENCES naming the table and field it points to.
要把一张表连到另一张，就加上 FOREIGN KEY 子句写明是哪个字段， 再用 REFERENCES 写明它指向哪张表的哪个字段。
Two more you should recognise: ALTER TABLE changes an existing table, usually to add a field, and DROP TABLE deletes the table and everything in it.
还有两个你要认得： ALTER TABLE 修改已有的表，通常是加一个字段；DROP TABLE 则把表连同里面的一切删掉。
Finally the data types worth memorising: INTEGER, REAL, VARCHAR with a length, CHAR, DATE, TIME, BOOLEAN, and DECIMAL with a precision and a scale.
最后是值得背下来的数据类型：INTEGER、REAL、带长度的 VARCHAR、CHAR、 DATE、TIME、BOOLEAN，以及带精度和小数位的 DECIMAL。
The query you will write most is SELECT, and it reads almost like English if you take it one clause at a time.
你写得最多的查询是 SELECT，而只要一句一句地看，它读起来几乎就是英语。
SELECT lists the fields you want back — here, Name and Phone.
SELECT 列出你想要拿回来的字段——这里是 Name 和 Phone。
FROM names the table they come from.
FROM 写明它们来自哪张表。
WHERE filters the rows, keeping only those where the condition is true; notice the string in single quotes.
WHERE 过滤行，只留下条件为真的那些；注意字符串是用单引号括起来的。
And ORDER BY sorts the result, with ASC for ascending or DESC for descending.
ORDER BY 给结果排序，用 ASC 表示升序，DESC 表示降序。
Leave out the WHERE and you get every row; leave out the ORDER BY and the order is not guaranteed.
省掉 WHERE，你就会拿到每一行；省掉 ORDER BY，顺序就没有保证。
Often the data you want lives in two tables — the customer's name is in CUSTOMER but the order date is in ORDER.
你要的数据常常分在两张表里——客户姓名在 CUSTOMER，而订单日期在 ORDER。
A join brings them together using the foreign-key relationship.
连接用外键关系把它们拼到一起。
Write INNER JOIN between the two table names, then the ON clause, which states which two fields must match: C dot CustomerID equals O dot CustomerID.
在两个表名之间写 INNER JOIN，然后写 ON 子句， 它说明哪两个字段必须相等：C 点 CustomerID 等于 O 点 CustomerID。
That ON condition is the join, and forgetting it is the classic mistake — you get every row paired with every other row.
那个 ON 条件就是连接本身，忘了它是最经典的错误—— 你会得到每一行和每一行两两配对的结果。
The single letters C and O are table aliases, just short names so you can say which table each field came from.
C 和 O 这两个单字母是表的别名， 只是简短的称呼，好让你说清每个字段来自哪张表。
There are five aggregate functions to know: COUNT, SUM, AVG, MIN and MAX.
有五个聚合函数要掌握：COUNT、SUM、AVG、MIN 和 MAX。
What they share is that they collapse many rows into one value.
它们的共同点是把很多行压缩成一个值。
On their own they give one number for the whole table.
单独使用时，它们给出整张表的一个数字。
Add GROUP BY and they give one number per group instead — here, one row per customer with the number of orders that customer placed.
加上 GROUP BY，它们就改为每一组给出一个数字—— 这里是每位客户一行，写着这位客户下了多少笔订单。
The rule to remember is that every field in the SELECT list must either be in the GROUP BY or be inside an aggregate function.
要记住的规则是：SELECT 列表里的每个字段，要么出现在 GROUP BY 里， 要么被包在一个聚合函数里。
And AS renames the column in the output, which makes the result readable.
而 AS 给输出的列改名，让结果更好读。
Three statements change the data.
有三条语句会改变数据。
INSERT INTO names the table and the fields, then VALUES gives the matching values in the same order.
INSERT INTO 写明表名和字段，然后 VALUES 按同样的顺序 给出对应的值。
UPDATE sets a new value on rows that match its condition.
UPDATE 给符合条件的行设置新值。
DELETE removes rows that match its condition.
DELETE 删掉符合条件的行。
And now the warning that is worth a mark and a great deal of real-world grief: always put a WHERE clause on an UPDATE or a DELETE.
接下来这句警告既值一分，在现实中也能免去大量痛苦： 对 UPDATE 和 DELETE 一定要加 WHERE 子句。
Without one, UPDATE rewrites every row in the table, and DELETE empties it completely.
没有它， UPDATE 会重写表里的每一行，而 DELETE 会把整张表清空。
Five habits that protect easy SQL marks.
有五个习惯能保住 SQL 里那些容易拿的分。
Use the exact table and field names given in the question — inventing a tidier name loses the mark.
使用题目里给出的确切表名和字段名—— 自己改成一个更整齐的名字就要丢分。
Quote strings with single quotes, and never quote numbers.
字符串用单引号括起来，数字则绝不加引号。
Know the comparison operators, and note that not-equal is a less-than sign followed by a greater-than sign, not an exclamation mark.
记熟比较运算符，并注意不等于写作一个小于号后面跟一个大于号，而不是感叹号。
Learn the three pattern tools: LIKE with a percent sign matches any string and an underscore matches one character; IN checks against a list; BETWEEN covers a range inclusively.
学会三个模式工具：LIKE 配百分号匹配任意字符串，配下划线匹配一个字符； IN 检查是否在一个列表里；BETWEEN 覆盖一个闭区间。
And combine conditions with AND, OR and NOT, finishing every statement with a semicolon.
再用 AND、OR、NOT 组合条件，每条语句都以分号结尾。
Three marks to secure.
三个要拿稳的分。
First, define the terms exactly — entity, attribute, primary key, foreign key, and the relationship types.
第一，准确地定义术语——实体、属性、主键、外键，以及各种关系类型。
Second, for each normal form, give the reason: first form, no repeating groups; second, no partial dependency; third, no non-key dependency.
第二，对每一个范式，都要给出理由：第一范式，没有重复的组；第二范式，没有部分依赖； 第三范式，没有非键依赖。
Third, keep the two halves of the query language apart: one defines the structure, the other changes the data.
第三，把查询语言的两部分分清楚：一部分定义结构，另一部分改动数据。
Nail these, and databases are yours.
把这些做扎实，数据库就是你的了。