Transformation of ER Diagram to Relation Transformation of

Transformation of E/R Diagram to Relation

Transformation of E/R Diagram to Relation � Primary keys allow entity sets and relationship sets to be expressed uniformly as relations � E/R diagram collection of tables � For each entity set and each relationship set there is a unique table CSCE 520 2/12/2022 2

Transformation from E/R to Relation � Entity set: becomes a relation with the attributes of the entity set � Relationship: becomes a relation with attributes: ◦ Key of connected entity sets ◦ Attributes of the relationship CSCE 520 2/12/2022 3

Example E/R Diagram Name Age Weight Breed Dog Name Boards Owns Kennel Address Phone Pays Owner Name License # Amount Phone CSCE 520 2/12/2022 4

Entity Set to Relation Name Age Weight Breed Dog(Name, Breed, Age, Weight) CSCE 520 2/12/2022 5

Entity Set to Relation Name Amount Date Name Address Kennel Pays Owner Phone Pays(Kennel. Name, Owner. Name, Amount, Date) CSCE 520 2/12/2022 6

Combining Relations: Many-One Name Amount Date Name Address Kennel Pays Owner Phone Favorite Combine relation for entity of “many” side (Owner) with relationship set, i. e. , Owner’s-favorite(Name, Phone, Favorite. Kennel. Name) CSCE 520 2/12/2022 7

Combining Relations: Many-Many Name Address Kennel Pays Owner Phone Combining Owner with pays: Owner(Kennel. Name, Owner. Name, Phone) CSCE 520 2/12/2022 8

Many-many Relationship Kennel Name Owner Phone White Oak Mary 890 -4421 Little Creek Mary 890 -4421 White Oak Viki 987 -4456 White Oak Joe 334 -2234 2/12/2022 CSCE 520 Redundancy 9

Weak Entity Sets to Relation � Relation for a weak entity set must include attributes for its complete key (including those that belong to other entities) as well as its own, non-key attributes � Supporting relationship is redundant and does not yield a relation CSCE 520 2/12/2022 10

Example: Weak Entity Set Name Age Breed Dog Name Owns Owner Phone Owner(Owner. Name, Phone) Dog(Dog. Name, Owner. Name, Age, Breed) Owns(Dog. Name, Owner. Name 1, Owner. Name 2) Already in Dog Redundant! Must be the same CSCE 520 2/12/2022 11

Entity Sets with Subclasses Three approaches: 1. Object-oriented: each entity belongs to exactly one class. Create a relation for each class with all its attributes. 2. E/R style: create one relation for each subclass with only the key attributes and the attributes of the subclass. Entity is represented in all relations to whose subclass entity set it belongs. 3. Null values: create on relation with all attributes. Entities have null values in attributes that do not belong to them. CSCE 520 2/12/2022 12

Example Subclass Name Breed Dog ISA Show-Dog Rank CSCE 520 2/12/2022 13

Object-Oriented Approach Name Buddy Breed Mix Dog Name Pepper Breed Rank Germanshepherd 1 st Show dog CSCE 520 2/12/2022 14

E/R Approach Name Pepper Buddy Breed Germanshepherd Mix Dog Name Pepper Rank 1 st Show dog CSCE 520 2/12/2022 15

Null Values Name Buddy Pepper Breed Rank Mix NULL Germanshepherd 1 st Dog CSCE 520 2/12/2022 16

Comparisons � Object-Oriented: good for queries like “find all G. S. dogs that ranked 2 nd or above”. � E/R: good for “find all G. S. dogs (regardless of being a show dog or not) � Null values: may save space, but not good if too many attributes with null values. CSCE 520 2/12/2022 17