Normalization

1. Normalization Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

2. What is Normalization • Normalization is a set of rules to systematically achieve a good design • If these rules are followed, then the DB design is guarantee to avoid several problems: • Inconsistent data • Anomalies: insert, delete and update • Redundancy: which wastes storage, and often slows down query processing

3. Problem I: Insert Anomaly Student Student Info Professor Info Question: Could we insert any professor ? Note: We cannot insert a professor who has no students. Insert Anomaly: We are not able to insert “valid” value/(s)

4. Problem II: Delete Anomaly Student Student Info Professor Info Question: Can we delete a student and keep a professor info ? Note: We cannot delete a student that is the only student of a professor. Delete Anomaly: We are not able to perform a delete without losing some “valid” information.

5. Problem III: Update Anomaly Student VV VV Student Info Professor Info Question: Can we simply update a professor’s name ? Note: To update the name of a professor, we have to update in multiple tuples. Update Anomaly: To update a value, we have to update multiple rows. Update anomalies are due to redundancy.

6. Problem IV: Inconsistency Student VV Student Info Professor Info What if the name of professor p1 is updated in one place and not the other!!! Inconsistent Data: The same object has multiple values. Inconsistency is due to redundancy.

7. What if Combine Schemas? • Suppose we combine borrowand loanto get • bor_loan= (customer_id, loan_number, amount ) • A loan can be given to multiple customers • Result is possible repetition of information (L-100 in example below) When to combine and when to decompose???

8. How to Decompose (Lossy Decomposition) After the join, did not get back the original correct data

9. What is Needed… • Functional Dependency • A method to find “dependencies” between attributes • Normalization Theory • Rules to remove harmful dependencies, when they exist • Relational decomposition • Break R (A,B,C,D) into R1 (A, B) and R2 (B, C, D) • These two together are used to: • Decide whether a particular relation R is in “good” form • If not, how to decompose R to be in a “good” form

10. Functional Dependencies (FDs)

11. Keys : Revisited • A key for a relation R(a1, a2, …, an) is a set of attributes, K, that together uniquely determine the values for all attributes of R. • A Candidatekey is minimal: no subset of K is a key. • A super keyneed not be minimal • Aprime attribute: an attribute that is part of a key

12. Functional Dependencies (FDs) Student Suppose we have the FD: sNumber address That is,there is a functional dependency from sNumber to address Meaning: A student number determines the student address == For any two rows in the Student relation with the same value for sNumber, the value for address must be same.

13. Functional Dependencies (FDs) • Require that the value for a certain set of attributes determines uniquely the value for another set of attributes • A functional dependency is a generalization of the notion of a key • FD: A1,A2,…An  B1, B2,…Bm L.H.S R.H.S The values in the L.H.S uniquely determines the values in the R.H.S attributes

14. FD and Keys Student Primary Key : <sNumber> • Questions : • Does a primary key implies functional dependencies? Which ones ? • Does unique keys imply functional dependencies? Which ones ? • Does a functional dependency imply keys ? Which ones ? Observation : Any key (primary or candidate) or superkey of a relation R functionally determines all attributes of R.

15. Functional Dependencies (FDs) • Let R be a relation schema where • α⊆R and β⊆R -- α and β are subsets of R’s attributes • The functional dependency α→β holds on R if and only if: • For any legal instance of R, whenever any two tuples t1 and t2agree on the attributes α, they also agree on the attributes β. That is, • t1[α]=t2[α] ⇒ t1[β] =t2[β] A B A  B (Does not hold) B  A (holds)

16. Functional Dependencies & Keys • K is a superkey for relation schema R if and only if • K → R -- K determines all attributes of R • K is a candidate key for R if and only if • K→R, and • No α⊂K, α→R Keys imply FDs, and FDs imply keys

17. Properties of FDs • Consider A, B, C, Z are sets of attributes • Reflexive (trivial): • A  B is trivial if B  A

18. Properties of FDs (Cont’d) • Consider A, B, C, Z are sets of attributes • Transitive: • if A  B, and B  C, then A  C • Augmentation: • if A  B, then AZ  BZ • Union: • if A  B, A  C, then A  BC • Decomposition: • if A  BC, then A  B, A  C

