Transcript Relational Model

Relational Model
CS 157A
Prof. Sin-Min Lee
By Truc Truong
What is Relational Model?


Relational model is most widely used data
model for commercial data-processing.
The reason it’s used so much is, because
it’s simple and easy to maintain.
The model is based on a collection of
tables. Users of the database can create
tables, insert new tables or modify
existing tables. There are several
languages for database programming.
• SQL, Oracle, etc.
History of Relational Modeling
Introduced by Ted Codd in 1970
 Ted Codd was an IBM Researcher
 Laid the foundation for database
theory
 Many database concepts & products
based on his model

Relational Model Basic
The relational model gives us a single
way to represent data: as a twodimensional table called a relation.
 Attributes
 Schemas
 Tuples
 Domains
 Equivalent Representations of a
Relation
Attributes
Attributes of a relation serve as names for the
columns of the relation. Usually, an attribute
describes the meaning of entries in the column
below.
Table = relation.
Column headers = attributes.
Attribute
Title
Star Wars
Year
1977
Length
124
Might Ducks
Wayne’s World
1991
1992
104
95
Schemas






The name of a relation and the set of attributes for a
relation is called a schema.
We show the schema for the relation with the relation
name followed by a parenthesized list of its attributes.
Relation schema = name(attributes) + other structure
info., e.g., keys, other constraints.
Order of attributes is arbitrary, but in practice we
need to assume the (standard) order given in the
relation schema.
Relational database schema = collection of relation
schemas.
So the schema for previous slide is
Movies (title, year, length)
Database Schema

DataBase Schema
• Logical View of the Database

Database Instance
• A view of data in database at anytime.

Relation Schema
• Corresponds to the programming language concept of
type definition
• E.g. Java


String movie = “Spiderman”;
Relation Instance
• Corresponds to the programming-language concept of
the value of a variable
• E.g. Java

String movie = “Spiderman”;
Tuples
 The rows of a relation, other than the header row containing
The attribute names are called tuples.
 A tuple has one component for each attribute of the relation.
Tuple
Title
Star Wars
Year
1977
Length
124
Might
Ducks
Wayne’s
World
1991
104
1992
95
Domains



Each attribute of a relation is associated
with a particular elementary type called
domain.
The components of any tuple of the
relation must have, in each component, a
value that belongs to the domain of the
corresponding column.
Example:
• with title string is associated
• with year integer is associated
Equivalent Representations of a
Relation




Schemas are sets of attributes (not lists).
Tuples are sets of components (not lists).
Instances are sets of tuples (not lists)
 After permutation of rows and columns
the relations remains the same! (permute
values and attributes)
Equivalent Representations of a
Relation cont.

Formal notion of a tuple=
a function {attributes}  {values}
• title  Star Wars
• year  1977
• length  121
• filmType  color

(Star Wars, 1977, 121,color) and
(1977, 121,color, Star Wars) are the
same object.
Original Relation
Title
Star Wars
Year
1977
Length
124
Mighty Ducks
1991
104
Wayne’s World
1992
95
Modified Relation
Year
1977
Length
124
Title
Star Wars
1991
1992
104
95
Mighty Ducks
Wayne’s World
Relational Data Model:
Set theoretic
summary
Domain — set of values
Relation as table
Rows = tuples
Columns = components
Names of columns = attributes
Relation name + set of attribute
names= schema
REL (A1,A2,...,An)
C
a
r
d
i
n
a
l
i
t
y
A1 A2 A3 ... An
a1 a2 a3
an
b1 b2 a3
cn
a 1 c2 b 3
.
.
.
bn
x1 v2 d3
wn
Arity
Attributes




like a data type
Cartesian product (or
product)
D1
D2
...
Dn
n-tuples (V1,V2,...,Vn)
s.t., V1
D1, V2
D2,...,Vn
Dn

•Relation=subset of cartesian
product of one or more domains
Tuple
Component

FINITE only; empty set allowed
•Tuples = members of a relation
inst.
•Arity = number of domains
•Components = values in a tuple
•Domains — corresp. with
attributes
•Cardinality = number of tuples
Schema versus Instance







DB instances change continuously
(e.g., movies are added, deleted, changed,…)
The schema is stable
(attributes change almost never)
A RDB instance is the set of tuples that are ‘now’
in the DB
When designing the DB only the schema is
important (=the structure of the data/DB)
We only imagine typical instances to help us with
the design
Intentional level: schema
Extensional level: instances
E/R Diagrams to Relational
Designs
Creation of a DB:
 Design phase (on “paper”, which information, relationships,
constraints, …)
 Implementation phase (real RDBMS)
 It is “easier” to start from ODL or E/R and later convert to
