Unit-IV: Distributed and Client Server Databases

Distributed and Client Server Databases: Need for Distributed Database Systems - Structure
of Distributed Database - Advantages and Disadvantages of DDBMS - Advantages of Data
Distribution - Disadvantages of Data Distribution - Data Replication - Data Fragmentation.
Client Server Databases: Emergence of Client Server Architecture - Need for Client Server
Computing - Structure of Client Server Systems & its advantages. PL/SQL Introduction,
Language fundamentals, conditional and sequential control, Iterative, Processing and loops.

Distributed and Client Server Databases:

Need for Distributed Database Systems:
A distributed database is basically a database that is not limited to one system, it is spread
over different sites, i.e, on multiple computers or over a network of computers. A distributed
database system is located on various sites that don’t share physical components. This may be
required when a particular database needs to be accessed by various users globally. It needs to
be managed such that for the users it looks like one single database.
Types:
1. Homogeneous Database:
In a homogeneous database, all different sites store database identically. The operating
system, database management system, and the data structures used – all are the same at all
sites. Hence, they’re easy to manage.
2. Heterogeneous Database:
In a heterogeneous distributed database, different sites can use different schema and software
that can lead to problems in query processing and transactions. Also, a particular site might
be completely unaware of the other sites. Different computers may use a different operating
system, different database application. They may even use different data models for the
database. Hence, translations are required for different sites to communicate.
Distributed Data Storage:
There are 2 ways in which data can be stored on different sites. These are:
1. Replication –
In this approach, the entire relationship is stored redundantly at 2 or more sites. If the entire
database is available at all sites, it is a fully redundant database. Hence, in replication,
systems maintain copies of data.
This is advantageous as it increases the availability of data at different sites. Also, now query
requests can be processed in parallel.
However, it has certain disadvantages as well. Data needs to be constantly updated. Any
change made at one site needs to be recorded at every site that relation is stored or else it may
lead to inconsistency. This is a lot of overhead. Also, concurrency control becomes way more
complex as concurrent access now needs to be checked over several sites.
2. Fragmentation –
In this approach, the relations are fragmented (i.e., they’re divided into smaller parts) and
each of the fragments is stored in different sites where they’re required. It must be made sure
that the fragments are such that they can be used to reconstruct the original relation (i.e, there
isn’t any loss of data).
Fragmentation is advantageous as it doesn’t create copies of data, consistency is not a
problem.

Fragmentation of relations can be done in two ways:

 Horizontal fragmentation – Splitting by rows –

The relation is fragmented into groups of tuples so that each tuple is assigned to at
least one fragment.
 Vertical fragmentation – Splitting by columns –
The schema of the relation is divided into smaller schemas. Each fragment must
contain a common candidate key so as to ensure a lossless join.
In certain cases, an approach that is hybrid of fragmentation and replication is used.
Applications of Distributed Database:
 It is used in Corporate Management Information System.
 It is used in multimedia applications.
 Used in Military’s control system, Hotel chains etc.
 It is also used in manufacturing control system.
A distributed database system is a type of database management system that stores data
across multiple computers or sites that are connected by a network. In a distributed database
system, each site has its own database, and the databases are connected to each other to form
a single, integrated system.
The main advantage of a distributed database system is that it can provide higher availability
and reliability than a centralized database system. Because the data is stored across multiple
sites, the system can continue to function even if one or more sites fail. In addition, a
distributed database system can provide better performance by distributing the data and
processing load across multiple sites.
Structure Of Distributed Database System:
In Distributed DBMS, data is distributed over the geographical site. Each site is a complete
database system site on its end, but the different sites have to work together because if any
user wants to access the data it can easily access data anywhere in the network as the data is
stored at the user’s own computer.
Advantages of Distributed Database System:
1) There is fast data processing as several sites participate in request processing.
2) Reliability and availability of this system is high.
3) It possesses reduced operating cost.
4) It is easier to expand the system by adding more sites.
5) It has improved sharing ability and local autonomy.
Disadvantages of Distributed Database System:
1) The system becomes complex to manage and control.
2) The security issues must be carefully managed.
3) The system requires deadlock handling during the transaction processing otherwise
the entire system may be in inconsistent state.
4) There is need of some standardization for processing of distributed database
system.
Difference between Client/Server and Distributed DBMS:

