DBMS Interview Questions

1. What is DBMS?

• Database Management Systems (DBMS) are software systems used to store, retrieve, and run queries on data. 
• A DBMS serves as an interface between an end-user and a database, allowing users to create, read, update, and delete data in the database. 
• In DbMS 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.  
• Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.

 2. What is a database? 

• Database is an organized collection of data stored and accessed electronically from a computer system. 
• Where databases are more complex they are often developed using formal design and modeling techniques.  
• Database mostly consists of the objects (tables), and tables include of 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.

 The collection of database and DBMS software together is known as a database system. 

3. What are the advantages of DBMS?

• Improved data sharing
• Improved data security
• Improved data access
• Improved decision making
• Increased end-user productivity
• Redundancy control
• Restriction for unauthorized access
• Provides multiple user interfaces
• Provides backup and recovery
• Enforces integrity constraints
• Ensure data consistency
• Easy data extraction and data processing due to the use of queries

 4. What is a checkpoint in DBMS? When does checkpoint occur in DBMS?

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

A Checkpoint is the means in which the SQL Server Database Engine guarantees the databases consistency, where no data will be lost when the system is crashed as the dirty pages that are located in the memory and resulted from committed transaction will be redone although they are not written to the database files yet.

5. What do you mean by transparent DBMS?

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 stored on disk. 

6. What are the unary operations in Relational Algebra?

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.

7. What is RDBMS?

RDBMS stands for Relational Database Management Systems. RDBMS is a type of DataBase Management System that stores data in the form of relations (tables). Relational databases are powerful, so they require few assumptions about how data is related or how, it will be extracted from 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. 

Examples of the most popular RDBMS are MYSQL, Oracle, IBM DB2, and Microsoft SQL Server database

8. How many types of database languages are?

There are four types of database languages:

• Data Definition Language (DDL) DDL defines the statements to implement the database schema. 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) Data Manipulation Language has a set of statements that allows users to access and manipulate the data in the database. 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 on the database. So, they are the part of 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.

 9. What do you understand by Data Model?

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.

10. Define a Relation Schema and a Relation.

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 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 which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ...., vn). 

11. What is a degree of Relation?

The degree of relation is a number of attribute of its relation schema. A degree of relation is also known as Cardinality it is defined as the number of occurrence of one entity which is connected to the number of occurrence of other entity. 

12. What is the Relationship?

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 object and vice versa.

Many-to-many: Here more than one records of an object can be related to n number of records of another object

13. What are the disadvantages of file processing systems?

· Data redundancy & inconsistency.
· Difficult in accessing data.
· Data isolation.
· Data integrity.
· Concurrent access is not possible.
· Security Problems. 

14. What is data abstraction in DBMS?

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. 

15. What are the three levels of data abstraction?

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.

16. What is Relational Algebra?

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.

17. What do you understand by query optimization?

The term query optimization specifies an efficient execution plan for evaluating a query that has the least estimated cost.

18. What is normalization?

Normalization is a process of 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)

19. What is Denormalization?

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 join

20. What is functional Dependency?

The functional dependency is 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.

21. What is the E-R model?

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

 

22. What is Weak Entity set?

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 mean to differentiate among all those entries in the entity set that depend on one particular strong entity set.

23. What is an attribute?

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. 

24. What are the integrity rules in DBMS?

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

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

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

Keys are defined to easily identify any row of data in a table.

• Key plays a vital role in the relational database (is a collection of data items with pre-defined relationships between them)
• It is used for identifying unique rows from the table.
• It also establishes the relationship among tables.
• A Key can be a single attribute or a group of attributes

Types of Keys

Primary Key – A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.

Super Key – A super key is a set of one or more columns (attributes) to uniquely identify rows in a table.

Candidate Key – A super key with no redundant attribute is known as the candidate key

Alternate Key – Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys.

Composite Key – A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called a composite key.

Foreign Key – Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

Non-key Attributes

Non-key attributes are the attributes or fields of a table, other than candidate key attributes/fields in a table.

Non-prime Attributes

Non-prime Attributes are attributes other than the Primary Key attribute(s).

Let's take a simple Student table, with fields student_id, name, phone and age.

Super Key: 

student_id, (student_id, name), phone




Candidate Key: 
student_id and phone




Primary Key: 
student_id

Composite Key: 

Normalization

•  Normalization is the process of organizing the data in the database.
• Normalization is used to,

