数据库系统概论——ER Model Constraints

ER Model Constraints

Constraints:

A constraint = an assertion about the database that must be true at all times

约束=数据库中任何时间都为真的断言

Part of the database schema 

数据库模式的一部分

Very important in database design

Commonly used constraints：

*Keys: resident identification card number uniquely identifies a person.

键约束

Single-value constraints:  a person can have only one father.

单值约束

*Referential integrity constraints: if you work for a company, it must exist in the database.

引用完整性约束

*Domain constraints:  peoples’ ages are between 0 and 150.

域约束

General constraints:  all others (at most 50 students enroll in a class)

公共约束

Importance of Constraints

Give more semantics to the data, help us better understand it

Allow us to refer to entities (e.g, using keys)

Enable efficient storage, data lookup, etc.

keys in E/R Diagrams

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

在关系中能唯一标识元组的属性集称为关系模式的超键

A candidate key of an entity set is a minimal super key

最小的超键称为候选码。（某一属性组的值能唯一地标识一个元组，而其他自己不能，则称该属性为候选码）

    e.g, Customer_id is candidate key of customer

Although several candidate keys may exist, one of the candidate keys is selected to be the primary key

虽然可能会有多个候选码存在，但是被选择的一个候选码会成为主码。

More about Keys

Every entity set must have a key, So that the entity can be identified

每个实体集都必须有一个键，目的是实体能够被确认。

A key can consist of more than one attribute

一个键可以包含多于一个的属性。

There can be more than one key for an entity set, one key will be designated as primary key.

实体集可以有多个键, 某一个键将被指定为主键。

Weak Entity Sets

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

没有主键的实体集叫做弱实体集。

The existence of a weak entity set depends on the existence of a identifying 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 depend on one particular strong entity.

弱实体集的鉴别器（或者部分键）是一个能够区分弱实体集和它依赖的强实体集的属性集合。

The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity

set is existence dependent, plus the weak entity set's discriminator.

弱实体集的主键由所依赖的强实体集的主键和弱实体集的鉴别器构成。

In a relational database, a weak entity is an entity that cannot be uniquely identified by its attributes alone; therefore,it must use a foreign key in conjunction with its attributes to create a primary key. The foreign key is typically a primary key of an entity it is related to.

ER Modeling Principle

1. Avoid redundancy. 避免冗余
2. Limit the use of weak entity sets. 限制弱实体集的使用
3. Don't use an entity set when an attribute will do. 尽可能使用属性，而不是实体集。

Good Example:

Bad Example:

This design states the manufacturer of a beer twice: as an attribute and as a related entity.

这设计将啤酒生产商描述了两次：既当作实体集，也当作属性。

This design repeats the manufacturer's address once for each beer; loses the address if there are temporarily no beers for a manufacturer.

啤酒暂时不存在时，就无法获得啤酒生产商的地址。

Since the manufacturer is nothing but a name, and is not at the "many" end of any relationship, it should not be an entity set.

既然生产商只有一个信息，并且啤酒没有多个生产商，那生产商就不该作为一个实体集。

Good Example: