1 / 24

Announcement

Announcement. 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). The Relational Data Model.

nerice
Download Presentation

Announcement

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Announcement • 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)

  2. The Relational Data Model Tables Schemas Conversion from E/R to Relations

  3. Attributes (column headers) Tuples (rows) A Relation is a Table name manf Winterbrew Pete’s Bud Lite Anheuser-Busch Beers

  4. Schemas • 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.

  5. Why Relations? • Very simple model. • Often matches how we think about data. • Abstract model that underlies SQL, the most important database language today.

  6. From 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.

  7. Entity Set -> Relation Relation: Beers(name, manf) name manf Beers

  8. Likes husband 2 1 Favorite Buddies Likes(drinker, beer) Favorite(drinker, beer) wife Buddies(name1, name2) Married Married(husband, wife) Relationship -> Relation name name addr manf Drinkers Beers

  9. Combining Relations • OK to combine into one relation: • The relation for an entity-set E • 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).

  10. Combining 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?

  11. Redundancy Risk with Many-Many Relationships • Combining Drinkers with Likes would be a mistake. It leads to redundancy, as: name addr beer Sally 123 Maple Bud Sally 123 Maple Miller

  12. Handling 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.

  13. Must be the same At becomes part of Logins Example name name Logins At Hosts location billTo Hosts(hostName, location) Logins(loginName, hostName, billTo) At(loginName, hostName, hostName2) What if “At” has some attributes ?

  14. Case Study Co. name Co. Popu. Popu. name Located counties states Belongs-to capitals cities Ci. name Ci. Popu.

  15. Sample 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)

  16. Subclasses: Three Approaches • Object-oriented: One relation per subset of subclasses, with all relevant attributes. • Use nulls: One relation; entities have NULL in attributes that don’t belong to them. • E/R style: One relation for each subclass: • Key attribute(s). • Attributes of that subclass.

  17. Example Beers name manf isa Ales color

  18. Object-Oriented name manf Bud Anheuser-Busch Beers name manf color Summerbrew Pete’s dark Ales Good for queries like “find the color of ales made by Pete’s.”

  19. E/R Style name manf Bud Anheuser-Busch Summerbrew Pete’s Beers name color Summerbrew dark Ales Good for queries like “find all beers (including ales) made by Pete’s.”

  20. Using Nulls name manf color Bud Anheuser-Busch NULL Summerbrew Pete’s dark Beers Saves space unless there are lots of attributes that are usually NULL.

  21. Case Study name salary ssno employee Isa faculty staff student assistant position Percentage Time rank

  22. Subclass – Object-oriented name salary ssno Relations: employee(ssno, name, salary) staff(ssno, name, salary,position) faculty(ssno, name, salary, rank) studentassistant(ssno, name, salary, percentagetime) Key: ssno for all the relations employee Isa faculty staff Student assistant position Time percentage rank

  23. Subclass – E/R Style name salary ssno Relations: employee(ssno, name, salary) staff(ssno, position) faculty(ssno, rank) studentassistant(ssno, percentage_time) Key: ssno for all relations employee Isa faculty staff student assistant position Percentage Time rank

  24. Subclass – null value name salary ssno Relation: employee(ssno, name, salary, position, rank, percentage-time) Key : ssno as key Note: Sometimes we add an attribute “jobType” to make queries easier. employee Isa faculty staff Student assistant position Percentage Time rank

More Related