Minimize the redundancy (repetition of data which increases the size of data) from a relation or set of relations. 
Eliminate the undesirable characteristics like Insertion, Updates and Deletion Anomalies.

• Normalization divides the larger table into the smaller table and links them using relationship.
• The normal form is used to reduce redundancy from the database table.

Types of Normal Forms

There are four types of normal forms,

• 1NF
• 2NF
• 3 NF
• BCNF
• 4 NF
• 5 NF

First Normal Form (1NF)

• A relation will be 1NF if it contains an atomic value.
• It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attribute.
• First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

A table is in 1 NF iff:

• There are only Single Valued Attributes.
• Attribute Domain does not change.
• There is a unique name for every Attribute/Column.
• The order in which data is stored does not matter.

Second Normal Form (2NF)

• A relation must be in first normal form and relation must not contain any partial dependency. 
• A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.

To remove Partial dependency, we can divide the table, remove the attribute which is causing partial dependency, and move it to some other table where it fits in well.

A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in Second Normal Form (2NF).

Note – If the proper subset of candidate key determines non-prime attribute, it is called partial dependency.

The normalization of 1NF relations to 2NF involves the removal of partial dependencies. If a partial dependency exists, we remove the partially dependent attribute(s) from the relation by placing them in a new relation along with a copy of their determinant.

Functional Dependency

• Functional dependency in DBMS, as the name suggests is a relationship between attributes of a table dependent on each other.
• It helps in preventing data redundancy.
• Functional Dependency is represented by -> (arrow sign)

For example: 

Suppose we have a student table with attributes: Stu_Id, Stu_Name, Stu_Age. 

Here Stu_Id attribute uniquely identifies the Stu_Name attribute of student table because if we know the student id we can tell the student name associated with it. 

This is known as a functional dependency and can be written as Stu_Id->Stu_Name or in words, we can say Stu_Name is functionally dependent on Stu_Id.

Formally:

If column A of a table uniquely identifies the column B of the same table then it can be represented as

 A->B (Attribute B is functionally dependent on attribute A)

NOTE: Where an attribute in a table depends on only a part of the primary key and not on the whole key is referred to as Partial Dependency.

Third Normal Form (3NF)

A relation will be in 3NF,

1. It should be in the Second Normal form.
2. And it should not have Transitive Dependency.

A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).

 

Transitive Dependency

• When a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key, this is called Transitive Dependency. 
• A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. For e.g.

X -> Z is a transitive dependency if the following three functional dependencies hold true:

• X->Y
• Y does not ->X
• Y->Z

Note: A transitive dependency can only occur in a relation of three or more attributes. This dependency helps us normalizing the database in 3NF (3rd Normal Form).

The advantage of removing transitive dependency is,

• Amount of data duplication is reduced.
• Data integrity achieved.

Boyce-Codd Normal Form (BCNF)

For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:

1. It should be in the Third Normal Form.
2. And, for any dependency A → B, A should be a super key.

In simple words, it means, that for a dependency A → B, A cannot be a non-prime attribute, if B is a prime attribute.

Fourth Normal Form (4NF)

For a table to satisfy the Fourth Normal Form, it should satisfy the following two conditions:

1. It should be in the Boyce-Codd Normal Form.
2. And, the table should not have any Multi-valued Dependency.

Multi-valued Dependency

A table is said to have a multi-valued dependency if the following conditions are true,

1. For a dependency A → B, if for a single value of A, multiple values of B exists, then the table may have a multi-valued dependency.
2. Also, a table should have at least 3 columns for it to have a multi-valued dependency.
3. And, for a relation R(A,B,C), if there is a multi-valued dependency between, A and B, then B and C should be independent of each other.

If all these conditions are true for any relation(table), it is said to have a multi-valued dependency.

Fifth normal form (5NF)

• A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.
• 5NF is satisfied when all the tables are broken into as many tables as possible in order to avoid redundancy.
• 5NF is also known as Project-join normal form (PJNF).

 MySQL is an open-source relational database management system. MySQL is a relational database management system (RDBMS) based on the SQL (Structured Query Language) queries. It is one of the most popular languages for accessing and managing the records in the table. MySQL is open-source and free software under the GNU license. Oracle Company supports it. With MySQL, its fixed and structured data schema provides greater consistency and reliability than most databases. Another great benefit of using MySQL is its superior data security due to ACID-compliant transactions, being the most suitable choice for applications that value this feature

 The data in a MySQL database is stored in the form of tables. A table is a collection of related data, and it consists of columns and rows.