Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Relational Design Theory, Shortcomings of BCNF/4NF

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

November 12, 2011 | Comments Off on Intro to Databases: Relational Design Theory, Shortcomings of BCNF/4NF | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: