Distributed databases

A distributed database is a collection of multiple interconnected databases,

which are spread physically across various locations that communicate via a
computer network.
• Databases in the collection are logically interrelated with each other. Often
they represent a single logical database.
• Data is physically stored across multiple sites. Data in each site can be
managed by a DBMS independent of the other sites.
• The processors in the sites are connected via a network. They do not have
any multiprocessor configuration.
• A distributed database is not a loosely connected file system.
• A distributed database incorporates transaction processing, but it is not
synonymous with a transaction processing system.

Distributed Database Management System

A distributed database management system (DDBMS) is a centralized software
system that manages a distributed database in a manner as if it were all stored in a
single location.
Features
• It is used to create, retrieve, update and delete distributed databases.
• It synchronizes the database periodically and provides access mechanisms
by the virtue of which the distribution becomes transparent to the users.
• It ensures that the data modified at any site is universally updated.
• It is used in application areas where large volumes of data are processed
and accessed by numerous users simultaneously.
• It is designed for heterogeneous database platforms.
• It maintains confidentiality and data integrity of the databases.

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 of Distributed Database

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.

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.

• Distributed database systems can be used in a

variety of applications, including e- commerce,

financial services, and telecommunications.

Architecture of Distributed DBMS

There are several different architectures for distributed
database systems, including:
Client-server architecture: In this architecture, clients
connect to a central server, which manages the distributed
database system. The server is responsible for coordinating
transactions, managing data storage, and providing access
control.

Peer-to-peer architecture: In this architecture, each site in the

distributed database system is connected to all other sites. Each
site is responsible for managing its own data and coordinating
transactions with other sites.

Federated architecture: In this architecture, each site in the

distributed database system maintains its own independent
database, but the databases are integrated through a middleware
layer that provides a common interface for accessing and
querying the data.

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 possess 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 require 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.

Database links
The central concept in distributed database systems is a database
link. A database link is a connection between two physical
database servers that allows a client to access them as one logical
database.
What Are Database Links?
A database link is a pointer that defines a one-way communication path from
an Oracle Database server to another database server. The link pointer is
actually defined as an entry in a data dictionary table. To access the link, you
must be connected to the local database that contains the data dictionary
entry.

A database link connection is one-way in the sense that a client connected to

local database A can use a link stored in database A to access information in
remote database B, but users connected to database B cannot use the same
link to access data in database A. If local users on database B want to access
data on database A, then they must define a link that is stored in the data
dictionary of database B.

A database link connection allows local users to access data on a remote database.
For this connection to occur, each database in the distributed system must have a
unique global database name in the network domain. The global database name
uniquely identifies a database server in a distributed system.

Figure Database Link

Database links are either private or public. If they are private, then only the user who created the
link has access; if they are public, then all database users have access.

Type of
Link Description

Connected Users connect as themselves, which means that they must have an
user link account on the remote database with the same user name and
password as their account on the local database.

Fixed user Users connect using the user name and password referenced in the
link link. For example, if Jane uses a fixed user link that connects to
the hq database with the user name and password scott/password,
then she connects as scott, Jane has all the privileges in hq granted
to scott directly, and all the default roles that scott has been granted
in the hq database.
 Type of
Link Description

Current A user connects as a global user. A local user can connect as a

user link global user in the context of a stored procedure, without storing the
global user's password in a link definition. For example, Jane can
access a procedure that Scott wrote, accessing Scott's account and
Scott's schema on the hq database. Current user links are an
aspect of Oracle Advanced Security.

SQL Snapshots

SQL snapshots are a recent read-only copy of the table from the database or a
subset of rows/columns of a table. The SQL statement that creates and subsequently
maintains a snapshot normally reads data from the source database server.

A snapshot is created on the destination system with the CREATE

SNAPSHOT SQL command. The remote table is immediately defined and
populated from the master table. These are used in data replication, reporting,
maintaining historical data, and dynamically replicating data between distributed
databases.

Create Snapshot in SQL

The CREATE SNAPSHOT syntax is used to create a snapshot in SQL.

To create a simple snapshot based on a single table or a straightforward SELECT query from a
single table, you can use the following syntax:

CREATE SNAPSHOT snapshot_name AS

SELECT * FROM your_source_table;
Types of SQL Snapshots

