DBMS Interview Questions and Answers [ TO GET HIRED ]

Last updated on 18th Nov 2021, Blog, Interview Questions

In this article, we will discuss the most important DBMS Interview Questions in order to make you familiar with the type of questions that can be asked during a job interview related to the Database Management System (DBMS). Basically, out of my personal experience, there is not any specific way or formula to get through an Interview Process and it totally depends on you and the type of Interviewer. But still, it’s good to be prepared on your profile for your own confidence and knowledge so that you don’t feel hesitant to answer the questions asked during an interview.

1. What is DBMS?

Ans:

DBMS is a collection of programs that facilitates users to create and maintain a database. In other words, DBMS provides us an interface or tool for performing different operations such as the creation of a database, inserting data into it, deleting data from it, updating the data, etc. DBMS is a software in which data is stored in a more secure way as compared to the file-based system. Using DBMS, we can overcome many problems such as- data redundancy, data inconsistency, easy access, more organized and understandable, and so on. There is the name of some popular Database Management System- MySQL, Oracle, SQL Server, Amazon simple DB (Cloud-based) , etc.

2. What is a database?

Ans:

A Database is a logical, consistent and organized collection of data that can easily be accessed, managed and updated. Databases, also known as electronic databases are structured to provide the facility of creation, insertion, updating of the data efficiently and are stored in the form of a file or set of files, on the magnetic disk, tapes and other sort of secondary devices. Database mostly consists of the objects (tables. , and tables include the records and fields. Fields are the basic units of data storage, which contain the information about a particular aspect or attribute of the entity described by the database. DBMS is used for extraction of data from the database in the form of the queries.

3. What is a database system?

Ans:

The collection of database and DBMS software together is known as a database system. Through the database system, we can perform many activities such as- The data can be stored in the database with ease, and there are no issues of data redundancy and data inconsistency.

The data will be extracted from the database using DBMS software whenever required. So, the combination of database and DBMS software enables one to store, retrieve and access data with considerate accuracy and security.

4. What are the advantages of DBMS?

Ans:

• Redundancy control
• Restriction for unauthorized access
• Provides multiple user interfaces
• Provides backup and recovery
• Enforces integrity constraints
• Ensure data consistency
• Easy accessibility
• Easy data extraction and data processing due to the use of queries

5. What is a checkpoint in DBMS?

Ans:

The Checkpoint is a type of mechanism where all the previous logs are removed from the system and permanently stored in the storage disk.

There are two ways which can help the DBMS in recovering and maintaining the ACID properties, and they are- maintaining the log of each transaction and maintaining shadow pages. So, when it comes to log based recovery systems, checkpoints come into existence. Checkpoints are those points to which the database engine can recover after a crash as a specified minimal point from which the transaction log record can be used to recover all the committed data up to the point of the crash.

6. When does a checkpoint occur in DBMS?

Ans:

A checkpoint is like a snapshot of the DBMS state. Using checkpoints, the DBMS can reduce the amount of work to be done during a restart in the event of subsequent crashes. Checkpoints are used for the recovery of the database after the system crash. Checkpoints are used in the log-based recovery system. When due to a system crash we need to restart the system then at that point we use checkpoints. So that, we don’t have to perform the transactions from the very starting.

7. What do you mean by transparent DBMS?

Ans:

The transparent DBMS is a type of DBMS which keeps its physical structure hidden from users. Physical structure or physical storage structure implies to the memory manager of the DBMS, and it describes how the data is stored on disk.

8. What are the unary operations in Relational Algebra?

Ans:

PROJECTION and SELECTION are the unary operations in relational algebra. Unary operations are those operations which use single operands. Unary operations are SELECTION, PROJECTION, and RENAME.

As in SELECTION relational operators are used for example – =,<=,>=, etc.

9. What is RDBMS?

Ans:

RDBMS stands for Relational Database Management Systems. It is used to maintain the data records and indices in tables. RDBMS is the form of DBMS which uses the structure to identify and access data concerning the other piece of data in the database. RDBMS is the system that enables you to perform different operations such as- update, insert, delete, manipulate and administer a relational database with minimal difficulties. Most of the time RDBMS uses SQL language because it is easily understandable and is used often.

10. How many types of database languages are there?

Ans:

There are four types of database languages :

• Data Definition Language (DDL) e.g., CREATE, ALTER, DROP, TRUNCATE, RENAME, etc. All these commands are used for updating the data that?s why they are known as Data Definition Language.
• Data Manipulation Language (DML. e.g., SELECT, UPDATE, INSERT, DELETE, etc. These commands are used for the manipulation of already updated data that’s why they are the part of Data Manipulation Language.
• DATA Control Language (DCL. e.g., GRANT and REVOKE. These commands are used for giving and removing the user access to the database. So, they are part of the Data Control Language.
• Transaction Control Language (TCL. e.g., COMMIT, ROLLBACK, and SAVEPOINT. These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML.
Database language implies the queries that are used for the update, modify and manipulate the data.

11. What do you understand about the Data Model?

Ans:

The Data model is specified as a collection of conceptual tools for describing data, data relationships, data semantics and constraints. These models are used to describe the relationship between the entities and their attributes.

There is the number of data models :

• Hierarchical data model
• network model
• relational model
• Entity-Relationship model and so on.

12. Define a Relation Schema and a Relation.

Ans:

A Relation Schema is specified as a set of attributes. It is also known as table schema. It defines what the name of the table is. Relation schema is known as the blueprint with the help of which we can explain how the data is organized into tables. This blueprint contains no data.

A relation is specified as a set of tuples. A relation is the set of related attributes with identifying key attributes

See this example :

Ans:

Let r be the relation which contains set tuples (t1, t2, t3, …, tn) . Each tuple is an ordered list of n-values t=(v1,v2, …., vn).

13. What is a degree of Relation?

Ans:

The degree of relation is a number of attributes of its relation schema. A degree of relation is also known as Cardinality; it is defined as the number of occurrences of one entity which is connected to the number of occurrences of another entity. There are three degrees of relation; they are one-to-one(1:1. , one-to-many(1:M. , and many-to-one(M:M) .

14. What is a Relationship?

Ans:

The Relationship is defined as an association among two or more entities. There are three type of relationships in DBMS –

• One-To-One : Here one record of any object can be related to one record of another object.
• One-To-Many (many-to-one) : Here one record of any object can be related to many records of other objects and vice versa.
• Many-to-many : Here more than one record of an object can be related to a number of records of another object.

15. What are the disadvantages of file processing systems?

Ans:

• Inconsistent
• Not secure
• Data redundancy
• Difficult in accessing data
• Data isolation
• Data integrity
• Concurrent access is not possible
• Limited data sharing
• Atomicity problem

16. What is data abstraction in DBMS?

Ans:

Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures, it makes the user interact with the database.

For example: We know that most of the users prefer those systems which have a simple GUI that means no complex processing. So, to keep the user tuned and to make the access to the data easy, it is necessary to do data abstraction. In addition to it, data abstraction divides the system in different layers to make the work specified and well defined.

17. What are the three levels of data abstraction?

Ans:

Following are three levels of data abstraction :

• Physical level: It is the lowest level of abstraction. It describes how data is stored.
• Logical level: It is the next higher level of abstraction. It describes what data is stored in the database and what the relationship among those data is.
• View level: It is the highest level of data abstraction. It describes only part of the entire database.

For example- User interacts with the system using the GUI and fills the required details, but the user doesn’t have any idea how the data is being used. So, the abstraction level is entirely high in VIEW LEVEL.

Then, the next level is for PROGRAMMERS as in this level the fields and records are visible and the programmers have the knowledge of this layer. So, the level of abstraction here is a little low in VIEW LEVEL.

And lastly, the physical level in which storage blocks are described.

18. What is DDL (Data Definition Language. ?

Ans:

Data Definition Language (DDL. is a standard for commands which defines the different structures in a database. Most commonly DDL statements are CREATE, ALTER, and DROP. These commands are used for updating data into the database.

19. What is DML (Data Manipulation Language. ?

Ans:

DData Manipulation Language (DML) is a language that enables the user to access or manipulate data as organized by the appropriate data model. For example- SELECT, UPDATE, INSERT, DELETE.

There is two type of DML :

• Procedural DML or Low level DML : It requires a user to specify what data are needed and how to get that data.
• Non-Procedural DML or High level DML : It requires a user to specify what data are needed without specifying how to get that data.

20. Explain the functionality of DML Compiler.

Ans:

The DML Compiler translates DML statements in a query language that the query evaluation engine can understand. A DML Compiler is required because the DML is the family of syntax elements which is very similar to the other programming language which requires compilation. So, it is essential to compile the code in the language which the query evaluation engine can understand and then work on those queries with proper output.

21. What is Relational Algebra?

Ans:

Relational Algebra is a Procedural Query Language which contains a set of operations that take one or two relations as input and produce a new relationship. Relational algebra is the basic set of operations for the relational model. The decisive point of relational algebra is that it is similar to the algebra which operates on the number.

There are few fundamental operations of relational algebra :

• select
• project
• set difference
• union
• rename,etc.

22. What is Relational Calculus?

Ans:

Relational Calculus is a Non-procedural Query Language which uses mathematical predicate calculus instead of algebra. Relational calculus doesn’t work on mathematics fundamentals such as algebra, differential, integration, etc. That’s why it is also known as predicate calculus.

There is two type of relational calculus :

• Tuple relational calculus
• Domain relational calculus

23. What do you understand by query optimization?

Ans:

The term query optimization specifies an efficient execution plan for evaluating a query that has the least estimated cost. The concept of query optimization came into the frame when there were a number of methods, and algorithms existed for the same task then the question arose which one is more efficient and the process of determining the efficient way is known as query optimization.

There are many benefits of query optimization :

• It reduces the time and space complexity.
• More queries can be performed as due to optimization every query comparatively takes less time.
• User satisfaction as it will provide output fast

24. What do you mean by durability in DBMS?

Ans:

Once the DBMS informs the user that a transaction has completed successfully, its effect should persist even if the system crashes before all its changes are reflected on disk. This property is called durability. Durability ensures that once the transaction is committed into the database, it will be stored in the non-volatile memory and after that system failure cannot affect that data anymore.

25. What is normalization?

Ans:

Analysing the given relation schemas according to their functional dependencies. It is used to minimize redundancy and also used to minimize insertion, deletion and update distractions. Normalization is considered as an essential process as it is used to avoid data redundancy, insertion anomaly, updation anomaly, deletion anomaly.

There most commonly used normal forms are :

• First Normal Form(1NF)
• Second Normal Form(2NF)
• Third Normal Form(3NF)
• Boyce & Codd Normal Form(BCNF)

26. What is Denormalization?

Ans:

Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data. Denormalization is a part of database optimization technique. This process is used to avoid the use of complex and costly joins. Denormalization doesn’t refer to the thought of not to normalize instead denormalization takes place after normalization. In this process, firstly the redundancy of the data will be removed using the normalization process then through the denormalization process we will add redundant data as per the requirement so that we can easily avoid the costly joins.

27. What is functional Dependency?

Ans:

Functional Dependency is the starting point of normalization. It exists when a relation between two attributes allows you to determine the corresponding attribute’s value uniquely. The functional dependency is also known as database dependency and defines as the relationship which occurs when one attribute in a relation uniquely determines another attribute. It is written as A->B which means B is functionally dependent on A.

28. What is the E-R model?

Ans:

E-R model is a short name for the Entity-Relationship model. This model is based on the real world. It contains necessary objects (known as entities) and the relationship among these objects. Here the primary objects are the entity, attribute of that entity, relationship set, an attribute of that relationship set can be mapped in the form of E-R diagram.

In the E-R diagram, entities are represented by rectangles, relationships are represented by diamonds, attributes are the characteristics of entities and represented by ellipses, and data flow is represented through a straight line.

29. What is an entity?

Ans:

The Entity is a set of attributes in a database. An entity can be a real-world object which physically exists in this world. All the entities have their attributes which in the real world are considered as the characteristics of the object.

For example : In the employee database of a company, the employee, department, and the designation can be considered as the entities. These entities have some characteristics which will be the attributes of the corresponding entity.

30. What is an Entity type?

Ans:

An entity type is specified as a collection of entities, having the same attributes. Entity type typically corresponds to one or several related tables in the database. A characteristic or trait which defines or uniquely identifies the entity is called entity type.

For example, a student has student_id, department, and course as its characteristics.

31. What is an Entity set?

Ans:

The entity set specifies the collection of all entities of a particular entity type in the database. An entity set is known as the set of all the entities which share the same properties.

For example, a set of people, a set of students, a set of companies, etc..

32. What is an Extension of entity type?

Ans:

An extension of an entity type is specified as a collection of entities of a particular entity type that are grouped into an entity set.

33. What is a Weak Entity set?

Ans:

An entity set that doesn’t have sufficient attributes to form a primary key is referred to as a weak entity set. The member of a weak entity set is known as a subordinate entity. Weak entity set does not have a primary key, but we need a means to differentiate among all those entries in the entity set that depend on one particular strong entity set.

34. What is an attribute?

Ans:

An attribute refers to a database component. It is used to describe the property of an entity. An attribute can be defined as the characteristics of the entity. Entities can be uniquely identified using the attributes. Attributes represent the instances in the row of the database.

For example : If a student is an entity in the table then age will be the attribute of that student.

35. What are the integrity rules in DBMS?

Ans:

Data integrity is one significant aspect while maintaining the database. So, data integrity is enforced in the database system by imposing a series of rules. That set of integrity is known as the integrity rules.

There are two integrity rules in DBMS :

• Entity Integrity : It specifies that “Primary key cannot have a NULL value.”
• Referential Integrity : It specifies that “Foreign Key can be either a NULL value or should be the Primary Key value of other relation

36. What do you mean by extension and intension?

Ans:

Extension : The Extension is the number of tuples present in a table at any instance. It changes as the tuples are created, updated and destroyed. The actual data in the database changes quite frequently. So, the data in the database at a particular moment in time is known as extension or database state or snapshot. It is time dependent.

Intension : Intension is also known as Data Schema and defined as the description of the database, which is specified during database design and is expected to remain unchanged. The Intention is a constant value that gives the name, structure of tables and the constraints laid on it.

37. What is System R? How many of its two major subsystems?

Ans:

System R was designed and developed from 1974 to 1979 at IBM San Jose Research Centre. System R is the first implementation of SQL, which is the standard relational data query language, and it was also the first to demonstrate that RDBMS could provide better transaction processing performance. It is a prototype which is formed to show that it is possible to build a Relational System that can be used in a real-life environment to solve real-life problems.

Following are two major subsystems of System R :

• Research Storage
• System Relational Data System

38. What is Data Independence?

Ans:

Data independence specifies that “the application is independent of the storage structure and access strategy of data.” It makes you able to modify the schema definition at one level without altering the schema definition in the next higher level.

It makes you able to modify the schema definition in one level and should not affect the schema definition in the next higher level.

There are two types of Data Independence :

• Physical Data Independence: Physical data is the data stored in the database. It is in the bit-format. Modification at the physical level should not affect the logical level.

For example : If we want to manipulate the data inside any table that should not change the format of the table.

• Logical Data Independence: Logical data in the data about the database. It basically defines the structure. Such as tables stored in the database. Modification at the logical level should not affect the view level.

For example : If we need to modify the format of any table, that modification should not affect the data inside it.

39. What are the challenges of managing big data?

Ans:

Those who deal with analytics may be excited by the promise of insight and business intelligence that comes from big data, but those who manage databases face the challenges that come along with managing overall growth and data types from an increasing number of database platforms.

40. What is Join?

Ans:

The Join operation is one of the most useful activities in relational algebra. It is most commonly used way to combine information from two or more relations. A Join is always performed on the basis of the same or related column. Most complex queries of SQL involve JOIN command.

There are following types of join :

Inner joins: Inner join is of 3 categories. They are :

• Theta join
• Natural join
• Equi join

Outer joins : Outer joins have three types. They are :

• Left outer join
• Right outer join
• Full outer join

41. What is 1NF?

Ans:

1NF is the First Normal Form. It is the simplest type of normalization that you can implement in a database. The primary objectives of 1NF are to :

• Every column must have atomic (single value.
• To Remove duplicate columns from the same table
• Create separate tables for each group of related data and identify each row with a unique column

42. What is 2NF?

Ans:

2NF is the Second Normal Form. A table is said to be 2NF if it follows the following conditions :

• The table is in 1NF, i.e., firstly it is necessary that the table should follow the rules of 1NF.
• Every non-prime attribute is fully functionally dependent on the primary key, i.e., every non-key attribute should be dependent on the primary key in such a way that if any key element is deleted, then even the non_key element will still be saved in the database.

43. What is 3NF?

Ans:

3NF stands for Third Normal Form. A database is called in 3NF if it satisfies the following conditions :

• It is in its second normal form.
• There is no transitive functional dependency.
• For example : X->Z
• Where :
• X->Y
• Y does not -> X
• Y->Z so, X->Z

44. What is BCNF?

Ans:

BCMF stands for Boyce-Codd Normal Form. It is an advanced version of 3NF, so it is also referred to as 3.5NF. BCNF is stricter than 3NF.

A table complies with BCNF if it satisfies the following conditions :

• It is in 3NF.
• For every functional dependency X->Y, X should be the super key of the table. It merely means that X cannot be a non-prime attribute if Y is a prime attribute.

45. Explain ACID properties

Ans:

ACID properties are some basic rules, which have to be satisfied by every transaction to preserve the integrity. These properties and rules are :

ATOMICITY : Atomicity is more generally known as the ‘all or nothing rule.’ Which implies all are considered as one unit, and they either run to completion or not executed at all.

CONSISTENCY : This property refers to the uniformity of the data. Consistency implies that the database is consistent before and after the transaction.

ISOLATION : This property states that the number of the transaction can be executed concurrently without leading to the inconsistency of the database state.

DURABILITY : This property ensures that once the transaction is committed it will be stored in the non-volatile memory and system crash can also not affect it anymore.

46. What is the stored procedure?

Ans:

A stored procedure is a group of SQL statements that have been created and stored in the database. The stored procedure increases the reusability as here the code or the procedure is stored into the system and used again and again that makes the work easy, takes less time in processing and decreases the complexity of the system. So, if you have a code which you need to use again and again then save that code and call that code whenever it is required.

47. What is the difference between a DELETE command and TRUNCATE command?

Ans:

DELETE command: DELETE command is used to delete rows from a table based on the condition that we provide in a WHERE clause.

• DELETE command delete only those rows which are specified with the WHERE clause.
• The DELETE command can be rolled back.
• The DELETE command maintains a log, that’s why it is slow.
• DELETE use row lock while performing DELETE function.



TRUNCATE command: TRUNCATE command is used to remove all rows (complete data) from a table. It is similar to the DELETE command with no WHERE clause.

• The TRUNCATE command removes all the rows from the table.
• The TRUNCATE command cannot be rolled back.
• The TRUNCATE command doesn’t maintain a log. That’s why it is fast.
• TRUNCATE uses table log while performing the TRUNCATE function.

48. What is 2-Tier architecture?

Ans:

The 2-Tier architecture is the same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side.

49. What is the 3-Tier architecture?

Ans:

The 3-Tier architecture contains another layer between the client and server. Introduction of 3-tier architecture is for the ease of the users as it provides the GUI, which makes the system secure and much more accessible. In this architecture, the application on the client-end interacts with an application on the server which further communicates with the database system.

50. How do you communicate with an RDBMS?

Ans:

You have to use Structured Query Language (SQL) to communicate with the RDBMS. Using queries of SQL, we can give the input to the database and then after processing of the queries the database will provide us the required output.

51. What is the difference between a shared lock and exclusive lock?

Ans:

• Shared lock : Shared lock is required for reading a data item. In the shared lock, many transactions may hold a lock on the same data item. When more than one transaction is allowed to read the data items then that is known as the shared lock.
• Exclusive lock : When any transaction is about to perform the write operation, then the lock on the data item is an exclusive lock. Because, if we allow more than one transaction then that will lead to the inconsistency in the database.

52. Describe the types of keys?

Ans:

There are following types of keys :

• Primary key : The Primary key is an attribute in a table that can uniquely identify each record in a table. It is compulsory for every table.
• Candidate key : The Candidate key is an attribute or set of an attribute which can uniquely identify a tuple. The Primary key can be selected from these attributes.
• Super key : The Super key is a set of attributes which can uniquely identify a tuple. Super key is a superset of the candidate key.
• Foreign key : The Foreign key is a primary key from one table, which has a relationship with another table. It acts as a cross-reference between tables.

53. What are the differences between a DBMS and RDBMS?

Ans:

DBMS	RDBMS
Provides an organized way of managing, retrieving, and storing from a collection of logically related information	Provides the same as that of DBMS, but it provides relational integrity

54. What do you understand about aggregation and atomicity?

Ans:

Aggregation	Atomicity
This is a feature of the E-R model which allows a relationship set to participate in another relationship set.	This property states that a database modification must either follow all the rules or nothing at all. So, if one part of the transaction fails, then the entire transaction fails.

55.What do you understand by intension and extension?

Ans:

• Intension: Intension or most commonly known as Database schema defines the description of the database. This is specified during the database design and mostly remains unchanged.
• Extension: Extension is the number of tuples available in the database at any instance of time. This value keeps changing as and when the tuples are created, updated and destroyed. So, the data present in the database at a specific instance of time is known as the extension of the database or most commonly known as the snapshot of the database.

56. What is concurrency control?

Ans:

This is a process of managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control :

• Optimistic approach – Involves versioning
• Pessimistic approach – Involves locking

57. What are the different types of normalization?

Ans:

The following are the first three normal forms. Apart from these, you have higher normal forms such as BCNF :

First Normal Form (1NF) – No repeating groups within rows

Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.

Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting column value)

58. What do you understand by correlated subqueries in DBMS?

Ans:

A correlated subquery is also a sort of subquery reliant on another query. So, when subqueries are executed for each of the rows of outer queries, then they are termed as correlated subqueries. Each subquery is executed a single time for every row of the outer query.

You can also understand correlated subqueries as those queries, which are used for row-by-row processing by the parent statement. Here, the parent statement can be a SELECT, UPDATE or DELETE statement.

59. Explain Database partitioning and its importance.

Ans:

Data partitioning is the process of dividing a logical database into independent units for the betterment of availability, performance, and manageability.

60. What do you understand about functional dependency and transitive dependency in DBMS?

Ans:

Functional Dependency: A functional dependency is a constraint that is used in describing the relationship among different attributes in a relation.

Example : Consider a relation “A1” having attributes X and Y. The functional dependency among these two attributes will be X -> Y, this implies that Y is functionally dependent on X.




Transitive Dependency: A transitive dependency is a constraint that can only occur in a relation of three or more attributes.

Example : Consider a relation “A1” having attributes X, Y and Z. Now, X->Z is said to hold transitive dependency, only if the following functional dependencies holds true :

X -> Y

Y doesn’t ->X

Y -> Z

61. What is the difference between two and three-tier architectures?

Ans:

Two-tier architecture	Three-tier architecture
This is similar to the client-server architecture.	This architecture contains an extra layer between the client and the server.
Clients directly communicate with the database at the server-side	Clients communicate with an application(GUI. on the server-side, that makes the system more secure and accessible. This application thereafter communicates with the database system.

62. When was the first database management system created?

Ans:

History of database management systems The first DBMS was developed in the early 1960s when Charles Bachman created a navigational DBMS known as the Integrated Data Store (IDS. . Shortly after, IBM developed Information Management System (IMS. , a hierarchical DBMS designed for IBM mainframes that’s still used by many large organizations today.

63. Mention the differences between Trigger and Stored Procedures

Ans:

64. What are the differences between Hash join, Merge join and Nested loops?

Ans:

Hash join	Merge join	Nested loops
The hash join is used when you have to join large tables.	Merge join is used when projections of the joined tables are sorted on the join columns.	The nested loop consists of an outer loop and an inner loop.

65. What do you understand about Proactive, Retroactive and Simultaneous Update?

Ans:

• Proactive Update : These updates are applied to the database before it becomes effective in the real-world environment.
• Retroactive Update : These retroactive updates are applied to a database after it becomes effective in the real-world environment.
• Simultaneous Update : These updates are applied to the database at the same instance of time as it becomes effective in a real-world environment.

66. What are indexes? Mention the differences between the clustered and non-clustered index

Ans:

Indexes are data structures responsible for improving the speed of data retrieval operations on a table. This data structure uses more storage space to maintain extra copies of data by using additional writes. So, indexes are mainly used for searching algorithms, where you wish to retrieve data in a quick manner.

The differences between clustered and non-clustered index are as follows :

67. What do you understand by cursor? Mention the different types of cursor

Ans:

A cursor is a database object which helps in manipulating data, row by row and represents a result set.

The types of cursor are as follows :

• Implicit cursor: This type of cursor is declared automatically as soon as the execution of SQL takes place. Here, the user is not indicated about the declaration of the cursor.
• Explicit cursor: This type of cursor is defined by the PL/ SQL, as it handles a query in more than a single row.

68. Explain the terms specialization and generalization

Ans:

• Specialization : Specialization is a process of defining a set of subclasses of the entity type. Here, each subclass will contain all the attributes and relationships of the parent entity. Apart from this, the subclasses may contain additional attributes and relationships specific to itself.
• Generalization : Generalization is a process of finding relations, common attributes for a particular set of entities; and finally defining a common superclass for them.

69. What do you understand about Data Independence?

Ans:

When you say an application has data independence, it implies that the application is independent of the storage structure and data access strategies of data.

70. What are the different integrity rules present in the DBMS?

Ans:

The different integrity rules present in DBMS are as follows :

• Entity Integrity: This rule states that the value of the primary key can never be NULL. So, all the tuples in the column identified as the primary key should have a value.
• Referential Integrity: This rule states that either the value of the foreign key is NULL or it should be the primary key of any other relation.

71. What does the Fill Factor concept mean with respect to indexes?

Ans:

Fill Factor is used to mention the percentage of space left on every leaf-level page, which is packed with data. Usually, the default value is 100.

72. What is Index hunting and how does it help in improving query performance?

Ans:

The process of boosting a collection of indexes is known as Index hunting. This is done as indexes improve the query performance and the speed at which they are processed.

It helps in improving query performance in the following way :

• The best queries are suggested using the query optimizer.
• Index, query distribution and their performance are used as metrics to check the effect.
• Databases are tuned into a small collection of problem queries.

73. What are the differences between network and hierarchical database models?

Ans:

74. Explain what is a deadlock and mention how it can be resolved?

Ans:

Deadlock is a situation which occurs when two transactions wait on a resource which is locked or other transaction holds. Deadlocks can be prevented by making all the transactions acquire all the locks at the same instance of time. So, once a deadlock occurs, the only way to cure it is to abort one of the transactions and remove the partially completed work.

75. What are the differences between an exclusive lock and a shared lock?

Ans:

Exclusive Lock : An exclusive lock is a lock on a data item when a transaction is about to perform the write operation.

Shared Lock : A shared lock allows more than one transaction to read the data items.

76. What are the differences between DROP, TRUNCATE and DELETE commands?

Ans:

77. What are the different types of DBMS’s?

Ans:

The data stored in the database can be retrieved, deleted and modified based on the client’s requirement. The different types of DBMS are as follows: Relational DBMS (RDBMS. : This type of DBMS uses a structure which allows the users to access data in relation to another piece of data in a database.

78. What does DML stand for in a DBMS interview?

Ans:

DML: DML stands for data manipulation language; it has been used for manipulating data available in one single database by using some key SQL queries INSERT or DELETE. Other sets of SQL queries can also be considered as DML like SELECT, DELETE, INSERT or UPDATE.

79. Which is the most common type of DBMS?

Ans:

The most widely commonly used type of DBMS today is the Relational Database Management Systems (RDBMS. . Some Database Management Systems can be accessed directly using programming languages such as COBOL while others provide their own programming language for interacting with the database.

80. When was the database management system ( DBMS . first used?

Ans:

The term DBMS has been around since the 1960s when IBM developed the first DBMS model called Information Management System, in which data was stored in a computer in a hierarchical tree structure. Individual pieces of data were connected only between parent and child records.

62. Mention the differences between Unique Key and Primary Key

Ans:

Unique Key	Primary Key
Unique Key can have a NULL value.	The primary key cannot have a NULL value.
Each table can have more than one unique key	Each table can have only one primary key

82. What do you need to know about DBMS’s?

Ans:

History of DBMS. Introduction. A DBMS (Database management system. is used to create and maintain the structure of a database, and then to enter, manipulate and retrieve the data it stores. Creating an efficient database design is the key to effectively using a database to support an organization’s business operations(1. .

83. Which is the best example of a database management system?

Ans:

A database management system (DBMS) is a computer program designed to manage a large amount of structured data, and run operations on the desired data requested by the users. The best example of DBMS is Banking.

84. Who was the first person to create a DBMS?

Ans:

Here are the important landmarks from history: 1960 – Charles Bachman designed first DBMS system 1976- Peter Chen coined and defined the Entity-relationship model also known as the ER model 1985- Object-oriented DBMS developed. 1990s- Incorporation of object-orientation in relational DBMS.

85. What are some examples of database management systems?

Ans:

Some examples of database management system software are Oracle, Microsoft SQL Server, PostgreSQL, and MySQL. They are all examples of relational database management systems. There are also non-relational database management systems and MongoDB is an example of one of those.

86. What are some examples of data management systems?

Ans:

The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.

87. What is the best database management software?

Ans:

Oracle database is the most widely used object-relational database management software. The latest version of this tool is 12c where c means cloud computing. It supports multiple Windows, UNIX , and Linux versions.

88. What is the first database system?

Ans:

A database, as a collection of information, can be organized so a Database Management System can access and pull specific information. In 1960, Charles W. Bachman designed the Integrated Database System, the “first” DBMS. IBM, not wanting to be left out, created a database system of their own, known as IMS.

89. When was the first relational database system created?

Ans:

Two major relational database system prototypes were created between the years 1974 and 1977, and they were the Ingres, which was developed at UBC, and System R, created at IBM San Jose. Ingres used a query language known as QUEL, and it led to the creation of systems such as Ingres Corp., MS SQL Server, Sybase, Wang’s PACE, and Britton-Lee.

90. When did databases become dominant in Computer Science?

Ans:

Often the term “database” is also used to loosely refer to any of the DBMS, the database system or an application associated with the database. Computer scientists may classify database-management systems according to the database models that they support. Relational databases became dominant in the 1980s.

91. What was the evolution of the database system?

Ans:

The Evolution of Database. From pre-stage flat-file systems, to relational and object-relational systems, database technology has gone through several generations and its history is spread over more than 40 years now. The Evolution : 1968 File-Based: predecessor of database, Data was maintained in a flat file.

92. How long has the evolution of databases lasted?

Ans:

From pre-stage flat-file systems, to relational and object-relational systems, database technology has gone through several generations and its history is spread over more than 40 years now. The Evolution : 1968 File-Based: predecessor of database, Data was maintained in a flat file.

93. What was the predecessor of the relational database?

Ans:

From pre-stage flat-file systems, to relational and object-relational systems, database technology has gone through several generations and its history is spread over more than 40 years now. 1968 File-Based: predecessor of database, Data was maintained in a flat file.

94. What was the first hierarchical database in the world?

Ans:

[1968-1980] Era of Hierarchical Database: Prominent hierarchical database model was IBM’s first DBMS called IMS ( Information Management System . . Hierarchical Data Model:

95. How are Records arranged in a hierarchical database?

Ans:

Hierarchical Database Model, as the name suggests, is a database model in which the data is arranged in a hierarchical tree edifice. As it is arranged based on the hierarchy, every record of the data tree should have at least one parent, except for the child records in the last level, and each parent should have one or more child records.

96. What’s the difference between hierarchical and network databases?

Ans:

this model allows relationships, and it has a “schema” (a diagrammatic representation of the relationships. The main difference between the hierarchical model and the network model is that the network model allows each record to have more than one parent and child record.

97. How does a hierarchical database model work in RDBMS?

Ans:

Hierarchical Database Model. Database RDBMS Computer Science. A hierarchical model represents the data in a tree-like structure in which there is a single parent for each record. To maintain order there is a sort field which keeps sibling nodes into a recorded manner.

98. When was the hierarchical database model created by IBM?

Ans:

In order to retrieve data from a hierarchical database the whole tree needs to be traversed starting from the root node. This model is recognized as the first database model created by IBM in the 1960s. The Hierarchical Data Model is a way of organizing a database with multiple one to many relationships.

99. Why are RDBMS used in non relational databases?

Ans:

Technologically, RDBMS are considered primeval. However, the strong theoretical foundation of relational database technology has kept it pertinent in a volatile industry. This is why even non-relational databases include RDBMS-like features, such as the SQL interface in Hadoop.

100. What are the challenges in DBMS?

Ans:

Challenges of database security in DBMS Last Updated : 20 Aug, 2019 Seeing the vast increase in volume and speed of threats to databases and many information assets, research efforts need to be considered to the following issues such as data quality, intellectual property rights, and database survivability.