Dbms day-5 more concepts

DBMS more cncepts - audio blog
Importing and exporting database:

When you are using some clients like phpmyadmin importing and exporting can be done easily using button clicks. It doesn’t mean importing and exporting databases is difficult in command line. Mysql allows users to export and import databases using dump files.
A dump file of a database contains all sql statements needed to create the database and populate the data again.
A dump file can be created using using following command,

> mysqldump -u user_name -p database_name > dump_file_name.sql

Now a dump file for the database database_name will be created as dump_file_name.sql

To import this dump file into a database the command used is,

> mysql -u user_name -p database_name < dump_file.sql

this command imports dump_file.sql into the database database_name

Views:
Sometimes you require only few tuples of a data to be viewed often or you need only few fields of a table to be shown every time. Rarely you would need to see the full table. In that case you can create a view of a table and use it every time.
A view can be created using the following sql query,

mysql>create view view_name as select_query;

here select_query can be written in a way to match your needs. It is to be noted that updating table automatically updates table and vice versa.

Triggers:
A trigger is an sql statement or set of sql statements which can be made to be executed whenever an insert or update or delete operation happens in some user specified table.

create trigger trigger_name
[before/after] [update/delete/insert]
on table_name
for each row
begin
sql_statements
end
In place of sql_statements you can write the sql statements you need to execute when the trigger is triggered. mysql provides old and new keywords for accessing the values of fields before and after modification !!

For example, suppose you need to make a note of all name changes in some log file,
then you can create a trigger that gets triggered after every update in user table.

create trigger log_trigger
after update
on users
for each row
begin
insert into log values(old.user_name,new.user_name)
end


It is advised to change the delimiter at the start of the trigger so that you can use semicolon for ending each sql statement within the body of the trigger
for example,
delimiter |
trigger_defn
end;
|
After this you can change the delimiter back to semicolon
delimiter ;
It is to be noted that the triggers will be triggered only by sql statements, whenever an API does some modification the trigger is not automatically triggered!!

Stored Procedures:
They are just like procedures in programming languages. When you have a set of sql statements to be executed often, you can store them as a procedure and call it using the procedure name you have assigned.
When we have such procedures in server, the clients or users can directly call the procedure. In a way it gives more security and modularity. The clients won’t be using individual queries (or in most cases prevented from accessing the individual queries) every time.

create procedure procedure_name()
begin
    sql statements
end
This is the basic syntax for declaring a procedure in mysql. The procedure can then be called using the keyword call

call procedure_name()

Procedures allow you to define variables. The scope of the variables is between the begin and end statements of the procedure.

I was wondering how a function can be really useful without parameters ! Well procedures in sql does have parameters. They can be of any of the following three type.

• IN --> input to the function. Changes done inside the procedure on the parameter doesn’t get reflected outside
• OUT --> output from function. This is the value which is changed inside the procedure returned after the procedure gets over.
• INOUT --> can be given as input to the function and the return value is also stored here. Or in other words the changes done inside the procedure on this parameter gets reflected.

As in triggers delimiter has to be changed here too !

Commit and rollback:
In mysql autocommit is set to be true. So if you want to check commit and rollback statements set autocommit=0;
then start a transaction ( start transaction ) , set a savepoint ( savepoint savepoint_name) do some modifications like insertion or deletion, then rollback to the previous savepoint (rollback to savepoint savepoint_name).
Now the changes you made wont be reflected in the database.
Only when you commit changes are written to disk.
A transaction ends whenever you give a commit or rollback statement.

Database Normalization:
It's just a process of organizing data in database. It mostly involves splitting large tables into small ones(not necessarily in all cases). There are well defined rules for normalizing a database. We have 1st normal form , 2nd , 3rd normal forms.. etc !
Explaining about each normal form in detail is not possible. I will just give a hint of each.

• First normal form: Eliminates repeating data in individual table
• Second normal form: Creating separate tables for sets of values that are common to many tuples
• Third normal form: Eliminate those fields that are not related to the primary key (not always desirable or possible!)

That’s it for today. See you in next blog.


Trained @ Sourcebits