数据库原理与安全笔记：数据模型

该部分为本科期间数据库原理与安全笔记备份。

A collection of tools for describing

数据库用数据模型这个工具来抽象、表示和处理现实世界中的数据和信息

数据模型应满足三方面要求:

1. 能比较真实地模拟现实世界
2. 容易为人所理解
3. 便于在计算机上实现

Why E-R model?

customer do not master the computer skill

There is only one concept in relation model-relation

Modeling

entity-relationship (E-R):

• a collection of entities
• relationships among entities

Entity Set

entity:an object that exists and is distinguishable from other objects

Entity set: set of entities of the same type that share the same properties

区别于其他的对象的事务或对象。客观世界存在的一个对象。

实体是一个静态的概念，只包括数据的结构而不包括操作。

Attributes

descriptive properties possessed by all members of an entity set

实体所具有的某一个特征。

Domain: the set of permitted values for each attribute 每一个属性都有一个取值范围。

An attribute has a null value when an attribute dos not have value for it

Keys

super key of an entity set:a set of one or more attributes whose values uniquely determine each entity.

candidate key of an entity set:a minimal super key

primary key: one of the candidate keys is selected to distinguish the entity set,never, or very rarely changed.

type

1. Simple and composite attributes.
   • Simple attributes:
     • can not be divided into subparts
     • contains a single value
   • composite attributes:
     • can be divided into subparts
     • consists of several components (e.g., address)
2. Single-valued and multi-valued attributes
   • Multi-valued attributes
     • multiple values for an particular entity
3. Derived attributes
   • derived attribute can be derived from the values of other related attributes or entities

Relationship Sets

relationship: association among several entities

实体与联系的关系是参与

关系中也能带有属性

Degree（度）: number of entity sets that participate in a relationship set

Relationships between more than two entity sets are rare. Most relationships are binary. (More on this later.)

Mapping Cardinality Constraints

映射基数约束： 一个实体集中的元素和另外一个实体集中几个实体发生关系。

Express the number of entities to which another entity can be associated via a relationship set.

For a binary relationship set：

• One to one
• One to many
• Many to one
• Many to many

\(→\):signifying "one"；

\(—\):signifying "many";

these between the relationship set and the entity set.

Participation Constraints

E is entity set and R is relationship set

If every entity in E participates in at least one relationship in R, the participation of E in R is total

If only some entities in E participate in relationships in R, the participation of E in R is partial

NOTE: Total participation is indicated by double line.

Roles

Entity sets involved in a relationship set need not to be distinct

Can use roles to specify how entities interact via relationship set

一个实体在一个联系中起的作用称为角色(role)。角色通常都是含蓄的并常常不被定义。实体在联系中的作用称为实体的角色

当同一个实体集不止一次参与一个联系集时，为区别各实体的参与联系的方式，需要显式指明其角色。（常用在一元关系中）

参加约束

实体的参与度:实体参与联系的最小和最大次数

The number of the entity participate in the relation（ one to many)

注意可以用“*”号进行省略。

易错点

0..*表示表示在这个联系中可以有多个loan参与到这个联系中。

1..1表示最多只能有一个customer参与到这个联系当中来。

如果从表明上看， *到1，感觉是多对1，但是恰恰相反，而是1对多

ERD with Ternary Relationship

Cardinalities for ternary relationships can take the form of 1:1:1, 1:1:M, 1:M:N or M:N:P.

The cardinality is defined by a pair of two entity instances associated with the other single entity instance.

Keys for Relationship Sets

\[R \ Set:Primary \ key(E_1)⋃Primary \ key(E_2)⋃...⋃Primary \ key(E_n)⋃｛a_1,a_2,...,a_n｝ \]

\[Super \ Key:Primary \ key(E_1)⋃Primary \ key(E_2)⋃...⋃Primary \ key(E_n) \]

\(｛a_1,a_2,...,a_n｝\): Attributes of relationship set R

\(Primary \ key(E_i)\): denote the set of attributes that forms the primary key of entity set E

