Chapter 5 The Relational Data Model and Relational Database Constraints

1. بسم الله الرحمن الرحيمLecture (7) Chapter 5 The Relational Data Model and Relational Database Constraints

2. An introduction of relational model • قدمه الباحثTed Codd في 1970 (IBM Research ). • يعتبر من أشهر أنواع الـImplementation models ويستخدم في كثير من الـ DBMS الحالية . • يمتاز بسهولة وبساطة مفاهيمه وأساسه الرياضي(Mathematical Foundation) • يعتمد علي أساس مفهوم العلاقة الرياضية Mathematical Relation))ويخضع لقوانين نظرية المجموعات Set theory)).

3. Basic concepts of the model • تعرف قاعدة البيانات في النموذج العلائقي عليأنها مجموعة من العلاقات (set of relations) . • العلاقة تمثل بجدول من القيم (Table of values) وهي تشبه لحد ما ملف السجلات المسطح. • وتعرف العلاقة علي انها مجموعة من الصفوف ((set of rows. • اي صف (row) يمثل مجموعة من القيم (valuesof set) والتي تمثل حقيقة . • أعمدة الجدول أوالعلاقة تسمي بـ(columns) وتمثل صفات العلاقة (attributes). • الصفوف في النموذج العلائقي يطلقعليها (tuples) . • اسم العلاقة وأسماء الاعمدة (name of attributes) تساعد علي تفسير القيم في صفوف العلاقة

4. مثال العلاقة تتحدث عن نوع الكائن Student وأن القيم في أي صف من صفوف العلاقة تتحدث عن (particularentity) (طالب خاص) .

5. المجال (Domain) • اي صفة في العلاقة لها مجال. • المجال هو مجموعة عناصر القيم (Atomic values) التي يمكن أن تأخذها الصفة التي تنتمي لهذا المجال. • يمكن أن تعتبر الـ Data types المعروفة (int,char,…) مجالات للصفات . • يمكن أيضا تعريف المجال ويكون له اسم ،نوع ،نسق(format) ويسمي هذا بالتعريف المنطقي للمجال (logical definition of domain). • مثال: المجال للصفة StdNo في العلاقة Student اسم المجال: Student Number ، النوع: char(5) ، النسق (format): dd-dd .

6. Relation Schema andRelationState( الرموز الاصطلاحية المستخدمة في النموذج العلائقي) • خارطة العلاقة R توضح كالأتيR(A1: dom(A1), A2: dom(A2),., An: dom(An)) اوR(A1, A2,…....., An) • اسم العلاقة :R (يختلف عن أسماء كل العلاقات في داخل قاعدة البيانات الواحدة) • صفات العلاقة: A1 ، A2،.....، An. • ولكل صفة Ai مجال dom(Ai) . • درجة العلاقة: تعني عدد صفات العلاقة. R )من الدرجة n) .

7. Cont. • حالة العلاقة: تعني مجموعة الصفوف الحالية المكونة للعلاقة (current set of tuples)) ويرمز لها بـr(R) ولها نفس اسم خارطة العلاقة وهي عبارة عن مجموعة من الـm-tuples(cardinality of R) . r(R)={t1,t2,….,tm} • حيث كل صف t يحتوي علي (n values). • t=<v1,………,vn> • vi هي عبارة عن عنصر في المجال (dom(Ai حيث n ≥ i ≥1 أو قيمة خالية )(null. • vi is the value corresponding to attribute Ai • t[Ai] is the ith value in the tuple t , which corresponds to the attribute Ai .

8. Cont. • r (R ) هي عبارة عن علاقة رياضية من الدرجة n للمجالات dom(A1)، dom(A2)،....، dom(An) • r(R)  dom (A1) X dom (A2) X ....X dom(An) • وهي تمثل مجموعة جزئية من الحاصل الكارتيزي لمجالات الصفات وهي المجموعات المختلفة التي يمكن تكوينها من عناصر المجالات.

9. مثال • خارطة العلاقة Student: • Student (Std-Name,Std-No,Tel-No,Address,Average,Age) • درجة العلاقة =6 • الصفة A للعلاقة R تكتب R.A(Dot Notation) ،مثلا Student.Std-Name، Student.Age . • في العلاقة Student إذا كان لدينا الصف • t=('Ali', '02-11', '554612', 'Omdurman','87', '22') • t[Std-Name] = <'Ali> • t[Std-No,Address,Age] = <'02-11', 'Omdurman', '22'> • حالة العلاقة تتغير باستمرار(عند تحديث البيانات)ولكن الخارطة نادرا ما تتغير(كما ذكر سابقا)

10. Definition Summary

11. Characteristics of Relations • ترتيب الصفوف في العلاقة:- العلاقة معرفة علي أساس أنها مجموعة من الصفوف، رياضيا عناصر المجموعة لا يشترط فيها الترتيب . وبالتالي فتعريف العلاقة لا يحتوي علي أي شرط للترتيب. أحيانا يكون الترتيب منطقياً. • ترتيب القيم في الصف:- علي حسب ترتيب الصفات في خارطة العلاقة، ترتيب الصفات أيضا غير محدد في العلاقة وقد يكون أيضا منطقيا.

12. Cont. • القيم في الصفوف:- • القيم في الصف يجب أن تكون صماء (Atomic) . • Each value in a tuple must be from the domain of the attribute for that column • If tuple t = <v1, v2, …, vn> is a tuple (row) in the relation state r of R(A1, A2, …, An) • Then each vi must be a value from dom(Ai) • بعض القيم في الصفوف قد تأخذ القيمة)Null). • Several meaning for null: Value unknown, value exists but is not available, or attribute does not apply to this tuple.

