Entity-Relationship Model

1. Entity-Relationship Model

2. Database Modeling • The process of designing a database begins with: • an analysis of what information the database must hold, and • the relationships among components of that information. • The structure of the database, called the database schema is specified in a suitable notation for expressing designs. • We will use theEntity-Relationship (E/R) model, which is graphical in the nature. Ideas E/R design Relational schema Relational DBMS Mechanical process Abstract design Concrete design

3. Elements of the E/R Model • Entities are like objects in OO programming. Entity sets are like the classes. • However, E/R is a static model, so there are no methods associated with the entities. Movies • Attributes are similar to the attributes in OO programming. • However, they are atomic, i.e. only numbers and strings are allowed. Title • Relationships are connections among two or more entity sets. Stars-in

4. Studios name address Example filmType length year name address title Movies Stars-In Stars Owns

5. Visualizing E/R Relationships • It’s often helpful (for us) to represent E/R relationships by a table, each row representing a list (it can be pair) of entities participating in the relationship. • For instance the Stars-in relationship could be thought of as a table: • Movies | Stars • Basic Instinct | Sharon Stone. • Total Recall | Arnold Schwarzeneger. • Total Recall | Sharon Stone. • This table is called the relationship set for the relationship. • The members of the relationship set are the rows of the table.

6. Movies Owns Studios Multiplicity of Relationships • A relationship R is many-one from E to F • if any member of E can be associated to at most one member of F. • Equivalently, we say that R is one-many from F to E. • Arrows indicate the multiplicity of a relationship. • Arrow means “at most one.” It does not guarantee the existence of an entity of the set pointed to.

7. Multiplicity (Cont.) • If a relationship R is both many-one from E to F and many-one from F to E, then we say that R is one-one • Arrows pointing to both E and F. Studios Runs Presidents • If a relationship R is neither many-one from E to F or from F to E, then we say R is many-many. Movies Stars-In Stars

8. Example Movies Stars-In Stars Owns No arrow now! Studios What could go wrong with this design? Sometimes binary relationships aren’t enough!

9. Relating studios, movies and stars • Suppose that we want to know: Which stars a studio is paying for a specific movie? • As long as a movie is produced by a single studio (having the arrow) the previous organization works very well. Why? • Because we can relate a studio s through a specific movie m with the stars sti forming valid triples: • (s, m, st1), (s, m, st2),…, (s, m, stk) • Suppose now that movies can be produced (and owned) by not just a single studio but, by some studios (no arrow). • Now if we try to create the above triples they can be invalid.

10. Why the triples can be invalid? • Suppose studio s1 is paying a star st1 for the movie m • and • studio s2 is paying st2 for the same movie m. • Then we will have the following triples: • (s1, m, st1) (s1, m, st2) (s2, m, st1) (s2, m, st2) • The second and the third triples aren’t valid. • If we consider the collection of all the valid triples, it is nothing else but a three way relationship between Studios, Movies and Stars

11. Contracts Stars Movies Studios Solution: Three-way relationship • Relationship Contractsinvolves a studio,a star,and a movie. • It says that a studio has contracted a particular star in a particular movie. • Arrow pointing to Studiosmeans: • If we select one entity from Stars and one entity from Movies, these entities (together) are related to at most one entity in Studios.

12. An entity set can appear two or more times in a relationship. • Each line to the entity set represents a different role. Original Sequel-of Movies Sequel • A movie may have many sequels, but for each sequel there is only one original movie. Roles in a relationship

13. Relationship Set Husband Wife Bob Ann Joe Sue … … husband wife Another Example Married Drinkers

14. Another Example Relationship Set Buddy1 Buddy2 Bob Ann Joe Sue Ann Bob Joe Moe … … Buddies 1 2 Drinkers

15. title year name addr salary Movies Stars Contracts length filmType Studios addr name Attributes on Relationships We wish to record the salary associated with a contract. Can’t associate it with the star; A star might get different salaries for different movies. Doesn’t make sense to associate it with a studio or with a movie. However, it’s appropriate to associate a salary with the (star, movie, studio)triple in the relationship set for the Contractsrelationship.

16. “Bars-Beer-Drinkers” (BBD) Example • Bars sell some beers. • Drinkers like some beers. • Drinkers frequent some bars. • What would be the E/R diagram?

17. “Bars-Beer-Drinkers” (BBD) Example name addr name manf Bars Beers Sells Bars sell some beers. license Drinkers like some beers. Frequents Likes Note: license = beer, full, none Drinkers frequent some bars. Drinkers name addr Why we need it?

18. BBD Multiway Relationship • Suppose that drinkers will only drink certain beers at certain bars. • Our three binary relationships Likes, Sells, and Frequents do not allow us to make this distinction. But a 3-way relationship would.

19. BBD Multiway Relationship name addr name manf Bars Beers license Preferences Drinkers name addr

20. A Typical Relationship Set Bar Drinker Beer Joe’s Bar Ann Miller Sue’s Bar Ann Bud Sue’s Bar Ann Pete’s Ale Joe’s Bar Bob Bud Joe’s Bar Bob Miller Joe’s Bar Cal Miller Sue’s Bar Cal Bud Lite

21. Likes Drinkers Beers Favorite Multiple Relationships Between Two Entity Sets

22. Best- seller Manfs Beers • Some beers are not the best-seller of any manufacturer, so a rounded arrow to Manfs would be inappropriate. • But a manufacturer has to have a best-seller (we assume they are beer manufacturers). “Exactly one” Multiplicity