关系型数据库设计/检索数据

关系代数

关系代数是一种用于表达数据库或关系操作的正式语言。最重要的操作是

σ（小写西格玛） - 选择符合条件的值）
Π（大写派） - 投影，仅显示命名的属性
X 或笛卡尔积（显示一个关系中每一行与另一个关系中每一行的组合），
（基于条件连接，等值连接，自然连接（基于两个关系的公共字段的等值连接））。

此外，还有并集、交集、差集的集合操作： $A\cup B$ , $A\cap B$ , $A-B$ （其中 x 不是 A 与 B 交集的元素）。

还有一种除法运算，它类似于自然连接，其中第二个关系的属性是第一个关系的属性的一个子集，并且只选择第一个关系中具有第二个关系中对应属性值的那些行，并投影非公共属性。

A 除以 B，可能更好地说是，给定 B 的 A，或 A 中所有将显示 B 中所有行的行。

关系演算

SQL

SQL 语句分为两种类型，数据声明语句 (DDL) 和数据操作语句 (DML)（L 代表语言）。

数据操作 SQL

这是迄今为止 SQL 最主要的用途，因为一旦数据库设计完成，SQL 数据操作通常灵活到足以弥补糟糕的设计，并且允许空值，因此可以绕过设计不当的规范化，方法是将字段留空。因此，了解数据操作语句比了解数据声明语句更有用，因为这样你才能知道如何处理糟糕的设计，这可能比精通提取、转换和加载，同时又精通最佳规范化数据库设计的专家更可能成为招聘的原因。

检索数据

使用 SQL 从数据库检索数据是使用 *SELECT* 关键字实现的，范围从使用非常简单的语句检索表的单个列，到使用条件连接、分组、排序、限制等实现的极其复杂的语句。

最基本的 SELECT 查询采用以下形式

SELECT <columns> 
  FROM <table>;

多个列名用逗号分隔，星号字符可用作通配符来检索表中的所有列。

从表中检索给定列或列的另一种方法是使用以下语法

SELECT table.column ...

当然，能够根据给定的条件仅从表中返回某些行非常重要。此操作的基本语法如下

SELECT <columns>
  FROM <table>
 WHERE <condition>;

到目前为止，基于这些关于相关 SQL 语法基本总结的语句，可以让你从符合 SQL 标准的数据库中检索数据，但不能以关系的方式检索。以这种方式检索数据的关键是 *JOIN* 关键字。最基本的 JOIN 语法是

SELECT <columns>
  FROM <table1>
  JOIN <table2> ON <join_condition>
 WHERE <condition>;

以这种方式使用 `JOIN` 关键字可以让你根据条件从多个表中检索列到单个结果集中。这使你能够在检索时将已经规范化为多个表的数据“重新连接”成一致的数据。

但是，规范化的原因之一是，第二个表中可能并不总是有与第一个表中任何数据相对应的数据，反之亦然。在这种情况下，需要决定如何处理未“连接”在一起的数据。通常，需要决定是忽略第二个表中与第一个表中的任何数据不匹配的数据，还是忽略第一个表中与第二个表中的任何数据不匹配的数据；在 SQL 中，这可以通过分别使用 `LEFT JOIN` 或 `RIGHT JOIN` 来实现。

数据声明 SQL

这些语句为你构建一个数据库。层次结构是数据库包含一个或多个模式，模式包含一个或多个表，表包含一个或多个列。

数据声明还包含用于创建用户或角色的语句，这些语句允许使用权限分配进行访问控制：数据库将定义某些类型的操作，如 SELECT、REFERENCE、UPDATE、DELETE、USAGE、CREATION，并允许向用户或角色授予这些操作，以不同的粒度，例如，授予数据库、模式、表或表的某个部分。

以下是使用 DML 构建数据库的一个示例，没有任何规划。数据库设计的一种方法可能是将这种黑客行为放在一个带有网络黑客行为的前端，并邀请人们免费玩虚拟商业模拟，看看会出现什么问题。

CREATE ROLE CUSTDB_ADMIN;

GRANT CUSTDB_ADMIN CREATE DATABASE;

CREATE DATABASE CUSTDB OWNER CUSTDB_ADMIN;

CREATE SCHEMA CUST;

CREATE TABLE CUST.CUSTOMER (
  CUST_ID INTEGER PRIMARY KEY,
  FIRSTNAME TEXT,
  SURNAME TEXT,
  ADDRESS TEXT,
  PHONE TEXT
);

CREATE SCHEMA STOCK;

CREATE TABLE STOCK.PRODUCT (
  PROD_ID INTEGER PRIMARY KEY,
  REFERENCE_NO NUMERIC(15),
  NAME TEXT, DESCRIPTION TEXT
);

CREATE TABLE STOCK.INVENTORY (
  INVENT_ID INTEGER PRIMARY KEY,
  PROD_ID INTEGER REFERENCES STOCK.PRODUCT, 
  QTY INTEGER
);

CREATE TABLE STOCK.INVENTORY_CHANGE (
  INV_CHANGE_ID INTEGER PRIMARY KEY,
  INVENT_ID INTEGER REFERENCES STOCK.INVENTORY,
  QTY INTEGER, WHEN TIMESTAMP
);

