Lecture 6: ER –Relational Translation môn Principle of Management | Trường Đại học Quốc tế, Đại học Quốc gia Thành phố Hồ Chí Minh

International University, VNU-HCMC
School of Computer Science and Engineering Lecture 6: ER –Relational Translation
Instructor: Nguyen Thi Thuy Loan
nttloan@hcmiu.edu.vn nthithuyloan@ , gmail.com https://nttloan.wordpress.com/
International University, VNU-HCMC Acknowledgement
The following slides have been created adapting
the materials of the [GUW] book provided by the
authors Prof.Jeffrey D. Ullman and et al.
Other slides are referenced from Northeastern
University and Duke University.
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 2
International University, VNU-HCMC ER model: review Design ERD •Notation •Entity/ Weak entity •Attributes •Relationship oAttributes on relationships oMultiplicity
oBinary versus n-ary relationships
Assoc. Prof. Nguyen Thi Thuy Loan, PhD oISA relationships 3
International University, VNU-HCMC To d a y ’s topics
•Understand the real-world domain being modeled
•Specify it using a database design model (e.g., E/R)
•Translate specification to the data model of DBMS (e.g., relational) •Create DBMS schema
•Reading material: [GUW] Chapter 3
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 4
International University, VNU-HCMC 5 Example number name nn Trains StopsAt e in ng eerStations address type time type You designed an ER digram
Translate it to a Relational Database Train (number e , ngineer,type) Station (name,address,type)
Assoc. Prof. Nguyen Thi Thuy Loan, PhD TrainStop (train_number,stati a on_n me,time) 5
International University, VNU-HCMC From E/R Diagrams to Relations •Entity set -> relation.
–Attributes -> attributes.
•Relationships -> relations whose attributes are only:
–The keys of the connected entity sets.
–Attributes of the relationship itself.
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 6
International University, VNU-HCMC General steps
•Each entity (regular/weak) in the ER is converted into a schema.
•For (1,1)-(1,1) relationships. We make one entity’s primary key
another entity’s foreign key and vice versa.
•The same for (1,1)-(1,n) relationships, we don’t establish a
new schema but bring the primary key from the (1,n) entity
into the (1,1) entity to make a foreign key.
•For (1,n)-(1,n) relationships, we establish a new schema
containing the primary keys of the entities that formed it and its own attributes.
•For multivalued attribute is converted into a new schema.
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 7
International University, VNU-HCMC Step 1: Regular Entity Types
i. For each regular/strong entity type, create a
corresponding relation that includes all the
simple attributes (includes simple attributes of composite relations)
ii. Choose one of the key attributes as primary
§If composite, the simple attributes together form the primary key.
iii. Any remaining key attributes are kept as
secondary unique keys (these will be useful for
physical tuning with reference to indexing analysis)
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 8
International University, VNU-HCMC Step 2: Weak Entity Types
i. For each weak entity type, create a
corresponding relation that includes all the simple attributes
ii. Add as a foreign key all of the primary key
attribute(s) in the entity corresponding to the owner entity type
iii. The primary key is the combination of all the
primary key attributes from the owner and the
partial key of the weak entity, if any
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 9
International University, VNU-HCMC Step 3: Mapping Binary 1-to-1 Three approaches –Foreign Key •Usually appropriate –Merged Relation
•Possible when both participations are total –Relationship Relation •Not discussed
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 10
International University, VNU-HCMC Step 3: Mapping Binary 1-to-1
i. Choose one relation as S, the other T
§Better if S has total participation (reduces number of NULL values)
ii. Add to S all the simple attributes of the relationship
iii. Add as a foreign key in S the primary key attributes of T
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 11
International University, VNU-HCMC Step 4: Binary 1-to-N
i. Choose the S relation as the type at the N-side
of the relationship, other is T
ii. Add as a foreign key to S all of the primary key attribute(s) of T
Another approach: create a relationship relation
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 12
International University, VNU-HCMC Step 5: Binary M-to-N
i. Create a new relation S(termed: relationship relation)
–In some ERD dialects, actually drawn in
ii. Add as foreign keys the primar keys y of both
relations; their combination forms the primary key of S
iii. Add any simple attributes of the M : N relationship to S
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 13
International University, VNU-HCMC Step 6: Multivalued Attributes i. Create a new relation S
ii. Add as foreign keys the primary keys of the corresponding relation
iii. Add the attribute to S(if composite, the
simple attributes);the combination of all
attributes in Sforms the primary key
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 14
International University, VNU-HCMC
Step 7: Specialization/Generalization
A. Multiple relations –subclass and superclass
§Usually works (assumes unique id at parent)
B. Multiple relations –subclass only
§Should only be used for disjoint
C. Single relation with one type attribute
§Only for disjoint, can result in many NULLs
D. Single relation with multiple type attributes Assoc. Prof. Nguyen § Thi Better Thuy Loan, PhD
for overlapping, could be disjoint 15
International University, VNU-HCMC Example ERD
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 16
International University, VNU-HCMC Resulting Relational Schema
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 17
International University, VNU-HCMC Step 1: Regular Entity Types
i. For each regular/strong entity type, create a
corresponding relation that includes all the
simple attributes (includes simple attributes of composite relations)
ii. Choose one of the key attributes as primary
§If composite, the simple attributes together form the primary key
iii. Any remaining key attributes are kept as
secondary unique keys (these will be useful for
physical tuning w.r.t. indexing analysis)
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 18
International University, VNU-HCMC Example ERD
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 19
International University, VNU-HCMC Step 1Result
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 20
International University, VNU-HCMC Step 2: Weak Entity Types
i. For each weak entity type, create a
corresponding relation that includes all the simple attributes
ii. Add as a foreign key all of the primary key
attribute(s) in the entity corresponding to the owner entity type
iii. The primary key is the combination of all the
primary key attributes from the owner and the
partial key of the weak entity, if any
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 21
International University, VNU-HCMC Step 2Result
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 22
International University, VNU-HCMC Step 3: Mapping Binary 1-to-1 Three approaches –Foreign Key •Usually appropriate –Merged Relation
•Possible when both participations are total –Relationship Relation •Not discussed
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 23
International University, VNU-HCMC Step 3: Mapping Binary 1-to-1
i. Choose one relation as S, the other T
§Better if S has total participation (reduces number of NULL values)
ii. Add to S all the simple attributes of the relationship
iii. Add as a foreign key in S the primary key attributes of T
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 24
International University, VNU-HCMC Step 2Result
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 25
International University, VNU-HCMC Step 3Result
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 26
International University, VNU-HCMC Step 4: Binary 1-to-N
i. Choose the S relation as the type at the N-
side of the relationship, other is T
ii. Add as a foreign key to S all of the primary key attribute(s) of T
Another approach: create a relationship relation
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 27
International University, VNU-HCMC Step 4Result
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 28
International University, VNU-HCMC Step 5: Binary M-to-N
i. Create a new relation S(termed: relationship relation)
–In some ERD dialects, actually drawn in
ii. Add as foreign keys the primar keys y of both
relations; their combination forms the primary key of S
iii. Add any simple attributes of the M : N relationship to S
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 29
International University, VNU-HCMC Step 5Result
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 30
International University, VNU-HCMC Step 6: Multivalued Attributes i. Create a new relation S
ii. Add as foreign keys the primary keys of the corresponding relation
iii. Add the attribute to S(if composite, the simple
attributes);the combination of all attributes in S forms the primary key
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 31
International University, VNU-HCMC Step 6Result
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 32
International University, VNU-HCMC
Step 7: Specialization/Generalization
A. Multiple relations –subclass and superclass
§Usually works (assumes unique id at parent)
B. Multiple relations –subclass only
§Should only be used for disjoint
C. Single relation with one type attribute
§Only for disjoint, can result in many NULLs
D. Single relation with multiple type attributes
§Better for overlapping, could be disjoint
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 33
International University, VNU-HCMC
Specialization/Generalization (A)
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 34
International University, VNU-HCMC
Specialization/Generalization (B)
B. Multiple relations –subclass only
Should only be used for disjoint
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 35
International University, VNU-HCMC
Specialization/Generalization (C)
C. Single relation with one type attribute
Only for disjoint, can result in many NULLs
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 36
International University, VNU-HCMC
Specialization/Generalization (D)
D. Single relation with multiple type attributes
Better for overlapping, could be disjoint
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 37
International University, VNU-HCMC Subclasses: Three Approaches
1. Object-oriented: One relation per subset of
subclasses, with all relevant attributes.
2. Use nulls: One relation; entities have NULL in
attributes that don’t belong to them.
3. E/R style: One relation for each subclass: –Key attribute(s).
–Attributes of that subclass.
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 38
International University, VNU-HCMC
Example: Subclass -> Relations name manf Beers isa color Ales
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 39
International University, VNU-HCMC Object-Oriented Name Manf Bud Anheuser -Busch Beers name manf Beers Name Manf Color Summerbrew Pete’s Drak Ales isa color Ales
Good for queries like “find the color of ales made by Pete’s.”
Assoc. Prof. Nguyen Thi Thuy Loan, PhD 40