Database
International Baccalaureate · IB Diploma · Computer Science · SL Topik 3 4:23 Narasi bahasa Inggris · Subtitle bahasa Inggris + 中文 disematkan langsung
Bab-bab
Transkrip
A school library keeps loans in one spreadsheet. Each row has the student's name, form, the book and the date.
学校图书馆把借书记录放在一张电子表格里，每一行都有学生姓名、班级、书和日期。
When a student changes form, dozens of rows must be edited, and some are missed.
学生换班时，几十行都要改，总有漏改的。
Now two rows disagree about the same student.
于是两行对同一个学生的记录互相矛盾。
A database is designed to stop this by storing each fact once.
数据库的设计就是为了避免这种情况：每个事实只存一次。
A database management system stores the data, runs queries, controls access and makes backups.
数据库管理系统负责存储数据、执行查询、控制访问和备份。
A relational database fixes the flat file's problems. Each fact is stored once, so there is no redundancy and nothing to disagree.
关系数据库解决了平面文件的问题：每个事实只存一次，没有冗余，也就没有矛盾。
SQL can join tables, and user accounts control who sees what.
SQL 能连接多张表，用户账户控制谁能看什么。
A primary key is unique for every record, like student ID in the Student table.
主键对每条记录都是唯一的，比如学生表中的学号。
A foreign key holds another table's primary key: the Loan table stores the student ID, linking each loan to a student.
外键保存另一张表的主键：借阅表里存着学号，把每次借阅和一个学生联系起来。
Referential integrity means a loan cannot refer to a student who does not exist.
参照完整性意味着借阅记录不能指向一个不存在的学生。
An entity is a thing we store data about.
实体是我们要存储数据的对象。
Students and books are many-to-many: one student borrows many books, and one book is borrowed by many students.
学生和书是多对多关系：一个学生借很多书，一本书被很多学生借。
That cannot be stored directly, so a link table, Loan, sits between them.
这不能直接存储，所以中间放一张连接表——借阅表。
One student makes many loans, and each loan is of one book.
一个学生有很多借阅记录，每条借阅记录对应一本书。
Normalisation removes redundancy.
规范化用来消除冗余。
First normal form: every field holds one value, so the order gets one row per item.
第一范式：每个字段只有一个值，所以订单每件商品一行。
Second normal form: no field depends on only part of the key, so the customer moves to an Order table and the price to an Item table.
第二范式：没有字段只依赖于键的一部分，所以顾客移到订单表，价格移到商品表。
Third normal form: the phone depends on the customer, not the order, so it moves to a Customer table.
第三范式：电话依赖于顾客而不是订单，所以移到顾客表。
After normalising there are four tables.
规范化之后有四张表。
Customer holds the name and phone.
顾客表存姓名和电话。
Order holds the customer ID.
订单表存顾客编号。
OrderLine holds the order, the item and the quantity.
订单明细表存订单、商品和数量。
Item holds the description and price.
商品表存描述和价格。
Li Na's phone number is now stored once.
李娜的电话现在只存一次。
SQL queries the tables.
SQL 用来查询表。
Select name from Student where year group is eleven, ordered by name descending.
从学生表中选出年级为十一的学生姓名，按姓名降序排列。
Where keeps only the year eleven rows, Ana and Chen.
WHERE 只保留十一年级的行：安娜和陈。
Order by descending sorts Z to A, so the result is Chen, then Ana.
降序从 Z 到 A 排，所以结果是陈，然后是安娜。
A join combines tables by matching each foreign key with its primary key.
连接通过让外键和主键相匹配来组合多张表。
Starting from Loan, join Student on student ID and Book on book ID.
从借阅表出发，按学号连接学生表，按书号连接图书表。
Loan one hundred gives Ana and Dune, one hundred and one Ana and Holes, one hundred and two Chen and Emma.
借阅一百得到安娜和《沙丘》，一百零一得到安娜和《别有洞天》，一百零二得到陈和《爱玛》。
Ben has no loans, so he does not appear.
本没有借书，所以不出现。
Aggregate functions such as count, sum and average work on groups of rows.
COUNT、SUM、AVG 等聚合函数作用于行的分组。
Select student ID and count from Loan, grouped by student ID, gives student one with two loans and student three with one.
从借阅表选出学号和计数，按学号分组，得到学生一借了两本，学生三借了一本。
Where filters rows before grouping; having filters the groups afterwards.
WHERE 在分组前筛选行，HAVING 在分组后筛选组。
Insert adds a new record, such as the book Wonder.
INSERT 添加一条新记录，比如《奇迹男孩》这本书。
Update changes fields in the rows that match the where clause, and delete removes them.
UPDATE 修改符合 WHERE 条件的行中的字段，DELETE 删除这些行。
Always check for the where clause: an update or delete without one changes every row in the table.
一定要检查有没有 WHERE：没有它，UPDATE 或 DELETE 会改动表中的每一行。
A transaction is a group of operations that must all succeed or all fail, like a bank transfer.
事务是一组必须全部成功或全部失败的操作，比如银行转账。
Atomicity: all of it or none of it, so money is never lost or created.
原子性：要么全做，要么全不做，钱不会凭空丢失或多出。
Consistency: from one valid state to another.
一致性：从一个有效状态到另一个有效状态。
Isolation: transactions at the same time do not interfere.
隔离性：同时进行的事务互不干扰。
Durability: once committed, a change survives a crash.
持久性：一旦提交，更改在崩溃后依然存在。
Try these. Explain why students and books need a link table. Normalise a booking table where capacity depends on the room. Write SQL for every book by Austen. And explain atomicity with a bank transfer.
试试这些：解释为什么学生和书需要连接表；规范化一张容量依赖于房间的预订表；写出查询奥斯汀所有书的 SQL；用银行转账解释原子性。
In the exam, say a primary key is unique and a foreign key links to it.
考试时，说主键是唯一的，外键链接到主键。
In normalisation, name the dependency you remove, then show the new tables.
规范化时说出要消除的依赖，再写出新表。
Write SQL keywords in capitals with a semicolon, give every join its on condition, and check updates and deletes for a where clause.
SQL 关键字大写，以分号结尾，每个连接都写 ON 条件，并检查 UPDATE 和 DELETE 有没有 WHERE。