DBMS

DB Indexing

Indexing is a data structure technique which allows you to quickly retrieve records from a database file. An Index is a small table having only two columns. The first column comprises a copy of the primary or candidate key of a table. Its second column contains a set of pointers for holding the address of the disk block where that specific key value stored.

Normaliztion 

The process of organizing data to avoid any duplication of data and redundancy is known as Normalization. There are many successive levels of normalization which are known as normal forms. Each consecutive normal form depends on the previous one. 

Why we use normalization

• Make the database more efficient
• Prevent the same data from being stored in more than one place (called an “insert anomaly”)
• Prevent updates being made to some data but not others (called an “update anomaly”)
• Prevent data not being deleted when it is supposed to be, or from data being lost when it is not supposed to be (called a “delete anomaly”)
• Ensure the data is accurate
• Reduce the storage space that a database takes up
• Ensure the queries on a database run as fast as possible

First Normal Form (1NF):.

This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.

• Does the combination of all columns make a unique row every single time?
• What field can be used to uniquely identify the row?

Second Normal Form (2NF):.

Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

• Are all of these columns dependent on and specific to the primary key?

Third Normal Form (3NF):.

This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.A normalized relation is in the third normal form if all the nonkey attributes are fully functionally dependent on the primary key and there are no transitive (nonkey) dependencies.

• Is every attribute that is not the primary key must depend on the primary key and the primary key only?

Fourth Normal Form (4NF):.

Meeting all the requirements of third normal form and it should not have multi- valued dependencies.

What is Multi-valued Dependency

A table is said to have multi-valued dependency, if the following conditions are true,

For a dependency A → B, if for a single value of A, multiple value of B exists, then the table may have multi-valued dependency.

Also, a table should have at-least 3 columns for it to have a multi-valued dependency.

And, for a relation R(A,B,C), if there is a multi-valued dependency between, A and B, then B and C should be independent of each other.

If all these conditions are true for any relation(table), it is said to have multi-valued dependency.

Denormalization

Denormalization is a database optimization technique used by database administrators to optimize the efficiency of their database by adding redundant (duplicate) data to one or more tables. This method can help us to avoid costly joins in a relational database made during normalization.

Traditionally data is stored in normalized databases, in which multiple separate tables are maintained in a relational database to minimize the redundant data. Therefore, whenever we have to access data from multiple tables, we need to perform complex and costly join operations on the required tables.Denormalization does not mean that the database is never normalized. It is just a method of optimizing databases that is implemented after normalization has been achieved.

Stored Procedure

A stored procedure is a set of precompiled SQL statements that are used to perform a special taskStored procedures are a batch of SQL statements.Most major DBMs support stored procedures; however, not all do.SP help to centralize data access logic into a single place that is then easy for DBA's to optimize.SP provide security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. SP can accept input parameters so that a single procedure can be used over the network by several clients using different input data. If we modify a SP all the clients will get the updated stored procedure. SP allows faster execution as they are pre-compiled. SP can reduce network traffic by An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

EX :

CREATE PROCEDURE spGetStudent(@StudentID int)

AS

SELECT FirstName, LastName ....

FROM Student

WHERE StudentID = @StudentID

How to create a DB(user) in a server

• sqlplus /nolog
• connect as /sysdba
• create user identified by 'your DB(user) name ';
• grant connect,dba,resource to 'your DB(user) name ';

Adding data to created user from data backup

* Exit;

* imp (press enter)

* give your back up's path by dragging it here (press enter)

* give your user name

* give a password

* Then select your option as your requirements with Enter key

Toad is software which help to create and manipulate DBMS easily

Importing

Data base->import utility wizard->Then select options according to your requirements

Exporting(Taking backup)

Data base->Export utility wizard->Then select options according to your requirements

How to unlock your DB

Log to the DB as sys user

check whether your DB has Locked by Running Query :

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,b.osuser, b.machine

FROM v$locked_object a, v$session b, dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id;

If you got some rows return as query output, DB has lcked

unlocked by running the Query with result(SID , serial#) of above query :

alter system kill session ' SID , serial# ' immediate;