Normal Forms through BCNF CPSC 356 Database Ellen

Normal Forms through BCNF CPSC 356 Database Ellen Walker Hiram College (Includes figures from Database Systems by Connolly & Begg, © Addison Wesley 2002)

Unnormalized Form • A table that includes one or more repeating groups. Student. Name Student. Classes Room. No Professor Minnie Mouse English 101 Psych 240 Accounting 110 BC Gerst 120 Hins 209 H. Higgins Lucy Van Pelt Scrooge Mc. D Goofy Basketweaving 1 KC English 101 BC Daffy Duck H. HIggins

First Normal Form (1 NF) • Each row, column intersection must have a single value – No composite attributes – No multivalued attributes • Put the table into 1 NF by repeating student names for each repeating group.

Example in 1 NF Student. Name Student. Class Room. No Professor Minnie Mouse English 101 BC H. Higgins Minnie Mouse Psych 240 Gerst. 120 Lucy van Pelt Minnie Mouse Accounting 110 Hins. 209 Scrooge Mc. D Goofy Basketweaving KC Daffy Duck Goofy English 101 H. Higgins BC

Example: FDs and Candidate Key • Functional Dependencies – Student. Class -> Room. No, Prof – Prof -> Room. No • Candidate Key – Since Student. Name cannot be determined from anything else, it must be part of the key. Student. Class gives the rest. – Student. Name, Student. Class -> all attributes

Second Normal Form (2 NF) • Every non primary key is fully functionally dependent on the primary key • Full Functional Dependency – Attribute B depends on A, but not on any subset of A • In other words, the primary key determines every other attribute, but no subset of the primary key determines any other attribute!

Example is not 2 NF • Primary key is Student. Name, Student. Class – Student. Name must be included because it can’t be derived from anything else – Student. Class distinguishes tuples of the same student • Room. No and Prof are not Fully Functionally Dependent on primary key – Student. Class -> Room. No, Prof

Going to 2 NF • Split the relation into (at least) 2 relations – One relation has the subset of the primary key and all attributes that depend on it – The other relation has the rest of the attributes and an appropriate foreign key • In our example: – Course. Info (Student. Class, Room. No, Prof) – Student(Student. Name, Student. Class)

Notation for functional dependency • First row is the relational schema • Additional row for each dependency – “down arrow” for left side – “up arrow” for right side – Example goes here • For 2 NF, no dependency has down arrow for only part of the primary key. • Non-key dependencies don’t matter, e. g. Prof->Room

Example 13. 8 (p. 392)

Third Normal Form (3 NF) • Schemas in 3 NF have no transitive dependencies of non-key attributes • Transitive dependencies cause potential duplication in the relations • A transitive dependency is when – A, B, and C are attributes in the relation – A->B and B->C – C is not an attribute of the relation’s key

Recognizing Transitive Dependencies • If any attribute has both a down-arrow and an uparrow (staff. No in example 13. 8), then there is a transitive dependency. • If the attribute is part of the relation’s key, the transitive dependency does not violate 3 NF • Any dependency between 2 non-keys will be transitive! (key->non-key 1; non-key 1 -> non-key 2) • Therefore, assuming that every Prof has a favorite Room, we have – Student. Course->Prof, Prof->Room. No

From 2 NF to 3 NF • Recognize transitive dependencies • Remove attributes involved in transitive dependency to their own relation, leaving only a “foreign key” behind. • Example: – Course. Info (Student. Class, Prof) – Student(Student. Name, Student. Class) – Favorite. Room(Prof, Room. No)

Summary of Normal Forms Every attribute depends on 1. the key, 2. The whole key, 3. And nothing but the key! Condition 1 describes the definition of key Condition 2 describes 2 NF Condition 3 describes 3 NF (or BCNF)

Boyce Codd Normal Form (BCNF) • A relation is in BCNF if and only if every determinant is a candidate key • A determinant is a set of attributes on which some other attribute is fully functionally dependent. • Schemas that are 3 NF but not BCNF are rare. They require – Two or more composite candidate keys – Candidate keys share at least one attribute

BCNF vs. 3 NF • BCNF if every FD X->Y satisfies one of the following conditions: – The FD is trivial (Y is subset of X) – X is a superkey • 3 NF if BCNF or the following is true: – Every attribute in X but not Y belongs to a candidate key

BCNF Can Lose Dependencies • When putting a relation into BCNF, it is possible that a functional dependency will not be preserved, because the related attributes will be split into separate relations. • Tradeoff: – 3 NF preserves all dependencies – BCNF prevents all redundancies

Example: Find 3 NF, Is it BNF? Student, Course, Semester -> Prof, Semester -> Course (Prof teaches 1 course / sem) Course, Semester, Time -> Prof, Semester, Time -> Room, Course Prof, Semester, Course, Time -> Room (redundant!)

Up / Down Arrow Form (Not 2 NF) Student Course Prof. Sem. v v ^ v ^ v v Room Time v ^ v

Find Candidate Keys • Student, Semester, and Time have no uparrow (cannot be determined by other attributes), so must be part of a candidate key • Candidate keys: – Student, Course, Semester, Time – Student, Prof, Semester, Time

