HR & Technical Interview Questions With Answers.

82. What Is A Trigger? What Are The Types Of Trigger? Triggers are database object. Basically these are special type of stored procedure that is automatically fired / executed when a DDL or DML command statement related with the trigger is executed. They can be executed automatically on the insert, delete and update operation. Trigger allows us to execute a batch of SQL code when an insert, update or delete command is executed against a specific table. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster. In SQL Server we can create four types of triggers Data Definition Language (DDL) triggers. Data Manipulation Language (DML) triggers. After Trigger (using FOR/AFTER CLAUSE) Instead of Trigger (using INSTEAD OF CLAUSE) Common Language Runtime (CLR) triggers. Logon tri...

“DBMS/RDBMS” Interview Questions With Answers". Here, are some sample questions based on “RDBMS”. Read it carefully as these questions will improve your basic concept on RDBMS, and will help you in cracking any interview. Click on any question to find out it's answers: 1)  What Is A Database? 2)  What Is DBMS & Database? 3)  What Are The Advantages Of DBMS? 4)  What Are The Disadvantages In File Processing System? 5)  Discuss How To Know When There Is A Need Of DBMS & When There Is Not? 6)  What Is Parallel & Distributed Database? Differentiate Them. 7)  Differentiate Between File-Oriented System & Database System? 8)  What Are The Applications Of Databases? 9)  What Is The Importance Of Databases? 10)  What Is DBMS & RDBMS? 11)  What Are The Three Types Of Users In DBMS? 12)  What Are The Disadvantages Of Database System? 13)  Describe The Three Levels Of Data Abstraction? 14...

77) What Is Meant By Deadlock In DBMS? Answer:  A deadlocks involves a chain of transactions that are cyclically waiting for each other to release a lock. The DBMS detects deadlock with a transaction dependency graph. It resolves the impasse by sacrificing one of the transactions in cycle. 78) What Are The Three Techniques For Handling Deadlocks? Answer: Three techniques used for handling deadlocks are: Timeouts   Wait for only a system-defined period of time. Deadlock Prevention Order transactions using transaction timestamps. Wait-Die Wound-Wait Deadlock Detection Wait-For Graph 79) How You Can Identify, Prevent & Resolve A Deadlock? Answer:  Detecting and resolving deadlocks as they arise has advantage over taking measures to prevent deadlock, because deadlocks tend to be rare. The lock manager maintains a Waits-For Graph to detect deadlock cycles. DBMS allows deadlock to occur but recognizes occurrences of deadlock and brea...

73) What Do You Mean By Serializability In Transaction Processing? Answer:  The objective of Serializability is to find non-serial schedules that allow transactions to execute concurrently without interfering with one another, and thereby produce a database state that could be produce by a serial execution. Example: Two transaction that read data only. Two transactions that read or update separate data item. 74) What Are The Two Methods That Guarantee Serializability? Answer: The two methods that guarantee Serializability are: Two Phase Locking. Timestamping. 75) What Is Locking? What Are The Types Of Locking? Answer: A procedure used to control concurrent access to data. W hen one transaction is accessing the database, a lock may deny access to other transactions to prevent incorrect results. The two types of locking are: Shared lock: If a transaction has a shared lock on a data item, it can read the item but not update it. Exclusive lock: If a transa...

71) Describe The ACID Properties Of A Transaction? Answer: To ensure integrity of data, the database system must maintain the ACID Properties. Atomicity: A transaction is either performed in its entirety or is not performed at all. Either all operations of the transaction are reflected properly in the database, or none are. It is the responsibility of the recovery subsystem of the DBMS to ensure atomicity. Consistency: A transaction must transform the database from one consistent state to another consistent state. Execution of a transaction in isolation (that is, with no other transaction executing concurrently) preserves the consistency of the database. It is the responsibility of DBMS and the application developer to ensure consistency. Isolation: Transactions execute independently of one another. It is the responsibility of the concurrency control subsystem to ensure isolation. Even though multiple transactions may execute concurrently, the system guarantee...

68) What Are The Lossless Inference Rules For Functional Dependencies (FD's) Or State The Armstrong's Axioms?  Answer:  Inference rules also known as Armstrong's Axioms are published by Armstrong . These properties are as given below: 1. Reflexivity Property: X -> Y is true if Y is subset of X. 2. Augmentation Property: If X -> Y is true, then  XZ -> YZ is also true. 3. Transitivity Property: If X -> Y and Y -> Z then X -> Z is implied. 4. Union Property: If X -> Y and X -> Z are true, then X -> YZ is also true. This property indicates that if right hand side of FD contains many attributes then FD exists for each of them. 5. Decomposition Property: If X -> Y is implied and Z is subset of Y, then X -> Z is implied. This property is the reverse of union property. 6. PseudoTransitivity Property:  If X -> Y and WY -> Z are given, then XW -> Z is true. 69) What Is Domain-Key...

66) What is Fourth (4th) Normal Form (4NF)? Answer: Fourth normal form eliminates independent many-to-one relationships between columns. To be in Fourth Normal Form: A relation must first be in Boyce-Codd Normal Form. A given relation may not contain more than one Multi-valued attribute. 4NF is defined as a relation that is in Boyce-Codd Normal Form and contains no nontrivial multi-valued dependencies. Example:  67) What is Fifth (5th) Normal Form (5NF)? Answer: A relation decomposes into two relations must have the lossless-join property, which ensures that no spurious tuples are generated when relations are reunited through a natural join operation. However, there are requirements to decompose a relation into more than two relations. Although rare, these cases are managed by join dependency and fifth normal form (5NF). ALSO CHECK THE BELOW FAQ's: 68)  What Are The Lossless Inference Rules For Functional Dependencies (FD's) Or Stat...

63) What Is Second (2nd) Normal Form (2NF)? Answer: The purpose of second normal form (2NF) is to eliminate partial key dependencies. Each attribute in an entity must depend on the whole key, not just a part of it. Definition: A relation Schema R is in 2NF if every non-prime attribute A in R is fully functionally dependent on the primary key of R. 64) What Is Third (3rd) Normal Form (3NF)? Answer: Third Normal form also helps to eliminate redundant information by eliminating inter dependencies between non-key attributes. Before proceeding to 3NF, check that it is already in 2NF and there are no non-key attributes that depend on another non-key attribute. 65) What Is Boyce-Codd Normal Form (BCNF)? Also Define Multivalued Dependency? Answer: A relation schema R is in Boyce-Codd Normal Form (BCNF) if whenever an FD X -> A holds in R, then X is a superkey of R. Each normal form is strictly stronger than the previous one: Every 2NF relation...

61)   What Are The Steps Of Normalization? Answer: 62)   What Is First (1st) Normal Form (1NF)? Answer: First normal form (1NF or Minimal Form) is a normal form used in database normalization. A relation R in which the intersection of each row and column contains one and only one value is said to be in 1NF. One has to make sure that the relation should have no non-atomic values. The purpose of first normal form (1NF) is to eliminate repeating groups of attributes in an entity which means disallow composite attributes, multivalued attributes, and nested relations. A table is in 1NF if and only if it satisfies the following conditions: There are no columns with similar or repeated data. Each row is unique i.e. it has a primary key. Each data item cannot be broken down any further. Each field has a unique name. Violation of any of these conditions would mean that the table is not strictly relational, and therefore that it is not in 1NF. The final t...

