Distributed DBMS - Database Control

Distributed DBMS - Database Control

Database control refers to the role of implementing regulations in order to provide authentic database users and applications with correct data. All data should conform to the integrity constraints specified in the database in order for correct data to be accessible to users. In addition , data should be filtered away from unauthorized users in order to protect the database's protection and privacy. One of the key roles of the database administrator ( DBA) is database control.

The three aspects of database control are –

• Authentication
• Access rights
• Integrity constraints

Authentication

Authentication is the process in a distributed database system in which only legitimate users can gain access to the data resources.

Two levels of authentication can be applied.

• Controlling Client Computer Access − User access is limited at this level when logging into the client computer that provides the database server with the user interface. A username / password combination is the most prevalent method. More advanced techniques like biometric authentication can, however, be used for high-security data.
• Database Software Access Control − The database software / administrator assigns certain credentials to the user at this level. Using these keys, the user gains access to the data base. The development of a login account inside the database server is one of the methods.

Access Rights

The access rights of a user refer to the privileges given to the user for DBMS operations, such as the rights to create a table, drop a table, add / delete / update tuples in a table, or to query the table.

It is not feasible to assign individual access rights to users in distributed environments, because there are large numbers of tables and yet greater numbers of users. So, those roles are specified by DDBMS. A position is a construct within a database system that has certain privileges. Once the various roles are established, one of these roles is allocated to the individual users. Sometimes, a hierarchy of roles is established according to the hierarchy of authority and obligation of the company.

For example , the following SQL statements establish a "Accountant" function and then assign it to a "ABC" user.

CREATE ROLE ACCOUNTANT; 
GRANT SELECT, INSERT, UPDATE ON EMP_SAL TO ACCOUNTANT; 
GRANT INSERT, UPDATE, DELETE ON TENDER TO ACCOUNTANT; 
GRANT INSERT, SELECT ON EXPENSE TO ACCOUNTANT; 
COMMIT; 
GRANT ACCOUNTANT TO ABC; 
COMMIT;

Semantic Integrity Control

Semantic control of integrity determines and enforces the database system's integrity constraints.

The limits of integrity are as follows-

• Data type integrity constraint
• Entity integrity constraint
• Referential integrity constraint

Data Type Integrity Constraint

The constraint of the data type limits the range of values and the type of operations that can be applied to the field with the data type defined.

For example, let us remember that there are three fields in a "HOSTEL" list-the number of the hostel, the name of the hostel and the capacity. The number of the hostel should begin with the capital letter 'H' and can not be NULL, and the capacity should not exceed 150. For data definition, the following SQL command can be used –

CREATE TABLE HOSTEL ( 
   H_NO VARCHAR2(5) NOT NULL, 
   H_NAME VARCHAR2(15), 
   CAPACITY INTEGER, 
   CHECK ( H_NO LIKE 'H%'), 
   CHECK ( CAPACITY <= 150) 
); 

Entity Integrity Control

Entity integrity control enforces the rules such that it is possible to distinguish and tuple from other tuples uniquely. A primary key is assigned for this. A primary key is a set of minimal fields that can identify a tuple uniquely. Entity integrity constraint states that for primary keys, no two tuples in a table can have identical values and that no field that is part of the primary key can have a NULL value.

For instance, the hostel number can be assigned as the primary key in the above hostel table via the following SQL statement (ignoring the checks) –

CREATE TABLE HOSTEL ( 
   H_NO VARCHAR2(5) PRIMARY KEY, 
   H_NAME VARCHAR2(15), 
   CAPACITY INTEGER 
); 

Referential Integrity Constraint

The rules of foreign keys are set out by referential integrity constraint. A foreign key is a field in a data table that is a similar table's primary key. The referential integrity constraint lays down the rule that the foreign key field value should either be one of the primary key values of the referenced table, or it should be NULL in its entirety.

For example, let us consider a student table where a student can choose to live in a hostel. To include this, in the student table, the primary key of the hostel table should be used as a foreign key. This is part of the following SQL statement –

CREATE TABLE STUDENT (  
   S_ROLL INTEGER PRIMARY KEY, 
   S_NAME VARCHAR2(25) NOT NULL, 
   S_COURSE VARCHAR2(10), 
   S_HOSTEL VARCHAR2(5) REFERENCES HOSTEL 
);