Fundamentals of Database Systems Fourth Edition El Masri & Navathe Instructor: Mr. Ahmed Al Astal

1. University Of Palestine Fundamentals ofDatabase SystemsFourth EditionEl Masri & NavatheInstructor: Mr. Ahmed Al Astal Chapter 10 Functional Dependencies and Normalization for Relational Databases

2. Database Management CH 10 University Of Palestine • Normalization • Dependency Preserving:- • Suppose that the relation R is decomposed into R1,R2, and the set of FDs F that holds on R ar distributed on R1,R2 then: • We can say that the decomposition is dependency preserving iff (F1UF2) ≡F • Otherwise the decomposition is not Dependency Preserving.

3. Database Management CH 10 University Of Palestine • Normalization (Cont.) • Lossless-join decomposition:- • Suppose that the relation R is decomposed into R1,R2, and the set of FDs F that holds on R ar distributed on R1,R2, If the common attribute between R1 and R2 as a key in either R1 or R2 then: • We can say that the decomposition is Lossless-Join • Otherwise the decomposition is Lossy Join

4. Database Management CH 10 University Of Palestine • Normalization (Cont.) • Example • Suppose that we have the following relation std_info: • and the following set of FDs that hold on sd_info • F={Sname Major,Phone, • Cname Instructor, • Sname, Cname Score} • Then The Decomposition : • F1={ Sname  Major, Department} F2={Cname instructor • Sname, Cname Score} • This Decomposition is Dependency Preserving and Lossless Join

5. Database Management CH 10 University Of Palestine • Normalization (Cont.) • Normalization: • The process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations • Normal form: • Condition using keys and FDs of a relation to certify whether a relation schema is in a particular normal form

6. Database Management CH 10 University Of Palestine • Practical Use of Normal Forms • Normalization is carried out in practice so that the resulting designs are of high quality and meet the desirable properties • The practical utility of these normal forms becomes questionable when the constraints on which they are based are hard to understand or to detect • The database designers need not normalize to the highest possible normal form • (usually up to 3NF, BCNF or 4NF)

7. Database Management CH 10 University Of Palestine • Definitions of Keys and Attributes Participating in Keys (1) • A superkeyof a relation schema R = {A1, A2, ...., An} is a set of attributes S subset-of R with the property that no two tuples t1 and t2 in any legal relation state r of R will have t1[S] = t2[S] • A key K is a superkeywith the additional property that removal of any attribute from K will cause K not to be a superkey any more.

8. Database Management CH 10 University Of Palestine • Definitions of Keys and Attributes Participating in Keys (1) • If a relation schema has more than one key, each is called a candidate key. • One of the candidate keys is arbitrarily designated to be the primary key, and the others are called secondary keys. • A Prime attribute must be a member of some candidate key • A Nonprime attribute is not a prime attribute—that is, it is not a member of any candidate key.

9. Database Management CH 10 University Of Palestine • First Normal Form • If a relation schema has more than one key, each is called a candidate key. • One of the candidate keys is arbitrarily designated to be the primary key, and the others are called secondary keys. • A Prime attribute must be a member of some candidate key • A Nonprime attribute is not a prime attribute—that is, it is not a member of any candidate key.

10. Database Management CH 10 University Of Palestine • First Normal Form • Disallows • composite attributes • multivalued attributes • nested relations; attributes whose values for an individual tuple are non-atomic • Considered to be part of the definition of relation

11. Database Management CH 10 University Of Palestine

12. Database Management CH 10 University Of Palestine • BCNF (Boyce-Codd Normal Form) • The strongest Normal Form for the relation • Given arelation R and aset of FDs F hold on R, R is in BCNF (i.e. R is a good relation) iff for each FD in the form α -> β in F : α must be a super key. • Each normal form is strictly stronger than the previous one: • Every 2NF relation is in 1NF • Every 3NF relation is in 2NF • Every BCNF relation is in 3NF • There exist relations that are in 3NF but not in BCNF • The goal is to have each relation in BCNF (or 3NF)

13. Database Management CH 10 University Of Palestine BCNF (Boyce-Codd Normal Form) stdinfo F={ sname  major,phone cname  instructor sname,cname  score } • Each FD on R must satisfy the condition to be in BCNF, otherwise we must divide the relation R into tow relation R1,R2 as follows: • R1(α, β) , R2(R- β) that satisfying the tow conditions • The decomposition is dependency preserving • The decomposition is loosless-join

