translation of eer model into relational model
DESCRIPTION
Translation of EER model into relational model. Jose M. Peña [email protected]. Real world. Queries. Answers. Model. Databases. Processing of queries and updates. DBMS. Access to stored data. Physical database. Overview. Translation ER/EER to Relational. - PowerPoint PPT PresentationTRANSCRIPT
![Page 2: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/2.jpg)
OverviewReal world
ModelQueries Answers
Databases
Physical database
DBMS Processing of queries and updates
Access to stored data
![Page 3: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/3.jpg)
Translation ER/EER to Relational
Migrate from mini world model to a model understandable to a DBMS
![Page 4: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/4.jpg)
EER model for the COMPANY database
![Page 5: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/5.jpg)
ER to Relations
Step 1: Mapping Regular Entity TypesFor each strong entity type R, create a relation E that has the same simple attributes as R.
• Derived attributes are not stored.• Composite attributes are not stored, their component ones are stored.• Multivalued attributes are treated later.
![Page 6: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/6.jpg)
PROJECT( Number, Name, Location)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
DEPARTMENT ( Number, Name)
Composite attributes are not stored, their component ones are stored.
”Location”: multivalued attributes are treated later.
”Number_of_employee”: derived attribute are not stored.
![Page 7: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/7.jpg)
ER to RelationsStep 5: Mapping M:N Relationship Types
For each binary M:N relationship, identify the relations S and T that correspond to the connected entity types. Create a new relation R and use the primary keys from S and T as foreign keys and primary keys in R. If there are attributes on the relation these are also added to R.
On delete/update CASCADE ?!
S T
RM N
PKS PKT
PKS PKT Ratt
S T
R
PKS PKTRatt
![Page 8: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/8.jpg)
DEPARTMENT( Number, Name)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
PROJECT( Number, Name, Location)
WorksOn( Hours)Ssn, Number,
![Page 9: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/9.jpg)
ER to Relations
Step 4: Mapping 1:N Relationship Types1. For each binary 1:N relationship, identify the relation S
that represents the entity type on the N-side of the relationship type, and relation T that represents the entity type on the 1-side of the relationship type. Include as a foreign key in S the primary key of T. If there are attributes on the relation these are also added to S.
On delete/update CASCADE ?!
S T
RN 1
PKS PKTRatt
PKT
PKS PKT Ratt
T
S
![Page 10: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/10.jpg)
DEPARTMENT( Number, Name, Location)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
PROJECT ( Number, Name)
WorksOn( Hours)Ssn, Number,
SupervisorSSN,...)
![Page 11: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/11.jpg)
ER to Relations
Step 4: Mapping 1:N Relationship Types1. For each binary 1:N relationship, identify the relation S
that represents the entity type on the N-side of the relationship type, and relation T that represents the entity type on the 1-side of the relationship type. Include as a foreign key in S the primary key of T. If there are attributes on the relation these are also added to S.
2. Implement as M:N relationship (unlike M:N relationship, now PK is PK(S)). Convenient if few tuples are participate in the relationship.
On delete/update CASCADE ?!PKS PKT
PKS PKT Ratt
S T
R
S T
RN 1
PKS PKTRatt
![Page 12: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/12.jpg)
DEPARTMENT( Number, Name, Location)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
PROJECT ( Number, Name)
WorksOn( Hours)Ssn, Number,
SupervisorSSN,...)
Supervision( Ssn, SupervisorSSN)
![Page 13: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/13.jpg)
ER to RelationsStep 3: Mapping 1:1 Relationship Types1. Implement as 1:N relationship (prefer the entity type with
total participation, if any, as the entity type to which the foreign key is added). Convenient if few tuples participate in the relationship.
![Page 14: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/14.jpg)
PROJECT( Number, Name, Location)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
DEPARTMENT ( Number, Name, Manager)
![Page 15: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/15.jpg)
ER to RelationsStep 3: Mapping 1:1 Relationship Types1. Implement as 1:N relationship (prefer the entity type with
total participation, if any, as the entity type to which the foreign key is added). Convenient if few tuples participate in the relationship.
2. For each binary 1:1 relationship B, identify the relations S and T that correspond to the incoming entity types. Merge S and T into a single relation R. Set the primary key of S or T as the primary key of R. Do not forget the attributes of the relationship type. Indicated only when S and/or T with total participation.
S T
R1 1
PKS PKTRatt
PKS PKT RattS
![Page 16: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/16.jpg)
ER to RelationsStep 2: Mapping Weak Entity Types
For each weak entity type W with owner entity type E, create a relation R that has the same simple attributes as W, also add (as a foreign key) the primary key attributes from the relation that corresponds to E.
Primary key attributes in R are composed of the primary key attributes from E and the partial key from W.
On delete/update CASCADE ?!
![Page 17: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/17.jpg)
DEPARTMENT( Number, Name)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
PROJECT ( Number, Name)
WorksOn( Hours)Ssn, Number,
SupervisorSSN,...)
DEPENDENT( Ssn, Name, Sex, Birth_date, …)
![Page 18: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/18.jpg)
ER to RelationsStep 2: Mapping Weak Entity Types
For each weak entity type W with owner entity type E, create a relation R that has the same simple attributes as W, also add (as a foreign key) the primary key attributes from the relation that corresponds to E.
Primary key attributes in R are composed of the primary key attributes from E and the partial key from W.
What if the
owner entity is
also weak ?
On delete/update CASCADE ?!
![Page 19: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/19.jpg)
ER to Relations
Step 7: Mapping N-ary Relationship Types
For each N-ary relationship with N>2, create a new relation S that contains the primary keys from the incoming relations as foreign keys. Primary key of S are those keys that come from cardinality constraints ≠ 1. Do not forget the attributes of the relationship type.
On delete/update CASCADE ?!
![Page 20: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/20.jpg)
N-ary relationships
Example. A person works as an engineer at one company and as a gym instructor at another company.
Employee JobType
Company
works asN M
K
Employee(PN, …)
JobType(JID, …)
Company(CID, …)
Works_as(PN,JID, CID)
![Page 21: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/21.jpg)
ER to Relations
Step 6: Mapping multivalued attributesFor each multivalued attribute A in R, create a new relation P that contains one attribute for each attribute in A and the primary key K of R as a foreign key. The primary key of P is the combination of K and some suitable simple attributes of A.
PersonName
PN AddressAddress Street
PostNum
Person(PN, Name)
Address(PN, PostNum, Street)
On delete/update CASCADE ?!
![Page 22: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/22.jpg)
ER to Relations
Materializing the relationship:
M:N implies two joins 1:N implies one or two joins 1:1 implies zero, one or two joins N-ary implies N joins.
![Page 23: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/23.jpg)
DEPARTMENT( Number, Name)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
PROJECT( Number, Name, Location)
WorksOn( Hours)Ssn, Number,
SELECT E.Fname, P.Name, W.HoursFROM EMPLOYEE E, PROJECT P, WorksOn W WHERE W.SSN = E.SSN AND W.Number = P.Number
![Page 24: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/24.jpg)
ER to Relations
Materializing the relationship:
M:N implies two joins 1:N implies one or two joins 1:1 implies zero, one or two joins N-ary implies N joins.
![Page 25: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/25.jpg)
PROJECT( Number, Name, Location)
EMPLOYEE(Ssn, Bdate, Fname, Minit, Lname, …)
DEPARTMENT ( Number, Name, Manager)
SELECT E.Fname, D.NameFROM EMPLOYEE E, DEPARTMENT DWHERE D.Manager = E.Ssn;
![Page 26: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/26.jpg)
EER to Relations
Step 8: Mapping Specialization
a) create relations for each class (super+sub)
X(ID, A)
Y(ID, B)
Z(ID, C)
X
ZY
ID A
BC
U
U
* Always works.
![Page 27: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/27.jpg)
EER to Relations Step 8: Mapping Specialization
b) subclass relations only
Y(ID, A, B)
Z(ID, A, C)
X
ZY
ID A
BC
U
U
* Works only for total participation.* Overlapping implies duplication.
![Page 28: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/28.jpg)
EER to Relations Step 8: Mapping Specialization
c) single relation with one type attribute and all subclass attributes
X(ID, A, Type, B, C)
X
ZY
d
ID A
BC
U
U
* Works only for disjoint subclasses.* May produce many NULLs if many subclass-specific attributes exist.
No needed if specialization is attribute-defined.
![Page 29: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/29.jpg)
EER to Relations Step 8: Mapping Specialization
d) single relation with multiple type attributes and all subclass attributes
X(ID, A, IsY, B, IsZ, C)
X
ZY
ID A
BC
U
U
* Always works.* May produce many NULLs if many subclass-specific attributes exist.
![Page 30: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/30.jpg)
EER to Relations
Materializing the superclass/subclasses Option a, inner/outer join. Option b, outer join (against theory…). Option c, done. Option d, done.
May be more space inefficient but more time efficient.
![Page 31: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/31.jpg)
EER to Relationsa) create relations for each class (super+sub)
X(ID, A)
Y(ID, B)
Z(ID, C)
X
ZY
ID A
BC
U
U
SELECT X.ID, X.A, Y.BFROM X LEFT JOIN Y ON X.ID = Y.ID;
![Page 32: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/32.jpg)
EER to Relationsb) subclass relations only
Y(ID, A, B)
Z(ID, A, C)
X
ZY
ID A
BC
U
U
SELECT Y.ID, Z.ID, Y.A, Z.A,Y.B, Z.CFROM Y FULL OUTER JOIN Z ON Y.ID = Z.ID;
![Page 33: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/33.jpg)
EER to Relations Step 9: Mapping of Union Types
a) If the defining superclasses have different primary keys, introduce a surrogate key in the union relation and use it as a foreign key in the superclasses.
CompanyID
PersonID
Y(CompanyID, B, XID)
Z(PersonID, C, XID)
X(XID, A)X
ZY
u
A
B C
U
![Page 34: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/34.jpg)
EER to Relations Step 9: Mapping of Union Types
b) If the defining superclasses use the same primary key, no need for surrogate key.
Y(PersonID, B)
Z(PersonID, C)
X(PersonID, A)
PersonID
PersonID
X
ZY
u
A
B C
U
* No FKs in Y and Z, unless total participation (correct figure 7.7 in the book)
![Page 35: Translation of EER model into relational model](https://reader035.vdocuments.us/reader035/viewer/2022081512/5681664c550346895dd9c8b4/html5/thumbnails/35.jpg)
Example: LARM days
Person Organization
Teacher Student
Responsible Exhibition
PID
PhoneNum
Name
OrgNr
Address
Street
Town
PostNum
is-contact-for
organizes
o
u
shows
U
U U
N M
N 1
1 1
UID
Description