CSCI 305 – Fall 2013

1. CSCI 305 – Fall 2013 The Entity-Relationship Model Based on Slides by Prof. Brian King

2. Why model? • HACKER mentality  BAD! • THINK before CODING  GOOD! • Modeling in any software development is an important discipline • Forces you to think abstractly about your program • Most modeling approaches involve graphical views that allow you to discuss with other teammates, other non-CS folks • Models are fluid, dynamic, easy to change • Released software is NOT! • Database modeling • Makes it easy to extract database schema that makes sense • Makes it easy to understand queries to be made • Learning to model will provide a formal process to take you from ideas to a database schema

3. First, how do we get ideas about a database to an ER design? • Our first topic • Convert an ER diagram to a full relational schema • Our next topic

4. Entity Relationship Model • The E-R Model • A very common database modeling method • An abstract and conceptual representation of data • Provides mechanism to sketch database schema designs • Depict structure of data • Constraints can be depicted, operation cannot • Use during analysis and design of a database-centric system • For now, take a step back and forget about relations and tables • Yields a graphical view of model called Entity-Relationship Diagram • ER Diagram • ERD

5. ER Diagrams • Entity: • An abstract object, or "thing“, similar to OOP concept of object • Entity Set: • A collection of similar entities • Attributes: • Properties of the entities in that set • Simple values (e.g, integers, characters, dates, strings) • ERD: • Entity set = rectangle • Attribute = oval • Has a line to the rectangle representing its entity set

6. Examples • Entity Set: Employee • Every employee entity in the set has three attributes SSN name Employee hireDate

7. name manf Beers Example • Entity set Beers has two attributes, name and manf • Each Beers entity has values for these two attributes • (Railbender,Erie Brewing)

8. Instance of an Entity Set • An instance of an entity set stores a specific set of entities • Each entity is a tuple containing specific values for each attribute • This is conceptual only (database not created yet!)

9. Relationships • A relationship connects two or more entity sets • In ERD, we use a diamond, with lines to each of the entity sets involved

10. name addr name manf Sells Bars Beers Bars sell some beers. license Drinkers like some beers. Frequents Likes Note: license = beer, full, none Drinkers frequent some bars. Drinkers name addr Example: Relationships

11. Example • Students Take Courses • Professors Teach Courses • Professors Advise Students

12. Relationship Set • An entity has a "value" • The value of its attributes • An entity set has a "value" • The set of entities that belong to it • Example: • The set of all bars • The set of all beers • An instance of the entity set is a specific set of values • A relationship has a "value" • Called a relationship set • A set of entities with values for each related entity

13. Example: Relationship Set • Relationship Sells • We might have a relationship set like:

14. Multiplicities in relationships • Multiplicities specify a constraint between two entities • Tell us something about how members are related • Our multiplicity constraints: • Many-many • Many-one • One-one

15. Visual Example

16. Many-One Relationships • R is a many-one relationship between E and F if each entity in E can be connected to at most one entity in F by R • Example: • Favorite, from Drinkers to Beers • A drinker can have at most one favorite beer • A beer can be the favorite of any number of drinkers, including none

17. One-One Relationships • R is a one-one relationship between E and F if R is many-one between E and F and many-one between F and E • Example: • Relationship BestSeller between entity sets Manfs and Beers • A beer cannot be made by more than one manufacturer, and no manufacturer can have more than one best-seller (assuming no ties)

18. Many-Many Relationships • R is a many-many relationship between E and F if R is neither one-one or many-one • Example: • A relationship Sells between beers and bars • a bar sells many beers • a beer is sold by many bars

19. Representing "Multiplicity" • many-one relationship • arrow entering the "one" side • one-one relationship • arrow entering both sides • many-many relationship • no arrows

20. Example

21. Example: Runs President Country

22. Multiway Relationships • Sometimes, we need a relationship that connects more than two entity sets • Not often. Occasionally three entity sets are related, very rarely more • 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

23. Example: MultiwayRelationship name addr name manf Bars Beers license Preferences Drinkers name addr

24. Relationships as roles • No restriction on quantity of relationships between entity sets • When you have more than one distinct relationship path between entity sets, useful to consider the "roles" between entity sets • The same entity sets can have different roles • Called Parallel Relationships EnrolledIn Student Course TA

25. Relationships as roles • Sometimes a single entity set will appear more than once in a relationship • Label the lines with the role of the entity for clarity • Example: • A course can have other courses as a prerequisite Requirement Course Prereq Requires

26. Attributes on Relationships • Sometimes it is convenient to attach an attribute to a relationship • The value of the attribute depends on the value of the related entities in the relationship set price name manf address name Sells Bar Beer

27. Multiway to Binary • Some data models often limit relationships to be binary • ER does not restrict this; yet it is good to know how to convert to binary • Solution: Create connecting entity sets whose entities are the tuples in the relationship set • Example: Person Sales Bar Beer Date

