1 / 12

Department of Computer and Information Science, School of Science, IUPUI

Department of Computer and Information Science, School of Science, IUPUI. Data Modeling Translating E-R Diagrams to Relations. Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu. From E-R Diagrams to Relations. Entity set -> relation. Attributes -> attributes.

obelia
Download Presentation

Department of Computer and Information Science, School of Science, IUPUI

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Department of Computer and Information Science,School of Science, IUPUI Data Modeling Translating E-R Diagrams to Relations Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

  2. 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.

  3. Entity Set -> Relation Relation: Beers(name, manf) name manf Beers

  4. Likes husband 2 1 Favorite Buddies Likes(drinker, beer) Favorite(drinker, beer) wife Buddies(name1, name2) Married Married(husband, wife) Relationship -> Relation name name addr manf Drinkers Beers

  5. Combining Relations • OK to combine into one relation: • The relation for an entity-set E • The relations for many-one relationships of which E is the “many.” • Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer).

  6. Redundancy Risk with Many-Many Relationships • Combining Drinkers with Likes would be a mistake. It leads to redundancy, as: name addr beer Sally 123 Maple Bud Sally 123 Maple Miller

  7. Handling Weak Entity Sets • Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes. • A supporting relationship is redundant and yields no relation (unless it has attributes).

  8. Enterprise E-R Diagrams • Placing attributed in ovals, and listing every relationship with diamonds in okay in small models are in an academic exercise. • Enterprise E-R Diagrams contains hundreds of entities and thousands of attributes. • A more compact representation is necessary. • Rectangles are entities, lines are relationships, associative entities (use to resolve many-many) are diamonds. • Cardinality is included on relationship lines (0, 1, Many = “crows feet”)

  9. TicketMaster Example

  10. Relationship Naming • Relationships are labeled with verbs • Relationships go both directions, each with its own verb. • Relationships are read in both directions choosing the “clockwise” verb and cardinality on the terminating end of the relationship. Student Major declares rosters “Each student declares exactly one Major.” “Each Major rosters zero to many Students.”

  11. Logical vs Physical E-R Diagrams • E-R Tools support logical and physical diagrams. • Logical diagrams are 3NF and allow Many-Many relationships • Physical diagrams resolve Many-Many using associative entities • Performance tuning may denormalize (add redundancy) to the physical model in order to reduce joins and improve WHERE clause performance. • Recommendation: Never remove normalized entities, only add redundancy in a controlled way.

  12. Acknowledgements • McFadden and Hoffer. Database Management • Loney, Kevin. Oracle Database 10g The Complete Reference • Ullman, Jeff. Database Systems The Complete Book. • DatabaseAnswers.com

More Related