Transcript Data Modeling E-R Diagram To Relation Translation

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: [email protected]
Dale Roberts
1
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.
Dale Roberts
2
Entity Set -> Relation
Relation: Beers(name, manf)
name
manf
Beers
Dale Roberts
3
Relationship -> Relation
name
husband
addr
Drinkers
1
name
Likes
manf
Beers
2
Buddies
Favorite
wife
Married
Dale Roberts
Likes(drinker, beer)
Favorite(drinker, beer)
Buddies(name1, name2)
Married(husband, wife)
4
Combining Relations
OK to combine into one relation:
1.
2.
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).
Dale Roberts
5
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
Redundancy
Dale Roberts
6
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).
Dale Roberts
7
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”)
3/27/2016
Dale Roberts
8
TicketMaster Example
Dale Roberts
9
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
declares
rosters
Major
“Each student declares exactly one Major.”
“Each Major rosters zero to many Students.”
Dale Roberts
10
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.
Dale Roberts
11
Acknowledgements
McFadden and Hoffer. Database Management
Loney, Kevin. Oracle Database 10g The Complete
Reference
Ullman, Jeff. Database Systems The Complete Book.
DatabaseAnswers.com
3/27/2016
Dale Roberts
12