Geog 495: GIS Database Design

10/10/05

Entity-Relationship Modeling (ERM)

Building block of ERM

 

How attribute is denoted in ERD?

 

1. Attribute

 

Simple and composite attributes

Single-valued and multivalued attributes

 

Q. SSN

 

Derived attributes

 

2. Relationships

 

 

--------------------------

2-1. Connectivity

 

Describes relationship classification

e.g. Student and class has many to many relationship because students can take more than one course and class can hold more than one students

 

Most of relationship connectivity is 1:M

M:N is transformed to 1:M due to redundancy

 

2-2. Cardinality

 

Minimum and maximum number of entity occurrences associated with related entities

Through cardinality, constraints can be enforced (code should be written)

For example, course will not offered unless more than 10 students are enrolled; professor is not allowed to teach more than 4 courses

 

e.g. (1,4) at professor: professor teaches at least one class and no more than 4 courses

(1,1) at class: class is taught by one and only professor

 

 

Chen model distinguish cardinality while Crow’s foot model doesn’t

 

Q. There is a research professor who does not teach class, then the minimum cardinality of professor being associated with class would be ?

 

Q. how is cardinality determined?

 

2-3. Strength

 

Is the relationship strong or weak?

 

Existence dependence: entity’s existence depends on the existence of one or more other entities (e.g. dependent is existence-dependent on employee)

 

Existence independence: entity can exist apart from one or more related entities

 

 

 

Weak (non-identifying) relationships: one entity is not existence-independent on another entity, and PK is not derived from related entities

 

Strong (identifying) relationships: one entity is existence-dependent, and PK is derived from related entities (CRS_CODE is used as PK in both tables at Figure 4.10)

 

Crow’s foot model distinguishes weak/strong relationship (dashed line)

Chen model does not denote but we can tell it from PKs

 

2-4. Participation

 

Is the relationship mandatory or optional?

 

Relationship is optional if the minimum cardinality is 0 (e.g. the relationship between research professor and class is optional); determined by business rule

 

Relationship is mandatory if one entity occurrence requires a corresponding entity occurrence

 

 

Please note notation small circle beside Class to denote optional relationship

Circle is placed next to optional side (e.g. class is optional to professor, but professor is mandatory to class: class is taught by one professor, no class without professor)

 

2-5. Degree

 

How many entities are associated?

 

Unary (recursive), binary, ternary, fourth….

 

 

 

3. Entity

 

3-1. Weak entity

 

An entity can be described as weak if the following two conditions are met

(1)   existence-dependent

(2)   PK is derived from related entities

 

 

 

3-2. Composite (bridge) entity

 

Dealing with M:N relationship

Composite entity is composed of PKs of entities to be connected

e.g. student, class à student, enroll, class

denoted by rectangle/diamond in Chen model

 

 

3-3. Supertype/subtype entity

 

Generalization hierarchy

Supertype contains generic attributes while subtype contains unique attributes

 

 

Disjoint subtypes is denoted by (G); overlapping subtypes is denoted by (Gs)

 

Lab

 

Here’s the scenario and business rules on video rentals

 

 

Customer can rent many tapes

A can be rented by many customers

Some customers do not rent tapes

Some tapes are never rented

 

 

  1. Identify entities and relationships in this scenario
  2. What’s the relationship connectivity between entities?
  3. Is the relationship optional or mandatory? (relationship participation)
  4. Draw ERD without attributes (make sure connectivity/participation is denoted whenever appropriate)
  5. Transform two-entities ERD to three-entities ERD using the concept of composite entities (or bridge entities); now what’s the relationship connectivity?
  6. Denote relationship cardinalities in the Chen model

 

Open Ch04_Rental.mdb at P:\geog495aut05\

 

  1. This time, include attributes in Crow’s foot model; also identify PK and FK for each table
  2. What are degrees of relationships?
  3. Are relationships strong or week?