Database Developer – FAQ

Database Design Concept
Database normalization is the process of efficiently organizing data in a database.
There are two reasons of the normalization process:

Eliminating redundant data, for example, storing the same data in more than one tables.
Ensuring data dependencies make sense.

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that
data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure.

—>Reduce data duplication
—>data integrity

Ensure that there are no repeating groups of data.
Ensure that there is a primary key.

1. Simplify the multiple telephone number in separate rows along with customer id
2. Simplify the multiple subjects in separate rows along with student id

Let say consider a table which have data of emp’s skills and current work location
divide the single table into 2 tables
1 table which have emp and current work location (Remove redundancy of duplicate location)
2 table which have emp and skills (Here multiple skills)

It is in second normal form.
All nonprimary fields are dependent on the primary key.
The dependency of nonprimary fields is between the data. For example, street name, city,
and state are unbreakably bound to the zip code.

The dependency between zip code and address is called a transitive dependency.
To comply with third normal form, all you need to do is move the Street, City,
and State fields into their own table, which you can call the Zip Code table:

Most 3NF tables are free of update, insertion, and deletion anomalies.

Database Constraint
Types of Joins
Choose the Database Objects
Manage Duplicate Records

Block Concept
Control Structure
Anonymous Block

On insert triggers have no :OLD values.
On delete triggers have no :NEW values.
Triggers do not commit transactions. If a transaction is rolled back, the data changed by the trigger is also rolled back.
Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or none.
Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger.
If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all the actions in the required order.
A trigger can cause other events to execute triggers.

Avoiding Mutating triggers

1. Don't use triggers
2. Use an "after" or "instead of" trigger
If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.

3. Re-work the trigger syntax -
Dr. Hall has some great notes on mutating table errors, and offers other ways to avoid mutating tables with a combination of row-level and statement-level triggers.

4.Use autonomous transactions -
You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.

Implicit Cursor
Explicit Cursor
Exception Handling

10gR2 New Feature: DML Error Logging
DML Error Logging Handles: Exceptions:
Too-large column values Except for LONG, LOB, or object type columns
Constraint violations (NOT NULL, unique, referential, and check constraints) Except for:

  • Violated deferred constraints
  • Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
  • Any UPDATE or MERGE operation that raises a unique constraint or index violation.
  • Violation of a constraint on a LONG, LOB, or object type column
Trigger execution errors
Type conversion errors arising from type conversion between a column in a subquery and the corresponding column of the table Except for LONG, LOB, or object type columns
Partition mapping errors
A specific MERGE operation error (ORA-30926: Unable to get a stable set of rows)

Automation using Database Scheduler

Data Migration using SQL Loader
Data Processed using Oracle External Table

What is Oracle mutating errors?

A trigger can not change a table that it has read from. This is the mutating table error issue.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.