What is trigger?

A Database Trigger is a stored procedure that is fired when a DML operation is performed on the table. In total there are 13 types of Triggers

Syntax for creating a trigger:

            CREATE OR REPLACE TRIGGER <TRIGGERNAME> before / after

                        [INSERT / UPDATE / DELTE ] ON <TABLE NAME>

                        {For each Statement / Row}

                        {When <condition…..>}

How can you tell if a database object is invalid?

By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.

Select * from all_objects where object_name =’PACKAGE_NAME’;

Dynamic SQL (Execute Immediate) in PLSQL

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead; EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more users friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.

1. EXECUTE IMMEDIATE will not commit a DML transaction carried out and an explicit commit should be done.

If the DML command is processed via EXECUTE IMMEDIATE, one needs to explicitly commit any changes that may have been done before or as part of the EXECUTE IMMEDIATE itself. If the DDL command is processed via EXECUTE IMMEDIATE, it will commit all previously changed data.

2. Multi-row queries are not supported for returning values, the alternative is to use a temporary table to store the records (see example below) or make use of REF cursors.

3. Do not use a semi-colon when executing SQL statements, and use semi-colon at the end when executing a PL/SQL block.

4. This feature is not covered at large in the Oracle Manuals. Below are examples of all possible ways of using Execute immediate. Hope it is handy.

5. For Forms Developers, this feature will not work in Forms 6i front-end as it is on PL/SQL 8.0.6.3.

declare

 l_depnam varchar2(20) := 'testing';

 l_loc    varchar2(10) := 'Dubai';

begin

 execute immediate 'insert into dept values (:1, :2, :3)'

   using 50, l_depnam, l_loc;

 commit;

end;

EXECUTE IMMEDIATE is a much easier and more efficient method of processing dynamic statements than could have been possible before. As the intention is to execute dynamic statements, proper handling of exceptions becomes all the more important. Care should be taken to trap all possible exceptions.

Which system tables contain information on privileges granted and privileges obtained?

USER_TAB_PRIVS_MADE

USER_TAB_PRIVS_RECD

Which system table contains information on constraints on all the tables created?

USER_CONSTRAINTS.

Normalization / De-Normalization

Normalization is the process of efficiently organizing data in a database.  There are two goals of the normalization process:  eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table).  Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

1.     Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.

2.     Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3.     Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.

4.     Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.

5.     Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.

1st Normal Form (1NF)

A table (relation) is in 1NF if

1. There are no duplicated rows in the table.

2. Each cell is single-valued (i.e., there are no repeating groups or arrays).

3. Entries in a column (attribute, field) are of the same kind.

Note: The order of the rows is immaterial; the order of the columns is immaterial.

Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns).

2nd Normal Form (2NF)

A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.

Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in 1NF and if it has no partial dependencies."

3rd Normal Form (3NF)

A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

Boyce-Codd Normal Form (BCNF)

A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

4th Normal Form (4NF)

A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.

5th Normal Form (5NF)

A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain-Key Normal Form (DKNF)

A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.

De-Normalization:

De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply De-normalization in the process of deriving a physical data model from a logical form.

What is Normalization?

Normalization is the process that helps analysis or database designer to design table structure for an application. The focus of normalization is to attempt to reduce redundant table data to very minimum.

Normalization is carried out for the following reason:

1.     To structure the data between tables so that maintenance is simplified.

2.     To re allow data retrieval at optimal speed.

3.     To simplify data maintenance through update, insert & deletes.

4.     To reduce the need to restructure table as new application requirement arise.

5.     To improve the quality of design for an application by rationalization of table data.

Normalization is technique that:

1.     Decompose data into two dimensional tables

2.     Eliminate any relationship in which table data does fully depend upon the primary key of record.

3.     Eliminate any relationships that contain transitive dependencies.

FIRST NORMAL FROM:

When a table is decomposed into two dimensional table with all repeating group of data eliminated the table data is said to be in its first normal form.

The repetitive portion of data belonging to the record is treated as repeating groups.

A table is in 1^st normal form if:

·         There are no repeating groups.

·         All the key attribute are defined.

·         All attribute are dependent on a primary key.

To convert a table to its First Normal Form:

1.     The un-normalized data in the first table is the entire table.

2.     A key that will uniquely identify each record should be assigned to the table. This key has to be unique because it should be capable of identifying any specific row from the table for extracting information for use. This key is called the table’s primary key.

SECOND NORMAL FROM:

A table is said to be in its second normal form when each record in the table is in the first normal form & each column in the record is fully dependent on its primary key.

A table is in 2^nd normal form if:

·         It’s in 1^st normal form.

·         It include no partial dependencies (where as attribute is dependent on only a part of primary key)

To convert a table to its Second Normal Form:

1.     Find & remove fields that are related to the only part of the key

2.     Group the remove item in the another table

3.     Assign the new table with the key i.e. part of a composite key.

THIRD NORMAL FROM:

Table data is said to be in third normal form when all transitive dependencies are remove from this data.

A table is in 3^rd normal form if:

1.     It’s in 2^nd normal form.

2.     It contains no transitive dependencies (where a non-key attribute is dependent on another non –key attribute)