the relational model theoretical foundation l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
The Relational Model - theoretical foundation PowerPoint Presentation
Download Presentation
The Relational Model - theoretical foundation

Loading in 2 Seconds...

play fullscreen
1 / 45

The Relational Model - theoretical foundation - PowerPoint PPT Presentation


  • 133 Views
  • Uploaded on

The Relational Model - theoretical foundation . The Relational Model. data structures constraints operations algebra (ISBL) tuple calculus (QUEL, SQL) domain calculus (QBE) views. Data Structures. let D 1 , D 2 , D 3 , ..., D n be sets (not necessarily distinct) of atomic values

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The Relational Model - theoretical foundation' - andrew


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the relational model
The Relational Model
  • data structures
  • constraints
  • operations
    • algebra (ISBL)
    • tuple calculus (QUEL, SQL)
    • domain calculus (QBE)
  • views
data structures
Data Structures
  • let D1, D2 , D3 , ..., Dnbe sets (not necessarily distinct) of atomic values
  • relation, R, defined over D1, D2 , D3 , ..., Dn is a subset of the set of ordered n-tuples {<d1, d2, d3, ..., dn | di Di, i=1, ...,n}; D1, D2 , D3 , ..., Dn are called domains
  • the number, n, is the degree of the relation (unary, binary, ternary, n-ary).
  • the number of tuples, |R|, in R is called the cardinality of R
  • if D1, D2 , D3 , ..., Dn are finite then there are 2|D1||D2| ... |Dn|possible relation states
data structures4
Data Structures
  • an attribute name refers to a position in a tuple by name rather than position
  • an attribute name indicate the role of a domain in a relation
  • attribute names must be unique within relations
  • by using attribute names we can forget the ordering of field values in tuples
  • a relation definition includes the following R( A1:D1, A2 :D2 , ..., An :Dn)
constraints
Constraints
  • keys
  • primary keys
  • entity integrity
  • referential integrity

FLT-SCHEDULE

CUSTOMER

FLT#

CUST#

CUST-NAME

p

p

RESERVATION

FLT#

DATE

CUST#

slide6

AIRPORT

airportcode name city state

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

FLT-WEEKDAY

flt# weekday

FLT-INSTANCE

flt# date plane#

#avail-seats

AIRPLANE

plane# plane-type total-#seats

CUSTOMER

cust# first middle last phone# street city state zip

RESERVATION

flt# date cust# seat# check-in-status ticket#

operations
Operations
  • classes of relational DMLs:
    • relational algebra (ISBL)
    • tuple calculus (QUEL, SQL)
    • domain calculus (QBE)
  • a relational DML with the same “retrieval power” as the relational algebra is said to be relationally complete
  • all relational DMLs have syntax for:
    • change (insert, delete, update)
    • queries (retrieval)
operations insert delete update

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

Operations- insert, delete, update
  • constructs for insertion are very primitive:

INSERT INTO FLT-SCHEDULE

VALUES (“DL212”, “DELTA”, 11-15-00, “ATL”,

13-05-00, ”CHI”, 650, 00351.00);

INSERT INTO FLT-SCHEDULE

