数据库

在数据库之前,程序把数据存储在平面文件(flat files)中——通常每个程序一个文件。这对小数据没问题,但在规模上会崩溃。

局限

• 数据冗余(data redundancy)——相同的数据(一个客户的地址)保存在几个文件中,每个程序一个,既浪费存储,每个副本又都要更新。
• 数据不一致(data inconsistency)——一个副本更新了而另一个没有,文件之间互相矛盾,没人知道哪个是对的。
• 数据依赖(data dependence)——每个程序都是针对其文件的确切布局写的;改一个字段的长度或加一个字段,每个读该文件的程序都要重写。
• 无法共享访问——一个程序使用文件时文件被锁定,用户不能同时处理数据。
• 完整性(integrity)薄弱——没有集中规则阻止无效值或指向不存在客户的链接;安全薄弱——访问按文件而不是按字段控制;跨文件的查询每次都要一个新程序。

一个关系数据库(relational database)通过把数据存储在由一个所有程序都使用的软件(DBMS)管理的表中来修复这些。

为什么关系数据库更好——三分答案。 每项数据只在一个表中存储一次,表之间用键连接,所以没有冗余、没有不一致;数据与程序独立,程序向 DBMS 索取需要的数据,结构改变时不受影响;DBMS 集中执行完整性规则,按用户和按字段控制访问,允许多用户同时使用,并且无需编写新程序就能回答任何查询。

例题。 一家维修店用基于文件的方法存储顾客、设备和维修工作,每个程序一个文件。给出这造成的三个问题,并描述关系数据库如何消除它们。

顾客的姓名和电话既存在维修文件里又存在发票文件里(冗余);顾客换号码时,一个文件更新了而另一个没有(不一致);店里想要一份新报表——每位技师的维修数——就得写一个新程序去读文件(无法即席查询)。在关系数据库中,顾客只在 CUSTOMER 表中存储一次,REPAIR 表用 CustomerID 引用它,所以改动只做一次、处处可见;报表只是一条 SQL 查询。

• 表(table,关系)——行和列的一个网格;每种实体(entity)一个表(例如 CUSTOMER)。
• 记录(record,行,也叫元组(tuple))——一行;实体的一个实例。
• 字段(field,列,也叫属性(attribute))——一列;关于每条记录的一条信息。
• 主键(primary key)——唯一标识每条记录的一个字段(或多个字段);从不为空或重复。
• 外键(foreign key)——一个值匹配另一个表主键的字段,把两者链接起来。
• 复合键(composite key)——由两个或更多字段一起构成的一个主键。
• 候选键(candidate key)——任何可以是主键的字段。
• 次键(secondary key)——一个为快速搜索而建立索引的非主字段。
• 索引(indexing)——在一个字段上建立一个索引,使查找和连接运行得更快。
• 参照完整性(referential integrity)——每个外键值必须匹配一个现有的主键(没有孤立记录)。

一个表用简写书写,主键加下划线、外键标注:

CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate)   -- CustomerID is FK → CUSTOMER

例题。 说明关系数据库模型中实体、主键和参照完整性的含义,并补全元组和属性的术语 ↔ 描述表。

实体是被存储数据的对象——一个人、物体或事件——它成为一个表。主键是唯一标识表中每条记录的属性(或属性组合)。参照完整性指每个外键值必须匹配它所引用的表中某个主键的值,这样一条记录就不能引用一条不存在的记录。元组是表的一行(一条记录);属性是一列(一个字段)。记住这些配对:表/关系,记录/元组,字段/属性。

一个实体关系图(entity-relationship diagram)显示结构:每个实体是一个矩形,每个关系是一条线,在每端标注基数(cardinality):

• 一对一(1:1)。
• 一对多(one-to-many,1:M)——每个客户有许多订单;每个订单有一个客户。
• 多对多(many-to-many,M:N)——学生选许多课程,而课程有许多学生。

一个多对多关系不能直接存储。把它拆成两个一对多关系,通过一个容纳两个外键的连接表(link table):

ENROLMENT(StudentID, CourseID, EnrolmentDate)

为给定的一组表画 E-R 图。 每个表成为一个实体。凡是一个表持有指向另一个表的外键,就存在一个关系;它从持有外键的表(多的一端)连到以该键为主键的表(一的一端)。一个有两个外键、没有其他身份的表通常是解决多对多关系的连接表。给每条线标上关系类型。

例题。 一家维修店有表 CUSTOMER(CustomerID, Name, Phone)、DEVICE(DeviceID, CustomerID, Type, Model)、TECHNICIAN(TechnicianID, Name) 和 REPAIR(RepairID, DeviceID, TechnicianID, RepairDate, Cost)。指出各关系及其类型。

