Relational Model

1
Relational Model Algebra

• Zachary G. Ives
• University of Pennsylvania
• CIS 550 Database Information Systems
• September 14, 2004

Some slide content courtesy of Susan Davidson
Raghu Ramakrishnan
2
Administrivia

• TJs office hours 300-430, Mondays
• My office hours will move to Thursday after class
• Homework assignments will normally be given out
  on Thursdays, due the following Thursday unless
  otherwise directed
• For some of the readings, youll need to write
  1-page summaries/reviews (more to come on this)
• Start thinking about which project you want to
  do, who you might work with
• Will need to form groups and pick a project by
  the end of next week

3
Thinking Back to Last Time

• There are a variety of ways of representing data,
  each with trade-offs
• Free text
• Classes and subclasses
• Shapes/points in space
• Objects with properties
• In general, our emphasis will be on the last item
• though there are spatial databases, OO
  databases, text databases, and the like

4
The Relational Data Model (1970)

• Lessons from the Codd paper
• Lets separate physical implementation from
  logical
• Model the data independently from how it will be
  used (accessed, printed, etc.)
• Describe the data minimally and mathematically
• A relation describes an association between data
  items tuples with attributes
• We generally think of tables and rows, but thats
  somewhat imprecise
• Use standard mathematical (logical) operations
  over the data these are the relational algebra
  or relational calculus
• How does this model relate to objects,
  properties? What are its abilities and
  limitations?

5
Why Did It Take So Many Years to Implement
Relational Databases?

• Codds original work 1969-70
• Earliest relational database research 1976
• Why the gap?
• You could do the same thing in other ways
• Nobody wants to write math formulas
• Why would I turn my data into tables?
• It wont perform well
• What do you think?

6
Getting More Concrete Buildinga Database and
Application

• Start with a conceptual model
• On paper using certain techniques well discuss
  next week
• We ignore low-level details focus on logical
  representation
• Design implement schema
• Design and codify (in SQL) the relations/tables
• Do physical layout indexes, etc.
• Import the data
• Write applications using DBMS and other tools
• Many of the hard problems are taken care of by
  other people (DBMS, API writers, library authors,
  web server, etc.)

7
Conceptual Design for CIS Student Course Survey
Whos taking what, and what grade do they
expect?
PROFESSOR
fid
name
This design is independent ofthe final form of
the report!
Teaches
Takes
STUDENT
COURSE
cid
name
semester
sid
name
exp-grade
8
Example Schema
STUDENT
COURSE
Takes

• Our focus now relational schema set of tables
• Can have other kinds of schemas XML, object,

PROFESSOR
Teaches
9
Some Terminology

• Columns of a relation are called attributes or
  fields
• The number of these columns is the arity of the
  relation
• The rows of a relation are called tuples
• Each attribute has values taken from a domain,
  e.g., subj has domain string
• Theoretically a relation is a set of tuples no
  tuple can occur more than once
• Real systems may allow duplicates for efficiency
  or other reasons well ignore this for now
• Objects and XML may also have the same content
  with different identity

10
Describing Relations

• A schema can be represented many ways
• To the DBMS, use data definition language (DDL)
  like programming language type definitions
• In relational DBs, we use relation(attributedomai
  n)

STUDENT(sidint, namestring) Takes(sidint,
exp-gradechar2, cidstring) COURSE(cidstring,
subjstring, semchar3) Teaches(fidint,
cidstring) PROFESSOR(fidint, namestring)
11
More on Attribute Domains

• Relational DBMSs have very limited built-in
  domains either tables or scalar attributes
  int, string, byte sequence, date, etc.
• But more generally
• We can have nested relations
• Object-oriented, object-relational systems allow
  complex, user-defined domains lists, classes,
  etc.
• XML systems allow for XML trees (or lists of
  trees) that follow certain structural constraints
• Database people, when they are discussing design,
  often assume domains are evident to the
  readerSTUDENT(sid, name)

12
Integrity Constraints

• Domains and schemas are one form of constraint on
  a valid data instance
• Other important constraints include
• Key constraints
• Subset of fields that uniquely identifies a
  tuple, and for which no subset of the key has
  this property
• May have several candidate keys one is chosen as
  the primary key
• A superkey is a subset of fields that includes a
  key
• Inclusion dependencies (referential integrity
  constraints)
• A field in one relation may refer to a tuple in
  another relation by including its key
• The referenced tuple must exist in the other
  relation for the database instance to be valid

13
SQL Structured Query Language

• The standard language for relational data
• Invented by folks at IBM, esp. Don Chamberlin
• Actually not a great language
• Beat a more elegant competing standard, QUEL,
  from Berkeley
• Separated into a DML DDL
• DML based on relational algebra calculus, which
  we discuss this week