Must consider the mapping cardinality of the relationship set when deciding what are the candidate keys.

Need to consider semantics of relationship set in selecting the primary key in case of more than one candidate key

UML Modeling Notation

Entity—class

Relationship—association

设计

设计原则

• 忠实性:忠实于应用的具体要求
• 简单性:没有需要，不要在设计中添加更多的成分
• 避免冗余:
  • 描述同一概念的两个或多个关系被认为是冗余的。
  • 需要注意的是两个实体间允许两个或更多关系的存在，只要这些关系具有不同的含义。在这种情况下这些关系不是冗余的。
• 选择正确的联系:如果联系可以由其他联系推导出来，该联系就不需要加入
• 选择正确的元素种类:

Design Issues

如何区分实体和属性

实体与属性是相对而言的。同一事物，在一种应用环境中作为“属性”，在另一种应用环境中可能作为“实体”。

一般原则

• 属性不能再具有需要描述的性质。即属性必须是不可分的数据项，不能再由另一些属性组成。
• 属性不能与其他实体具有联系。联系只发生在实体之间。
• 单值的

符合上述特性的事物一般作为属性对待。

为了简化E-R图的处置，现实世界中的事物凡能够作为属性对待的，应尽量作为属性。

Possible guideline is to designate a relationship set to describe an action that occurs between entities.

实体和联系都可以接受，尽量使用联系

三元联系对二元联系

三元联系不能被多个二元联系替代。

判定方法:

1. 根据语义来判定
2. 能够用低元联系表示，不用高元联系表示

Some relationships that appear to be non-binary may be better represented using binary relationships

联系的属性一般出现在“多”对“多”的二元关系或三元关系上。一般“一”对“一”或“一”对“多”关系上不会放属性（会引起歧义）。而且这些属性可以移至一端的实体中。

Weak Entity Sets

