DOC PREVIEW
UT Dallas CS 6360 - CS-6360_ch09 ER and EER Mapping

This preview shows page 1-2-3-4-25-26-27-51-52-53-54 out of 54 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Chris Irwin Davis, Ph.D.!!Email: [email protected]!Phone: (972) 883-3574!Office: ECSS 4.705Chapter 9: ER and EER Mapping to Relational SchemaCS-6360 Database DesignChapter 9 Outline§Relational Database Design Using ER-to-Relational Mapping!§Mapping EER Model Constructs to RelationsRelational Database Design by ER- and EER-to-Relational Mapping§Design a relational database schema §Based on a conceptual schema design!§Seven-step algorithm to convert the basic ER model constructs into relations!§COMPANY database example!§Assume that the mapping will create tables with simple single-valued attributes!§Additional steps for EER modelCOMPANY ER DiagramCOMPANY Relation SchemaER-to-Relational Mapping Algorithm§Step 1: Mapping of Regular Entity Types!§For each regular entity type, create a relation R that includes all the simple attributes of E §Called entity relations •Each tuple represents an entity instanceER Diagram: Step 1Relational Model: Step 1ER-to-Relational Mapping Algorithm (cont’d.)§Step 2: Mapping of Weak Entity Types!§For each weak entity type, create a relation R and include all simple attributes of the entity type as attributes of R §Include primary key attribute of owner as foreign key attributes of RER Diagram: Step 2EssnRelational Model: Step 2ER-to-Relational Mapping Algorithm (cont’d.)§Step 3: Mapping of Binary 1:1 Relationship Types!§For each binary 1:1 relationship type !•Identify relations that correspond to entity types participating in R §Possible approaches: !• Foreign key approach • Merged relationship approach • Crossreference or relationship relation approachStep 3: Foreign Key Approach§Step 3: Mapping of Binary 1:1 Relationship Types!§Foreign key approach!§Choose one of the relations—S, say—and include as a foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. !§Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S.ER Diagram: Step 3Relational Model: Step 3Mgr_ssnStep 3: Merged Relationship Approach§Step 3: Mapping of Binary 1:1 Relationship Types!§Merged relationship approach!§An alternative mapping of a 1:1 relationship type is to merge the two entity types and the relationship into a single relation.!§This is possible when both participations are total, as this would indicate that the two tables will have the exact same number of tuples at all times.Step 3: Merged Relationship ApproachssnEMPLOYEEssnPROFILEfname minit address dnolnamessnEMPLOYEEfname minit address dnolnameStep 3: Merged Relationship Approach§Step 3: Mapping of 1:1 Relationship Types!§Crossreference or relationship relation approach!§The third option is to set up a third relation R for the purpose of cross-referencing the primary keys of the two relations S and T representing the entity types. !§As we will see, this approach is required for binary M:N relationships. The relation R is called a relationship relation (or “lookup table”), because each tuple in R represents a relationship instance that relates one tuple from S with one tuple from T.ER Diagram: Step 3 (optional)Crossreference or relationship relation approach (Step 3 alternative)ssn dno dnumberssnEMPLOYEE DEPARTMENTstart_dateCROSS REFERENCE TABLEER-to-Relational Mapping Algorithm (cont’d.)§Step 4: Mapping of Binary 1:N Relationship Types!§For each regular binary 1:N relationship type !•Identify relation that represents participating entity type at N-side of relationship type!•Include primary key of other entity type as foreign key in S •Include simple attributes of 1:N relationship type as attributes of SER Diagram: Step 4Relational Model: Step 4Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DnoDnameDnumberMgr_ssn Mgr_start_dateDlocationDnumberEssn Dependent_name BdateSex RelationshipEMPLOYEEDEPARTMENTDEPT_LOCATIONSDEPENDENTER-to-Relational Mapping Algorithm (cont’d.)§Alternative approach !•Use the relationship relation (cross-reference “relationship table”) option as in the third option for binary 1:1 relationshipsER-to-Relational Mapping Algorithm (cont’d.)§Step 5: Mapping of Binary M:N Relationship Types!§For each binary M:N relationship type!•Create a new relation S •Include primary key of participating entity types as foreign key attributes in S !•Include any simple attributes of M:N relationship typeER Diagram: Step 5Relational Model: Step 5Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DnoDname Mgr_ssn Mgr_start_dateDnumberPname Pnumber Plocation DnumEssn Pno HoursEssn Dependent_name BdateSex RelationshipEMPLOYEEDEPARTMENTPROJECTWORKS_ONDEPENDENTER-to-Relational Mapping Algorithm (cont’d.)§Step 6: Mapping of Multivalued Attributes!§For each multivalued attribute!•Create a new relation!•Primary key of R is the combination of A and K •If the multivalued attribute is composite, include its simple componentsER Diagram: Step 6Relational Diagram: Step 6Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DnoDnameDnumberMgr_ssn Mgr_start_dateDlocationDnumberPname Pnumber Plocation DnumEssn Pno HoursEssn Dependent_name BdateSex RelationshipEMPLOYEEDEPARTMENTDEPT_LOCATIONSPROJECTWORKS_ONDEPENDENTStep 7: Mapping of N-ary Relationship Types§Step 7: Mapping of N-ary Relationship Types!§For each n-ary relationship type R •Create a new relation (i.e. table) S to represent R •Include primary keys of participating entity types as foreign keys!•Include any simple attributes as attributesStep 7: Mapping of N-ary Relationship Types§Step 7: Mapping of N-ary Relationship Types!§The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. !§However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation Eʹ′ corresponding to E.Step 7Discussion and Summary of Mapping for ER Model Constructs (cont’d.)§In a relational schema, ER Relationship Types are not always represented explicitly!§Depends on Cardinality and Participation!§May represented by having two attributes A and B: one a primary key and the other a foreign


View Full Document

UT Dallas CS 6360 - CS-6360_ch09 ER and EER Mapping

Documents in this Course
Ch22(1)

Ch22(1)

44 pages

Ch21

Ch21

38 pages

Ch19

Ch19

46 pages

Ch18

Ch18

25 pages

Ch17

Ch17

21 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch05

Ch05

34 pages

Ch22

Ch22

45 pages

Ch21

Ch21

38 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch16

Ch16

17 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

34 pages

Ch06

Ch06

43 pages

Ch05

Ch05

34 pages

Ch04

Ch04

39 pages

Ch03(2)

Ch03(2)

36 pages

Ch02

Ch02

33 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

lab-manual

lab-manual

215 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch17

Ch17

25 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch04(1)

Ch04(1)

43 pages

Ch07

Ch07

40 pages

Ch03

Ch03

42 pages

Ch01

Ch01

36 pages

Ch02

Ch02

38 pages

Ch05

Ch05

41 pages

Ch06

Ch06

47 pages

Ch08

Ch08

39 pages

Ch17

Ch17

25 pages

Ch18

Ch18

24 pages

Ch09

Ch09

42 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04(1)

Ch04(1)

43 pages

Ch03

Ch03

42 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Load more
Download CS-6360_ch09 ER and EER Mapping
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view CS-6360_ch09 ER and EER Mapping and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view CS-6360_ch09 ER and EER Mapping 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?