S.No
. Client/Server Distributed DBMS

Client can access only one User can access many sites
1.
server at a time. simultaneously.

2. It is difficult to manage. It is easy to manage.

 S.No
. Client/Server Distributed DBMS

In this data is distributed In this data is distributed

3.
across clients. across sites.

Speed of accessing database Speed of accessing database is

4. is poor as compared to much better than Client/Server
Distributed DBMS. Architecture.

If somehow server crashes, The crash of one site does not

5.
the whole system stops. stop the entire system.

Accessing of data is easy to Accessing of data is difficult

6.
control. to control.

It is less expensive as
7. compared to Distributed It is expensive.
DBMS.

8. Maintenance cost is low. Maintenance cost is high.

Data Replication:
Data Replication is the process of storing data in more than one site or node. It is useful
in improving the availability of data. It is simply copying data from a database from one
server to another server so that all the users can share the same data without any
inconsistency. The result is a distributed database in which users can access data relevant to
their tasks without interfering with the work of others. Data replication encompasses the
duplication of transactions on an ongoing basis so that the replicate is in a consistently
updated state and synchronized with the source. However, in data replication data is
available at different locations, but a particular relation has to reside at only one location.
There can be full replication, in which the whole database is stored at every site. There can
also be partial replication, in which some frequently used fragments of the database are
replicated, and others are not replicated.
Replication Schema or Types:
1. Full Replication: The most extreme case is replication of the whole database at every site
in the distributed system. This will improve the availability of the system because the system
can continue to operate as long as atleast one site is up.

Advantages of full replication:

 High Availability of Data.
 Improves the performance for retrieval of global queries as the result can be obtained
locally from any of the local site.
 Faster execution of Queries.
Disadvantages of full replication:
 Concurrency is difficult to achieve in full replication.
 Slow update process as a single update must be performed at different databases to
keep the copies consistent.
No replication
No replication means, each fragment is stored exactly at one site.

Advantages of No replication:
 The data can be easily recovered.
 Concurrency can be achieved in no replication.
Disadvantages of No replication –
 Since multiple users are accessing the same server, it may slow down the execution of
queries.
  The data is not easily available as there is no replication.Concurrency has been
minimized as only one site to be updated.
3.Partial replication: Partial replication means, some fragments are replicated whereas
others are not. Only a subset of the database is replicated at each site. This reduces storage
costs but requires careful planning to ensure data consistency.

Advantages of Partial Replication:

 To provide a consistent copy of data across all the database nodes.
 To increase the availability of data.
 The reliability of data is increased through data replication.
 Data Replication supports multiple users and gives high performance.
 To remove any data redundancy, the databases are merged and slave databases are
updated with outdated or incomplete data.
 Since replicas are created there are chances that the data is found itself were the
transaction is executing which reduces the data movement.
 To perform faster execution of queries.
Disadvantages of Partial Replication–
 More storage space is needed as storing the replicas of same data at different sites
consumes more space.
 Data Replication becomes expensive when the replicas at all different sites need to be
updated.
 Maintaining Data consistency at all different sites involves complex measures.
