CS411 Database Systems

1
CS411Database Systems

• 03 Relational Model

2
Motivations comparison of ER with relational
model ...
3
Database Modeling Implementation
Ideas
Database Model (E/R, ODL)
Physical storage
Relational Schema
Complex file organization and index structures.
Diagrams (E/R)
Tables column names attributes rows
tuples
4
ER Model vs. Relational Model

• Both are used to model data
• ER model has many concepts
• entities, relations, attributes, etc.
• well-suited for capturing the app. requirements
• not well-suited for computer implementation
• (does not even have operations on its structures)
• Relational model
• has just a single concept relation
• world is represented with a collection of tables
• well-suited for efficient manipulations on
  computers

5
The basics of the relational model ...
6
An Example of a Relation
Table name
Attribute names
Products
Name Price Category
Manufacturer gizmo 19.99
gadgets GizmoWorks Power
gizmo 29.99 gadgets
GizmoWorks SingleTouch 149.99
photography Canon MultiTouch 203.99
household Hitachi
tuples
7
Domains

• Each attribute has a type
• Must be atomic type (why? see later)
• Called domain
• Examples
• Integer
• String
• Real

8
Schemas vs. instances (very important, make sure
you knowthe difference)
9
Schemas

• The Schema of a Relation
• Relation name plus attribute names
• E.g. Product(Name, Price, Category, Manufacturer)
• In practice we add the domain for each attribute
• The Schema of a Database
• A set of relation schemas
• E.g. Product(Name, Price, Category,
  Manufacturer),
• Vendor(Name, Address, Phone),
  . . . . . . .

10
Instances

• Relational schema R(A1,,Ak)Instance
  relation with k attributes (of type R)
• values of corresponding domains
• Database schema R1(), R2(), , Rn()Instance
  n relations, of types R1, R2, ..., Rn

11
Example
Relational schemaProduct(Name, Price, Category,
Manufacturer) Instance
12
Updates
The database maintains a current database
state. Updates to the data 1) add a
tuple 2) delete a tuple 3) modify an
attribute in a tuple Updates to the data happen
very frequently. Updates to the schema
relatively rare. Rather painful. Why?
13
Schemas and Instances

• Analogy with programming languages
• Schema type
• Instance value
• Important distinction
• Database Schema stable over long periods of
  time
• Database Instance changes constantly, as data
  is inserted/updated/deleted

14
How should we talk about relations (that is,
represent them)?
15
Two Mathematical Definitions of Relations

• Relation as Cartesian product
• Tuple element of string x int x string x string
• E.g. t (gizmo, 19, gadgets, GizmoWorks)
• Relation subset of string x int x string x
  string
• Order in the tuple is important !
• (gizmo, 19, gadgets, GizmoWorks)
• (gizmo, 19 , GizmoWorks, gadgets)
• No attributes

16

• Relation as a set of functions
• Fix the set of attributes
• Aname , price, category, manufacturer
• A tuple function tA Domains
• Relation set of tuples
• E.g.
• Order in a tuple is not important
• Attribute names are important

name gizmo, price
19, category gadgets,
manufacturer gizmoWorks
17
Two Definitions of Relations

• We will switch back and forth between these two
• Positional tuples, without attribute names
• Relational schemas with attribute names

18
Now the fun part translating from ER to
relational model
19
Translating ER Diagram to Rel. Design

• Basic cases
• entity set E relation with attributes of E
• relationship R relation with attributes being
  keys of related entity sets attributes of R
• Special cases
• combining two relations
• translating weak entity sets
• translating is-a relationships and subclasses

20

An Example
name
category
name
price
makes
Company
Product
Stock price
buys
employs
Person
name
ssn
address
21
Basic cases ...
22
Entity Sets to Relations
name
category
price
Product
Product Name
Category Price
gizmo gadgets
19.99
23
Relationships to Relations

