Relational Algebra Flashcards
Relational Calculus
Higher level, declarative language for relational Databases
Product of Relational Calculus operation
New Relation
Elements of Relational Calculus
Tuples and Domain
Relational Algebra
A procedural query language, which takes instances of relations as input and yields instances of relations as output
Groups of Relational Algebra
Set theory operations and relational db specific operations
Operations in Relational Algebra Set Ops
UNION, INTERSECTION, DIFFERENCE (MINUS), CARTESIAN PRODUCT (CROSS JOIN)
Operations in Relational DB Ops
SELECT, PROJECT, JOIN, RENAME
Unary Operations
Operations that apply on a single relation (SELECT, PROJECT)
Binary Operations
Operate on two relations by combining related tuples (JOIN)
Aggregate Functions
Operations that summarize data from Tables or Joins (SUM, COUNT)
Selectivity
Fraction of tuples selected by a Selection Condition
Selection Condition
Statement of what data for a Select statement to display
Symbol for SELECT in Relational Algebra
σ
Sigma
Symbol for Relation in Relational Algebra
R
Degree in SELECT
# of attributes in a Relation (Same as the degree of R)
Cascade
Sequence of SELECT statements with a Conjuntive (AND, OR)
Commutative
σ (σ(R)) = σ(σ(R))
PROJECT
Selects columns from a relation. All tuples are DISTINCT
Symbol for PROJECT in Relational Algebra
π
Pi
Degree in PROJECT
of attributes in a Relation
Multiset
Set of tuples with duplicates. AKA “bag”
In-Line Expression
Single line of Relational Algebra depicting results that don’t have a given name.
RENAME
Renames a relation or attribute name, aka AS
Symbol for RENAME in Relational Algebra
ρ or ϱ, with S as relation name or B as attribute name
Rho
Union Compatibility
Relations in question for a binary operation must have
- Same degree (# of attributes)
- Same domain
Cartesian Product
AKA Cross Join, binary set operation but doesn’t force Union Compatibility
Symbol for Cartesian Product in Relational Algebra
X
Symbol for JOIN in Relational Algebra
⋈
JOIN
combine related tuples from two relations
into single “longer” tuples.
allows us to process relationships
among relations
Notation for JOIN statement
DEPT_MGR ← DEPARTMENT ⋈ Mgr_ssn=Ssn EMPLOYEE
THETA JOIN
allows for arbitrary comparison relationships (such as ≥)
EQUIJOIN
a theta join using the equality operator (instead of comparison)
NATURAL JOIN
an equijoin on attributes that have the same name in each relationship.
Complete Set
the set of relational algebra operations {σ,π,∪,ρ,–,×}
any of the other original relational algebra operations can be
expressed as a
sequence of operations from this set
For example, the INTERSECTION operation can be expressed by using UNION and MINUS as follows:
R∩S≡(R∪S) – ((R–S)∪(S–R))
Symbol for DIVISION in Relational Algebra
÷
DIVISION
Find the values that do not belong in the answer , and remove them from the list of possible answers.
Query Tree
data structure that corresponds to a relational algebra expression
input relations of the query as
leaf nodes
relational algebra operations as internal nodes
Set of Aggregate Functions
COUNT, SUM, AVERAGE, MAXIMUM, and MINIMUM
Symbol for Aggregate Function in Relational Algebra
ℑ
script F
Recursive Closure
applied to a recursive relationship between tuples of the same type
example of a recursive operation is to retrieve all supervisees of an employee (e) at all levels—that is, all employees (e’) directly supervised by e all employees
e’’ ℑ directly supervised by each employee e’, all employees e’’’ directly supervised by each employee e’’, etc
,
OUTTER JOIN
Join that maintains all the tuples of one or both relations
Range Relation
The relation a tuple “ranges over” (or belongs to)
Relational Calc Tuple Variable
A variable ranging over a relation that may take any value which is a tuple from that relation
{t|COND(t)}
Parts of a Relational Calc Tuple
{t.Fname,t.Lname | EMPLOYEE(t) AND t.Salary>50000}
range relation - EMPLOYEE(t)
selected combination - t.Salary > 50000
requested attribute - t.Fname, t.Lname
