Announcement Recitation time Before midterm 6 7 pm

Announcement • Recitation time § Before midterm: 6 -7 pm, by Earl Wagner § After midterm: 5 -6 pm, 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) 1

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

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

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

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

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

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

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

Combining 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 Drinker 1(name, addr, fav. Beer). 9

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

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

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

Example name bill. To Logins name At Hosts location Hosts(host. Name, location) Logins(login. Name, host. Name, bill. To) At(login. Name, host. Name 2) At becomes part of Logins Must be the same What if “At” has some attributes ? 13

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

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

Subclasses: Three Approaches 1. 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. 16

Example name Beers manf isa color Ales 17

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

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. ” 19

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

Case Study ssn o name salar y employee Isa staff faculty position rank student assistant Percentage Time 21

Subclass – Object-oriented ssn o name salar y employee Isa staff faculty Student assistant position rank Time percentage 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 22

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

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