Practical No.

Aim: Study about distributed database system.

Theory:

A distributed database is a database in which storage devices are not all attached to a
common processor. It may be stored in multiple computers, located in the same physical location; or
may be dispersed over a network of interconnected computers. Unlike parallel systems, in which the
processors are tightly coupled and constitute a single database system, a distributed database system
consists of loosely coupled sites that share no physical components.
System administrators can distribute collections of data (e.g. in a database) across multiple physical
locations. A distributed database can reside on organized network servers or decentralized
independent computers on the Internet, on corporate intranets or extranets, or on other
organization networks. Because they store data across multiple computers, distributed databases
may improve performance at end-user worksites by allowing transactions to be processed on many
machines, instead of being limited to one.
Two processes ensure that the distributed databases remain up-to-date and
current: replication and duplication.

1.Replication involves using specialized software that looks for changes in the distributive database.
Once the changes have been identified, the replication process makes all the databases look the
same. The replication process can be complex and time-consuming depending on the size and
number of the distributed databases. This process can also require a lot of time and computer
resources.

2.Duplication, on the other hand, has less complexity. It basically identifies one database as
a master and then duplicates that database. The duplication process is normally done at a set time
after hours. This is to ensure that each distributed location has the same data. In the duplication
process, users may change only the master database. This ensures that local data will not be
overwritten.

Both replication and duplication can keep the data current in all distributive locations.
Besides distributed database replication and fragmentation, there are many other distributed
database design technologies. For example, local autonomy, synchronous and asynchronous
distributed database technologies. These technologies' implementations can and do depend on the
needs of the business and the sensitivity/confidentiality of the data stored in the database, and the
price the business is willing to spend on ensuring data security, consistency and integrity.
When discussing access to distributed databases, favors the term distributed query, which it defines
in protocol-specific manner as "any SELECT, INSERT, UPDATE, or DELETE statement that
references tables and row sets from one or more external OLE DB data sources". Oracle provides a
more language-centric view in which distributed queries and distributed transactions form part
of distributed SQL

Architecture
A database user accesses the distributed database through:
Local applications
Applications which do not require data from other sites.

Global applications
Applications which do require data from other sites.

A homogeneous distributed database has identical software and hardware running all databases
instances, and may appear through a single interface as if it were a single database.
A heterogeneous distributed database may have different hardware, operating systems, database
management systems, and even data models for different databases.
Advantages

 Management of distributed data with different levels of transparency like network

transparency, fragmentation transparency, replication transparency, etc.

 Increase reliability and availability

 Easier expansion
 Reflects organizational structure — database fragments potentially stored within the
departments they relate to
 Local autonomy or site autonomy — a department can control the data about them (as they
are the ones familiar with it)
 Protection of valuable data — if there were ever a catastrophic event such as a fire, all of the
data would not be in one place, but distributed in multiple locations
 Improved performance — data is located near the site of greatest demand, and the database
systems themselves are parallelized, allowing load on the databases to be balanced among
servers. (A high load on one module of the database won't affect other modules of the
database in a distributed database)
 Economics — it may cost less to create a network of smaller computers with the power of a
single large computer
 Modularity — systems can be modified, added and removed from the distributed database
without affecting other modules (systems)
 Reliable transactions - due to replication of the database
 Hardware, operating-system, network, fragmentation, DBMS, replication and location
independence
 Continuous operation, even if some nodes go offline (depending on design)
 Distributed query processing can improve performance
 Single-site failure does not affect performance of system.
 For those systems that support full distributed transactions, operations enjoy the ACID
properties:
 A-atomicity, the transaction takes place as a whole or not at all
 C-consistency, maps one consistent DB state to another
 I-isolation, each transaction sees a consistent DB
 D-durability, the results of a transaction must survive system failures
 Management of distributed data with different levels of transparency like network
transparency, fragmentation transparency, replication transparency, etc.

 Increase reliability and availability

 Easier expansion
 Reflects organizational structure — database fragments potentially stored within the
departments they relate to
 Local autonomy or site autonomy — a department can control the data about them (as they
are the ones familiar with it)
 Protection of valuable data — if there were ever a catastrophic event such as a fire, all of the
