Announcement

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
A Relation is a Table

• name manf
• Winterbrew Petes
• 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
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
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
Example
name
name
Logins
Hosts
At
location
billTo
Hosts(hostName, location) Logins(loginName,
hostName, billTo) At(loginName, hostName,
hostName2)
What if At has some attributes ?
14
Case Study
Co. Popu.
Co. name
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 dont 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 Petes dark Ales
Good for queries like find the color of ales
made by Petes.
19
E/R Style
name manf Bud Anheuser-Busch Summerbrew
Petes Beers name color Summerbrew
dark Ales
Good for queries like find all beers
(including ales) made by Petes.
20
Using Nulls
name manf color Bud Anheuser-Busch
NULL Summerbrew Petes dark Beers
Saves space unless there are lots of attributes
that are usually NULL.
21
Case Study
name
salary
ssno
employee
Isa
staff
faculty
student assistant
position
Percentage Time
rank
22
Subclass Object-oriented

• 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

name
salary
ssno
employee
Isa
staff
faculty
Student assistant
position
Time percentage
rank
23
Subclass E/R Style

• Relations
• employee(ssno, name, salary)
• staff(ssno, position)
• faculty(ssno, rank) studentassistant(ssno,
  percentage_time)
• Key ssno for all relations

name
salary
ssno
employee
Isa
staff
faculty
student assistant
position
Percentage Time
rank
24
Subclass null value

• Relation
• employee(ssno, name, salary, position, rank,
  percentage-time)
• Key ssno as key
• Note Sometimes we add an attribute jobType to
  make queries easier.

name
salary
ssno
employee
Isa
staff
faculty
Student assistant
position
Percentage Time
rank