Transactions in Database

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

•    Begin Transaction
•    Rollback Transaction
•    Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Error Handling

The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a clients connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of @@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after any INSERT, UPDATE, or DELETE statement.

Example:- 

 

 

Note:-

It’s used to safe your data during error occurs.

 

Read More

Tips to Prevent Your Email from Hacking

We can proudly say that we live in a world that has developed scientifically and technically in all aspects, but are you sure that these developments are safe and secure. A survey says that more than billions of users are using internet worldwide and email has become one of the important communication tool and here goes one of the beautiful quotes by Tipper Gore “We use tools such as email, not just as a way to keep in daily touch with family members who live in other cities, but also as a way to keep in touch with staff and members of the public.”

Email, the most necessary tool for communication is threatened by a technological threat called hacking. Hacking of email have become so common nowadays and hope these few tips helps you to prevent your email account from hacking.

 • Change your password at regular intervals and always choose a password that cannot be easily guessed by using combinations of upper case, lower case along with numbers and symbols. (but do remember your passwords)

•  Don’t ever give your email id’s to all websites you browse 

•  Beware of what you access and of what is being shared over the network. These public connections can allow others to access your information without you even being aware it is happening. 

•  Always check your email settings such that any of the settings you made were not changed 

• Have an anti-virus software on your computer such that it helps you to recognize any unusual activity

 • Use a browser that has phishing filters and check for filters in your email account if you use Gmail account.

Read More

Cursor in Database

Cursor:-

A cursor is a database object that helps in accessing and manipulating data in a given result set. The main advantage of cursors is that you can process data row-by-row.

A result set is defined as a collection of rows obtained from a SELECT statement that meet the criteria specified in the WHERE clause.

Cursors, Serve as a mechanism for applications to operate on a single row or a set of rows.

Cursors enable the processing of rows in the given result set in the following ways:

1) Allow specific rows to be retrieved from the result set.

2) Allow the current row in the result set to be modified.

3) Help navigate from the current row in the result set to a different row.

4) Allow data modified by other users to be visible in the result set.

Structure of Cursors:

The following tasks need to be performed while using a cursor in SQL Server:

1) The cursor needs to be defined and its attributes need to be set.
2) The cursor needs to be opened.
3) The required rows need to be fetched from the cursor. Fetch refers to the process of retrieving a row from the result set.
4) The data in the current row of the cursor can be modified, if required.
5) The cursor needs to be closed.
6) The cursor should be deallocated.

This is a good practice as resources used by the cursor are released.

 

Result:-  

Note:-

 Cursor is used to read a record on row by row basis, so that above cursor stored procedure shows only one record at a time.

 So we apply the “while” condition and get all the records row by row.

Result:- 

Conclusion:-

(I)    It is returned by only one row at a time. So it gives less performance.
(II)    If the result set are less than 50 or 100 records it is better to go for cursors.

Read More

Continuation of Normalization Forms

Fourth Normal Form (4NF):-

(i)    Remove the multi-valued  dependency.
(ii)    Make separate table for multi-valued  Fields.

 

Result:-

 

Fifth Normal Form (5NF):- 

(i)    Remove the join dependencies.
(ii)    Break the database table into smaller and smaller tables to remove all data redundancy.

Result:-

Read More

Normalization in Database (Continuation)

In Tychons, we never get fed up of sharing information and even working too. On our previous posts regarding normalization in database, we saw what is normalization? Its benefits and we saw how many different types of normalization forms are available and also we saw the first two of its types such as first and second normal form. Now let us see third normal form. 

Third Normal Form (3NF):-

(i)    Remove columns that are not fully dependent upon the primary key.

Example:-

Boys-Codd Normal Form (BCNF or 3.5NF):-

1. A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a “candidate key”.
2.  Remove the non trival functional dependency.
3.  It’s considered a special condition of third Normal form.

Candidate key:-

Define:-
 More than one primary key in the table, that primary key is called as candidate key.

3NF:-

Step:1

 Step:2

Note:-

•    For each subject, every student is educated by one teacher.
•    Every teacher teaches one subject only.
•    Each subject can be teached by more than one teacher.

3NF to BCNF:-

1)    Student is not a “Primary key”.
2)    Teacher and Subject is “Primary key”.

Note:-

A relation is in BCNF, if and only if, every determinant is a candidate key. So filter the non primary key column.

On Behalf of tychons family, i would like to thank our employee who has been sharing continuos information regarding normalization in database. Do watch our blog for regular updates on remaining types of normalization forms.

Read More

Normalization in Database

Normalization:-

The set of rules used to design the table.

Benefits of Normalizing:-

1)    Eliminate data redundancy.

2)    Improve performance.

3)    Query optimization.

4)    Faster update due to less number of columns in one table.

5)    Index improvement.

Different types of Normalizations form available in the Database:-

1)    INF.
2)    2NF.
3)    3NF.
4)    Boyce-Codd Normal Form (or) 3.5NF.
5)    4NF
6)    5NF.

First Normal Form (1NF):

(i)    Eliminate duplicative columns from the same table.
(ii)    Create primary key.

Example:-

Second Normal Form (2NF):

(i)    Remove subsets of data that apply to multiple rows of a table and place them in separate tables 
(ii)   Create relationships between these new tables and their predecessors through the use of foreign
         keys.
(iii)  Remove columns which create duplicate data in a table and related a new table with Primary
         Key – Foreign Key relationship.


Example:-

Step: - 1

Step: - 2

Read More