Features of data replication are:
Increased Availability: Data replication can improve availability by providing multiple
copies of the same data in different locations, which reduces the risk of data unavailability
due to network or hardware failures.
Improved Performance: Replicated data can be accessed more quickly since it is available
in multiple locations, which can help to reduce network latency and improve query
performance.
Enhanced Scalability: Replication can improve scalability by distributing data across
multiple nodes, which allows for increased processing power and improved performance.
Improved Fault Tolerance: By storing data redundantly in multiple locations, replication
can improve fault tolerance by ensuring that data remains available even if a node or network
fails.
Improved Data Locality: Replication can improve data locality by storing data close to the
applications or users that need it, which can help to reduce network traffic and improve
performance.
Simplified Backup and Recovery: Replication can simplify backup and recovery processes
by providing multiple copies of the same data in different locations, which reduces the risk of
data loss due to hardware or software failures.
Enhanced Disaster Recovery: Replication can improve disaster recovery capabilities by
providing redundant copies of data in different geographic locations, which reduces the risk
of data loss due to natural disasters or other events.
The advantages of data replication include:
1. Improved performance, as data can be read from a local copy of the data instead of a
remote one.
2. Increased data availability, as copies of the data can be used in case of a failure of the
primary database.
3. Improved scalability, as the load on the primary database can be reduced by reading
data from the replicas.
The disadvantages of data replication include:
1. Increased complexity, as the replication process needs to be configured and
maintained.
2. Increased risk of data inconsistencies, as data can be updated simultaneously on
different replicas.
3. Increased storage and network usage, as multiple copies of the data need to be stored
and transmitted.
4. Data replication is widely used in various types of systems, such as online transaction
processing systems, data warehousing systems, and distributed systems.
Data Fragmentation
The process of dividing the database into smaller multiple parts or sub−tables is called
fragmentation. The smaller parts or sub−tables are called fragments and are stored at different
locations. Data fragmentation should be done in a way that the reconstruction of the original
parent database from the fragments is possible. The restoration can be done using UNION or
JOIN operations.
Database fragmentation is of three types:
 Horizontal fragmentation
 Vertical fragmentation
 Mixed or Hybrid fragmentation.
Horizontal Fragmentation
It divides a table horizontally into a group of rows to create multiple fragments or subsets of a
table. These fragments can then be assigned to different sites in the database. Reconstruction
is done using UNION or JOIN operations. In relational algebra, it is represented as σp(T) for
any given table(T).
Example
In this example, we are going to see how the horizontal fragmentation looks in a table.
Input:
STUDENT
id name age salary
1 aman 21 20000
2 naman 22 25000
3 raman 23 35000
4 sonam 24 36000
Example
SELECT * FROM student WHERE salary<35000;
SELECT * FROM student WHERE salary>35000;
Output
id name age salary
1 aman 21 20000
2 naman 22 25000

id name age salary

4 soman 24 36000

Vertical Fragmentation
It divides a table vertically into a group of columns to create multiple fragments or subsets of
a table. These fragments can then be assigned to different sites in the database.
Reconstruction is done using full outer join operation.
Example
This example shows how the Select statement is used to do the fragmentation and to provide
the output.
Input Table:
STUDENT
id name age salary
1 aman 21 20000
 2 naman 22 25000
3 raman 23 35000
4 sonam 24 36000
Example
SELECT * FROM name;#fragmentation 1
SELECT * FROM id, age;#fragmentation 2
Output
name
aman
naman
raman
sonam

age
21
22
23
24

Mixed or Hybrid Fragmentation

It is done by performing both horizontal and vertical partitioning together. It is a group of
rows and columns in relation.
Example
This example shows how the Select statement is used with the where clause to provide the
output.
SELECT * FROM name WHERE age=22;
Output
name age
naman 22
Client-Server Databases:
Client/server is developed to deal with various computing environments that have many
computers and servers connected via a network. In this architecture, a client is a user machine
which provides the user interface and local processing capabilities. When any client requires
additional functionality like database access, it can connect to Server that can provide the
functionality needed by the client. Basically, Server is a machine that provides services to the
Client i.e user machine.
Need for Client Server Computing:
In client server computing, the clients request a resource, and the server provides that
resource. A server may serve multiple clients at the same time while a client is in contact with
only one server.
The different structures for two tier and three tier are given as follows −
Two - Tier Client/Server Structure
The two tier architecture primarily has two parts, a client tier and a server tier.The client tier
sends a request to the server tier and the server tier responds with the desired information.
An example of a two-tier client/server structure is a web server. It returns the required web
pages to the clients that requested them.
An illustration of the two-tier client/server structure is as follows −

Advantages of Two - Tier Client/Server Structure

Some of the advantages of the two-tier client/server structure are −
 This structure is quite easy to maintain and modify.
 The communication between the client and server in the form of request response
messages is quite fast.
Disadvantages of Two - Tier Client/Server Structure
A major disadvantage of the two-tier client/server structure is −
 If the client nodes are increased beyond capacity in the structure, then the server is not
