CS 320: Database Management Systems
The Relational Data Model
Contents
- • Tables
- • Schemas
- • Conversion from E/R to Relations
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.
Why Relations?
- • Very simple model.
- • Often matches how we think about data.
- • Abstract model that underlies SQL, the most important database language today.
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.
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: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer) .
Risk with Many-Many Relationships
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).
Example
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.