DEVICE 持有 CustomerID,所以 CUSTOMER–DEVICE 是一对多(一个顾客,许多设备)。REPAIR 持有 DeviceID,所以 DEVICE–REPAIR 是一对多;它还持有 TechnicianID,所以 TECHNICIAN–REPAIR 是一对多。没有直接的 CUSTOMER–REPAIR 线:联系经由 DEVICE。三条线,三个鸦爪,都在 REPAIR 或 DEVICE 那一端。

规范化(normalisation)组织表以减少冗余和不一致,按顺序经过各范式(normal forms)。

• 第一范式(1NF)——每个字段容纳一个单一(原子(atomic))值,没有重复组,并有一个主键。
• 第二范式(2NF)——处于 1NF,并且每个非键字段依赖于整个主键(只对一个复合键有意义)。
• 第三范式(3NF)——处于 2NF,并且每个非键字段只依赖于主键,而不依赖于另一个非键字段(没有传递依赖(transitive dependency))。

一个 3NF 设计把每个事实存储一次,所以插入/更新/删除异常消失。权衡是更多的表和更多的连接。以 3NF 为目标。

要产生一个 3NF 设计:找出实体及其属性;为每个选择一个主键;拆分重复/非原子字段(1NF);拆分依赖于一个复合键一部分的字段(2NF);拆分传递地依赖于键的字段(3NF);为关系添加外键。

例题。 表 ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity) 的复合主键是 (OrderID, ProductID)。把它规范化到 3NF。逐个用主键去检验每个非键字段。Quantity 同时依赖 OrderID 和 ProductID,这没问题。但 CustomerID 只依赖 OrderID - 也就是复合主键的一部分。这是部分依赖,所以该表不满足 2NF。把它拆成 ORDER_LINE(OrderID, ProductID, Quantity) 和 ORDER(OrderID, CustomerID, CustomerName)。再检验 3NF:在新的 ORDER 表中,CustomerName 依赖于 CustomerID,而后者不是主键 - 这是传递依赖。再拆一次:ORDER(OrderID, CustomerID) 和 CUSTOMER(CustomerID, CustomerName)。要点出破坏每种范式的那种依赖(部分依赖破坏 2NF,传递依赖破坏 3NF);写"它有重复数据"只是在描述症状,得不到分。

对任何表都要问的三个问题。 每个单元格是否都是单一值、没有重复组? 不是,就不在 1NF。如果键是复合的,每个非键字段是否都依赖于整个键? 若某字段只依赖于它的一部分,就存在部分依赖(partial dependency),表不在 2NF。每个非键字段是否只依赖于键? 若某字段依赖于另一个非键字段,就存在传递依赖,表不在 3NF。"解释为什么该表不在 3NF"的答案要说出依赖的名称和涉及的字段。

例题。 一家租车店把每次租车记录为 RENTAL(RentalID, RentalDate, CustomerID, CustomerName, CustomerPhone, CarReg, CarModel, DailyRate, Days),一次租车可以包含几辆车。解释为什么该表未规范化,并给出 3NF 设计。

不在 1NF: 汽车字段 CarReg, CarModel, DailyRate, Days 构成一个重复组——一次租车有几辆车。把它们移到 RENTAL_CAR(RentalID, CarReg, CarModel, DailyRate, Days),复合键为 (RentalID, CarReg)。不在 2NF: 在 RENTAL_CAR 中,CarModel 和 DailyRate 只依赖于 CarReg——部分依赖。把它们移到 CAR(CarReg, CarModel, DailyRate),留下 RENTAL_CAR(RentalID, CarReg, Days)。不在 3NF: 在 RENTAL 中,CustomerName 和 CustomerPhone 依赖于非键字段 CustomerID——传递依赖。把它们移到 CUSTOMER(CustomerID, CustomerName, CustomerPhone),留下 RENTAL(RentalID, RentalDate, CustomerID)。3NF 设计是四个表——CUSTOMER、RENTAL、RENTAL_CAR、CAR——以 CustomerID、RentalID 和 CarReg 为外键;给每个主键加下划线。

一个 DBMS(数据库管理系统)集中管理数据库。修复基于文件的局限的特性:

