relational algebra tuple calculus and sql short notes

Quick Revision Grid: TRC to RA

Quick Revision Grid: TRC to RA Concept Key Characteristic Relational Algebra Equivalent TRC Nature Declarative ("What" to retrieve) N/A RA Nature Procedural ("How" to retrieve) N/A Existential (\exists) "There exists at least one..." Join (⋈), Intersection (\cap) Universal (\forall) "For all..." Division (\div) Negation (\neg) "Does not exist..." Set Difference (-) Safe Expression Guarantees finite result Variables bound to specific relations

SQL Queries: Rapid Revision Checklist

SQL Queries: Rapid Revision Checklist Execution Order: FROM \to WHERE \to GROUP BY \to HAVING \to SELECT \to ORDER BY. WHERE vs HAVING: WHERE filters rows (no aggregates). HAVING filters groups (uses aggregates). COUNT Trap: COUNT(*) includes NULL rows. COUNT(col) ignores NULL values in col . Outer Join: Unmatched rows are preserved and padded with NULL . Empty Aggregates: AVG, SUM, MIN, MAX on empty sets return NULL, not 0 . Universal Quantification: "For all" is implemented as NOT EXISTS ( ... NOT EXISTS ... ) . GROUP BY Rule: Every column in SELECT must be in GROUP BY or inside an aggregate function.

Relational Algebra: Rapid Revision Checklist

Rapid Revision Checklist Self-Join Requirement Always use the rename operator (ρ) to create distinct aliases before joining a relation with itself. Division Meaning Answers "for all" or "contains every" queries. Division Formula R \div S = Π_{X} (R) - Π_{X} ((Π_{X} (R) \times S) - R), where X are attributes in R but not in S . Division Schema Attributes (R \div S) = Attributes (R) - Attributes (S) . "At Least N" Pattern Requires joining N copies of the relation, which demands exactly N - 1 cross product/join operations. Distinctness in Self-Join When finding "at least N" distinct entities, explicitly enforce inequality (\neq =) on the primary keys across all N aliases.

1 more card in this chapter

Relational Algebra, Tuple Calculus and SQL Short Notes for GATE CS

Relational Algebra, Tuple Calculus and SQL short notes for GATE CS: 4 study cards covering concepts, formulas, shortcuts and exam traps, plus solved practice questions.

Quick Revision Grid: TRC to RA

Concept	Key Characteristic	Relational Algebra Equivalent
TRC Nature	Declarative ("What" to retrieve)	N/A
RA Nature	Procedural ("How" to retrieve)	N/A
Existential ( $\exists$ )	"There exists at least one..."	Join ( $⋈$ ), Intersection ( $\cap$ )
Universal ( $\forall$ )	"For all..."	Division ( $\div$ )
Negation ( $\neg$ )	"Does not exist..."	Set Difference ( $-$ )
Safe Expression	Guarantees finite result	Variables bound to specific relations

SQL Queries: Rapid Revision Checklist

Execution Order: FROM $\to$ WHERE $\to$ GROUP BY $\to$ HAVING $\to$ SELECT $\to$ ORDER BY.
WHERE vs HAVING: WHERE filters rows (no aggregates). HAVING filters groups (uses aggregates).
COUNT Trap: COUNT(*) includes NULL rows. COUNT(col) ignores NULL values in col.
Outer Join: Unmatched rows are preserved and padded with NULL.
Empty Aggregates: AVG, SUM, MIN, MAX on empty sets return NULL, not 0.
Universal Quantification: "For all" is implemented as NOT EXISTS ( ... NOT EXISTS ... ).
GROUP BY Rule: Every column in SELECT must be in GROUP BY or inside an aggregate function.

Relational Algebra: Rapid Revision Checklist

Rapid Revision Checklist

Self-Join Requirement

Always use the rename operator (

ρ

) to create distinct aliases before joining a relation with itself.

Division Meaning

Answers "for all" or "contains every" queries.

Division Formula

R \div S = Π_{X} (R) - Π_{X} ((Π_{X} (R) \times S) - R)

, where

X

are attributes in

R

but not in

S

Division Schema

Attributes (R \div S) = Attributes (R) - Attributes (S)

"At Least N" Pattern

Requires joining

N

copies of the relation, which demands exactly $N - 1$ cross product/join operations.

Distinctness in Self-Join

When finding "at least N" distinct entities, explicitly enforce inequality (

\neq =

) on the primary keys across all

N

aliases.

Cardinality Estimation: Rapid Revision Checklist

Rapid Revision Checklist

Your quick reference for cardinality estimation formulas and rules.

Equality:

T_{σ_{A = v} (R)} = \frac{T _{R}}{V ( A , R )}

Range ( $A > v$ ):

T_{R} \times \frac{max ( A ) - v}{max ( A ) - min ( A )}

AND (Independent):

\frac{T _{σ_{C_{1}} (R)} \times T _{σ_{C_{2}} (R)}}{T _{R}}

OR:

T_{C_{1}} + T_{C_{2}} - T_{C_{1} \land C_{2}}

Precondition: Multiplication rules require attributes to be independently distributed.

Distinct Values: For integer ranges

[min, ma x]

V = ma x - min + 1