文章目录
- ER Modelling \textbf{ER Modelling} ER Modelling
- 1. Introduction \textbf{1. Introduction} 1. Introduction
- 2. ER Modelling (Chen’s) \textbf{2. ER Modelling (Chen's)} 2. ER Modelling (Chen’s)
- 2.1. Basic Concepts \textbf{2.1. Basic Concepts} 2.1. Basic Concepts
- 2.2. Relation \textbf{2.2. Relation} 2.2. Relation
- 2.3. Entity: Weak Entity \textbf{2.3. Entity: Weak Entity} 2.3. Entity: Weak Entity
- 2.4. Attribute (Special Type) \textbf{2.4. Attribute (Special Type)} 2.4. Attribute (Special Type)
- 2.5. Conceptual Design: Entity or Attribute? \textbf{2.5. Conceptual Design: Entity or Attribute?} 2.5. Conceptual Design: Entity or Attribute?
- 3. ER Modelling (Crow’s Foot) \textbf{3. ER Modelling (Crow's Foot)} 3. ER Modelling (Crow’s Foot)
- 4. Relational Model \textbf{4. Relational Model} 4. Relational Model
- 5. ER→Relational Model \textbf{5. ER→Relational Model} 5. ER→Relational Model
ER Modelling \textbf{ER Modelling} ER Modelling
1. Introduction \textbf{1. Introduction} 1. Introduction
1.1. Data→DBMS \textbf{1.1. Data→DBMS} 1.1. Data→DBMS
1️⃣ Data vs. Information: \text{Data vs. Information:} Data vs. Information:
Item \textbf{Item} Item 定义 描述 示例 Data \text{Data} Data Known facts stored and recorded \text{Known facts stored and recorded} Known facts stored and recorded Raw Data \text{Raw Data} Raw Data 123 \text{123} 123 Information \text{Information} Information Data presented in context \text{Data presented in context} Data presented in context Processed Data \text{Processed Data} Processed Data StuID=123 \text{StuID=123} StuID=123 2️⃣ Metadata: \text{Metadata: } Metadata:
- 含义:描述数据的数据,提供数据的背景和结构信息
- 示例:书籍的标题/作者/出版年份/ ISBN \text{ISBN} ISBN等信息
3️⃣ Database: Large integrated and structured collection of data \text{Database: }\textbf{Large integrated} \text{ and } \textbf{structured } \text{collection of data } Database: Large integrated and structured collection of data
4️⃣ DBMS (Database Management System) \text{DBMS (Database Management System)} DBMS (Database Management System)
- 定义: Software designed to store/manage/facilitate access to databases. \text{Software designed to } \textbf{store/manage/facilitate access}\text{ to databases.} Software designed to store/manage/facilitate access to databases.
- 比如 MySQL \text{MySQL} MySQL
1.2. DB Development Process \textbf{1.2. DB Development Process} 1.2. DB Development Process
1.2.1. High Level Design \textbf{1.2.1. High Level Design} 1.2.1. High Level Design
1️⃣ Database Planning: \text{Database Planning:} Database Planning: 比如企划银行的结构,有客户/账户/员工等以及对应关系
2️⃣ System Definition: \text{System Definition:} System Definition: 确定系统的边界与接口;比如抵押系统如何从客户系统获取必要信息
3️⃣ Requirements Definition/Analysis: \text{Requirements Definition/Analysis:} Requirements Definition/Analysis: 确定系统需求(能实现什么),比如银行系统能否汇率转换
1.2.2. Core Design \textbf{1.2.2. Core Design} 1.2.2. Core Design
1️⃣ Conceptual Design/ER Modelling \text{Conceptual Design/ER Modelling} Conceptual Design/ER Modelling
- 独立于物理实现的数据模型
- 最常见的概念设计方法为 ER Modelling \text{ER Modelling} ER Modelling
- 包含 Entity(Employee)+Attributes(ssn/name/age) \text{Entity(Employee)+Attributes(ssn/name/age)} Entity(Employee)+Attributes(ssn/name/age)
- 常见的 ER Diagram \text{ER Diagram} ER Diagram有 Chen’s \text{Chen's} Chen’s还有 Crow’s Foot \text{Crow's Foot} Crow’s Foot
2️⃣ Logical Design/Relational Model \text{Logical Design/Relational Model} Logical Design/Relational Model
Employee (ssn, name, age) -- Employee为关系,(ssn, name, age)为其三个属性
- 独立于具体数据库的逻辑模型
- 最常见的逻辑设计方法为 Relational Model \text{Relational Model} Relational Model
- ER Modelling → { Entity → Reation(表) Attribute → Attribute(列) Relational Model \text{ER Modelling}\xrightarrow{\begin{cases}\text{Entity}\to{}\text{Reation(表)}\\\\\text{Attribute}\to\text{Attribute(列)}\end{cases}}\text{Relational Model} ER Modelling{Entity→Reation(表)Attribute→Attribute(列)Relational Model
3️⃣ Physical Design: \text{Physical Design:} Physical Design:
Employee (ssn CHAR(11), name VARCHAR(20), age INTEGER)
- 实现具体数据库的物理模型
- 其实就是 Logical → Choose data type Physical \text{Logical}\xrightarrow{\text{Choose data type}}\text{Physical} LogicalChoose data typePhysical
4️⃣ Implementation & Instance: \text{Implementation \& Instance:} Implementation & Instance:
- Implementation: \text{Implementation:} Implementation: 通过 MySQL Workbench \text{MySQL Workbench} MySQL Workbench创建表格
CREATE TABLE Employee ( ssn CHAR(11), name VARCHAR(20), age INTEGER, PRIMARY KEY (ssn) );
- Instance: \text{Instance:} Instance: 往实现好了的 SQL \text{SQL} SQL表格中填充数据
1.2.3. Other Design \textbf{1.2.3. Other Design} 1.2.3. Other Design
1️⃣ Application Design: \text{Application Design:} Application Design: 设计使用数据库的用户界面 APP \text{APP} APP
2️⃣测试和维护
2. ER Modelling (Chen’s) \textbf{2. ER Modelling (Chen's)} 2. ER Modelling (Chen’s)
2.1. Basic Concepts \textbf{2.1. Basic Concepts} 2.1. Basic Concepts
1️⃣概述
Concept \textbf{Concept} Concept 解释 备注 Entity(Set) \text{Entity(Set)} Entity(Set) 真实世界中的对象(集合) 单个实体由多个属性描述 Attribute \text{Attribute} Attribute 对于一个实体的描述 实体的属性中必有一个 Key \text{Key} Key(区分每个个体) Relationship(Set) \text{Relationship(Set)} Relationship(Set) 两个/多个实体的联结(集合) 关系也可能有 (Descriptive)Attribute \text{(Descriptive)Attribute} (Descriptive)Attribute 2️⃣综合示例( (Chen’s Model) { Entity: Employee/Department Relation: Works in \text{(Chen's Model)}\begin{cases}\text{Entity: Employee/Department}\\\\\text{Relation: Works in}\end{cases} (Chen’s Model)⎩ ⎨ ⎧Entity: Employee/DepartmentRelation: Works in
- Primary Key \text{Primary Key} Primary Key要用下划线标出
3️⃣注意事项:
- 同一实体可出现在不同关系
- 同一实体可有不同角色(员工的上下级)
2.2. Relation \textbf{2.2. Relation} 2.2. Relation
2.2.1. Binary: Relation Constraints \textbf{2.2.1. Binary: Relation Constraints} 2.2.1. Binary: Relation Constraints
0️⃣关系基数: 1-1/1-M/M-M \text{1-1/1-M/M-M} 1-1/1-M/M-M三种
1️⃣ Key/Cardinalitiy Constraints: \text{Key/Cardinalitiy Constraints:} Key/Cardinalitiy Constraints: 确定了数量的上限 1/N \text{1/N} 1/N
关系类型 Employee(Manager) \textbf{Employee(Manager)} Employee(Manager) 员工键约束 Department \textbf{Department} Department 部门键约束 M-M \text{M-M} M-M N \text{N} N(一部门有多员工) ❌ N \text{N} N(一员工在多部门) ❌ 1-M \text{1-M} 1-M 1 \text{1} 1(一部门只有一经理) ✔️ N \text{N} N(一经理管多部门) ❌ 1-1 \text{1-1} 1-1 1 \text{1} 1(一部门只有一经理) ✔️ 1 \text{1} 1(一经理管一部门) ✔️ 2️⃣ Participation Constraints: \text{Participation Constraints:} Participation Constraints: 确定了数量的下限 0/1 \text{0/1} 0/1
- Total/Partial Participation: \text{Total/Partial Participation:} Total/Partial Participation: 一个实体集中所有实体必须/非必须参与到关系中
- Chen’s \text{Chen's} Chen’s表示: Total participation is represented by bold line \text{Total participation is represented by bold line} Total participation is represented by bold line
3️⃣两种限制的组合:确定了数量的上下限
Type \textbf{Type} Type Participation \textbf{Participation} Participation Key Constraint \textbf{Key Constraint} Key Constraint E.g. Prof may teach_lecture \textbf{E.g. Prof may teach\_lecture} E.g. Prof may teach_lecture Optional Many \text{Optional Many} Optional Many Partial \text{Partial} Partial(下限 0 \text{0} 0) ❌(上限 N \text{N} N) 0/1/N \text{0/1/N} 0/1/N Mandatory Many \text{Mandatory Many} Mandatory Many Total \text{Total} Total(下限 1 \text{1} 1) ❌(上限 N \text{N} N) 1/N \text{1/N} 1/N Optional One \text{Optional One} Optional One Partial \text{Partial} Partial(下限 0 \text{0} 0) ✔️(上限 1 \text{1} 1) 0/1 \text{0/1} 0/1 Mandatory One \text{Mandatory One} Mandatory One Total \text{Total} Total(下限 1 \text{1} 1) ✔️(上限 1 \text{1} 1) 1 \text{1} 1
- 注意 Chen’s \text{Chen's} Chen’s模型中,靠近一方实体的线 { 决定这一方实体的Optional/Mandatory 决定另一方实体的One/Many ( 箭头指向One ) \begin{cases}决定这一方实体的\text{Optional/Mandatory}\\\\决定另一方实体的\text{One/Many}(箭头指向\text{One})\end{cases} ⎩ ⎨ ⎧决定这一方实体的Optional/Mandatory决定另一方实体的One/Many(箭头指向One)
2.2.2. Ternary Relationships \textbf{2.2.2. Ternary Relationships} 2.2.2. Ternary Relationships
1️⃣含义:三个实体同时参与一个关系
2️⃣示例:
- 部门( Depaertment \text{Depaertment} Depaertment)/供应商( Supplier \text{Supplier} Supplier)之间,建立了一个有关组件( Part \text{Part} Part)的合同
- 关系的属性包含了合同中组件的数量
2.3. Entity: Weak Entity \textbf{2.3. Entity: Weak Entity} 2.3. Entity: Weak Entity
1️⃣弱实体基本要素
- 强弱实体
Entity \textbf{Entity} Entity 描述 Owner/Strong Entity \text{Owner/Strong Entity} Owner/Strong Entity 弱实体所赖以存在的实体 Weak Entity \text{Weak Entity} Weak Entity 依赖于强实体而存在的实体 - Identifying/Weak Relationship: \text{Identifying/Weak Relationship:} Identifying/Weak Relationship: 连接强弱实体的关系,所有弱实体必定有一个弱关系
2️⃣关于 Weak Entity \text{Weak Entity} Weak Entity的 Key \text{Key} Key
Key \textbf{Key} Key 描述 Weak Entity \text{Weak Entity} Weak Entity的 PK \text{PK} PK 又称为 Partial Key \text{Partial Key} Partial Key,在 Chen’s \text{Chen's} Chen’s中用虚下滑线表示 Owner Entity \text{Owner Entity} Owner Entity的 PK \text{PK} PK 塞进 Weak Entity \text{Weak Entity} Weak Entity当 PFK \text{PFK} PFK,识别弱实体要自身 PK+ \text{PK+} PK+强实体 PK(PFK) \text{PK(PFK)} PK(PFK) 3️⃣如何辨认 Weak Entity \text{Weak Entity} Weak Entity
- 如果 A \text{A} A不存在 B \text{B} B也不能存在,则 B \text{B} B是 A \text{A} A的 Weak Entity \text{Weak Entity} Weak Entity
- 如果需要识别出 A \text{A} A,不仅要 A \text{A} A的 PK \text{PK} PK还需要 B \text{B} B的 PK \text{PK} PK,则 B \text{B} B是 A \text{A} A的 Weak Entity \text{Weak Entity} Weak Entity
- Weak Entity \text{Weak Entity} Weak Entity和 Owner Entity \text{Owner Entity} Owner Entity通常是 1-M \text{1-M} 1-M关系,且弱对强一定是 Mandatory One \text{Mandatory One} Mandatory One(粗箭头)
- 弱实体必定有赖于强实体而存在( Mandatory \text{Mandatory} Mandatory)
- 弱实体也只能依赖唯一的强实体( One \text{One} One)
4️⃣ Chen’s ER Diagram: \text{Chen's ER Diagram:} Chen’s ER Diagram: 员工家属 (Dependent) \text{(Dependent)} (Dependent)是弱实体
- 弱实体/弱关系边框加粗
- Partial Key \text{Partial Key} Partial Key需要用虚线下划线标记
2.4. Attribute (Special Type) \textbf{2.4. Attribute (Special Type)} 2.4. Attribute (Special Type)
2.4.1. Multi-Valued Attributes \textbf{2.4.1. Multi-Valued Attributes} 2.4.1. Multi-Valued Attributes
1️⃣概念:一个属性拥有多个(但非无限)个同类型值,比如一个员工拥有多个电话
2️⃣ Chen’s ER Diagram: \text{Chen's ER Diagram:} Chen’s ER Diagram: 用镶嵌气泡表示
2.4.2. Composite Attributes \textbf{2.4.2. Composite Attributes} 2.4.2. Composite Attributes
1️⃣概念:拥有内部结构的属性,比如员工地址内还包含邮政编码/街道名/街道号
2️⃣ Chen’s ER Diagram: \text{Chen's ER Diagram:} Chen’s ER Diagram: 气泡插到气泡上,
2.4.3. Derived Attributes \textbf{2.4.3. Derived Attributes} 2.4.3. Derived Attributes
1️⃣含义:从别的属性派生出来的属性
2️⃣特点:并不会在物理层面存储该属性,所以在物理设计阶段就会消失
3️⃣ Chen’s ER Diagram: \text{Chen's ER Diagram:} Chen’s ER Diagram: 用虚框表示
- Years Employeed \text{Years Employeed} Years Employeed可以由以下规则派生
DATE.now - Contact.start
2.5. Conceptual Design: Entity or Attribute? \textbf{2.5. Conceptual Design: Entity or Attribute?} 2.5. Conceptual Design: Entity or Attribute?
1️⃣通常情况下,选择设计成 Entity \text{Entity} Entity比设计成 Attribute \text{Attribute} Attribute灵活性更高,也是首选
2️⃣示例:用户( UserID \text{UserID} UserID订阅 Plan \text{Plan} Plan及其 Type \text{Type} Type
- 当 Type \text{Type} Type为 Attribute: \text{Attribute:} Attribute: 如果要新增 TypeX \text{TypeX} TypeX,则需强制增加一个使用 TypeX \text{TypeX} TypeX的 UserID \text{UserID} UserID,明显不行
Plan \textbf{Plan} Plan表 UserID \textbf{UserID} UserID Type \textbf{Type} Type / \text{/} / 1 \text{1} 1 TypeA \text{TypeA} TypeA / \text{/} / 2 \text{2} 2 TypeB \text{TypeB} TypeB / \text{/} / 3 \text{3} 3 TypeC \text{TypeC} TypeC / \text{/} / ? \text{?} ? TypeX \text{TypeX} TypeX - 当 Type \text{Type} Type为 Entity: \text{Entity:} Entity: 可以灵活新增 TypeX \text{TypeX} TypeX,不鸟 UserID \text{UserID} UserID如何独立存在
Plan \textbf{Plan} Plan表 UserID \textbf{UserID} UserID Plan \textbf{Plan} Plan表 TypeID \textbf{TypeID} TypeID / \text{/} / 1 \text{1} 1 / \text{/} / A \text{A} A / \text{/} / 2 \text{2} 2 / \text{/} / B \text{B} B / \text{/} / 3 \text{3} 3 / \text{/} / C \text{C} C / \text{/} / / \text{/} / / \text{/} / X \text{X} X
3. ER Modelling (Crow’s Foot) \textbf{3. ER Modelling (Crow's Foot)} 3. ER Modelling (Crow’s Foot)
3.1. Basic Component \textbf{3.1. Basic Component} 3.1. Basic Component
1️⃣ Entity \text{Entity} Entity
Concept \textbf{Concept} Concept Chen’s Notation \textbf{Chen's Notation} Chen’s Notation Crow’s Foot Notation \textbf{Crow's Foot Notation} Crow’s Foot Notation Entity \text{Entity} Entity Weak Entity \text{Weak Entity} Weak Entity 2️⃣ Attributes \text{Attributes} Attributes
- 各种类型的属性
Concept \textbf{Concept} Concept Chen’s Notation \textbf{Chen's Notation} Chen’s Notation Crow’s Foot Notation \textbf{Crow's Foot Notation} Crow’s Foot Notation Attribute \text{Attribute} Attribute Mutivalue A. \text{Mutivalue A.} Mutivalue A. Composote A. \text{Composote A.} Composote A. Derived A. \text{Derived A.} Derived A. - Key(Covered Later in Relational Model) \text{Key(Covered Later in Relational Model)} Key(Covered Later in Relational Model)
Concept \textbf{Concept} Concept Chen’s Notation \textbf{Chen's Notation} Chen’s Notation Crow’s Foot Notation \textbf{Crow's Foot Notation} Crow’s Foot Notation Key A. \text{Key A.} Key A. Weak/Partial Key A. \text{Weak/Partial Key A.} Weak/Partial Key A. - 示例( In Workbench \text{In Workbench} In Workbench)
Primary Key \text{Primary Key} Primary Key Foreign Key \text{Foreign Key} Foreign Key Mandatory \text{Mandatory} Mandatory/ NOT NULL
Optional \text{Optional} Optional/ NULL
3️⃣ Relationship \text{Relationship} Relationship
- 强弱关系
Concept \textbf{Concept} Concept Chen’s \textbf{Chen's} Chen’s Crow’s Foot \textbf{Crow's Foot} Crow’s Foot Relationship \text{Relationship} Relationship Relationship/Identifying Relationship \text{Relationship/Identifying Relationship} Relationship/Identifying Relationship - 示例
3.2. Relationship Constraints \textbf{3.2. Relationship Constraints} 3.2. Relationship Constraints
1️⃣ Constraints \text{Constraints} Constraints
Type \textbf{Type} Type Chen’s Vs. Crow’s Foot \textbf{Chen's Vs. Crow's Foot} Chen’s Vs. Crow’s Foot Crow’s Foot E.g. \textbf{Crow's Foot E.g.} Crow’s Foot E.g. Optional Many \text{Optional Many} Optional Many Mandatory Many \text{Mandatory Many} Mandatory Many Optional One \text{Optional One} Optional One Mandatory One \text{Mandatory One} Mandatory One 2️⃣ Relationship (Cardinalitiy Only) \text{Relationship (Cardinalitiy Only)} Relationship (Cardinalitiy Only)
Type \textbf{Type} Type Chen’s \textbf{Chen's} Chen’s Crow’s Foot \textbf{Crow's Foot} Crow’s Foot M-M \text{M-M} M-M 1-M \text{1-M} 1-M 1-1 \text{1-1} 1-1 3.3. Chen’s ↔ 转换 Crow’s Foot \textbf{3.3. Chen's}\xleftrightarrow{转换}\text{Crow's Foot} 3.3. Chen’s转换 Crow’s Foot
1️⃣转换的核心规则: Crow’s Foot \text{Crow's Foot} Crow’s Foot一侧的鸦足标记 ↔ 对应 Chen’s \xleftrightarrow{对应}\text{Chen's} 对应 Chen’s另一侧的线/箭头
2️⃣示例
4. Relational Model \textbf{4. Relational Model} 4. Relational Model
4.1. Basic Conception \textbf{4.1. Basic Conception} 4.1. Basic Conception
1️⃣ Relational Data Model \text{Relational Data Model} Relational Data Model
- Data Model: \text{Data Model:} Data Model: 将 Real-World \text{Real-World} Real-World实体转化为计算机 Readable \text{Readable} Readable的结构,比如 Relational Model \text{Relational Model} Relational Model
- Relational Model \text{Relational Model} Relational Model的基本要素:
- Schema: \text{Schema: } Schema: 关系(表格)的名称 + \text{+} +表格每列的属性名/属性类型
- Instance: \text{Instance: } Instance: 表格每行的 Tuple \text{Tuple} Tuple
- Data→Relational Model \text{Data→Relational Model} Data→Relational Model转换
- Data → Represent Data With Table { Tuple/Record → Row Attribute → Column Relational Model \text{Data}\xrightarrow[\text{Represent Data With Table}]{\begin{cases}\text{Tuple/Record}\to{}\text{Row}\\\\\text{Attribute}\to{}\text{Column}\end{cases}}\text{Relational Model} Data{Tuple/Record→RowAttribute→ColumnRepresent Data With TableRelational Model
2️⃣ Relational Databases: \text{Relational Databases: } Relational Databases: 利用 Relation \text{Relation} Relation存储数据的数据库
- Records → 组成 一系列记录 Relations → 组成 一系列关系 Relational Databases \text{Records}\xrightarrow[组成]{\text{一系列记录}}\text{Relations}\xrightarrow[组成]{\text{一系列关系}}\text{Relational Databases} Records一系列记录组成Relations一系列关系组成Relational Databases
- Relations → Populated With Actual Data Instance/Table \text{Relations}\xrightarrow{\text{Populated With Actual Data}}\text{Instance/Table} RelationsPopulated With Actual DataInstance/Table
3️⃣ Cardinality/Degree \text{Cardinality/Degree} Cardinality/Degree
- 含义
含义 备注 Cardinality \text{Cardinality} Cardinality 表中 Rows/Records/Tuples \text{Rows/Records/Tuples} Rows/Records/Tuples数量 表中记录可以无序,但要唯一 Degree \text{Degree} Degree 表中 Column/Attributes/Fields \text{Column/Attributes/Fields} Column/Attributes/Fields数量 N/A \text{N/A} N/A - 示例: Cardinality=3/Degree=5/All Rows Distinct \text{Cardinality=3/Degree=5/All Rows Distinct} Cardinality=3/Degree=5/All Rows Distinct
4.2. Key \textbf{4.2. Key} 4.2. Key
1️⃣ Super Key: \text{Super Key:} Super Key:
- 含义: A set of attributes that uniquely identifies each tuple \text{A set of attributes that uniquely identifies each tuple} A set of attributes that uniquely identifies each tuple(一个/多个属性都行)
- 特点:
- Super Key Value ↔ 一一对应 Tuple \text{Super Key Value}\xleftrightarrow{一一对应}\text{Tuple} Super Key Value一一对应 Tuple
- 每个关系必定有一组属性组成超键(因为关系中每行必唯一)
- 假设属性集 A \text{A} A是 Super Key \text{Super Key} Super Key,则 A+ \text{A+} A+任意其他 Attributes \text{Attributes} Attributes也是 Super Key \text{Super Key} Super Key
- 示例: Super Key \text{Super Key} Super Key为
names+login+age
2️⃣ Primary Key(PK) \text{Primary Key(PK)} Primary Key(PK)
- Key in a Relationship: \text{Key in a Relationship:} Key in a Relationship:
- 最小化的 Super Ksy \text{Super Ksy} Super Ksy,用最少的信息区分每一个 Tuple \text{Tuple} Tuple
- 即使是最小化,但也不一定只有一个属性
- 两种键类型
Key Type \textbf{Key Type} Key Type 含义 备注 示例 Primary \text{Primary} Primary 唯一标识每个记录的属性(集) 每个关系只有一个主键 sid
Candidate \text{Candidate} Candidate 所有可能作为主键的属性(集) 基本和主键混为一谈 login
- 特殊键类型
Key Type \textbf{Key Type} Key Type 含义 备注 示例 Composite \text{Composite} Composite 多个属性组成的主键 无单一属性主键时适用 sid+name
Surrogate \text{Surrogate} Surrogate 自定义的(代理)主键 无任何属性当主键,就自定义 N/A \text{N/A} N/A
- 代理键在 MySQL \text{MySQL} MySQL中的值,默认为 1/2/3..... \text{1/2/3.....} 1/2/3.....即 Auto-Incremented \text{Auto-Incremented} Auto-Incremented
3️⃣ Foreign Key(FK) \text{Foreign Key(FK)} Foreign Key(FK)
- Foreign Key(FK) \text{Foreign Key(FK)} Foreign Key(FK)
- 含义
Type \textbf{Type} Type 概念 示例 Primary key \text{Primary key} Primary key 确保表中每个记录都唯一,能唯一定位 course.cid, Student.sid
Foreign Key \text{Foreign Key} Foreign Key 指向另一个表格中 Data Entry \text{Data Entry} Data Entry course.sid
- 示例
- Primary Foreign Key(PFK) \text{Primary Foreign Key(PFK)} Primary Foreign Key(PFK)
- 含义:既是自的 PK \text{PK} PK又是别的关系的 FK \text{FK} FK,常见于 Associative Entity \text{Associative Entity} Associative Entity / Weak Entity \text{Weak Entity} Weak Entity
- 示例:中间联结表的两个键
4.3. Integrity \textbf{4.3. Integrity} 4.3. Integrity(完整性)
1️⃣ Integrity Constraint: \text{Integrity Constraint:} Integrity Constraint: 建立表格所要满足一下所有条件
Integrity Constraint \textbf{Integrity Constraint} Integrity Constraint 描述 Key \text{Key} Key 关系的 Primary Key \text{Primary Key} Primary Key必须唯一 Referential Integrity \text{Referential Integrity} Referential Integrity 所有 Foreign Key \text{Foreign Key} Foreign Key(引用)必须有效 Domain Integrity \text{Domain Integrity} Domain Integrity 作 Data Type \text{Data Type} Data Type的区分,比如只给整数属性上输入整数数据 Entity Integrity \text{Entity Integrity} Entity Integrity 属性值可以 Optional \text{Optional} Optional( NULL \text{NULL} NULL),或者必须有值( NOT NULL \text{NOT NULL} NOT NULL) 2️⃣示例
操作对象 操作行为 结果 Enroll \text{Enroll} Enroll表 插入记录(其 sid
不存在于 Student \text{Student} Student表)违反完整性约束 → \to{} →拒绝 Student \text{Student} Student表 删除一个记录 同时也删除对应 Enroll \text{Enroll} Enroll表中的数据
5. ER→Relational Model \textbf{5. ER→Relational Model} 5. ER→Relational Model
5.1. Entity Conversion \textbf{5.1. Entity Conversion} 5.1. Entity Conversion
5.1.1. Single Entity \textbf{5.1.1. Single Entity} 5.1.1. Single Entity
1️⃣转换原理
Entity \textbf{Entity} Entity的 Attributes \textbf{Attributes} Attributes类型 操作 普通情况 正常转换 Composite Attributes \text{Composite Attributes} Composite Attributes 把复合属性展开( Flatten \text{Flatten} Flatten) Multi-Valued Attributes (Small/Fixed Number of Values) \text{Multi-Valued Attributes (Small/Fixed Number of Values)} Multi-Valued Attributes (Small/Fixed Number of Values) 展开 Multi-Valued Attributes (Large Number of Values) \text{Multi-Valued Attributes (Large Number of Values)} Multi-Valued Attributes (Large Number of Values) 创建新表(弱实体) Derived Attributes \text{Derived Attributes} Derived Attributes 直接忽略 2️⃣ Chen’s \text{Chen's} Chen’s的转换示例
- 普通情况:
Employee(SSN(Key),Name,Age)
- Composite Attributes: \text{Composite Attributes:} Composite Attributes: 去除原有复合属性,展开内部的属性
Employee(SSN(Key),Name,Age,Postcode,Street_name,Street_num)
- Multi-Valued Attribute: \text{Multi-Valued Attribute:} Multi-Valued Attribute: 去除原有多值属性,展开内部的属性
Employee(SSN(Key),Name,Age,Num_1,Num_2)
3️⃣ Crow’s Foot \text{Crow's Foot} Crow’s Foot示例
- Composite Attributes: \text{Composite Attributes:} Composite Attributes: 去掉复合属性本身,展开复合属性内部的属性
- Multi-Valued Attributes (Small/Fixed Number of Values) → \text{Multi-Valued Attributes (Small/Fixed Number of Values)}\to{} Multi-Valued Attributes (Small/Fixed Number of Values)→直接展开
- Multi-Valued Attributes (Large Number of Values) \text{Multi-Valued Attributes (Large Number of Values)} Multi-Valued Attributes (Large Number of Values)
- 建立 1-N \text{1-N} 1-N关系: 1-Employee → Plays N-Roles \text{1-Employee}\xrightarrow{\text{Plays}}\text{N-Roles} 1-EmployeePlaysN-Roles,建立 Role \text{Role} Role弱实体
- 建立 N-N \text{N-N} N-N关系: N-Employee → Plays N-Roles \text{N-Employee}\xrightarrow{\text{Plays}}\text{N-Roles} N-EmployeePlaysN-Roles,建立 Role \text{Role} Role弱实体 + + +一个联结表
5.1.2. Weak Entity \textbf{5.1.2. Weak Entity} 5.1.2. Weak Entity
1️⃣规则:其实也就是说辨认一个 Weak Entity \text{Weak Entity} Weak Entity需要——自身 PK+Strong Entity \text{PK+Strong Entity} PK+Strong Entity的 PK \text{PK} PK
弱实体的键 规则 备注 弱实体的主键 强实体的主键 + + +弱实体自身一/多个属性 N/A \text{N/A} N/A 弱实体的外键 强实体的主键 Aka PFK \text{Aka PFK} Aka PFK(外主键) 2️⃣ Chen’s \text{Chen's} Chen’s示例:箭头指向一侧的 PK \text{PK} PK过来,到箭头的另一端当== PFK \text{PFK} PFK==
CREATE TABLE Dependent ( dname CHAR(20) NOT NULL, age INTEGER NULL, cost DECIMAL(7,2) NOT NULL, ssn CHAR(11) NOT NULL, PRIMARY KEY (dname, ssn), -- weak+owner key as composite primary key FOREIGN KEY (ssn) REFERENCES Employees -- refer to owner entity ON DELETE CASCADE -- Owner deleted, all weak entities deleted(确保完整性) );
- 注意
ON DELETE CASCADE
,意思是主实体删除时,弱实体也随之删除3️⃣ Crow’s Foot \text{Crow's Foot} Crow’s Foot示例
5.2. Relationship Conversion \textbf{5.2. Relationship Conversion} 5.2. Relationship Conversion
5.2.1. Unary Relationship \textbf{5.2.1. Unary Relationship} 5.2.1. Unary Relationship
Typle \textbf{Typle} Typle 处理方法 1-1 \text{1-1} 1-1 Put a Foreign key in the relation \text{Put a Foreign key in the relation} Put a Foreign key in the relation 1-N(Key Constrains) \text{1-N(Key Constrains)} 1-N(Key Constrains) Put a Foreign key in the relation \text{Put a Foreign key in the relation} Put a Foreign key in the relation N-N \text{N-N} N-N Generate an Associative Entity \text{Generate an Associative Entity} Generate an Associative Entity 1️⃣ 1-1 \text{1-1} 1-1关系示例:婚姻关系,一个人的主键变成另一个人的外键,即增加一个配偶 ID \text{ID} ID外键
2️⃣ 1-N \text{1-N} 1-N关系示例:员工(包含部分管理者)关系,管理者的主键成为员工的外键
3️⃣ N-N \text{N-N} N-N关系示例: Item \text{Item} Item(包括各种 Componment \text{Componment} Componment成分)关系,建立 Componment \text{Componment} Componment弱实体
- 弱实体的两个外键成为复合主键
- 弱实体要包含关系的描述属性( Descriptive Attributes \text{Descriptive Attributes} Descriptive Attributes)
5.2.2. Binary Relationship \textbf{5.2.2. Binary Relationship} 5.2.2. Binary Relationship
1️⃣ N-N \text{N-N} N-N关系:
- 规则:
- 建立 Associative Entity → 包含 Descriptive Attributes+ \text{Associative Entity}\xrightarrow{包含}\text{Descriptive Attributes+} Associative Entity包含Descriptive Attributes+两边的 PK \text{PK} PK(作为自身 PFK \text{PFK} PFK)
- Associative Entity \text{Associative Entity} Associative Entity是原有两实体的 Weak Entity \text{Weak Entity} Weak Entity
- Crow’s Foot \text{Crow's Foot} Crow’s Foot转换时,关系种类交叉处理(详见示例红虚线)
- Chen’s \text{Chen's} Chen’s示例:创建新关系
Works_In(ssn, did, since)
Relations \textbf{Relations} Relations PK \textbf{PK} PK FK \textbf{FK} FK Employee(ssn, name, age)
ssn
N/A
Department(did, dname, budget)
did
N/A
Works_In(ssn, did, since)
ssn,did
ssn,did
- Crow’s Foot \text{Crow's Foot} Crow’s Foot示例: Conceptual → Physical \text{Conceptual}\to{}\text{Physical} Conceptual→Physical
- 注意联结实体实质上是一个弱实体(实线),联结实体的行数是联结两端的行数乘积
- 原有的 Crow’s Foot \text{Crow's Foot} Crow’s Foot标志需要交叉放置
- Implemetation \text{Implemetation} Implemetation
CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, -- 此处记得空两格,记得逗号; 可以在此处就定义PK CustFirstName VARCHAR(45) ); CREATE TABLE Address ( AddressID INT, StreetNumber VARCHAR(45), PRIMARY KEY (AddressID) -- 也可以在最末尾定义PK ); CREATE TABLE Address_has_Customer ( AddressID INT, CustomerID INT, AddressDateFrom VARCHAR(45), AddressDateTo VARCHAR(45), PRIMARY KEY (AddressID, CustomerID), FOREIGN KEY (AddressID) REFERENCES Address(AddressID), -- FK起始 FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) );
2️⃣ 1-N \text{1-N} 1-N关系( Key Constraints \text{Key Constraints} Key Constraints)
类型 规则 Crow’s Foot \text{Crow's Foot} Crow’s Foot PK from ONE side → FK at MANY side \text{PK from ONE side}\to{}\text{FK at MANY side} PK from ONE side→FK at MANY side Chen’s \text{Chen's} Chen’s 箭头指向一侧的 PK \text{PK} PK过来,到箭头的另一端当== FK \text{FK} FK==,同时描述属性也过来
- Crow’s Foot \text{Crow's Foot} Crow’s Foot示例:一个客户有多个账户
- Chen’s \text{Chen's} Chen’s示例
Employee (ssn, name, age) -- PK ssn Department (did, dname, budget, ssn, since) -- PK did + FK ssn
3️⃣ 1-1 \text{1-1} 1-1关系
- 规则
关系的 Participation \textbf{Participation} Participation限制 Move the Key to the__ \textbf{Move the Key to the\_\_} Move the Key to the__ Optional-Optional \text{Optional-Optional} Optional-Optional 随意将一方的 PK \text{PK} PK移到另一方作 FK \text{FK} FK Mandatory-Mandatory \text{Mandatory-Mandatory} Mandatory-Mandatory 随意将一方的 PK \text{PK} PK移到另一方作 FK \text{FK} FK Mandatory-Optional \text{Mandatory-Optional} Mandatory-Optional 将 Mandatory \text{Mandatory} Mandatory的 PK \text{PK} PK移动到 Optional \text{Optional} Optional作 FK \text{FK} FK - 示例
- 概念设计
- 逻辑设计
5.2.3. Ternary Relationship \textbf{5.2.3. Ternary Relationship} 5.2.3. Ternary Relationship
1️⃣规则:
- 更具关系创建新实体,关系描述属性成为新实体的属性
- 关系中三个实体的 PK → \text{PK}\to{} PK→新实体中的 PFK \text{PFK} PFK
2️⃣ Chen’s \text{Chen's} Chen’s示例
Contracts (supplier_id,part_id,department_id) -- three of each are PFKs Suppliers (id,name) -- id(PK) Parts (id,name) -- id(PK) Departments (id,name) -- id(PK)
3️⃣ Crow’s Foot \text{Crow's Foot} Crow’s Foot示例
- 概念设计
- 逻辑设计
5.3. Participation Constraint \textbf{5.3. Participation Constraint} 5.3. Participation Constraint
1️⃣转为逻辑设计时,要指定属性 NULL/NOT NULL \text{NULL/NOT NULL} NULL/NOT NULL,以确定是 Optional/Mandatory \text{Optional/Mandatory} Optional/Mandatory
2️⃣示例: Eevery department MUST have a manager \text{Eevery department MUST have a manager} Eevery department MUST have a manager
CREATE TABLE Department ( did INTEGER NOT NULL, dname CHAR(20) NOT NULL, budget FLOAT NULL, ssn CHAR(11) NOT NULL, -- ssn 表示每一个Department必须有一个Employee since DATE NULL, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employee ON DELETE NO ACTION );
3️⃣ PK \text{PK} PK一定是 NOT NULL \text{NOT NULL} NOT NULL的