Transcript 数据库原理

数据库原理
Principles of Database System
第3章 概念建模
Conceptual Modeling
Textbook:Chapter 3 Data Modeling Using the Entity-Relationship Model
课程教材的架构

数据库基本概念(Introduction)



Chapter 1:Databases and Database Users
Chapter 2:Database System Concepts and
Architecture
关系数据模型(逻辑模型之一：Relational
Model)




Chapter 5:The Relational Data Model and
Relational Database Constraints(“数据结构”和
“数据的约束条件”)
Chapter 6:Relational Algebra and Relational
Calculus(“数据操作”)
Chapter 8:SQL-99 (“数据操作”)
Chapter 9:More SQL (“数据操作”)
2
课程教材的架构(续)

数据库设计




Chapter 3:Data Modeling Using the Entity-Relationship
Model(概念模型之一：Entity-Relationship Model)(Conceptual
Modeling) (本讲内容)
Chapter 7:Relational Database Design by ER- and
EER-to-Relational Mapping
Part 3:Database Design Theory and
Methodology(“数据模型优化”的理论基础)
Chapter 16:Physical Database Design and
Tuning(物理数据库设计)


Chapter 13:Disk Organization, Basic File Structures, and
Hashing(物理数据模型)
Chapter 14:Indexing Structures for Files(物理数据库设计
的主要任务)
3
本章主要内容







3.1 使用高级概念数据模型进行数据库设
计
3.2 一个示例数据库应用
3.3 实体类型、实体集、属性和键
3.4 联系类型、联系集、角色和结构约束
3.5 弱实体型的基本概念
3.6 改进company数据库的er图
3.7 E-R图、命名约定和设计问题
4
Phases of Database Design and Implementation
Process(数据库设计的基本过程)







Phase 1:Requirements Collections and Analysis(需求
收集与分析)
Phase 2:Conceptual Database Design(概念结构设计)
Phase 3:Choice of a DBMS(选择合适的DBMS)
Phase 4:Data Model Mapping (Logical Database
Design)(逻辑结构设计)
Phase 5:Physical Database Design(物理结构设计)
Phase 6:Database System Implementation(数据库实
施)
Phase 7:Database System Operation and
Maintenance(数据库运行和维护)
5
本章主要内容







3.1 使用高级概念数据模型进行数据库设
计
3.2 一个示例数据库应用
3.3 实体类型、实体集、属性和键
3.4 联系类型、联系集、角色和结构约束
3.5 弱实体型的基本概念
3.6 改进company数据库的er图
3.7 E-R图、命名约定和设计问题
6
company




1.公司以部门来组织。每个部门都有唯一的名称和编号，
并有一个专门的员工（部门经理）负责管理这个部门。需
要记录部门经理开始管理的起始日期，一个部门可能有多
个办公地点。
2.一个部门控制着多个项目，每个项目都有唯一的名称、
编号和单一的办公地点。
3.保存每个员工的姓名，身份证号，地址，工资，性别以
及出生日期。每个员工归于某个部门，但可能参与多个部
门的工作，而这些项目不一定由一个部门控制。记录每个
员工每周在每个项目上的工作时间，同时记录每个员工的
直接主管。
4.考虑到保险问题，需要记录每个员工的受抚养人的信息，
记录受抚养人的姓名，性别，出生日期和该员工的关系。
7
Entity-Relationship (ER)
Model(实体－联系模型)


A popular high-level conceptual data
model.
The ER model describes data as
entities(实体), relationships(联系), and
attributes(属性).
8
ER
9
本章主要内容







3.1 使用高级概念数据模型进行数据库设
计
3.2 一个示例数据库应用
3.3 实体类型、实体集、属性和键
3.4 联系类型、联系集、角色和结构约束
3.5 弱实体型的基本概念
3.6 改进company数据库的er图
3.7 E-R图、命名约定和设计问题
10
3.3 实体类型、实体集、属性和键



3.3.1 实体和属性
3.3.2 实体类型、实体集、键和值集
3.3.3 company的初步设计
11
Entities and Attributes