56) What Is Fully Functional Dependency? Answer: In a relation R, X and Y are attributes. X functionally determines Y. Subset of X should not functionally determine Y.  In the above example marks is fully functionally dependent on student_no and course_no together and not on subset of {student_no, course_no}. This means marks cannot be determined either by student_no or course_no alone. It can be determined only using student_no and course_no together. Hence marks is fully functionally dependent on {student_no, course_no}. 57) What Is Partial Dependency? Answer: Attribute Y is partially dependent on the attribute X only if it is dependent on a subset of attribute X. For example course_name, Instructor_name are partially dependent on composite attributes {student-no, course_no} because course_no alone defines course_name, Instructor_name. 58) What Is Transitive Dependency? Answer: X, Y and Z are 3 attributes in the relation R. Transitive depend...

51) What Is Normalization. Explain It In Detail? Answer:   Normalization is the process of organizing and designing a data model to efficiently store data in a database. The end result is that redundant data is eliminated, and only data related to the attribute is stored within the table. In short, Normalization is a process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations. Normal forms reduce the amount of redundancy and inconsistent dependency within databases. Normalization organizes the data into tables where each item is a row and the attributes of the item are in columns. Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. For example: An employee address change is much easier to implement if that data is stored only in the Employee table and nowhere else in the datab...

46) What Is A Key? What Are Different Types Of Keys In A Database? Answer: A key is nothing but an attribute or group of attributes. Attribute is a particular property, which describes the entity. Attributes are also called columns or fields in DBMS. Keys are used to establish and identify relation between tables. They also ensure that each record within a table can be uniquely identified by combination of one or more fields within a table. Different types of keys in database are primary key, secondary key, alternative key, super key, candidate key, compound or concatenated or composite key, foreign key, etc. 47) What Is A Primary Key & Secondary Key? Answer: An attribute that can be used to identify a record uniquely is considered to be a Primary Key. For example, In the student table roll_no is the primary key because it can be used to identify unique record or unique student. An attribute that can be used to identify a group of records satisfying a given conditio...

41) Define The Types Of Data Integrity Rules? Answer: Data Integrity falls under the following three categories: a) Entity Integrity: Entity integrity ensures that each row can be uniquely identified by an attribute called the Primary key. The Primary key cannot have a NULL value. b ) Domain Integrity: Domain integrity refers to the range of valid entries for a given column. It ensures that there are only valid entries in the column. c) Referential Integrity: Referential integrity ensures that for every value of a Foreign key, there is a matching value of the Primary key. It states that "Foreign Key can be either a NULL value or should be Primary Key value of other relation". 42) What Is A Table? What Is A Tuple? What Is A Relation? Answer: Table is a representation of a relation having records as rows and attributes as columns. Tuple is nothing but a row in a table. A relation consists of a homogeneous set of tuples. Informally a relation is a table....

36) What Is An Extension Of Entity Type? Answer: The collections of entities of a particular entity type are grouped together into an entity set. 37) What Is Weak Entity Set? Answer: An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set. Weak Entity Set is an entity that does not have a key attribute. A weak entity must participate in an identifying relationship type with an owner or identifying entity type. Entities are identified by the combination of: A partial key of the weak entity type The particular entity they are related to in the identifying entity type. EXAMPLE: A DEPENDENT entity is identified by the dependent’s first name, and the specific EMPLOYEE with whom the dependent is related. Name of DEPENDENT is the partial key. DEPENDENT is a weak entity type. EMPLOYEE is its identifying entity type via th...

31) What Is Degree Of A Relation? Answer: It is the number of attribute of its relation schema. 32) Discuss The Properties Of A Relation? Answer: A relation has the following properties: Has a name that is distinct from all other relation names in the relational schema. Each cell contains exactly one atomic (single) value. Each attribute has a distinct name. The values of an attribute are all from the same domain. Each tuple is distinct; there are no duplicate tuples. The order of attributes has no significance. The order of tuples has no significance, theoretically. (However, in practice, the order may affect the efficiency of accessing tuples.) 33) What Is Relationship, Relationship Type & Relationship Set? Answer: Relationship is an association among two or more entities. A relationship relates two or more distinct entities with a specific meaning. For example, EMPLOYEE Rahul Jain works on the XXX PROJECT, or EMPLOYEE Vineet ...