Nile University
Faculty of Computer Studies and Information
Technology
THID year -Semester Five (IT)
Semester 5 (2023-2024 )
Course title : Distributed Systems
Instructor : Hiba sayed
Lecture(5)
Distributed DBMS - Database Control
Outline
2
Authentication
Access rights
Integrity constraints
3 Distributed DBMS - Database Control
Controlling a database means being able to provide
correct data to valid users and applications. A data item
must satisfy the correctness condition(s) that have been
defined for it. The correctness conditions that are attached
to a piece of data are called constraints, semantic integrity
rules, integrity constraints, or assertions. Besides, data
should be screened away from unauthorized users so as
to maintain security and privacy of the database.
Database control is one of the primary tasks of the
database administrator (DBA).
IT_Distributed Systems_sem5
4 Distributed DBMS - Database Control
The three dimensions of database control are :
1. Authentication.
2. Access rights.
3. Semantic Integrity Control.
5 1. Authentication
In a distributed database system, In a distributed database
system, authentication guarantees that only legitimate users
have access to data resources.
Authentication can be enforced in two levels :
1. Controlling Access to Client Computer : At this level,
user access is restricted while login to the client computer
that provides user-interface to the database server. The most
common method is a username/password combination.
However, more sophisticated methods like biometric
authentication may be used for high security data.
6 1. Authentication cont.
2. Controlling Access to the Database Software :
Once the user is connected to the computer, accessing the DBE
software is controlled by the second level of authentication. At
this level, the user may use credentials that have been assigned
by the DBE or the DBE can trust the operating system
authentication and allow connection to the DBE software. The
first approach is known as database authentication while the
second is known as operating system authentication.
7 2. Access Rights
In relational database systems, what a database user can do
inside the database is controlled by the access rights that are
given to that user. A user’s access rights specify the privileges
that the user has. such as the rights to create a table, drop a
table, add/delete/update tuples in a table or query upon the
table.
In any large database environment, there are many users,
many databases, many tables with many columns, and many
other database objects. As a result, in large systems,
assignment of individual privileges to individual users is a
time-consuming and error-prone task.
To reduce the overhead associated with management of rights
for a large system, the concept of a role is used.
8 2. Access Rights cont.
A role is a construct with certain privileges within a
database system. Once the different roles are defined,
the individual users are assigned one of these roles.
Often a hierarchy of roles are defined according to the
organization’s hierarchy of authority and
responsibility.
9 2. Access Rights cont.
For example, the following SQL statements shows creation of a
role called MANAGER. This role is then given some privileges
and, finally, USER1 is given the MANAGER role. After
executing these statements, USER1 will have all privileges that
have been assigned to the role ‘MANAGER’. USER1 plays the
role of a MANAGER.
CREATE ROLE MANAGER;
COMMIT;
GRANT SELECT, INSERT, UPDATE ON EMPLOYEE TO MANAGER;
GRANT INSERT, UPDATE, DELETE ON PROJECT TO MANAGER;
GRANT SELECT ON DEPARTMENT TO MANAGER;
GRANT EXECUTE ON PAYROLL TO MANAGER;
COMMIT;
GRANT MANAGER TO USER1;
COMMIT;
IT_Distributed Systems_sem5
10 3. Semantic Integrity Control
A DBMS must have the ability to specify and enforce
correctness assertions in terms a set of semantic integrity
rules.
Semantic integrity control defines and enforces the
integrity constraints of the database system.
The integrity constraints are as follows :
▪ Data type integrity constraint.
▪ Entity integrity constraint.
▪ Referential integrity constraint.
IT_Distributed Systems_sem5
11 Data Type Integrity Constraint
A data type constraint restricts the range of values and the
type of operations that can be applied to the field with the
specified data type.
For example, let us consider that a table "HOSTEL" has
three fields - the hostel number, hostel name and capacity.
The hostel number should start with capital letter "H" and
cannot be NULL, and the capacity should not be more
than 150. The following SQL command can be used for
data definition :
CREATE TABLE HOSTEL (
H_NO VARCHAR2(5) NOT NULL,
H_NAME VARCHAR2(15),
CAPACITY INTEGER,
CHECK ( H_NO LIKE 'H%'),
CHECK ( CAPACITY <= 150) );
12 Entity Integrity Control
Entity integrity control enforces the rules so that each
tuple can be uniquely identified from other tuples. For this
a primary key is defined.
A primary key is a set of minimal fields that can uniquely
identify a tuple.
Entity integrity constraint states that no two tuples in a
table can have identical values for primary keys and that
no field which is a part of the primary key can have NULL
value.
IT_Distributed Systems_sem5
13 Entity Integrity Control
For example, in the above hostel table, the hostel
number can be assigned as the primary key through the
following SQL statement (ignoring the checks) :
CREATE TABLE HOSTEL (
H_NO VARCHAR2(5) PRIMARY KEY,
H_NAME VARCHAR2(15),
CAPACITY INTEGER );
IT_Distributed Systems_sem5
14 Referential Integrity Constraint
Referential integrity constraint lays down the rules of
foreign keys.
A foreign key is a field in a data table that is the primary
key of a related table.
The referential integrity constraint :restrict the values
stored in the database so
that they reflect the way the objects of the real world are
related to each other. lays down the rule that the value of
the foreign key field should either be among the values of
the primary key of the referenced table or be entirely
NULL.
IT_Distributed Systems_sem5
15 Referential Integrity Constraint cont.
For example, let us consider a student table where a
student may opt to live in a hostel. To include this, the
primary key of hostel table should be included as a foreign
key in the student table. The following SQL statement
incorporates this :
CREATE TABLE STUDENT (
S_ROLL INTEGER PRIMARY KEY,
S_NAME VARCHAR2(25) NOT NULL,
S_COURSE VARCHAR2(10),
S_HOSTEL VARCHAR2(5) REFERENCES HOSTEL
); IT_Distributed Systems_sem5
16 Distributed Semantic Integrity Control
In distributed systems, semantic integrity assertions are
basically the same as in centralized systems (i.e., relation,
data type, referential, and explicit constraints). When
considering a distributed system, semantic integrity
control issues become more complicated due to
distribution. The first question that we have to answer is,
“Where are the semantic integrity rules defined?” When
thinking about the location of the rules, we have to be
aware of the fact that some semantic integrity rules are
local and some are distributed.
IT_Distributed Systems_sem5
17 Distributed Semantic Integrity Control cont.
After we define each rule, we need to consider which tables
are affected or referenced by it. When all the tables are
deployed in a single database (at a single site), the rule is a
local rule; otherwise, it is a distributed rule.
We also need to consider where each rule should be
defined and enforced. It makes sense to have local rules
defined and enforced at the site where all the tables
involved are deployed.
Deciding where to define and implement the enforcement
for distributed (multisite) rules is a much bigger challenge.
18 Distributed Semantic Integrity Control cont.
The following outlines the additional challenges in
distributed semantic integrity control:
▪ Creation of multisite semantic integrity rules.
▪ Enforcement of multisite semantic integrity rules.
▪ Maintaining mutual consistency of local semantic
integrity rules across copies.
▪ Maintaining consistency of local semantic integrity rules
and global semantic integrity rules.
19 Distributed Semantic Integrity Control cont.
It should be obvious that these challenges are due to
database fragmentation, replication, and distribution.
Obviously, the more a database is fragmented and/or
replicated, the more overhead will be involved in creation
and enforcement of the integrity rules.
The main component of this overhead is the cost of
communication between sites. An attempt to minimize the
cost of communication can reduce the overall overhead
associated with the creation and enforcement of integrity
rules in a distributed database system.
One approach for minimizing communication cost is to try
to distribute a database in such a way that does not create
multisite semantic integrity rules.
Please find the “Review Questions No (5)” document