VALUES (FLT#:“DL212”, AIRLINE:“DELTA”);

operations insert delete update9

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

FLT-WEEKDAY

flt# weekday

FLT-INSTANCE

flt# date plane#

#avail-seats

Operations- insert, delete, update
  • “insert into FLT-INSTANCE all flights scheduled for Thursday,

9/10/98”

INSERT INTO FLT-INSTANCE(flt#, date)

(SELECT S.flt#, 1998-09-10

FROM FLT-SCHEDULE S, FLT-WEEKDAY D

WHERE S.flt#=D.flt# AND weekday=“TH”);

  • interesting only because it involves a query
operations insert delete update10

FLT-WEEKDAY

flt# weekday

Operations- insert, delete, update
  • constructs for deletion are very primitive:
  • “delete flights scheduled for Thursdays”

DELETE

FROM FLT-WEEKDAY

WHERE weekday=“TH”;

  • interesting only because it involves a query
operations insert delete update11

FLT-WEEKDAY

flt# weekday

Operations- insert, delete, update
  • constructs for update are very primitive:
  • “update flights scheduled for Thursdays to Fridays”

UPDATE FLT-WEEKDAY

SET weekday=“FR”

WHERE weekday=“TH”;

  • interesting only because it involves a query
relational algebra
Relational Algebra
  • the Relational Algebra is procedural; you tell it how to construct the result
  • it consists of a set of operators which, when applied to relations, yield relations (closed algebra)

R S union

R S intersection

R \ S set difference

R S Cartesian product

A1, A2, ..., An (R) projection

expression (R) selection

R S natural join

R S theta-join

RSdivideby

[A1 B1,.., An Bn] rename

selection

FLT-WEEKDAY

flt# weekday

Selection
  • “find (flt#, weekday) for all flights scheduled for Mondays”

weekday=MO (FLT-WEEKDAY)

  • the expression in expression (R) involves:
  • operands: constants or attribute names of R
  • comparison operators: Š  ° =
  • logical operators: 
  • nesting: ( )
projection

FLT-WEEKDAY

flt# weekday

Projection
  • “find flt# for all flights scheduled for Mondays

flt#(weekday=MO (FLT-WEEKDAY))

  • the attributes in the attribute list ofA1, A2, ..., An (R) must be attributes of the operand R
union

FLT-WEEKDAY

flt# weekday

Union
  • “find the flt# for flights that are schedule for either Mondays, or Tuesdays, or both”

flt#(weekday=MO (FLT-WEEKDAY))

flt#(weekday=TU (FLT-WEEKDAY))

  • the two operands must be "type compatible"
intersection

FLT-WEEKDAY

flt# weekday

Intersection
  • “find the flt# for flights that are schedule for both Mondays and Tuesdays”

flt#(weekday=MO (FLT-WEEKDAY))

flt#(weekday=TU (FLT-WEEKDAY))

  • the two operands must be "type compatible"
set difference

FLT-WEEKDAY

flt# weekday

Set Difference
  • “find the flt# for flights that are scheduled for Mondays, but not for Tuesdays”

flt#(weekday=MO (FLT-WEEKDAY))

\ flt#(weekday=TU (FLT-WEEKDAY))

  • the two operands must be "type compatible"
  • Note: RS = R \ (R \ S)
cartesian product

FLT-INSTANCE

flt# date plane#

#avail-seats

CUSTOMER

cust# first middle last phone# street city state zip

RESERVATION

flt# date cust# seat# check-in-status ticket#

Cartesian Product

“make a list containing (flt#, date, cust#)

for DL212 on 9/10, 98 for all customers in

Roswell that are not booked on that flight”

(cust#(city=ROSWELL(CUSTOMER)) 

flt#,date (flt#=DL212  date=1998-09-10

(FLT-INSTANCE)))\flt#,date ,cust#(RESERVATION)

natural join

FLT-WEEKDAY

flt# weekday

FLT-INSTANCE

flt# date plane#

#avail-seats

Natural Join
  • “make a list with complete flight instance information”

FLT-INSTANCE FLT-WEEKDAY

  • natural join joins relations on attributes with the same names
  • all joins can be expressed by a combination of primitive operators:

FLT-INSTANCE.flt#, date, weekday, #avail-seats

(FLT-INSTANCE.flt#=FLT-WEEKDAY.flt#

(FLT-INSTANCEFLT-WEEKDAY))

slide20

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

FLT-INSTANCE

flt# date plane#

#avail-seats

-join
  • “make a list of pairs of (FLT#1, FLT#2) that form possible connections”

fl1, flt#(([flt#fl1, from-airportcode da1,dtime dt1, to-airportcode aa1, atime at1, date d1]

(FLT-SCHEDULE FLT-INSTANCE ))

d1=date aa1=from-airportcode  at1< dtime

(FLT-SCHEDULE FLT-INSTANCE))

  • the-operators: Š  ° =
divideby

FLT-INSTANCE

flt# date plane#

#avail-seats

RESERVATION

flt# date cust# seat# check-in-status ticket#

Divideby
  • “list the cust# of customers that have reservations on all flight instances”

flt#, date, cust# RESERVATION

flt#, date (FLT-INSTANCE)

isbl an example algebra
ISBL - an example algebra

R S R UNION S

R S R INTERSECT S

R \ S R MINUS S

A1, A2, ..., An (R) R[A1, A2, ..., An]

expression (R) R WHERE EXPRESSION

R S R JOIN S (no shared attributes)

R S R JOIN S (shared attributes)

R S via selection from 

RS R DIVIDEBY S

[A1 B1,..., An Bn](R)R[A1 B1,.., An Bn]

features of isbl
Features of ISBL
  • the Peterlee Relational Test Vehicle, PRTV, has a query optimizer for ISBL
  • Naming results: T = R JOIN S
  • Lazy evaluation: T = N!R JOIN N!S
  • LIST T
  • 2-for-1 JOIN:
    • Cartesian product if no shared attribute names
    • natural join if shared attribute names
  • ISBL is relationally complete !
isbl an example query

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

FLT-INSTANCE

flt# date plane#

#avail-seats

ISBL - an example query
  • “make a list of pairs of (FLT#1, FLT#2) that form possible connections”
  • LIST(((FLT-SCHEDULE JOIN FLT-INSTANCE )
  • [FLT#FL1, FROM-AIRPORTCODE DA1,DTIME DT1, TO-AIRPORTCODE AA1, ATIME AT1, DATE D1]) JOIN
  • (FLT-SCHEDULE JOIN FLT-INSTANCE) WHERED1=DATE AA1=FROM-AIRPORTCODE  AT1< DTIME)[FL1, FLT#]
relational calculus
Relational Calculus
  • the Relational Calculus is non-procedural. It allows you to express a result relation using a predicate on tuple variables (tuple calculus):

{ t | P(t) }

or on domain variables (domain calculus):

{ <x1, x2, ..., xn> | P(<x1, x2, ..., xn>) }

  • you tell the system which result you want, but not how to construct it
tuple calculus
Tuple Calculus
  • query expression: { t | P(t) } where P is a predicate built from atoms
  • range expression: tR denotes that t is a member of R; so does R(t)
  • attribute value: t.A denotes the value of t on attribute A
  • constant: c denotes a constant
  • atoms: tR, r.A s.B, or r.A  c
  • comparison operators: Š  < > ° =
  • predicate: an atom is a predicate; if P1 and P2 are predicates, so are ¬(P1 ) and (P1 ), P1P2, P1 P2, and P1 P2
  • if P(t) is a predicate, t is a free variable in P, and R is a relation then tR(P(t)) andtR (P(t)) are predicates
tuple calculus27

CUSTOMER

cust# first middle last phone# street city state zip

Tuple Calculus
  • { r |(rCUSTOMER} is infinite, or unsafe
  • a tuple calculus expression { r | P(r) } is safe if all values that appear in the result are from Dom(P), which is the set of values that appear in P itself or in relations mentioned in P
selection28

FLT-WEEKDAY

flt# weekday

Selection
  • “find (FLT#, WEEKDAY) for all flights scheduled for Mondays

{ t | FLT-WEEKDAY(t) t.WEEKDAY=MO}

projection29

FLT-WEEKDAY

flt# weekday

Projection
  • “find FLT# for all flights scheduled for Mondays

{ t.FLT# | FLT-WEEKDAY(t) t.WEEKDAY = MO}

union30

FLT-WEEKDAY

flt# weekday

Union
  • “find the FLT# for flights that are schedule for either Mondays, or Tuesdays, or both”

{ t.FLT# | FLT-WEEKDAY(t) (t.WEEKDAY=MO t.WEEKDAY=TU)}

intersection31

FLT-WEEKDAY

flt# weekday

Intersection
  • “find the FLT# for flights that are schedule for both Mondays and Tuesdays”

{ t.FLT# | FLT-WEEKDAY(t)t.WEEKDAY=MO 

sFLT-WEEKDAY(s) t.FLT#=s.FLT# s.WEEKDAY=TU)}

set difference32

FLT-WEEKDAY

flt# weekday

Set Difference
  • “find the FLT# for flights that are scheduled for Mondays, but not for Tuesdays”

{ t.FLT# | FLT-WEEKDAY(t) t.WEEKDAY=MO ((s) (FLT-WEEKDAY(s) t.FLT#=s.FLT# s.WEEKDAY=TU))}

cartesian product33

FLT-INSTANCE

flt# date plane#

#avail-seats

CUSTOMER

cust# first middle last phone# street city state zip

RESERVATION

flt# date cust# seat# check-in-status ticket#

“make a list containing (FLT#, DATE, CUST#)

for DL212 on 9/10, 98 for all customers in

Roswell that are not booked on that flight”

Cartesian Product

{s.FLT#, s.DATE, t.CUST#| FLT-INSTANCE(s) CUSTOMER(t) t.CITY=ROSWELLs.FLT#=DL212 s.DATE=1998-09-10rFLT-INSTANCE(r) r ° sr.FLT#=s.FLT#r.DATE=s.DATE r.CUST#=t.CUST#)}

natural join34

FLT-WEEKDAY

flt# weekday

FLT-INSTANCE

flt# date plane#

#avail-seats

Natural Join
  • “make a list with complete flight instance information”

{ s.FLT#, s.WEEKDAY, t.DATE, t.PLANE#, t.#AVAIL-SEATS | FLT-WEEKDAY(s) FLT-INSTANCE(t)  s.FLT#=t.FLT# }

slide35

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

FLT-INSTANCE

flt# date plane#

#avail-seats

-join
  • “make a list of pairs of (FLT#1, FLT#2) that form possible connections”

{ s. FLT#, t.FLT# | FLT-SCHEDULE(s) FLT-SCHEDULE(t)  ((u)(v) FLT-INSTANCE(u) FLT-INSTANCE(v) u.FLT#=s.FLT# v.FLT#=t.FLT# u.DATE=v.DATE s.TO-AIRPORTCODE=t.FROM-AIRPORTCODEs.ATIME < t.DTIME) }

divideby36

FLT-INSTANCE

flt# date plane#

#avail-seats

RESERVATION

flt# date cust# seat# check-in-status ticket#

Divideby
  • “list the CUST# for customers that have reservations on all flight instances”

{ s.CUST# | RESERVATION(s)  (( t) FLT-INSTANCE(t) ((r) RESERVATION(r)  r.FLT#=t.FLT#  r.DATE=t.DATE r.CUST#=s.CUST#))}

quel an example tuple calculus

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

FLT-INSTANCE

flt# date plane#

#avail-seats

QUEL - an example tuple calculus
  • “make a list of pairs of (FLT#1, FLT#2) that form possible connections”

range s is FLT-SCHEDULE

range t is FLT-SCHEDULE

range u is FLT-INSTANCE

range v is FLT-INSTANCE

retrieve into CON( s.FLT#, t.FLT#)

where u.FLT#=s.FLT# and v.FLT#=t.FLT# and

u.DATE=v.DATE and s.TO-AIRPORTCODE=t.FROM-AIRPORTCODE and s.ATIME < t.DTIME;

qbe projection

FLT-WEEKDAY

FLT#

WEEKDAY

P.

=MONDAY

QBE - Projection
  • “find FLT# for all flights scheduled for Mondays
qbe union

FLT-WEEKDAY

FLT#

WEEKDAY

P.

MONDAY

P.

TUESDAY

QBE - Union
  • “find the FLT# for flights that are schedule for either Mondays, or Tuesdays, or both”
qbe intersection

FLT-WEEKDAY

FLT#

WEEKDAY

P._SX

MONDAY

_SX

TUESDAY

QBE - Intersection
  • “find the FLT# for flights that are schedule for both Mondays and Tuesdays”
qbe set difference

FLT-WEEKDAY

FLT#

WEEKDAY

P._SX

MONDAY

_SX

TUESDAY

QBE - Set Difference
  • “find the FLT# for flights that are scheduled for Mondays, but not for Tuesdays”
qbe cartesian product

CUSTOMER

FLT-INSTANCE

#AVAIL-

SEATS

CUST#

CUST-NAME

CITY

FLT#

DATE

P._C

ROSWELL

P._F

P._D

_F

98-9-10

DL212

_D

RESERVATION

FLT#

DATE

CUST#

_F

_D

_C

QBE - Cartesian Product

“make a list containing (FLT#, DATE, CUST#) for DL212 on 9/10, 98 for all customers in Roswell that are not booked on that flight”

qbe natural join

FLT-WEEKDAY

FLT-INSTANCE

#AVAIL-

SEATS

FLT#

WEEKDAY

FLT#

DATE

P._SX

P.

_SX

P.

P.

QBE - Natural Join
  • “make a list with complete flight instance information”
qbe join

FLT-SCHEDULE

FROM-

AIRPORT

CODE

TO-

AIRPORT

CODE

FLT#

AIRLINE

DTIME

ATIME

PRICE

P._SX

_A

_AT

FLT-SCHEDULE

FROM-

AIRPORT

CODE

TO-

AIRPORT

CODE

FLT#

AIRLINE

DTIME

ATIME

PRICE

P._SY

_A

_DT

FLT-INSTANCE

CONDITION

FLT#

DATE

#SEATS

_AT < _DT

_SX

_D

_SY

_D

QBE-join
  • “make a list of pairs of (FLT#1, FLT#2) that form possible same day connections”
views
Views
  • relational query languages are closed, i.e., the result of a query is a relation
  • a view is a named result of a query
  • a view is a snapshot relation
  • views can be used in other queries and view definitions
  • queries on views are evaluated by query modification
  • some views are updatable
  • some views are not updatable
  • more on views when we look at SQL