Entity-Relationship Model
- The E-R model is a widely used data model for database design.
- It provides a convenient graphical representation, i.e., the E-R diagram, to view data, relationships and constraints.
- 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:
-
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 -
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 -
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 -
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
-
Total participation (indicated by double line)
E.g. the participation of student in advisor is total
-
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
-
Requirement Analysis
-
Conceptual Design
Translate user requirements to a Entity-Relationship model
-
Logical Design
The designer maps the Entity-Relationship model into a relational database schema
-
Physical Design
Representing Relationship Sets
- many-to-many
- many-to-one
- one-to-one
relationship sets to relation schemas
-
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)
-
many-to-one
adding the primary key of the “one” side to the “many” side
instructor = ( ID, name, salary, dept_name)
-
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 )