Online Tutorials & Training Materials |
Register Login

Top 20 DBMS Interview Questions and Answers

|| 0

Top 20 DBMS Interview Questions and Answers

What is DBMS and what does DBMS stand for?

DBMS stands for Database Management System that is a software used for developing and managing databases. It provides users with a system to create databases, update data, retrieve information and manage the information.

What are the advantages of DBMS?

The advantages of DBMS are as follows:

  • A DBMS allows users to share the data among themselves by different levels of authorization
  • Unlike file-based data management systems, there is no possibility of data duplicity as duplicate values are removed
  • The data remains consistent and the updated values are available to the users immediately
  • It provides concurrent access to the data so that data can be accessed by only one user at a time
  • Data backup and recovery facilities are provided

What is Key in DBMS?

In DBMS, a key is an attribute that is used for identifying a specific row in a table. It also helps users to understand the relationship between two tables. By combining two or more columns, a particular row can be uniquely identified.

What is Super Key in DBMS?

A super key is a combination of one or more columns or attributes that are used for uniquely identifying a specific row in a table.

What is the Primary key and Foreign key in DBMS?

A primary key is used for uniquely identifying rows in a table. The primary key has to be unique for each row in the table. The key is usually underlined to specify the uniqueness. A primary does not contain any null values.

For example, in the database having student data, the Student ID is the primary key as it is unique for each student.

What is Serializability in DBMS?

A database may be inconsistent if multiple transactions are running concurrently. Serializability helps to ensure that the data is consistent at all times and the serial schedules are in order. A serial schedule achieves this as a new transaction will start after the other transaction has completed execution.

What is a Functional Dependency in DBMS?

A functional dependency is a relationship between two attributes in a DBMS system. This relationship is observed between a primary key and non-primary key of a table. For example, if there is a table having a Student ID, Student Name and Address, the Student ID is the primary key.

This key can uniquely identify the student name, so the relation between the two attributes can be represented as:

Student ID → Student Name

It can be said that Student Name is functionally dependent on Student ID.

What is Concurrency Control in DBMS?

Concurrency control is performed for multiple database operations without them being affected by each other. In large databases where read and write operations are performed, problems in concurrency may occur. So, the main aim of concurrency control is to maintain data integrity.

What are the Constraints in DBMS?

In DBMS, constraints are rules that are imposed on the columns of a table. It limits the type of data being entered in the table and maintains the data accuracy. The column level constraints are applied on the column and row constraints are applied on the rows.

They can be specified when a table is created or when altered. Example - NOT NULL, PRIMARY KEY and UNIQUE.

What is Tuple in DBMS?

A tuple is a single row of a database table that contains all the information of an individual record. It is a single entry in the table also called record or row, having related information. For example,

1   Jason Roy  England   Batsman

Here, the column headings may be a Serial number, Player name, Country and Specialty.

What is a transaction in DBMS?

A transaction may be a group of tasks or a single processing unit that cannot be divided further. A transaction can also be considered as a group of tasks where the contents of the database may be updated. DBMS transactions must satisfy ACID properties – Atomic, Consistent, Isolated and Durable.

A successful transaction may alter the state of a database from Consistent to another state.

What is normalization in DBMS?

Normalization is the process of managing the data in a database. Amongst a set of relations, it is used for reducing the chances of data redundancy. Insertion, update, and deletion anomalies are also reduced. This is done by dividing the database into smaller tables and linking them through a relationship.

What is data abstraction and the levels of abstraction in DBMS?

Database systems consist of complicated data structures that may create problems in terms of data retrieval and usability. To resolve these problems and improve the ease of use, database developers hide internal details from the users. This approach is called data abstraction.

The levels of data abstraction are:

  • Physical level
  • Logical level
  • View level

What is acid property in DBMS?

The ACID properties are:

  • Atomicity – A transaction is considered an atomic unit
  • Consistency – A database must stay consistent after a transaction
  • Isolation – Transactions may occur concurrently without affecting each other and the consistency of the database
  • Durability – The database must be durable to accommodate the latest updates even if the system fails

What is data independence in DBMS?

Data independence is a DBMS property that allows users to alter a database schema at one level so that they do not have to change it at the next higher level. The data at the other level is not affected. It also helps to keep the data separate from the programs using it.

Types of data independence are:

  • Physical
  • Logical

What is hashing in DBMS?

Hashing is a technique used in databases to locate a data record on a disk without using indexes. Here, a hash function is applied to the location in memory where the data records are stored. The memory locations are known as data blocks or buckets. The generated address of the data block is called a hash index.

What is BCNF in DBMS?

BCNF stands for Boyce-Codd Normal Form is a form of normalization. It is an advanced version of the 3 NF (Third Normal form). A table is in BCNF is there are no non-trivial functional dependencies except the superset of a candidate key. So for a functional dependency, X → Y, X has to be the super key of the table.

What is aggregation in DBMS?

In an entity-relationship diagram, it is not possible to depict the relationship between an entity and a relation. In such cases, aggregation is used for treating the relationship between the two entities as a single entity.

What are anomalies in DBMS?

Anomalies are problems that arise in a database due to unorganized data and un-normalized database tables. This also occurs when everything is stored in a flat database. This condition is removed through normalization where tables and relations are properly structured.

Types of anomalies are:

  • Insert
  • Delete
  • Update

What are DDL and DML statements?

DDL (Data Definition Language) consists of the SQL commands that are used for defining the structure of the database. The different SQL commands in DDL are:

  • Create
  • Alter
  • Drop
  • Comment
  • Truncate
  • Rename

DML (Data Manipulation Language) consists of the SQL statements that are used for manipulating the contents of the database. The different SQL commands in DML are:

  • Insert
  • Delete
  • Select
  • Update