/* 
 * NEU Silicon Valley
 * CS 5200 Database Management Systems
 * http://dbis-uibk.github.io/relax/calc.htm
 * Wikipedia - Relational algebra data set
 *
 * Examples of how the various relational operators work.
 * Uncomment each one to execute the expression and see 
 * what it does with the Employee and Dept relations.
 
 * For help see relational algebra reference:
 * http://dbis-uibk.github.io/relax/help.htm#relalg-reference
 */
 
-- ---------------
-- Basic relations
-- ---------------
 
/* The Employee relation.
 */
-- Employee

/* The Dept relation.
 */
-- Dept

-- ----------
-- Select (σ)
-- ----------

/* Select tuples from Employee with DeptName = 'Finance'.
 * Result is relation of same type and only selected tuples.
 */
-- σ DeptName = 'Finance' Employee

/* Select tuples from Employee with DeptName ≠ 'Finance' 
 * Result is relation of same type and only selected tuples.
 */
-- σ DeptName ≠ 'Finance' Employee

/* Select tuples from Employee with DeptName = 'Sales' or 
 * DeptName ='Executive' 
 * Result is relation of same type and only selected tuples.
 */
 -- σ DeptName = 'Sales' ∨ DeptName = 'Executive' Employee

-- -----------
-- Project (π)
-- -----------

/* Project the Name and DeptName attributes of Employee.
 * Result is relation of new type with only Name and 
 * DeptName attributes.
 */
-- π Name, DeptName Employee

/* Project the DeptName, Name, EmpId  attributes of Employee.
 * Result is relation of new type with same attributes 
 * reordered.
 */
-- π DeptName, Name, EmpId Employee


/* Project the Name and DeptName attributes of the
 * selection of Employee tuples with DeptName ≠ 'Finance'
 * Result is relation of new type with only Employee Name 
 * and DeptName attributes.
 */
-- π Name, DeptName (σ DeptName ≠ 'Finance' Employee)

-- -----------
-- Group (γ)
-- -----------

/* Group to count total number of employee tuples.
 * Result is relation with Count attribute and one tuple
 * with number of employees in the relation.
 */
-- γ count(*)->Count Employee

/* Group to count number of employee tuples per department.
 * Result is relation with DeptName and Count attributes
 * showing the number of employees per department.
 */
-- γ DeptName;count(*)->Count Employee

/* Group to count total number of employees with the same
 * Name regardless of EmpId.
 * Result is relation with Name and Count attributes
 * showing the number of employees with the same name.
 */
-- γ Name;count(*)->Count Employee

-- -----------
-- Sort (τ)
-- -----------

/* Employee relation sorted ascending (default) by Name 
 * attribute
 */
-- τ Name Employee

/** Employee relation sorted descending by Name attribute
 */
-- τ Name desc Employee

/** Employee relation sorted ascending by EmpId attribute
 */
-- τ EmpId asc Employee

/* Sort in descending order by Name 
 * the projection of the Name and DeptName attributes of
 * the selection of Employee tuples with DeptName ≠ 'Finance'
 * Result is relation of new type with only Name and DeptName 
 * attributes.
 */
-- τ Name desc (π Name, DeptName (σ DeptName ≠ 'Finance' Employee))

-- -----------
-- Rename (ρ)
-- -----------

/* Rename EmpId attribute to EmployeeID
 */
-- ρ EmployeeID←EmpId Employee

/* Rename Employee relation to Worker
 */
-- ρ Worker Employee

/* Rename Employee relation to Worker and the
 * EmpId relation to WorkerId
 */
-- ρ WorkerID←EmpId (ρ Worker Employee)


-- -----
-- Union
-- -----

/* Take the union the Name and Dept name fields from
 * Employee and the Manager and DeptName fields from Dept.
 */
 -- (π Name, DeptName Employee) ∪ (π Manager, DeptName Dept)

 -- ---------
 -- Intersect
 -- ---------
 
/* Take the intersection the Name and Dept name fields from
 * Employee and the Manager and DeptName fields from Dept.
 */
-- (π Name, DeptName Employee) ∩ (π Manager, DeptName Dept)

 -- ----------
 -- Difference
 -- ----------

/* Take the difference the Name and Dept name fields from
 * Employee and the Manager and DeptName fields from Dept.
 */
-- (π Name, DeptName Employee) - (π Manager, DeptName Dept)


-- --------------
-- Cross Join (⨯)
-- --------------

/* Cross join the Employee and Dept relations.
 * Result is a relation of new type with attributes of both relations
 * with all possible combinations of tuples from each relation.
 * Note: Attributes are qualified by relation name, so DeptName
 * attribute from each relation is distinct
 */
-- Employee ⨯ Dept

/* Select tuples with matching DeptName attributes from
 * Cross join of Employee and Dept relations.
 * Result is new type with tuples that have matching DeptName
 * attributes.
 */
-- σ Employee.DeptName = Dept.DeptName (Employee ⨯ Dept)

-- ----------------
-- Natural Join (⨝)
-- ----------------

/* Natural join of the Employee and Dept relations with 
 * matching DeptName attributes. 
 * Result is new type with tuples that have matching DeptName
 * attributes (same as previous select of cross join)
 */
-- Employee ⨝ Employee.DeptName = Dept.DeptName Dept

-- -------------------
-- Left Outer Join (⟕)
-- -------------------

/* Left Outer Join of the Employee and Dept relations on 
 * matching DeptName attributes.
 * Result is new type with tuples from Employee relation, 
 * combined with matching tupples from Dept relation or 
 * NULL attributes if no Dept tuple with matching DeptName 
 * attribute.
 */
-- Employee ⟕ Employee.DeptName = Dept.DeptName Dept

-- --------------------
-- Right Outer Join (⟖)
-- --------------------

/* Right Outer Join of the Employee and Dept relations on 
 * matching DeptName attributes.
 * Result is new type with tuples from Dept relation,
 * combined with matching tupples from Employee relation or 
 * NULL attributes if no Employee tuple with matching 
 * DeptName attribute.
 */
-- Employee ⟖ Employee.DeptName = Dept.DeptName Dept

-- --------------------
-- Full Outer Join (⟗)
-- --------------------

/* Full Outer Join of the Employee and Dept relations on 
 * matching DeptName attributes.
 * Result is new type whose tuples have attributes from 
 * Dept relation, combined with matching tupples from 
 * Employee relation, or NULL attributes if no Employee 
 * tuple with matching DeptName value or no Dept tupple 
 * with matching DeptName value.
 */
-- Employee ⟗ Employee.DeptName = Dept.DeptName Dept

-- --------------------
-- Left Semijoin (⋉)
-- --------------------

/* Left Semijoin on the Employee and Dept relations.
 * Result is the tuples from Employee relation where 
 * there is a matching attribute in Dept relation.
 */
-- Employee ⋉ Dept

-- --------------------
-- Right Semijoin (⋊)
-- --------------------

/* Right Semijoin on the Employee and Dept relations.
 * Result is the tuples from Dept relation where 
 * there is a matching attribute in Employee relation.

 */
-- Employee ⋊ Dept

-- -------------
-- Antijoin (▷)
-- -------------

/* Antijoin on the Employee and Dept relations.
 * Result is the tuples from Employee relation that 
 * has no matching attributes in Dept relation.
 */
-- Employee ▷ Dept