The existence of a weak entity set depends on the existence of a identifying entity set(标识实体集）

An entity set that does not have a primary key is referred to as a weak entity set.

it must relate to the identifying entity set via a：

• total
• one-to-many relationship set from the identifying o the weak entity set

The discriminator (or partial key)分辨符(部分码) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set that related to the same entity of identifying entity set.

\[Primary \ Key_{weak-entity}=Primary \ Key_{strong-entity}+Discriminator_{weak-entity} \]

弱实体集的属性不能形成主码，但不是说弱实体集就没有主码。由该弱实体集所存在依赖的强实体集的主码和该若实体集的分辨符(部分码)共同构成的属性集合就是它的主码。

支配实体集拥有它所标识的弱实体集，而弱实体集与其拥有者之间的联系称为标识性联系，相应的联系集称为标识性联系集。

这里需要注意的一点是：弱实体集的属性不能形成主码，但不是说弱实体集就没有主码。由该弱实体集所存在依赖的强实体集的主码和该弱实体集的分辨符(部分码)共同构成的属性集合就是它的主码。

画法示例：

引入弱实体的好处：有效解决了属性实体化后缺少标识问题

使用实体的属性，还是使用弱实体？

如果不研究属性和其它属性/联系/实体之间的联系， 应该使用属性。

如果需要研究属性和其它属性/联系/实体之间的联系， 则需要将属性实体化，必要时使用弱实体。

ISA Hierarchies

A ISA B:every A entity is also considered to be a B entity.

as superclass - subclass relationship

Specialization 特殊化

实体集内部进行分组的过程称为特殊化。

实体集可以根据多个差异特征来进行特殊化。

Attribute inheritance:a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.

ISA relationship:specialization（特殊化）、generalization（概括）

虽然概括是特殊化的逆过程，但是概括和特殊化还是有很大的区别的:

特殊化是从单一的实体集出发，通过创建不同的低层实体集来强调同一实体集中不同实体间的差异;设计者采用特殊化的原因正是为了表达同一实体集中不同实体间的互不相同的特征；概括是在多个实体集共性的基础上将它们综合成一个高层实体集。概括主要用于强调低层实体集间隐藏于它们区别背后的相似形。

特殊化和概括所产生的低层实体集和高层实体集的一个重要特性就是属性继承。高层实体集（也叫超类）的属性被低层实体集（也叫子类）继承。

需要注意的是以下两点：一是同高层实体集相联系的所有属性和联系也适用于它的所有低层实体集；二是低层实体集所特有 的性质(即属性)仅仅适用于某个特定的低层实体集。

Design Constraints on a Specialization

Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization:

• Disjoint:an entity can belong to only one lower-level entity set
• an entity can belong to more than one lower-level entity set

Completeness constraint （完全性约束）:

• total : an entity must belong to one of the lower-level entity sets（全部一般化）
• partial: an entity need not belong to one of the lower-level entity sets（部分一般化）

E-R Design Decisions

1. use of an attribute or entity set to represent an object
2. Whether a real-world concept is best expressed by an entity set or a relationship set
3. use of a ternary relationship versus a pair of binary relationships
4. use of a strong or weak entity set
5. use of specialization/generalization

总结

Reduction to Relation Schemas

each entity set there is a unique table which is assigned the name of the corresponding entity set.

Each table has a number of columns (generally corresponding to attributes), which have unique names.

Representing Entity Sets as Schemas

A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set.

A many-to-many relationship set is represented as a schema with attributes for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set.

EXAMPLE:

方法一和方法二的优缺点:

1. 方法一：多产生一个关系；但对于有些查询，方法一需要两次连接，而方法二只需要一次连接即可
2. 方法二：缺点是浪费空间，如果有些账户没有顾客的话

Redundancy of Schemas

For one-to-one relationship sets, either side can be chosen to act as the “many” side

Composite Attributes

flattened out by creating a separate attribute for each component attribute

Multivalued Attributes

A multivalued attribute M of an entity E is represented by a separate schema EM

Each value of the multivalued attribute maps to a separate tuple of the relation on schema EM

关系翻译

一元一对一联系的翻译:

create table employee 
( 
emp_id char(10), 
emp_name char(20), 
spouse_id char(10), 
primary key(emp_id), 
foreign key(spouse_id) references employee(emp_id )
);

一元一对多联系的翻译

create table employee
(
emp_id char(10),
leader_id char(10) not null, 
primary key(emp_id), 
foreign key(leader_id) references employee (emp_id),
);

一元多对多联系:

create table person 
( person_id char(10) primary key , 
person_name char(20));
create table friend 
( person_id char(10),  friend_id char(10), 
primary key(person_id, friend_id), 
foreign key(person_id) references person, 
foreign key(friend_id) references person, 
check(person_id < friend_id) 
);

三元1:1:1联系的翻译:

Technician(emp_id)

Project(p_id), Notebook(nid)

Use-notebook(emp_id, p_id, nid)

任选其中两个实体的primary key作为联系的primary key

三元1:1:N的翻译:

Employee(emp_id)

Project(p_id)

Location(l_id)

Assigned-to(emp_id, p_id，l_id)或者Assigned-to(emp_id, l_id,p_id)

3元1Ⓜ️n的翻译:

Engineer(emp_id)

Project(p_id)

Manager(l_id)

Assigned-to(emp_id, p_id，m_id)

3元m:n:p联系的翻译:

Employee(emp_id)

Project(p_id)

Skill(s_id)

Assigned-to(emp_id, p_id，s_id)

Representing Specialization as Schemas:

Method 1:

• Form a schema for the higher-level entity
• Form a schema for each lower-level entity set, include primary key of higher-level entity set and local attributes

Method 2:Form a schema for each entity set with all local and inherited attributes

两种方法的比较:

1. 方法一:
   • 当查询涉及到employee,customer的所有属性需要连接操作;
   • 当查询仅涉及到person的属性时则在person关系上进行即可;另一个优点是可以存储非employee和customer的实体
2. 方法二:
   • 主要确定无法存储非employee和customer的实体;
   • 优点是仅涉及到employee或customer的查询仅在一个关系上进行即可,不需要额外的连接操作,但涉及到所有person的查询则需要在两个关系上进行;