logging in or signing up CA II,U 4 ,Integrity Constraints and Rel mstufail Download Post to : URL : Related Presentations : Share Add to Flag Embed Email Send to Blogs and Networks Add to Channel Uploaded from authorPOINT lite Insert YouTube videos in PowerPont slides with aS Desktop Copy embed code: (To copy code, click on the text box) Embed: URL: Thumbnail: WordPress Embed Customize Embed The presentation is successfully added In Your Favorites. Views: 259 Category: Education License: All Rights Reserved Like it (0) Dislike it (0) Added: February 22, 2010 This Presentation is Public Favorites: 0 Presentation Description No description available. Comments Posting comment... Premium member Presentation Transcript UNIT IV Integrity Constraints and Relational Database Design Computer Application II M.S.Tufail Department of Mechanical Engg. Y.C.C.E, Nagpur : UNIT IV Integrity Constraints and Relational Database Design Computer Application II M.S.Tufail Department of Mechanical Engg. Y.C.C.E, Nagpur 1 UNIT-IV : 2 UNIT-IV Integrity Constraints and Relational Database Design: Domain Constraints Referential integrity Functional Dependencies Assertions Triggers Pitfalls in relational Database Normalisation Using Functional Dependencies Using Multi-Valued Dependencies Domain Key Normal Form. Integrity Constraints : 3 Integrity Constraints Integrity constraints provide a way of ensuring that changes made to the database by authorized users do not result in a loss of data consistency Integrity constraint with E-R models: Key declarations Stipulation that certain attributes form a candidate key for the entity set Form of a relationship Mapping cardinalities 1:1, 1:M & M:M An integrity constraint can be any attribute predicate applied to the database They may be costly to evaluate Domain Constraints : 4 Domain Constraints A domain of possible values should be associated with every attribute These domain constraints are the most basic form of integrity constraint Domain types: Attributes may have the same domain Ex. customer_name, employee_name It is not as clear whether branch_name & customer_name domain ought to be distinct At the conceptual level, they are both character strings Cont… Domain Constraints : 5 Domain Constraints The check clause in SQL-92 permits domains to be restricted in powerful ways: The check clause permits schema designer to specify a predicate that must be satisfied by any value assigned to a variable whose type is the domain Example Create domain hourly-wage numeric(5,2) Constraint wage-value-test check(value>= 4.00) Create domain account-number char(10) Constraint account-number-null-test check(value not null)) Referential Integrity : 6 Referential Integrity Often we wish to ensure that a value appearing in a relation for a given set of attributes also appears for another set of attributes in another relation. This is called referential integrity. Cont… Referential Integrity: Basic Concepts : 7 Referential Integrity: Basic Concepts Dangling tuples Consider a pair of relations r(R) and s(S), and the natural join r s There may be a tuple tr in r that does not join with any tuple in s i.e. there is no tuple ts in s such that tr[RS]= ts[RS] We call this a dangling tuple. Cont… Referential Integrity: Basic Concepts : 8 Referential Integrity: Basic Concepts Suppose there is a tuple t1 in the account relation with the value t1[branch_name] = “Sadar”, but no matching tuple in the branch relation for the Sadar branch This is undesirable, as t1 should refer to a branch that exists. Now suppose there is a tuple t2 in the branch relation with t2[branch_name] = “CA”, but no matching tuple in the account relation for CA branch This means that a branch exists for which no accounts exist. This is possible, for ex., when a branch being opened. We want to allow this situation. Cont… Referential Integrity: Basic Concepts : 9 Referential Integrity: Basic Concepts Note the distinction between these two situations: branch_name is the primary key of branch, while it is not for account In account, branch_name is a foreign key, being the primary key of another relation Cont… Referential Integrity in E-R Model : 10 Referential Integrity in E-R Model Every relation arising from a relationship set has referential integrity constraints Refer figure Fig shows an n-ary relationship set R relating entity sets E1, E2,……, En Let Ki denote the primary key of Ei The attributes of the relation schema for relationship set R include K1 K2 ... Kn Each Ki in the schema for R is a foreign key that leads to a referential integrity constraint. Cont… Referential Integrity in E-R Model : 11 Referential Integrity in E-R Model Relational schemas for weak entity sets must include the primary key of the strong entity set on which they are existent dependent. This is a foreign key, that leads to another integrity constraint. Referential Integrity in SQL : 12 Referential Integrity in SQL An addition to the original standard, allows specification of primary and candidate keys and foreign keys as part of the create table command primary key clause includes a list of attributes forming a candidate key unique key clause includes a list of attributes forming a candidate key foreign key clause includes a list of attributes forming the foreign key, and the name of the relation referenced by the foreign key Cont… Referential Integrity in SQL : 13 Referential Integrity in SQL Example Cont… not null, not null, not null, not null, not null, create table customer (customer-name char(20), customer-street char(30), customer-city char(30),primary key (customer-name)) create table branch (branch-name char(15), branch-city char(30), assets integer, primary key (branch-name)) Referential Integrity in SQL : 14 Referential Integrity in SQL Example Cont… not null, not null, not null, not null, not null, create table account(account-number char(10) not null,branch-name char(15),balance integer,primary key (account-number), foreign key (branch-name) references branch) create table depositor(customer-name char(20) not null,account-number char(10) not null, primary key (customer-name, account-number),foreign key (account-number) references account,foreign key (customer-name) references customer) Referential Integrity in SQL : 15 Notes on Foreign keys: A short form to declare a single column is a foreign key branch_name char(15) references branch When a referential integrity constraint is violated, the normal procedure is to reject the action. But a foreign key clause in SQL-92 can specify steps to be taken to change the tuples in the referenced relation to restore the constraint Referential Integrity in SQL Cont… Referential Integrity in SQL : 16 Example create table account ( . . .foreign key(branch-name) references branch on delete cascade on insert cascade. . . ) If a delete of a tuple in branch results in the preceding referential integrity constraints being violated, the delete is not rejected, but the delete “cascade” to the account relation, deleting the tuple that refers to the branch that was deleted Update will be cascaded to the new value of the branch! Referential Integrity in SQL Cont… Referential Integrity in SQL : 17 SQL-92 also allows the foreign key clause to specify actions other than cascade, such as setting the referencing field to null, or to a default value, if the constraint is violated on delete set null on delete set default If there is a chain of foreign key dependencies across multiple relations, a deletion or update at one end of the chain can propagate across the entire chain If a cascading update or delete causes a constraint violation that cannot be handled by a further cascading operation, the system aborts the transaction and all the changes caused by the transaction and its cascading actions are undone Referential Integrity in SQL Assertions : 18 Assertions An assertion is a predicate expressing a condition we wish the database to always satisfy Domain constraints, functional dependency and referential integrity are special forms of assertion Where a constraint cannot be expressed in these forms, we use an assertion, e.g.: Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch Ensuring every loan customer keeps a minimum of Rs. 1000 in an account Cont… Assertions : 19 Assertions An assertion in SQL-92 takes the form create assertion <assertion name> check (predicate> Ex.: Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch 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 (account.branch_name=branch.branch_name))) Cont… Assertions : 20 Assertions When an assertion is created, the system tests it for validity If the assertion is valid, any further modification to the database is allowed only if it does not cause that assertion to be violated This testing may result in significant overhead if the assertions are complex. Because of this, the assert should be used with great care. Triggers : 21 Triggers A trigger is a statement that is automatically executed by the system as a side effect of a modification to the database We need to specify the condition under which the trigger is executed the actions to be taken by the trigger Suppose that an overdraft is intended to result in the account balance being set to zero, and a loan being created for the overdraft amount The trigger actions for tuple t with negative balance are as follows: Cont… Triggers : 22 Triggers Insert a new tuple s in the borrower relation with s[branch_name] = t[branch_name] s[loan_number] = t[account_number] s[amount] = - t[balance] s[customer_name] = t[customer_name] We need to negate balance to get amount, as balance is negative Set t[balance] to 0 Not a good example! What would happen if the customer already had a loan? Cont… Triggers : 23 Triggers define trigger overdraft on update of account T (if new T.balance < 0 then (insert into loan values (T.branch_name, T.account_number, - new T.balance) insert into borrower (select customer_name, account_number from depositor where T.account_number=depositor.account_number) update account S set S.balance = 0 where S.account_number = T.account_number)) Cont… Triggers : 24 Triggers We sometimes require external world actions to be triggered on a database update E.g. re-ordering an item whose quantity in a warehouse has become small, or turning on an alarm light, Triggers cannot be used to directly implement external-world actions, BUT Triggers can be used to record actions-to-be-taken in a separate table Have an external process that repeatedly scans the table, carries out external-world actions and deletes action from table Cont… Triggers : 25 Triggers E.g. Suppose a warehouse has the following tables inventory(item, level) How much of each item is in the warehouse minlevel(item, level) The minimum desired level of each item reorder(item, amount) The quantity to re-order at a time orders(item, amount) Orders to be placed (read by external process) Cont… Triggers: When not to use : 26 Triggers: When not to use Triggers were used earlier for tasks such as maintaining summary data (e.g. total salary of each department) Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica There are better ways of doing these now: Databases today provide built in materialized view facilities to maintain summary data Databases provide built-in support for replication Encapsulation facilities can be used instead of triggers in many cases Define methods to update fields Carry out actions as part of the update methods instead of through a trigger Authorization : 27 Authorization Forms of authorization on parts of the database: Read authorization Allows reading, but not modification of data Insert authorization Allows insertion of new data, but not modification of existing data Update authorization Allows modification, but not deletion of data Delete authorization Allows deletion of data Cont… Authorization : 28 Authorization Forms of authorization to modify the database schema: Index authorization Allows creation and deletion of indices Resources authorization Allows creation of new relations Alteration authorization Allows addition or deletion of attributes in a relation Drop authorization Allows deletion of relations Authorization and Views : 29 Authorization and Views Users can be given authorization on views, without being given any authorization on the relations used in the view definition Ability of views to hide data serves both to simplify usage of the system and to enhance security by allowing users access only to data they need for their job A combination or relational-level security and view-level security can be used to limit a user’s access to precisely the data that user needs. View Example : 30 View Example Suppose a bank clerk needs to know the names of the customers of each branch, but is not authorized to see specific loan information. Approach: Deny direct access to the loan relation, but grant access to the view cust-loan, which consists only of the names of customers and the branches at which they have a loan. The cust-loan view is defined in SQL as follows: create view cust-loan as select branch_name, customer_name from borrower, loan where borrower.loan-number = loan.loan_number View Example : 31 View Example The clerk is authorized to see the result of the query: select * from cust-loan When the query processor translates the result into a query on the actual relations in the database, we obtain a query on borrower and loan Authorization must be checked on the clerk’s query before query processing replaces a view by the definition of the view Authorization on Views : 32 Authorization on Views Creation of view does not require resources authorization since no real relation is being created The creator of a view gets only those privileges that provide no additional authorization beyond that he already had. E.g. if creator of view cust-loan had only read authorization on borrower and loan, he gets only read authorization on cust-loan Granting of Privileges : 33 Granting of Privileges The passage of authorization from one user to another may be represented by an authorization graph. The nodes of this graph are the users. The root of the graph is the database administrator. Consider graph for update authorization on loan. An edge Ui Uj indicates that user Ui has granted update authorization on loan to Uj. Authorization Grant Graph : 34 Authorization Grant Graph Requirement: All edges in an authorization graph must be part of some path originating with the database administrator If DBA revokes grant from U1: Grant must be revoked from U4 since U1 no longer has authorization Grant must not be revoked from U5 since U5 has another authorization path from DBA through U2 Must prevent cycles of grants with no path from the root: DBA grants authorization to U7 U7 grants authorization to U8 U8 grants authorization to U7 DBA revokes authorization from U7 Must revoke grant U7 to U8 and from U8 to U7 since there is no path from DBA to U7 or to U8 anymore. Attempt to Defeat Authorization Revocation : 35 Attempt to Defeat Authorization Revocation Authorization Graph : 36 Authorization Graph Security Specification in SQL : 37 Security Specification in SQL The grant statement is used to confer authorization grant <privilege list> on <relation name or view name> to <user list> <user list> is: a user-id public, which allows all valid users the privilege granted A role (more on this later) Granting a privilege on a view does not imply granting any privileges on the underlying relations. The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator). Privileges in SQL : 38 Privileges in SQL select: allows read access to relation,or the ability to query using the view Example: grant users U1, U2, and U3 select authorization on the branch relation: grant select on branch to U1, U2, U3 insert: the ability to insert tuples update: the ability to update using the SQL update statement delete: the ability to delete tuples. references: ability to declare foreign keys when creating relations. usage: In SQL-92; authorizes a user to use a specified domain all privileges: used as a short form for all the allowable privileges Privilege To Grant Privileges : 39 Privilege To Grant Privileges with grant option: allows a user who is granted a privilege to pass the privilege on to other users. Example: grant select on branch to U1 with grant option gives U1 the select privileges on branch and allows U1 to grant this privilege to others Roles : 40 Roles Roles permit common privileges for a class of users can be specified just once by creating a corresponding “role” Privileges can be granted to or revoked from roles, just like user Roles can be assigned to users, and even to other roles SQL:1999 supports roles create role teller create role manager grant select on branch to teller grant update (balance) on account to teller grant all privileges on account to manager grant teller to manager grant teller to sanjay, ajay grant manager to vijay Revoking Authorization in SQL : 41 Revoking Authorization in SQL The revoke statement is used to revoke authorization. revoke<privilege list> on <relation name or view name> from <user list> [restrict|cascade] Example: revoke select on branch from U1, U2, U3 cascade Revocation of a privilege from a user may cause other users also to lose that privilege; referred to as cascading of the revoke. We can prevent cascading by specifying restrict: revoke select on branch from U1, U2, U3 restrict With restrict, the revoke command fails if cascading revokes are required. Revoking Authorization in SQL (cont) : 42 Revoking Authorization in SQL (cont) <privilege-list> may be all to revoke all privileges the revokee may hold. If <revokee-list> includes public all users lose the privilege except those granted it explicitly. If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation. All privileges that depend on the privilege being revoked are also revoked. Limitations of SQL Authorization : 43 Limitations of SQL Authorization SQL does not support authorization at a tuple level E.g. we cannot restrict students to see only (the tuples storing) their own grades With the growth in Web access to databases, database accesses come primarily from application servers. End users don't have database user ids, they are all mapped to the same database user id All end-users of an application (such as a web application) may be mapped to a single database user Limitations of SQL Authorization : 44 Limitations of SQL Authorization The task of authorization in above cases falls on the application program, with no support from SQL Benefit: fine grained authorizations, such as to individual tuples, can be implemented by the application. Drawback: Authorization must be done in application code, and may be dispersed all over an application Checking for absence of authorization loopholes becomes very difficult since it requires reading large amounts of application code Audit Trails : 45 Audit Trails An audit trail is a log of all changes (inserts/deletes/updates) to the database, including: which user performed the change when the change was made Used to track erroneous/fraudulent updates identify other updates made by the same user Can be implemented using triggers, but many database systems provide direct support UNIT-V : 46 UNIT-V Indexing and Hashing: Basic Concepts Indexing B+Tree Index Files B-Tree Index Files Static Hashing Comparison of Indexing and Hashing Index Definition in SQL Multiple_key Access Introduction to Transaction Concurrency Control Recovery Systems Query Interpretation and Query Optimisation You do not have the permission to view this presentation. In order to view it, please contact the author of the presentation.
CA II,U 4 ,Integrity Constraints and Rel mstufail Download Post to : URL : Related Presentations : Share Add to Flag Embed Email Send to Blogs and Networks Add to Channel Uploaded from authorPOINT lite Insert YouTube videos in PowerPont slides with aS Desktop Copy embed code: (To copy code, click on the text box) Embed: URL: Thumbnail: WordPress Embed Customize Embed The presentation is successfully added In Your Favorites. Views: 259 Category: Education License: All Rights Reserved Like it (0) Dislike it (0) Added: February 22, 2010 This Presentation is Public Favorites: 0 Presentation Description No description available. Comments Posting comment... Premium member Presentation Transcript UNIT IV Integrity Constraints and Relational Database Design Computer Application II M.S.Tufail Department of Mechanical Engg. Y.C.C.E, Nagpur : UNIT IV Integrity Constraints and Relational Database Design Computer Application II M.S.Tufail Department of Mechanical Engg. Y.C.C.E, Nagpur 1 UNIT-IV : 2 UNIT-IV Integrity Constraints and Relational Database Design: Domain Constraints Referential integrity Functional Dependencies Assertions Triggers Pitfalls in relational Database Normalisation Using Functional Dependencies Using Multi-Valued Dependencies Domain Key Normal Form. Integrity Constraints : 3 Integrity Constraints Integrity constraints provide a way of ensuring that changes made to the database by authorized users do not result in a loss of data consistency Integrity constraint with E-R models: Key declarations Stipulation that certain attributes form a candidate key for the entity set Form of a relationship Mapping cardinalities 1:1, 1:M & M:M An integrity constraint can be any attribute predicate applied to the database They may be costly to evaluate Domain Constraints : 4 Domain Constraints A domain of possible values should be associated with every attribute These domain constraints are the most basic form of integrity constraint Domain types: Attributes may have the same domain Ex. customer_name, employee_name It is not as clear whether branch_name & customer_name domain ought to be distinct At the conceptual level, they are both character strings Cont… Domain Constraints : 5 Domain Constraints The check clause in SQL-92 permits domains to be restricted in powerful ways: The check clause permits schema designer to specify a predicate that must be satisfied by any value assigned to a variable whose type is the domain Example Create domain hourly-wage numeric(5,2) Constraint wage-value-test check(value>= 4.00) Create domain account-number char(10) Constraint account-number-null-test check(value not null)) Referential Integrity : 6 Referential Integrity Often we wish to ensure that a value appearing in a relation for a given set of attributes also appears for another set of attributes in another relation. This is called referential integrity. Cont… Referential Integrity: Basic Concepts : 7 Referential Integrity: Basic Concepts Dangling tuples Consider a pair of relations r(R) and s(S), and the natural join r s There may be a tuple tr in r that does not join with any tuple in s i.e. there is no tuple ts in s such that tr[RS]= ts[RS] We call this a dangling tuple. Cont… Referential Integrity: Basic Concepts : 8 Referential Integrity: Basic Concepts Suppose there is a tuple t1 in the account relation with the value t1[branch_name] = “Sadar”, but no matching tuple in the branch relation for the Sadar branch This is undesirable, as t1 should refer to a branch that exists. Now suppose there is a tuple t2 in the branch relation with t2[branch_name] = “CA”, but no matching tuple in the account relation for CA branch This means that a branch exists for which no accounts exist. This is possible, for ex., when a branch being opened. We want to allow this situation. Cont… Referential Integrity: Basic Concepts : 9 Referential Integrity: Basic Concepts Note the distinction between these two situations: branch_name is the primary key of branch, while it is not for account In account, branch_name is a foreign key, being the primary key of another relation Cont… Referential Integrity in E-R Model : 10 Referential Integrity in E-R Model Every relation arising from a relationship set has referential integrity constraints Refer figure Fig shows an n-ary relationship set R relating entity sets E1, E2,……, En Let Ki denote the primary key of Ei The attributes of the relation schema for relationship set R include K1 K2 ... Kn Each Ki in the schema for R is a foreign key that leads to a referential integrity constraint. Cont… Referential Integrity in E-R Model : 11 Referential Integrity in E-R Model Relational schemas for weak entity sets must include the primary key of the strong entity set on which they are existent dependent. This is a foreign key, that leads to another integrity constraint. Referential Integrity in SQL : 12 Referential Integrity in SQL An addition to the original standard, allows specification of primary and candidate keys and foreign keys as part of the create table command primary key clause includes a list of attributes forming a candidate key unique key clause includes a list of attributes forming a candidate key foreign key clause includes a list of attributes forming the foreign key, and the name of the relation referenced by the foreign key Cont… Referential Integrity in SQL : 13 Referential Integrity in SQL Example Cont… not null, not null, not null, not null, not null, create table customer (customer-name char(20), customer-street char(30), customer-city char(30),primary key (customer-name)) create table branch (branch-name char(15), branch-city char(30), assets integer, primary key (branch-name)) Referential Integrity in SQL : 14 Referential Integrity in SQL Example Cont… not null, not null, not null, not null, not null, create table account(account-number char(10) not null,branch-name char(15),balance integer,primary key (account-number), foreign key (branch-name) references branch) create table depositor(customer-name char(20) not null,account-number char(10) not null, primary key (customer-name, account-number),foreign key (account-number) references account,foreign key (customer-name) references customer) Referential Integrity in SQL : 15 Notes on Foreign keys: A short form to declare a single column is a foreign key branch_name char(15) references branch When a referential integrity constraint is violated, the normal procedure is to reject the action. But a foreign key clause in SQL-92 can specify steps to be taken to change the tuples in the referenced relation to restore the constraint Referential Integrity in SQL Cont… Referential Integrity in SQL : 16 Example create table account ( . . .foreign key(branch-name) references branch on delete cascade on insert cascade. . . ) If a delete of a tuple in branch results in the preceding referential integrity constraints being violated, the delete is not rejected, but the delete “cascade” to the account relation, deleting the tuple that refers to the branch that was deleted Update will be cascaded to the new value of the branch! Referential Integrity in SQL Cont… Referential Integrity in SQL : 17 SQL-92 also allows the foreign key clause to specify actions other than cascade, such as setting the referencing field to null, or to a default value, if the constraint is violated on delete set null on delete set default If there is a chain of foreign key dependencies across multiple relations, a deletion or update at one end of the chain can propagate across the entire chain If a cascading update or delete causes a constraint violation that cannot be handled by a further cascading operation, the system aborts the transaction and all the changes caused by the transaction and its cascading actions are undone Referential Integrity in SQL Assertions : 18 Assertions An assertion is a predicate expressing a condition we wish the database to always satisfy Domain constraints, functional dependency and referential integrity are special forms of assertion Where a constraint cannot be expressed in these forms, we use an assertion, e.g.: Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch Ensuring every loan customer keeps a minimum of Rs. 1000 in an account Cont… Assertions : 19 Assertions An assertion in SQL-92 takes the form create assertion <assertion name> check (predicate> Ex.: Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch 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 (account.branch_name=branch.branch_name))) Cont… Assertions : 20 Assertions When an assertion is created, the system tests it for validity If the assertion is valid, any further modification to the database is allowed only if it does not cause that assertion to be violated This testing may result in significant overhead if the assertions are complex. Because of this, the assert should be used with great care. Triggers : 21 Triggers A trigger is a statement that is automatically executed by the system as a side effect of a modification to the database We need to specify the condition under which the trigger is executed the actions to be taken by the trigger Suppose that an overdraft is intended to result in the account balance being set to zero, and a loan being created for the overdraft amount The trigger actions for tuple t with negative balance are as follows: Cont… Triggers : 22 Triggers Insert a new tuple s in the borrower relation with s[branch_name] = t[branch_name] s[loan_number] = t[account_number] s[amount] = - t[balance] s[customer_name] = t[customer_name] We need to negate balance to get amount, as balance is negative Set t[balance] to 0 Not a good example! What would happen if the customer already had a loan? Cont… Triggers : 23 Triggers define trigger overdraft on update of account T (if new T.balance < 0 then (insert into loan values (T.branch_name, T.account_number, - new T.balance) insert into borrower (select customer_name, account_number from depositor where T.account_number=depositor.account_number) update account S set S.balance = 0 where S.account_number = T.account_number)) Cont… Triggers : 24 Triggers We sometimes require external world actions to be triggered on a database update E.g. re-ordering an item whose quantity in a warehouse has become small, or turning on an alarm light, Triggers cannot be used to directly implement external-world actions, BUT Triggers can be used to record actions-to-be-taken in a separate table Have an external process that repeatedly scans the table, carries out external-world actions and deletes action from table Cont… Triggers : 25 Triggers E.g. Suppose a warehouse has the following tables inventory(item, level) How much of each item is in the warehouse minlevel(item, level) The minimum desired level of each item reorder(item, amount) The quantity to re-order at a time orders(item, amount) Orders to be placed (read by external process) Cont… Triggers: When not to use : 26 Triggers: When not to use Triggers were used earlier for tasks such as maintaining summary data (e.g. total salary of each department) Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica There are better ways of doing these now: Databases today provide built in materialized view facilities to maintain summary data Databases provide built-in support for replication Encapsulation facilities can be used instead of triggers in many cases Define methods to update fields Carry out actions as part of the update methods instead of through a trigger Authorization : 27 Authorization Forms of authorization on parts of the database: Read authorization Allows reading, but not modification of data Insert authorization Allows insertion of new data, but not modification of existing data Update authorization Allows modification, but not deletion of data Delete authorization Allows deletion of data Cont… Authorization : 28 Authorization Forms of authorization to modify the database schema: Index authorization Allows creation and deletion of indices Resources authorization Allows creation of new relations Alteration authorization Allows addition or deletion of attributes in a relation Drop authorization Allows deletion of relations Authorization and Views : 29 Authorization and Views Users can be given authorization on views, without being given any authorization on the relations used in the view definition Ability of views to hide data serves both to simplify usage of the system and to enhance security by allowing users access only to data they need for their job A combination or relational-level security and view-level security can be used to limit a user’s access to precisely the data that user needs. View Example : 30 View Example Suppose a bank clerk needs to know the names of the customers of each branch, but is not authorized to see specific loan information. Approach: Deny direct access to the loan relation, but grant access to the view cust-loan, which consists only of the names of customers and the branches at which they have a loan. The cust-loan view is defined in SQL as follows: create view cust-loan as select branch_name, customer_name from borrower, loan where borrower.loan-number = loan.loan_number View Example : 31 View Example The clerk is authorized to see the result of the query: select * from cust-loan When the query processor translates the result into a query on the actual relations in the database, we obtain a query on borrower and loan Authorization must be checked on the clerk’s query before query processing replaces a view by the definition of the view Authorization on Views : 32 Authorization on Views Creation of view does not require resources authorization since no real relation is being created The creator of a view gets only those privileges that provide no additional authorization beyond that he already had. E.g. if creator of view cust-loan had only read authorization on borrower and loan, he gets only read authorization on cust-loan Granting of Privileges : 33 Granting of Privileges The passage of authorization from one user to another may be represented by an authorization graph. The nodes of this graph are the users. The root of the graph is the database administrator. Consider graph for update authorization on loan. An edge Ui Uj indicates that user Ui has granted update authorization on loan to Uj. Authorization Grant Graph : 34 Authorization Grant Graph Requirement: All edges in an authorization graph must be part of some path originating with the database administrator If DBA revokes grant from U1: Grant must be revoked from U4 since U1 no longer has authorization Grant must not be revoked from U5 since U5 has another authorization path from DBA through U2 Must prevent cycles of grants with no path from the root: DBA grants authorization to U7 U7 grants authorization to U8 U8 grants authorization to U7 DBA revokes authorization from U7 Must revoke grant U7 to U8 and from U8 to U7 since there is no path from DBA to U7 or to U8 anymore. Attempt to Defeat Authorization Revocation : 35 Attempt to Defeat Authorization Revocation Authorization Graph : 36 Authorization Graph Security Specification in SQL : 37 Security Specification in SQL The grant statement is used to confer authorization grant <privilege list> on <relation name or view name> to <user list> <user list> is: a user-id public, which allows all valid users the privilege granted A role (more on this later) Granting a privilege on a view does not imply granting any privileges on the underlying relations. The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator). Privileges in SQL : 38 Privileges in SQL select: allows read access to relation,or the ability to query using the view Example: grant users U1, U2, and U3 select authorization on the branch relation: grant select on branch to U1, U2, U3 insert: the ability to insert tuples update: the ability to update using the SQL update statement delete: the ability to delete tuples. references: ability to declare foreign keys when creating relations. usage: In SQL-92; authorizes a user to use a specified domain all privileges: used as a short form for all the allowable privileges Privilege To Grant Privileges : 39 Privilege To Grant Privileges with grant option: allows a user who is granted a privilege to pass the privilege on to other users. Example: grant select on branch to U1 with grant option gives U1 the select privileges on branch and allows U1 to grant this privilege to others Roles : 40 Roles Roles permit common privileges for a class of users can be specified just once by creating a corresponding “role” Privileges can be granted to or revoked from roles, just like user Roles can be assigned to users, and even to other roles SQL:1999 supports roles create role teller create role manager grant select on branch to teller grant update (balance) on account to teller grant all privileges on account to manager grant teller to manager grant teller to sanjay, ajay grant manager to vijay Revoking Authorization in SQL : 41 Revoking Authorization in SQL The revoke statement is used to revoke authorization. revoke<privilege list> on <relation name or view name> from <user list> [restrict|cascade] Example: revoke select on branch from U1, U2, U3 cascade Revocation of a privilege from a user may cause other users also to lose that privilege; referred to as cascading of the revoke. We can prevent cascading by specifying restrict: revoke select on branch from U1, U2, U3 restrict With restrict, the revoke command fails if cascading revokes are required. Revoking Authorization in SQL (cont) : 42 Revoking Authorization in SQL (cont) <privilege-list> may be all to revoke all privileges the revokee may hold. If <revokee-list> includes public all users lose the privilege except those granted it explicitly. If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation. All privileges that depend on the privilege being revoked are also revoked. Limitations of SQL Authorization : 43 Limitations of SQL Authorization SQL does not support authorization at a tuple level E.g. we cannot restrict students to see only (the tuples storing) their own grades With the growth in Web access to databases, database accesses come primarily from application servers. End users don't have database user ids, they are all mapped to the same database user id All end-users of an application (such as a web application) may be mapped to a single database user Limitations of SQL Authorization : 44 Limitations of SQL Authorization The task of authorization in above cases falls on the application program, with no support from SQL Benefit: fine grained authorizations, such as to individual tuples, can be implemented by the application. Drawback: Authorization must be done in application code, and may be dispersed all over an application Checking for absence of authorization loopholes becomes very difficult since it requires reading large amounts of application code Audit Trails : 45 Audit Trails An audit trail is a log of all changes (inserts/deletes/updates) to the database, including: which user performed the change when the change was made Used to track erroneous/fraudulent updates identify other updates made by the same user Can be implemented using triggers, but many database systems provide direct support UNIT-V : 46 UNIT-V Indexing and Hashing: Basic Concepts Indexing B+Tree Index Files B-Tree Index Files Static Hashing Comparison of Indexing and Hashing Index Definition in SQL Multiple_key Access Introduction to Transaction Concurrency Control Recovery Systems Query Interpretation and Query Optimisation