able to handle the request overflow and performance of the system degrades.
Three - Tier Client/Server Structure
The three-tier architecture has three layers namely client, application, and data layer. The
client layer is the one that requests the information. In this case it could be the GUI, web
interface etc. The application layer acts as an interface between the client and data layer. It
helps in communication and provides security. The data layer is the one that contains the
required data.
An illustration of the three-tier client/server structure is as follows −

Advantages of Three - Tier Client/Server Structure

Some of the advantages of the three-tier client/server structure are −
 The three-tier structure provides much better service and fast performance.
 The structure can be scaled according to requirements without any problem.
 Data security is much improved in the three-tier structure.
Disadvantages of Three - Tier Client/Server Structure
A major disadvantage of the three-tier client/server structure is −
 Three - tier client/server structure is quite complex due to advanced features.
Advantages of Client-server Database Architecture in DBMS
1. All the data and resources are controlled by server .im this way all data and resources
are very consistent.
2. You can easily increase the number of clients in this architecture at any time. This all
increases the scalability of the network.
3. This is very easy to maintain you can easily repair, replace or add clients in this
network. the independence of the changes also known as encapsulation.
 4. This network is very easy to use, and it is not complicated.
Disadvantages of Client-server Database Architecture in DBMS
1. Traffic is a big problem in this network.
2. When you add large numbers of the client with server this network will be more
complicated.
3. When the server goes down all the clients are not able to send their request. The
whole work will be stopped.
4. The hardware and software are very expensive.
5. The client does not have resources for each resource they need to request the server.
Because of all resources exit on server
PL/SQL Introduction:
PL/SQL is a block structured language that enables developers to combine the power of SQL
with procedural statements. All the statements of a block are passed to oracle engine all at
once which increases processing speed and decreases the traffic.
Basics of PL/SQL
 •PL/SQL stands for Procedural Language extensions to the Structured Query
Language (SQL).
 PL/SQL is a combination of SQL along with the procedural features of programming
languages.
 Oracle uses a PL/SQL engine to processes the PL/SQL statements.
 PL/SQL includes procedural language elements like conditions and loops. It allows
declaration of constants and variables, procedures and functions, types and variable of
those types and triggers.
Disadvantages of SQL:
 SQL doesn’t provide the programmers with a technique of condition checking,
looping, and branching.
 SQL statements are passed to Oracle engine one at a time which increases traffic and
decreases speed.
 SQL has no facility of error checking during manipulation of data.
Features of PL/SQL:
1. PL/SQL is basically a procedural language, which provides the functionality of
decision making, iteration and many more features of procedural programming
languages.
2. PL/SQL can execute several queries in one block using single command.
 3. One can create a PL/SQL unit such as procedures, functions, packages, triggers, and
types, which are stored in the database for reuse by applications.
4. PL/SQL provides a feature to handle the exception which occurs in PL/SQL block
known as exception handling block.
5. Applications written in PL/SQL are portable to computer hardware or operating
system where Oracle is operational.
6. PL/SQL Offers extensive error checking.
Differences between SQL and PL/SQL:

SQL PL/SQL

PL/SQL is a block of codes that used to

SQL is a single query that is used to
write the entire program blocks/ procedure/
perform DML and DDL operations.
function, etc.

It is declarative, that defines what needs to

PL/SQL is procedural that defines how the
be done, rather than how things need to be
things needs to be done.
done.

Execute as a single statement. Execute as a whole block.

Mainly used to manipulate data. Mainly used to create an application.

It is an extension of SQL, so it can contain

Cannot contain PL/SQL code in it.
SQL inside it.

Structure of PL/SQL Block:

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a
structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All
PL/SQL programs are made up of blocks, which can be nested within each other.

Typically, each block performs a logical action in the program. A block has the following
structure:
DECLARE
declaration statements.
BEGIN
executable statements
EXCEPTIONS
exception handling statements
END;
 Declare section starts with DECLARE keyword in which variables, constants,
records as cursors can be declared which stores data temporarily. It basically consists
of definition of PL/SQL identifiers. This part of the code is optional.
 Execution section starts with BEGIN and ends with END keyword. This is a
