Dbms Interview Questions and Answers
1) What is database?
A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images.
2) What is DBMS?
A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.
3) What are the advantages of DBMS?
Controlling Data Redundancy<br/> Sharing of Data<br/> Data Consistency <br/> Integration of Data<br/> Integration Constraints<br/> Data Security<br/> Report Writers<br/> Backup and Recovery Procedures
4) What are the disadvantage in File Processing System?
The file processing system has the following major disadvantages:<br/> Data redundancy and inconsistency.<br/> Integrity Problems.<br/> Security Problems.<br/> Difficulty in accessing data.<br/> Data isolation.
5) Define the "integrity rules" in DBMS?
There are two Integrity rules.<br/> <strong>Entity Integrity: </strong>States that "Primary key cannot have NULL value"<br/> <strong> Referential Integrity:</strong> States that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.
6) What is primary, candidate and foreign keys in DBMS?
<strong>Primary Key:</strong> The attribute or combination of attributes that uniquely identifies a row or record in a relation is known as primary key.<br/> <strong>Candidate Key or Alternate key: </strong>A relation can have only one primary key. It may contain many fields or combination of fields that can be used as primary key. One field or combination of fields is used as primary key. The fields or combination of fields that are not used as primary key are known as candidate key or alternate key.<br/> <strong>Foreign Key:</strong>A foreign key is an attribute or combination of attribute in a relation whose value match a primary key in another relation. The table in which foreign key is created is called as dependent table. The table to which foreign key is refers is known as parent table.
7) What is a super key?
A <strong>superkey </strong>is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.
8) What is the difference between primary key and unique constraints?
<strong>1.</strong>Primary Key constraints are not nullable. UNIQUE constraints may be nullable.<br/> <strong>2.</strong>When you create a UNIQUE constraint, the database automatically creates a UNIQUE index. For MS SQL Server databases, a PRIMARY KEY will generate a unique CLUSTERED INDEX.
9) What is extension and intension in DBMS?
<strong>Extension:</strong> It is the number of tuples present in a table at any instance. This is time dependent. <br/><strong>Intension:</strong> It is a constant value that gives the name, structure of table and the constraints laid on it.
10) What is a view? How it is related to data independence in DBMS?
In a database management system, a view is a way of portraying information in the database. Often thought of as a virtual table, the view doesn't actually store information itself, but just pulls it out of one or more existing tables.
11) What is Data Model in DBMS?
A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized, and manipulated. The most popular example of a database model is the relational model, which uses a table-based format.
12) What is E-R model in DBMS?
An entity relationship model, also called an entity-relationship (ER) diagram, is a graphical representation of entities and their relationships to each other, typically used in computing in regard to the organization of data within databases or information systems.
13) What is Object Oriented model in DBMS?
<strong>Object-oriented modeling (OOM) </strong>is the construction of objects using a collection of objects that contain stored values of the instance variables found within an object. Unlike models that are record-oriented, object-oriented values are solely objects.
14) What is an Entity?
An entity can be a real-world object, either animate or inanimate, that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity.
15) What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.
16) What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may specify the mapping between two schemas.
17) What is DML (Data Manipulation Language)?
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: <strong>SELECT, UPDATE, INSERT </strong>statements. DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.
18) What is DML Compiler in DBMS?
It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand. What is Query evaluation engine? It executes low-level instruction generated by compiler.
19) What is normalization in DBMS?
Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
20) What is 1 NF (Normal Form)?
The domain of attribute must include only atomic (simple, indivisible) values.
21) What is Fully Functional dependency?
It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.
22) What is 2NF?
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.
23) What is 3NF?
A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true<br> X is a Super-key of R.<br> A is a prime attribute of R.<br> In other words, if every non prime attribute is non-transitively dependent on primary key.
24) What is BCNF (Boyce-Codd Normal Form)?
A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.
25) What is indexing and what are the different kinds of indexing?
Indexing is a way of sorting a number of records on multiple fields. Creating an index on a field in a table creates another data structure which holds the field value, and pointer to the record it relates to. This index structure is then sorted, allowing Binary Searches to be performed on it. Types:<br> <strong>1. </strong>Binary search style indexing<br/> <strong>2.</strong> B-Tree indexing<br/> <strong>3. </strong>Inverted list indexing<br/> <strong>4.</strong> Memory resident table<br/> <strong>5.</strong> Table indexing
26) What is meant by query optimization?
A query optimizer is a critical database management system (DBMS) component that analyzes Structured Query Language (SQL) queries and determines efficient execution mechanisms. A query optimizer generates one or more query plans for each query, each of which may be a mechanism used to run a query.
27) What is durability in DBMS?
In database systems, durability is the ACID property which guarantees that transactions that have committed will survive permanently. For example, if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system crashes.
28) What do you mean by atomicity and aggregation?
<strong>Atomicity: </strong>Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions. <br/><strong>Aggregation:</strong> A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.
29) What is a Phantom Deadlock?
In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.
30) What are the different states of transaction?
A transaction in a database can be in one of the following states ?<br/> <strong>Active</strong> ? In this state, the transaction is being executed. This is the initial state of every transaction.<br/> <strong>Partially Committed </strong>? When a transaction executes its final operation, it is said to be in a partially committed state.<br/> <strong>Failed</strong> ? A transaction is said to be in a failed state if any of the checks made by the database recovery system fails. A failed transaction can no longer proceed further.<br/> <strong>Aborted</strong> ? If any of the checks fails and the transaction has reached a failed state, then the recovery manager rolls back all its write operations on the database to bring the database back to its original state where it was prior to the execution of the transaction. Transactions in this state are called aborted. The database recovery module can select one of the two operations after a transaction aborts ?<br/> Re-start the transaction<br> Kill the transaction<br/> <strong>Committed</strong> ? If a transaction executes all its operations successfully, it is said to be committed. All its effects are now permanently established on the database system.
31) What is a query?
A query is a request for data or information from a database table or combination of tables. This data may be generated as results returned by Structured Query Language (SQL) or as pictorials, graphs or complex results, e.g., trend analyses from data-mining tools.
32) What is the difference between having and where clause?
<strong>HAVING</strong> is used when you are using an aggregate such as GROUP BY . The difference between the two is in the relationship to the GROUP BY clause: WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records. <br/><strong>HAVING</strong> comes after GROUP BY; SQL evaluates HAVING after it groups records.
33) What is Join?
An <strong>SQL JOIN</strong> clause is used to combine rows from two or more tables, based on a common field between them.<br/> <strong>INNER JOIN:</strong> Returns all rows when there is at least one match in BOTH tables<br/> <strong>LEFT JOIN:</strong> Return all rows from the left table, and the matched rows from the right table<br/> <strong>RIGHT JOIN:</strong> Return all rows from the right table, and the matched rows from the left table<br/> <strong>FULL JOIN: </strong>Return all rows when there is a match in ONE of the tables
34) What is database Trigger?
A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.
35) What is a stored procedure?
A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.
36) What is the difference between Trigger and Stored Procedure?
A stored procedure is a user defined piece of code written in the local version of PL/SQL, which may return a value (making it a function) that is invoked by calling it explicitly.