13. تفسير العلاقة • الصفوف في العلاقة إما توضح حقيقة لكائنات خاصة (Particular Entities) كما في العلاقة Student أو حقيقة لعلاقة بين الكائنات. • مثال: • خارطة العلاقة Majors(Std-No , Dep-No) : يوضح كل صف في هذه العلاقة علاقة كل طالب والقسم الذي يتبع له (علاقة بين كائنات مختلفة).

14. قيود النموذج العلائقي Relational model costraints • قيد المجال Domain Constraint • قيد المفتاح Key Constraint • قيد التكاملية للكائن Entity Integrity Constraint • قيد التكاملية المرجعية Referential Integrity Constraint • يعرف القيدان الأخيران بقيود التكاملية (Integrity Constraints) ويرمز لها بـ IC) )

15. قيود المجالdomain constraints • تقتضي أن أي قيمة للصفة A يجب أن تكون قيمة صماء في .dom(A) • Domain constraint specify that the value of each attribute A within each tuple MUST BE ATOMIC VALUE FROM DOM(A).

16. Key Constraint • Superkey of R: • Is a set of attributes SK of R with the following condition: • No two tuples in any valid relation state r(R) will have the same value for SK • That is, for any distinct tuples t1 and t2 in r(R), t1[SK]  t2[SK] • This condition must hold in any valid state r(R) • Key of R: • A "minimal" superkey • That is, a key is a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey (does not possess the superkey uniqueness property)