The basic object that the ER model
represents is an entity, which is a “thing” in
the real world with an independent existence
（独立存在）.




An object with a physical existence—a particular
person, car, house, or employee
An object with a conceptual existence—a company,
a job, or a university course
Each entity has attributes—the particular
properties(特性) that describe it.
A particular entity will have a value for each
of its attributes. The attribute values that
describe each entity become a major part of
the data stored in the database.
12
公司名
总部
总裁
13
Composite(复合) Versus Simple
(Atomic：原子的) Attributes

Composite attributes


Composite attributes can be divided into
smaller subparts(组件), which represent
more basic attributes with independent
meanings.
Simple (Atomic) attributes

Attributes that are not divisible are called
simple or atomic attributes.
14
根据业务需求决定是否分解
“复合属性”为“原子属性”
州
邮政编码
楼号
15
Single-valued(单值) Versus
Multivalued(多值) Attributes

Single-valued attribute



对于一个特定实体，属性都只有一个值
例如：人的“年龄”属性
Multivalued attribute



一个属性可能有多个值
例如：汽车的颜色属性，人的“学位”属性
多值属性可能有下界和上界，以限制所允许的值的
个数
注意区分“复合”属性与“多值”属性
16
Stored(存储) Versus Derived(导出)
Attributes

Stored attribute


受抚养人的“生日”属性
Derived attribute

受抚养人的“年龄”属性
17
Null Values(空值)

Not Applicable(不适用的)


部分没有学位员工的“学位”属性
Unknown(未知的)

部分学生的“家庭电话”属性：学生没有填
写此项数据
18
复杂属性

可以任意嵌套复合属性和多值属性。


例：57页，图3.5
{addressphone({phone(areacode,phonenu
mber)},address(streetaddress(number,stre
et,apartmentnumber),city,state,zip))}
19
3.3 实体型、实体集、属性和键



3.3.1 实体和属性
3.3.2 实体型、实体集、键和值集
3.3.3 company的初步设计
20
Entity Types




entity type ：Entities with the same basic
attributes are grouped or typed into an entity
type. （实体类型：定义一个具有相同属性的实体集
合。）
Entity sets:the collection of all entities of a
particular entity type in the database at any
point in time.(某一时刻数据库中一个特定实体类型
的所有实体的集合)
实体类型用矩形框表示，框内为实体名字，属性名用
椭圆框起，并用直线与实体类型相连
实体类型为内涵，实体集为外延
21
Entity Types(实体型) and Entity Sets(实体集)
22
Key Attributes(键属性) of an
Entity Type

An entity type usually has an attribute
whose values are distinct for each
individual entity in the collection(集合).
Such an attribute is called a key
attribute, and its values can be used to
identify each entity uniquely.
23
Composite Key(复合键)


Sometimes, several attributes together form a
key, meaning that the combination of the
attribute values must be distinct for each
entity. If a set of attributes possesses this
property, we can define a composite attribute
that becomes a key attribute of the entity
type.
Superfluous(多余的) attributes must not be
included in a key.
24
Some entity types have more than one key attribute.
25
Value Sets(值集) (Domains：域) of
Attributes

Each simple attribute of an entity type
is associated with a value set (or
domain of values), which specifies the
set(集合) of values that may be
assigned to that attribute for each
individual entity.
26
3.3 实体类型、实体集、属性和键



3.3.1 实体和属性
3.3.2 实体类型、实体集、键和值集
3.3.3 company的初步设计（定义实体类
型和属性）
27
company




1.公司以部门来组织。每个部门都有唯一的名称和编号，
并有一个专门的员工（部门经理）负责管理这个部门。需
要记录部门经理开始管理的起始日期，一个部门可能有多
个办公地点。
2.一个部门控制着多个项目，每个项目都有唯一的名称、
编号和单一的办公地点。
3.保存每个员工的姓名，身份证号，地址，工资，性别以
及出生日期。每个员工归于某个部门，但可能参与多个部
门的工作，而这些项目不一定由一个部门控制。记录每个
员工每周在每个项目上的工作时间，同时记录每个员工的
直接主管。
4.考虑到保险问题，需要记录每个员工的受抚养人的信息，
记录受抚养人的姓名，性别，出生日期和该员工的关系。
28
29
Initial
Design of
Entity
Types:
EMPLOYEE,
DEPARTMENT,
PROJECT,
DEPENDENT
30
本章主要内容







