Database Integrity

1. Database Integrity پایگاه داده پیشرفته قمرناز تدین

2. جامعیت پایگاه داده (Database integrity ) • منظور از جامعیت داده، پردازش صحیح پایگاه داده است مانند بکارگیری قوانین کاری (business rules) درزمان انجام عملیات پایگاه داده • به این معنی است که داده ذخیره شده در پایگاه داده صحیح هستند.

3. Database Integrity • روش های مختلفی برای تضمین جامعیت داده وجود دارد: • جامعیت دامنه Domain integrity • محدودیت جامعیت موجودیت Entity integrity constraint • جامعیت ارجاعی Referential integrity • قوانین فعالیت Business rules • سازگاری پایگاه داده Database consistency

4. Database Integrity • Domain integrity • دامنه ای از مقادیر ممکن باید برای هر خصیصه مشخص شود. (مثلا نوع صحیح، کرکتر، تاریخ/زمان) • ابتدایی ترین شکل محدودیت جامعیت است. • زمانی که داده جدیدی وارد پایگاه داده می شود، به راحتی توسط سیستم بررسی می شود. • Entity integrity constraint • هر سطر رابطه باید منحصر به فرد باشد. • کلید اصلی منحصر به فرد بودن سطرها را نشان میدهد و نمی تواند NULL باشد.

5. Database Integrity • Referential integrity • اگر یک جدول شامل کلید خارجی باشد، آنگاه سطر مربوط به آن کلید باید در جدول ارجاع شده وجود داشته باشد. • مثلا گروه مربوط به هر درس باید تعریف شده باشد. مقدار dept_nameدر رکورد درس باید در خصیصه dept_nameرکوردی از رابطه گروه وجود داشته باشد. • تغییرات در پایگاه داده ممکن است جامعیت ارجاعی را نقض کنند. • اگر محدودیت جامعیت ارجاعی نقض شود، این عملیات رد می شوند.

6. Database Integrity • Business rules • رابطه میان موجودیتها، قوانین کاری را تعریف میکنند. • Database consistency • پیگاه داده باید قبل و بعد از هر تراکنش سازگار باشد. • همه محدودیتهای جامعیت پایگاه داده باید برقرار باشند.

7. Entity Integrity • Examples: • An instructor name cannot be null. • No two instructors can have the same instructor ID. • Every department name in the course relation must have a matching department name in the department relation. • The budget of a department must be greater than $0.00.

8. In SQL create table r (A1 D1, A2 D2, . . . , An Dn, integrity-constraint1 , . . . , integrity-constraintk ); The allowed integrity constraints include • not null • unique • check(<predicate>)

9. Entity Integrity - Primary Keys • یادآوری: کلید اصلی هرسطر باید منحصر به فرد و غیر تهی باشد. • Example: The primary key of the Viewing table is composed of two attributes (composite key): CREATE TABLE Viewing ( ClientNo VARCHAR(5) NOT NULL, PropertyNo VARCHAR(5) NOT NULL, PRIMARY KEY (ClientNo, PropertyNo)); • SQL هر عملیاتی که منحصر به فرد بودن کلید اصلی را نقض کند را رد میکند. • Can use UNIQUE(Colname) to enforce uniqueness of alternate keys

10. create table section (course id varchar (8), sec id varchar (8), semester varchar (6), year numeric (4,0), building varchar (15), room number varchar (7), slot id varchar (4), primary key (course id, sec id, semester, year), check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)));

11. Referential Integrity - Foreign Keys • یاداوری: • کلید خارجی، جدول فرزند را به والد پیوند می دهد. • اگر کلید خارجی تهی نباشد باید با سطری از جدول والد منطبق باشد. • SQL: CREATE TABLE PropertyForRent (... StaffNo VARCHAR(5) NOT NULL, FOREIGN KEY (StaffNo) REFERENCES Staff); • درصورتی که عملیاتی باعث تناقض در محدودیت ارجاعی شود، اجرا نخواهد شد.

12. Referential Integrity - Foreign Keys create table department (dept name varchar (20), building varchar (15), budget numeric (12,2) check (budget > 0), primary key (dept name)) create table course (course id varchar (8), title varchar (50), dept name varchar (20), credits numeric (2,0) check (credits > 0), primary key (course id), foreign key (dept name) references department)

13. create table instructor (ID varchar (5), name varchar (20), not null dept name varchar (20), salary numeric (8,2), check (salary > 29000), primary key (ID), foreign key (dept name) references department)

14. create table classroom (building varchar (15), room number varchar (7), capacity numeric (4,0), primary key (building, room number)) create table section (course id varchar (8), sec id varchar (8), semester varchar (6), check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’), year numeric (4,0), check (year > 1759 and year < 2100) building varchar (15), room number varchar (7), time slot id varchar (4), primary key (course id, sec id, semester, year), foreign key (course id) references course, foreign key (building, room number) references classroom)