17. Key Constraint (continued) • Example: Consider the CAR relation schema: • CAR(State, Reg#, SerialNo, Make, Model, Year) • CAR has two keys: • Key1 = {State, Reg#} • Key2 = {SerialNo} • Both are also superkeys of CAR • {SerialNo, Make} is a superkey but not a key. • In general: • Any key is a superkey (but not vice versa) • Any set of attributes that includes a key is a superkey • A minimal superkey is also a key

18. Key Constraint (continued) • If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. • The primary key attributes are underlined. • Example: Consider the CAR relation schema: • CAR(State, Reg#, SerialNo, Make, Model, Year) • We chose SerialNo as the primary key • The primary key value is used to uniquely identify each tuple in a relation • Provides the tuple identity • Also used to reference the tuple from another tuple • General rule: Choose as primary key the smallest of the candidate keys (in terms of size) • Not always applicable – choice is sometimes subjective

19. CAR table with two candidate keys – LicenseNumber chosen as Primary Key

20. قيد المفتاح • تعرف العلاقة علي أنها مجموعة من الصفوف، وأي عنصر في المجموعة يجب أن لا يتكرر وبالتالي فلا يسمح بتكرار أي صف في العلاقة ولضمان ذلك يجب أن يكون هنالك مفتاح أساسي (Primary Key) لكل علاقة وهو يتكون من صفة أو أكثر من صفات العلاقة ويستخدم لتمييز أي صف عن الأخر وقيمته لاتتكرر فى صفوف العلاقة (خاصيةالتفرد Uniqueness) • اذاً المفتاح الاساسى PK للعلاقة R يحقق الاتى: t1(PK)<>t2(PK)<>…………..tm(PK)

21. مثال :في العلاقة Student يمكن أن تكون الصفة Std-No هي المفتاح الأساسي للعلاقة . • قد يكون للعلاقة أكثر من صفة يمكن أن تمثل المفتاح الأساسي( كل صفة علي حدة تمتاز بخاصية التمييز) وتسمي هذه الصفات بـ Candidate Keys (المفاتيح المرشحة). ولكن يتم اختيار صفة واحدة من بين هذه الصفات لتمثل الـ PK • . يوضح الـPK في خارطة العلاقة بوضع خط تحت الصفة التي تمثل صفة المفتاح الأساسي . • R (A , B, C) Simple PK, R (A , B, C) Composite PK

22. أحيانا نحدد ايضا ما إذا كانت الصفة يمكن أن تأخذ القيمة Null فمثلا نحدد أن الصفة Std-Name دائما Not Null وهذا يعتبر قيد بالنسبة للصفة .

23. خارطة قاعدة البيانات العلائقيةRelational Database Schema:- قاعدة البيانات العلائقية عبارة عن مجموعة من العلاقات وبالتالي فخارطة قاعدة ا S تمثل مجموعة خرط العلاقات المكونة لقاعدة البيانات S ={ R1 , R2, ….., Rm} (Integrity Constraints IC) ومجموعة من قيود التكاملية • A set S of relation schemas that belong to the same database. • S is the name of the whole database schema • S = {R1, R2, ..., Rn} • R1, R2, …, Rn are the names of the individual relation schemas within the database S • حالة قاعدة البيانات العلائقية Relational Database State:- هي مجموعة حالات العلاقات المكونة لقاعدة البيانات{r1, r2, ………,rm} وهي حالات صحيحة (Valid States) أي مستوفية لكل القيود( قيود قاعدة البيانات IC).

24. COMPANY Database Schema

25. ملاحظات • الصفة DNumber في العلاقة Department وفي العلاقة Dep-Location يمثلان نفس المفهوم. • لا يشترط في الصفات التي تمثل نفس المفهوم أن يكون لها نفس الاسم في العلاقات المختلفة. • الصفة DNo في العلاقة Employee والصفة Dnum في العلاقة Project يشيران الي نفس المفهوم وهو رقم القسم. • نستطيع أن نتعرف علي أن الصفتان لهما نفس المفهوم من فهم معني العلاقات Semantic meaning of the relations • نستخدم الـ DDL لانشاء خارطة قاعدة البيانات (وهي جزء من أوامر الـSQL) بالاضافة الي تحديد كل القيود الخاصة بقاعدة البيانات.

26. قيد تكاملية الكائن(Entity Integrity Constraints) • تقتضى ان قيمة المفتاح الاساسى لإى صف فى العلاقة لا يمكن ان تكون NULL وذلك لأن هذه الصفة تستخدم للتمييز بين الصفوف. • The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R). • This is because primary key values are used to identify the individual tuples. • t[PK]  null for any tuple t in r(R) • If PK has several attributes, null is not allowed in any of these attributes • Note: Other attributes of R may be constrained to disallow null values, even though they are not members of the primary key.

27. قيود التكاملية المرجعيةReferential Integrity Constraints • قيود المفتاح وقيود التكاملية للكائن تحدد فى علاقة واحدة فقط (individual relation) ولكن قيود التكاملية المرجعية تكون مشتركة بين علاقتين specified between two relations)) وهى مفيدة للحفاظ على صحة البيانات(Data Consistency) بين صفوف العلاقتين.

28. Cont. • التكاملية المرجعية بين العلاقتين R1وR2 تقتضى الأتي : • وجود عمود او صفة( اوصفات) فى علاقة R1يمثل مفتاح خارجى ((foreign key ويرمز له ب(FK) وتسمى R1العلاقة الراجعة الى المرجع( referencing relation) بحيث يمثل هذا المفتاح الخارجى نفس مفهوم ويكون له نفس مجال المفتاح الاساسى(PK) فى العلاقة R2،وتسمى R2 بالعلاقه المرجع((referenced relation. • قيمة المفتاح الخارجى فى R1 إما ان تكون NULL او تكون قيمة من قيم المفتاح الاساسى فى العلاقة المرجع R2. • اذا لم يتحقق الشرطان اعلاه فاذاً لايوجد FK فى R1 وبالتالي لاتتحقق شروط التكاملية المرجعية بين R1وR2.

29. Cont. • Another definition:- • (When a foreign key value is not null ( in referencing relation ) then it must be exist as a value of PK in another relation ( referenced relation) otherwise is not a foreign key).

30. مثال • Student (Std-No, Std-name, Project-No) • Project(Pr-No, Pr-Name) هنا يمكن القول بأن شروط التكاملية المرجعيه مستوفاة لآن Project – No فى Student تمثل الـ FK لمادا ؟

31. Cont. • مادا يحدث للمفتاح الخارجى FK لو عدلنا او حذفنا المفتاح الاساسى فى العلاقة المرجع : هنالك ثلاثة خيارات : • منع الحذف او التعديل لقيم المفتاح الاساسى عند وجود مفتاح خارجى FK يحمل نفس القيمة (Restrict ) . • جعل قيمة المفتاح الخارجى NULL عند حذف المفتاح الاساسى .(Nullify) • متابعة حذف او تعديل قيم المفتاح الخارجى عند حذف او تعديل المفتاح الاساسى (Cascade ) .

32. Cont. • من المثال السابق لو حذفنا الصف الاول فى العلاقه Project الخيارات :- • منع الحذف للصف الاول لان قيمة المفتاح الاساسى فى هذا الصف تمثل قيمة لمفتاح خارجى فى العلاقة student فى الصف الاول • جعل قيمة المفتاح الخارجى فى الصف الاول فى العلاقه student تساوى Null . • او حذف الصف الاول للعلاقه Student • كل قيود التكاملية توضح فى خارطة قاعدة البيانات القاعدية باستخدام اوامر الـ DDL ( تعرف كجزء من تعريف قاعدة البيانات ).

33. cont. • يمكن توضيح قيد تكاملية المرجع على خارطة قاعدة البيانات العلائقية برسم خطوط تربط بين العلاقة المرجع والعلاقة الراجعة للمرجع بحيث رأس السهم في العلاقة المرجع. • انظر الشكل ادناه.

34. Referential Integrity Constraints for COMPANY database

35. Other Types of Constraints • Semantic Integrity Constraints: • based on application semantics and cannot be expressed by the model. • Example: “the max. no. of hours per employee for all projects he or she works on is 56 hrs per week” • A constraint specification language may have to be used to express these . • SQL-99 allows triggers and ASSERTIONS to express for some of these.

36. Populated database state • Each relation will have many tuples in its current relation state • The relational database state is a union of all the individual relation states • Whenever the database is changed, a new state arises • Basic operations for changing the database: • INSERT a new tuple in a relation • DELETE an existing tuple from a relation • MODIFY an attribute of an existing tuple • Next slide shows an example state for the COMPANY database

37. Populated database state for COMPANY

38. Update Operations on Relations • INSERT a tuple. • DELETE a tuple. • MODIFY a tuple. • Integrity constraints should not be violated by the update operations. • Several update operations may have to be grouped together. • Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints.

39. Update Operations on Relations • In case of integrity violation, several actions can be taken: • Cancel the operation that causes the violation (RESTRICT or REJECT option) • Perform the operation but inform the user of the violation • Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) • Execute a user-specified error-correction routine

40. Possible violations for each operation • INSERT may violate any of the constraints: • Domain constraint: • if one of the attribute values provided for the new tuple is not of the specified attribute domain • Key constraint: • if the value of a key attribute in the new tuple already exists in another tuple in the relation • Referential integrity: • if a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation • Entity integrity: • if the primary key value is null in the new tuple

41. Possible violations for each operation • DELETE may violate only referential integrity: • If the primary key value of the tuple being deleted is referenced from other tuples in the database • Can be remedied by several actions: RESTRICT, CASCADE, SET NULL (see Chapter 8 for more details) • RESTRICT option: reject the deletion • CASCADE option: propagate the new primary key value into the foreign keys of the referencing tuples • SET NULL option: set the foreign keys of the referencing tuples to NULL • One of the above options must be specified during database design for each foreign key constraint

42. Possible violations for each operation • UPDATE may violate domain constraint and NOT NULL constraint on an attribute being modified • Any of the other constraints may also be violated, depending on the attribute being updated: • Updating the primary key (PK): • Similar to a DELETE followed by an INSERT • Need to specify similar options to DELETE • Updating a foreign key (FK): • May violate referential integrity • Updating an ordinary attribute (neither PK nor FK): • Can only violate domain constraints

43. Summary • Presented Relational Model Concepts • Definitions • Characteristics of relations • Discussed Relational Model Constraints and Relational Database Schemas • Domain constraints’ • Key constraints • Entity integrity • Referential integrity • Described the Relational Update Operations and Dealing with Constraint Violations

44. Quiz 3 Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT(SSN, Name, Major, Bdate) COURSE(Course#, Cname, Dept) ENROLL(SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT(Book_ISBN, Book_Title, Publisher, Author) Draw a relational schema diagram specifying the foreign keys for this schema.