name
category
price
Start Year
name
makes
Company
Product
Stock price
Relation Makes (watch out for attribute name
conflicts) Product-name
Product-Category Company-name Starting-year
gizmo gadgets
gizmoWorks 1963
24
Relationship to Relation Another Example
name
name
addr
manf
Drinkers
Beers
25
Special cases1) many one relations2) weak
entity sets3) isa cases
26
Combining Two Relations

name
category
price
Start Year
name
makes
Company
Product
Stock price
No need for Makes. Just modify Product
name category price StartYear
companyName gizmo gadgets 19.99
1963 gizmoWorks
27
Combining Relations

• It is OK to combine the relation for an
  entity-set E with the relation R for a many-one
  relationship from E to another entity set.
• Example Drinkers(name, addr) and
  Favorite(drinker, beer) combine to make
  Drinker1(name, addr, favoriteBeer).

28
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
29
Handling Weak Entity Sets
affiliation
University
Team
number
sport
name
Relation Team Sport
Number Affiliated
University mud wrestling
15 Montezuma State U.
- need all the attributes that contribute to
the key of Team - dont need a separate
relation for Affiliation. (why ?)
30
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 (double-diamond) relationship is
  redundant and yields no relation.

31
Another Example
name
name
Logins
Hosts
At
time
Hosts(hostName) Logins(loginName, hostName,
time) At(loginName, hostName)
32
Translating Subclass Entities
Product
ageGroup topic
Platforms required memory
isa
isa
Educational Product
Software Product
isa
isa
Educational-method
Educ-software Product
33
Option 1 the OO Approach
4 tables each object can only belong to a single
table Product(name, price, category,
manufacturer) EducationalProduct( name, price,
category, manufacturer,
ageGroup, topic) SoftwareProduct(
name, price, category, manufacturer,
platforms, requiredMemory)
EducationalSoftwareProduct( name, price,
category, manufacturer,
ageGroup, topic,
platforms,
requiredMemory) All names are distinct
34
Option 2 the E/R Approach
Product(name, price, category, manufacturer)
EducationalProduct( name, ageGroup, topic)
SoftwareProduct( name, platforms,
requiredMemory) No need for a relation
EducationalSoftwareProduct Unless, it has a
specialized attribute EducationalSoftwareProduc
t(name, educational-method) Same name may appear
in several relations
35
Option 3 The Null Value Approach
Have one table Product ( name, price,
manufacturer, age-group, topic, platforms,
required-memory, educational-method)
Some values in the table will be NULL, meaning
that the attribute not make sense for the
specific product. Too many meanings for NULL
36
Translating Subclass Entities The Rules

• 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 attribute(s) and
  attributes attached to that E.S. entity
  represented in all relations to whose
  subclass/E.S. it belongs.
• Use nulls create one relation entities have
  null in attributes that dont belong to them.

37
Example
Beers
name
manf
isa
Ales
color
38
Object Oriented
Beers
name
manf
isa
name manf Bud Anheuser-Busch Beers name
manf color Summerbrew Petes dark Ales
Ales
color
39
E/R Style
Beers
name
manf
isa
name manf Bud Anheuser-Busch Summerbrew
Petes Beers name color Summerbrew
dark Ales
Ales
color
40
Using Nulls
Beers
name
manf
isa
Ales
color
name manf color Bud Anheuser-Busch
NULL Summerbrew Petes dark Beers
41
Comparisons

• O-O approach good for queries like find the
  color of ales made by Petes.
• Just look in Ales relation.
• E/R approach good for queries like find all
  beers (including ales) made by Petes.
• Just look in Beers relation.
• Using nulls saves space unless there are lots of
  attributes that are usually null.

42
Translation Review

• Basic cases
• entity to table, relation to table
• selecting attributes based on keys
• Special cases
• many-one relation can be merged
• merging many-many is dangerous
• translating weak entity sets
• translating isa hierarchy
• 3 choices, with trade-offs