DBMS Interview Questions

DBMS (Database Management System) is a computer software application that allows users and other applications to view, monitor, and analyze data. It is an application to handle data on various subjects.

Database partitioning is the process of splitting large tables into smaller database entities. Following are the benefits of partitioning-

• When rows are in one partition, query performance in such situations is improved.
• It also helps to access large parts of a single partition.
• Data that are rarely used can be stored in the slower and cheaper storage medium.

A database cursor enables traversal over the records in the database. The different cursor types are-

• Dynamic is highly subject to changes while scrolling the cursor in the database.
• Static it doesn’t show any changes while scrolling. It also works on the recording of snapshots.
• Keyset enables the user to modify the data without displaying the new data.

DDL commands are used to modify and edit the structure of a database system. Some of the important commands are-

1. CREATE table command- It defines each column of the table uniquely and distinctly like name, data type, size, etc.
2. Syntax: CREATE TABLE [column name] ([column definitions]) [table parameters]
3. ALTER table command- ALTER is used to make changes and edit the existing table of the database. Syntax: ALTER objecttype objectname parameters.
4. DROP table command – DROP is used for destroying a table and all the recorded data in it. It destroys an existing database. Syntax: DROP objecttype objectname parameters.

DBMS is a structure that manages and handles large volumes of data stored in a database. it serves as an intermediate between users and the database. Following are few advantages of database management system-

• Data redundancy- this happens when multiple copies of data are stored. With DBMS, data is stored in one structure database and the data is inputted only once.
• No unauthorized access- it discourages unauthorized access and improves data access.
• Supports multiple user interfaces.
• Minimized data inconsistency- In DBMS, data inconsistency is reduced as different versions of the same data don’t appear in different places.

There are various kinds of interactions supported by DBMS like-

• Data definition
• Update
• Retrieval
• Administration

Following is a list of the differences between NoSQL and RDBMS: –

• In terms of data format, NoSQL does not follow any order for its data format. Whereas, RDBMS is more organized and structured when it comes to the format of its data.
• When it comes to scalability, NoSQL is more very good and more scalable. Whereas, RDBMS is average and less scalable than NoSQL.
• For querying of data, NoSQL is limited in terms of querying because there is no join clause present in NoSQL. Whereas, querying can be used in RDBMS as it uses the structured query language.
• The difference in the storage mechanism of NoSQL and RDBMS is that NoSQL uses key-value pairs, documents, column storage, etc. for storage. Whereas, RDBMS uses various tables for storing data and relationships.

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 several data models:

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

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. A relation schema is known as the blueprint with the help of which we can explain that 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:

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

The degree of relation is several 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 that is connected to the number of occurrences of other entities. There are three degree of relation they are one-to-one(1:1), one-to-many(1:M), many-to-one(M:M).

The Relationship is defined as an association among two or more entities. There are three types 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 n number of records of another object.

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

Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interaction 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 for making access to the data easy, it is necessary to do data abstraction. In addition, data abstraction divides the system into different layers to make the work specified and well defined.

Following are three levels of data abstraction:

Physical level: It is the lowest level of abstraction. It describes how data are stored.

Logical level: It is the next higher level of abstraction. It describes what data are 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- The user interacts with the system using the GUI and fills in the required details, but the user doesnt have any idea how the data is being used. So, the abstraction level is entirely high in the 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 the VIEW LEVEL.

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

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 in the database.

data 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 types of DML:

Procedural DML or Low-level DML: It requires a user to specify what data are needed and how to get those data.

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

The DML Compiler translates DML statements in a query language that the query evaluation engine can understand. 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.

Relational Algebra is a Procedural Query Language that 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.

mathematics fundamentals such as algebra, differential, integration, etc. Thats why it is also known as predicate calculus.

There is two types of relational calculus:

• Tuple relational calculus
• Domain relational calculus

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 several methods, and algorithms that existed for the same task then the question arose that 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

Once the DBMS informs the user that a transaction has been completed successfully, its effect should persist even if the system crashes before all its changes are reflected on the 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.

Normalization is a process of analyzing 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 an essential process as it is used to avoid data redundancy, insertion anomaly, update anomaly, deletion anomaly.

Theyre most commonly used normal forms are:

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

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

Functional Dependency is the starting point of normalization. It exists when a relation between two attributes allows you to determine the corresponding attributes 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.

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 an 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.

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 attribute 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 designation can be considered as entities. These entities have some characteristics which will be the attributes of the corresponding entity.

An entity type is specified as a collection of entities, having the same attributes. The 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.

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.

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.

An entity set that doesnt 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. A weak entity set does not have a primary key, but we need the means to differentiate among all those entries in the entity set that depend on one particular strong entity set.