There are two types of snapshots available in SQL

1. Simple snapshots

2. Complex snapshots

Example of Simple Snapshot:

CREATE SNAPSHOT emp_snap

AS SELECT * FROM emp;

Complex snapshot

CREATE SNAPSHOT sampleSnps1

AS SELECT student.rollno, student.name

FROM student

UNION ALL

SELECT new_student.rollno, new_student.name

FROM new_student;
Advantages of Snapshots in SQL

• Response time is improved when local read-only copy of table exists.

• Once snapshot is built on remote database, if node containing data from

which the snapshot is built is not available. Snapshot can be used without
need to access the unavailable database.

• Ease network loads.

• Data subsetting.

• Disconnected computing.

• Mass deployment.

Disadvantages of Snapshots in SQL

• Snapshots are not reachable when primary database goes offline.

• It does not support full text indexing.

• Snapshot runs out of disk if data changes frequently faster.

• As number of snapshots increases, disk space becomes problematic.

Applications of Snapshots

• Protects data.

• Maintains history of data.

• Used in testing application software.

• Used in data mining.

• Recovers data when information is lost because of human error or corruption

of data.

Data dictionary
A Data Dictionary comprises two words i.e. Data which simply means
information being collected through some sources and Dictionary means where
this information is available.

A Data Dictionary can be defined as a collection of information on all data

elements or contents of databases such as data types, and text descriptions of the
system. It makes it easier for users and analysts to use data as well as understand
and have common knowledge about inputs, outputs, components of a database,
and intermediate calculations.

Why Data Dictionary is Essential?

There is less information and details provided by data models. So, a

data dictionary is essential and needed to have proper knowledge and
usage of contents. Data Dictionary provides all information about
names that are used in system models. Data Dictionary also provides
information about entities, relationships, and attributes that are present
in the system model. As a part of the structured analysis and design
tool, the implementation of a data dictionary is done.

The following data name type of Information is used to store in a data

dictionary:
 Name Description

Name generally includes the primary name of all composite data

Name or control items available, and the name of the external entity or
data store.

Aliases Any other word used in place of Name

A data dictionary generally gives information about where and

Where or How
how data or control items are used which may include an input/
it’s used?
output to process.

Description A notation for representing content

Types of Data Dictionary

Data Dictionary is basically of two types. We will discuss each of them.

• Integrated Data Dictionary
• Stand Alone Data Dictionary
1. Integrated Data Dictionary
Integrated Data Dictionary can be seen as a catalog that can be
maintained by the relational database. In previous databases, there is not
any functionality of Integrated Data Dictionary, so they use Stand Alone
Data Dictionary.
There are two types of Integrated Data Dictionary.
• Active Data Dictionary: Active Database Dictionary is a type of
database that is updated automatically in case any changes are to be
done to the database. These are self-updating.
• Passive Data Dictionary: Passive Databases are the databases that
have to be maintained or updated manually in case of any changes
have been made to the system.
2. Stand Alone Data Dictionary
Stand Alone Data Dictionary is a type of flexible data dictionary as
Database Administrator has ease of managing data. It does not require
data that is computer-based. It has no fixed format. But some elements
are common in this kind of database.
• Data Elements: It has the elements like name, datatype, validation
rules, etc.
• Tables: These contain all the necessary information that is required for
the table, how many rows in the table, how many columns in the table,
etc.
• Index: The index of the databases is to be stored here.
• Programs: These are used for accessing the database, and can
include SQL Queries, Reports, etc.
• Relationship between Data Elements: This stores the relationship
among the different databases, like cardinality, connectivity, etc .

How to Create a Data Dictionary?

Data Dictionary can be formed with the help of the following relational
database like MySQL, SQL Server, etc. Several notations are present
everywhere which enhances the quality of the Data Dictionary. We will
also look into that. Database Administrators can have the following
templates like SQL Server, that help in making Data Dictionary.

Notations in Data Dictionary

Data Construct Notation Meaning

Composition = Is composed of
Sequence + Represents AND
Selection [ |] Represents OR
Repetition { }n Represents n repetitions or repetition for n times
Parentheses () Optional data that may or may not be present
Comment *…* Delimits a comment or commented information

Example: Let us understand this by taking an example of the reservation system.