• 数据字典(data dictionary)——对每个表、字段、类型和键的描述;程序查询它而不是硬编码结构。
• 冗余/一致性控制——每个事实存储一次。
• 并发访问(concurrent access)控制——锁和事务让许多用户一次工作。
• 备份(backup)和恢复;安全和每用户权限。
• 完整性规则——键、唯一和范围约束,集中强制执行。
• 事务(transactions)——一组全部成功或全部失败的操作。
• 视图(views)——向每个用户显示"他们的"那一片数据的虚拟表。
• 数据管理(data management)和数据建模(data modelling)——控制数据如何存储,并把它的结构定义为一个逻辑模式(logical schema,逻辑设计,独立于物理存储)。
• 数据完整性(data integrity)和数据安全(data security)——集中强制正确性并控制访问。
• 一个查询处理器(query processor)运行查询;一个开发者接口(developer interface)给出构建应用程序的工具和 API。

它的工具包括一个数据字典编辑器、一个查询构建器、一个表单构建器、一个报表生成器、用户管理,以及一个 SQL 编辑器。

数据字典里有什么("给出三项"的题目):表的名称;每个表中字段的名称;每个字段的数据类型和长度;主键、外键以及表之间的关系;验证规则;索引;以及谁可以访问每个表。它是元数据——关于数据的数据——DBMS 用它检查每条查询和每次改动。

DBMS 如何保证数据安全("描述两种方法"的题目):身份验证(authentication)——任何访问之前先输入用户名和密码,或用生物特征;访问权限——每个用户或用户组只被允许读、写或删除某些表或字段,常通过视图实现;对存储的数据和传给它的数据加密,使复制出去的文件无法读取;定期备份,使数据在丢失后可以恢复;还有一份记录谁改了什么的事务日志。

两个软件工具。 开发者接口是程序员用来构建数据库及其上应用程序的工具:创建表、设置键和验证、编写查询和 SQL、设计表单和报表,而不必知道数据在物理上如何存储。查询处理器接受一条查询(来自程序的 SQL,或在接口中构建的查询),对照数据字典检查它,找出最高效的执行方式,取出数据并返回结果。

逻辑模式。 DBMS 把逻辑设计(存在哪些表和字段、它们如何关联)与物理存储(文件、索引、磁盘块)分开。程序面对的是逻辑模式,所以物理存储可以重新组织而一个程序都不用改——这正是基于文件的方法所缺少的数据独立性。

SQL(结构化查询语言,Structured Query Language)有两半:

• 数据定义语言(Data Definition Language,DDL)——创建或改变结构(表、键、约束)。
• 数据操纵语言(Data Manipulation Language,DML)——处理数据(插入、更新、删除、查询(query))。

DDL 基础

CREATE TABLE CUSTOMER (
  CustomerID INTEGER PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Phone VARCHAR(20)
);

添加一个外键:

CREATE TABLE ORDER (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER,
  OrderDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

修改和删除:

ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
DROP TABLE CUSTOMER;

常见类型:INTEGER、REAL、VARCHAR(n)、CHAR(n)(也叫 CHARACTER(n))、DATE、TIME、BOOLEAN、DECIMAL(p, s)。

DML 基础

用 SELECT 查询:

SELECT Name, Phone
FROM CUSTOMER
WHERE City = 'London'
ORDER BY Name ASC;

SELECT 列出字段,FROM 命名表,WHERE 过滤行,ORDER BY 排序。

一个连接(join)用一个外键关系组合两个表:

SELECT C.Name, O.OrderDate
FROM CUSTOMER C INNER JOIN ORDER O
  ON C.CustomerID = O.CustomerID
WHERE O.OrderDate >= '2024-01-01';

聚合函数(aggregate functions,COUNT、SUM、AVG、MIN、MAX)常与 GROUP BY 一起用:

SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDER
GROUP BY CustomerID;

插入、更新、删除:

INSERT INTO CUSTOMER (CustomerID, Name, Phone)
VALUES (101, 'Ada Lovelace', '020-1234-5678');

UPDATE CUSTOMER SET Phone = '020-9999-0000' WHERE CustomerID = 101;

DELETE FROM CUSTOMER WHERE CustomerID = 101;

务必将WHERE条件置于UPDATE和DELETE之上，否则更改将影响每一行。

考试 SQL 的提示

• 使用题目中确切的表名和字段名。
• 用单引号引起字符串('Smith');不要引起数字。
• 比较:=、<、>、<=、>=、<>。
• LIKE 'A%' 匹配任何以 A 开头的(% = 任何字符串,_ = 一个字符);IN (1,2,3);BETWEEN 10 AND 20。
• 用 AND / OR / NOT 组合条件,并以一个分号结束每条语句。

考试要的 DDL 模式。 每条 CREATE TABLE 给每个字段命名并写出类型,标出主键,并声明每个外键及其引用的表;复合键单独占一行声明:

CREATE TABLE RENTAL_CAR (
  RentalID INTEGER,
  CarReg VARCHAR(8),
  Days INTEGER,
  PRIMARY KEY (RentalID, CarReg),
  FOREIGN KEY (RentalID) REFERENCES RENTAL(RentalID),
  FOREIGN KEY (CarReg) REFERENCES CAR(CarReg)
);

例题。 用 CUSTOMER(CustomerID, Name, Phone) 和 DEVICE(DeviceID, CustomerID, Type, Model),写 SQL 脚本:(a) 按姓名字母顺序列出每个拥有类型为 'tablet' 的设备的顾客的姓名和电话;(b) 统计每种类型的设备数;(c) 记录顾客 17 现在的电话号码是 '0771 234 5678';(d) 添加一台新设备,ID 305,类型 'laptop'、型号 'X1',属于顾客 17。

(a)

SELECT CUSTOMER.Name, CUSTOMER.Phone
FROM CUSTOMER INNER JOIN DEVICE
  ON CUSTOMER.CustomerID = DEVICE.CustomerID
WHERE DEVICE.Type = 'tablet'
ORDER BY CUSTOMER.Name ASC;

(b)

SELECT Type, COUNT(DeviceID) AS NumberOfDevices
FROM DEVICE
GROUP BY Type;

(c) UPDATE CUSTOMER SET Phone = '0771 234 5678' WHERE CustomerID = 17; (d) INSERT INTO DEVICE (DeviceID, CustomerID, Type, Model) VALUES (305, 17, 'laptop', 'X1');

评分按子句进行——字段、表、连接条件、WHERE、ORDER BY——因此即使脚本中有一个子句错误，其余部分仍可获得分数。当涉及两个表时，请编写Table.Field。

例题。 解释这段脚本做什么:SELECT T.Name, SUM(R.Cost) AS Total FROM TECHNICIAN T INNER JOIN REPAIR R ON T.TechnicianID = R.TechnicianID GROUP BY T.Name;

它输出每位技师的姓名以及该技师所做维修的总费用,每位技师一行:两个表通过 TechnicianID 连接,行按姓名分组,每组的费用相加。被问脚本做什么时,描述结果,而不是语法。

SQL 条件和键

LIKE 'Li%'匹配以Li开头的文本；%代表任意数量的字符，_代表恰好一个字符。AND要求同时满足两个条件；OR要求至少满足其中一个。组合使用时请使用括号。

CREATE DATABASE Club;
CREATE TABLE Booking (
  MemberID INTEGER,
  EventID INTEGER,
  PRIMARY KEY (MemberID, EventID)
);
ALTER TABLE Booking ADD FOREIGN KEY (MemberID)
  REFERENCES Member(MemberID);
SELECT Name FROM Member
WHERE Name LIKE 'Li%' AND (Year = 12 OR Year = 13)
ORDER BY Name DESC;

复合主键使配对唯一，而非各列单独唯一。外键防止对缺失父记录的无效引用。ON UPDATE CASCADE将父键更改传递到引用行；ON DELETE CASCADE在删除父记录时删除这些行。

定义题按固定的表述给分。把这些记准确,并且只写一个答案。

• 精确地定义术语:实体、属性、主键、外键,以及关系类型(1:1、1:多、多:多)。
• 在每个范式给出一个理由:1NF(没有重复组)、2NF(没有部分依赖)、3NF(没有非键依赖)——并说出涉及的字段。
• 解释一个 DBMS 提供什么(数据独立性、安全、完整性、并发访问、数据字典、开发者接口、查询处理器)。
• 区分 DDL(定义结构)和 DML(查询和改变数据),并按子句写 SQL:SELECT、FROM、INNER JOIN … ON、WHERE、GROUP BY、ORDER BY。
• 从表画 E-R 图时,先找出每个外键:每个外键就是一个一对多关系,"多"在持有它的表那一端。

常见错误

• 直接画多对多关系。它必须通过一个持有两个外键的连接表拆成两个一对多关系。
• 用"数据重复了"解释一个表不在 3NF。要说出依赖(部分或传递)以及涉及的字段。
• 双引号用于SQL中的字符串，或单引号用于数字。字符串取'single quotes'；数字不取引号。
• 在 INNER JOIN 之后遗漏了 ON 条件。若无此条件，两个表将无法连接。
• 在 SELECT 中，若没有 GROUP BY，则将普通字段置于 COUNT 或 SUM 旁边。
• UPDATE 或 DELETE 没有 WHERE。这会改变或删除表中的每一行。