data would not be in one place, but distributed in multiple locations
 Improved performance — data is located near the site of greatest demand, and the database
systems themselves are parallelized, allowing load on the databases to be balanced among
servers. (A high load on one module of the database won't affect other modules of the
database in a distributed database)
 Economics — it may cost less to create a network of smaller computers with the power of a
single large computer
 Modularity — systems can be modified, added and removed from the distributed database
without affecting other modules (systems)
 Reliable transactions - due to replication of the database
 Hardware, operating-system, network, fragmentation, DBMS, replication and location
independence
 Continuous operation, even if some nodes go offline (depending on design)
 Distributed query processing can improve performance
 Single-site failure does not affect performance of system.
 For those systems that support full distributed transactions, operations enjoy
the ACID properties:
 A-atomicity, the transaction takes place as a whole or not at all
 C-consistency, maps one consistent DB state to another
 I-isolation, each transaction sees a consistent DB
 D-durability, the results of a transaction must survive system failures
Disadvantages

 Complexity — DBAs may have to do extra work to ensure that the distributed nature of the
system is transparent. Extra work must also be done to maintain multiple disparate systems,
instead of one big one. Extra database design work must also be done to account for the
disconnected nature of the database — for example, joins become prohibitively expensive
when performed across multiple systems.
 Economics — increased complexity and a more extensive infrastructure means extra labour
costs
 Security — remote database fragments must be secured, and they are not centralized so the
remote sites must be secured as well. The infrastructure must also be secured (for example,
by encrypting the network links between remote sites).
 Difficult to maintain integrity — but in a distributed database, enforcing integrity over a
network may require too much of the network's resources to be feasible
 Inexperience — distributed databases are difficult to work with, and in such a young field
there is not much readily available experience in "proper" practice
 Lack of standards — there are no tools or methodologies yet to help users convert a
centralized DBMS into a distributed DBMS[citation needed]
 Database design more complex — In addition to traditional database design challenges, the
design of a distributed database has to consider fragmentation of data, allocation of
fragments to specific sites and data replication
 Additional software is required
 Operating system should support distributed environment
 Concurrency control poses a major issue. It can be solved by locking and timestamping.
 Distributed access to data
 Analysis of distributed data

Conclusion: Thus we have learned about distributed database system, what is architecture and what
are the advantages and disadvantages.
 Practical No. 2

Aim: Implement DCL commands, autherization, authenticacation, privilages on database.

Software Used: MYSQL, wamp server.

Theory:

A data control language (DCL) is syntax similar to a computer programming language used to
control access to data stored in a database (Authorization). In particular, it is a component
of Structured Query Language (SQL).
Examples of DCL commands include:

 GRANT to allow specified users to perform specified tasks.

 REVOKE to cancel previously granted or denied permissions.

The operations for which privileges may be granted to or revoked from a user or role apply to both
the Data definition language (DDL) and the Data manipulation language (DML), and may
include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, and USAGE.
In the Oracle database, executing a DCL command issues an implicit commit. Hence you cannot
roll back the command.
In PostgreSQL, executing DCL is transactional, and can be rolled back.
SQLite does not have any DCL commands as it does not have usernames or logins.
Instead, SQLite depends on file system permissions to define who can open and access a database.

Query:

For Grant
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO public;
GRANT ALL ON employees TO public;

For Revoke
REVOKE DELETE ON employees FROM anderson; REVOKE ALL ON employees FROM
anderson;

Conclusion: Thus we have grant the permission using DCL commands successfully.
 Practical No. 3

Aim: Perform queries to create synonyms, sequence and index.

Software Used: MySQL, wamp server.

Theory:

SEQUENCE

Sequences are database objects from which multiple users can generate unique integers. The
sequence generator generates sequential numbers, which can help to generate unique primary keys
automatically, and to coordinate keys across multiple rows or tables .

Without sequences, sequential values can only be produced programmatically. A new primary key
value can be obtained by selecting the most recently produced value and incrementing it. This
method requires a lock during the transaction and causes multiple users to wait for the next value of
the primary key; this waiting is known as serialization. If developers have such constructs in
applications, then you should encourage the developers to replace them with access to sequences.
Sequences eliminate serialization and improve the concurrency of an application.

