Database Trigger

Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.

Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view. 

Triggers are commonly used to

1)automatically generate derived column values

2)prevent invalid transactions

3)enforce complex security authorizations

4)enforce referential integrity across nodes in a distributed database

5)enforce complex business rules

6)provide transparent event logging

7)provide sophisticated auditing

8)maintain synchronous table replicates

9)gather statistics on table access 

Cascading Triggers:-

When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. 

A trigger has three basic parts:-

A)a triggering event or statement:-

A triggering event or statement is the SQL statement that causes a trigger to be fired. A triggering event can be an INSERT, UPDATE, or DELETE statement on a table.  

B)a trigger restriction:-

A trigger restriction specifies a Boolean (logical) expression that must be TRUE for the trigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE or UNKNOWN. 

C)a trigger action :-

A trigger action is the procedure (PL/SQL block) that contains the SQL statements and PL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.

Types of Triggers:-

There are two types of triggers:- 

Row Triggers: A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not executed at all.  

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. 

Statement Triggers: A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected). For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. 

BEFORE vs. AFTER Triggers

BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. These triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement. 

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed.These triggers are used when you want the triggering statement to complete before executing the trigger action. 

You can create 4 types of triggers by combinations in the following execution order:-

BEFORE statement trigger

BEFORE row trigger 

AFTER statement trigger

AFTER row trigger

B)Trigger Execution:-

A trigger can be in either of two distinct modes: 

enabled

An enabled trigger executes its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE. 

disabled

A disabled trigger does not execute its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to TRUE.

Database View

Database view:-

A Database View is a subset of the database sorted and displayed in a particular way. For each view, you can control which columns are displayed, what order they are displayed in, how wide each column is, how the data is sorted, and what types of records to display.

Unlike base tables in a relational database, a view does not form part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database. Changing the data in a table alters the data shown 

Views can provide advantages over tables:

1)Views can represent a subset of the data contained in a table.

2)Views can join and simplify multiple tables into a single virtual table.

3)Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data.

4)Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.

5)Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents.

6)Depending on the SQL engine used, views can provide extra security.

7)Views can limit the degree of exposure of a table or tables to the outer world in subsequent invocations of the view.

  

Read-only vs. updatable views:-

View can be defined as read-only or updatable. If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable. INSERT, UPDATE, and DELETE operations can be performed on updatable views. Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. A view update is done by key preservation.

Materialized view :-
The Oracle database introduced the concept of materialized views: pre-executed, non-virtual views commonly used in data warehousing."It is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data." Materialized views, which store data based on remote tables, are also known as snapshots. A snapshot can be redefined as a materialized view.

Normalization

Normalization:-

Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Below are the different normal forms:-

1NF: Eliminate Repeating Groups
A database is said to be in First Normal Form when all entities have a unique identifier or key(primary key), and when every column in every table contains only a single value and doesn't contain a repeating group or composite field. "All columns (fields) must be atomic".

2NF: Eliminate Redundant Data
A database table is in 2NF when it is in 1NF and if all its non-prime attributes are functionally dependent on the whole of every candidate key. (A non-prime attribute is one that does not belong to any candidate key. that means "table should be in1NF and every non-key column is fully dependent on the (entire) primary key".


As the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns. 


3NF: Eliminate Columns Not Dependent On Key 
A database is in Third Normal Form when it is in Second Normal Form and each column that isn't part of the primary key doesn't depend on another column that isn't part of the primary key.All attributes must be directly dependent on the primary key."In 2NF and every non-key column is mutually independent." 

BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related. "Remove the multivalued dependency."

5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
A table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys

ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form
A model free from all modification anomalies.


De-normalization:-
It is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.


When not to normalize:-
1)Want to keep tables simple so user can make their own queries
2)Avoid processing multiple tables
3)whenever performance issues.Testing shows Normalization has poorer performance

 
 

SQL DDL, DML, DCL and TCL Commands

SQL DDL, DML, DCL and TCL Commands

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Database Indexes

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.By default, Oracle creates B-tree indexes.

Types of Index

A)Clustered Index

B)Non Clustered Index

Clustered Index:- Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table.

• Typically created on primary key column.

Non Clustered Index:-The data is present in random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout the table. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the page and the row number in the data page

• The physical order of the rows is not the same as the index order.
• Typically created on column used in JOIN, WHERE, and ORDER BY clauses.

Difference between Clustered and non Clustered Index:-

1)Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.Non Clustered index is Good for tables whose values may be modified frequently.

