CHAPTER 2 MODELING DATA IN THE ORGANIZATION Modern

  • Slides: 28
Download presentation
CHAPTER 2: MODELING DATA IN THE ORGANIZATION Modern Database Management 12 th Edition Jeff

CHAPTER 2: MODELING DATA IN THE ORGANIZATION Modern Database Management 12 th Edition Jeff Hoffer, Ramesh Venkataraman, Heikki Topi Copyright © 2016 Pearson Education, Inc.

E-R MODEL CONSTRUCTS Entity instance–person, place, object, event, concept (often corresponds to a row

E-R MODEL CONSTRUCTS Entity instance–person, place, object, event, concept (often corresponds to a row in a table) Entity Type–collection of entity instance (often corresponds to a table) Relationship instance–link between entity instance (corresponds to individual primary key-foreign key value match) Relationship link between entity types (primary key-foreign key link) Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -22

Basic E-R notation (Figure 2 -2) Entity symbols Attribute symbols A special entity that

Basic E-R notation (Figure 2 -2) Entity symbols Attribute symbols A special entity that is also a relationship Relationship symbols Relationship degrees specify number of entity types involved Chapter 2 Relationship cardinalities specify how many of each entity type is allowed Copyright © 2016 Pearson Education, Inc. 2 -3

ATTRIBUTES � Attribute–property or characteristic of an entity or relationship type � Classifications of

ATTRIBUTES � Attribute–property or characteristic of an entity or relationship type � Classifications of attributes: �Simple versus Composite Attribute �Single-Valued versus Multivalued Attribute �Stored versus Derived Attributes �Identifier Attributes Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -44

SIMPLE VS. COMPOSITE ATTRIBUTES � Composite attribute – An attribute that has meaningful component

SIMPLE VS. COMPOSITE ATTRIBUTES � Composite attribute – An attribute that has meaningful component parts (attributes) The address is broken into component parts Figure 2 -7 A composite attribute Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -5

MULTI-VALUED AND DERIVED ATTRIBUTES Multivalued – may take on more than one value for

MULTI-VALUED AND DERIVED ATTRIBUTES Multivalued – may take on more than one value for a given entity (or relationship) instance Derived – values can be calculated from related attribute values (not physically stored in the database) Figure 2 -8 Entity with multivalued attribute (Skill) and derived attribute (Years Employed) Multivalued an employee can have more than one skill Chapter 2 Derived Calculated from date employed and current date Copyright © 2016 Pearson Education, Inc. 2 -6

IDENTIFIERS (KEYS) � Identifier (Key)–an attribute (or combination of attributes) that uniquely identifies individual

IDENTIFIERS (KEYS) � Identifier (Key)–an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type � Simple versus Composite Identifier Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -77

Figure 2 -9 Simple and composite identifier attributes The identifier is boldfaced and underlined

Figure 2 -9 Simple and composite identifier attributes The identifier is boldfaced and underlined Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -8

STRONG VS. WEAK ENTITIES, AND IDENTIFYING RELATIONSHIPS � Strong entity � exists independently of

STRONG VS. WEAK ENTITIES, AND IDENTIFYING RELATIONSHIPS � Strong entity � exists independently of other types of entities � has its own unique identifier � � identifier underlined with single line Weak entity � dependent on a strong entity (identifying owner)…cannot exist on its own � does not have a unique identifier (only a partial identifier) � entity � box and partial identifier have double lines Identifying relationship � links Chapter 2 strong entities to weak entities Copyright © 2016 Pearson Education, Inc. 2 -9

Figure 2 -5 Example of a weak identity and its identifying relationship Strong entity

Figure 2 -5 Example of a weak identity and its identifying relationship Strong entity Chapter 2 Weak entity Copyright © 2016 Pearson Education, Inc. 2 -10

MODELING RELATIONSHIPS � Relationship Types vs. Relationship Instances �The relationship type is modeled as

MODELING RELATIONSHIPS � Relationship Types vs. Relationship Instances �The relationship type is modeled as lines between entity types…the instance is between specific entity instances � Two entities can have more than one type of relationship between them (multiple relationships) Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -11 11

Figure 2 -10 Relationship types and instances a) Relationship type (Completes) b) Relationship instances

Figure 2 -10 Relationship types and instances a) Relationship type (Completes) b) Relationship instances Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -12

DEGREE OF RELATIONSHIPS �Degree of a relationship is the number of entity types that

DEGREE OF RELATIONSHIPS �Degree of a relationship is the number of entity types that participate in it �Unary Relationship �Binary Relationship �Ternary Relationship Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -13

Degree of relationships – from Figure 2 -2 One entity related to another of

Degree of relationships – from Figure 2 -2 One entity related to another of the same entity type Chapter 2 Entities of two different types related to each other Copyright © 2016 Pearson Education, Inc. Entities of three different types related to each other 2 -14

CARDINALITY OF RELATIONSHIPS � One-to-One: Each entity instance in the relationship will have exactly

CARDINALITY OF RELATIONSHIPS � One-to-One: Each entity instance in the relationship will have exactly one related entity instance � One-to-Many: An entity instance on one side of the relationship can have many related entity instances, but an entity instance on the other side will have a maximum of one related entity instance. � Many-to-Many: An entity instance on either side of the relationship can have many related entity instances on the other side Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -15 15

Figure 2 -12 Examples of relationships of different degrees a) Unary relationships Chapter 2

Figure 2 -12 Examples of relationships of different degrees a) Unary relationships Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -16

Figure 2 -12 Examples of relationships of different degrees (cont. ) b) Binary relationships

Figure 2 -12 Examples of relationships of different degrees (cont. ) b) Binary relationships Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -17

CARDINALITY CONSTRAINTS � Cardinality Constraints—the number of instances of one entity that can or

