Question

Please, No handwriting

COURSE; introduction to database
Define and explain the following with example:
1. Outer Joins
2. Views
3. Transactions
4. Audit Trails

Answer 1

(1)SQL OUTER JOIN:

In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not.

If you take an example of employee table

Outer join of two types:

1.Left outer join (also known as left join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no matching in the right table it returns NULL values.

2.Right outer join (also known as right join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.

This diagram shows the different type of joins:

SQL LEFT JOIN

The SQL left join returns all the values from the left table and it also includes matching values from right table, if there are no matching join value it returns NULL.

BASIC SYNTAX FOR LEFT JOIN:

1. SELECT table1.column1, table2.column2....  
2. FROM table1   
3. LEFTJOIN table2  
4. ON table1.column_field = table2.column_field;  

let us take two tables in this example to elaborate all the things:

CUSTOMER TABLE:

ID	NAME	AGE	SALARY
1	ARYAN	51	56000
2	AROHI	21	25000
3	VINEET	24	31000
4	AJEET	23	32000
5	RAVI	23	42000

This is second table

ORDER TABLE:

O_ID	DATE	CUSTOMER_ID	AMOUNT
001	20-01-2012	2	3000
002	12-02-2012	2	2000
003	22-03-2012	3	4000
004	11-04-2012	4	5000

join these two tables with LEFT JOIN:

1. SQL SELECT ID, NAME, AMOUNT,DATE  
2. FROM CUSTOMER  
3. LEFT JOIN ORDER  
4. ON CUSTOMER.ID = ORDER.CUSTOMER_ID;  

This will produce the following result:

ID	NAME	AMOUNT	DATE
1	ARYAN	NULL	NULL
2	AROHI	3000	20-01-2012
2	AROHI	2000	12-02-2012
3	VINEET	4000	22-03-2012
4	AJEET	5000	11-04-2012
5	RAVI	NULL	NULL

SQL RIGHT JOIN

The SQL right join returns all the values from the rows of right table. It also includes the matched values from left table but if there is no matching in both tables, it returns NULL.

Basic syntax for right join:

1. SELECT table1.column1, table2.column2.....  
2. FROM table1   
3. RIGHT JOIN table2  
4. ON table1.column_field = table2.column_field;  

let us take an example with 2 tables table1 is CUSTOMERS table and table2 is ORDERS table.

CUSTOMER TABLE:

ID	NAME	AGE	SALARY
1	ARYAN	51	56000
2	AROHI	21	25000
3	VINEET	24	31000
4	AJEET	23	32000
5	RAVI	23	42000

and this is the second table:

ORDER TABLE:

DATE	O_ID	CUSTOMER_ID	AMOUNT
20-01-2012	001	2	3000
12-02-2012	002	2	2000
22-03-2012	003	3	4000
11-04-2012	004	4	5000

Here we will join these two tables with SQL RIGHT JOIN:

1. SQL> SELECT ID,NAME,AMOUNT,DATE  
2. FROM CUSTOMER  
3. RIGHT JOIN ORDER  
4. ON CUSTOMER.ID = ORDER.CUSTOMER_ID;  

ID	NAME	AMOUNT	DATE
2	AROHI	3000	20-01-2012
2	AROHI	2000	12-02-2012
3	VINEET	4000	22-03-2012
4	AJEET	5000	11-04-2012

SQL FULL JOIN

The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found.

SQL full outer join and SQL join are same. generally it is known as SQL FULL JOIN.

SQL full outer join:

What is SQL full outer join?

SQL full outer join is used to combine the result of both left and right outer join and returns all rows (don?t care its matched or unmatched) from the both participating tables.

Syntax for full outer join:

1. SELECT *  
2. FROM table1  
3. FULL OUTER JOIN table2  
4. ON table1.column_name = table2.column_name;  

Note:here table1 and table2 are the name of the tables participating in joining and column_name is the column of the participating tables.

Let us take two tables to demonstrate full outer join:

table_A

A	M
1	m
2	n
4	o

table_B

A	N
2	p
3	q
5	r

Resulting table

A	M	A	N
2	n	2	p
1	m	-	-
4	o	-	-
-	-	3	q
-	-	5	r

Because this is a full outer join so all rows (both matching and non-matching) from both tables are included in the output. Here only one row of output displays values in all columns because there is only one match between table_A and table_B.

(2)SQL Views

A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

The Syntax to create a sql view is

CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition];

• view_name is the name of the VIEW.
• The SELECT statement is used to define the columns and rows that you want to display in the view.

For Example: to create a view on the product table the sql query would be like

CREATE VIEW view_product
AS
SELECT product_id, product_name
FROM product;

(3)Transactions:

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Properties of Transactions:

Transactions have the following four standard properties, usually referred to by the acronym ACID.

• Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.

• Consistency − ensures that the database properly changes states upon a successfully committed transaction.

• Isolation − enables transactions to operate independently of and transparent to each other.

• Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control:

The following commands are used to control transactions.

• COMMIT − to save the changes.

• ROLLBACK − to roll back the changes.

• SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.

• SET TRANSACTION − Places a name on a transaction.

Transactional Control Commands

Transactional control commands are only used with the DML Commands such as - INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

The COMMIT Command

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

The syntax for the COMMIT command is as follows.

COMMIT;

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example which would delete those records from the table which have age = 25 and then COMMIT the changes in the database.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> COMMIT;

Thus, two rows from the table would be deleted and the SELECT statement would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

The syntax for a ROLLBACK command is as follows −

ROLLBACK;

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would delete those records from the table which have the age = 25 and then ROLLBACK the changes in the database.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> ROLLBACK;

Thus, the delete operation would not impact the table and the SELECT statement would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The SAVEPOINT Command

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

The syntax for a SAVEPOINT command is as shown below.

SAVEPOINT SAVEPOINT_NAME;

This command serves only in the creation of a SAVEPOINT among all the transactional statements. The ROLLBACK command is used to undo a group of transactions.

The syntax for rolling back to a SAVEPOINT is as shown below.

ROLLBACK TO SAVEPOINT_NAME;

Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

Example

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code block contains the series of operations.

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone −

SQL> ROLLBACK TO SP2;
Rollback complete.

Notice that only the first deletion took place since you rolled back to SP2.

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

The RELEASE SAVEPOINT Command

The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.

The syntax for a RELEASE SAVEPOINT command is as follows.

RELEASE SAVEPOINT SAVEPOINT_NAME;

Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.

The SET TRANSACTION Command

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write.

The syntax for a SET TRANSACTION command is as follows.

SET TRANSACTION [ READ WRITE | READ ONLY ];

(4)AUDIT TRAIL

An audit trail (also called an audit log) is a chronological record of security-relevant data that documents the sequence of activities affecting an operation, procedure, event, file or document.
Audit logs are used to track the date, time and activity of each user, including the pages that have been viewed.

The benefits of audit trail include :

Individual Accountability - Audit trails are a technical mechanism that help managers maintain individual accountability. By advising users that they are personally accountable for their actions, which are tracked by an audit trail that logs user activities, managers can help promote proper user behavior. Users are less likely to attempt to circumvent security policy if they know that their actions will be recorded in an audit log.

Intrusion detection - Intrusion detection refers to the process of identifying attempts to penetrate a system and gain unauthorized access. If audit trails have been designed and implemented to record appropriate information, they can assist in intrusion detection.

Detailed Insight - Because an audit log tracks how long and how frequently individual users access a document, it can be used to gain insight into which investors or potential partners are most interested in a business, enabling the company to be more strategic with its negotiations.

Follow the sequence of activations of a BTS process, SALES1234567890. The activities that make up the process run on two CICS® regions.

For clarity, the example does not show the activations of any other processes that might also be running in these regions.

Example audit trails. This table shows, for each audit level setting, the points at which audit records would be written. The letters in the columns are the names of the activities for which records are being written.

In this example, an application running on region SYS1 defines a new process, SALES1234567890, and requests it to run. The root activity of the new process begins running on SYS1. It defines and runs an activity B, which executes synchronously. When control returns to the root activity, it defines activities C and D and schedules them to run asynchronously. After the root activity has returned, activity C starts on SYS1 and activity D starts on SYS2.

Activity C schedules child activities E and F to run asynchronously and returns. E and F run on different systems. When each of its child activities completes, C is reactivated and checks the completion status of the child. Lastly, C completes normally, which causes the root activity to be reactivated.

Activity D defines a child activity G and schedules it to run asynchronously. Later, another transaction issues ACQUIRE ACTIVITYID and CANCEL ACQACTIVITY commands against activity G. G completes in a FORCED state. D is reactivated and discovers what has happened to G with a CHECK ACTIVITY command. In response to G's failure, D defines a new activity H and requests it to run asynchronously. D then returns and H runs on the other region. When H completes normally, D is reactivated and completes normally. This causes the root activity to be reactivated. The root activity issues a CHECK ACTIVITY command to see how D completed, and then completes normally, ending the process.