L.05 Flashcards
Continuing With Relational Algebra
What is the purpose of Relational Algebra (RA)?
It is a formal mathematical language used to define queries over relational models.
What is the result of applying an RA operation on a relation?
The result is always a new relation, making RA a closed algebra.
What does the Selection (𝞂) operation do?
It filters rows in a relation based on a specified condition.
Example:
Find employees in department 4:
𝞂DNO=4(EMPLOYEE)
What does the Projection (π) operation do?
It selects specific columns (attributes) from a relation and removes duplicates.
Example:
List first names, last names, and salaries:
πLNAME,FNAME,SALARY(EMPLOYEE)
What is Generalized Projection in RA?
It allows functions on attributes rather than selecting them directly.
Example:
Compute yearly salary:
πLNAME,FNAME,12 * SALARY(EMPLOYEE)
What is the purpose of Assignment (←) in RA?
It stores intermediate results for complex RA operations.
DEP5_EMPS ← 𝞂DNO=5(EMPLOYEE)
πLNAME,FNAME,SALARY(DEP5_EMPS)
What does the Renaming (ρ) operation do?
It renames the relation and its attributes for better readability.
ρYEMPLOYEE(LNAME, FNAME, YSALARY (πLNAME,FNAME,12 * SALARY(EMPLOYEE))
What are the set operations in RA?
Union (∪): Combines unique tuples from both relations.
Intersection (∩): Returns tuples present in both relations.
Difference (-): Returns tuples in one relation but not in the other.
What are the conditions for type compatibility between two relations in RA?
1) They must have the same number of attributes.
2) The domains of corresponding attributes must match.
What does the Cartesian Product (×) operation do?
It pairs every tuple in one relation with every tuple in another, creating a new relation.
Example:
Ranks × Suits → Creates all possible playing cards.
Why is Join (⨝) commonly used instead of Cartesian Product (×) + Selection (𝞂)?
Because Join directly combines related tuples without creating unnecessary combinations.
DEPT_MGR ← DEPARTMENT ⨝ MGRSSN=SSN EMPLOYEE
What is an Equijoin?
A Join where the condition consists only of equality (=).
R ⨝ R.a = S.b S
What is a Natural Join ( or ⨝)* in RA?
A special case of Equijoin where the join is done on all attributes with the same name in both relations.
R(A,B,C,D) * S(C,D,E)
What problem does an Outer Join solve?
It preserves unmatched tuples in one or both relations instead of discarding them.
Types:
Left Outer Join (⟕): Keeps all tuples from the left relation.
Right Outer Join (⟖): Keeps all tuples from the right relation.
Full Outer Join (⟗): Keeps all tuples from both relations.
What are Aggregate Functions in RA?
Functions that summarize values over multiple tuples.
Examples:
ℱSUM Salary(EMPLOYEE) → Total salary of all employees.
ℱMAX Salary(EMPLOYEE) → Maximum salary.
ℱCOUNT Ssn(EMPLOYEE) → Number of employees.
How do we apply aggregation with grouping in RA?
Use grouping attributes before applying the aggregate function.
Example:
DNO ℱCOUNT Ssn, AVERAGE Salary(EMPLOYEE)
(Groups by DNO and computes count & average salary per department.)
What is unique about the COUNT function in RA?
It does not matter which attribute is counted—it always returns the number of tuples.
Does RA remove duplicates before computing SUM, COUNT, or AVERAGE?
No, duplicates remain during calculation.
Why is Relational Algebra Important?
Why is Relational Algebra fundamental to databases?
It forms the foundation of SQL queries.
Helps in query optimization.
Provides a formal way to express data retrieval.
What are the most important takeaways from Relational Algebra?
✔ Selection (𝞂), Projection (π), Renaming (ρ).
✔ Joins & Foreign Keys.
✔ Set Operations (∪, ∩, -).
✔ Aggregation & Grouping.
✔ Outer Joins for handling missing data.