CARDINALITY CONSTRAINTS � Cardinality Constraints—the number of instances of one entity that can or must be associated with each instance of another entity � Minimum Cardinality �If zero, then optional �If one or more, then mandatory � Maximum �The Chapter 2 Cardinality maximum number Copyright © 2016 Pearson Education, Inc. 2 -18

Figure 2 -17 Examples of cardinality constraints a) Mandatory cardinalities A patient history is

Figure 2 -17 Examples of cardinality constraints a) Mandatory cardinalities A patient history is recorded for one and only one patient Chapter 2 A patient must have recorded at least one history, and can have many Copyright © 2016 Pearson Education, Inc. 2 -19

Figure 2 -17 Examples of cardinality constraints (cont. ) b) One optional, one mandatory

Figure 2 -17 Examples of cardinality constraints (cont. ) b) One optional, one mandatory A project must be assigned to at least one employee, and may be assigned to many Chapter 2 An employee can be assigned to any number of projects, or may not be assigned to any at all Copyright © 2016 Pearson Education, Inc. 2 -20

Figure 2 -17 Examples of cardinality constraints (cont. ) c) Optional cardinalities A person

Figure 2 -17 Examples of cardinality constraints (cont. ) c) Optional cardinalities A person is married to at most one other person, or may not be married at all Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -21

ASSOCIATIVE ENTITIES An entity–has attributes A relationship–links entities together Chapter 2 Copyright © 2016

ASSOCIATIVE ENTITIES An entity–has attributes A relationship–links entities together Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -22 22

Figure 2 -11 a A binary relationship with an attribute Here, the date completed

Figure 2 -11 a A binary relationship with an attribute Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationship. Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -23

Figure 2 -11 b An associative entity (CERTIFICATE) Associative entity is like a relationship

Figure 2 -11 b An associative entity (CERTIFICATE) Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right. Note that the many-to-many cardinality between entities in Figure 2 -11 a has been replaced by two one-to-many relationships with the associative entity. Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -24

Figure 2 -12 Examples of relationships of different degrees (cont. ) c) Ternary relationship

Figure 2 -12 Examples of relationships of different degrees (cont. ) c) Ternary relationship Note: a relationship can have attributes of its own Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -25

Figure 2 -18 Cardinality constraints in a ternary relationship Chapter 2 Copyright © 2016

Figure 2 -18 Cardinality constraints in a ternary relationship Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -26

Figure 2 -22 Data model for Pine Valley Furniture Company in Microsoft Visio notation

Figure 2 -22 Data model for Pine Valley Furniture Company in Microsoft Visio notation Different modeling software tools may have different notation for the same constructs. Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -27

Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -28

Chapter 2 Copyright © 2016 Pearson Education, Inc. 2 -28

CHAPTER 2 MODELING DATA IN THE ORGANIZATION ModernCHAPTER 2 MODELING DATA IN THE ORGANIZATION Modern
Chapter 3 Modeling Data in the Organization ModernChapter 3 Modeling Data in the Organization Modern
CHAPTER 2 MODELING DATA IN THE ORGANIZATION ModernCHAPTER 2 MODELING DATA IN THE ORGANIZATION Modern
CHAPTER 2 MODELING DATA IN THE ORGANIZATION ModernCHAPTER 2 MODELING DATA IN THE ORGANIZATION Modern
Chapter 3 Modeling Data in the Organization ModernChapter 3 Modeling Data in the Organization Modern
CHAPTER 2 MODELING DATA IN THE ORGANIZATION ModernCHAPTER 2 MODELING DATA IN THE ORGANIZATION Modern
Chapter 3 Modeling Data in the Organization ModernChapter 3 Modeling Data in the Organization Modern
Chapter 3 Modeling Data in the Organization ModernChapter 3 Modeling Data in the Organization Modern
Chapter 3 Modeling Data in the Organization ModernChapter 3 Modeling Data in the Organization Modern
CHAPTER 3 MODELING DATA IN THE ORGANIZATION ModernCHAPTER 3 MODELING DATA IN THE ORGANIZATION Modern
CHAPTER 2 MODELING DATA IN THE ORGANIZATION ModernCHAPTER 2 MODELING DATA IN THE ORGANIZATION Modern
Chapter 3 Modeling Data in the Organization ModernChapter 3 Modeling Data in the Organization Modern
Chapter 2 Modeling Data in the Organization ModernChapter 2 Modeling Data in the Organization Modern
Chapter 2 Modeling Data in the Organization ModernChapter 2 Modeling Data in the Organization Modern
Chapter 3 Project Organization Organization Designs Organization isChapter 3 Project Organization Organization Designs Organization is
CHAPTER 2 MODELING DATA IN THE ORGANIZATION DATACHAPTER 2 MODELING DATA IN THE ORGANIZATION DATA
Ancient vs Modern Olympics Modern Olympics The ModernAncient vs Modern Olympics Modern Olympics The Modern
MODERN HISTORY DANCE MODERN DANCE Modern dance emergedMODERN HISTORY DANCE MODERN DANCE Modern dance emerged
xindi modern times modern age modern era Peoplexindi modern times modern age modern era People
Modern Smart Materials Modern Material A MODERN MATERIALModern Smart Materials Modern Material A MODERN MATERIAL
Modern Humanities An Overview Modern Entertainment Modern ArchitectureModern Humanities An Overview Modern Entertainment Modern Architecture
Modern Fantasy Definition of Modern Fantasy Modern fantasyModern Fantasy Definition of Modern Fantasy Modern fantasy
Modern and Post Modern Poetry Characteristics of ModernModern and Post Modern Poetry Characteristics of Modern
Modern Data Acquisition Systems Reinoud Sleeman Modern dataModern Data Acquisition Systems Reinoud Sleeman Modern data