- 153 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about ' DATABASE TRANSACTION' - christine-burt

**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

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

- 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 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

- 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 Commands

- SAVEPOINT: It defines breakpoints for the transaction to allow partial rollbacks.
- SET AUTOCOMMIT: It enables or disables the default commit mode.

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 sqrtX Y e.gx=16 then y=4

sqrt

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.

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;

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;

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

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;

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;

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 clause

- Output:-

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 Clause

- Output:-

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

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

Non-equi Join

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

Natural Join

The Join in which only one of the identical column exist is called natural 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

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

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.

Integrity Constraint

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

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

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

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;

Download Presentation

Connecting to Server..