DBMS Interview Questions

Difference between primary key and unique key

A primary key is a table that represents each row in the table. The primary key will not allow any duplicate value or Null value. The entered value cannot be changed in a primary key.

A unique key is also a table like a primary key, but the unique key will accept one duplicate value or Null value.

Normalization in DBMS is the term used for organizing data in the database without accepting any deletion, update or insertion. 1nf, 2nf, and 3nf are some of the examples of normalization used to organize the data in different techniques.

There are four types of database languages available in DBMS. These are as follows:

• Data Definition Language 
• Data Manipulation Language 
• Data Control Language 
• Transaction Control Language

Database systems are composed of complex data structures and in order to be the database with user interaction, a technique was developed to hide any internal irrelevant details from Developers or users. This process of hiding from the user is known as data abstraction. There are three basic levels of this process i.e. physical, logical and view. The main purpose of this system is to provide an abstract view to the users.

Relational Algebra is a query language used to get input as relation and yield output of regard in a modified form. An operator can raise the query, and the most likely operator will be unary or binary. The relational queries are performed when there is a higher level of language used to handle the questions.

In atomicity, when an ongoing transaction is incomplete, the user does not need to worry about the incompletion. DBMS ensures to complete the action. Either the operation will be completed, or the entire system will be stopped and restarted.

When a relation to be expressed within the relationships, a system is followed to execute this process. The process is termed as aggregation.

In DBMS, functional dependency is a relationship that exists between two attributes. It is basically a constraint that specifies the relationship between two sets of attributes where the one set can accurately determine the value of other sets and can be denoted as X → Y.

Entity-Relationship modeling represents a graphical approach for database design by utilizing the entity-relationship, it depicts real-world projects. In ER modeling, the entities are modeled along with their attributes and overall relationship among entities. Also, describe the interrelated interest in any specific domain of knowledge. The basic ER model is comprised of several and its types and specifies the relationship that exists between them.

Object-oriented modeling can be defined as the construction of objects using a collection of objects that contain stored values of the instance variables found within an object. It allows for object identification and communication while supporting data abstraction, inheritance, and encapsulation.

BCNF (Boyce-Codd Normal Form) is a general form that is utilized in database normalization. It is the stronger version than the third normal form which was developed by Raymond and Edgar in 1974. It is based on the functional dependencies which take account of all the candidate keys in their precise relation and it also has the additional constants compared with the significant definition of 3NF. Its application may identify any additional redundancy which might be caused by the dependency to violate one or multiple candidate keys.

Intension and extension, in logic, correlative words that indicate the reference of a term or concept such that intension indicates the internal content of a term or concept that constitutes its formal definition, whereas extension indicates its range of applicability by naming the particular objects that it denotes.

The difference between DBMS and RDBMS can be stated as DBMS applications store data as files in either a hierarchical form or a navigational form. It uses a file system to store data, so there will be no relation between the tables. Whereas RDBMS applications store data in a tabular form such that the tables have an identifier called primary key and the data values are stored in the form of tables. It is designed to handle a large amount of data.

A transaction can be defined as a single logical unit of work that accesses and possibly modifies the contents of a database. It can be also stated as any logical calculation done in a consistent model in a database is known as a transaction.

The difference between attributes and identifiers can be stated as the attributes are properties that describe the entity's characteristics. Whereas Identifiers are attributes that name or identify entity instances.

Some of the advantages of a Database Management System are as follows:

• Data Integrity
• Data Security
• Better data integration
• Minimized Data Inconsistency
• Faster Data Access
• Better decision making
• Simplicity

A Data Dictionary is a collection of names, definitions, and attributes about data elements that are being used or captured in a database The data in a data dictionary is the metadata about the database. These elements are then used as part of a database, research project, or information system.

A query is an operation that retrieves data from one or more tables or views. It is a request for data or information from a database table or combination of tables. Whereas a Subquery is a type of query which is written inside another query. A subquery becomes a part of a larger query.

Some of the basic DDL and DML commands are as follows:

DDL  Commands:

• CREATE
• DROP 
• ALTER 
• RENAME 
• TRUNCATE

DML Commands:

• SELECT 
• INSERT 
• UPDATE 
• DELETE

JOIN is an important feature of DBMS, which is used to combine two tables based on a specified common field between them. It can be categorized in the following types:

• inner join
• left outer join
• right outer join
•  full outer join

The various phases of transaction are as follows:

• Active State – When the instructions of the transaction are running then the transaction is in active state.
• Partially Committed – After completion of all the read and write operation the changes are made in main memory or local buffer. 
• Failed State – When any instruction of the transaction fails, it goes to the “failed state”.
• Aborted State – After having any type of failure the transaction goes from “failed state” to “aborted state”.
• Committed State – It is the state when the changes are made permanent on the Data Base and the transaction is complete.
• Terminated State – If there isn’t any roll-back or the transaction comes from the “committed state”, then the system is consistent and ready for new transaction and the old transaction is terminated. 

Difference between candidate key and foreign key

Candidate key - This key is a candidate for primary care for any table and it can fulfill all the requirements of the primary key which cannot be referred to as null. They have unique records for the primary key as a candidate and every table has at least one of them.

Foreign key - It is used to define the relationship between two tables. Whenever there is a need to implement a relationship between the concepts of the foreign key is used.