Transcript CS206 --- Electronic Commerce

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)
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
addr
Drinkers
1
name
Likes
manf
Beers
2
Buddies
Favorite
wife
Married
Likes(drinker, beer)
Favorite(drinker, beer)
Buddies(name1, name2)
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 Drinker1(name, addr, favBeer).
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
addr
beer
Sally 123 Maple Bud
Sally 123 Maple 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
billTo
Logins
name
At
Hosts
location
Hosts(hostName, location)
Logins(loginName, hostName, billTo)
At(loginName, hostName, hostName2)
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
manf
Summerbrew Pete’s
Ales
color
dark
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
Summerbrew
color
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 “jobType”
to make queries easier.
24