l09: er modeling 2 - northeastern university · 73 1./2. cardinality + participation constraints...
TRANSCRIPT
![Page 1: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/1.jpg)
64
L09:ERmodeling2
CS3200 Databasedesign(sp18 s2)https://course.ccs.neu.edu/cs3200sp18s2/2/8/2018
![Page 2: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/2.jpg)
65
Announcements!
• Avoidsittinginthelastrow• Slidesarenowsplitupperlecture• Classattendance:Remainingnametags• P1 tobereleasedtoday• HW collaborationpolicies• Exam1nextMonday.Practiceexam0today!- BB:filesavailableunder"assignments"at11:55- Nextweek:60min,SQLonly,1cheatsheet,tobehandedinafterclass,signhonorcode
• Outline- Practiceexam0- ERmodeling
![Page 3: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/3.jpg)
66
Customer/Purchase
Purchaseoiddatecidamount
Customercidnameageaddresssalary
391
Q1: Find the number of purchases for each customer. Return customer name and number of purchases.
![Page 4: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/4.jpg)
67
Back to ERDs:Relationships
![Page 5: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/5.jpg)
68
Overview:3importantconceptsforrelationships
• Cardinality ("arity"):numberofentityinstancesthatparticipate(~mainlymax)
• Participationconstraints:mandatoryoroptional(equivalenttominimumcardinality0or1)
• Degree:numberofentitytypesthatparticipate
Randomquestion:Why"relationalmodel"?
![Page 6: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/6.jpg)
69
1.Cardinality(multiplicity)ofrelationships• Definesthenumberofentityinstances thatparticipateinit.Alsocalledtypeofrelationship
(or"connectivity"attimes).
• One-to-One- Eachentityintherelationshipwillhaveexactlyonerelatedentity
• One-to-Many- Anentityononesideoftherelationshipcanhavemanyrelatedentities,butanentityontheotherside
willhaveamaximumofonerelatedentity
• Many-to-Many- Entitiesonbothsidesofthe
relationshipcanhavemanyrelatedentitiesontheotherside
Thisnotationisalsocalled"crow'sfootnotation"
![Page 7: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/7.jpg)
70
1.Cardinality(multiplicity)ofE/Rrelationships
123
abcd
One-to-one:
123
abcd
One-to-many:
123
abcd
Many-to-many:
X->YmeansthereexistsafunctionmappingfromXtoY(recallthedefinitionofafunction)
Stanford'sArrownotation Crow'sFeet
![Page 8: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/8.jpg)
71
address name ssn
Person
buys
makes
employs
CompanyProduct
name category
stockprice
name
price
Whatdoesthissay?
![Page 9: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/9.jpg)
72
2.Participationconstraints
Whetherarelationismandatoryoroptionalisalsosometimescalled"participationconstraints"
• Thenumberofinstancesofoneentitythatcanormustbeassociatedwitheachinstanceofanotherentity
• Minimum:ifzero,thenoptional,ifoneormore,thenmandatory
• Maximum
- Mandatory one
- Mandatorymany
- Optional one
- Optionalmany
![Page 10: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/10.jpg)
73
1./2.Cardinality+participationconstraints
• Definestherulesoftheassociationbetweenentities
Customer makes Order
Thisisaone-to-manyrelationship:Onecustomercanhavemanyorders.ThesymbolsclosesttotheOrderentityrepresents"one,ormany",whereasanorderhas"oneandonlyone"Customer.
at least – oneat most - many
at least – oneat most - one
Source:MIS5101,DataManagementandWarehousing,Temple.
![Page 11: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/11.jpg)
74
Ex:cardinality+participationconstraints(1/2)
Source:Fig.2.17:Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
PATIENT PATIENTHISTORY
PleaseinterprettheconstraintsinthefollowingERdiagram:
Cardinalities+Participation
![Page 12: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/12.jpg)
75
Apatientmusthaverecor-dedatleastonehistory,andcanhavemany
Apatienthistoryisrecordedforoneandonlyonepatient
Ex:cardinality+participationconstraints(1/2)
Source:Fig.2.17:Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
PATIENT PATIENTHISTORY
Alice
Bob
Charlie
Visit1Visit2Visit3Visit4
Cardinalities+Participation
PleaseinterprettheconstraintsinthefollowingERdiagram:
![Page 13: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/13.jpg)
76
Ex:cardinality+participationconstraints(2/2)
Source:Fig.2.17:Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
EMPLOYEE PROJECT
PleaseinterprettheconstraintsinthefollowingERdiagram:
Cardinalities+Participation
![Page 14: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/14.jpg)
77
Anemployeecanbeassignedtoanynumberofprojects,ormaynotbeassignedtoanyatall
Aprojectmustbeassignedtoatleastoneemployee,andmaybeassignedtomany
Ex:cardinality+participationconstraints(2/2)
Source:Fig.2.17:Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
EMPLOYEE PROJECT
Alice
Bob
Charlie
Project1
Project2
Project3
Project4Dorothy
PleaseinterprettheconstraintsinthefollowingERdiagram:
Cardinalities+Participation
![Page 15: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/15.jpg)
78
ParticipationconstraintsinChennotation
Each student must be guided by one professor.A professor professor can (but does not have to) guide one or more students
Total participation means that every entity in the set is involved in the relationship.
Source:http://www.vertabelo.com/blog/technical-articles/chen-erd-notation
![Page 16: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/16.jpg)
79
3.DegreeofRelationship
• Definesthenumberofentitytypes thatparticipateinit
UnaryRelationship- Oneentitytyperelatedtoitself
BinaryRelationship- Twoentitytypesrelatedtoeachother
TernaryRelationship- Threeentitytypesrelatedtoeachother
• Itispossible,butunusual,tohaverelationshiptypesoflargerthan3entities
![Page 17: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/17.jpg)
80
Multi-wayRelationships
Howdowemodelapurchaserelationshipbetweenbuyers,productsandstores?
Purchase
Product
Person
Store
![Page 18: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/18.jpg)
81
ArrowsinMultiwayRelationshipsQ:Whatdoesthearrowmean?
Purchase
Product
Person
Store
![Page 19: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/19.jpg)
82
ArrowsinMultiwayRelationshipsQ:Whatdoesthearrowmean?
Purchase
Product
Person
Store
![Page 20: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/20.jpg)
83
ArrowsinMultiwayRelationshipsQ:Howdowesaythateverypersonshopsinatmostonestore?
A: Cannot. This is the best approximation.(Why only approximation ?)
Product
Person
StorePurchase
![Page 21: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/21.jpg)
84
ConvertingMulti-wayRelationshipstoBinary
Fromwhatwehadonpreviousslidetothis- whatdidwedo?
Purchase
Person
Store
Product
StoreOf
ProductOf
BuyerOf
date
![Page 22: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/22.jpg)
85
ConvertingMulti-wayRelationshipstoNewEntity+BinaryRelationships
Purchase
Person
Store
Product
StoreOf
ProductOf
BuyerOf
date
Sidenote:Whatarrowsshouldbeaddedhere?Arethesecorrect?
![Page 23: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/23.jpg)
86
Decision:Multi-wayorNewEntity+Binary?
Shouldweuseasinglemulti-wayrelationshiporanewentitywithbinaryrelations?
Purchase
Person
Store
Product
StoreOf
ProductOf
BuyerOf
date
Purchase
Product
Person
Store
Multi-wayRelationship Entity+Binary
![Page 24: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/24.jpg)
87
Decision:Multi-wayorNewEntity+Binary?
(A)Multi-wayRelationship (B)Entity+Binary
• Coveredearlier:(B)isusefulifwewanttohavemultipleinstancesofthe“relationship”perentitycombination
Multiplepurchasesper(product,store,person)combopossiblehere!
Purchase
Person
Store
Product
StoreOf
ProductOf
BuyerOf
date
Purchase
Product
Person
Store
• (A)isusefulwhenarelationshipreallyisbetweenmultipleentities- Ex: A three-party legal contract
![Page 25: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/25.jpg)
88
Weak (or dependent)
Entities
![Page 26: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/26.jpg)
89
Strongvs.Weak(Dependent)Entities• Strongentities
- Canbeidentified("exist")independently ofothertypesofentities- Havetheirownuniqueidentifier
• Weakentities- Dependentonastrongentity,
cannotexistontheirown(better:cannotbeidentifiedindependently)
- Donothaveuniqueidentifiers:PKoverlapswithparent'sPK
- (representedwithdouble-linerectangle)
• Identifyingrelationship- Linksstrongentitiestoweakentities- Representedwithdoublelinerelationship
Entitysetsareweakwhenpartoftheiridentifiercomesfromclassestowhichtheyarerelated
![Page 27: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/27.jpg)
90
Example:StrongandWeakEntities
• Employeecariesonedependent- Employee:ID,name- Dependent:name,Date_of_Birth
Source:Fig2.5.Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
Identifying relationship
Strong entity= identifying owner
Weak entity= dependent entity
Partial identifier
Note we need both EMPLOYEE_ID and DEPENDENT_NAME to uniquely identify a dependent
![Page 28: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/28.jpg)
91
AlternativenotationsforsamescenarioEMPLOYEE
*EMPLOYEE_ID
EMPLOYEE_NAME *DEPENDENT_NAME
DATE_OF_BIRTH
DEPENDENT
has
Dependentpolicy
DOB name
Employee
namedeptssn
![Page 29: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/29.jpg)
92
Participationconstraintsandweakentities
Source:http://www.vertabelo.com/blog/technical-articles/chen-erd-notation
![Page 30: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/30.jpg)
93
AssociativeEntities
![Page 31: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/31.jpg)
94
AssociativeEntities
• It seemslikearelationship:itlinksentitiestogether• Yetitseemslikeanentity:ithasattributes• Whenshouldarelationshipwithattributesinsteadbeanassociativeentity?- Allrelationshipsfortheassociativeentityshouldbemany- Theassociativeentitycouldhavemeaningindependentoftheotherentities- Theassociativeentitypreferablyhasauniqueidentifier(butnotnecessarily),and
shouldalsohaveotherattributes- Ternaryrelationships shouldbeconvertedtoassociativeentities- (Theassociativeentitymayparticipateinotherrelationshipsotherthantheentitiesof
theassociatedrelationship)
![Page 32: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/32.jpg)
95Source:Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
Example:CertificateAssociativeEntity(1/3)• Associativeentitycanberepresentedasrectanglewithroundedcorners(Hoffernotation)• Notethatthemany-to-manycardinalitysymbolsfacetoward
theassociativeentityandnottowardtheotherentities
![Page 33: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/33.jpg)
96Source:Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
Example:CertificateAssociativeEntity(1/3)• Associativeentitycanberepresentedasrectanglewithroundedcorners(Hoffernotation)• Notethatthemany-to-manycardinalitysymbolsfacetoward
theassociativeentityandnottowardtheotherentities
![Page 34: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/34.jpg)
97
Example:CertificateAssociativeEntity(2/3)
*CERTIFICATE_NUMBER
DATE_COMPLETED
EMPLOYEE
*EMPLOYEE_ID
EMPLOYEE_NAME
COURSE
*COURSE_ID
COURSE_TITLE
EMPLOYEE*EMPLOYEE_ID
EMPLOYEE_NAME
COURSE*COURSE_ID
COURSE_TITLEGets Certified
CERTIFICATE
Relationship
AssociativeEntity
DATE_COMPLETED
![Page 35: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/35.jpg)
98
Example:CertificateAssociativeEntity(3/3)
Source:Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
NotationusedbyMicrosoftVisio
Important!NoticethatERDsdonotshowforeignkeys!
![Page 36: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/36.jpg)
99
CASTinourIMDBmoviedatabase
![Page 37: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/37.jpg)
100Source:Fig2-21,Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
Multiplerelationships
![Page 38: L09: ER modeling 2 - Northeastern University · 73 1./2. Cardinality + participation constraints •Defines the rules of the association between entities Customer makes Order This](https://reader034.vdocuments.us/reader034/viewer/2022042221/5ec77189e9b0871e1d6e9efc/html5/thumbnails/38.jpg)
101
Here,mincardinalityconstraintis2:Atleasttwoprofessorsmustbequalifiedtoteacheachcourse
Source:Fig2-21,Hoffer,Ramesh,Topi,"Moderndatabasemanagement,"10th ed,2010.
Multiplerelationships