Database Management and Warehousing

Sheet 1

Functional Dependencies, Normalization & Relational Algebra

4 formulas

Key formulas

Functional Dependency ☆

\[ X \rightarrow Y \]

every value of X determines exactly one value of Y

BCNF Condition ☆

\[ X \rightarrow Y \Rightarrow X \text{ is a superkey} \]

for every non-trivial FD

3NF Condition ☆

\[ X \to Y \text{ trivial, OR } X \text{ superkey, OR } Y \subseteq \text{some candidate key} \]

Core Relational Algebra Operators ☆

\[ \sigma \text{ (select)} \quad \pi \text{ (project)} \quad \bowtie \text{ (join)} \quad \cup,\ -,\ \times \]

Test your recall

Can you recall the functional dependency formula?

Reveal formula

\[ X \rightarrow Y \]

GATE traps 2 traps

BCNF is strictly stronger than 3NF

Every relation in BCNF is also in 3NF, but not every 3NF relation is in BCNF -- 3NF allows one specific exception (Y part of a candidate key) that BCNF doesn't.

Partial dependency is a 2NF issue, not 3NF

A non-prime attribute depending on only PART of a composite candidate key violates 2NF. 3NF is about transitive dependencies between non-prime attributes -- don't conflate the two.