Why databases and the relational model · 为什么用数据库与关系模型
| English | 中文 | Pinyin · 拼音 |
|---|---|---|
| table/ˈteɪbl/ | 表 | biǎo |
| relational database/rɪˈleɪʃənl ˈdeɪtəbeɪs/ | 关系数据库 | guān xì shù jù kù |
| flat files/flæt faɪlz/ | 平面文件 | píng miàn wén jiàn |
| data redundancy/ˈdeɪtə rɪˈdʌndənsi/ | 数据冗余 | shù jù rǒng yú |
| data inconsistency/ˈdeɪtə ˌɪnkənˈsɪstənsi/ | 数据不一致 | shù jù bù yī zhì |
| integrity/ɪnˈteɡrɪti/ | 完整性 | wán zhěng xìng |
| field/fiːld/ | 字段 | zì duàn |
| entity/ˈentɪti/ | 实体 | shí tǐ |
| record/ˈrekɔːd/ | 记录 | jì lù |
| tuple/ˈtuːpl/ | 元组 | yuán zǔ |
| attribute/ˈætrɪbjuːt/ | 属性 | shǔ xìng |
| primary key/ˈpraɪməri kiː/ | 主键 | zhǔ jiàn |
| composite key/ˈkɒmpəzɪt kiː/ | 复合键 | fù hé jiàn |
| candidate key/ˈkændɪdeɪt kiː/ | 候选键 | hòu xuǎn jiàn |
| secondary key/ˈsekəndəri kiː/ | 次键 | cì jiàn |
| indexing/ˈɪndeksɪŋ/ | 索引 | suǒ yǐn |
| foreign key/ˈfɒrən kiː/ | 外键 | wài jiàn |
| referential integrity/ˌrefəˈrenʃl ɪnˈteɡrɪti/ | 参照完整性 | cān zhào wán zhěng xìng |
| one-to-many/wʌn tə ˈmeni/ | 一对多 | yī duì duō |
| many-to-many/ˈmeni tə ˈmeni/ | 多对多 | duō duì duō |
The paper IBM did not want
- In 1970 an IBM researcher called Edgar Codd published a twelve-page paper proposing that data be stored in simple tables, linked by shared values, and asked for in a language that said what you wanted rather than where it was on the disk.
- IBM already sold a database that stored data as trees tied to the file layout, so it ignored him for years. Every program written against those files had to be rewritten whenever the layout changed.
- By the 1980s every bank, airline and government department was moving to Codd's tables. Half a century later they still run on them.
- This lesson is the problems of the file-based approach, how a relational database 关系数据库 solves them, and the words you must use exactly.
IBM 不想要的那篇论文
- 1970 年,一位叫 Edgar Codd 的 IBM 研究员发表了一篇十二页的论文,提议把数据存在简单的表里,用共享的值连接,并用一种说出你要什么而不是它在磁盘哪里的语言来查询。
- IBM 已经在卖一种把数据存成与文件布局绑定的树的数据库,所以多年忽视了他。针对那些文件写的每个程序,在布局改变时都得重写。
- 到 20 世纪 80 年代,每家银行、航空公司和政府部门都在转向 Codd 的表。半个世纪后,它们仍然运行在这些表上。
- 这一课讲基于文件的方法的问题、关系数据库(relational database)怎样解决它们,以及你必须精确使用的那些词。
The file-based approach
- Before databases, each program kept its own flat files 平面文件: the sales program had a customer file, the accounts program had another, the delivery program a third.
- Each file had a fixed layout that the program's code depended on, and nothing outside the program knew what was in it.
- It works for one small program. The trouble starts when the second program needs the same data.
Three programs, three copies of the customer
基于文件的方法
- 在数据库之前,每个程序保存自己的平面文件(flat files):销售程序有一个客户文件,财务程序有另一个,配送程序有第三个。
- 每个文件有固定的布局,程序代码依赖它,而程序之外没有任何东西知道文件里有什么。
- 对一个小程序这行得通。麻烦从第二个程序需要同样的数据时开始。
三个程序,三份客户副本
The limitations
- Data redundancy 数据冗余: the same data, a customer's address, is held in several files, wasting storage and effort.
- Data inconsistency 数据不一致: the copies are updated separately, so they drift apart and nobody knows which is right.
- Data dependence: programs are tied to the file format, so a change to the layout means rewriting every program that uses the file.
- Integrity 完整性 is hard to enforce, data is hard to share safely, searching across files is slow, and security cannot be set per field.
局限
- 数据冗余(data redundancy):同样的数据——客户的地址——保存在几个文件里,浪费存储和精力。
- 数据不一致(data inconsistency):各副本分别更新,于是彼此偏离,没人知道哪个是对的。
- 数据依赖:程序与文件格式绑定,布局一改,使用该文件的每个程序都要重写。
- 完整性(integrity)难以保证,数据难以安全共享,跨文件搜索缓慢,安全性无法按字段设置。
Storing a customer's address in several separate files leads to: · 把一个客户的地址存在几个分开的文件里导致:
The same data held in many places (redundancy) can be updated separately and become inconsistent. · 同样的数据保存在许多地方(冗余)能被分开更新并变得不一致。
In a flat-file system the same data is often duplicated across files, which can become inconsistent when only one copy is updated. · 在一个平面文件系统中,同样的数据常常在文件间重复,当只有一个副本被更新时它能变得不一致。
That redundancy and the resulting inconsistency is the core problem the relational model solves. · 那种冗余和由此产生的不一致是关系模型解决的核心问题。
Which are limitations of the file-based approach? Select all · 所有 that apply. · 哪些是基于文件方法的局限?选出所有适用的。
Redundancy, inconsistency and data dependence are the three named limitations. Tables and joins belong to the relational approach that replaces it. · 冗余、不一致和数据依赖是三个点名的局限。表和连接属于取代它的关系方法。
How a relational database answers them
- A relational database stores the data in tables 表 managed by one piece of software, the DBMS, which every program uses.
- Each fact is stored once, so redundancy and inconsistency disappear: an address is changed in one place and every program sees the change.
- Programs ask the DBMS for data by name, so the storage can change without the programs changing: data independence.
- Integrity rules, access rights and backups are enforced centrally, and any table can be searched or joined with any other.
One copy of the data, one gatekeeper
关系数据库怎样回应它们
- 关系数据库把数据存在由一个软件——DBMS——管理的表(table)中,每个程序都使用它。
- 每个事实只存一次,所以冗余和不一致消失:地址在一处改变,每个程序都看到变化。
- 程序按名字向 DBMS 索取数据,所以存储可以改变而程序不变:数据独立性。
- 完整性规则、访问权限和备份集中执行,任何表都可以被搜索或与任何其他表连接。
一份数据,一个守门人
How does a relational database remove data inconsistency? · 关系数据库怎样消除数据不一致?
One copy, one update, no drift. The DBMS gives every program the same current value. · 一份副本,一次更新,不会偏离。DBMS 给每个程序同样的当前值。
The vocabulary: tables, rows and columns
- A table (a relation) is a grid of rows and columns, one table for each type of entity 实体, a thing about which data is stored:
CUSTOMER,ORDER,PRODUCT. - A record 记录 is one row, one instance of the entity; the formal word is tuple 元组.
- A field 字段 is one column, one piece of information about each record; the formal word is attribute 属性.
Rows are records, columns are fields, and one column reaches into the other table
术语:表、行和列
- 表(关系)是行和列的网格,每种实体(entity)——被存储数据的事物——一张表:
CUSTOMER、ORDER、PRODUCT。 - 记录(record)是一行,实体的一个实例;正式的词是元组(tuple)。
- 字段(field)是一列,关于每条记录的一项信息;正式的词是属性(attribute)。
行是记录,列是字段,一列伸进另一张表
Read a relational table with SELECT · 用 SELECT 读一个关系表
A relational table is just rows (records) and columns (fields). WHERE keeps the rows that match a condition; SELECT then keeps only the columns you asked for. · 一个关系表只是行(记录)和列(字段)。WHERE 保留匹配一个条件的行;SELECT 然后只保留你要的列。
The vocabulary: keys
- A primary key 主键 is a field, or combination of fields, that uniquely identifies each record; it is never null and never duplicated. A composite key 复合键 is a primary key made of two or more fields.
- A candidate key 候选键 is any field or combination that could serve as the primary key. A secondary key 次键 is a non-primary field that is indexed for fast searching; indexing 索引 builds an index on a field so look-ups and joins run faster.
- A foreign key 外键 is a field whose value matches the primary key of another table, linking the two. In shorthand, the primary key is underlined and the foreign key noted:
术语:键
- 主键(primary key)是唯一标识每条记录的字段或字段组合;它永不为空、永不重复。复合键(composite key)是由两个或更多字段组成的主键。
- 候选键(candidate key)是任何可以作为主键的字段或组合。次键(secondary key)是为快速搜索而建立索引的非主键字段;索引(indexing)在字段上建立索引,让查找和连接更快。
- 外键(foreign key)是值与另一张表的主键匹配的字段,连接两张表。简写中主键加下划线,外键注明:
CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate) -- CustomerID 是外键 → CUSTOMER
A primary key: · 一个主键:
The primary key uniquely identifies each row. A foreign key is the one that links to another table. · 主键唯一标识每一行。外键是链接到另一个表的那个。
Match each kind of key to what it is. · 把每种键与它是什么配对。
The primary key identifies a row; a foreign key links to another table; composite = several fields together; candidate = a possible primary key. · 主键标识一行;外键链接到另一个表;复合 = 几个字段一起;候选 = 一个可能的主键。
A field whose value matches the primary key of another table is a ______ key. · 一个值匹配另一个表主键的字段是一个______键。
The foreign key is what creates the relationship between two tables. · 外键是创造两个表之间关系的东西。
Worked example: name the parts of a library database
- A library records its books, its members and each loan. Identify the entities, a primary key for each, and the foreign keys.
- Entities:
BOOK,MEMBER,LOAN. Primary keys:BookID,MemberID,LoanID, each chosen because it is unique for every record and never blank; a title or a name would not do, since two books can share a title. LOAN(LoanID, BookID, MemberID, DateOut, DateDue):BookIDandMemberIDare foreign keys, each matching the primary key of its own table. A member's phone number belongs inMEMBER, not in every loan.
例题:说出图书馆数据库的各部分
- 一家图书馆记录它的书、会员和每次借阅。指出实体、每个实体的主键,以及外键。
- 实体:
BOOK、MEMBER、LOAN。主键:BookID、MemberID、LoanID,选它们是因为对每条记录唯一且永不为空;书名或姓名不行,因为两本书可以同名。 LOAN(LoanID, BookID, MemberID, DateOut, DateDue):BookID和MemberID是外键,各自匹配自己那张表的主键。会员的电话号码属于MEMBER,不属于每次借阅。
In LOAN(LoanID, BookID, MemberID, DateOut), BookID and MemberID are ____ keys. · 在 LOAN(LoanID, BookID, MemberID, DateOut) 中,BookID 和 MemberID 是____键。
Each matches the primary key of another table, BOOK and MEMBER, and links the loan to them. · 各自匹配另一张表——BOOK 和 MEMBER——的主键,把借阅与它们连接起来。
Relationships and referential integrity
- A relationship links two entities. One-to-one: each member has one library card. One-to-many 一对多: one member has many loans; each loan belongs to one member. Many-to-many 多对多: a book has many authors and an author writes many books.
- Referential integrity 参照完整性 means every foreign-key value must match an existing primary key in the table it refers to: no loan for a member who does not exist, no orphan records.
- The DBMS enforces it: it refuses an insert with an unknown foreign key, and refuses to delete a record that other records still refer to.
关系与参照完整性
- 关系连接两个实体。一对一:每个会员有一张借书卡。一对多(one-to-many):一个会员有多次借阅;每次借阅属于一个会员。多对多(many-to-many):一本书有多个作者,一个作者写多本书。
- 参照完整性(referential integrity)意味着每个外键值都必须匹配它所引用的表中一个已存在的主键:不能有属于不存在会员的借阅,没有孤儿记录。
- DBMS 执行它:拒绝外键未知的插入,拒绝删除仍被其他记录引用的记录。
Referential integrity ensures that: · 参照完整性确保:
It prevents orphan records — you cannot reference a primary key that does not exist. · 它防止孤儿记录——你不能引用一个不存在的主键。
Match each relationship to its type. · 把每个关系与它的类型配对。
Count how many of each entity can be linked to one of the other. Many-to-many needs a link table to store. · 数一数一个实体能与另一个实体的多少个相连。多对多需要连接表来存储。
Worked example: what referential integrity stops
- A customer with three outstanding orders is deleted from the
CUSTOMERtable. Explain how referential integrity applies. - Each order's
CustomerIDis a foreign key that must match an existing customer. Deleting the customer would leave three orders pointing at a record that no longer exists: orphan records. - The DBMS therefore refuses the deletion until the orders are deleted or reassigned, or, if set up to cascade, deletes the orders too. Either way no order ever refers to a customer who is not there.
- Say what the rule is, what would break it, and what the DBMS does.
例题:参照完整性阻止什么
- 一个有三笔未完成订单的客户被从
CUSTOMER表中删除。解释参照完整性怎样起作用。 - 每笔订单的
CustomerID是必须匹配已存在客户的外键。删除该客户会让三笔订单指向一条不再存在的记录:孤儿记录。 - 因此 DBMS 拒绝删除,直到订单被删除或重新分配;或者若设置为级联,则连订单一起删除。无论哪种,都不会有订单引用一个不在的客户。
- 说出规则是什么、什么会破坏它、DBMS 做什么。
Referential integrity allows a customer to be deleted while orders in another table still refer to that customer. · 参照完整性允许在另一张表的订单仍引用某客户时删除该客户。
That would create orphan records. The DBMS refuses the deletion, or cascades it to the orders, so every foreign key keeps pointing at an existing record. · 那会产生孤儿记录。DBMS 拒绝删除,或级联到订单,所以每个外键都始终指向一条存在的记录。
Definitions the examiner accepts
| Term | Definition |
|---|---|
| entity | a thing about which data is stored, represented by one table |
| attribute | one item of data about an entity, a column |
| tuple | one row of a table, one record |
| primary key | an attribute or combination of attributes that uniquely identifies each tuple |
| candidate key | an attribute or combination that could be chosen as the primary key |
| secondary key | an indexed attribute used to search the table quickly |
| foreign key | an attribute in one table that is the primary key of another, forming the link |
| referential integrity | every foreign-key value refers to an existing primary key |
考官接受的定义
| 术语 | 定义 |
|---|---|
| 实体 | 被存储数据的事物,用一张表表示 |
| 属性 | 关于实体的一项数据,一列 |
| 元组 | 表的一行,一条记录 |
| 主键 | 唯一标识每个元组的属性或属性组合 |
| 候选键 | 可以被选为主键的属性或组合 |
| 次键 | 用于快速搜索表的已建索引的属性 |
| 外键 | 一张表中作为另一张表主键的属性,形成连接 |
| 参照完整性 | 每个外键值都引用一个已存在的主键 |
Marks that slip away
- A tuple is a row, an attribute is a column. Swapping them costs both marks.
- "Unique" alone does not define a primary key; it identifies each record and is never null.
- A foreign key may repeat: one customer, many orders. It is the primary key that may not.
- A secondary key is for fast searching, not for identification. Do not call it "a second primary key".
容易丢掉的分
- 元组是行,属性是列。互换会丢掉两分。
- 单说"唯一"定义不了主键;它标识每条记录,并且永不为空。
- 外键可以重复:一个客户,多笔订单。不能重复的是主键。
- 次键是为了快速搜索,不是为了标识。不要叫它"第二个主键"。
You've got it
- the file-based approach suffers redundancy, inconsistency and data dependence, with weak integrity, sharing, searching and security
- a relational database stores each fact once in tables managed by one DBMS, so programs are independent of the storage and rules are enforced centrally
- entity → table · tuple → record, row · attribute → field, column · primary key identifies, foreign key links, candidate could identify, secondary is indexed for searching
- referential integrity: every foreign key matches an existing primary key, so no orphan records
你掌握了
- 基于文件的方法受冗余、不一致和数据依赖之苦,完整性、共享、搜索和安全都弱
- 关系数据库把每个事实存一次在由一个 DBMS 管理的表中,所以程序独立于存储,规则集中执行
- 实体 → 表 · 元组 → 记录、行 · 属性 → 字段、列 · 主键标识,外键连接,候选键可以标识,次键为搜索建索引
- 参照完整性:每个外键匹配一个已存在的主键,所以没有孤儿记录