HR & Technical Interview Questions With Answers.

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 break them. Deadl

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 transactio

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 guarantees th

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 Normal Form (DKNF)? Answer: A rel

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 State The

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 is in 1NF. E

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 table is 1

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 dependency describes a co

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 the id

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 Kumar manages the Financ

   26)  What Is An Entity, Entity Type & An Entity Set? Answer: Entity is a 'thing' in the real world with an independent existence. An entity is something which is described in the database by storing its data, it may be a concrete entity a conceptual entity. For Example: Every person in a college is an entity, Every room in a college is an entity. Entity type is a collection (set) of entities that have same attributes. Entities with the same basic attributes are grouped or typed into an entity type. For Example: EMPLOYEE and PROJECT. Entity set is a collection of all entities of particular entity type in the database. Each entity type will have a collection of entities stored in the database, this is called entity set. Example: Consider 3 employees : Rahul, Ajay & Rohit. Rahul, Ajay & Rohit are each categorized as Entity. Employee will be the Entity type. Entity set will be {Rahul, Ajay, Rohit} - i.e. set of instances of entity. 27) W

21) What Is DDL (Data Definition Language), VDL (View Definition Language) and SDL (Storage Definition Language)? Answer: A database schema is specified by a set of definitions expressed by a special language called DDL . It is used by DBA and database designers to specify the conceptual schema of a database. In many DBMSs, the DDL is also used to define internal and external schemas (views). Also in some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schemas. VDL (View Definition Language) specifies user views and their mappings to the conceptual schema. SDL (Storage Definition Language) is to specify the internal schema. This language may specify the mapping between two schemas. SDL is typically realized via DBMS commands provided to the DBA and database designers 22) What Is Data Storage - Definition Language? Answer: The storage structures and access methods used by database sys

16) Discuss About Internal/Physical/Storage Level Of The Architecture? Answer:   The internal view is the view about the actual physical storage of data. It tells us what data is stored in the database and how. 17) What Is Data Independence? Answer: Data independence means that the application is independent of the storage structure and access strategy of data. In other words, the ability to modify the schema definition in one level should not affect the schema definition in the next higher level. There are two types of Data Independence: Physical Data Independence: Modification in physical level should not affect the logical level. Logical Data Independence: Modification in logical level should affect the view level. 18) What Is Physical Data Independence? Answer: The separation of the conceptual view from the internal view enables us to provide a logical description of the database without the need to specify physical structures is often called physical data i