Boyce-Codd Normal Form
Relation R with FDs is in BCNF if for each A -> B, A is a key
Fourth Normal Form
Relation R with MVDs is in 4NF if for each nontrivial A ->> B, A is a key
Apply(SSN, cName, date, major)
Can apply to each college once for one major
Colleges have non-overlapping application dates
FDs: SSN, cName -> date, major date -> cName
Keys: SSN, cName
BCNF: No, because the second FD doesn’t have a key on the left side.
A2(SSN, date, major)
Now in BCNF, but not good design. Separated date and major of application from college.
Student(SSN, HSname, GPA, priority)
Multiple HS okay, priority determined from GPA
FDs: SSN -> GPA GPA -> priority SSN -> priority
Keys: SSN, HSname
S2(SSN, HSname, GPA)
Now in BCNF, but not good design. Lost GPA to priority FD.
After decomposition, no guarantee dependencies can be checked on decomposed relations.
Scores(SSN, sName, SAT, ACT)
Multiple SAT and ACT allowed
All queries return name + composite score for SSN
FDs: SSN -> sName
MVDs: SSN, sName ->> SAT
4NF: No. Violating FD and MVD.
S1(SSN, sName, SAT)
S2(SSN, sName, ACT)
Now in 4NF, not great design.
Not necessarily good design. Too decomposed.