mandatory section and here the program logic is written to perform any task like loops
and conditional statements. It supports all DML commands, DDL commands and
SQL*PLUS built-in functions as well.
 Exception section starts with EXCEPTION keyword. This section is optional which
contains statements that are executed when a run-time error occurs. Any exceptions
can be handled in this section.
PL/SQL identifiers
There are several PL/SQL identifiers such as variables, constants, procedures, cursors,
triggers etc.
1. Variables: Like several other programming languages, variables in PL/SQL must be
declared prior to its use. They should have a valid name and data type as well. Syntax
for declaration of variables:
variable_name datatype [NOT NULL := value ];
1. Example to show how to declare variables in PL/SQL :

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;
BEGIN
null;
END;
/

1. Output:
PL/SQL procedure successfully completed.
1. Explanation:
 SET SERVEROUTPUT ON: It is used to display the buffer used by the
dbms_output.
 var1 INTEGER: It is the declaration of variable, named var1 which is of
integer type. There are many other data types that can be used like float, int,
real, smallint, long etc. It also supports variables used in SQL as well like
NUMBER (prec, scale), varchar, varchar2 etc.
 PL/SQL procedure successfully completed.: It is displayed when the code is
compiled and executed successfully.
 Slash (/) after END; The slash (/) tells the SQL*Plus to execute the block.
 Assignment operator (: =) : It is used to assign a value to a variable.
2. Displaying Output: The outputs are displayed by using DBMS_OUTPUT which is a
built-in package that enables the user to display output, debugging information, and
send messages from PL/SQL blocks, subprograms, packages, and triggers. Let us see
an example to see how to display a message using PL/SQL:

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
 var varchar2(40) := 'I love Computer Projects' ;
BEGIN
dbms_output.put_line(var);
END;
/

1. Output:
I love Computer Projects
PL/SQL procedure successfully completed.
1. Explanation:
 dbms_output.put_line : This command is used to direct the PL/SQL output to a
screen.
2. Using Comments: Like in many other programming languages, in PL/SQL also,
comments can be put within the code which has no effect in the code. There are two
syntaxes to create comments in PL/SQL:
 Single Line Comment: To create a single line comment, the symbol – – is
used.
 Multi Line Comment: To create comments that span over several lines, the
symbol /* and */ is used.
3. Taking input from user: Just like in other programming languages, in PL/SQL also,
we can take input from the user and store it in a variable. Let us see an example to
show how to take input from users in PL/SQL:

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
-- taking input for variable a
a number := &amp;a;
-- taking input for variable b
b varchar2(30) := &amp;b;
BEGIN
null;
END;
/
 1. Output:
Enter value for a: 24
old 2: a number := &a;
new 2: a number := 24;
Enter value for b: 'HiHello'
old 3: b varchar2(30) := &b;
new 3: b varchar2(30) := 'HiHello';
PL/SQL procedure successfully completed.
1. (***) Let us see an example on PL/SQL to demonstrate all above concepts in one
single block of code.

--PL/SQL code to print sum of two numbers taken from the user.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
-- taking input for variable a
a integer := &a ;
-- taking input for variable b
b integer := &b ;
c integer ;
BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);
END;
/

Enter value for a: 2

Enter value for b: 3
Sum of 2 and 3 is = 5
PL/SQL procedure successfully completed.
PL/SQL Execution Environment:
The PL/SQL engine resides in the Oracle engine.The Oracle engine can process not only
single SQL statement but also block of many statements.The call to Oracle engine needs to be
made only once to execute any number of SQL statements if these SQL statements are
bundled inside a PL/SQL block.
PL/SQL Control Structures
PL/SQL Control Structures are used to control flow of execution. PL/SQL provides different
kinds of statements to provide such type of procedural capabilities.These statements are
almost same as that of provided by other languages.
The flow of control statements can be classified into the following categories:
 Conditional Control
 Iterative Control
 Sequential Control
