Entity-Relationship ModelPurpose of E/R ModelEntity SetsE/R DiagramsExampleRelationshipsSlide 7Relationship SetSlide 9Multiway RelationshipsSlide 11A Typical Relationship SetMany-Many RelationshipsIn Pictures:Many-One RelationshipsSlide 16Slide 17One-One RelationshipsSlide 19Representing “Multiplicity”Slide 21Slide 22In the E/R DiagramAttributes on RelationshipsSlide 25Equivalent Diagrams Without Attributes on RelationshipsSlide 27RolesSlide 29Slide 30SubclassesSubclasses in E/R DiagramsSlide 33E/R Vs. Object-Oriented SubclassesSlide 35Subclass/Superclass RelationshipsSlide 37KeysKeys in E/R DiagramsExample: name is Key for BeersExample: a Multi-attribute KeyModeling of ConstraintsSingle-Value ConstraintReferential IntegrityReferential integrity constraintsWeak Entity SetsSlide 47In E/R DiagramsWeak Entity-Set RulesDesign TechniquesAvoiding RedundancyExample: GoodExample: BadSlide 54Entity Sets Versus AttributesSlide 56Slide 57Slide 58Don’t Overuse Weak Entity SetsWhen Do We Need Weak Entity Sets?Case Study 1Design 1: badDesign 2: goodCase Study 2Slide 65Slide 661Entity-Relationship ModelDiagramsClass hierarchiesWeak entity sets2Purpose of E/R Model•The E/R model allows us to sketch database designs.Kinds of data and how they connect.Not how data changes.•Designs are pictures called entity-relationship diagrams.•Later: convert E/R designs to relational DB designs.3Entity Sets•Entity = “thing” or object.•Entity set = collection of similar entities.Similar to a class in object-oriented languages.•Attribute = property of (the entities of) an entity set.Attributes are simple values, e.g. integers or character strings.4E/R Diagrams•In an entity-relationship diagram:Entity set = rectangle.Attribute = oval, with a line to the rectangle representing its entity set.5Example•Entity set Beers has two attributes, name and manf (manufacturer).•Each Beers entity has values for these two attributes, e.g. (Bud, Anheuser-Busch)Beersname manf6Relationships•A relationship connects two or more entity sets.•It is represented by a diamond, with lines to each of the entity sets involved.7ExampleDrinkersaddrnameBeersmanfnameBarsnamelicenseaddrNote:license =beer, full,noneSellsBars sell somebeers.LikesDrinkers likesome beers.FrequentsDrinkers frequentsome bars.8Relationship Set•The current “value” of an entity set is the set of entities that belong to it.Example: the set of all bars in our database.•The “value” of a relationship is a set of lists of currently related entities, one from each of the related entity sets.9Example•For the relationship Sells, we might have a relationship set like:Bar BeerJoe’s Bar BudJoe’s Bar MillerSue’s Bar BudSue’s Bar Pete’s AleSue’s Bar Bud Lite10Multiway Relationships•Sometimes, we need a relationship that connects more than two entity sets.•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.11ExampleBarsBeersDrinkersnamenameaddrmanfname addrlicensePreferences12A Typical Relationship SetBar Drinker BeerJoe’s Bar Ann MillerSue’s Bar Ann BudSue’s Bar Ann Pete’s AleJoe’s Bar Bob BudJoe’s Bar Bob MillerJoe’s Bar Cal MillerSue’s Bar Cal Bud Lite13Many-Many Relationships•Focus: binary relationships, such as Sells between Bars and Beers.•In a many-many relationship, an entity of either set can be connected to many entities of the other set.E.g., a bar sells many beers; a beer is sold by many bars.14In Pictures:many-many15Many-One Relationships•Some binary relationships are many -one from one entity set to another.•Each entity of the first set is connected to at most one entity of the second set.•But an entity of the second set can be connected to zero, one, or many entities of the first set.16In Pictures: many-one17Example•Favorite, from Drinkers to Beers is many-one.•A drinker has at most one favorite beer.•But a beer can be the favorite of any number of drinkers, including zero.18One-One Relationships•In a one-one relationship, each entity of either entity set is related to at most one entity of the other set.•Example: Relationship Best-seller between entity sets Manfs (manufacturer) and Beers.A beer cannot be made by more than one manufacturer, and no manufacturer can have more than one best-seller (assume no ties).19In Pictures: one-one20Representing “Multiplicity”•Show a many-one relationship by an arrow entering the “one” side.•Show a one-one relationship by arrows entering both entity sets.•Rounded arrow = “exactly one,” i.e., each entity of the first set is related to exactly one entity of the target set.21ExampleDrinkers BeersLikesFavorite22Example•Consider Best-seller between Manfs and Beers.•Some beers are not the best-seller of any manufacturer, so a rounded arrow to Manfs would be inappropriate.•But a beer manufacturer has to have a best-seller.23In the E/R DiagramManfs BeersBest-sellerCan you come up with another example ?24Attributes on Relationships•Sometimes it is useful to attach an attribute to a relationship.•Think of this attribute as a property of tuples in the relationship set.25ExampleBars BeersSellspricePrice is a function of both the bar and the beer,not of one alone.Any other example?26Equivalent Diagrams Without Attributes on Relationships•Create an entity set representing values of the attribute.•Make that entity set participate in the relationship.27ExampleBars BeersSellspricePricesNote convention: arrowfrom multiway relationship= “all other entity setstogether determine aunique one of these.”28Roles•Sometimes an entity set appears more than once in a relationship.•Label the edges between the relationship and the entity set with names called roles.29ExampleDrinkersMarriedhusband wife Relationship SetHusband WifeBob AnnJoe Sue… …30ExampleDrinkersBuddies1 2 Relationship SetBuddy1 Buddy2Bob AnnJoe SueAnn BobJoe Moe… …Can you give an example of many-to-one relationship from the same entity set?31Subclasses•Subclass = special case = fewer entities = more properties.•Example: Ales are a kind of beer.Not every beer is an ale, but some are.Let us suppose that in addition to all the properties (attributes and relationships) of beers, ales also have the attribute