To create a sequence in your schema, you must have the CREATE SEQUENCE system privilege.
To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE
privilege. Create a sequence using the CREATE SEQUENCE statement.

CREATE SEQUENCE emp_sequence

INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

INSERT INTO Orders_tab (Orderno, Custno) // using sequence

VALUES (Order_seq.NEXTVAL, 1032);

SYNONYMS

A synonym is an alias for a schema object. Synonyms can provide a level of security by masking
the name and owner of an object and by providing location transparency for remote objects of a
distributed database. Also, they are convenient to use and reduce the complexity of SQL statements
for database users. Synonyms allow underlying objects to be renamed or moved, where only the
synonym needs to be redefined and applications based on the synonym continue to function without
modification.

You can create both public and private synonyms. A public synonym is owned by the special user
group named PUBLIC and is accessible to every user in a database. A private synonym is contained
in the schema of a specific user and available only to the user and the user's grantees.

To create a private synonym in your own schema, you must have the CREATE SYNONYM
privilege. To create a private synonym in another user's schema, you must have the CREATE ANY
SYNONYM privilege. To create a public synonym, you must have the CREATE PUBLIC
SYNONYM system privilege. Create a synonym using the CREATE SYNONYM statement.

You can successfully use any private synonym contained in your schema or any public synonym,
assuming that you have the necessary privileges to access the underlying object, either explicitly,
from an enabled role, or from PUBLIC. You can also reference any private synonym contained in
another schema if you have been granted the necessary object privileges for the private synonym.

CREATE PUBLIC SYNONYM public_emp FOR jward.emp

INSERT INTO emp (empno, ename, job)

VALUES (emp_sequence.NEXTVAL, 'SMITH', 'CLERK');

EXECUTE Fire_emp(7344);

INDEX

“An index makes the query fast”. An index is a distinct structure in the database that is built using
the create index statement. It requires its own disk space and holds a copy of the indexed table data.
That means that an index is pure redundancy. Creating an index does not change the table data; it
just creates a new data structure that refers to the table. A database index is, after all, very much like
the index at the end of a book: it occupies its own space, it is highly redundant, and it refers to the
actual information stored in a different place.

Searching in a database index is like searching in a printed telephone directory. The key concept is
that all entries are arranged in a well-defined order. Finding data in an ordered data set is fast and
easy because the sort order determines each entry's position.

A database index is, however, more complex than a printed directory because it undergoes constant
change. Updating a printed directory for every change is impossible for the simple reason that there
is no space between existing entries to add new ones. A printed directory bypasses this problem by
only handling the accumulated updates with the next printing. An SQL database cannot wait that
long. It must process insert, delete and update statements immediately, keeping the index order
without moving large amounts of data.

The database combines two data structures to meet the challenge: a doubly linked list and a search
tree. These two structures explain most of the database's performance characteristics.

Syntax:

CREATE INDEX index_name

ON table_name (column1, column2, ...);
Query:

The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the
"Persons" table:

CREATE INDEX idx_lastname

ON Persons (LastName);

Conclusion: Thus we have performed queries for indexing and sequencing.

 Practical No. 4

Aim: Perform queries to create, alter, update view.

Software Used: MYSQL, wamp server.

Theory:
SQL View
A view in SQL is a logical subset of data from one or more tables. View is used to restrict data
access.Syntax for creating a View,

CREATE or REPLACE view view_name AS

SELECT column_name(s)
FROM table_nameWHERE condition

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or
more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the
data were coming from one single table.

CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1,column2, ...
FROM table_name
WHERE condition;

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Query:

Mysql> create view e1 as select id, name, department from employee;

Mysql> update e1 set department=”development” where name=”kalpa”;
Mysql> alter view e1 (id, name) as select id, name from employee;

Output:

Conclusion: Thus we have learned the concept of view in sql and perform view queries for create
alter and update successfully.
 Practical No. 5

Aim: Display system date in various format.

Software Used: MySQL, wamp server.

Theory:

