Professor Nancy C' Shaw

1
Data Modeling

• Professor Nancy C. Shaw

2
Agenda

• Data Modeling Concepts
• The ER Diagram

3
Basic Modeling Concepts

• Database design is both art and science.
• A data model is the relatively simple
  representation, usually graphic, of complex
  real-world data structures.
• Used as communications tools to facilitate the
  interaction among the designer, the applications
  programmer, and the end user.

4
The Entity Relationship (E-R) Model

• E-R model is commonly used to
• Translate different views of data among managers,
  users, and programmers to fit into a common
  framework.
• Define data processing and constraint
  requirements to help us meet the different views.
• Eventually used to implement the database.

5
The Entity Relationship (E-R) Model

• E-R Model Components
• Entities
• An entity is represented by a rectangle
  containing the entitys name.
• Attributes
• Attributes are represented by ovals and are
  connected to the entity with a line.
• Each oval contains the name of the attribute it
  represents.
• Primary keys are underlined.
• Relationships

6
The Attributes of the STUDENT Entity
7
Relationships

• Relationships
• A relationship is an association between
  entities.
• Relationships are represented by diamond-shaped
  symbols.

8
Relationships within the Relational Database

• One to One
• 1 1
• One to Many
• 1 M
• Many to Many
• M N

9
Relationships within the Relational Database

• E-R Diagram (ERD) Rules
• Rectangles are used to represent entities.
• Entity names are nouns and capitalized.
• Diamonds are used to represent the
  relationship(s) between the entities.
• The number 1 is used to represent the 1 side of
  the relationship.
• The letter M is used to represent the many
  sides of the relationship.

10
Relationships within the Relational Database

• One to One
• Caution ! May indicate improper entity
  definition
• May be necessary in certain cases

1
1
School
Dean
11
Relationships within the Relational Database

• One to Many
• Most common
• Read it both ways to make sure it works !

12
Put Primary Key of the One as Foreign Key into
the Many
13
Relationships within the Relational Database

• Many to Many
• Cannot be implemented in this form
• Must be corrected
• Ok for early logical design, not for physical
  design !

14
Sample Student Enrollment Data
15
Changing the MN Relationship to Two 1M
Relationships
16
The bridge entity contains the keys of both, and
sometimes other attributes
17
The Expanded Entity Relationship Model
18
The Relational Schema for the ERD
19
Relationships

• A relationship is described by
• Degree of the relationship
• Connectivity of the relationship
• Cardinality of the relationship
• Participation

20
Relationships

• A relationships degree indicates the number of
  associated entities or participants.
• A unary relationship is a recursive relationship
  (one instance of an entity is related to another
  instance of the same entity)
• A binary relationship exists when two entities
  are associated.
• A ternary relationship exists when three entities
  are associated.

21
Relationships
22
Relationships

• Connectivity
• The term connectivity is used to describe the
  relationship classification (e.g., one-to-one,
  one-to-many, and many-to-many).

23
Relationships

• Cardinality
• Cardinality expresses the specific number of
  entity occurrences associated with one occurrence
  of the related entity.
• Correct specification of cardinality constraints
  depends on detailed knowledge of business rules.

24
Relationships
25
Relationships

• Relationship Participation
• The participation is partial (optional) if one
  entity instance does not require a corresponding
  entity instance of the other entity participating
  in the relationship.
• An optional entity is shown by a small circle on
  the side of the optional entity.

26
Want to Learn More?

• Take Database Design
• MIS 310