19. Closure of a Set of FunctionalDependencies • Given a set Fset of functional dependencies, there are other FDs that can be inferred based on F • For example: If A → Band B → C, then we can infer that A → C • Closure set F  F+ • The set of all FDs that can be inferred from F • We denote the closure of F by F+ • F+ is a superset of F • Computing the closure F+ of a set of FDs can be expensive

20. Inferring FDs • Suppose we have: • a relation R (A, B, C, D) and • functional dependencies A  B, C D, A  C • Question: • What is a key for R? • We can infer A  ABC, and since C  D, then • A  ABCD • Hence A is a key in R

21. Attribute Closure • Attribute Closure of A • Given a set of FDs, compute all attributes X that A determines • A  X • Attribute closure is easy to compute • Just recursively apply the transitive property • A can be a single attribute or set of attributes

22. Algorithm for Computing Attribute Closures • Computing the closureof set of attributes {A1, A2, …, An}: • Let X = {A1, A2, …, An} • If there exists a FD: B1, B2, …, Bm  C, such that every Bi  X, then X = X  C • Repeat step 2 until no more attributes can be added. • X is the closure of the {A1, A2, …, An}attributes • X = {A1, A2, …, An} +

23. Example: Attribute Closure • Assume relation R (A, B, C, D, E) • Given F = {A  B, B  C, C D  E } • What is the attribute closure of A (A+)? • A+ = {A} • A+ = {A, B} • A+ = {A, B, C}

24. Example 1: Inferring FDs • Assume relation R (A, B, C) • Given FDs : A  B, B  C, C  A • What are the possible keys for R ? • Compute the closure of each attribute X, i.e., X+ • X+ contains all attributes, then X is a key • For example: • {A}+ = {A, B, C} • {B}+= {A, B, C} • {C}+= {A, B, C} • So keys for R are <A>, <B>, <C>

25. Example 2: Inferring FDs • Assume relation R (A, B, C, D, E) • Does F = {A  B, B  C, C D  E } • imply A  E? • The above question is the same as • Is E in the attribute closure of A (A+)? • Is A  E in the function closure F+ ? A  E does not hold A D  ABCDE does hold A D is a key for R

26. Why did We Study FDs • To discover all dependencies between attributes • To know the keys of relations • To decompose the relations to better schemas • Decomposition should not be Lossy (should be Lossless)

27. sNumber sName pNumber pName s1 Dave p1 MM s2 Greg p2 MM Student Professor sNumber sName pNumber pNumber pName s1 Dave p1 p1 MM s2 Greg p2 p2 MM Student Professor sNumber sName pName pNumber pName S1 Dave MM p1 MM S2 Greg MM p2 MM Decomposing Relations StudentProf FDs: pNumber  pName Lossless Lossy

28. Normalization

29. Normalization • Set of rules to remove harmful dependencies, when they exist • Decide whether a particular relation R is in “good” form • If not, decompose R to be in a “good” form • If a relation is in a certain normal form, then it is known that certain kinds of problems are avoided or minimized

30. First Normal Form (1NF) • Attribute domain isatomicif its elements are considered to be indivisible units (primitive attributes) • Examples of non-atomic domains are multi-valued and composite attributes • A relational schema R is in first normal form (1NF) if the domains of all attributes of R are atomic • Non-atomic values complicate storage and encourage redundant (repeated) storage of data We assume all relations are in 1NF

31. Boyce-Codd Normal Form (BCNF) A relation schema R is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F+of the form α→β where α ⊆ R and β ⊆ R, at least one of the following holds: • α → β is trivial (i.e.,β⊆α) • α is a superkey for R

32. Decomposing a Schema into BCNF • If R is not in BCNF because of non-trivial dependency α →β, then decompose R • R is decomposed into two relations • R1 = (α U β )-- α is super key in R1 • R2 = (R-(β-α))-- R2.α is foreign keys to R1.α This decomposition is lossless (Because R1 and R2 can be joined based on α, and αis unique in R1)

33. Example of BCNF Decomposition StudentProf FDs: pNumber pName Student Professor FOREIGN KEY: Student (PNum) references Professor (PNum)

34. Example 2 Relation: SCI (student, course, instructor) FDs: student, course  instructor instructor  course Decomposition: SI (student, instructor) Instructor (instructor, course)