Question

In: Computer Science

Please, No handwriting COURSE; introduction to database Define and explain the following with example: 1. Outer...

Please, No handwriting

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

Solutions

Expert Solution

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


Related Solutions

Not handwriting answer, please In a word document, please answer the following questions: Course Introduction of...
Not handwriting answer, please In a word document, please answer the following questions: Course Introduction of Biostatistics Define the following terms: correlation coefficient scatter plot bivariate relationship Provide an example where the outlier is more important to the research than the other observations? Identify when to use Spearman’s rho.
Please define, explain and give an example of the following : Leadership and Millennial - A...
Please define, explain and give an example of the following : Leadership and Millennial - A Disruptive Leadership? ( Plagiarism check, minimum 1000 word ). I will give you good rating. 1) Define Millennial leadership style 2) How does the effect of Millennial leadership 3) Brief explain Disruptive Leadership, are you agree Millennial leadership to be classified as Disruptive Leadership. If yes, what is the reason,
In your own words, define the following database terms and give an example of each: Table...
In your own words, define the following database terms and give an example of each: Table Record Field Primary Key Foreign Key
Note: No handwriting, please 1. Define traditional banking and describe the causes of its demise. 2....
Note: No handwriting, please 1. Define traditional banking and describe the causes of its demise. 2. Explore different competitors to traditional banking, like credit unions and online banking - what do they use to compete with traditional banking institutions? 3. Explain why the government can't simply legislate bad things out of existence. 4. Explain how the government regulators made the Savings and Loan Crisis worse, and exacerbated the Great Depression.
Please use an Access database with two tables to answer the following: use an example to...
Please use an Access database with two tables to answer the following: use an example to discuss the difference between a right, left, and inner join. Next, perform the left joint, right joint, and inner joint all on the each of the two tables.
Use your words to explain it. Please NO handwriting. NO copying. Reflect on the following questions...
Use your words to explain it. Please NO handwriting. NO copying. Reflect on the following questions and discuss: What is the common structure of introduction and conclusions? Provide an example of an introduction and a conclusion paragraph you have written in the past. Analyze your introduction and conclusion according to the structural suggestions stated within the Craft of Research.
Define the four main types of relational constraints and use the example relational database (Figure 1)...
Define the four main types of relational constraints and use the example relational database (Figure 1) to illustrate each of these constraints. Figure 1 House(MLS, Addr, NumRooms, NumBedRooms, SellID, OfficeID, Price) Seller(SellID, Name) PotentialBuyer(BuyID, Name) REOffice(OffID, Name, Addr, Phone) Agent(AgID, OffID, Name) Showing(AgID, MLS, BuyID, Date)
Please explain the following questions in detail: 1. The introduction of gall flies to control invasive...
Please explain the following questions in detail: 1. The introduction of gall flies to control invasive knapweed in montana permitted an increase in the numbers of deer mice. Apart from an increase in hantavirus in the area, what other effects might this have on the local community. 2. Explain how you might set a field experiment to determine whether insect pests are affecting crop yields. Be careful to include appropriate treatments and controls.
Please Use your keyboard (Don't use handwriting) Thank you.. Courses Name: Introduction to Biostatistics Please answer...
Please Use your keyboard (Don't use handwriting) Thank you.. Courses Name: Introduction to Biostatistics Please answer the following questions: I need new and unique answers, please. (Use your own words, don't copy and paste) i need more than 500 words pleasssse Q1. Discuss the tools to measure central tendency. Q2. a) Discuss parametric and nonparametric test used for hypothesis testing. b) In a cross sectional study on coronary heart disease (CHD), the smoking and CHD status is summarized below. Use...
Please define, explain and give an example of the following: Neo-Charismatic Leadership: Charismatic Leadership: Transactional Leadership:...
Please define, explain and give an example of the following: Neo-Charismatic Leadership: Charismatic Leadership: Transactional Leadership: Transformational Leadership: Define and explain the differences between micro and macro leadership. Which do you respond best to and why?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT