1 / 45

The Relational Model - theoretical foundation

Explore the theoretical foundation of the relational model, including data structures, constraints, operations, and algebra. Learn about tuple calculus, domain calculus, and views. Understand the importance of data structures, attribute names, and constraints like keys and referential integrity. Discover the power and syntax of relational DMLs like relational algebra, tuple calculus, and domain calculus.

elza
Télécharger la présentation

The Relational Model - theoretical foundation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 , ..., 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

  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( A1:D1, 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. 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#

  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. 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”);

  9. 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

  10. 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

  11. 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

  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Sdivideby [A1 B1,.., An Bn] rename

  13. 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: ( )

  14. 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

  15. 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"

  16. 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"

  17. 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)

  18. 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)

  19. 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))

  20. 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: Š  ° =

  21. 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)

  22. 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]

  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. 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#]

  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): { <x1, x2, ..., xn> | P(<x1, x2, ..., xn>) } • 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: Š  < > ° = • 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. 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

  28. FLT-WEEKDAY flt# weekday Selection • “find (FLT#, WEEKDAY) for all flights scheduled for Mondays { t | FLT-WEEKDAY(t) t.WEEKDAY=MO}

  29. FLT-WEEKDAY flt# weekday Projection • “find FLT# for all flights scheduled for Mondays { t.FLT# | FLT-WEEKDAY(t) t.WEEKDAY = MO}

  30. 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)}

  31. 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)}

  32. 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))}

  33. 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#)}

  34. 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# }

  35. 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) }

  36. 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#))}

  37. 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;

  38. FLT-WEEKDAY FLT# WEEKDAY P. =MONDAY QBE - Projection • “find FLT# for all flights scheduled for Mondays

  39. 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”

  40. FLT-WEEKDAY FLT# WEEKDAY P._SX MONDAY _SX TUESDAY QBE - Intersection • “find the FLT# for flights that are schedule for both Mondays and Tuesdays”

  41. 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”

  42. 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”

  43. 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”

  44. 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”

  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

More Related