DBMS Interview Questions and Answers
The Best LIVE Mock Interview - You should go through before Interview
Test your skills through the online practice test: DBMS Quiz Online Practice Test
Experienced / Expert level questions & answers
Ques 1. What is normalization? What are different type of normalization?
There is set of rules that has been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.
Benefits of Normalizing your database include:
=>Avoiding repetitive entries
=>Reducing required storage space
=>Preventing the need to restructure existing tables to accommodate new data.
=>Increased speed and flexibility of queries, sorts, and summaries.
Following are the three normal forms :-
First Normal Form:
For a table to be in first normal form, data must be broken up into the smallest un possible.In addition to breaking data up into the smallest meaningful values, tables first normal form should not contain repetitions groups of fields.
Second Normal form:
The second normal form states that each field in a multiple field primary keytable must be directly related to the entire primary key. Or in other words,each non-key field should be a fact about all the fields in the primary key.
Third normal form:
A non-key field should not depend on other Non-key field.
Is it helpful?
Add Comment
View Comments
Ques 2. What is denormalization?
Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance.The sacrifice to performance is that you increase redundancy in database.
Is it helpful?
Add Comment
View Comments
Ques 3. Can you explain Fourth Normal Form and Fifth Normal Form?
In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy “Third Normal form”.
Fifth normal form deals with reconstructing information from smaller pieces of information.
These smaller pieces of information can be maintained with less redundancy.
Is it helpful?
Add Comment
View Comments
Ques 4. Have you heard about sixth normal form?
If we want relational system in conjunction with time we use sixth normal form. At this moment
SQL Server does not supports it directly.
Is it helpful?
Add Comment
View Comments
Ques 5. We have an employee salary table how do we find the second highest from it?
Below Sql Query find the second highest salary:
SELECT * FROM wbEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM wbEmployeeSalary b WHERE b.salary>=a.salary))
Is it helpful?
Add Comment
View Comments
Ques 6. What is “CROSS JOIN”? or What is Cartesian product?
“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.
Is it helpful?
Add Comment
View Comments
Ques 7. Can you explain the SELECT INTO Statement?
SELECT INTO statement is used mostly to create backups. The below SQL backsup the
Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of
wbEmployeeBackup and wbEmployee table should be same. SELECT * INTO
wbEmployeeBackup FROM wbEmployee
Is it helpful?
Add Comment
View Comments
Ques 8. What is SQL injection?
It is a Form of attack on a database-driven Web site in which the attacker executes
unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database.
SQL injection attacks typically are easy to avoid by ensuring that a system has strong input
validation.
As name suggest we inject SQL which can be relatively dangerous for the database. Example this is a simple SQL
SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'
Now somebody does not put “x” as the input but puts “x ; DROP TABLE members;”.
So the actual SQL which will execute is :-
SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' ; DROP TABLE
members;
Think what will happen to your database.
Is it helpful?
Add Comment
View Comments
Ques 9. What is Data Independence?
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.
Two types of Data Independence:
1. Physical Data Independence: Modification in physical level should not affect the logical level.
2. Logical Data Independence: Modification in logical level should affect the view level.
Is it helpful?
Add Comment
View Comments
Ques 10. What is Functional Dependency?
A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.
Is it helpful?
Add Comment
View Comments
Ques 11. 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.
Is it helpful?
Add Comment
View Comments
Ques 12. What is 4NF?
A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true.
1.) X is subset or equal to (or) XY = R.
2.) X is a super key.
Is it helpful?
Add Comment
View Comments
Ques 13. What is 5NF?
A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true
1) Ri = R for some i.
2) The join dependency is implied by the set of FD, over R in which the left side is key of R.
Is it helpful?
Add Comment
View Comments
Ques 14. What is Domain-Key Normal Form?
A relation is said to be in DKNF if all constraints and dependencies that should hold on the the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.
Is it helpful?
Add Comment
View Comments
Ques 15. What are partial, alternate, artificial, compound and natural key?
1. Partial Key: It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.
2. Alternate Key: All Candidate Keys excluding the Primary Key are known as Alternate Keys.
3. Artificial Key: If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.
4. Compound Key: If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.
5. Natural Key: When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.
Is it helpful?
Add Comment
View Comments
Ques 16. What is indexing and what are the different kinds of indexing?
Indexing is a technique for determining how quickly specific data can be found.
Types:
1. Binary search style indexing
2. B-Tree indexing
3. Inverted list indexing
4. Memory resident table
5. Table indexing
Is it helpful?
Add Comment
View Comments
Ques 17. What is system catalog or catalog relation? How is better known as?
A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.
Is it helpful?
Add Comment
View Comments
Ques 18. What is durability in DBMS?
Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.
Is it helpful?
Add Comment
View Comments
Ques 19. 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.
Is it helpful?
Add Comment
View Comments
Ques 20. What is a checkpoint and When does it occur?
A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.
Is it helpful?
Add Comment
View Comments
Ques 21. What is RDBMS KERNEL?
Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrentresource usage; dispatches and schedules user requests; and manages space usage within its table-spacestructures.
Is it helpful?
Add Comment
View Comments
Ques 22. Name the sub-systems of a RDBMS.
I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management.
Is it helpful?
Add Comment
View Comments
Ques 23. Which part of the RDBMS takes care of the data dictionary? How?
Data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the kernel.
Is it helpful?
Add Comment
View Comments
Ques 24. What is the job of the information stored in data-dictionary?
The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.
Is it helpful?
Add Comment
View Comments
Ques 25. 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.
Is it helpful?
Add Comment
View Comments
Ques 26. What is Storage Manager?
It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.
Is it helpful?
Add Comment
View Comments
Ques 27. What is Authorization and Integrity manager?
It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.
Is it helpful?
Add Comment
View Comments
Ques 28. What are stand-alone procedures?
Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution.
Is it helpful?
Add Comment
View Comments
Ques 29. What are cursors give different types of cursors?
PL/SQL uses cursors for all database information accesses statements. The language supports the use two types
of cursors
1.) Implicit
2.) Explicit
Is it helpful?
Add Comment
View Comments
Most helpful rated by users:
- What is database or database management systems (DBMS)?
- What is SQL?
- What's the difference between file and database? Can files qualify as a database?
- What’s difference between DBMS and RDBMS?
- What are E-R diagrams?