RELATIONAL DATA MODEL

1
RELATIONAL DATA MODEL
2
Reading and Exercises

• Database Systems- The Complete Book Chapter 3.1,
  3.2, 3.3
• Self testing exercises
• 3.1.1
• 3.2.1, 3.2.3
• 3.3.4
• Any of the remaining exercises in the textbook
• Following lecture slides are modified from Jeff
  Ullmans slides for Fall 2002 -- Stanford

3
Relation
Dog relation (table)
Attributes
Tuples
4
Schema

• Relation schema relation name and attributes (in
  order) types of attributes
• Dog(namestring, breedstring, ageint,
  weightint)
• Database collection of relations
• Database schema set of relation schemas in the
  database

5
Why Relations?

• Very simple
• Intuitive
• Abstract data model, underlies SQL
• SQL bag
• Relational model set

6
Transformation of E/R Diagram to Relation

• Primary keys allow entity sets and relationship
  sets to be expressed uniformly as relations
• E/R diagram ? collection of tables
• For each entity set and each relationship set
  there is a unique table

7
Transformation from E/R to Relation

• Entity set becomes a relation with the
  attributes of the entity set
• Relationship becomes a relation with attributes
• Key of connected entity sets
• Attributes of the relationship

8
Example E/R Diagram
Breed
Name
Name
License
Dog
Kennel
Boards
Age
Address
Phone
Owns
Weight
Pays
Owner
Amount
Phone
Name
9
Entity Set to Relation
Breed
Name
Dog
Age
Weight
Dog(Name,Breed,Age,Weight)
10
Entity Set to Relation
Amount
Name
Date
Name
Address
Owner
Kennel
Pays
Phone
Pays(Kennel. Name,Owner.Name,Amount,Date)
11
Combining Relations Many-One
Name
Amount
Date
Name
Address
Owner
Kennel
Pays
Phone
Favorite
Combine relation for entity of many side
(Owner) with relationship set, i.e.,
Owners-favorite(Name,Phone,FavoriteKennel.Name)
12
Combining Relations Many-Many
Name
Name
Address
Owner
Kennel
Pays
Phone
Combining Owner with pays Owner(Kennel.Name,Owner
.Name,Phone)
13
Many-many Relationship
Redundancy
14
Weak Entity Sets to Relation

• Relation for a weak entity set must include
  attributes for its complete key (including those
  that belong to other entities) as well as its
  own, non-key attributes
• Supporting relationship is redundant and does not
  yield a relation

15
Example Weak Entity Set
Breed
Name
Name
Dog
Owner
Owns
Phone
Age
Owner(Owner.Name, Phone) Dog(Dog.Name,Owner.Name,
Age,Breed) Owns(Dog.Name,Owner.Name1,Owner.Name2)
Must be the same
Already in Dog Redundant!
16
Entity Sets with Subclasses

• Three approaches
• Object-oriented each entity belongs to exactly
  one class. Create a relation for each class with
  all its attributes.
• E/R style create one relation for each subclass
  with only the key attributes and the attributes
  of the subclass. Entity is represented in all
  relations to whose subclass entity set it
  belongs.
• Null values create on relation with all
  attributes. Entities have null values in
  attributes that do not belong to them.

17
Example Subclass
Name
Breed
Dog
ISA
Show-Dog
Rank
18
Object-Oriented Approach
Dog
Show dog
19
E/R Approach
Dog
Show dog
20
Null Values
Dog
21
Comparisons

• Object-Oriented good for queries like find all
  G.S. dogs that ranked 2nd or above.
• E/R good for find all G.S. dogs (regardless of
  being a show dog or not)
• Null values may save space, but not good if too
  many attributes with null values.