The Relational Model

relation = math. for table, NOT from ER-model

A relation has two parts:

• heading: set of attributes A:T with name A and type T
  denotes a predicate (truth-valued function)
• body: set of tuples A:V with attribute A and value V
  i.e. a set of tuples that conforms to the heading
  each tuple denotes a true proposition, i.e. instantiates the predicate

When the context is clear the attribute names can be ommitted, e.g.
the (unnamed) tuples of relation Age are { (John, 30), (Jane, 25) }

Relations are sets of tuples:

→ there is no preferred order for the tuples, and
→ there is no point in stating a fact more than once.

Relational Algebra: a brief Overview

Operations on one or more relation always result in a relation (not some other kind of object).

• Restrict A: all attributes of tuples in A satisfying a condition
• Project A: specified attributes of all tuples in A
  remove duplicates!
• Product A,B: all combinations of tuples in A and B
• Union A,B: all tuples appearing in either or both
  remove duplicates!
• Difference A,B: all tuples in A but not in B
• Intersect A,B: all tuples appearing in both
• Join: A,B: all combinations with common value(s) for common attribute(s)

  Emp = { (Smith, Accounting), (Jones, Accounting), (Brown, Sales) }
  Bonus = { (Accounting, 300), (Sales, 400), (Marketing, 0) }
  Join Emp, Bonus = { (Smith, Accounting, 300), (Jones, Accounting, 300), (Brown, Sales, 400) }

• Divide A,B,C: tuples in A that match via B with all tuples in C

  Doctor = { (Semmelweiss), (Billroth), (Paracelsus) }
  Treatment = { (Billroth, Smith), (Billroth, Jones), (Billroth, Brown), (Semmelweiss, Smith), (Paracelsus, Luther) }
  Sick = { (Smith), (Brown) }
  Divide Doctor, Treatment, Sick = { (Billroth) }

  Interpretation e.g. doctors who treat all patients
  in the supplier-parts example: suppliers (relation S) that supply (SP) all parts (P)