CREATE TABLE STOCK.PRICING (
  PRICING_ID INTEGER PRIMARY KEY,
  PRODUCT_ID INTEGER REFERENCES PRODUCT,
  PRICE NUMERIC(10,2)
);

CREATE TABLE STOCK.COST (
  COST_ID INTEGER PRIMARY KEY,
  PRODUCT_ID INTEGER REFERENCES PRODUCT,
  COST NUMERIC(10,2)
);

CREATE SCHEMA SALES;

CREATE TABLE SALES.PRICE (
  PRICE_ID INTEGER PRIMARY KEY,
  PROD_ID INTEGER REFERENCES STOCK.PRODUCT,
  PRICING_ID REFERENCES STOCK.PRICING,
  ALTERATION NUMERIC(10,2)
);

CREATE TABLE CUST.ORDER (
  ORDER_ID INTEGER PRIMARY KEY,
  CUST_ID INTEGER REFERENCES CUST.CUSTOMER,
  WHEN TIMESTAMP
);

CREATE TABLE CUST.ORDER_ITEM (
  ORDER_ITEM_ID INTEGER PRIMARY KEY,
  ORDER_ID INTEGER REFERENCES CUST.ORDER,
  PROD_ID INTEGER REFERENCES STOCK.PRODUCT,
  QTY INTEGER,
  PRICE_ID INTEGER REFERENCES SALES.PRICE
);

请注意，每个表都有一个主键约束，并且许多表都有一个外键约束，其中关键字 REFERENCES 可以在列声明之后应用（其中列的类型必须与主键的类型匹配），或者在表的末尾应用，其中 FOREIGN KEY CONSTRAINT 字段 REFERENCES other_table 是前缀。

将数据声明 SQL 关联到使用规范化的关系设计

在这些表中，没有复合键，如果这些表处于 3NF，那么根据 Date/Fagin 的说法，它们也处于 5NF，如上所述。这些表是否符合 3NF 的定义 - 对于函数依赖 X->Y，X 是一个超键或 Y 是候选键的一部分？

对于客户：FD 是 CUST_ID -> FIRSTNAME、LASTNAME、ADDRESS、PHONE - 3NF（也是 5NF）

对于产品：FD 是 PROD_ID -> REFERENCE_NO -1、REFERENCE_NO -> NAME、DESCRIPTION -2

FD1 X 是一个超键，FD2 Y 是 NAME、DESCRIPTION，可能是一个候选键，因此 3NF 成立。但是，可能有人争辩说 NAME 是一个候选键，而 TEXT 是 NAME 的依赖项，即 NAME -> DESCRIPTION。在这里，NAME 对于每个产品行可能都是唯一的，因此它也是一个候选键，因此 X 是一个超键，并且 3NF 对于 NAME->DESCRIPTION 仍然成立。

PROD_ID 和 REFERENCE_NO 之间似乎存在明显的问题，并且可能应该将产品分解为 (PROD_ID, REFERENCE_NO)、(PROD_ID, NAME, DESCRIPTION)。

但 2NF 成立（没有非主属性依赖于最小超键或候选键的一部分而不是全部，这些键是 PROD_ID 或 REFERENCE_NO），3NF 成立（X->A，X 是一个超键，或者 A 是候选键的一部分），甚至 BCNF 也成立（REFERENCE_NO->NAME、DESCRIPTION，并且 REFERENCE_NO 是一个备用候选键）。因此，分解似乎没有必要。

对于库存表，INVENT_ID -> PROD_ID, QTY 是主键，但 PROD_ID -> QTY 也可能成立，所以 QTY 可以放到产品表中。库存表可以删除，库存变更表应该引用产品表。

对于库存变更表，原来的 invent_id 现在是 prod_id，而 prod_id 不是候选键，因为同一个 prod_id 可能会有多个库存变更。因此，该表的 FD 为 inv_change_id -> prod_id, qty, when。符合 3NF（也符合 BCNF）。

对于定价表，同一个产品可能有多个不同的价格，应该有一个类似定价期间和定价原因的信息，可能需要单独的表。该表的 FD 为 pricing_id -> price, prod_id （符合 3NF，BCNF）。

对于订单表，Order_id -> customer, when。

对于订单项表，order_item_id -> Order_id, prod_id, qty, pricing_id，但 pricing_id -> prod_id, price，所以在构建最小覆盖 FD 集时，order_item_id 的 FD 可能变成 order_item_id -> order_id, pricing_id, qty，这样就消除了 prod_id。

由于 pricing_id -> prod_id，这个 FD 中 X 不是 order_item 关系的超键，违反了 BCNF，但不违反 3NF，因为 pricing_id 是候选键 order_id, pricing_id, qty 的一部分。

最好先尝试达到 BCNF，所以应该从订单项表中移除 prod_id。

因此，我们使用范式化来检查一个由增量设计过程产生的有机数据库。ER 图可能会显示订单项、定价和订单之间冗余关系，例如：

订单项 - 关于 - 产品是一个冗余关系。

另外，检查后发现所有表都只有一个属性的主键，并且都符合 3NF，因此也符合 5NF，尽管看起来只有多值依赖被正确分解了；例如，订单 ->-> 订单项，产品 ->-> 定价。