Deprecated: add_custom_background is deprecated since version 3.4.0! Use add_theme_support( 'custom-background', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Deprecated: add_custom_image_header is deprecated since version 3.4.0! Use add_theme_support( 'custom-header', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the jquery handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the smoothscroll handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Deprecated: The called constructor method for WP_Widget in Yoko_SocialLinks_Widget is deprecated since version 4.3.0! Use __construct() instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5177
Intro to Databases: Relational Design Theory, Shortcomings of BCNF/4NF | Megan Taylor

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.


Notice: Undefined index: host in /home/pqmz7qzy9yt5/public_html/wp-content/plugins/jetpack/modules/stats.php on line 209