1. What is deadlock?
A dead lock is a situation in which two users (or transactions) have locks on separate objects, and each user is waiting for a lock on the other’s object. It usually occurs in a multi-user environment.
2. What are triggers?
A trigger is a block of code that constitutes with a set of T-SQL statements that are activated in response to certain actions. A trigger can also be interpreted as a special kind of stored procedure that is executed whenever an action, such as data modification, takes place.
A trigger is always defined on a table, and is said to have fired whenever the data in the underlying table is affected by any of the Data Manipulation Language (DML) statements-INSERT, UPDATE, or DELETE. A trigger fires in response to an event like insertion, updation, and deletion of data.
Triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex actions and cascade these actions to other dependant tables.
Characteristics of a trigger:
· It can be associated with tables.
· It cannot be defined on temporary tables or views. However, it can reference temporary tables and views.
· Whenever any data modification statement is issued then SQL Server fires it automatically.
· It cannot be explicitly invoked or executed, as in the case of stored procedures.
· Triggers can be nested up to 16 levels. The nesting of triggers occurs when a trigger performs an action that initiates another trigger.
· It prevents incorrect, unauthorized, and inconsistent changes in data.
· It cannot return data to the user.
There are three types of triggers
1) INSERT
2) DELETE
3) UPDATE
3. What is Normalization? Explain three normal forms?
Normalization is a scientific method of breaking down complex table structures in to simple table structures by using certain rules. Hence reduce redundancy in a table and eliminate inconsistency problems and disk space usage.
First Normal Form
A table is said to be in 1NF when each cell of the table contains precisely one value.
Functional dependency
If you have two attribute A and B, A is said to be functionally dependant on B, if for each value of B, there is exactly one value of A.
Second Normal Form
Identify the functionally dependent keys and place them in a different table.
Third Normal Form
A relation is said to be 3NF when every non-key attribute is functionally dependant only on the primary key.
Posts
