Entity Relationship, EER Model Modified.ppt

ER diagram

1. Chapter 3
Data Modeling Using the
Entity-Relationship (ER) Model

2. Chapter 3-2
ER Model Concepts
Entities and Attributes
–Entitiesarespecificobjectsorthingsinthemini-worldthatare
representedinthedatabase.ForexampletheEMPLOYEEJohn
Smith,theResearchDEPARTMENT,theProductPROJECT
–Attributesarepropertiesusedtodescribeanentity.Forexample
anEMPLOYEEentitymayhaveaName,SSN,Address,Sex,
BirthDate
Anentitysetisasetofentitiesofthesametypethatsharethesame
properties.
Example: set of all persons, companies, trees, holidays

3. Chapter 3-3
Types of Attributes
Simple
–Eachentityhasasingleatomicvaluefortheattribute.For
example,SSNorSex.
Composite
–Theattributemaybecomposedofseveralcomponents.For
example,Address(Apt#,House#,Street,City,State,ZipCode,
Country)orName(FirstName,MiddleName,LastName).
Compositionmayformahierarchywheresomecomponentsare
themselvescomposite.
Multi-valued
–Anentitymayhavemultiplevaluesforthatattribute.Forexample,
ColorofaCARorPreviousDegreesofaSTUDENT.Denotedas
{Color}or{PreviousDegrees}
•Derived-Aderivedattributecanbeobtainedfromother
attributesorrelatedentities.
•AgeisthederivedattributefromDOB

4. E-R Diagram With Composite, Multivalued, and Derived
Attributes

5. Chapter 3-5
SUMMARY OF ER-DIAGRAM
NOTATION FOR ER SCHEMAS
Meaning
ENTITY TYPE
WEAK ENTITY TYPE
RELATIONSHIP TYPE
IDENTIFYING RELATIONSHIP TYPE
ATTRIBUTE
KEY ATTRIBUTE
MULTIVALUED ATTRIBUTE
COMPOSITE ATTRIBUTE
DERIVED ATTRIBUTE
TOTAL PARTICIPATION OF E
2IN R
CARDINALITY RATIO 1:N FOR E
1:E
2 IN R
STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION
OF E IN R
Symbol
E
1
R E
2
E
1
R E
2
R
(min,max)
E
N

6. E-R Diagram
Rectangles represent entity sets.
Diamonds represent relationship sets.
Lines link attributes to entity sets and entity sets to relationship sets.
Ellipses represent attributes
Double ellipses represent multivaluedattributes.
Dashed ellipses denote derived attributes.
Underline indicates primary key attributes

7. Chapter 3-7
Relationships and Relationship Types
Arelationshiprelatestwoormoredistinctentitieswitha
specificmeaning.Forexample,EMPLOYEEJohnSmith
worksontheProductXPROJECTorEMPLOYEEFranklin
WongmanagestheResearchDEPARTMENT.
Thedegreeofarelationshiptypeisthenumberof
participatingentitytypes.BothMANAGES and
WORKS_ONarebinaryrelationships.
Morethanonerelationshiptypecanexistwiththesame
participatingentitytypes.Forexample,MANAGES and
WORKS_FOR aredistinctrelationshipsbetween
EMPLOYEEandDEPARTMENT

8. Chapter 3-8
Example relationship instances of the WORKS_FOR
relationship between EMPLOYEE and DEPARTMENT
e
1 
e
2 
e
3 
e
4 
e
5 
e
6 
e
7 
EMPLOYEE
r
1
r
2
r
3
r
4
r
5
r
6
r
7
WORKS_FOR
d
1
d
2
d
3
DEPARTMENT

9. Chapter 3-9
Example relationship instances of the WORKS_ON
relationship between EMPLOYEE and PROJECT
e
1 
e
2 
e
3 
e
4 
e
5 
e
6 
e
7 
r
1
r
2
r
3
r
4
r
5
r
6
r
7
p
1
p
2
p
3
r
8
r
9

10. Chapter 3-10
Weak Entity Types
Anentitythatdoesnothaveakeyattribute
Aweakentitymustparticipateinanidentifyingrelationship
typewithanowneroridentifyingentitytype
Example:
SupposethataDEPENDENT entityisidentifiedbythe
dependent’sfirstnameandbirthdate,andthespecific
EMPLOYEEthatthedependentisrelatedto.DEPENDENT
isaweakentitytypewithEMPLOYEEasitsidentifying
entitytypeviatheidentifyingrelationshiptype
DEPENDENT_OF

11. Chapter 3-11
Weak Entity Type is: DEPENDENT
Identifying Relationship is: DEPENDENTS_OF

12. Chapter 3-12
Mapping Cardinalities/Cardinality
Constraints on Relationships
–( Also known as ratio constraints )
One-to-one(1:1)-1:1relationshipfromentitytypeSto
entitytypeTisoneinwhichanentityfromSisrelatedtoat
mostoneentityfromTandviceversa.
One-to-many(1:N)-1:NrelationshipfromentitytypeSto
entitytypeTisoneinwhichanentityfromScanberelated
totwoormoreentitiesfromT.
Many-to-one(N:1)-N:1relationshipfromentitytypeSto
entitytypeTisoneinwhichanentityfromTcanberelated
totwoormoreentitiesfromS.
Many-to-many(N:M)-N:Mrelationshipfromentity
typeStoentitytypeTisoneinwhichanentityfromScan
berelatedtotwoormoreentitiesfromT,andanentity
fromTcanberelatedtotwoormoreentitiesfromS.

13. One to one One to many

14. Many to one Many to many

15. One-To-Many Relationship
•In the one-to-many relationship a loan is
associated with at most one customer via
borrower, a customer is associated with
several (including 0) loans via borrower

