The Relational Model theoretical foundation

1
The Relational Model- theoretical foundation
2
The Relational Model

• data structures
• constraints
• operations
• algebra (ISBL)
• tuple calculus (QUEL, SQL)
• domain calculus (QBE)
• views

3
Data Structures

• let D1, D2 , D3 , ..., Dn be 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 ltd1,
  d2, d3, ..., dn di ? Di, i1, ...,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 2D1?D2? ... ?Dn possible relation states

4
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(
  A1D1, A2 D2 , ..., An Dn)

5
Constraints

• keys
• primary keys
• entity integrity
• referential integrity

FLT-SCHEDULE
CUSTOMER
FLT
CUST
CUST-NAME
p
p
RESERVATION
FLT
DATE
CUST
6
(No Transcript)
7
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)

8
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 (FLTDL212, AIRLINEDELTA)

9
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.fltD.flt AND weekdayTH)
• interesting only because it involves a query

10
Operations- insert, delete, update

• constructs for deletion are very primitive
• delete flights scheduled for Thursdays
• DELETE
• FROM FLT-WEEKDAY
• WHERE weekdayTH
• interesting only because it involves a query

11
Operations- insert, delete, update

• constructs for update are very primitive
• update flights scheduled for Thursdays to
  Fridays
• UPDATE FLT-WEEKDAY
• SET weekdayFR
• WHERE weekdayTH
• interesting only because it involves a query

12
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???S divideby ??A1 B1,.., An
Bn rename
13
Selection

• find (flt, weekday) for all flights scheduled
  for Mondays
• ???????????weekdayMO (FLT-WEEKDAY)
• the expression in ?expression (R) involves
• operands constants or attribute names of R
• comparison operators ????
• logical operators ??????
• nesting ( )

14
Projection

• find flt for all flights scheduled for Mondays
• ?????????flt(?weekdayMO (FLT-WEEKDAY))
• the attributes in the attribute list of??A1, A2,
  ..., An (R) must be attributes of the operand R

15
Union

• find the flt for flights that are schedule for
  either Mondays, or Tuesdays, or both?????????
• ????????flt(?weekdayMO (FLT-WEEKDAY))
• ????????flt(?weekdayTU (FLT-WEEKDAY))
• the two operands must be "type compatible"

16
Intersection

• find the flt for flights that are schedule for
  both Mondays and Tuesdays?????????
• ????????flt(?weekdayMO (FLT-WEEKDAY))
• ???????flt(?weekdayTU (FLT-WEEKDAY))
• the two operands must be "type compatible"

17
Set Difference

• find the flt for flights that are scheduled for
  Mondays, but not for Tuesdays?????????
• ????????flt(?weekdayMO (FLT-WEEKDAY))
• ????\ ??flt(?weekdayTU (FLT-WEEKDAY))
• the two operands must be "type compatible"
• Note R ? S R \ (R \ S)

18
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(?cityROSWELL(CUSTOMER)) ? ??flt,date
(?fltDL212 ? date1998-09-10 (FLT-INSTANCE)))?\
?flt,date ,cust(RESERVATION)
19
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.fltFLT-WEEKDAY.flt
• (FLT-INSTANCE ??FLT-WEEKDAY))

20
?-join

• make a list of pairs of (FLT1, FLT2) that
  form possible connections
• ?fl1, flt((?flt?fl1, from-airportcode
  da1,dtime dt1, to-airportcode aa1, atime at1,
  date d1
• (FLT-SCHEDULE FLT-INSTANCE ))
• d1date ??aa1from-airportcode ? at1lt dtime
• (FLT-SCHEDULE FLT-INSTANCE))
• the??-operators ????

21
Divideby

• list the cust of customers that have
  reservations on all flight instances
• ?flt, date, cust RESERVATION???
• ???????????????????flt, date (FLT-INSTANCE)

22
ISBL - an example algebra
R ??S R UNION S R ??S R INTERSECT S R \ S R
MINUS S ?A1, A2, ..., An (R) RA1, 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) RA1 B1,.., An Bn
23
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 !

24
ISBL - an example query

• make a list of pairs of (FLT1, FLT2) 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) WHERE D1DATE
  ??AA1FROM-AIRPORTCODE ? AT1lt DTIME)FL1, FLT

25
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)
• ltx1, x2, ..., xngt P(ltx1, x2, ..., xngt)
• you tell the system which result you want, but
  not how to construct it

26
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 lt gt
• 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

27
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

28
Selection

• find (FLT, WEEKDAY) for all flights scheduled
  for Mondays
• t FLT-WEEKDAY(t) ? t.WEEKDAYMO

29
Projection

• find FLT for all flights scheduled for Mondays
• t.FLT FLT-WEEKDAY(t) ? t.WEEKDAY MO

30
Union

• find the FLT for flights that are schedule for
  either Mondays, or Tuesdays, or both?????????
• t.FLT FLT-WEEKDAY(t) ? (t.WEEKDAYMO
  ??t.WEEKDAYTU)

31
Intersection

• find the FLT for flights that are schedule for
  both Mondays and Tuesdays?????????
• t.FLT FLT-WEEKDAY(t) ??t.WEEKDAYMO ?
• ????s??FLT-WEEKDAY(s) ??t.FLTs.FLT ?
  s.WEEKDAYTU)

32
Set Difference

• find the FLT for flights that are scheduled for
  Mondays, but not for Tuesdays?????????
• t.FLT FLT-WEEKDAY(t) ? t.WEEKDAYMO ?
  ?((??s) (FLT-WEEKDAY(s) ??t.FLTs.FLT ?
  s.WEEKDAYTU))

33
Cartesian Product
s.FLT, s.DATE, t.CUST FLT-INSTANCE(s) ?
CUSTOMER(t) ??t.CITYROSWELL?? s.FLTDL212
??s.DATE1998-09-10????????r??FLT-INSTANCE(r) ??r
s ? r.FLTs.FLT???r.DATEs.DATE
??r.CUSTt.CUST)
34
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.FLTt.FLT

35
?-join

• make a list of pairs of (FLT1, FLT2) that
  form possible connections
• s. FLT, t.FLT FLT-SCHEDULE(s) ?
  FLT-SCHEDULE(t) ? ((??u)(??v) FLT-INSTANCE(u) ?
  FLT-INSTANCE(v) ? u.FLTs.FLT ? v.FLTt.FLT ?
  u.DATEv.DATE ? s.TO-AIRPORTCODEt.FROM-AIRPORTCOD
  E ? s.ATIME lt t.DTIME)

36
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.FLTt.FLT ? r.DATEt.DATE ?
  r.CUSTs.CUST))

37
QUEL - an example tuple calculus

• make a list of pairs of (FLT1, FLT2) 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.FLTs.FLT and v.FLTt.FLT and
• u.DATEv.DATE and s.TO-AIRPORTCODEt.FROM-AIRPORTC
  ODE and s.ATIME lt t.DTIME

38
QBE - Projection

• find FLT for all flights scheduled for Mondays

39
QBE - Union

• find the FLT for flights that are schedule for
  either Mondays, or Tuesdays, or both?????????

40
QBE - Intersection

• find the FLT for flights that are schedule for
  both Mondays and Tuesdays?????????

41
QBE - Set Difference

• find the FLT for flights that are scheduled for
  Mondays, but not for Tuesdays?????????

42
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

43
QBE - Natural Join

• make a list with complete flight instance
  information

44
QBE????-join

• make a list of pairs of (FLT1, FLT2) that
  form possible same day connections

45
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