Database Administration, Integrity and Performance

1. Database Administration, Integrity and Performance

2. Objectives • Define the roles of database administrator • Describe the term integrity • Define the term security • Define the basic performance measures

3. The Database Administrator • Data Administrator(DA) makes the strategies and policies decisions regarding the data of the enterprise. • Database Administrator(DBA) implements those decisions technically.

4. Functions of DBA • Creating the conceptual schema (logical or conceptual database design) • Creating the internal schema (physical database design)

5. Functions of DBA • Defining security and integrity constraints • Defining backup and recovery policies • Monitoring performance and responding to changing environments.

6. SECURITY • Protecting the data against unauthorised disclosure, alteration or destruction.

7. Security is enforced by • DBMS’s security subsystem • Checking access requests to security constraints stored in system catalog • Giving certain privileges in a relation to certain users

8. What are views for? • Automatic Security for hidden data • Short hand capability • Same data to be seen by different users in a different way at the same time • May provide logical data independence • immunity of users and user programs to changes in the logical structure of a database • Growth • new attributes may be added • new relations may be added

9. SQL FACILITIES FOR VIEWS CREATE VIEW < view name> AS <table expression> [ WITH [<qualifier>] CHECK OPTION ] • With check option means that INSERT and UPDATE on the view will be rejected if they violate any integrity constraint implied by the view defining expression • Qualifier is CASCADED { Default } or LOCAL

10. Examples of views CREATE VIEW Bestsupplier AS SELECT S.S#, S.STATUS, S.CITY FROM SUPPLIER WHERE SUPPLIER.STATUS > 15 WITH CHECK OPTION ;

11. Examples of views CREATE VIEW GREENPART AS SELECT PART.P#, PART.NAME, PART.WEIGHT AS WT, PART.CITY FROM PART WHERE PART.Color = ‘Green’ WITH CHECK OPTION;

12. Examples of views CREATE VIEW HEAVY_GREENPART AS SELECT GP.P#, GP.NAME, GP.WT, GP.CITY FROM GREENPART AS GP WHERE GP.WT > 12.0 WITH CHECK OPTION;

13. Views and Security GRANT SELECT, UPDATE (NAME,WT), DELETE ON GREENPART TO user1, user2; • Privileges can be USAGE, SELECT, INSERT, UPDATE, DELETE and REFERENCES • Can be provided WITH GRANT OPTION

14. REVOKE REVOKE DELETE ON GREENPART FROM user1 RESTRICT • Revoke can be done for GRANT OPTION FOR also • RESTRICT is needed if a privilege is granted further

15. What is Integrity? • Accuracy or correctness of data in a database • Mostly RDBMS provide two kind of Integrity support: • Declarative Integrity support • Procedural Integrity support

16. Declarative Support • In the form of constraint definitions • If a new constraint is declared then the system ensures that current state of database satisfies it, otherwise, the new constraint is rejected • If a DBMS provides declarative support, then about 90% of typical database definition would consist of constraints.

17. Declarative Support Contd.. A system providing such support relieve application programmers of the burden, thus, increases productivity

18. Golden Rule • No update operation must ever be allowed to leave any relation in a state that violates its own predicate. No update transaction must ever be allowed to leave database in a state that violates its own predicate.

19. State Vs Transition Constraints • Concerned with the correct state of database • Constraints on legal transitions • Not married to married • No student class should ever decease

20. FOREIGN KEYS . • Let R2 be a relation, then a foreign key in R2 is a set of attributes of R2, say FK, such that : • There exists a relation R1 ( R1 & R2 not necessarily distinct) with a candidate key CK; and • For all times, each value of FK in the current value of R2 is identical to the value of CK in some tuple in the current value of R1.

21. Foreign Key contd ... . • Definition requires that every value of given foreign key appears as a value of matching candidate key - Converse is not a requirement • A foreign key is simple or composite • Each attribute of a given foreign key must have a same name and type as the corresponding component of matching candidate key

22. Referential Integrity • Foreign key is a Reference • Matching candidate key tuple is Referenced • Referential constraint : A value of foreign key must match the referenced candidate key • Referencing Relation : Have foreign key • Referenced Relation : Have candidate key

23. Referential Diagrams • A given relation can be both referenced and referencing at the same line R3 R2 R1 • R1 & R2 are not necessarily distinct Example : (Employee, Mgr.) Self-referencing relation • Foreign to candidate key match is the glue that holds database together .

24. SQL Facilities • A candidate key definition • A foreign key definition • A check constraint definition We may assign a name to constraint

25. Candidate Key UNIQUE (<column name comma list>) OR PRIMARY KEY (<column name comma list>) • At most one Primary key definition • Any number of UNIQUE specifications • NOT NULL is by default

26. Foreign Key FOREIGN KEY (< col name comma list>) References <base table name> [(< column name comma list>)] [ ON DELETE < referential action> ON UPDATE<Referential action>] • No action{default} or CASCADE or SET DEFAULT or SET NULL • Second Comma list, if foreign key references a candidate key that is not primary key.The foreign to candidate key matching is done on column position ( left to right)

27. Example of constraints CREATE TABLE SupPart ( S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOTNULL, PRIMARY KEY( S# , P# ) , FOREIGN KEY ( S#) REFERENCES SUP ON DELETE CASCADE, ON UPDATE CASCADE, FOREIGN KEY ( P#) REFERENCES PART ON DELETE CASCADE ON UPDATE CASCADE, CHECK ( QTY > 0 and QTY < 5000) );

28. TRIGGERED PROCEDURES • Procedures invoked automatically on occurrence of a specified exception or a specified interval of time • CASCADE referential action is a simple example of a triggered procedure-declaratively specified

29. Assertions • General constraints Cerate ASSERTION < constraints name > check ( < conditional expression >); DROP ASSERTIONS <Constraint name>; • Assertions are always satisfied by database. • On creation validity of an assertion is checked • Any change in database may result in checking by an creation =>High overheads

30. Example • The sum of all loans of a branch should be less than total account balances Create assertion sum-constraint check ( not exists (select * from branch where (select sum (amount) from loan where loan.branch-name = branch.branch-name ) >=(select sum (amount) from account where loan .branch-name = branch.branch-name)))

31. Deferred Checking • SQL constraints can be: • DEFERABLE (Initially Immediate OR initially deferrable ) • NOT DEFERABLE • SET CONSTRAINT <constraint name> <option>; statement switches on or off the deferrable constraint • Option is IMMEDIATE or DEFERRED

32. Performance Tuning involves • Tuning of hardware resources • Reducing I/O operations

33. Reducing Disk Accesses • Indexing is one major technique used to reduce disk accesses

34. Ordered indices • Based on based on sorted ordering of values • Index involve • Access time • Insertion time • Deletion time • Space overheads

35. Ordered indices • Index Structure is associated with a 'Search Key' • Primary index- on a sequentially sorted file. (index on primary key- not correct) • Indices whose search key specifies an order different from the sequential order of the file are called secondary or non clustering indices.

36. Dense Index • An index record/ entry appears for every search key 'value' in the file & a pointer to first data record with that search key value

37. Sparse Index • Entry only for some of the values and a pointer to first value of that type. • Take less space • A sparse index with one index entry per block may be a good choice.

38. Multilevel Indices • Are useful when size of index is very large

39. Secondary Indices • It contains pointers to all records • It should not be sparse • Improve performance • Overheads on modification of databases • Tradeoff : Frequency of Queries versus Modifications

40. B Tree • Is one of the major method of implementing index • B tree adds performance overheads for insertion and deletion and adds apace overheads. • This performance overhead is acceptable even for files with high frequency of modification- no cost of reorganization