database第3章 第1次课
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
CE EC EC
84
88 84Βιβλιοθήκη Baidu89
SCJ
3.1 Functional Dependencies
2. Keys of Relations 关系的键
We say a set of one or more attributes {A1,A2,…,An} is a key for a relation R if : 如果下列条件满足，就认为一个或多个属性集{A1,A2,…,An}是关系R的键： (1) Those attributes functionally determine all other attributes of the relation. That is, it is impossible for two distinct tuples of R to agree on all of A1,A2,…,An. 这些属性函数决定关系的所有其他属性。即不存在两个不同的元组，它们具有相 同的A1,A2,…,An值。
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}
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. 关系的设计理论使人们可以根据少数简单原则来认真检验一个设计并做出改进。这一 理论首先能够规定作用在关系上的约束。最常见的约束是“函数依赖”，它泛化了关系中 “键”的概念。本章随后介绍如何使用关系设计理论给予的一些简单工具，通过关系的分 解过程来改进设计，即用若干关系替代原关系，这些关系的属性集合包含了原关系的所有 属性。
有时一个关系可能会有多个键。如果是这样的话，通常就要指定其中一个为主键。 在商业数据库系统中，对主键的选择会影响某些实现问题，例如怎样在磁盘中存储关 系。然而，函数依赖理论并未给主键以特殊的角色。
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:
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.
如果确定关系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？关 系的键是什么？（假设一个地区只能用于一个州，一个邮编不能跨越两个地区代码， 两个人不能有相同的社会保险号，但可以有相同的地址和电话号码）
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。
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
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”.
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
当键只包括一个单独的属性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}
Chapter 3 Design Theory for Relational Databases - First
Content 3.1 Functional Dependencies 函数依赖 3.2 Rules About Functional Dependencies 函数依赖的规则
3.1 Functional Dependencies
84
88 84Βιβλιοθήκη Baidu89
SCJ
3.1 Functional Dependencies
2. Keys of Relations 关系的键
We say a set of one or more attributes {A1,A2,…,An} is a key for a relation R if : 如果下列条件满足，就认为一个或多个属性集{A1,A2,…,An}是关系R的键： (1) Those attributes functionally determine all other attributes of the relation. That is, it is impossible for two distinct tuples of R to agree on all of A1,A2,…,An. 这些属性函数决定关系的所有其他属性。即不存在两个不同的元组，它们具有相 同的A1,A2,…,An值。
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}
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. 关系的设计理论使人们可以根据少数简单原则来认真检验一个设计并做出改进。这一 理论首先能够规定作用在关系上的约束。最常见的约束是“函数依赖”，它泛化了关系中 “键”的概念。本章随后介绍如何使用关系设计理论给予的一些简单工具，通过关系的分 解过程来改进设计，即用若干关系替代原关系，这些关系的属性集合包含了原关系的所有 属性。
有时一个关系可能会有多个键。如果是这样的话，通常就要指定其中一个为主键。 在商业数据库系统中，对主键的选择会影响某些实现问题，例如怎样在磁盘中存储关 系。然而，函数依赖理论并未给主键以特殊的角色。
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:
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.
如果确定关系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？关 系的键是什么？（假设一个地区只能用于一个州，一个邮编不能跨越两个地区代码， 两个人不能有相同的社会保险号，但可以有相同的地址和电话号码）
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。
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
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”.
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
当键只包括一个单独的属性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}
Chapter 3 Design Theory for Relational Databases - First
Content 3.1 Functional Dependencies 函数依赖 3.2 Rules About Functional Dependencies 函数依赖的规则
3.1 Functional Dependencies