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
Example #1
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.
A1(date, cName)
A2(SSN, date, major)
Now in BCNF, but not good design. Separated date and major of application from college.
Example #2
Student(SSN, HSname, GPA, priority)
Multiple HS okay, priority determined from GPA
FDs: SSN -> GPA GPA -> priority SSN -> priority
Keys: SSN, HSname
BCNF: No.
S1(SSN, priority)
S2(SSN, HSname, GPA)
S3(SSN, GPA)
S4(SSN, HSname)
Now in BCNF, but not good design. Lost GPA to priority FD.
After decomposition, no guarantee dependencies can be checked on decomposed relations.
Example #3
Scores(SSN, sName, SAT, ACT)
Multiple SAT and ACT allowed
All queries return name + composite score for SSN
FDs: SSN -> sName
Keys: None
MVDs: SSN, sName ->> SAT
4NF: No. Violating FD and MVD.
S1(SSN, sName, SAT)
S2(SSN, sName, ACT)
S3(SSN, sName)
S4(SSN, SAT)
S5(SSN, ACT)
Now in 4NF, not great design.
Example #4
College(cName, state)
CollegeSize(cName, enrollment)
CollegeScores(cName, avgSAT)
CollegeGrades(cName, avgGPA)
BCNF/4NF: Yes.
Not necessarily good design. Too decomposed.
Shortcomings:
dependency enforcement
query workload
over-decomposition