数据库mysql的ER模型、关系模式以及ER模型转化成关系模式

Entity-Relationship Model

1. The E-R model is a widely used data model for database design.
2. It provides a convenient graphical representation, i.e., the E-R diagram, to view data, relationships and constraints.
3. The E-R data model employs three basic notions:
   • Entity sets
   • Relationship sets
   • Attributes

Representing Entity sets in ER Diagram

Entity sets can be represented graphically as follows:

• Rectangles represent entity sets
• Attributes listed inside entity rectangle
• Underline indicates primary key attributes

Relationship Sets

Diamonds represent relationship sets

Relationship Sets with Attributes

Mapping Cardinality

For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following types:

1. One to one

   An entity in A is associated with at most one （最多一个）entity in B
   An entity in B is associated with at most one entity in A

2. One to many

   An entity in A is associated with any number (zero or more) （任意个，包括零个）of entities in B
   An entity in B is associated with at most one entity in A

3. Many to one

   An entity in A is associated with at most one entity in B
   An entity in B is associated with any number (zero or more) of entities in A

4. Many to many

   An entity in A is associated with any number (zero or more) of entities in B
   An entity in B is associated with any number (zero or more) of entities in A

Representing Cardinality Constraints in ER Diagram

We express cardinality constraints by drawing

• either a directed line (->), signifying “one,”（最多一个）
• or an undirected line (—), signifying “many,”（任意个，包括零个）

Participation

1. Total participation (indicated by double line)

   E.g. the participation of student in advisor is total

2. Partial participation

   E.g. the participation of instructor in advisor is partial

Notation for Expressing More Complex Constraints

• min is the minimum of times each entity participates in relationships
• max is the maximum of times each entity participates in relationships
• A maximum value of * indicates no limit

Keys for Relationship Sets

If the relationship set advisor between student and instructor is	then the primary key of advisor is
many-to-many	{student_id, instructor_id}
many-to-one	{student_id}
one-to-many	{instructor_id}
one-to-one	{student_id} or {instructor_id}

Weak Entity Set

does not have sufficient attributes to form a primary key

Strong Entity Set

has a primary key

Identifying relationship

• A weak entity set must relate to a identifying entity set 标识实体集( owner entity set ) via an identifying relationship
• one-to-many from the identifying to the weak entity set
• the participation of the weak entity set is total

Reducing E-R Diagrams to Relational Schemas

Reduction to Relational Schemas

1. Requirement Analysis

2. Conceptual Design

   Translate user requirements to a Entity-Relationship model

3. Logical Design

   The designer maps the Entity-Relationship model into a relational database schema

4. Physical Design

Representing Relationship Sets

• many-to-many
• many-to-one
• one-to-one

relationship sets to relation schemas

1. many-to-many

   a schema with attributes for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set.

   

   advisor = (s_id, i_id)

2. many-to-one

   adding the primary key of the “one” side to the “many” side

   

   instructor = ( ID, name, salary, dept_name)

3. one-to-one

   **For one-to-one relationship sets, extra attribute can be added to either side **

   

   instructor = ( ID, name, salary, student_id )
   student = ( ID, name, tot_cred )
   OR
   instructor = ( ID, name, salary )
   student = ( ID, name, tot_cred, instructor_id )

Placement of Relationship Attributes

• Attributes of a one-to-many relationship set can be repositioned to the entity set on the many side
• Attributes of a one-to-one relationship set can be repositioned to either side

Exercises:

Entity author: author = ( name, address, URL )
Entity book: book = ( ISBN, title, year, price )
Relationship written-by:
book = ( ISBN, title, year, price, author_name )

Result:
author = ( name, address, URL )
book = ( ISBN, title, year, price, author_name )