14
Table DefinitionSQL-92 DDL and Constraints
CREATE TABLE Takes (fid INTEGER, exp-grade
CHAR(2), cid STRING(8), PRIMARY KEY (fid,
cid), FOREIGN KEY (fid) REFERENCES
STUDENT, FOREIGN KEY (cid) REFERENCES COURSE
)
CREATE TABLE STUDENT (sid INTEGER, name
CHAR(20), )
15
Example Data Instance
STUDENT
COURSE
Takes
PROFESSOR
Teaches
16
From Tables ? SQL ? Application

• lthtmlgt
• ltbodygt
• lt!-- hypotheticalEmbeddedSQL
• SELECT FROM STUDENT, Takes, COURSE
• WHERE STUDENT.sid Takes.sID
• AND Takes.cID cid
• --gt
• lt/bodygt
• lt/htmlgt

C -gt machine code sequence -gt microprocessor Java
-gt bytecode sequence -gt JVM SQL -gt relational
algebra expression -gt query execution engine
17
Codds Relational Algebra

• A set of mathematical operators that compose,
  modify, and combine tuples within different
  relations
• Relational algebra operations operate on
  relations and produce relations (closure)
• f Relation ? Relation f Relation x Relation ?
  Relation

18
Codds Logical Operations The Relational Algebra

• Six basic operations
• Projection ?? (R)
• Selection ?? (R)
• Union R1 R2
• Difference R1 R2
• Product R1 R2
• (Rename) ??-gtb (R)
• And some other useful ones
• Join R1 ?? R2
• Semijoin R1 ?? R2
• Intersection R1 Å R2
• Division R1 R2

19
Data Instance for Operator Examples
STUDENT
COURSE
Takes
PROFESSOR
Teaches
20
Projection, ??
21
Selection, ??
22
Product X
23
Join, ?? A Combination of Productand Selection
24
Union ?
25
Difference
26
Rename, ?a?b

• The rename operator can be expressed several
  ways
• The book has a very odd definition thats not
  algebraic
• An alternate definition
• ?a?b(x) Takes the relation with schema
  ? Returns a relation with the attribute list ?
• Rename isnt all that useful, except if you join
  a relation with itself
• Why would it be useful here?

27
Mini-Quiz

• This completes the basic operations of the
  relational algebra. We shall soon find out in
  what sense this is an adequate set of operations.
  Try writing queries for these
• The names of students named Bob
• The names of students expecting an A
• The names of students in Amir Roths 501 class
• The sids and names of students not enrolled

28
Deriving Intersection

• Intersection as with set operations, derivable
  from difference

A Å B
(A B) (A B) (B A) (A - B) (B - A)
A-B
B-A
A B
29
Division

• A somewhat messy operation that can be expressed
  in terms of the operations we have already
  defined
• Used to express queries such as The fid's of
  faculty who have taught all subjects
• Paraphrased The fids of professors for which
  there does not exist a subject that they havent
  taught

30
Division Using Our Existing Operators

• All possible teaching assignments Allpairs
• NotTaught, all (fid,subj) pairs for which
  professor fid has not taught subj
• Answer is all faculty not in NotTaught

?fid,subj (PROFESSOR ?subj(COURSE))
Allpairs - ?fid,subj(Teaches ? COURSE)
?fid(PROFESSOR) - ?fid(NotTaught)
?fid(PROFESSOR) - ?fid(
?fid,subj (PROFESSOR ?subj(COURSE)) -
?fid,subj(Teaches ? COURSE))
31
Division R1 R2

• Requirement schema(R1) ¾ schema(R2)
• Result schema schema(R1) schema(R2)
• Professors who have taught all courses
• What about Courses that have been taught by all
  faculty?

?fid (?fid,subj(Teaches ? COURSE) ?subj(COURSE))
32
The Big Picture SQL to Algebra toQuery Plan to
Web Page
Web Server / UI / etc
Query Plan anoperator tree
Execution Engine
Optimizer
Storage Subsystem
SELECT FROM STUDENT, Takes, COURSE
WHERE STUDENT.sid Takes.sID AND
Takes.cID cid
33
Hint of Future Things OptimizationIs Based on
Algebraic Equivalences

• Relational algebra has laws of commutativity,
  associativity, etc. that imply certain
  expressions are equivalent in semantics
• They may be different in cost of evaluation!

?c Ç d(R) ?c(R) ?d(R)
?c (R1 R2) R1 ?c R2
?c Ç d (R) ?c (?d (R))

• Query optimization finds the most efficient
  representation to evaluate (or one thats not bad)

34
Next Time An Equivalent, ButVery Different,
Formalism

• Codd invented a relational calculus that he
  proved was equivalent in expressiveness
• Based on a subset of first-order logic
  declarative, without an implicit order of
  evaluation
• More convenient for describing certain things,
  and for certain kinds of manipulations