بسم الله الرحمن الرحيم

1. بسم الله الرحمن الرحيم Lecture (12&13)

2. Chapter 10 Functional Dependencies and Normalization for Relational Databases

3. Informal Design Guidelines for Relational Databases • What is relational database design? • The grouping of attributes to form "good" relation schemas •  Two levels of relation schemas • The logical "user view" level • The storage "base relation" level •  Design is concerned mainly with base relations •  What are the criteria for "good" base relations? 

4. Informal Design Guidelines for Relational Databases • We first discuss informal guidelines for good relational design • Then we discuss formal concepts of functional dependencies and normal forms • - 1NF (First Normal Form) • - 2NF (Second Normal Form) • - 3NF (Third Normal Form)

5. Informal design Guidelines For Good Relation • Four informal measures of quality for relation schema design: • Semantics of the attributes . • Reducing the redundant values in tuples. • Reducing the null values in tuples . • Disallowing the possibility of generating spurious tuples.

6. Semantics of the relation attributes : • The relation is the set facts (each tuple in the relation represent a fact). • Grouping of attributes in one relation determine the clearness of semantics (meaning) of the attribute values in the relation tuple.( How the attribute values in tuple related to one another).

7. Guideline(1): • Design a relation schema so that it is easy to explain its meaning. • Do not combine attributes from multiple entity types and relationship types into a single relation. • If Relation schema corresponds to one entity type or one relationship type then the semantic of this relation is clear otherwise it becomes semantically unclear.

