![Page 1: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/1.jpg)
23.01.12 1 Anne-Kathrin Peters- IT - UU!
Details on ER and RM modeling
Elmasri/Navathe ch 7,9 Padron-McCarthy/Risch ch 2-3"
Anne-Kathrin Peters
Department of Information Technology Uppsala University, Uppsala, Sweden
![Page 2: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/2.jpg)
23.01.12 2 Anne-Kathrin Peters- IT - UU!
Content"
• Last lecture (L2) and goal for L2 • Comparison of assignment • Translation of ER-model to RM – continued • “Database in Action” BREAK • EER and translation to RM
![Page 3: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/3.jpg)
23.01.12 3 Anne-Kathrin Peters- IT - UU!
Last Lecture L2 Introduction to the Relational Model
"
course
member course in
persnr
gives
nn m
1
trainer name
id
name
max
personnr
address
tel-nr
street
nr
post code
city
payed
price
member in trainer
…
pnr pnr id id tid pnr
Conceptual Schema
Conceptual Design
Logical Design
Conceptual Model e.g. UML, ERM
Implementation Model (Data Model of DBMS here RM)
Logical Schema
![Page 4: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/4.jpg)
23.01.12 4 Anne-Kathrin Peters- IT - UU!
Lessons learnt from ER modeling"
• Be careful with attributes that are more like a relation: e.g.: “members” might sound like an attribute for the entity type “group”. NO- it is a relationship type (N:M). It needs to be implemented through an extra table!!!
• An “artificial” attribute, e.g. id is quite convenient to use as a primary key • Weak entities are not used so often (they don’t have a primary key) • ER model is used to communicate with the user / to specify system
à Avoid too high complexity. Try to keep it simple J
![Page 5: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/5.jpg)
23.01.12 5 Anne-Kathrin Peters- IT - UU!
From E-R to relational model"• The basic procedure defines a set of relational schemas that represent entity
and relationship types in the E-R model. This model should further with integrity constraints. – Primary keys allow entity types and relationship types to be expressed uniformly as tables
which represent the contents of the database. – A database which conforms to an E-R diagram can be represented by a collection of tables. – For each entity type and relationship type there is a unique table which is assigned the name
of the corresponding entity type or relationship type. – Each table has a number of columns (generally corresponding to attributes), which have
unique names. – Converting an E-R diagram to a table format is the basis for deriving a relational database
design from an E-R diagram.
![Page 6: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/6.jpg)
23.01.12 6 Anne-Kathrin Peters- IT - UU!
Translation of ER modele.g. social network DB"
PROFILE(id, first name, last name, username, looking for, political views, photo_url)
PHOTO(url, description) PINBOARDENTRY(id, text, datetime) GROUP(id, description, creator_id) MEMBER(group_id, profile_id)
![Page 7: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/7.jpg)
23.01.12 7 Anne-Kathrin Peters- IT - UU!
Translation of ER using Example"Weak entity types & owning relationship
relationship type with degree > 2
Multivalued attributes
![Page 8: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/8.jpg)
23.01.12 8 Anne-Kathrin Peters- IT - UU!
1. Comparison"
![Page 9: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/9.jpg)
23.01.12 9 Anne-Kathrin Peters- IT - UU!
Relational schemas for the example social network DB"
• Schemas for the entity types PROFILE(id, first name, last name, username, looking for,
political views, photo_url) PHOTO(url, description, datetime) PINBOARDENTRY(id, text, datetime) GROUP(id, description, public, creator_id) LOCATION(id, street, nr, postal code, city, country) EVENT(id, description, datetime, public, creator_id,
location_id) • Schemas for relationship types
MEMBER(group_id, profile_id, status) CONTAINS(pinentry_id, photo_url) ���LIVES(profile_id, location_id) INVITED(profile_id, event_id, answer) FRIENDWITH(profile1_id, profile2_id)
![Page 10: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/10.jpg)
23.01.12 10 Anne-Kathrin Peters- IT - UU!
Mapping of n-ary Relationship Types"
1. Make new table 2. Decide on primary key
HAS(pinboardentry_id, group_id, creator_id) PINBOARDENTRY(id, text, datetime, group_id, creator_id)
![Page 11: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/11.jpg)
23.01.12 11 Anne-Kathrin Peters- IT - UU!
Weak Entity types"• Table with attributes as columns • Primary key composed: candidate key of weak entity type and primary key
of owner entity type à “owner relationship” is mapped already
Comment(text, datetime, pinentryId, profileid)
Attention: This means that one user can’t comment with the same text on the same pinboard entry! So position is probably really a better partial key!
![Page 12: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/12.jpg)
23.01.12 12 Anne-Kathrin Peters- IT - UU!
pk a1 pk k a2
R!
Translating entity types cont. . ."
a1
pk
a2
k 1! N!
• Weak entity types - a weak entity type becomes a table that includes a column for the primary key of the identifying strong entity type .
E1
![Page 13: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/13.jpg)
23.01.12 13 Anne-Kathrin Peters- IT - UU!
Mapping of multivalued Attributes"
• Think of it as weak entity: – Extra table – Attribute A as one column – Primary key:
• Attribute A • Primary key of owning entity
bands profile likes n 1name
bands(name, pId)
![Page 14: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/14.jpg)
23.01.12 14 Anne-Kathrin Peters- IT - UU!
Relational schemas for the example social network DB"
• Schemas for the entity types PROFILE(id, first name, last name, username, looking for,
political views, photo_url) BANDS(name, p_id) PHOTO(url, description, datetime) PINBOARDENTRY(id, text, datetime) COMMENT(position, datetime, pinboardentry_id, profile_id) GROUP(id, description, public, creator_id) LOCATION(id, street, nr, postal code, city, country) EVENT(id, description, datetime, public, creator_id,
location_id) • Schemas for relationship types
MEMBER(group_id, profile_id, status) CONTAINS(pinentry_id, photo_url) ���GROUPHASPINENTRIES(pindentry_id, group_id, creator_id) PROFILEHASPINENTRIES(pindentry_id, adressee_id, creator_id) LIVES(profile_id, location_id) INVITED(profile_id, event_id, answer) FRIENDWITH(profile1_id, profile2_id)
![Page 15: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/15.jpg)
23.01.12 15 Anne-Kathrin Peters- IT - UU!
Summary of Translations"ERM RM (see slides for details!)
Entity type Relation (Table) Simple Attributes Attribute in Relation (Column) Candidate key attribute(s)
Choose one as primary key (if more than one)
Composite Attributes Set of simple component attributes (columns) Multivalued Attributes Extra relation (“treat as weak entity type”) n:m Relation Relation (table), primary key: 2 foreign keys 1:n Relation Either include in “n-Entity”, else extra relation with
primary key: key of “n-Entity” 1:1Relation 3 options: 1. merge table with table of entity E1, E2,
2. extra table, 3. one table for E1, E2, R n-ary Relation Extra table (, merge?)
![Page 16: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/16.jpg)
23.01.12 16 Anne-Kathrin Peters- IT - UU!
Use of Databases in WWW"
Source: Ludwig-Maximilians-University, Munich, Germany
JavaScript, JavaApplets PHP, Java Servlets, JSP
Execution client side Execution server side
![Page 17: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/17.jpg)
23.01.12 17 Anne-Kathrin Peters- IT - UU!
Integrity constraints
1. Domain constraint – Values for attribute A shall be atomic values from dom(A)
2. Key constraint – candidate keys for a relation must be unique
3. Entity integrity constraint – no primary key is allowed to have a null value
4. Referential integrity constraint – Foreign Keys must refer to an existing tuple
5. Semantic integrity constraint – e.g. “an employee’s total work time per week can not exceed
40 hours for all projects taken all together”
![Page 18: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/18.jpg)
23.01.12 18 Anne-Kathrin Peters- IT - UU!
Letʼs get a bit deeper intoDB design…"
![Page 19: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/19.jpg)
23.01.12 19 Anne-Kathrin Peters- IT - UU!
ER model transf. cont. ..."
Replacing relationships with degree>2 with an entity type and binary relationships.
O!
N!
Time!
COURSE!
TEACHER!
ROOM!N!
N!
Time!
COURSE!
LECTURES!
TEACHER!
ROOM!consists of!
1!
lectures!
1!
booked for!
N!
1!
M!lectures!
![Page 20: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/20.jpg)
23.01.12 20 Anne-Kathrin Peters- IT - UU!
cardinality/participation vs. min-max"
part_of!PRODUCT DETAIL 1 N
part_of!PRODUCT DETAIL (0,N) (1,1)
![Page 21: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/21.jpg)
23.01.12 21 Anne-Kathrin Peters- IT - UU!
Extended Entity-Relationship (EER) modeling"
• EER (extended or enhanced ER) – Specialization / Generalization
• Superclass / subclass • supertype/subtype,
• is-a relationship • constraints
– Aggregation (whole/part or part-of relationship) – Union types (category)
![Page 22: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/22.jpg)
23.01.12 22 Anne-Kathrin Peters- IT - UU!
EER diagram notation for specialization and generalization(Elmasri/Navathe Figure 7.19)!
partial/total (similar to ERM)
Disjunkt / Overlapping (à)
Type inheritance through: entity of subclass is also member of superclass!
Constraints on membership, e.g. through attribute “job_type”
![Page 23: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/23.jpg)
23.01.12 23 Anne-Kathrin Peters- IT - UU!
Overlapping (nondisjoint) subclasses (Elmasri/Navathe Figure 7.23)"
Disjunkt / Overlapping (à)
![Page 24: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/24.jpg)
23.01.12 24 Anne-Kathrin Peters- IT - UU!
Generalization of subclasses
(Elmasri/Navathe Figure 7.21)"
![Page 25: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/25.jpg)
23.01.12 25 Anne-Kathrin Peters- IT - UU!
Summary Subclasses, superclasses & inheritance"
• Two approaches for creating superclass/subclass relationships – Specialization of superclass into subclasses – Generalization of subclasses into a superclass
• Entity of subclass is also entity of superclass • Type inheritance: Type of entity- attributes and relationship • Characteristics of specialization & generalization
– Disjoint (d) vs. Overlapping (o) – Completeness: Total vs. Partial
![Page 26: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/26.jpg)
23.01.12 26 Anne-Kathrin Peters- IT - UU!
How to translate?"
Options: 1. Multiple-Relation:
EMPLOYEE(Ssn,Fname,LBirthDate,…) TECHNICIAN(Ssn,Tgrade) SECRETARY(Ssn,TypingSpeed) ENGINEER(Ssn,EngType)
2. Single Relation: ENGINEER(Ssn,Fname,Minit,Lname, BirthDate,Address,TypingSpeed, Tgrade,EngType)
![Page 27: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/27.jpg)
23.01.12 27 Anne-Kathrin Peters- IT - UU!
Translation of Specialization / Generalization"
Given m Subclasses {S1, …, Sm} and (generalized) superclass C with attributes Attrs(C)={k,a1,…,an}, k primary key (PK(C)=k). à Options for translation: 1. Multiple relations, Superclasses and subclasses:
Relation for C, Attrs(C)={k,a1,…,an} and relation Li for each subclass with Attrs(Li)={k}∪ {attributes of Si}, PK(Li)=k (1≤i≤m)
2. Multiple relations, subclasses only: Relation Li for each subclass Si , 1≤i≤m, Attrs(Li)={k,a1,…,an}∪ {attributes of Si}, PK(Li)=k (only for total subclass specialization)
3. Single relation with one type attribute: Single Relation L, Attrs(Li)={k,a1,…,an}∪{attributes of S1}∪ … ∪ , {attributes of Sm}∪{t}, t type attribute, PK(Li)=k (for disjoint subclasses)
4. Single relation with multiple type attributes: Single Relation L, Attrs(Li)={k,a1,…,an}∪{attributes of S1}∪ … ∪ , {attributes of Sm}∪{t1, t2… , tm} boolean type attributes, PK(Li)=k (for overlapping subclasses)
![Page 28: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/28.jpg)
23.01.12 28 Anne-Kathrin Peters- IT - UU!
Multiple distinct superclass relationships"
![Page 29: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/29.jpg)
23.01.12 29 Anne-Kathrin Peters- IT - UU!
Union / Category (Elmasri/Navathe
Figure 7.28)"
![Page 30: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/30.jpg)
23.01.12 30 Anne-Kathrin Peters- IT - UU!
Summary & translation"
• Union: – Union of different entity types (instead of intersection of different entity types) – Partial or total
• Translation:
![Page 31: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/31.jpg)
23.01.12 31 Anne-Kathrin Peters- IT - UU!
Representation of aggregation in ER notation"• 3 cases:
1. Aggregation of attribute values to form object 2. Aggregation of attribute to form a relationship 3. Particular relationship “part-of” / “is-component-of” between primitive
objects • No explicit representation of aggregation in ER notation���
������
part_of"Team Player 1 N
position
name
id
name
nr
id
![Page 32: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/32.jpg)
23.01.12 32 Anne-Kathrin Peters- IT - UU!
Representation of aggregation in
ER notation
(Elmasri/Navathe Figure 7.21)"
3 cases: 1. Aggregation of attribute
values to form object 2. Aggregation of attribute to
form a relationship 3. Particular relationship
“part-of” / “is-component-of” between primitive objects
![Page 33: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/33.jpg)
23.01.12 33 Anne-Kathrin Peters- IT - UU!
A UML conceptual schema (Elmasri/Navathe Figure. 9.1)"
Further information: www.uml.org
![Page 34: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/34.jpg)
23.01.12 34 Anne-Kathrin Peters- IT - UU!
Specialization/generalization in UML
(Elmasri/Navathe Figure 9.2)"
Further information: www.uml.org
![Page 35: Details on ER and RM modeling - Uppsala University€¦ · • ER model is used to communicate with the user / to specify system ! Avoid too high complexity. Try to keep it simple](https://reader033.vdocuments.us/reader033/viewer/2022042220/5ec6b66d4c24394dfb0e0fac/html5/thumbnails/35.jpg)
23.01.12 35 Anne-Kathrin Peters- IT - UU!
Alternative diagrammatic
notation for ER/EER
(Elmasri/Navathe Figure A.1)"