Midterm 1 Notes  Lectures 1 to 5
Unlock Document
University of Toronto Mississauga
Computer Science
CSC343H5
Ivan Chow
Fall
Description
CSC343  Intro To Databases!
 Projection (Π) : Filter by column SELECT A,B FROM R )"
 Selection (σ) : Filter rows according to equalitySELECT * FROM R WHERE A=3 ) "
 Renaming (ρ) : Rename columns (SELECT * FROM R [AS] S) or (SELECT A [AS] B FR"M R)
 Cartesian Product (R × S) : All possible combinations ( SELECT L FROM R,S )"
 Union ( R ∪ S) : R, S needs to have the same columns"
 Intersection (R ∩ S): R, S needs to have the same columns"
 Difference (R − S): R, S needs to have the same columns"

Assignment R(A1,..,An) := S"
 Natural Join (R⋈ S) : Needs to have at least one common column.( SELECT L FROM R NATURAL JOIN S )"
 Theta Join (R ⋈ S) : Cartesian join, rows ﬁltered by condition C. (SELECT L FROM R JOIN S ON C )"
C
 Set model vs Bags (Bags has duplicates, Sets don’t)"
 Duplicate Elimination δ(R) : Removes duplicate rows ( SELECT DISTINCT A FROM R )"
 Sorting (τa,b: Sorts by attributes by a, then b. ““ makes descending. SELECT L FROM R ORDER BY A [ASC  DE)"]
 Extended Projection Π a,b+→ x,→y(R) (SELECT A, B+C AS X, D AS Y FROM R )"
 Outer Join ( R ⋈ [L  R])  Dangling tuples are included with null(⊥). Left, and Right outer join includes
dangling tuples from R, and S.) (SELECT L FROM R NATURAL [FULL  LEFT  RIGHT] OUTER JOIN S )"
 Grouping γ (L)  Operators  SUM, AVG, MIN, MAX, COUNT. eg. γ a,AVG(b))"
 Pattern Matching: ( SELECT * FROM R WHERE A [NOT] LIKE ‘hello’ ) Also, ‘_ello’ ‘%lo’ (single, multi char)"

Boolean Algebra: ( SELECT * FROM R WHERE A < 2 OR A >= 3 )"
 Subquery (FROM)  SELECT * FROM flights NATURAL JOIN (SELECT aid, cap FROM planes WHERE cap > 100) AS R.
 If a subquery is guaranteed to return 1 row and 1 col. Then subquery can be in WHERE x = (SUBQ) ;"
 If S returns nonempty it’s trueSELECT L FROM R WHERE [NOT] EXISTS (SUBQ);
 SELECT L FROM R WHERE E [NOT] IN (S); TRUE if and only if E is equal to one of the tuples returned by S.
 SELECT L FROM R WHERE E [NOT] ANY (S) TRUE iff ∃▯∈▯ st ▯ ▯."
 SELECT L FROM R WHERE E [NOT] ALL (S); TRUE iff ∀▯∈▯ st ▯ ▯."
 Grouping ( SELECT L FROM R WHERE C1 GROUP BY A [HAVING C2]; ) "
• WHERE > GROUP BY > HAVING. WHERE remove rows before grouping, HAVING remove groups after grouping."
• Use DISTINCT to ignore duplicates eg. COUNT(DISTINCT A)."
 InsertionINSERT INTO R(A ,1..,A ) nALUES (v ,…,1 ); On INSERT INTO R(A, B) (SELECT D
More
Less
Related notes for CSC343H5