Access restrictions are enforced by the DBMS facility that ensures that only authorised users gain access to the DBMS. For example, a valid user is allowed to manipulate a table with given access rights.
Integrity constraints are constraints that maintain the consistency and correctness of data.
They protect the contents of the database in totally distinct ways. When a DBMS restricts access to a user to do certain things, like granting right to only view a table rather than update it, then it is ensuring that the data providers with responsibility for maintaining the accuracy of the data content enters, updates and deletes the data while the data consumers just have the privilege to review data for their proper functioning. Also the sensitive company data pertaining to finance and personnel functions can be shielded from the prying eyes of those who have no need for direct access to this data by not authorizing access to these data areas. Essentially, access restriction is ensuring that only the data necessary and sufficient for carrying out a job is made available to the person and the rest of the data is hidden from him. Through access restrictions we can segregate responsibilities within the organisations by providing access authorization to data horizon necessary for a role. These security access restrictions are centrally defined and DBMS automatically enforces them while accessing the database (Block1,p24). The data correctness is achieved by proper responsibility sharing through access privileges and obviating the potential for unauthorised rogue data manipulations. Any SQL statement issued by a user can only be commensurate with his authorised access profile or DBMS will not execute it.
Integrity constraint ability to enforce consistent and correctness is best understood through example. If we have a geographical hierarchy with levels of company, country, region and world (eg Unilever UK, UK, Europe, Global held in COMPANIES, COUNTRIES, REGIONS and WORLD tables) then while defining a company in COMPANIES table it ensures that it is only linked to the valid countries defined in the COUNTRIES table in the database and countries and linked to the valid regions in REGIONS table. If we had linked a company to an undefined country in the database, then while aggregating regional data this rogue country would have been missed in table linkages as it is not part of the hierarchy. Also if we try to delete a region in our REGIONS table while there are countries linked to that region in the COUNTRIES table then integrity constraint could either prevent us from carrying out this operation or cascade the delete to all the records in COUNTRIES table that are linked to REGIONS table with region_id as the foreign key. All these type of rule governing referential integrity are stored in the system catalog managed by the DBMS and are automatically enforced by DBMS without required any programming intervention by the developer (Block1,p24). Although we have focussed on the referential integrity in our example, we can see the data integrity being enforced by DBMS when it enforces user-defined rules like date of birth has to be lower than date of school start or numeric phone number should not have any arithmetic operations performed on them. We can define a number of integrity constraints like email address must include ‘@’, the values for a particular column must be within 100-800 range or area code is restricted to a subset of predefined codes etc. These are all examples of integrity constraints defined to ensure the correctness and validity of the data contained in the database. The integrity constraints could also be implemented through pre-insert, pre-update, pre-delete triggers etc on the tables.
No comments:
Post a Comment