Conditional Control:
PL/SQL allows the use of an IF statement to control the execution of a block of code.
In PL/SQL, the IF -THEN - ELSIF - ELSE - END IF construct in code blocks allow
specifying certain conditions under which a specific block of code should be executed.
Syntax:
IF < Condition > THEN
< Action >
ELSIF <Condition> THEN
< Action >
ELSE < Action >
END IF;
Example:
create file named "condi.sql"
DECLARE
a Number := 30; b Number;
BEGIN
IF a > 40 THEN
b := a - 40;
DBMS_OUTPUT.PUT_LINE('b=' || b);
elseif a = 30 then
b := a + 40;
DBMS_OUTPUT.PUT_LINE('b=' || b);
 ELSE
b := 0;
DBMS_OUTPUT.PUT_LINE('b=' || b);
END IF;
END;
/
Output:
Run SQL Command Line
SQL>set serveroutput on

SQL>start d://condi.sql
b=70

PL/SQL successfully completed.

Iterative Control :
Iterative control indicates the ability to repeat or skip sections of a code block.
A loop marks a sequence of statements that has to be repeated. The keyword loop has to be
placed before the first statement in the sequence of statements to be repeated, while the
keyword end loop is placed immediately after the last statement in the sequence.
Once a loop begins to execute, it will go on forever. Hence a conditional statement that
controls the number of times a loop is executed always accompanies loops.
PL/SQL supports the following structures for iterative control:
Simple loop :
In simple loop, the key word loop should be placed before the first statement in the sequence
and the keyword end loop should be written at the end of the sequence to end the loop.
Syntax:
Loop
< Sequence of statements >
End loop;
Example:
create file named it.sql
DECLARE
i number := 0;
BEGIN
LOOP
 dbms_output.put_line ('i = '||i);
i:=i+1;
EXIT WHEN i>=11;
END LOOP;

END;
/
Output:
Run SQL Command Line
SQL>set serveroutput on

SQL>start d://it.sql
i=0
i=1
i=2
i=3
i=4
i=5
i=6
i=7
i=8
i=9
i = 10
PL/SQL successfully completed.
WHILE loop
The while loop executes commands in its body as long as the condtion remains true
Syntax :

WHILE < condition >

LOOP
< Action >
END LOOP

Example :
find reverse of given number using while loop
DECLARE
num Number(3) :=123;
ans Number(3) :=0;
i Number(3) :=0;
BEGIN
WHILE num != 0
LOOP
i:=mod(num,10);
ans:=(ans * 10 ) + i;
num:=floor(num/10);
END LOOP;
dbms_output.put_line('reverse of given number is: ' || ans);
END;
/
Output :
Run SQL Command Line
SQL>set serveroutput on

SQL>start d://rev.sql

reverse of given number is: 321

PL/SQL successfully completed.
The FOR Loop
The FOR loop can be used when the number of iterations to be executed are known.
Syntax :
FOR variable IN [REVERSE] start..end
LOOP
< Action >
END LOOP;
The variable in the For Loop need not be declared. Also the increment value cannot be
specified. The For Loop variable is always incremented by 1.
Example :
DECLARE
i number ;
BEGIN
FOR i IN 1 .. 10
LOOP
dbms_output.put_line ('i = '||i);
END LOOP;
END;
/
Output :
Run SQL Command Line
SQL>set serveroutput on

SQL>start d://it.sql
i=1
i=2
i=3
i=4
i=5
i=6
i=7
i=8
i=9
i = 10
PL/SQL successfully completed.
Sequential Control :
The GOTO Statement
The GOTO statement changes the flow of control within a PL/SQL block. This statement
allows execution of a section of code, which is not in the normal flow of control. The entry
point into such a block of code is marked using the tags «userdefined name». The GOTO
statement can then make use of this user-defined name to jump into that block of code for
execution.
Syntax :
GOTO jump;
....
<<jump>>
Example :
DECLARE
BEGIN
dbms_output.put_line ('code starts');
dbms_output.put_line ('before GOTO statement');
GOTO down;
dbms_output.put_line ('statement will not get executed..');
<<down>>
dbms_output.put_line ('flow of execution jumped here.');
END;
/
Output :
Run SQL Command Line
SQL>set serveroutput on

SQL>start d://a.sql
code starts
before gotostatements
flow of execution jumped here.

PL/SQL successfully completed.