Most applications store and manipulate dates and times. Dates are quite complicated: not only are
they highly formatted, but there are myriad rules for determining valid values and valid calculations
(leap days and years, national and company holidays, date ranges, etc.). Fortunately, PL/SQL and
the Oracle RDBMS provide many ways to handle date information.
PL/SQL provides a true DATE datatype that stores both date and time information. Each date value
contains the century, year, month, day, hour, minute, and second. The DATE datatype does not
support the storage of fractions of time less than a second in length. The time itself is stored as the
number of seconds past midnight. If you enter a date without a time (most applications do not
require the tracking of time), the time portion of the database value defaults to midnight (12:00:00
AM). PL/SQL validates and stores dates which fall in the range January 1, 4712 B.C. to December
31, 4712 A.D (in Oracle Server 8.0 and higher, the maximum valid date is December 31, 9999).
Support for a true date datatype is only half the battle. You also need a language that can manipulate
those dates in a natural and intelligent manner -- as dates. PL/SQL offers a set of eight date
functions for just this purpose.
With PL/SQL you will never have to write a program which calculates the number of days between
two dates. You will not need to write your own utility to figure out the day of the week on which a
date falls. This information, and just about anything else you can think of having to do with dates, is
immediately available to you through built-in functions. The date functions in PL/SQL all take
dates, and, in some cases, numbers, for arguments, and all return date values.
A date contained in single quotation marks is a character string. PL/SQL converts the string to a true
date data type when it applies the function. (This is an implicit conversion.) Date values that are
displayed in the format DD-MON-YYYY and are not contained in single quotation marks represent
actual date values in the database.

So to denote the system date:

1. In MySQL
CURDATE() returns the current date.
2. In sql server
The GETDATE() function returns the current date and time from the SQL Server.
3. In oracle
The SYSDATE function returns the current system date and time as recorded in the database. The
time component of SYSDATE provides the current time to the nearest second. It takes no
arguments.

MySql date format function():

selectdate_format(date, '%a %D %b %Y')
asformatted_date
fromtable_name;
Where date is the name of your date field, and formatted_date is a column alias which you can use
as a column heading.
date_format String Example

'%Y-%m-%d' 2011-02-11

'%e/%c/%Y' 11/2/2011 UK

'%c/%e/%Y' 2/11/2011 US

'%d/%m/%Y' 11/02/2011 UK

'%m/%d/%Y' 02/11/2011 US

'%e/%c/%Y %H:%i' 11/2/2011 12:30 UK

'%c/%e/%Y %H:%i' 2/11/2011 12:30 US

'%d/%m/%Y %H:%i' 11/02/2011 12:30 UK

'%m/%d/%Y %H:%i' 02/11/2011 12:30 US

'%e/%c/%Y %T' 11/2/2011 12:30:10 UK

'%c/%e/%Y %T' 2/11/2011 12:30:10 US

'%d/%m/%Y %T' 11/02/2011 12:30:10 UK

'%m/%d/%Y %T' 02/11/2011 12:30:10 US

'%a %D %b %Y' Fri 11th Feb 2011

'%a %D %b %Y %H:%i' Fri 11th Feb 2011 12:30

'%a %D %b %Y %T' Fri 11th Feb 2011 12:30:10

'%a %b %e %Y' Fri Feb 11 2011

'%a %b %e %Y %H:%i' Fri Feb 11 2011 12:30

'%a %b %e %Y %T' Fri Feb 11 2011 12:30:10

'%W %D %M %Y' Friday 11th February 2011

'%W %D %M %Y %H:%i' Friday 11th February 2011 12:30

'%W %D %M %Y %T' Friday 11th February 2011 12:30:10

'%l:%i %p %b %e, %Y' 12:30 PM Feb 11, 2011

'%M %e, %Y' February 11, 2011

The following statement will format the specified datetime 2008-05-15 22:23:00 according to the
format specifier %W %D %M %Y. Here date has been formatted with week day name, day of the
month with english suffix, month name and year in numeric.

Query:

1. In MySQL
The following SELECT statement:
SELECT NOW(),CURDATE(),CURTIME()
In this the date format is “YYYY-MM-DD”

2. In oracle
The following SELECT statement:
Select sysdate from dual
In this the date format is “DD-MMM-YY”

SELECT DATE_FORMAT('2008-05-15 22:23:00', '%W %D %M %Y');

+---------------------------------------------------+
| DATE_FORMAT('2008-05-15 22:23:00', '%W %D %M %Y') |
+---------------------------------------------------+
| Thursday 15th May 2008 |
+---------------------------------------------------+
1 row in set (0.01 sec)

