Transcript CS206 --- Electronic Commerce

The Relational Data Model
Tables
Schemas
Conversion from E/R to Relations
1
A Relation is a Table
Attributes
(column
headers)
Tuples
(rows)
name
Corvette
Maxima
manf
G.M.
Nissan
cars
2
Schemas
Relation schema = relation name and
attribute list.
 Optionally: types of attributes.
 Example: cars(name, manf) or cars(name:
string, manf: string)
Database = collection of relations.
Database schema = set of all relation
schemas in the database.
3
Why Relations?
Very simple model.
Often matches how we think about
data.
Abstract model that underlies SQL, the
most important database language
today.
4
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.
5
Entity Set -> Relation
model
manf
cars
Relation: cars(model, manf)
6
Relationship -> Relation
name
husband
addr
drivers
1
name
Likes
manf
cars
2
Mustangdies
Favorite
wife
Married
Likes(driver, car)
Favorite(driver, car)
Mustangdies(name1, name
Married(husband, wife)
7
Combining Relations
 OK to combine into one relation:
1. The relation for an entity-set E
2. The relations for many-one relationships
of which E is the “many.”
 Example: drivers(name, addr) and
Favorite(driver, car) combine to make
driver1(name, addr, favcar).
8
Risk with Many-Many Relationships
Combining drivers with Likes would be
a mistake. It leads to redundancy, as:
name
addr
car
Sally 123 Maple Mustang
Sally 123 Maple Maxima
Redundancy
9
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 (unless it has
attributes).
10
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
11
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.
12
Example
model
cars
manf
isa
RLR
Sports Car
13
Object-Oriented
name
Mustang
cars
name
Corvette
manf
Nissan
manf
G.M.
Sports
RLR
None
Good for queries like “find the
RLR of Sports Car made by G.M..”
14
E/R Style
name
Mustang
Corvette
manf
Nissan
G.M.
Cars
name
RLR
Corvette
none
Sports Cars
Good for queries like
“find all cars (including
Sports Cars) made by G.M..”
15
Using Nulls
name
Mustang
Corvette
manf
Nissan
G.M.
cars
RLR
NULL
none
Saves space unless there are lots
of attributes that are usually NULL.
16