logic

• Relational algrebra allows combining of data from accross tables
• Operand = data that the operation is performed on
• Relation = table = set of tuple
• Selection σ: σ_Predicate(Table) = outputs a table with

  • the same schema as Table
  • including only the tuples in Table satisfying Predicate
• Projection Π: Π_AttributeSet(Table) = outputs a table

  • with all (but only) attributes/tuples in AttributeSet (column)
  • no duplicate rows (eg. if selecting a single (non superkey) column, any repeating values are now duplicate rows)
• Rename ρ: ρ_newName(Table) = outputs a table

  • the exact same table but now called newName
• rename ρ: ρ_newName(_a1, _a2, _a3,...,aN)(Table) outputs a table ​

  • where the table is now called newName
  • the attributes are now called a1, a2 etc
• rename ρ: ρ_oldname/newname(Table) outputs a table

  • where oldname is replaced with newname
  • can affect the table or attributes
• Set union ∪: T_1 ∪ T_2 where T1 and T2 are the same schema = outputs a table with

  • the same schema
  • all tuples of T1 and T2 without duplicates
  • (linking or)
  • used to link 2 statements
• Set difference −: T_1 - T_2 where T1 and T2 are the same schema

  • the same schema
  • tuples in T1 that are NOT in T2
  • (remove tuples in T2 from T1)
• Cartesian product ×: T_1 × T_2​ outputs a table with

  • all attributes of T1 + all attributes of T2 (same name still counts as different entity)
  • every possible combination of rows is mashed together
  • produces T1 no of tuples * T2 no of tuples results
  • Pair with selection to use as a join!
  • (σPROF.pid = TEACH.pid (TEACH x PROF))
• Natural Join ⋈: T_1 ⋈ T_2​ outputs the equivalent of:

  • doing cartesian product
  • ensuring attributes that are in common have the same value
  • removing the duplicate attributes(columns)
  • tables with attributes in common will come up empty
  • tables with the same schema will only show identical tuples
• Intersection ∩: T_1 ∩ T_2​ where T1 and T2 have the same schema

  • the same schema
  • only the tuples that appear in both
  • (linking and)
• Division ÷: T_1 ÷ T_2​ where T2 has a schema that is a subset of T1

  • basically returns the remaining attributes of T1 that fully match the given comparison table (T2) but without that column itself
  • works by: remaining columns of T1 minus the suppliers not supplying all of them, found by doing the non-important column(s) Cartesian product all columns (all possible combinations) minus T1 (to find the leftovers that don't match)