Conclusion: Hence the system date was displayed and its format was noted.
 Practical No. 6

Aim: Perform queries about sql join clause and its type.

Software Used: MySQl, wamp server.

Theory:

SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

 (INNER) JOIN: Returns records that have matching values in both tables
 LEFT (OUTER) JOIN: Return all records from the left table, and the matched
records from the right table
 RIGHT (OUTER) JOIN: Return all records from the right table, and the matched
records from the left table
 FULL (OUTER) JOIN: Return all records when there is a match in either left or right
table
 SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

 SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matched
records from the right table (table2). The result is NULL from the right side, if there is no
match.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

 SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched
records from the left table (table1). The result is NULL from the left side, when there is no
match.
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

 SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword return all records when there is a match in either left
(table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!

FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
  SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.
Self JOIN Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Query:

Mysql> select e.name from employee e inner join worker w on e.department=w.department;

Mysql> select e.name from employee e left join worker w on e.department=w.department;
Mysql> select e.name from employee e right join worker w on e.department=w.department;

Output:
Conclusion: Thus we have learned various types of joins and perform join operations on two tables
employee and worker.
 Practical No. 7

Aim: Perform queries about various function like min, max, count, avg, and sum.

Software Used: MySQL, wamp server.

Theory:

SQL has many built-in functions for performing calculations on data.

SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Function Description
AVG() Returns the average value
COUNT() Returns the number of rows
FIRST() Returns the first value
LAST() Returns the last value
MAX() Returns the largest value
MIN() Returns the smallest value
ROUND() Rounds a numeric field to the number of decimals specified
SUM() Returns the sum

 The SQL COUNT(), AVG() and SUM() Functions

The COUNT() function returns the number of rows that matches a specified criteria.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.

COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
  The SQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.

MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;

Query:

for count: select count(department) from employee where department=”development”;

for sum: select sum(salary) from employee where department=”testing”;
for average: select avg(salary) from employee where department=”testing”;

Output:

Conclusion: Thus we have learned aggregation functions and perform successfully.

 Practical No. 8

Aim: Implement Trigger in database.

Software Used: MySQl, wamp server.

Theory:

A trigger is a special kind of stored procedure that automatically executes when an event occurs in
the database server. DML triggers execute when a user tries to modify data through a data
manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements
on a table or view.
DDL triggers execute in response to a variety of data definition language (DDL) events. These
events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain
system stored procedures that perform DDL-like operations. Logon triggers fire in response to the
LOGON event that is raised when a user sessions is being established. Triggers can be created
directly from Transact-SQL statements or from methods of assemblies that are created in the
Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL
Server . SQL Server allows for creating multiple triggers for any specific statement.
MySQL trigger syntax

In order to create a new trigger, you use the CREATE TRIGGER statement. The following
illustrates the syntax of the CREATE TRIGGER statement

CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name
FOR EACH ROW
BEGIN
...
END;

 You put the trigger name after the CREATE TRIGGER statement. The trigger name should
follow the naming convention [trigger time]_[table name]_[trigger event], for
example before_employees_update.

 Trigger activation time can be BEFORE or AFTER. You must specify the activation time
when you define a trigger. You use the BEFORE keyword if you want to process action
prior to the change is made on the table and AFTER if you need to process action after the
change is made.

 The trigger event can be INSERT, UPDATE or DELETE. This event causes the trigger to be
invoked. A trigger only can be invoked by one event. To define a trigger that is invoked by
multiple events, you have to define multiple triggers, one for each event.
  A trigger must be associated with a specific table. Without a table trigger would not exist
therefore you have to specify the table name after the ON keyword.

 You place the SQL statements between BEGIN and END block. This is where you define
the logic for the trigger.

Query:

DELIMITER $$

CREATE TRIGGER `message_read_trigger` AFTER DELETE ON `communication`

FOR EACH ROW begin
insert into messagereadhistory (SenderName,ReceiverName,Message) values
(old.SenderName,old.ReceiverName,old.Message);
end $$

DELIMITER ;

Conclusion: Thus we have performed trigger on messageerahistory to insert on deleting

communication.