CS 425 – Database Organization - 2- Chapter 7: Entity-Relationship Model（2）

41、Review

1. Identify Entities
2. Identify Relationship
3. Determine Attributes
4. Determine Cardinality Constraints
   1)识别实体
   2)确定关系
   3)确定属性
   4)确定基数约束

42、Chapter 7: Entity-Relationship Model
实体关系模型

• Design Process 设计过程
• Modeling 建模
• Constraints 约束
• E-R Diagram e - r图
• Design Issues 设计问题
• Weak Entity Sets 弱实体集
• Extended E-R Features 扩展 E-R 功能
• Reduction to Relation Schemas 简化为关系模式
• Database Design 数据库设计
• UML 统一建模语言

43、Weak Entity Sets
弱实体集

① An entity set that does not have a primary key is referred to as aweak entity set.
没有主键的实体集被称为“弱实体集”。

② The existence of a weak entity set depends on the existence of a identifying entity set
弱实体集的存在依赖于识别实体集的存在

• It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set
  它必须通过一个从识别到弱实体集的一对多关系集与标识实体集相关联
• Identifying relationshipdepicted using a double diamond
  用双菱形标识关系

③ 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 are associated with the same entity of the identifying entity set
弱实体集的“”标识符“”(或部分键)是一组属性，用于区分弱实体集中与标识实体集的同一实体相关联的所有实体

④ 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.
弱实体集的主键由弱实体集存在相关的强实体集的主键和弱实体集的标识符组成。

44、 Weak Entity Sets (Cont.)

① We underline the discriminator of a weak entity set with a dashed line.
我们用虚线在弱实体集的鉴别符下画线。
② We put the identifying relationship of a weak entity in a double diamond.
我们把弱实体的识别关系放在双菱形中。
③ Primary key for section – (course_id, sec_id, semester, year)
section的主键- (course_id, sec_id, semester, year)

45、Weak Entity Sets (Cont.)

① Note: the primary key of the strong entity set is not explicitly stored with the weak entity set, since it is implicit in the identifying relationship.
注意:强实体集的主键没有显式地存储在弱实体集中，因为它隐含在标识关系中。

② If course_id were explicitly stored, section could be made a strong entity, but then the relationship between section and course would be duplicated by an implicit relationship defined by the attribute
course_id common to course and section
如果course_id被显式存储，section可以被做成一个强实体，但是section和course之间的关系将被属性定义的隐式关系复制
课程和section共有的Course_id

46、Reduction to Relational Schemas
简化为关系模式

① Entity sets and relationship sets can be expressed uniformly as relation schemas that represent the contents of the database.
实体集和关系集可以统一表示为表示数据库内容的关系模式。

② A database which conforms to an E-R diagram can be represented by a collection of relation schemas.
符合E-R图的数据库可以用一组关系模式来表示。

③ For each entity set and relationship set there is a unique relation schema that is assigned the name of the corresponding entity set or relationship set.
对于每个实体集和关系集，都有一个唯一的关系模式，该模式被分配相应的实体集或关系集的名称。

47、Representing Entity Sets With Simple Attributes
用简单属性表示实体集

① A strong entity set reduces to a schema with the same attributes
强实体集可以简化为具有相同属性的模式

student(ID, name, tot_cred)

② A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set
一个弱实体集变成一个表，其中包含标识强实体集的主键的列

section ( course_id, sec_id, sem, year )

48、Representing Relationship Sets
代表关系集

① 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: schema for relationship set advisor
例如:关系设置顾问的模式

advisor = (s_id, i_id)

49、Redundancy of Schemas
冗余模式

① Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side
多端上的多对一和一对多关系集可以通过向“多”端添加一个额外的属性来表示，该属性包含“1”端的主键
② Example: Instead of creating a schema for relationship set inst_dept, add an attribute dept_name to the schema arising from entity set instructor
示例:与其为关系集inst_dept创建模式，不如向实体集讲师产生的模式添加属性dept_name

50、Redundancy of Schemas (Cont.)

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

• That is, extra attribute can be added to either of the tables corresponding to the two entity sets
• If the relationship is total in both sides, the relation schemas from the two sides can be merged into one schema

② If participation is partial on the “many” side, replacing a schema by an extra attribute in the schema corresponding to the “many” side could result in null values
③ The schema corresponding to a relationship set linking a weak entity set to its identifying strong
entity set is redundant.
◦ Example: The section schema already contains the attributes that would appear in the sec_course
schema

51、Composite and Multivalued Attributes

① Composite attributes are flattened out by creating a separate attribute for each component attribute

• Example: given entity set instructor with composite attribute name with component attributes first_name and last_name the schema corresponding to the entity set has two attributes name_first_name and name_last_name
  • Prefix omitted if there is no ambiguity

② Ignoring multivalued attributes,extended instructor schema is

instructor(ID,
first_name, middle_initial, last_name,
street_number, street_name,
    apt_number, city, state, zip_code,
date_of_birth)

52、Composite and Multivalued Attributes

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

• Schema EM has attributes corresponding to the primary key of E and an attribute corresponding to
  multivalued attribute M
• Example: Multivalued attribute phone_number of instructor is represented by a schema:
  inst_phone= ( ID, phone_number)
• Each value of the multivalued attribute maps to a separate tuple of the relation on schema EM
  • For example, an instructor entity with primary key 22222 and phone numbers 456-7890 and 123-4567 maps to two tuples:
    (22222, 456-7890) and (22222, 123-4567)