In the reservation system, “passenger” is a data item whose information is
available on the data dictionary as follows:

Keys Values
Name Passenger

Aliases None
 Keys Values
Where or how it’s
Passenger’s query (input) Ticket (output)
used?

• Passenger = Passenger_name + Passenger_address

• Passenger_name = Passenger_lastname + Passenger_firstname +
Passenger_middle_initial
• Passenger_address = Local_address + Community_address +
Description
Zip_code
• Local_address = House_number + street_name +
Apartment_number
• Community_Address = City_name + State_name

Challenges with Data Dictionary

• The main challenge that occurs in front of us is that a data dictionary is

somehow difficult and it may lead to take much time in case when data is
not prepared previously.
• Data Preparation is a bit lengthy and hectic process for a large scale of
data.
• When you don’t do Data Preparation, Data Dictionary is not that much
efficient.
• It can be an expensive process when resources are not to be utilized
efficiently.
• Data Dictionary can be an insecure process as if you give access to so many
persons, it might be a challenge to the security of the Data Dictionary.
EQUI JOIN :

EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables. EQUI
JOIN also create JOIN by using JOIN with ON and then providing the names of the columns with
their relative tables to check equality using equal sign (=).

Syntax :
SELECT column_list
FROM table1
JOIN table2
[ON (join_condition)]

id name class city

3 Hina 3 Delhi

4 Megha 2 Delhi

6 Gouri 2 Delhi

id class city
9 3 Delhi

10 2 Delhi

12 2 Delhi
SELECT student.name, student.id, record.class, record.city
FROM
student
JOIN
record
ON student.city = record.city;
name id classcity
Hina 3 3 Delhi

Megha 4 3 Delhi

Gouri 6 3 Delhi

Hina 3 2 Delhi

Megha 4 2 Delhi

Gouri 6 2 Delhi

Hina 3 2 Delhi

Megha 4 2 Delhi

Gouri 6 2 Delhi

NON EQUI JOIN :

NON EQUI JOIN performs a JOIN using comparison operator other than equal(=)
sign like >, <, >=, <= with conditions.
Syntax:

SELECT *
FROM table_name1, table_name2
WHERE table_name1.column [> | < | >= | <= ] table_name2.column;

SELECT student.name, record.id, record.city

FROM student, record
WHERE Student.id < Record.id ;

name id city
name id city
Hina 9 Delhi

Megha 9 Delhi

Gouri 9 Delhi

Hina 10 Delhi

Megha 10 Delhi

Gouri 10 Delhi

Hina 12 Delhi

Megha 12 Delhi

Gouri 12 Delhi
OUTPUT

The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:
 • The percent sign % represents zero, one, or multiple characters
• The underscore sign _ represents one, single character
• SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

Return all customers from a city that starts with 'L' followed by one wildcard character, then 'nE' and then
two wildcard characters:
SELECT * FROM Customers
WHERE city LIKE 'L_nE__';
Return all customers from a city that contains the letter 'L':
SELECT * FROM Customers
WHERE city LIKE '%L%';

Return all customers that starts with 'La':

SELECT * FROM Customers

WHERE CustomerName LIKE 'La%';

Return all customers that starts with 'a' or starts with 'b':

SELECT * FROM Customers

WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b%';
Return all customers that contains the phrase 'or'
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
Return all customers that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

Return all customers from Spain:

SELECT * FROM Customers

WHERE Country LIKE 'Spain';

The SQL ANY Operator

The ANY operator:

• returns a boolean value as a result

• returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails
table has Quantity equal to 10 (this will return TRUE because the Quantity column has some
values of 10):
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);

The following SQL statement lists the ProductName if it finds ANY records in
the OrderDetails table has Quantity larger than 99 (this will return TRUE
because the Quantity column has some values larger than 99):

SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 99);
The SQL ALL Operator

The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to
10. This will of course return FALSE because the Quantity column has many different values (not only the value of 10):

SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);

The SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID =
Suppliers.supplierID AND Price < 20);

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.Suppli
erID = Suppliers.supplierID AND Price = 22);
The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Return all customers from 'Germany', 'France', or 'UK'

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

Return all customers that have an order in the Orders table:

SELECT * FROM Customers

WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Return all customers that have NOT placed any orders in the Orders table:

SELECT * FROM Customers

WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);