RM
• RM has only one concept (relation)
• E/R and ODL have complementary concepts and are
more flexible (constraints, …)
 Converting E/R design to a relational database schema:
• Turn each entity set into a relation with the same set of
attributes
• Replace a relationship by a relation whose attributes are
the keys for the connected entity set.
*Weak entity sets cannot be translated straightforwardly to
relations
*”Isa” relationships and subclasses require careful treatment.
Relational Design cont.


Design in ODL or E/R (schema+constraints)

implementation in a RDBMS
Simplest approach (not always best): convert each ODL
class or E/R entity set to a relation and each relationship to
a relation.
Class/Entity Set  Relation
Relationship  Relation
From Entity Sets to Relations


An entity set that is not weak, is translated
into a relation with the same name and
attributes.
E.g.,
Movie(title, year, length, filmType)
Star(name, address) or Star(name, street, city)
Owns
Studios
name
address
title
Movies
year
Star
Stars-in
length
filmType
name
address
From E/R Relationships to
Relations
E/R relationships are also translated to relations:
1. For each entity set involved in R, take key
attribute(s) as part of schema
2. If the relationship has attributes, add them to the
schema
 If an entity set appears more than once in a
relationship, rename its attributes to avoid
doubles and for clarity!
 E.g.,
Owns(title, year, studioName)
Owns
Studios
name
address
title
Movies
year
Star
Stars-in
length
filmType
name
address
Relationships to Relation cont.


If an entity set appears more than once in
a relationship, rename its attributes to
avoid doubles!
name
E.g., Contracts(title,year,starName,
studioOfStar,producingStudio)
Studio
Producing
studio
Studio of star
Contracts
Movies
Star
Handling Weak Entity Set
If there is a weak entity sets W we do the following
differently:
 Attributes of W plus key attributes of other
entity sets that contribute to the key of W
(double-diamond; many-one).
 Any relationship in which W appears must use
as a key for W all of its attributes including
those of the other entity sets that contribute to
W’s key
 Double-diamond relationships from W to
another entity set do not need to be converted
(this information is already in the relation for
W).
name
address
number
Crews
Unit-of
Studios
Studios(name, address)
Crews(number,studioName)
Unit-of(number,studioName,name) Unit-of(number,name)
are the same (many-one!)
(Disney crew #3, Disney)  (3, Disney, Disney)
Contracts(starName, studioName,
title, year, salary)
name
Studios
Relations for Movie-of,
Star-of and
Studio
Studio-of are superfluous…
Of
address
Stars
Movies
MovieOf
Contracts
Contracts
length
title
year
filmType
salary
StarOf
Star-of
Stars
name
address
Converting Subclass Structures to
Relations
Differences between E/R and ODL:
 In ODL: an object belongs to exactly
one class. It inherits properties from
superclass.

In E/R: an object may belong to
several entity sets related by isa.
Structures to Relations cont.
 Every subclass has its own relation
 It has all properties of this subclass
including inherited properties

E.g.,
Movie(title,year,length,filmType,studioName,starName)
Cartoon(title,year,length,filmType,StudioName,starName,
voice)
•
•
MurderMystery(title,year,length,filmType,StudioName,starN
ame,weapon)
Cartoon-MurderMystery(title,year,length,filmType,
StudioName,starName,voice, weapon)
An Object-Oriented Approach
A hierarchy is populated by entities related by isa’s.
 No relation is created for the isa relationship.
 For each entity set, a separate relation with its
own attributes plus key attributes of related
attribute sets.
Movies
year
isa
title
filmType
Voices
length
Bugs Bunny
isa
Cartoons
weapon
MurderMysteries
Differences between E/R and ODL:
 In ODL: all properties of an object
together in one relation; we have to
search 4 relations to find a movie
object!
Movie(title,year,length,filmType,studioName,starName
)
 Cartoon(title,year,length,filmType,StudioName,
starName,voice)
 MurderMystery(title,year,length,filmType,StudioNam
e,starName,weapon)
 Cartoon-MurderMystery(title,year,length,filmType,
StudioName,starName,voice,weapon)
Differences between E/R and ODL:

In E/R: a key of an entity is repeated
once for every entity set and
relationship it belongs to (scattered
information).
Using Null Values to Combine
Relations



ODL: Information of a hierarchy can be
given in one relation
NULL = “there is no appropriate
value for this attribute”
E.g.,
Movie(title,year,length,filmType,StudioName,
starName,voice, weapon)
References
Jeff Ullman and Jennifer Widom, A
First Course in Database systems.
2nd edition, Prentice Hall.
 Prof. Lee “Relation Model”
Presentation
 science.kennesaw.edu/~mguimara/8
080/ch3_4.ppt
 www.cs.niu.edu/~sheng/CSCI588/Le
cture2.ppt
