The four informal measures of quality for relation schema design
1. Semantics of the relation attributes; 2. reducing redundant information in tuples; 3. reducing null values in tuples; 4. disallowing spurious tuples.
The three update anomalies
Insertion anomalies, deletion anomalies, modification anomalies.
Three reasons for nulls
Attribute not applicable or invalid; attribute value unknown (may exist); value known to exist but unavailable.
Guideline #4 (spurious tuples)
Relations should satisfy the lossless join condition: no spurious tuples should be generated by joining any relations.
What is normalization?
Decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations.
When does α → β hold on R?
If and only if, for any legal relation r(R), whenever two tuples agree on α they also agree on β.
r(A, B) = (1,4), (1,5), (3,7): A → B? B → A?
A → B does NOT hold; B → A does hold.
Determinant and dependent in A → B
A is the determinant; B is the dependent — each value of A is associated with exactly one value of B.
Superkey and candidate key in terms of FDs
K is a superkey iff K → R. K is a candidate key iff K → R and no proper subset α of K has α → R.
What does 1NF disallow?
Composite attributes, multivalued attributes and nested relations — only single atomic values are permitted.
EMP_PROJ (Ssn, Ename, {Phone#}) in 1NF
EMP_PROJ1 (Ssn, Ename) and EMP_PROJ2 (Ssn, Phone#).
Prime attribute
An attribute that is a member of the primary key K.
Full vs partial functional dependency
Full: removing any attribute from the determinant breaks the FD. Partial: some attributes can be removed and the FD still holds.
{SSN, PNUMBER} → HOURS and {SSN, PNUMBER} → ENAME: full or partial?
HOURS: full (neither SSN nor PNUMBER alone determines it). ENAME: partial (SSN → ENAME holds).
2NF (definition)
Every non-prime attribute is fully functionally dependent on the primary key — no partial dependency on the primary key.
When can the 2NF test be skipped?
When the primary key contains a single attribute.
Transitive functional dependency
X → Y is transitive if some set Z, neither a primary nor a candidate key, has X → Z and Z → Y — e.g., SSN → DNUMBER → DMGRSSN.
3NF (definition)
In 2NF, and no non-prime attribute is transitively dependent on the primary key.
BCNF (definition)
Whenever an FD X → A holds in R, X is a superkey of R.
Is every 3NF relation in BCNF?
No — every BCNF relation is in 3NF, but there exist relations in 3NF that are not in BCNF.
{Student, course} → Instructor; Instructor → Course: normal form and decomposition
In 3NF, not BCNF (Instructor is not a superkey). Decompose into {Student, Instructor} and {Instructor, Course}.
Car_Sale: why is it not in 2NF?
Car# → Date_sold and Salesman# → Commission% are not full FDs on the primary key (Car#, Salesman#) — they are partial dependencies.