Database transaction
This presentation is the property of its rightful owner.
Sponsored Links
1 / 40

DATABASE TRANSACTION PowerPoint PPT Presentation


  • 107 Views
  • Uploaded on
  • Presentation posted in: General

DATABASE TRANSACTION. Transaction. It is a logical unit of work that must succeed or fail in its entirety. A transaction is an atomic operation which may not be divided into smaller operations. Transaction Properties.

Download Presentation

DATABASE TRANSACTION

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database transaction

DATABASE TRANSACTION


Transaction

Transaction

It is a logical unit of work that must succeed or fail in its entirety. A transaction is an atomic operation which may not be divided into smaller operations.


Transaction properties

Transaction Properties

  • ATOMICITY(ALL OR NONE CONCEPT) : This property ensures that either all operations of the transaction are reflected properly in the database or none are.

  • Consistency: This property implies that if the database was in consistent state before the start of transaction-execution, then upon termination of transaction, the database will also be in consistent state.


Transaction properties1

Transaction Properties

  • ISOLATION: This property implies that each transaction is unaware of other transactions executing concurrently in the System.

  • DURABLITY: This property of transaction ensures that after the successful completion of transaction, the changes made by it to the database persist, even if there are System failures.


Transaction control commands

Transaction Control Commands

  • BEGIN / START TRANSACTION : It marks the beginning of a transaction.

  • COMMIT: It ends the current transaction by saving database changes and start new transaction.

  • ROLLBACK: It ends the current transaction by discarding database changes and start new transaction.


Transaction control commands1

Transaction Control Commands

  • SAVEPOINT: It defines breakpoints for the transaction to allow partial rollbacks.

  • SET AUTOCOMMIT: It enables or disables the default commit mode.


Database transaction

SQL FUNCTIONS

AND

TABLE JOINS


Function

Function

Function is a special type of predefined commands that performs some operation and return a single value.

SQL Supports many and many Functions


Function sqrt x y e g x 16 then y 4

Function sqrtX Ye.gx=16 then y=4

sqrt


Types of sql function

Types of SQL Function

(1)Single Row Function: works with data of single row.

(2) Multiple Row or Group function: works with data of multiple rows.

Single Row function works with single data like sqrt function whereas multi row function work on more than one value like average function to find average of many numbers.


Multiple row or group function

Multiple Row or Group function


Table emp

TABLE: EMP


Avg function

AVG() function

This function computes the average of given data.

Suppose You want to find out the average of salary on EMP table then you can use AVG() function to do that

e.g.

SQL> SELECT AVG(empsal) “Average Salary” FROM EMP;


Avg function1

AVG() function

Output:-

Average Salary

------------------

15000


Count function

COUNT() function

This function computes the number of rows in a given column.

Suppose You want to find out the number of employees in EMP table then you can use count() function to do that

e.g.

SQL> SELECT count(empid) “Total” FROM EMP;


Count function1

COUNT() function

Output:-

Total

------------------

5


Max function

MAX() function

This function returns the maximum value for a given column.

Suppose You want to find out the highest amount of salary paid from EMP table then you can use MAX() function to do that


Max function1

MAX() function

Output:-

Maximum Salary

------------------

20000


Min function

MIN() function

This function returns the minimum value for a given column.

Suppose You want to find out the lowest amount of salary paid from EMP table then you can use MIN() function to do that

e.g.

SQL> SELECT MIN(empsal) “Maximum salary” FROM EMP;


Min function1

MIN() function

Output:-

Minimum Salary

------------------

10000


Sum function

SUM() function

This function computes the sum of values of a given column.

Suppose You want to find out the total salary paid to employees from EMP table then you can use SUM() function to do that

e.g.

SQL> SELECT SUM(empsal) “Total Salary” FROM EMP;


Sum function1

SUM() function

Output:-

Total Salary

------------------

60000


Grouping results

GROUPING RESULTS


Group by clause

GROUP BY clause

The GROUP BY clause combines all those records that have identical values in a particular column. E.g. To calculate the number of employees in each department. You can use the command.

SELECT dept, count(*) FROM emp GROUP BY dept;


Group by clause1

GROUP BY clause

  • Output:-


Having clause

HAVING Clause

The having clause places conditions on groups.

e.g. To calculate the number of employees in department no. 25. You can use the command.

SELECT dept, count(*) FROM emp GROUP BY dept HAVING dept = 25;


Having clause1

HAVING Clause

  • Output:-


Joins

Joins

A join is a query that combines two or more tables.

For example:

select * from emp, dept;

will give all the possible combination formed of all the rows of both the tables.


Equi join

Equi Join

The join in which columns are compared for equality is called equi-join.


Non equi join

Non-equi Join

It is a query that specifies some relationship other than equality between columns of tables.


Natural join

Natural Join

The Join in which only one of the identical column exist is called natural join.


Cross join

CROSS JOIN

It is a very basic type of join that simply matches each row from one table to every row from another table.


Left join

LEFT JOIN

It will return all rows from the first table whether there are matches in the second row or not. For unmatched rows of first table NULL is shown in second table.


Right join

RIGHT JOIN

It will return all rows from the second table whether there are matches in the first row or not. For unmatched rows of second table NULL is shown in first table.


Tables and integrity constraints

Tables and Integrity Constraints


Integrity constraint

Integrity Constraint

These are the rules that a database must comply at all times. Integrity constraint determine what all changes are permissible to database.


Database transaction

Valid database means consistent and correct data. Data is consistent if individual data items do not contradict one another. Data is correct if it satisfies all relevant constraints. MySQL maintains data integrity through constraints that are defined.


Alter table with constraints

Alter Table with constraints

Alter table command can be used for many things such as:

  • adding columns to table

  • Modifying column definition of a table

  • Adding constraints to table

  • Deleting columns of table

  • Enabling/disabling constraints


Viewing constraints and their columns

Viewing Constraints and their columns

To view all the information about how the table was created including its constraints, you need to write the following statement:

Show create table <tablename>;


Enabling disabling constraints

Enabling/Disabling Constraints

In MySQL, You cannot disable a Primary Key constraint, but you can disable or enable the foreign key.

To disable:

set foreign_key_checks = 0;

To enable:

set foreign_key_checks = 1;


  • Login