AnnouncementThe Relational Data ModelA Relation is a TableSchemasWhy Relations?From E/R Diagrams to RelationsEntity Set -> RelationRelationship -> RelationCombining RelationsCombining Relations (II)Risk with Many-Many RelationshipsHandling Weak Entity SetsExampleCase StudySample SolutionSubclasses: Three ApproachesSlide 17Object-OrientedE/R StyleUsing NullsSlide 21Slide 22Slide 23Slide 241Announcement•Recitation timeBefore midterm: 6-7pm, by Earl WagnerAfter midterm: 5-6pm, by Yi Qiao•Newsgroup safe to subscribeWill not cause you to added to the CS mailing listSend all course related questions there for timely response (unless privacy needed)2The Relational Data ModelTablesSchemasConversion from E/R to Relations3A Relation is a Tablename manfWinterbrew Pete’sBud Lite Anheuser-BuschBeersAttributes(columnheaders)Tuples(rows)4Schemas•Relation schema = relation name and attribute list.Optionally: types of attributes.Example: Beers(name, manf) or Beers(name: string, manf: string)•Database = collection of relations.•Database schema = set of all relation schemas in the database.5Why Relations?•Very simple model.•Often matches how we think about data.•Abstract model that underlies SQL, the most important database language today.6From E/R Diagrams to Relations•Entity set -> relation.Attributes -> attributes.•Relationships -> relations whose attributes are only:The keys of the connected entity sets.Attributes of the relationship itself.7Entity Set -> RelationRelation: Beers(name, manf)Beersname manf8Relationship -> RelationDrinkers BeersLikesLikes(drinker, beer)FavoriteFavorite(drinker, beer)MarriedhusbandwifeMarried(husband, wife)name addr name manfBuddies12Buddies(name1, name2)9Combining Relations•OK to combine into one relation:1. The relation for an entity-set E 2. The relations for many-one relationships from E (“many”) to F•Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer).10Combining Relations (II)•The combined relation schema consists ofAll attributes of EThe key attributes of FAny attributes belonging to the relationship R•Can we combine one-one relationship?•What about many-many?11Risk with Many-Many Relationships•Combining Drinkers with Likes would be a mistake. It leads to redundancy, as:name addr beerSally 123 Maple BudSally 123 Maple MillerRedundancy12Handling Weak Entity Sets•Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes.•A supporting relationship is redundant and yields no relation.13ExampleLogins HostsAtname nameHosts(hostName, location)Logins(loginName, hostName, billTo)At(loginName, hostName, hostName2)Must be the samebillToAt becomes part ofLoginslocationWhat if “At” has some attributes ?14Case StudycitiescountiesstatesPopu.nameLocatedCo. Popu.Co. namecapitalsCi. Popu.Ci. nameBelongs-to15Sample Solution•States (name, popu)•Conuties (co name, state name, co popu)•Cities (ci name, co name, state name, ci popu)•Capitals (state name, ci name, co name)16Subclasses: Three Approaches1. Object-oriented : One relation per subset of subclasses, with all relevant attributes.2. Use nulls : One relation; entities have NULL in attributes that don’t belong to them.3. E/R style : One relation for each subclass:Key attribute(s).Attributes of that subclass.17ExampleBeersAlesisanamemanfcolor18Object-Orientedname manfBud Anheuser-BuschBeersname manf colorSummerbrew Pete’s darkAlesGood for queries like “find thecolor of ales made by Pete’s.”19E/R Stylename manfBud Anheuser-BuschSummerbrew Pete’sBeersname colorSummerbrew darkAlesGood for queries like“find all beers (includingales) made by Pete’s.”20Using Nullsname manf colorBud Anheuser-Busch NULLSummerbrew Pete’s darkBeersSaves space unless there are lotsof attributes that are usually NULL.21Isastafffacultystudent assistantCase StudyemployeessnosalarynamepositionrankPercentageTime22Relations: employee(ssno, name, salary)staff(ssno, name, salary,position)faculty(ssno, name, salary, rank)studentassistant(ssno, name, salary, percentagetime) Key: ssno for all the relationsIsastafffacultyStudent assistantemployeessnosalarynamepositionrankTime percentageSubclass – Object-oriented23Relations: employee(ssno, name, salary)staff(ssno, position) faculty(ssno, rank) studentassistant(ssno, percentage_time) Key: ssno for all relationsIsastafffacultystudent assistantSubclass – E/R StyleemployeessnosalarynamepositionrankPercentageTime24IsastafffacultyStudent assistantemployeessnosalarynamepositionrankPercentageTimeRelation:employee(ssno, name, salary, position, rank, percentage-time) Key : ssno as keyNote: Sometimes we add an attribute “jobType” to make queries easier.Subclass – null