Understanding Database 4 - Entity Relationship(ER) Model

ER Design phase 

1. Requirements analysis: what problem is being solved? What does the DB need to do? 

2. Logical design: Identification of entities and their relationships (ER model) 

3. Normalization: systematic simplification and clarification of the logical design 

4. Physical Design: implementation in DBMS

 

ER model concepts 

1. Entity: thing, A noun 

2. Attribute: a property, An adjective (PK mark with *)

3. Relationship: a semantic link, A verb (two entity relationship: Binary, three entity type involve:Ternary)

4. Instance: Different data of Entity (another tuple) 

Figure 1. ER model components

** PKs, FKs, and data constraints will be added during DB implementation, but they should also be considered during conceptional design.

 

Cardinality constraints

How many instances of entity types can take part in the relationship between them????

The entity has various instances - which is a set of attribute values. (I would say each row from the table)

So, how many are they related to the other table? will decide the cardinality. (Zero, One or Many) 

-> In other words, for each relationship, mark the minimum and maximum cardinality. Check out the Figure 2.

• Zero or Many: Location can have zero or Many Location_Player - in other words
• One or Many: One or Many relationships 
• Zero or One: Location_player can be included zero or one location. (MAY)
• One and Only one: Test_table can have one and only one Location. (MUST)

Figure 2. Example of ER diagram relationship-crow's foot diagram

※Many to Many relationship

: this can be due to the violates atomicity(each attribute holds only one single value) 

-> solution: Introduce new joining(bridging /intersection/junction) entity. 

From the lecture materials, understanding database at UOM

From the lecture materials, understanding database at UOM

Crow's foot diagram 

Many signs look like crow's feet. Thus it is called the "crow's foot diagram". 

There is also another famous one called "Chen Notation". 

 

Weak Entity 

If a certain entity's existence totally depends on another entity, we call it a weak entity. For instance, hotel_Room can not exist if the hotel does not exist; thus, hotel_room is a weak entity. This Weak entity doesn't have PK. Instead, it has a discriminator/partial key. Which can be composited with PK of the parent table, and the composite of PK of the parent table + Discriminator used as a composite key. (Hotel.id + room.number can be used as identifier)

Figure3. Example of weak entity

Strong entity(Regular entity)

Constraints(NOT NULL), defaults(NONE, ZERO, etc.), and keys(PK, AUTO_INCR, FK) must be given.