database第3章 第1次课

当键只包括一个单独的属性A时，称A（而不是{A}）是键。
3.1 Functional Dependencies
2. Keys of Relations
Example: Student(ID, Name, Sex, Birth);
Now, we must argue that all proper subset of {ID, Name, Sex, Birth} functionally determines all other attributes. 下面将讨论的是{ID, Name, Sex, Birth} 的任一真子集都是否函数决定其他的属性。 {ID}; {Name}; {Sex}; {Birth}; {ID, Name}; {ID, Sex}; {ID, Birth}; {Name, Sex}; {Name, Birth}; {Sex, Birth} ; {ID, Name, Sex}; {ID, Name, Birth}; {ID, Sex, Birth}; {Name, Sex, Birth}; {ID, Name, Sex, Birth}
Relational Schema: SCJ(SID, SName, CID, CName, Score) SID → SName CID → CName (SID, CID) → Score
1002
1003 1003 1004
Rose
Flora Flora James
K01
K01 K02 K02
CE
3.1 Functional Dependencies
2. Keys of Relations Sometimes a relation has more than one key. If so, it is common to designate one of the keys as the primary key. In commercial database systems, the choice of primary key can influence some implementation issues such as how the relation is stored on disk. However, the theory of FD’s gives to special role to “primary key”.
3.1 Functional Dependencies
1. Definition of Functional Dependency If we can be sure every instance of a relation R will be one in which a given FD is true, then we say that R satisfies the FD. It is important to remember that when we say that R satisfies an FD f, we are asserting a constraint on R, not just saying something about one particular instance of R. It is common for the right side of an FD to be a single attribute.
3.1 Functional Dependencies
1. Definition of Functional Dependency 函数依赖的定义
A functional dependency(FD) on a relation R is a statement of the form “If two tuples of R agree on all of the attributes A1,A2,…,An (i.e., the tuples has the same values in their respective components for each of these attributes), then they must also agree on all of another list of attributes B1,B2,…,Bm. We write this FD formally as A1,A2,…,An → B1,B2,…,Bm and say that “A1,A2,…,An functionally determine B1,B2,…,Bm” 关系R上的函数依赖是指如果R的两个元组在属性A1,A2,…,An上一致（即它们对 应于这些属性的分量值都相等），那么它们必定在其他属性 B1,B2,…,Bm上也一致。 该函数依赖形式地记为 A1,A2,…,An → B1,B2,…,Bm ，并称为 A1,A2,…,An函数决定 B1,B2,…,Bm。
FD:
Social Security number → {Name, Area code, State, City, Street Adrress, Zipcode, Phone}
Area code State Street address, City, State zipcode Key: Social Security number {Street address, City, Area code, Phone, Name}
3.1 Functional Dependencies
2. Keys of Relations 关系的键
(2) No proper subset of {A1,A2,…,An} functionally determines all other attributes of R; i.e., a key must be minimal. 在{A1,A2,…,An} 的真子集中，没有一个能函数决定 R 的所有其他属性。也就是 说，键必须是最小的。 When a key consists of (rather than {A}) is a key. a single attribute A, we often say that A
有时一个关系可能会有多个键。如果是这样的话，通常就要指定其中一个为主键。 在商业数据库系统中，对主键的选择会影响某些实现问题，例如怎样在磁盘中存储关 系。然而，函数依赖理论并未给主键以特殊的角色。
3.1 Functional Dependencies
3. Super key 超键 A set of attributes that contains a key is called a super key, short for “super set of a key”. Thus, every key is a super key. However, some super keys are not minimal key. Note that every super key satisfies the first condition of a key: it functionally determines all other attributes of the relation. However, a super key need not satisfy the second condition:
如果确定关系R的每个实例都能使一个给定的FD为真，那么称R满足函数依赖f。 这是在R上声明了一个约束，而不是仅仅针对R的一个特殊实例。通常，FD的右边可 能是单个属性。
3.1 Functional Dependencies
Example:
SID 1001 1001 1001 1002 SName Jacky Jacky Jacky Rose CID T01 K01 T02 T01 CName OS CE DS OS Score 78 85 87 72
minimality.
一个包含键的属性集就叫做超键，它是键的超集的简写。因此，每个键都是超键。 然而，某些超键不是键。注意，每个超键都满足键的第一个条件：它函数决定了关系 中所有其他属性。但超键不需要满足第二个条件：最小化。
3.1 Functional Dependencies
4. Exercise （1）考虑一个关于美国公民的关系，这个关系的属性有：姓名、社会保险号、街道 地址、城市、州、邮编、地区代码和电话号码（7位数字）。这个关系有哪些FD？关 系的键是什么？（假设一个地区只能用于一个州，一个邮编不能跨越两个地区代码， 两个人不能有相同的社会保险号，但可以有相同的地址和电话号码）
There is a design theory for relations that lets us examine a design carefully and make improvements based on a few simple principles. The theory begins by having us state the constraints that apply to the relation. The most common constraint is the “functional dependency”, a statement of a type that generalizes the idea of a key for a relation, which we introduced in Section 2.5.3. Later in this chapter, we shall see how this theory gives up simple tools to improve our designs by the process of “decomposition” of relations : the replacement of one relation by several, whose sets of attributes together include all the attributes of the original. 关系的设计理论使人们可以根据少数简单原则来认真检验一个设计并做出改进。这一 理论首先能够规定作用在关系上的约束。最常见的约束是“函数依赖”，它泛化了关系中 “键”的概念。本章随后介绍如何使用关系设计理论给予的一些简单工具，通过关系的分 解过程来改进设计，即用若干关系替代原关系，这些关系的属性集合包含了原关系的所有 属性。