28. Multiway to Binary Person Person -Of Date Sale Bar-Of Beer-Of Bar Beer

29. Better… rethink your strategy Person Date Visits Purchases Sells Beer Bar

30. Subclasses • Subclass • A special case of an entity set • F is a subclass of E if each entity in F is an entity of E • F must have at least one attribute or participate in at least one relationship that E does not • (OOP idea of a subclass) • Example: • Ales are a kind of beer • Comedy is a kind of movie type • Dogs are a kind of Animal

31. Subclass in an ERD • In ERD, use isa relationship with a triangle • Isa triangles indicate the subclass relationship between entities • Points to the superclass • Example Beers name manf isa Ales color

32. E/R vs. OO subclasses • IN OO, objects are in one class only • Subclasses inherit from superclasses • E/R entities have representatives in all subclasses to which they belong • Rule: if entity e is represented in a subclass, then e is represented in the superclass (and recursively up the tree) • Example (from book): • Movie: Who Framed Roger Rabbit • Movie isa Cartoon • Movie isa Murder-Mystery • Will have attributes for all the above

33. Exercise 4.1.3

34. Section 4.2 • Section 4.2 – What makes a good design for an ER model? • READ IT • Summary: • KISS • Avoid redundancy, unnecessary entities and relationships • Stick to the spec • Choose the right design elements • Know when to use an attribute vs. an entity

35. Sec. 4.3 Constraints in the E/R model

36. Key • Key • Consistent with what we have learned • A key is a set of attributes for one entity set s.t. no two entities in this set agree on all attributes in the key • Every entity set must have a key • In your ERD: • Underline the key attributes(s) • In an isa hierarchy, only the root entity set has a key (and serves as key for all subclasses)

37. Example: Beers name manf isa Ales color

38. Example: Multi-attribute Key dept number hours room Courses

39. Referential Integrity • Referential integrity – says a value in one context must appear in another. • In terms of cardinality, it's a "one-and-only-one" constraint • IN E/R diagram, enforced with a "rounded" arrow. • Rounded arrow end indicates one and only one entity must exist in relationship • Example • We'll introduce "crow's foot notation" shortly, which provides a common means for representing cardinality

40. Degree Constraints • Attach a bounding number to an edge connecting an entity to limit the number of entities in a relationship

41. Weak Entity Sets • With a specified relationship, sometimes the key for an entity is not enough to enforce uniqueness • Example: Consider a Plays-On relationship between Players and Team • Players have two attributes: name and number; Team has one: name • A name is a possible key for a player • but there might be two players out there with the same name • A number for a player is not a key • Different teams can use the same player numbers • However, we could use number with the team name • We say that Players is a weak entity set

42. Weak Entity Set • Entities of a weak entity set can not be determined alone • Entity set E is said to be weak if, in order to identify entities of E uniquely, we need to: • follow one or more many-one relationships from E • include the key of those related entities • These many-one relationships are called supporting relationships for E • The corresponding entity sets reached from E are supporting entity sets.

43. IN E/R Diagrams number name Players Double rectangle for the weak entity set Double diamond for supporting many-one relationship Plays-On Rounded arrow – because each player needs a team (this assists with the key) Teams name

44. Weak Entity Set Rules • Requirement for Weak Entity Sets: • Its key consists of zero or more of its own attributes AND keys from supporting entities sets • Formally stated requirements for Supporting Relationship R between weak set E and Supporting Set F • R must be a binary, many-one from E to F • R must have referential integrity from E to F • each E must have exactly one F • (i.e, must have one_and_only_one end (i.e. rounded arrow) • The attributes that F supplies for E must be the key attributes of F • If F is weak, then there must exist another supporting set G to which F is connected by a supporting relationship

45. Don't Overuse Weak Entity Sets! • Beginners often doubt that anything could be a key by itself • Often end up with many weak entity sets • In reality, we usually create unique identifiers for entity sets • SSN • Driver's license number • Student ID • Weak entity sets are used when there is no "global authority" capable of creating unique IDs • Example: It is unlikely to ever have a global mechanism for assigning unique player numbers across all teams in the world!

46. Entity Sets Vs. Attributes • An entity set should satisfy at least one of the following conditions: • It is more than the name of something; it has at least one non-key attribute • It is the "many" in a many-one or many-many relationship. • An attribute should not be introduced if it is redundant • You should not have to change the same information in two or more places! • We will deal with this when we explore FDs next chapter

47. Example: GOOD Beers name BrewedBy name Manf addr • Manfs deserves to be an entity because of the non-key attribute addr • Beers deserves to be an entity set because it is the "many" of the many-one relationship.

48. Example: BAD Beers name manf BrewedBy name Manf addr Manufacturer of the beer is stated twice: as an attribute AND a related entity!

49. Example: BAD Beers name manf addr Manufacturer address will be stored repeatedly

50. Example: BAD Beers name BrewedBy name Manf Since the manufacturer is nothing but a name, and is not at the "many" end, it should not be an entity set.