3.1 使用高级概念数据模型进行数据库设
计
3.2 一个示例数据库应用
3.3 实体类型、实体集、属性和键
3.4 联系类型、联系集、角色和结构约束
3.5 弱实体型的基本概念
3.6 改进company数据库的er图
3.7 E-R图、命名约定和设计问题
31
3.4 联系类型、联系集、角
色和结构约束




3.4.1
3.4.2
3.4.3
3.4.4
联系类型、联系集和关系实例
联系度、角色名和递归联系
联系类型和约束
联系类型的属性
32
Refining the initial design by
introducing relationships(联系)


The initial design is typically not complete
Some aspects in the requirements will be
represented as relationships


In fact, whenever an attribute of one entity type
refers to another entity type, some relationship
exists.
In the ER model, these references(参照) should
not be represented as attributes but as
relationships.
33
Relationships and Relationship
Types(联系型)


A relationship relates two or more distinct entities
with a specific meaning.
 For example, EMPLOYEE John Smith works on the
ProductX PROJECT, or EMPLOYEE Franklin Wong
manages the Research DEPARTMENT.
Relationships of the same type are grouped or typed
into a relationship type.
 For example, the WORKS_ON relationship type in
which EMPLOYEEs and PROJECTs participate(参
与), or the MANAGES relationship type in which
EMPLOYEEs and DEPARTMENTs participate.
34
Relationship type vs. relationship set

Relationship Type:




Is the schema description of a relationship
Identifies the relationship name and the
participating entity types
Also identifies certain relationship constraints
Relationship Set:


The current set of relationship instances
represented in the database
The current state of a relationship type
35
3.4 联系类型、联系集、角
色和结构约束




3.4.1
3.4.2
3.4.3
3.4.4
联系类型、联系集和联系实例
联系度、角色名和递归联系
联系类型和约束
联系类型的属性
36
Degree(度) of a Relationship Type

The degree of a relationship type is the number of
participating entity types.
 The WORKS_FOR relationship is of degree two. A
relationship type of degree two is called binary(二
元), and one of degree three is called ternary(三
元).
37
角色和递归联系

角色：参与到一个联系类型中的各个实
体类型都在联系中扮演了一个特定的角
色。


例：职工，部门
递归联系：同一实体类型可能会以不同
的角色多次参与到一个联系类型中。

例：职工，领导 p65
38
Recursive Relationship
e1 supervises e2 and e3; e4 supervises e6 and e7; and e5 supervises e1 and e4
39
3.4 联系类型、联系集、角
色和结构约束




3.4.1
3.4.2
3.4.3
3.4.4
联系类型、联系集和联系实例
联系度、角色名和递归联系
联系类型和约束
联系类型的属性
40
Constraints(约束) on Relationships

Cardinality Ratio(基数比)




One-to-one (1:1)
One-to-many (1:N) or Many-to-one (N:1)
Many-to-many (M:N)
Existence Dependency(存在依赖) Constraint
(also called participation constraint：参与约束)


zero (optional participation/partial participation,
not existence-dependent)
one or more (mandatory participation/total
participation, existence-dependent)
41
Cardinality(映射基数) Ratios for Binary Relationships


The cardinality ratio for a binary relationship specifies
the maximum number of relationship instances that
an entity can participate in.
The possible cardinality ratios for binary relationship
types are 1:1, 1:N, N:1, and M:N.
42
Relationship instances of the WORKS_FOR N:1 relationship
between EMPLOYEE and DEPARTMENT
43
Relationship instances of the M:N WORKS_ON relationship
between EMPLOYEE and PROJECT
44
Existence Dependency(存在依
赖) Constraint


