GK One-Line Question & Answer

A+ = ABCDE (A to B to C, A to D to E, so A determines all)

AB, BC, and BD (compute closures: AB+ = ABCD, BC+ = ABCD, BD+ = ABCD; check minimality: no proper subset works for each)

If X to YZ then X to Y and X to Z (you can split the right-hand side - derived from Reflexivity and Transitivity)

Yes: X to YZ (given), so X to Y (decomposition), then X to XY (augmentation), XY to W (given), X to W by transitivity

An attribute A in the LHS of an FD X to Y such that removing A still preserves the equivalence: (F minus FD) union ((X-A) to Y) is equivalent to F

If X to Y and WY to Z then WX to Z (a generalization of transitivity where the intermediate attribute set Y is augmented with W on both sides)

Yes: E to A (given), A to BC (given), B to D (given); E to A to B to D, so E to D by transitivity chain

X multidetermines Y is trivial if Y is a subset of X or X union Y equals the entire attribute set of the relation (trivial MVDs always hold and convey no information)

If X multidetermines Y in relation R(X,Y,Z) where Z=R-X-Y then X multidetermines Z also holds; also every FD X to Y implies X multidetermines Y

Fc = A to B, B to C (A to C is redundant since it is derivable; both remaining FDs are necessary and have no redundant attributes)

The set of ALL functional dependencies that can be logically derived from F using Armstrongs axioms - the complete set of FDs that hold in every relation satisfying F

F covers G (G is a subset of F+) if every FD X to Y in G can be derived from F - tested by checking if Y is a subset of X+ computed under F for each FD in G

X to Y is a full FD if removing any attribute from X breaks the FD (Y no longer depends on X); partial if Y still depends on some proper subset of X

No other FDs are specified; the composite key is the only determinant of quantity placing the relation in BCNF assuming no other FDs exist

Some FDs in the original relation cannot be enforced by checking just one decomposed relation - they would require joining multiple decomposed relations to verify making constraint enforcement expensive

AD+ = ADE only: start with A,D, apply D to E getting ADE. Cannot reach B or C without them already in the closure. AD does NOT determine all attributes, so AD is NOT a candidate key.

X to Y where Y is a subset of X - it always holds in any relation (by reflexivity) because a set of attributes always determines its own subset providing no real constraint on data

If X to Y and X to Z then X to YZ (you can combine right-hand sides when the left-hand side is the same)

A theorem stating that the binary decomposition of R into R1 and R2 is lossless if and only if (R1 intersect R2) multidetermines (R1-R2) OR (R1 intersect R2) multidetermines (R2-R1) holds as an MVD in R

E+ = EABCD by E to A to BC to D and CD to E (already have E): E determines all attributes in the relation

Armstrongs axioms are both sound (only derive valid FDs) and complete (can derive ALL valid FDs) meaning F+ computed by Armstrong axioms exactly equals the set of all FDs logically implied by F

Satisfied by an instance: this specific relation instance happens to have X to Y hold (extensional). Implied by a set F: X to Y holds in EVERY possible instance that satisfies F (intensional) - a much stronger condition capturing the semantics of the schema

Fc = A to B, B to C, C to D, D to A - each FD is individually necessary (no FD is derivable from the others) since removing any one breaks the cycle

A set of FDs where none of them can be derived from the others - important because it means each FD adds genuine new information about the schema and removing any one would change the closure F+

For soundness: Reflexivity holds trivially by definition. Augmentation: if t1[X]=t2[X] then t1[XZ]=t2[XZ] which implies t1[Y]=t2[Y] gives t1[YZ]=t2[YZ]. Transitivity: if t1[X]=t2[X] implies t1[Y]=t2[Y] and t1[Y]=t2[Y] implies t1[Z]=t2[Z] then t1[X]=t2[X] implies t1[Z]=t2[Z]

Testing whether a decomposition has the lossless join property and whether functional dependencies are preserved by applying FDs to a canonical table (tableau)

For a set of attributes X the dependency basis is the finest partition of (R-X) such that X multidetermines each block; this partitions the other attributes into independent groups that X independently multidetermines

FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT

WHERE filters individual rows BEFORE grouping; HAVING filters groups AFTER GROUP BY and aggregation - HAVING can reference aggregate functions, WHERE cannot

NULL if expr1 equals expr2 (returns expr1 otherwise); used to avoid division-by-zero errors: NULLIF(count, 0) returns NULL instead of causing error when count=0

Aggregate functions (except COUNT(*)) IGNORE NULL values - COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values

To define named temporary result sets that can be referenced multiple times within a query improving readability and enabling recursive queries (WITH RECURSIVE)

CHAR(n) is fixed-length (always uses n bytes, padded with spaces if shorter); VARCHAR(n) is variable-length (uses only the space needed plus 1-2 bytes for length storage)

It returns NULL (the CASE expression evaluates to NULL when no WHEN matches and no ELSE is provided)

A subquery that references a column from the outer query causing it to be executed once for each row of the outer query (vs. non-correlated subquery which executes once independently)

EXISTS returns TRUE if a subquery returns at least one row (stops at first match), preferred over IN when the subquery could return NULLs (IN with NULL has counterintuitive behavior) or when checking existence is more efficient

An empty result set - the condition WHERE location = NULL is always FALSE (must use IS NULL instead of = NULL)

ROW_NUMBER(): unique sequential number with no gaps or ties; RANK(): same rank for ties then skips numbers (1,1,3); DENSE_RANK(): same rank for ties no gaps (1,1,2)

Accessing values from subsequent rows (LEAD) or preceding rows (LAG) within the result partition useful for computing differences between consecutive rows without self-joins

Defining the window frame for window functions - specifying which rows to include in each computation relative to the current row