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
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.
Types of Joins
Choose the Database Objects
Manage Duplicate Records
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.
10gR2 New Feature: DML Error Logging DBMS_ERRLOG.CREATE_ERROR_LOG
|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:
|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.