chapter chapter 33:: modeling data in the organizationis.cba.edu.kw/433/handouts/ch03.pdf ·...
TRANSCRIPT
Chapter Chapter 33::Modeling Data in the Modeling Data in the
OrganizationOrganizationOrganizationOrganization
Modern Database ManagementModern Database Managementgg99thth EditionEdition
Jeffrey A. Jeffrey A. HofferHoffer, Mary B. Prescott, , Mary B. Prescott, yy , y ,, y ,HeikkiHeikki TopiTopi
© © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 11
ObjectivesObjectivesObjectivesObjectivesD fi iti f tD fi iti f tDefinition of termsDefinition of termsImportance of data modelingImportance of data modelingWrite good names and definitions for entitiesWrite good names and definitions for entitiesWrite good names and definitions for entities, Write good names and definitions for entities, relationships, and attributesrelationships, and attributesDistinguish unary, binary, and ternary relationshipsDistinguish unary, binary, and ternary relationshipsDistinguish unary, binary, and ternary relationshipsDistinguish unary, binary, and ternary relationshipsModel different types of attributes, entities, relationships, Model different types of attributes, entities, relationships, and cardinalitiesand cardinalitiesDraw EDraw E--R diagrams for common business situationsR diagrams for common business situationsConvert manyConvert many--toto--many relationships to associative many relationships to associative entitieentitieentitiesentitiesModel timeModel time--dependent data using time stampsdependent data using time stamps
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 22
Business RulesBusiness Rules
Statements that define or constrain some Statements that define or constrain some aspect of the businessaspect of the businessAssert business structureAssert business structureAssert business structureAssert business structureControl/influence business behaviorControl/influence business behaviorExpressed in terms familiar to end usersExpressed in terms familiar to end usersAutomated through DBMS softwareAutomated through DBMS softwareAutomated through DBMS softwareAutomated through DBMS software
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 33
A Good Business Rule Is:A Good Business Rule Is:
DeclarativeDeclarative––what, not howwhat, not howP iP i l dl d iiPrecisePrecise––clear, agreedclear, agreed--upon meaningupon meaningAtomicAtomic––one statementone statementConsistentConsistent––internally and externallyinternally and externallyExpressibleExpressible––structured, natural languagestructured, natural languagep ess b ep ess b e st uctu ed, atu a a guagest uctu ed, atu a a guageDistinctDistinct––nonnon--redundantredundantBusinessBusiness--orientedoriented––understood by businessunderstood by businessBusinessBusiness--orientedoriented––understood by business understood by business peoplepeople
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 44
A Good Data Name Is:A Good Data Name Is:
Related to business, not technical, Related to business, not technical, characteristicscharacteristicsMeaningful and selfMeaningful and self--documentingdocumentingU iU iUniqueUniqueReadableReadableReadableReadableComposed of words from an approved listComposed of words from an approved listRepeatableRepeatableFollows standard syntaxFollows standard syntax
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 55
Follows standard syntaxFollows standard syntax
Data DefinitionsData Definitions
Explanation of a term or factExplanation of a term or factTermTerm––word or phrase with specific meaningword or phrase with specific meaningTermTerm word or phrase with specific meaningword or phrase with specific meaningFactFact––association between two or more termsassociation between two or more terms
Guidelines for good data definitionGuidelines for good data definitionGuidelines for good data definitionGuidelines for good data definitionGathered in conjunction with systems requirementsGathered in conjunction with systems requirementsAccompanied by diagramsAccompanied by diagramsAccompanied by diagramsAccompanied by diagramsConcise description of essential data meaningConcise description of essential data meaningAchieved by consensus and iteratively refinedAchieved by consensus and iteratively refinedAchieved by consensus, and iteratively refinedAchieved by consensus, and iteratively refined
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 66
EE--R Model ConstructsR Model ConstructsEE R Model ConstructsR Model ConstructsEntities:Entities:
Entity instanceEntity instance––person, place, object, event, concept (often person, place, object, event, concept (often corresponds to a row in a table)corresponds to a row in a table)Entity TypeEntity Type––collection of entities (often corresponds to a table)collection of entities (often corresponds to a table)y ypy yp ( p )( p )
Relationships:Relationships:Relationship instanceRelationship instance––link between entities (corresponds to primary link between entities (corresponds to primary keykey--foreign key equivalencies in related tables)foreign key equivalencies in related tables)Relationship typeRelationship type––category of relationship…link between entity category of relationship…link between entity p ypp yp g y p yg y p ytypestypes
AttributeAttribute––property or characteristic of an entity or relationship typeproperty or characteristic of an entity or relationship typeAttributeAttribute––property or characteristic of an entity or relationship type property or characteristic of an entity or relationship type (often corresponds to a field in a table)(often corresponds to a field in a table)
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 77
Sample E-R Diagram (Figure 3-1)
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 88
Basic E-R notation (Figure 3-2)
Entity symbols
Attribute symbols symbols
A special entity that is also a relationship
Relationship symbols
Relationship degrees specify
p
g p ynumber of entity types involved
Relationship cardinalitiescardinalities specify how many of each entity type is
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 99
entity type is allowed
What Should an Entity Be?What Should an Entity Be?What Should an Entity Be?What Should an Entity Be?SHOULD BE:SHOULD BE:SHOULD BE:SHOULD BE:
An object that will have many instances in An object that will have many instances in the databasethe databasethe databasethe databaseAn object that will be composed of multiple An object that will be composed of multiple tt ib ttt ib tattributesattributes
An object that we are trying to modelAn object that we are trying to model
SHOULD NOT BE:SHOULD NOT BE:A user of the database systemA user of the database systemA user of the database system A user of the database system An output of the database system (e.g., a An output of the database system (e.g., a report)report)
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1010
report)report)
Figure 3-4 Example of inappropriate entities
System System System System t tt tInappropriate
entitiesuseruser outputoutput
Appropriate entities
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1111
AttributesAttributesA ibA ib h i i fh i i fAttributeAttribute––property or characteristic of an property or characteristic of an entity or relationship typeentity or relationship typeClassifications of attributes:Classifications of attributes:
Required versus Optional AttributesRequired versus Optional AttributesRequired versus Optional AttributesRequired versus Optional AttributesSimple versus Composite AttributeSimple versus Composite AttributeSingleSingle--Valued versus Multivalued AttributeValued versus Multivalued AttributeSingleSingle--Valued versus Multivalued AttributeValued versus Multivalued AttributeStored versus Derived AttributesStored versus Derived AttributesIdentifier AttributesIdentifier AttributesIdentifier AttributesIdentifier Attributes
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1212
Identifiers (Keys)Identifiers (Keys)
Identifier (Key)Identifier (Key)––an attribute (or an attribute (or bi ti f tt ib t ) th t i lbi ti f tt ib t ) th t i lcombination of attributes) that uniquely combination of attributes) that uniquely
identifies individual instances of an entity identifies individual instances of an entity tttypetypeSimple versus Composite IdentifierSimple versus Composite Identifierp pp pCandidate IdentifierCandidate Identifier––an attribute that an attribute that could be a key satisfies the requirementscould be a key satisfies the requirementscould be a key…satisfies the requirements could be a key…satisfies the requirements for being an identifierfor being an identifier
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1313
Characteristics of IdentifiersCharacteristics of Identifiers
Will not change in valueWill not change in valueWill not be nullWill not be nullNo intelligent identifiers (e g containingNo intelligent identifiers (e g containingNo intelligent identifiers (e.g., containing No intelligent identifiers (e.g., containing locations or people that might change)locations or people that might change)Substitute new, simple keys for long, Substitute new, simple keys for long, composite keyscomposite keyscomposite keyscomposite keys
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1414
Figure 3-7 A composite attribute
An attribute b k i tbroken into component parts
Figure 3-8 Entity with multivalued attribute (Skill) d d i d ib (Y E l d)and derived attribute (Years_Employed)
M lti l dMultivaluedan employee can have more than one skill
Derivedfrom date employed and
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1515
employed and current date
Figure 3-9 Simple and composite identifier attributesg p p
The identifier is boldfaced and underlined
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1616
Figure 3-19 Simple example of time-stamping
This attribute is bothboth multivalued andcomposite
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1717
More on RelationshipsMore on RelationshipsMore on RelationshipsMore on RelationshipsRelationship Types vs. Relationship InstancesRelationship Types vs. Relationship Instancesp yp pp yp p
The relationship type is modeled as lines between The relationship type is modeled as lines between entity types…the instance is between specific entity types…the instance is between specific entity instancesentity instancesentity instancesentity instances
Relationships can have attributesRelationships can have attributesThese describe features pertaining to the associationThese describe features pertaining to the associationThese describe features pertaining to the association These describe features pertaining to the association between the entities in the relationshipbetween the entities in the relationship
Two entities can have more than one type of Two entities can have more than one type of ypyprelationship between them (multiple relationship between them (multiple relationships)relationships)Associative EntityAssociative Entity––combination of relationship combination of relationship and entityand entity
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1818
Figure 3-10 Relationship types and instancesFigure 3-10 Relationship types and instances
a) Relationship typea) Relationship type
b) Relationship instancesinstances
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 1919
Degree of RelationshipsDegree of Relationships
Degree of a relationship is the Degree of a relationship is the g pg pnumber of entity types that number of entity types that participate in itparticipate in itparticipate in itparticipate in it
Unary RelationshipUnary RelationshipUnary RelationshipUnary RelationshipBinary RelationshipBinary Relationshipy py pTernary RelationshipTernary Relationship
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2020
Degree of relationships – from Figure 3-2
Entities of O tit two different
types related t h th Entities of three
One entity related to another of to each other Entities of three
different types related to each
another of the same entity type
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2121
other
Cardinality of RelationshipsCardinality of Relationships
OneOne--toto--OneOneE h tit i th l ti hi ill h tlE h tit i th l ti hi ill h tlEach entity in the relationship will have exactly one Each entity in the relationship will have exactly one related entityrelated entity
OneOne--toto--ManyManyOneOne--toto--ManyManyAn entity on one side of the relationship can have An entity on one side of the relationship can have many related entities, but an entity on the other side many related entities, but an entity on the other side y , yy , ywill have a maximum of one related entitywill have a maximum of one related entity
ManyMany--toto--ManyManyEntities on both sides of the relationship can have Entities on both sides of the relationship can have many related entities on the other sidemany related entities on the other side
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2222
Cardinality ConstraintsCardinality ConstraintsCardinality ConstraintsCardinality Constraints——the number of the number of instances of one entity that can or must beinstances of one entity that can or must beinstances of one entity that can or must be instances of one entity that can or must be associated with each instance of another associated with each instance of another entityentityentityentityMinimum CardinalityMinimum Cardinality
If e o then option lIf e o then option lIf zero, then optionalIf zero, then optionalIf one or more, then mandatoryIf one or more, then mandatory
Maximum CardinalityMaximum CardinalityThe maximum numberThe maximum number
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2323
Figure 3-12 Examples of relationships of different degrees
a) Unary relationships
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2424
Figure 3-12 Examples of relationships of different degrees (cont.)
b) Binary relationships
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2525
Figure 3-12 Examples of relationships of different degrees (cont.)
c) Ternary relationship
Note: a relationship can have attributes of its ownChapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2626
Note: a relationship can have attributes of its own
Figure 3-17 Examples of cardinality constraints
a) Mandatory cardinalities
A patient must have recorded A patient history is e o ded fo one nd at least one history, and can
have manyrecorded for one and only one patient
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2727
Figure 3-17 Examples of cardinality constraints (cont.)
b) One optional, one mandatory
An employee can be assigned to any number of projects or
A project must be igned to t le t one to any number of projects, or
may not be assigned to any at all
assigned to at least one employee, and may be assigned to many
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2828
Figure 3-17 Examples of cardinality constraints (cont.)
c) Optional cardinalities
A person is married to at most one other person,
bor may not be married at all
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 2929
Figure 3-21 Examples of multiple relationships
a) Employees and departments
Entities can be related to one another in more than one way
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3030
Figure 3-21 Examples of multiple relationships (cont.)
b) Professors and courses (fixed lower limit constraint)
Here, min cardinality constraint is 2
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3131
Figure 3-15a and 3-15b Multivalued attributes can be represented as relationships
simple
composite
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3232
Strong vs. Weak Entities, andStrong vs. Weak Entities, andId if i R l i hiId if i R l i hiIdentifying RelationshipsIdentifying Relationships
S i iS i iStrong entities Strong entities exist independently of other types of entitiesexist independently of other types of entitieshas its own unique identifierhas its own unique identifierhas its own unique identifierhas its own unique identifieridentifier underlined with single lineidentifier underlined with single line
Weak entityWeak entitydependent on a strong entity (identifying owner)…cannot exist on dependent on a strong entity (identifying owner)…cannot exist on its ownits owndoes not have a unique identifier (only a partial identifier)does not have a unique identifier (only a partial identifier)does not have a unique identifier (only a partial identifier)does not have a unique identifier (only a partial identifier)partial identifier underlined with double linepartial identifier underlined with double lineentity box has double lineentity box has double line
Id if i l i hiId if i l i hiIdentifying relationshipIdentifying relationshiplinks strong entities to weak entitieslinks strong entities to weak entities
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3333
Identifying relationship (Figure 3-5)
Strong entity Weak entity
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3434
Associative EntitiesAssociative EntitiesAn An entityentity––has attributeshas attributes
AA relationshiprelationship links entities togetherlinks entities togetherA A relationshiprelationship––links entities togetherlinks entities together
When should a When should a relationship with attributesrelationship with attributes instead be an instead be an associative entityassociative entity??associative entityassociative entity? ?
All relationships for the associative entity should be manyAll relationships for the associative entity should be manyThe associative entity could have meaning independent of the The associative entity could have meaning independent of the other entitiesother entitiesother entitiesother entitiesThe associative entity preferably has a unique identifier, and should The associative entity preferably has a unique identifier, and should also have other attributesalso have other attributesThe associative entity may participate in other relationships otherThe associative entity may participate in other relationships otherThe associative entity may participate in other relationships other The associative entity may participate in other relationships other than the entities of the associated relationshipthan the entities of the associated relationshipTernary relationships should be converted to associative entitiesTernary relationships should be converted to associative entities
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3535
Figure 3-11a A binary relationship with an attributeFigure 3-11a 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 theemployee s completion of a course…it is an attribute of the relationship
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3636
Figure 3-11b 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 FigureNote that the many-to-many cardinality between entities in Figure 3-11a has been replaced by two one-to-many relationships with the associative entity
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3737
y
Figure 3-13c An associative entity – bill of materials structureg y
This could just be a relationship withThis could just be a relationship with attributes…it’s a judgment call
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3838
Fi 3 18 T l ti hi i ti titFigure 3-18 Ternary relationship as an associative entity
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 3939
Microsoft Visio Notation for Pine Valley Furniture
E R diagramE-R diagram
(Figure 3-22)( g )
Different modeling software tools may have different notation for the same constructs
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 4040
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means electronicretrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America.
Copyright © Copyright © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall
Chapter 3 © © 2009 2009 Pearson Education, Inc. Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall 4141