11The Relational Data ModelTablesSchemasConversion from E/R to Relations2A Relation is a Tablename manfWinterbrew Pete’sBud Lite Anheuser-BuschBeersAttributes(columnheaders)Tuples(rows)3SchemasRelation schema = relation name andattribute list. Optionally: types of attributes. Example: Beers(name, manf) orBeers(name: string, manf: string)Database = collection of relations.Database schema = set of all relationschemas in the database.24Why Relations?Very simple model.Often matches how we think aboutdata.Abstract model that underlies SQL, themost important database languagetoday.5From E/R Diagrams to RelationsEntity set -> relation. Attributes -> attributes.Relationships -> relations whoseattributes are only: The keys of the connected entity sets. Attributes of the relationship itself.6Entity Set -> RelationRelation: Beers(name, manf)Beersname manf37Relationship -> RelationDrinkers BeersLikesLikes(drinker, beer)FavoriteFavorite(drinker, beer)MarriedhusbandwifeMarried(husband, wife)name addr name manfBuddies12Buddies(name1, name2)8Combining Relations OK to combine into one relation: The relation for an entity-set E The relations for many-one relationshipsof which E is the “many.” Example: Drinkers(name, addr) andFavorite(drinker, beer) combine tomake Drinker1(name, addr, favBeer).9Risk with Many-Many RelationshipsCombining Drinkers with Likes would bea mistake. It leads to redundancy, as:name addr beerSally 123 Maple BudSally 123 Maple MillerRedundancy410Handling Weak Entity SetsRelation for a weak entity set mustinclude attributes for its complete key(including those belonging to otherentity sets), as well as its own, nonkeyattributes.A supporting relationship is redundantand yields no relation (unless it hasattributes).11ExampleLogins HostsAtname nameHosts(hostName, location)Logins(loginName, hostName, billTo)At(loginName, hostName, hostName2)Must be the samebillToAt becomes part ofLoginslocation12Subclasses: Three Approaches Object-oriented : One relation per subset ofsubclasses, with all relevant attributes. Use nulls : One relation; entities have NULLin attributes that don’t belong to them. E/R style : One relation for each subclass: Key attribute(s). Attributes of that subclass.513ExampleBeersAlesisanamemanfcolor14Object-Orientedname manfBud Anheuser-BuschBeersname manf colorSummerbrew Pete’s darkAlesGood for queries like “find thecolor of ales made by Pete’s.”15E/R Stylename manfBud Anheuser-BuschSummerbrew Pete’sBeersname colorSummerbrew darkAlesGood for queries like“find all beers (includingales) made by Pete’s.”616Using Nullsname manf colorBud Anheuser-Busch NULLSummerbrew Pete’s darkBeersSaves space unless there are lotsof attributes that are usually