Remove Partial Dependencies • Course depends only on Prof & Semester – R 1: {Prof, Semester, Course} • Room depends only on Prof, Semester and course; – R 2: {Prof, Semester, Room, Time} • Original with Room and Course removed – R 3: {Student, Prof, Semester, Time}

2 NF Partition (Also 3 NF) Course Prof. Sem ^ v v Prof. Sem Room Time v v ^ v Prof. Sem Student Time Unused Depen- dencies Below v v ^ v v

Dependency Analysis • No transitive dependencies – This is good news. We are in 3 NF • Some dependencies are “broken” – They do not connect attributes of a single relation – This is “non-dependency-preserving” • If we join back the relations we created will we get the same information? – Yes, in this case – No, in general

Algorithm to Get 3 NF • Start with 1 NF • Remove all violations of 2 NF by decomposition • Remove all violations of 3 NF by further decomposition • It’s possible you will break dependencies

Dependency Preserving 3 NF • First, massage the dependencies into a standard form (minimal cover): – Every right side is a single attribute – No attributes are redundant – No dependencies are redundant • Next, create a relation for each of the revised dependencies (guaranteed 3 NF because only one dependency per relation) • Finally, create one more relation for the primary key, if it’s not already included in one of the others.

Another Algorithm to get 3 NF • Find minimal cover of FDs – Every FD has one attribute on right – No FD can be derived from other FDs in the set • Combine FDs with same attributes on the left • Create a relation for each remaining FD • If no relation contains the original superkey for all attributes, construct one relation with just the superkey • This set is guaranteed to be 3 NF and equivalent to the original relation

Finding Minimal Cover • Split up the dependencies with multiple right sides – X->Y, Z becomes X->Y, X-> Z • Check for redundant attributes on left side – Compute closure of each set that leaves out one attribute. If it includes the right side, remove the extra attribute. • Check for redundant dependencies – For each dependency, compute the closure of the set on the left side against all the other dependencies except the one you’re testing. If you find the attribute on the right side in the closure, you can leave that dependency out.

Example: Single Right Sides T 1. Student, Course, Semester -> Prof T 2. Prof, Semester -> Course T 3. Course, Semester, Time -> Prof T 4. Prof, Semester, Time -> Room, Course T 5. Prof, Semester, Course, Time -> Room T 4 is split: T 4 a. Prof, Semester, Time -> Room T 4 b. Prof, Semester, Time -> Course

Example: Finding Redundant Attributes • Consider T 1: Student, Course, Sem -> Prof – {Student, Course}+ = {Student, Course} – {Student, Sem}+ = {Student, Sem} – {Course, Sem}+ = {Course, Sem} • Since Prof cannot be derived without all 3 attributes, T 1 has no redundant attributes • T 2 and T 3 and T 4 a are similar (no redundant attributes) • T 2 is T 4 b with Time removed; obviously Time is redundant and T 4 b can be removed from the set.

Example: Redundant Attributes in T 5 • T 5: Prof, Semester, Course, Time -> Room • {Prof, Semester, Time}+ = {Prof, Semester, Time, Course, Room} – Prof, Semester, Time -> Room by T 4 a – Therefore, Course is redundant in T 5 – Removing Course from T 5 makes it the same as T 4 a

Example After Redundant Attribute Removal T 1. Student, Course, Semester -> Prof T 2. Prof, Semester -> Course T 3. Course, Semester, Time -> Prof T 4 a. Prof, Semester, Time -> Room

Example: Remove Redundant Dependencies T 1. Student, Course, Semester -> Prof //Not redundant (see next slide) T 2. Prof, Semester -> Course //Course cannot be derived any other way T 3. Course, Semester, Time -> Prof //Not redundant (see next slide) T 4 a. Prof, Semester, Time -> Room //Room cannot be derived any other way

Show T 1 is not Redundant • Remove T 1 from the set of dependencies • Compute {Student, Course, Semester}+ using only T 2, T 3, and T 4 a – No left sides are satisfied by this combination, so the closure is simply {Student, Course, Semester} • Because Professor was not in the closure, T 1 is not redundant (Similar reasoning for T 3)

Creating the Relations • Each relation has all (and only) the attributes mentioned in one dependency; R 1 = { Student, Course, Semester, Prof } R 2 = { Prof, Semester, Course } R 3 = { Course, Semester, Time, Prof } R 4 = { Prof, Semester, Time, Room } Since none of these contains a key for the whole relation, we add R 5 = { Student, Course, Semester, Time }

Evaluating the Result • Every functional dependency from the closure affects exactly one relation in the schema – No dependencies are lost (this is dependency preserving) • At most one non-key attribute per relation, so a transitive dependency would have to lead back to a key attribute (as in R 3) • Therefore, our result is in 3 NF and dependency preserving.

Result is Not BCNF • The result is not BCNF because of the extra dependency in R 3: – Course Semester Time Prof – Prof, Semester -> Course – It’s ok to have this dependency for 3 NF, but not for BCNF.