WIXLIB

11eDatabase SystemsDesign, Implementation, and ManagementCoronel MorrisChapter 5Advanced Data Modeling 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Learning Objectives In this chapter, you will learn: About the extended entity relationship (EER) model How entity clusters are used to represent multipleentities and relationships The characteristics of good primary keys and how toselect them How to use flexible solutions for special data-modelingcases 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.2

Extended Entity Relationship Model (EERM) Result of adding more semantic constructs to theoriginal entity relationship (ER) model EER diagram (EERD): Uses the EER model 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.3

Entity Supertypes and Subtypes Entity supertype: Generic entity type related to oneor more entity subtypes Contains common characteristics Entity subtype: Contains unique characteristics ofeach entity subtype Criteria to determine the usage There must be different, identifiable kinds of the entityin the user’s environment The different kinds of instances should each have oneor more attributes that are unique to that kind ofinstance 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.4

Specialization Hierarchy Depicts arrangement of higher-level entity supertypesand lower-level entity subtypes Relationships are described in terms of “is-a”relationships Subtype exists within the context of a supertype Every subtype has one supertype to which it isdirectly related Supertype can have many subtypes 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.5

Specialization Hierarchy Provides the means to: Support attribute inheritance Define a special supertype attribute known as thesubtype discriminator Define disjoint/overlapping constraints andcomplete/partial constraints 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.6

Figure 5.2 - Specialization Hierarchy 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.7

Inheritance Enables an entity subtype to inherit attributes andrelationships of the supertype All entity subtypes inherit their primary key attributefrom their supertype At the implementation level, supertype and itssubtype(s) maintain a 1:1 relationship Entity subtypes inherit all relationships in whichsupertype entity participates Lower-level subtypes inherit all attributes andrelationships from its upper-level supertypes 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.8

Subtype Discriminator Attribute in the supertype entity that determines towhich entity subtype the supertype occurrence isrelated Default comparison condition is the equalitycomparison 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.9

Disjoint and Overlapping Constraints Disjoint subtypes: Contain a unique subset of thesupertype entity set Known as nonoverlapping subtypes Implementation is based on the value of the subtypediscriminator attribute in the supertype Overlapping subtypes: Contain nonunique subsetsof the supertype entity set Implementation requires the use of one discriminatorattribute for each subtype 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.10

Figure 5.4 - Specialization Hierarchy withOverlapping Subtypes 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.11

Table 5.1 - Discriminator Attributes withOverlapping Subtypes 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.12

Completeness Constraint Specifies whether each supertype occurrence mustalso be a member of at least one subtype Types Partial completeness: Not every supertype occurrenceis a member of a subtype Total completeness: Every supertype occurrence mustbe a member of any 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.13

Table 5.2 - Specialization Hierarchy ConstraintScenarios 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.14

Specialization and GeneralizationSpecializationGeneralization Top-down process Identifies lower-level, morespecific entity subtypes from ahigher-level entity supertype Based on grouping uniquecharacteristics andrelationships of the subtypes Bottom-up process Identifies a higher-level, moregeneric entity supertype fromlower-level entity subtypes Based on grouping commoncharacteristics andrelationships of the subtypes 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.15

Entity Cluster Virtual entity type used to represent multiple entitiesand relationships in ERD Avoid the display of attributes to eliminatecomplications that result when the inheritance ruleschange 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.16

Figure 5.5 - Tiny College ERD Using EntityClusters 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.17

Primary Keys Single attribute or a combination of attributes, whichuniquely identifies each entity instance Guarantees entity integrity Works with foreign keys to implement relationships 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.18

Natural Keys or Natural Identifier Real-world identifier used to uniquely identify realworld objects Familiar to end users and forms part of their day-to-daybusiness vocabulary Also known as natural identifier Used as the primary key of the entity being modeled 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.19

Desirable Primary Key CharacteristicsNon intelligentNo change over timePreferably single-attributePreferably numericSecurity-compliant 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.20

Use of Composite Primary Keys Identifiers of composite entities Each primary key combination is allowed once in M:Nrelationship Identifiers of weak entities Weak entity has a strong identifying relationship withthe parent entity 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.21

Use of Composite Primary Keys When used as identifiers of weak entities, represent areal-world object that is: Existence-dependent on another real-world object Represented in the data model as two separate entitiesin a strong identifying relationship 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.22

Figure 5.6 - The M:N Relationship betweenSTUDENT and CLASS 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.23

Surrogate Keys Primary key used to simplify the identification ofentity instances are useful when: There is no natural key Selected candidate key has embedded semanticcontents or is too long Require ensuring that the candidate key of entity inquestion performs properly Use unique index and not null constraints 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.24

Table 5.4 - Data Used to Keep Track of Events 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.25

Design Case 1: Implementing 1:1Relationships Foreign keys work with primary keys to properlyimplement relationships in relational model Rule Put primary key of the parent entity on the dependententity as foreign key Options for selecting and placing the foreign key: Place a foreign key in both entities Place a foreign key in one of the entities 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.26

Table 5.5 - Selection of Foreign Key in a 1:1Relationship 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.27

Figure 5.7 - The 1:1 Relationship betweenDepartment and Employee 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.28

Design Case 2: Maintaining History ofTime-Variant Data Time-variant data: Data whose values change overtime and for which a history of the data changesmust be retained Requires creating a new entity in a 1:M relationshipwith the original entity New entity contains the new value, date of the change,and other pertinent attribute 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.29

Figure 5.8 - Maintaining Salary History 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.30

Figure 5.9 - Maintaining Manager History 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.31

Figure 5.10 - Maintaining Job History 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.32

Design Case 3: Fan Traps Design trap: Occurs when a relationship isimproperly or incompletely identified Represented in a way not consistent with the real world Fan trap: Occurs when one entity is in two 1:Mrelationships to other entities Produces an association among other entities notexpressed in the model 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.33

Figure 5.11 - Incorrect ERD with Fan TrapProblem 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.34

Figure 5.12 - Corrected ERD After Removal ofthe Fan Trap 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.35