文章目录
- 1. Introduction of E-R Concepts
- 2. Further Details of E-R Modeling
- 2.1 Cardinality of Entity ( 关系的基数) Participation in relationship
- 2.2 “E-R 模型”向“RDB” 转换的规则
- 3. Functional Dependencies(函数依赖)
- 3.1 Armstrong Axioms
- 3.2 Theorem 6.6.5. Implications of Armstrong Axioms
- 3.3 Closure(闭包)
- 3.4 Cover(覆盖)
- 3.5 Minimal Cover(最小覆盖)
- 4. Lossless Decomposition(无损分解)
- 4.1 Define(定义)
- 4.1.1 Decomposition(分解)
- 4.1.2 Lossless Decomposition(无损分解)
- 4.2 Theorem(定理)
- 4.3 Super Key
- 5. Nomal Forms(范式)
1. Introduction of E-R Concepts
Classification | Description | Example |
Entity (实体) | A collection of distinguishable real-world objects with common properties. | Customers, Agents, Products, Employees |
Attribute (属性) | A data item that describes a property of an entity or relationship. | See below |
Identifier (标识符) | Uniquely identifier an entity or relationship instance (occurrence). | Customer identifier : cid, Employee identifier : eid |
Descriptor (描述符) | Non-key attribute, describing an entity or relationship. | city(for customers), capacity(for Class-rooms) |
Composite-attribute (复合属性) | A group of simple attributes that together describe a property of an object. | emp-address |
Multi-valued attribute (多值属性) | An entity attribute that takes on multiple values for a single entity instance. | hobbies |
Relationship (关系) | set of m-tuples, identifier subset of Cartesian product. | |
Binary relationship (二元关系) | A relationship on two distinct entities. | teaches, works-on |
Ring, recursive relationship (递归关系) | A relationship relating an entity to itself. | Manages |
Ternary relationship (三元关系) | A relationship on three distinct entities. | yearlies |
2. Further Details of E-R Modeling
2.1 Cardinality of Entity ( 关系的基数) Participation in relationship
max-card(E, R)=1 (N) determined the E take part in R with multiple (single) value. And min-card(E, R)=0 (1) determined the E take part in R with mandatory (optional).
- maximum cardinality ( 最大基) between E and R ( 最多实例关联线数)
- max-card(E, R) = 1. If all dots in E have AT MOST one line coming out.
- max-card(E, R) = N. If more than one line out is possible.
- minimum cardinality ( 最小基) between E and R ( 最少实例关联线数)
- min-card(E, R) = 1. If all dots in E have AT LEAST one line coming out.
- min-card(E, R) = 0. If some dots might not have a line coming out.
2.2 “E-R 模型”向“RDB” 转换的规则
(1) 一个实体E 转换为一个关系Table ,实体属性成为关系属性,实体码就是关系的码。
(2) 一个1-1 联系可转换为一个独立Table ;也可以与任一实体合并。
(3) 一个1-n 联系可转换为一个独立Table ;也可与n 端对应的实体合并。
(4) 一个m-n 联系可转换为一个独立Table ;与该联系相连的各实体码以及联系本身的属性均转换为Table 的属性;且Table 的码为各实体码的组合。
(5) 三个或三个以上实体间的多元联系R ,可以转换为一个关系Table ;与多元联系R 相连的各实体码,均需加入为Table 的属性;Table 的码为各实体码的组合。
(6) 具有相同码的关系Table 可合并。
3. Functional Dependencies(函数依赖)
给定一个包含至少两个属性A和B的表T,我们说(读作“A函数决定B”或“B函数依赖于A”),即一个A对应唯一的一个B,而一个B却有可能对应多个A。
3.1 Armstrong Axioms
(Armstrong 公理系统, 又称包含律、传递律、增广律)
- Inclusion rule: if , then
- Transitivity rule: if and , then
- Augmentation rule: if , then
Or be rewritten as then .
3.2 Theorem 6.6.5. Implications of Armstrong Axioms
(Armstrong 推论, 又称“合并规则、分解规则、伪传递规则、聚合规则”)
- Union rule: If and , then
- Decomposition rule: If , then and
- Pseudo transitivity rule: If and , then
- Set accumulation rule: If and , then
3.3 Closure(闭包)
Given a set F of FDs on attributes of a table T, define the CLOSURE of F (noted F+) to be the set of all FDs implied by F.
(给定一个函数依赖的集合,作用于表的属性,定义闭包为可以从推导出的所有函数依赖的集合)
3.4 Cover(覆盖)
A set F of FDs on a table T is said to COVER another set G of FDs on T, if set G can be derived by implication rules from set F. Note as . If F covers G and G covers F, we say the two FDs are equivalent as F ≡ G.
(给定两个表T的函数依赖的集合F和G,如果G的所有的函数以来都可以被集合F推导出来,则F是G的覆盖集。如果F覆盖G,G也覆盖F,则F恒等于G。)
3.5 Minimal Cover(最小覆盖)
构造一个最小的函数依赖集,它覆盖一个给定的函数依赖集F。
设有 T 上的函数依赖集F ,求其最小覆盖集 M ? ()
(1) 将F 中 “ 被决定因素Y ” 为复合属性的 FD ,拆分为 “ 单属性被决定因素 Yj ” 形式;
拆分被决定因素Y,例如:可以拆分为和.
(2) 检查每个 FD 是否为 “ 非基本的 ” (可去除)? 若是,则删除该条 FD ;
如果,那么该函数依赖可以去除。
(定义H为当前函数依赖集合,J为除去该函数依赖的函数依赖集合,X为一个FD的决定因素)
(3) 检查 “ 决定因素X ” 为复合属性的 FD ,看是否有多余 “ 决定属性 Xi ” 可以删除;
对每一个FD的每一个决定因素进行检查,如果,那么该决定因素可以去除。
(4) 运用 Armstrong 定理及其推论, 简化归并 FDs ,得到 M。
对得到的FD集合使用定理和推论,进行简化(删掉可以通过推导得到的FD)和合并。
4. Lossless Decomposition(无损分解)
4.1 Define(定义)
4.1.1 Decomposition(分解)
table T with FDs set of F, a decomposition of T into k tables {T1, T2, …,Tk} with this properties:
(对于表T和它的函数依赖集合F,T的分解是一个表的集合{T1, T2, …,Tk},该集合的性质包括:)
(1) for every table Ti in the set, Head(Ti) is a subset of Head(T);
(对于集合中的每一个表Ti,Head(Ti)都是Head(T)的子集)
(2) Head(T) = Head(T1) ∪ Head(T2) ∪…∪ Head(Tk).
4.1.2 Lossless Decomposition(无损分解)
the FDs in F guarantee that following relationship will hold : .
(函数依赖集F保证如下关系成立:.)
4.2 Theorem(定理)
Given a table T with a set F of FDs on T, then a decomposition of T into two tables {T1, T2} is a lossless decomposition, one of the following FD is implied by F:
(给定一个表T和其函数依赖集合F,一个将T分解为两个表{T1,T2}的无损分解,如下的两个函数依赖其中之一可以通过F推导出来:)
4.3 Super Key
X is a super key, then is in 。
5. Nomal Forms(范式)
Note:BCNF比较抽象,略作解释:在学生信息表里,学号是一个候选码,学号可确定学生姓名;(班级,学生姓名)也是一组候选码,有(班级,学生姓名)->学号,因此在主属性间形成了传递依赖。