Participation constraint:whether the existence
of an entity depends on its being related to
another entity via the relationship type.
This constraint specifies the minimum number
of relationship instances that each entity can
participate in, and is sometimes called the
minimum cardinality constraint.


Total Participation(完全参与) or existence
dependency(存在依赖),完全参与用双线表示
Partial Participation(部分参与)，部分参与用单线表
示
45
3.4 联系类型、联系集、角
色和结构约束




3.4.1
3.4.2
3.4.3
3.4.4
联系类型、联系集和联系实例
联系度、角色名和递归联系
联系类型和约束
联系类型的属性
46
Attributes of Relationship Types


Relationship types can also have
attributes, similar to those of entity
types.
For example: to record the number of
hours per week that an employee works
on a particular project, we can include
an attribute Hours for the WORKS_ON
relationship type.
47
本章主要内容







3.1 使用高级概念数据模型进行数据库设
计
3.2 一个示例数据库应用
3.3 实体类型、实体集、属性和键
3.4 联系类型、联系集、角色和结构约束
3.5 弱实体型的基本概念
3.6 改进company数据库的er图
3.7 E-R图、命名约定和设计问题
48
Weak Entity Types(弱实体型)



Entity types that do not have key attributes of
their own are called weak entity types. In
contrast, regular entity types that do have a
key attribute are sometimes called strong
entity types.
A weak entity type normally has a partial key,
which is the set of attributes that can
uniquely identify weak entities that are
related to the same owner entity.
主(parent entity type)-从(child entity type)关
系
49
50
本章主要内容







3.1 使用高级概念数据模型进行数据库设
计
3.2 一个示例数据库应用
3.3 实体类型、实体集、属性和键
3.4 联系类型、联系集、角色和结构约束
3.5 弱实体型的基本概念
3.6 改进company数据库的er图
3.7 E-R图、命名约定和设计问题
51
Refining the COMPANY database schema by
introducing relationships


By examining the requirements, six binary
relationship types are identified
Listed below with their participating entity
types:






WORKS_FOR (between EMPLOYEE, DEPARTMENT)
MANAGES (also between EMPLOYEE,
DEPARTMENT)
CONTROLS (between DEPARTMENT, PROJECT)
WORKS_ON (between EMPLOYEE, PROJECT)
SUPERVISION (between EMPLOYEE (as
subordinate), EMPLOYEE (as supervisor))
DEPENDENTS_OF (between EMPLOYEE,
52
DEPENDENT)
number
name
name
1
department
control
n
1
1
have
1
number
1
n
1
have
project
location
n
Works_for
manages
startdate
1
Name(fname,ln
ame,minit)
hours
workson
1
birthdate
m
employee
ssn
1 n
address
sex
birthdate
m
surpeviso
r
depend
sex
n
dependentname
dependent
relationship
53
54
本章主要内容







3.1 使用高级概念数据模型进行数据库设
计
3.2 一个示例数据库应用
3.3 实体类型、实体集、属性和键
3.4 联系类型、联系集、角色和结构约束
3.5 弱实体型的基本概念
3.6 改进company数据库的er图
3.7 E-R图、命名约定和设计问题
55
56
3.7.3 Discussion on Relationship Types

In the refined design, some attributes from
the initial entity types are refined into
relationships:





Manager of DEPARTMENT -> MANAGES
Works_on of EMPLOYEE -> WORKS_ON
Department of EMPLOYEE -> WORKS_FOR
etc
More than one relationship type can exist
between the same participating entity types


MANAGES and WORKS_FOR are distinct
relationship types between EMPLOYEE and
DEPARTMENT
Different meanings and different relationship
instances.
57
练习题

学校中有若干系，每个系有若干班级和
教研室，每个教研室有若干教员，其中
有的教授和副教授每人各带若干研究生，
每个班有若干学生，每个学生选修若干
课程，每门课可由若干学生选修。
58
作业



3.3
3.4
3.16 仿照COMPANY的E-R图绘制出
UNIVERSITY的E-R图
59