2) Clustered index usually saved in side the table however non Clustered Index saved in separate tables in database.

There are multiple ways to implement Index:-

1)Bitmap Index:-A bitmap index is a special kind of index that stores the bulk of its data as bitmaps and answers most queries by performing bitwise logical operations on these bitmaps. The most commonly used index, such as B+tree are most efficient if the values it indexes do not repeat or repeat a smaller number of times

2)B-tree Index:-By default, the Oracle creates a b_tree index.  In a b-tree, you walk the branches until you get to the node that has the data you want to use.  In the classic b-tree structure, there are branches from the top that lead to leaf nodes that contain the data.

3)Dense Index:- A dense index in database is a file with pairs of keys and pointers for every record  in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key.

4)Sparse Index:-A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block. primary key is a sparse index.

5)Reverse Index:- reverse key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.

6)Function based Index:- An index is defined on the result of a function applied to one or more columns of a single table. Functional indexes can be used to obtain fast access to data based on the result of function calls.

Ex:-This query can use an index, if one has been defined on the result of the lower(coll) operation:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

The function in the index definition can take more than one argument, but they must be table columns, not constants. Functional indexes are always single-column (namely, the function result) even if the function uses more than one input field; there cannot be multi column indexes that contain function calls.

Advantage of Index:- 

1. Faster retrieval of data. Increased performance.There is no need to access a row in the database from an index structure, so you can reduce the total number of I/O operations needed to retrieve data.
   
2. Presorted data-The data in the leaf nodes is already sorted by the value of the primary key. 

Disadvantages of Index:-

1. You must have a primary key on the table with a unique value.
2. You cannot have any other indexes on the data.
3. You cannot partition an index-organized table.
4. An index-organized table cannot be a part of a cluster.

 

PL/SQL Function and Procedure

Stored Procedure:-

A Stored procedure is named PL/SQL block which perform one or more specific task.A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. 

We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

 

Function:-

A function is a named PL/SQL Block which is similar to a procedure.The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.  

You can call your function in multiple ways like following:-

1) Since a function returns a value we can assign it to a variable.

   employee_name :=  employer_details_func;

2) As a part of a SELECT statement

  SELECT employer_details_func FROM dual; 

3) In a PL/SQL Statements like,

  dbms_output.put_line(employer_details_func);

    This line displays the value returned by the function.

Difference between function and procedure

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.

2. Function can be called from SQL statements where as procedure can not be called from the sql statements

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5. Function returns 1 value only. Procedure can return multiple values (max 1024).

6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named table1 and table2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.

7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.

9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller

Advantages and Comparison of Stored Procedure/Function with normal SQL:-

Overhead: Stored procedure statements are stored directly in the database, they may remove all or part of the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database.

 In addition, while they avoid some overhead, pre-compiled SQL statements add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions.

Avoidance of network traffic: A major advantage with stored procedures is that they can run directly within the database engine. The benefit here is that network communication costs can be avoided completely. This becomes particularly important for complex series of SQL statements.

Encapsulation of business logic: Stored procedures allow programmers to embed business logic an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. The database system can ensure data integrity and consistency with the help of stored procedures.

Delegation of access-rights: In many systems, stored procedures can be granted access rights to the database that users who execute those procedures do not directly have.

Some protection from SQL injection attacks: Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. Also, some DBMSs will check the parameter's type.

Disadvantage of procedure/functions:-

Vender Specific:-Stored procedure languages are quite often vendor-specific. Switching to use another vendor's database most likely requires rewriting any existing stored procedures.

Restricted Overloading Functionality:- Procedure and function overloading is available only within database packages or as PL/SQL sub-blocks. Database stored procedures cannot be overloaded. If you try to add a second overloaded stored procedure with the same procedure identifier, an error will result.

Disabled Procedure:-Database procedures can be accidentally dropped by a person with sufficient privilege. This is nothing new, except that there is no practical way of writing application code to detect inoperable procedure(s). Since SQL and PL/SQL are interpreted languages, an invalid or missing stored procedure is not detectable until module run-time. When the error is encountered, a confusing, cryptic database error results that is of very little help to the average application user.

Automatic dependency tracking:-When a simple DDL operation, such as adding a new column to an existing table, is performed, many stored procedures are marked as needing recompilation. What is not provided is the ability to recompile all these dependencies in a single action or to ascertain which procedures are, in fact, invalid. Other than compiling each object one at a time and checking its results, we have no way of determining the effect of a DDL modification.