15. Referential Integrityand Referential Actions • سوال: اگر فیلد کلید در جدول والد تغییر پیدا کند، در جدول فرزند چه اتفاقی می افتد؟ SQL چهار فرایند را امکان پذیر می کند. FOREIGN KEY (Key) REFERENCES Table [ON DELETE | UPDATE Action] • CASCADE - apply changes to child rows • SET NULL - set child keys to NULL • SET DEFAULT - set child keys to DEFAULT value • NO ACTION - reject the operation (default)

16. create table course ( . . . foreign key (dept name) references department on delete cascade on update cascade, . . . ); اگر حذف یک رکورد از department باعث نقض محدودیت جامعیت ارجاعی شود، سیستم عملیات حذف را رد نمیکند بلکه این عمل به صورت آبشاری در رابطه course هم اجرا می شود و رکوردی را که به گروه حذف شده ارجاع دارد حذف میکند.

17. Enterprise Constraints(Business Rules) • گاهی اوقات قوانین تجاری شامل محدودیتهایی هستند که بیش از یک جدول را درگیر میکنند. دراینصورت بهتر است محدودیتهای سازمانی فقط یک بار تعریف شوند. • Example: A member of staff may manage no more than 100 properties: CREATE ASSERTION StaffNotOverLoaded CHECK (NOT EXISTS (SELECT StaffNo FROM PropertyForRent GROUP BY StaffNo HAVING COUNT (*) > 100)); CREATE TABLE PropertyForRent ( ... CONSTRAINT StaffNotOverLoaded);

18. • For each tuple in the student relation, the value of the attribute tot_cred must equal the sum of credits of courses that the student has completed successfully. create assertion credits earned constraint check (not exists (select ID from student where tot_cred <> (select sum(credits) from takes natural join course where student.ID= takes.ID and grade is not null and grade<> ’F’ )

19. Triggers • معمولا قوانین کاری را نمیتوان با محدودیت ها تعرف کرد. • Triggerها برای تعیین عملیاتی استفاده می شوند که باید به طور خودکار درصورت وقایعی مثل درج، حذف یا بروزرسانی رکوردها در رابطه خاصی باید اجرا شوند. • Example: The branch manager is notified by e-mail if a client views more than 10 properties. • DBMS های مختلف معمولا مکانیسم trigger را دارند. • Trigger ها ممکن است شامل کد روالی هم باشند(if/then/else, function calls)

20. CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} {INSERT|DELETE|UPDATE [OF column_list]}… [[REFERENCING correlation_name] FOR EACH ROW [WHEN (condition)]] BEGIN PL/SQL code END

21. create trigger timeslot check1 after insert on section referencing new row as nrow for each row when (nrow.time_slot_id not in ( select time_slot_id from time_slot)) /* time slot id not present in time slot */ begin rollback end; trigger is initiated after any insert on the relation section and it ensures that the time_slot_id value being inserted is valid. The referencing new row as clause creates a variable nrow (called a transition variable) that stores the value of an inserted row after the insertion.

22. create trigger timeslot _check2 after delete on timeslot referencing old row as orow for each row when (orow.time_slot _id not in ( select time_slot_id from time_slot) /* last tuple for time slot id deleted from time slot */ and orow.time_slot_id in ( select time_slot_id from section)) /* and time slot id still referenced from section*/ begin rollback end;

23. create trigger credits _earned after update of takes on (grade) referencing new row as nrow referencing old row as orow for each row when nrow.grade <> ’F’ and nrow.grade is not null and (orow.grade = ’F’ or orow.grade is null) begin atomic update student set tot_cred= tot_cred+ (select credits from course where course.course _id= nrow.course _id) where student.id = nrow.id; end;

24. Events and Triggers • An event fires (initiates) a trigger (program, code): • Common events: • SQL-based on rows: INSERT, DELETE, UPDATE • SQL-based on tables: ALTER, CREATE, DROP • LOGOFF, LOGON – user’s action • SERVERERROR, SHUTDOWN, STARTUP – server’s action • SQL events support triggers at two points in time -- BEFORE and AFTER • Example: BEFORE UPDATE on Salary column, run a trigger that checks if a salary ceiling is respected.

25. Before Update on a table (all rows) After Update on the table Update point time Before Update of a row After Update of the row Table and Row Triggers

26. Trigger Example • The trigger logs employee ID, date, user entering data, • old salary, new salary into a table SalaryChanges, after each update • on the salary column in the Employee table. Useful for auditing. CREATE TRIGGER LogSalaryChanges AFTER UPDATE OF Salary ON Employee REFERENCING OLD ROW as oldrow NEW ROW AS newrow FOR EACH ROW INSERT INTO tblSalaryChanges (EmpID, ChangeDate, User, OldValue, NewValue) VALUES (newrow.EmployeeID, CURRENT_TIMESTAMP, CURRENT_USER, oldrow.Salary, newrow.Salary);