14. Database Management CH 10 University Of Palestine • BCNF (Boyce-Codd Normal Form) • Stdinforelation is not in BCNF so we must devide the relation as follows: • R1(sname, major, phone), R2(cname, sname,instructor,score) • The second step is ditrbuting the FDs to R1,R2 • F1={snamemajor,phone}, F2={cnameinstructor • cname,snamescore} • The third step we check the decomposition for dependency preserving and lossless-join conditions. R2 R1 F1 F2

15. Database Management CH 10 University Of Palestine • BCNF (Boyce-Codd Normal Form) • This decomposition is dependency preserving because (F1UF2)=FD and loosless-join because the common attribute which is Sname is a key for R1. • The forth step is repeating checking for BCNF for the resulting relation R1,R2. • For F1 On R1, α which is • Sname is akey for R1, So • R1 Is in BCNF R1 F1

16. Database Management CH 10 University Of Palestine BCNF (Boyce-Codd Normal Form) • For R2: • Sname, cname score • α is akeyfor R2 • Cname instructor • α is not akeyfor R2 so,, • We must devide R2 into tow relations R2.1,R2.2 R2 F2 F2={ Sname,Cname->score, Cname->instructor} R2.2 R2.1 F2.1 F2.1 R2.1,R2.2 are both in BCNF Form so finally we have 3 relations which are : R1, R2.1, R2.2 which ar all in BCNF

17. Database Management CH 10 University Of Palestine Summary of BCNF Checking Check Each FD for the condition α is a key. If any FD fails then we divide the relation into tow relation R1(α , β) , R2(R- β(. Distribute the original FD on R1, R2. Check the decomposition for dependency preserving and loosless-join. Repeat the checking for every resulting relation till having all the relations in BCNF Form.

18. Database Management CH 10 University Of Palestine BCNF: another example R FD FD={Bname,SnameBnakerName , BankerNameBname,Office#} Now we must check each FD for the BCNF condition: Bname,CnameBankerName : √ ( Bname,Sname is a key) BankerNameBname,Office# : X ( BankerName is not A key) So we must divide R into tow relations R1,R2 R1 R2 F2={ Ø } F1={ BankerName->Bname,Office# }

19. Database Management CH 10 University Of Palestine • BCNF: another example (Cont.) • Hence BankerName is akey For R1 so the last decomposition is loosless-join, But it is Not dependency Preserving because F1UF2 ≠ FD. • So here we stop and don’t continue with BCNF, we try by 3NF OR 2NF. • Notes: • BCNF has avery restricted condition so not always we have a good decomposition . • BCNF sometimes looses FDs.

20. Database Management CH 10 University Of Palestine • Third Normal Form 3NF • Given arelation R and aset of FDs F hold on R, R is in 3NF iff for each FD in the form α -> β in F at least one of the following conditions satisfies: • α must be a key for R OR • Each attribute in β is Prime attribute. • FD={Bname,SnameBnakerName, • BankerNameBname,Office#} R FD

21. Database Management CH 10 University Of Palestine • 3NF (Cont.) • The last relation is not in 3NF Because for the second FD: α is not a key and Office# is not a prime attribute. • So we must divide R into two relations as follows: • R1(α , all non prime attributes in β ) • R2(R- non prime attributes in β ) R1 R2 F1 F2

22. Database Management CH 10 University Of Palestine • 3NF (Cont.) • F1UF2 = FD So the decomposition is dependency preserving. • BankerName is a key for R1 so The decomposition is loosless join • R1,R2 are in 3NF so finally we have two relations R1,R2 • Important Notes: • 3NF Never ever loose FD. • If R is in BCNF then surely it will be in 3NF • Butt R may be in 3NF but Not in BCNF. R1 R2 F1 F2

23. Database Management CH 10 University Of Palestine • Second Normal Form 2NF • Given arelation R and aset of FDs F hold on R, R is in 3NF iff for each FD in the form α -> β in F at least one of the following conditions satisfies: • α must be a key for R OR • Each attribute in β is Prime attribute. • α is not a subset of any key

24. Database Management CH 10 University Of Palestine 2NF: Example FD={ BC->ADE E->G } For F1 BC is a key For F2 E is not a subset of any key So R is in 2NF R FD

25. Database Management CH 10 University Of Palestine 2NF: Other Example R FD FD={ BC->ADE D->B E->G } F1: BC is akey F2: E is not a subset of any key F3: B is prime attribute So R is in 2NF The decomposition when the test fails is similar to 3NF