Operators for a Relational Data Model

Matt DubeDoctoral Student, Spatial Information Science and Engineering

Monday’s Class Mathematical definitions that underlie the

relational data model:

Domain: set of inputs for a particular attribute

Type: structure of the members of that set

Cartesian Product: combination of members of one set with every member of another set…and another…and another

Relation: subset of the Cartesian product of the attribute domains

Key: unique identifying attributes for a relation

Discussion of Assignment

Think of an example in your particular discipline where a relational data model might be helpful.

What are the attributes?

What are their domains?

What would you key the database with?

Why?

Stepping Up from Relations

Having only useful data was a motivating concern for us

How do we go about that?

Operators are the critical component that allows us to transform a relational database of large size to one which is more manageable

There are six of these operators to be concerned with

ProjectionProjection is the first of the operators

Mathematical example: Projection is the “shadow” of a vector of any sort

onto a lower dimensional surface. Think of a right triangle: the lower leg is always

shorter than the hypotenuse (why?)

Projection thus represents only considering certain attributes of interest

Projection ExampleWhat is the projection here?

Dimensional reduction (Z coordinate removed)

Notationπ< pertinent attributes > (R)

New relation is thus a subset of a different space

That different space is a component of the original domain

Table ProjectionName Major Schoo

lGende

r

Andrew ECE UMaine

M

Guillaume

GIS UMaine

M

Valeryia ECO UMaine

F

Lisa GIS UMaine

F

Eric BUA UMaine

M

Chris SIE UMaine

M

Name Gender

Andrew M

Guillaume M

Valeryia F

Lisa F

Eric M

Chris M

Problem with Projection What dictates the usefulness of a projection?

Is the key involved?

What if a key isn’t involved?

If the key isn’t involved, duplicates are removed to preserve relation status = missing data!

Order not important…attributes can be listed in any order in the projection function (analogous to rotation)

SelectionSelection is the second operator, and is the

converse of projection

Mathematical example: Intersections in a Venn Diagram

Selection takes a list of specific properties and find things which satisfy that list

Selection ExampleWhat is the selection here?

What do these foxy ladies want to wear today?

Notationσ< selection criteria > (R)

New relation is thus a subset of the original relation

Table SelectionName Major Schoo

lGende

r

Andrew ECE UMaine

M

Guillaume

GIS UMaine

M

Valeryia ECO UMaine

F

Lisa GIS UMaine

F

Eric BUA UMaine

M

Chris SIE UMaine

M

Name Major

School

Gender

Andrew ECE UMaine

M

Valeryia ECO UMaine

F

Eric BUA UMaine

M

Chris SIE UMaine

M

Properties of SelectionSelection is in the same application space as

the original relation

Key structure is thus the same

Selection is associative

Associativity: Being able to interchange the groupings Addition and multiplication are associative

operators you are familiar with already

RenamingRenaming is the third operator

Mathematical example: Equivalent terms

Compact = Closed and Bounded

Renaming is used when combining relations

Why would that be potentially necessary?

Notation for this is ρ attribute / attribute (R)

Cartesian ProductWe went over this a bit mathematically, but

now we are going to apply it to relations themselves

Mathematical Example: The X,Y Plane (or the X,Y,Z space, or any other

similar type of space)

Take all possible combinations of relation records between 2 or more relations

Cartesian Product Example

What is this a Cartesian Product of?

Truth values for P, Q, and R

P Q R

T T T

T T F

T F T

T F F

F T T

F T F

F F T

F F F

P

T

F

P

T

F

Q

T

F

R

T

F

Cartesian ProductName Pet

George Fluffy

Karl Rover

State City

Delaware Dover

Oregon Corvallis

Name Pet State City

George Fluffy Delaware

Dover

Karl Rover Delaware

Dover

George Fluffy Oregon Corvallis

Karl Rover Oregon Corvallis

Properties of a Cartesian Product

How big will a Cartesian Product be?

Treat this generally: Relation R has x rows and y columns Relation S has z rows and w columns

R x S has x * z rows and y + w columns

Why?

The key of a Cartesian product needs to involve at least one attribute from both R and S.

Why?

UnionUnion is the fifth operator

Mathematical Example: Addition of positive integers is a natural union Addition of sets

Union thus takes relations and binds them together

Union ExampleWhat is this a union of?

The 50 States and Puerto Rico and the Virgin Islands

What does a Union have?

Unioned attributes always have identical domains Why?

Do unioned attributes have to have identical names? No Think of unioning two sets together. Did those sets have the

same names?

Unions are commutative

Commutativity: Changing the order is irrelevant Addition and multiplication are commutative operators Difference between this and associativity?

Commutative vs. Associative

DifferenceDifference is the sixth and final operator

Mathematical example: Subsets

Difference produces a subset not in common

Difference ExampleWhat is the difference here?

Unary and N-ary Operators

Unary operators only have one operand (in this case they only involve one relation)

Projection, Selection, Renaming

N-ary operators involve N operands (in this case they involve N relations)

Cartesian Product, Union, Difference

Can you classify the six?

FridayDeriving relations and operators in our first

order languages

Combinations of relations

Joins

Algebras