8. Example • Employee (emp#,emp-name,salary,birthdate,DNum) • Department (DNum,DName,Dmang#) • Emp-project(emp#,project #,no of hours) Three above relations have clear semantics why? • Emp-Dep (emp#,emp-name,salary,birthdate,DNum DNum,DName,Dmang#) • Emp-project-Dep (emp#, project #, D#. no of hours) The last two relations have unclear semantics why?

9. Reducing the redundant values in tuples :- • One goal of schema design is to minimize the storage space that base relations (files) occupy. • Grouping of attributes in relation affected on storage space. • Redundancy problems: • Wasting of storage space. • Update anomalies problems ( (التحديث الشاذ

10. Example The above relation is suffering from redundancy, What are the other problems of this relation?

11. Update anomalies classified as : • Insertion anomalies. • Deletion anomalies. • Modification anomalies.

12. Insertion anomalies: • To insert new employee in emp-dep relation ,if the employee does not work for a department as yet so that the attribute values for department must be null values. • If the employee department is determined say D14 ,so we must enter the attribute values for D14 so that they consistent with values for D14 in other tuples. • Also insertion new department may leads to insertion anomalies, how?

13. Deletion anomalies: If we delete an employee tuple from emp-dep ,and that employee represents the last employee working for a particular department ,then the information concerning that department is lost from DB. Example: delete employee E1 from emp-dep. Leeds to loosing of D15 information.

14. Modification anomalies: In emp-dep if we change the manager of D14 ,we must update all the tuples of all employees who are working in that department. • Guideline (2): • Design the base relation so that no insertion,deletion, or modification anomalies are present in the relation. • second guideline is consistent with, and in a way a restatement of the first guideline.

15. The first and second guidelines may sometimes have to be violated in order to improve the performance of certain queries.

16. null values in the tuples: • Problems of null values: • Wasting of storage space. • Problem of joining between relations • Problems when aggregate operations such as count,sum,or average are used ,how to account null values. •  Reasons for nulls: • Attribute not applicable or invalid • Attribute value unknown (may exist) • Value known to exist, but unavailable Guideline(3): As far as possible avoid placing attributes in a base relation whose values may frequently be null

17. Generation of spurious tuples: Spurious tuples, tuples resulting from joining between two relations with incorrect information (data or facts).

18. Example

22. Guideline(4): design relation schemas so that they can be joined with equality conditions on attributes that are either primary keys or foreign keys to guarantee that no spurious tuples are generated.

23. الاعتماد الدالي(Functional Dependency) والتبسيط (Normalization) • الاعتماد الدالى (FD):- • Are used to specify formal measures of the "goodness" of relational designs • And keys are used to define normal forms for relations • Are constraints that are derived from the meaning and interrelationships of the data attributes • من المفاهيم المهمة والمتعلقة بالتبسيط . • هى خاصية توصف بها العلاقات بين الصفات ، داخل العلاقة الواحدة وتعتمد على معنى الصفات ((semantic meaning of attributes • ويعتبر الاعتماد الدالى ايضاً قيد بين مجموعتين من الصفات داخل العلاقة.

24. تعريف الاعتماد الدالي ((1: • A functional dependency denoted by x y ,between two sets of attributes x and y that are subsets of relation R specifies a constraint on possible tuples that can form a relation state r of R . the constraint is that ,for any two tuples t1 and t2 in r that have t1[x]=t2[x],we must also have t1[y]=t2[y]. • X is functionally determines y • y is functionally dependent on x • x:left hand side of FD, y :R.h.s of FD

25. تعريف الاعتماد الدالي(2) :- إدا كانت B,A هى صفات فى علاقه R . • B تعتمد دالياً على (B is a functionally dependent on A) وتكتب إذا كانت إى قيمة ل A فى العلاقة R مرتبطة بقيمة واحدة ل B ، ولكن قيمة B قد تعطى اكثر من قيمة ل A وتسمى A بالمحدد(determinant) وتكون دائماً على جهة اليسار. B A

26. مثال :_ من العلاقة Student • (إى قيمة لرقم الطالب مرتبطة مع قيمة واحدة فقط لاسمه ) . Std# Stdname

27. مثال :- خارطة العلاقة employee :- Employee (E#,Ename , Bdate ,D# , Depname , Dage) E# {Ename , Bdate} D# Ename ( Ename is not FD on D # ) {E# , D#} {Depname ,Dage} FD={E# {Ename , Bdate} , {E# , D#} {Depname ,Dage}}

28. توضيح الاعتماد الدالي في شكل diagram Fd1 Fd2

29. Additional Examples of FD constraints • Social security number determines employee name • SSN -> ENAME • Project number determines project name and location • PNUMBER -> {PNAME, PLOCATION} • Employee ssn and project number determines the hours per week that the employee works on the project • {SSN, PNUMBER} -> HOURS

30. Inference Rules for FDs (1) • Given a set of FDs F, we can infer additional FDs that hold whenever the FDs in F hold • Armstrong's inference rules: • IR1. (Reflexive) If Y subset-of X, then X -> Y • IR2. (Augmentation) If X -> Y, then XZ -> YZ • (Notation: XZ stands for X U Z) • IR3. (Transitive) If X -> Y and Y -> Z, then X -> Z • IR1, IR2, IR3 form a sound and complete set of inference rules • These are rules hold and all other rules that hold can be deduced from these

31. Inference Rules for FDs (2) • Some additional inference rules that are useful: • Decomposition: If X -> YZ, then X -> Y • Union: If X -> Y and X -> Z, then X -> YZ • Psuedotransitivity: If X -> Y and WY -> Z, then WX -> Z • The last three inference rules, as well as any other inference rules, can be deduced from IR1, IR2, and IR3 (completeness property)

32. التبسيطNormalization • هى احدى نظريات النمودج العلائقى وهى عمليه تجزئه لخارطة العلاقة وذلك لجعل العلاقه مبسطة أى انها تحمل فكرة واحدة ( نتحدث عن E-T واحد او R-T واحدة ) تدور حول المفتاح الاساسى ( المفتاح الاساسى هو المحدد الوحيد ) . • Normalization: • The process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations

33. Cont. • يجب ان تعرف قاعدة البيانات العلائقية على انها مجموعه من العلاقات المبسطة (Set of normalized relations) . • إى علاقه تخضع الى اختبار أو تحليل معين وذلك لتحديد ما إذا كانت العلاقة مبسطة ام لا ثم توضع فى احدى مراتب التبسيط ( صيغ التبسيط ) . (Normal forms) Normal form of the relation: refers to the highest normal form condition that it meets , and hence indicates the degree to which it has been normalized

34. مرتبة التبسيط الاولى FIRST NORMAL FORM (1NF) :- • تكون العلاقة او الجدول فى مرتبة التبسيط الاولى اذا كان الجدول مسطحاً . وذلك بأن يكون الجدول خالى من المجموعات المتكررة (repeatinggroups) وان تكون عناصره بسيطة (single ) وليست (nestedrelation) • بمعنى آخر: أى تقاطع صف مع عمود فى الجدول يحتوى على قيمة واحدة غير قابله للتجزئه(atomic value) .

35. مثال (1) Department ( Dep# , Dname, Dlocation)

36. مثال(2) Employee ( E# , Ename, Dependants ) employee العلاقات Department و Employee ليست فى مرتبة التبسيط الاولى ( not in 1NF) لأن الاولى تحتوى على مجموعات متكررة والثانية تحتوى على علاقة متداخله.

37. لوضع العلاقه department فى INF لها ثلاثه خيارات :- Dep# Dlocationهى عبارة عن المفتاح الاساسى. ما هو عيب هدا الخيار؟

38. الخيار الثانى:- • تجزئة العلاقة الى علاقتين R1 (Dep# , Dname ) R2 )Dep# , Dlocation( • الخيار الثالث :- • إذا كان للقسم عدد معين من المواقع ( مثلاً لايتعدى الثلاث مواقع ) فتكون العلاقه فى INF كالآتى:- • Department( Dep# , Dname,Dlocation1 , D location 2, Dlocation 3) • ولكن هذا الخيار قد يؤدى الى ظهور (Null values) فى حالة أن القسم له أقل من ثلاثة مواقع .

39. لوضع العلا قه Employee فى INF مثال(2) تكون كالآتى : هدا الجدول فى INF مع التكرار والمفتاح الاساسى للجدول E# , D#

40. مرتبة التبسيط الثانية2NF)) :- تكون العلاقة فى مرتبة التبسيط الثانية إذا كانت في 1NFو الصفات خارج المفتاح الاساسى تعتمد إعتماد دالي كلي على المفتاح الاساسى (ليس على جزء منه) . • 2NF:a relation in 1NF and every non prime attribute in relation is fully functionally dependent on primary key. • فىهده الحالة يكون المفتاح الاساسى مركب من اكثر من صفة composite key ) ( Defn of full functional dependency: • If A and B are attributes of a relation , B is a fully functionally dependent on A if B is functionally dependent on A but not on any proper subset of A.

41. E# Ename, Bdate D# Ename, Bdate Dname Dage E#,D# E#,D# • مثال :- لوضع العلاقة Employee فى 2NF بعد وضعها فى 1NF • اولاً اختبار العلاقه :- • Employee ( E# , Ename, Bdate, D#, Dname, Dage ) • E#, D#هو المفتاح الاساسى للعلاقه • الصفات خارج المفتاح الاساسى هى Dage, Dname, Ename (Partial functional dependency) (full functional dependency) (full functional dependency) العلاقة ليست فى 2NF لأن اسم الموظف ( صفة خارج صفات المفتاح الاساسى ) تعتمد على جزء من المفتاح الاساسى وليست على المفتاح الاساسى كله .

42. ولجعل العلاقة فى 2NF تجزأ العلاقة الى علاقتين R1 (E# , Ename,Bdate ) R2 ( E# , D# , Dname , Dage )

43. مرتبة التبسيط الثالثة 3NF :- • تكون العلاقه فى 3NF إذا كانت كل الصفات خارج صفة المفتاح تعتمد فقط على المفتاح الاساسى وليس على اى صفة خارج صفات المفتاح الاساسى ، اى ان المفتاح الاساسى هو المحدد الوحيد لكل صفات العلاقة خارج المفتاح ( only one determinant ) 3NF:arelation in first and second normal form, and in which no non prime attribute is transitively dependent on the primary key. Defn of transitive dependency: if A,B and C are attributes of a relation such that if A B and B C ,then C is transitively dependent on A via B.

44. مثال • Suppliers ( S# , Sname , City , Transport cost )

45. S# S# Sname City City Transport cost هده العلاقة ليست فى 3NF لان سعر الترحيل يعتمد transitivelyعلى رقم المورد لأنه يعتمد علىالمدينة City. • ولوضع هده العلاقة فى 3NF يجب تجزئتها الى :- • RI (S# , Sname, City) • R2 ( City, Transport cost)

46. {Person ID, projectNo } No of hours projectNo Budget مثال • R ( Person ID, Project No, Budget, No of hours) • R in INF • نختبر R هل هى فى 2NF :- R ليست فى 2NF لماذا؟

47. لوضعها فى 2NF تجزأ العلاقة الى :- • R1 ( Person, ID, Project No, No of hours) • R2 )Project No , Budget ) 3NF فى R2 , R1

48. مضار عدم التبسيط • الحذف :- من المثال اعلاه ( العلاقة قبل التبسيط ) إذا كان العامل رقم S79 هو الموظف الوحيد الذي يعمل في المشروع P2 وحذفنا هذا الصف من العلاقة R نكون قد فقدنا المعلومة الوحيدة عن ميزانية المشروع P2 وهذه تعتبر مشكلة الـ( Deletion anomalies) . • الاضافة: إذا اضفنا مشروع جديد قبل تحديد العمال فهذا يؤدي الي ظهور Null Values ضمن الـ PK وهذا غير مسموح به (InsertionAnomalies). • التعديل :إذا اردنا تعديل ميزانية المشروع P1 فربما نحتاج لتعديل الميزانية قي أكثر من صف (علي حسب عدد العمال) (ModificationAnomalies) . • عدم التبسيط يؤدي الي تكرار البيانات التي تؤدي الى مشاكلال Update Anomaliesوعليه فان عملية التبسيط مفيدة في حالة التحديث ولكن تنشأ المشكلة أننا ربما نحتاج في التطبيق لتكوين الجدول الأول غير المبسط لكتابة التقارير وهذا يحتاج لعملية ربط الجداول المبسطة وعملية الربط مكلفة من ناحية الزمن والمساحة في الذاكرة.

49. Summery • Normalization of data means a process of analyzing the given relation schemas based on their FDs and primary keys to achieve properties of : • 1-minimizing the redundancy • 2-minimizing update anomalies.

50. تجزئة العلاقة فى عملية التبسيط تستوجب وجود الخصائص التالية: • lossless join or nonadditive join property • وذلك لضمان عدم ظهور ال spurious tuples قى حالة ربط العلاقات المجزئة نتيجة لعملية التبسيط مع بعضها البعض لتكوين العلاقة الاصلية. • the dependency preservation property • وهي لضمان ان اتحاد كل ال FDs في العلاقات المجزئة يمثل ال FDs في العلاقة الاصلية.