16. Many-To-One Relationships
•Inamany-to-onerelationshipaloanis
associatedwithseveral(including0)
customersviaborrower,acustomeris
associatedwithatmostoneloanvia
borrower

17. Many-To-Many Relationship
•Acustomerisassociatedwithseveral
(possibly0)loansviaborrower
•Aloanisassociatedwithseveral(possibly
0)customersviaborrower

18. Chapter 3-18
Relationships and Relationship
Types
Wecanalsohavearecursiverelationshiptype.
Bothparticipationsaresameentitytypeindifferentroles.
Forexample,SUPERVISION relationshipsbetween
EMPLOYEE(inroleofsupervisororboss)and(another)
EMPLOYEE(inroleofsubordinateorworker).
Infollowingfigure,firstroleparticipationlabeledwith1and
secondroleparticipationlabeledwith2.
InERdiagram,needtodisplayrolenamestodistinguish
participations.

19. Chapter 3-19
Recursive Relationship Type is: SUPERVISION
(participation role names are shown)

20. Chapter 3-20
Attributes of Relationship types
Arelationshiptypecanhaveattributes;for
example,HoursPerWeekofWORKS_ON;its
valueforeachrelationshipinstancedescribes
thenumberofhoursperweekthatan
EMPLOYEEworksonaPROJECT.

21. Chapter 3-21
Attribute of a Relationship Type is:
Hours of WORKS_ON

22. Totalparticipation(indicatedbydoubleline):everyentityintheentityset
participatesinatleastonerelationshipintherelationshipset
E.g.participationofloaninborroweristotal
everyloanmusthaveacustomerassociatedtoitviaborrower
Partialparticipation:someentitiesmaynotparticipateinanyrelationshipinthe
relationshipset
Example:participationofcustomerinborrowerispartial
Structural Constraints
Participation constraint(on each participating entity type):
total (called existence dependency) or partial.
SHOWN BY DOUBLE LINING THE LINK

23. Chapter 3-23
Relationships of Higher Degree
Relationshiptypesofdegree2arecalledbinary
Relationshiptypesofdegree3arecalledternaryandof
degreenarecalledn-ary
Ingeneral,ann-aryrelationshipisnotequivalentton
binaryrelationships
Higher-orderrelationshipsdiscussedfurtherinChapter4

24. Chapter 3-24
ER DIAGRAM FOR A BANK
DATABASE

25. Chapter 3-25
PROBLEM with ER notation
THE ENTITY RELATIONSHIP MODEL IN
ITS ORIGINAL FORM DID NOT
SUPPORT THE SPECIALIZATION/
GENERALIZATION ABSTRACTIONS

26. Chapter 3-26
Extended/Enhanced Entity-
Relationship (EER) Model
IncludesallmodelingconceptsofbasicER
Additionalconcepts: subclasses/superclasses,
specialization/generalization,categories,attribute
inheritance
IncorporatesSet-subsetrelationships
Itincludessomeobject-orientedconcepts,suchas
inheritance

27. Subclasses and Superclasses
Anentitytypemayhaveadditionalmeaningful
subgroupingsofitsentities
Example:EMPLOYEE maybefurthergroupedinto
SECRETARY,ENGINEER,MANAGER,TECHNICIAN,
SALARIED_EMPLOYEE, HOURLY_EMPLOYEE, …
–EachofthesegroupingsisasubsetofEMPLOYEEentities
–EachiscalledasubclassofEMPLOYEE
–EMPLOYEEisthesuperclassforeachofthesesubclasses
Thesearecalledsuperclass/subclassrelationships.
Example:
–EMPLOYEE/SECRETARY, EMPLOYEE/TECHNICIAN

28. Specialization
Is the process of defining a set of subclasses of a superclass
The set of subclasses is based upon some distinguishing characteristics
of the entities in the superclass
Example: {SECRETARY, ENGINEER, TECHNICIAN} is a
specialization of EMPLOYEE based upon job type.
–May have several specializations of the same superclass

29. Example of a Specialization

30. Generalization
The reverse of the specialization process
Several classes with common features are generalized into
a superclass; original classes become its subclasses
Example: CAR, TRUCK generalized into VEHICLE; both
CAR, TRUCK become subclasses of the superclass
VEHICLE.
–We can view {CAR, TRUCK} as a specialization of VEHICLE
–Alternatively, we can view VEHICLE as a generalization of CAR
and TRUCK

31. Specialization / Generalization
Hierarchies, Lattices and Shared
Subclasses
Diagrammaticnotationsometimesusedtodistinguishbetween
generalizationandspecialization
•Arrowpointingtothegeneralizedsuperclassrepresentsageneralization
•Arrowspointingtothespecializedsubclassesrepresentaspecialization
Asubclassmayitselfhavefurthersubclassesspecifiedonit
Formsahierarchyoralattice
Hierarchyhasaconstraintthateverysubclasshasonlyonesuperclass(calledsingle
inheritance)
Inalattice,asubclasscanbesubclassofmorethanonesuperclass(calledmultiple
inheritance)
Asubclasswithmorethanonesuperclassiscalledasharedsubclass

32. Specialization / Generalization
Lattice Example (UNIVERSITY)

33. Categories (UNION TYPES)
Insomecases,needtomodelasinglesuperclass/subclassrelationshipwith
morethanonesuperclass
SuchasubclassiscalledacategoryorUNIONTYPE
Example:Databaseforvehicleregistration,vehicleownercanbeaperson,a
bank(holdingalienonavehicle)oracompany.
–Category(subclass)OWNERisasubsetoftheunionofthethreesuperclasses
COMPANY,BANK,andPERSON
–Acategorymembermustexistinatleastoneofitssuperclasses

34. Example of categories
(UNION TYPES)