Chapter 2 Data Modeling Fundamentals of Database Management

Chapter 2 Data Modeling Fundamentals of Database Management Systems, 2 nd ed by Mark L. Gillenson, Ph. D. University of Memphis John Wiley & Sons, Inc.

Chapter Objectives ¨ Explain the concept and practical use of data modeling. ¨ Recognize which relationships in the business environment are unary, binary, and ternary relationships. ¨ Describe one-to-one, one-to-many, and many-tomany unary, binary, and ternary relationships. 2 -2

Chapter Objectives ¨ Recognize and describe intersection data. ¨ Model data in business environments by drawing entity-relationship diagrams that involve unary, binary, and ternary relationships. 2 -3

Essence of Data Modeling ¨ Exploring the different ways that entities can relate to each other as they always do in the real world ¨ Devising a way of recording, of diagramming, the entities and the ways in which they interrelate in the business environment 2 -4

Entity-Relationship (E-R) Model ¨ A diagramming technique ¨ Diagrams entities (with attributes) and the relationship between the entities. ¨ There are many variations of E-R diagrams in use. 2 -5

E-R Model Entity (and its attributes) ¨ Rectangular shape ¨ Salesperson = a type of entity ¨ Name of entity is in caps above the separator line. 2 -6

E-R Model Entity (and its attributes) ¨ Entity type’s attributes are shown below the separator line. ¨ PK and boldface denote the attribute(s) that constitute the entity type’s unique identifier. 2 -7

Relationships ¨ Associations between entities ¨ Different kinds: ª Binary relationships ª Unary relationships ª Ternary relationships 2 -8

Binary Relationships Simplest kind of relationship ¨ Relationship between two entity types ¨ A salesperson “sells” products or products are “sold” by salespersons ¨ 2 -9

Cardinality ¨ Represents the maximum number of entities that can be involved in a particular relationship. ª One-to-One Binary Relationship ª One-to-Many Binary Relationship ª Many-to-Many Binary Relationship 2 -10

One-to-One Binary Relationship ¨ 1 -1 ¨ A single occurrence of one entity type can be associated with a single occurrence of the other entity type and vice versa. 2 -11

One-to-Many Binary Relationship ¨ 1 -M ¨ Use “crow’s foot” to represent the multiple association. ¨ “many” = the maximum number of occurrences that can be involved, means a number that can be 1, 2, 3, . . . n. 2 -12

Many-to-Many Binary Relationship ¨ M-M ¨ “many” can be either an exact number or have a known maximum. 2 -13

Cardinality 2 -14

Modality ¨ The minimum number of entity occurrences that can be involved in a relationship. ¨ “inner” symbol on E-R diagram (“outer” symbol is cardinality) 2 -15

Cardinality & Modality 2 -16

Intersection Data ¨ Describes the relationship between two entities. ¨ Used with many-to-many relationships. ¨ Represented on E-R diagram as an “associative entity” 2 -17

Many-to-Many Binary Relationship with Intersection Data ¨ For example, we know not only that salesperson 137 sold some of product 24013 but also how many units of that product that salesperson sold. 2 -18

Associative Entity ¨ Entities can have attributes; many-to-many relationships can have attributes. ¨ Many-to-many relationship may be treated similarly to entities in an E-R diagram. 2 -19

Associative Entity ¨ The unique identifier of the associative entity is usually the combination of the unique identifiers of the two entities in the many-to-many relationship. 2 -20

Unary Relationships ¨ Associate occurrences of an entity type with other occurrences of the same entity type. ¨ Cardinality: ª One-to-One Unary Relationship ª One-to-Many Unary Relationship ª Many-to-Many Unary Relationship 2 -21

¨ Unary Relationships 2 -22

Ternary Relationship ¨ Involves three different entity types. 2 -23

¨ The General Hardware Company E-R Diagram ¨ Customer Employee is a dependent entity. 2 -24

Good Reading Bookstores 2 -25

World Music Association 2 -26

Lucky Rent-A-Car 2 -27

“Copyright 2004 John Wiley & Sons, Inc. All rights reserved. Reproduction or translation of this work beyond that permitted in Section 117 of the 1976 United States Copyright Act without express permission of the copyright owner is unlawful. Request for further information should be addressed to the Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up copies for his/her own use only and not for distribution or resale. The Publisher assumes no responsibility for errors, omissions, or damages caused by the use of these programs or from the use of the information contained herein. ” 2 -28