To solidify your knowledge and concepts in DBMS, we have listed here the most common DBMS interview questions to help you emerge victorious in the interview!
A collection of basic DBMS FAQs and answers
What does DBMS mean and what is it used for? Explain RDBMS with examples.
DBMS Plane Problem Resolution: As the name suggests, a DBMS or database management system is a set of applications or programs that enable users to create and maintain databases. A DBMS provides a tool or interface for performing various operations on the database, such as inserting, deleting, updating, and so on. It is a software that is able to store data more compactly and securely than file-based systems. The DBMS system helps users overcome problems such as data inconsistencies and data redundancy in the database, making it more convenient and organized to use.
Examples of popular DBMS systems are file systems, XML, Windows registry, etc.
RDBMS, which stands for relational database management system, was introduced in the 1970s to access and store data more efficiently than a DBMS. In contrast to a DBMS, which stores data as a file, an RDBMS stores data in the form of a table. Storing data as rows and columns makes it easier to locate specific values in the database and make it more efficient than a DBMS.
Examples of popular RDBMS systems are MySQL, Oracle DB, etc.
What are the common problems of DBMS: What does database mean?
A database is an organized, consistent, and logical collection of data that can be easily updated, accessed, and managed. A database primarily contains a table or set of objects made up of records and fields (anything created using the create command is a database object). A tuple or row represents a single entry in a table. An attribute or column represents the basic unit of a data store that contains information about a specific aspect of a table. The DBMS extracts data from the database in the form of queries given by the user.
What are the issues with traditional file-based systems that make a DBMS a better choice?
Traditional file-based systems don’t have indexes, which gives us the option to scan the entire page, making access to content tedious and ultra-slow. Another issue is redundancy and inconsistencies because files have many duplicate and redundant data, and changing one of them makes all the data inconsistent. Accessing data is more difficult in traditional file-based systems because the data in it is unorganized.
Another problem is the lack of concurrency control, where multiple operations can process a single file at the same time compared to a DBMS, which results in one operation locking the entire page.
Integrity checks, data isolation, atomicity, security, etc., are some of the other problems of traditional file-based systems, and DBMS provides some great solutions to these problems.
Explain some of the advantages of a DBMS.
Here are a few advantages of using a DBMS.
- Data sharing: Data from a single database can be shared by multiple users at the same time. This sharing also enables end users to react quickly to changes in the database environment.
- Integrity constraints: The existence of such constraints allows data to be stored in an organized and refined manner.
- Control redundancy in the database: Eliminate redundancy in the database by providing a mechanism to integrate all data into a single database.
- Data independence: This allows data structures to be changed without changing the composition of any of the applications being executed.
- Provides backup and recovery capabilities: Can be configured to automatically create data backups and restore data in the database if needed.
- Data Security: A DBMS provides the necessary tools to make the storage and transmission of data more reliable and secure. Authentication (the process of granting users restricted access) and encryption (encrypting sensitive data such as OTPs, credit card information, etc.) are some of the popular tools used to protect data in a DBMS.
Explain the different languages that exist in the DBMS.
The following are the various languages that exist in the DBMS:
- DDL (Data Definition Language): It contains the commands required to define the database.
For example, create, change, delete, truncate, rename, and so on. - DML (Data Manipulation Language): It contains the commands needed to manipulate the data present in the database.
For example, select, update, insert, delete, and so on. - DCL (Data Control Language): It contains the commands needed to handle user rights and control of the database system.
For example, grants and revocations. - TCL (Transaction Control Language): It contains the commands required to process database transactions.
FOR EXAMPLE, COMMIT, ROLLBACK, AND SAVEPOINT.
What does the ACID attribute mean in DBMS?
ACID stands for Atomicity, Consistency, Isolation, and Persistence in a DBMS, and these attributes ensure that data is shared between multiple users in a secure and reliable manner.
- Atomicity: This property reflects the concept of executing the entire query or not doing anything at all, which means that if an update occurs in the database, it is either reflected in the entire database or not at all.
- Consistency: This property ensures that the data in the database is consistent before and after the transaction.
- Isolation: This property ensures that each transaction occurs independently of the others. This means that the status of an ongoing transaction does not affect the status of another ongoing transaction.
- Persistence: This property ensures that data is not lost in the event of a system failure or reboot and exists in the same state as it was before the system failure or restart.
Is the NULL value in the database the same as a space or zero?
No, a NULL value is very different from a value of zeros and spaces because it represents an assigned, unknown, unavailable, or inapplicable value compared to a space that represents a character and a zero represents a number.
Example: A NULL value in a student’s selected “number_of_courses” indicates that its value is unknown, and a 0 indicates that the student did not select any courses.
A collection of frequently asked questions and answers for intermediate DBMS
What does data warehouse mean?
DBMS Plane Problem Resolution: The process of collecting, extracting, transforming, and loading data from multiple sources and storing them in a single database is called a data warehouse. A data warehouse can be thought of as a central repository where data flows out of transactional systems and other relational databases for data analysis. A data warehouse contains historical data from a variety of organizations that support an organization’s decision-making process.
Explain the different levels of data abstraction in a DBMS.
The process of hiding irrelevant details from users is known as data abstraction. Data abstraction can be divided into 3 levels:
- Physical Layer: The lowest layer, managed by the DBMS. This level consists of a data store description, the details of which are typically hidden from system administrators, developers, and users.
- Conceptual or logical level: It is the level at which developers and system administrators work that determines what data is stored in the database and the relationships between data points.
- External or view level: This level describes only a portion of the database and hides the details of the table schema and its physical storage from the user. The result of a query is an example of view-level data abstraction. A view is a virtual table created by selecting fields from one or more tables that exist in the database.
What are the common problems of DBMS: What does the Entity Relationship (ER) model mean? Explain the terms entities, entity types, and entity sets in the DBMS.
The entity-relational model is a diagrammatic approach to database design in which real-world objects are represented as entities and the relationships between them are mentioned.
- Entity: An entity is defined as a real-world object that has attributes that represent the characteristics of that particular object. For example, a student, employee, or teacher represents an entity.
- Entity type: An entity type is defined as a collection of entities with the same attributes. One or more related tables in the database represent an entity type. An entity type or attribute can be understood as a feature that uniquely identifies an entity. For example, a student represents an entity that has properties such as student_id, student_name, and so on.
- Entity set: An entity set can be defined as a collection of all entities that exist in a specific entity type in a database. For example, a collection of all students, staff, teachers, and so on represents a set of entities.
Explain the different types of relationships between tables in a DBMS.
The following are the different types of relationships between tables in a DBMS system:
- One-to-one relationship: This type of relationship is applied when a specific row in Table X is linked to a single row in Table Y.
- One-to-many relationships: This type of relationship is applied when a single row in Table X is related to multiple rows in Table Y.
- Many-to-many relationships: This type of relationship is applied when multiple rows in Table X can be linked to multiple rows in Table Y.
- Self-referential relationships: This type of relationship is applied when a specific row in Table X is associated with the same table.
Explain the difference between connotation and extension in databases.
Here are the main differences between connotations and extensions in databases:
- Connotation: The connotation or colloquially known database schema is used to define the description of the database and is specified during the database design and remains mostly unchanged.
- Scaling: Scaling, on the other hand, is a measure of the number of tuples present in the database at any given point in time. An extension of a database, also known as a snapshot of a database, has a value that constantly changes as a tuple is created, updated, or destroyed in the database.
Explain the difference between the DELETE and TRUNCATE commands in DBMS.
DELETE command: This command is used to delete rows from a table based on the conditions provided by the WHERE clause.
- It deletes only the rows specified by the WHERE clause.
- It can be rolled back if needed.
- It maintains a log to lock the table’s rows before deletion, so it’s slow.
TRUNCATE command: This command is required to remove full data from a table in the database. It’s like a DELETE command without a WHERE clause.
- It removes the complete data from the tables in the database.
- It can be rolled back even if needed. (Truncation can be rolled back in some databases, depending on their version, but this can be tricky and can lead to data loss). Check out this link for more details
- It doesn’t maintain logs and deletes the entire table at once, so it’s fast.
What is a lock. Explain the main differences between shared and exclusive locks during transactions in a database.
DBMS Plane Problem: Database lock is a mechanism to protect shared data from being updated by two or more database users at the same time. When a single database user or session gains a lock, no other database user or session can modify that data until the lock is released.
- Shared locks: Shared locks are required to read data items, and many transactions may hold locks on the same data item in a shared lock. Allows multiple transactions to read data items in a shared lock.
- Exclusive lock: An exclusive lock is a lock on any transaction that is about to perform a write operation. This type of lock does not allow more than one transaction, so it prevents any inconsistencies in the database.
What does normalized and non-normalized mean?
Normalization is the process of reducing redundancy by organizing data into multiple tables. Normalization makes better use of disk space and makes it easier to maintain the integrity of the database.
Denormalization is the inverse process of normalization that merges normalized tables into a single table, making data retrieval faster. The JOIN operation allows us to create a denormalized form of the data by inverting the normalization.
A collection of frequently asked questions and answers in the Advanced DBMS
Explain the different types of normalization forms in DBMS.
The following are the main forms of normalization in a DBMS:
Think of Table 1 above as a reference example for understanding the different forms of normalization.
- 1NF: It’s called the first paradigm and is the simplest type of normalization you can implement in a database. A table in the first normal form should meet the following conditions:
- Each column must have a value and should be atomic.
- Duplicate columns in the same table should be removed.
- A separate table should be created for each set of related data, and each row should be identified with a unique column.
Table 1 is converted to the 1NF form
- 2NF: It’s called the second paradigm. A table in the second normal form should meet the following conditions:
- The table should be in its 1NF, i.e., all the conditions for 1NF are met.
- Every non-primary attribute of the table should be functionally completely dependent on the primary key, i.e. every non-key attribute should depend on the primary key, so that if any key element is removed, then even the non_key element will be saved in the database.
Split Table-1 into 2 different tables to move it to 2NF.
- 3NF: It’s called the third normal. A table in the second normal form should meet the following conditions:
- The table should be in its 2NF, i.e., all the conditions for 2NF are met.
- None of the properties in the same table have a transfer function dependency on one property.
Split Table-1 into 3 different tables to move it to 3NF.
- BCNF： BCNF stands for Boyce-Codd Normal Form and is an advanced form of 3NF. For the same reason, it is also known as 3.5NF. The table of the BCNF paradigm should meet the following criteria:
- The table should be in its 3NF, i.e., all the conditions for 3NF are met.
- For each function dependency of any property A to B
(A->B), A should be the superkey of the table. It simply implies that if B is the primary attribute, then A cannot be a non-primary attribute.
What are the common problems of DBMS: Explain the different types of keys in the database.
There are 7 main types of keys in the database:
- Candidate key: A candidate key represents a set of attributes that uniquely identify a table. Each table may have more than one candidate key. You can select one of all candidate keys as the primary key. In the example below, because studentId and firstName can be considered candidates because they can uniquely identify each tuple.
- Hyperkey: A superkey defines a set of properties that uniquely identify a tuple. Candidate and primary keys are subsets of hyperkeys, in other words, hyperkeys are their supersets.
- Primary key: A primary key defines a set of properties that uniquely identify each tuple. In the example below, studentId and firstName are candidate keys, and you can choose either of them as your primary key. In the given example, studentId is chosen as the primary key for the student table.
- Unique keys: Unique keys are very similar to primary keys, except that primary keys don’t allow NULL values in columns, but unique keys allow them. So essentially the only key is the primary key with a NULL value.
- Alternate Keys: All candidate keys that are not selected as primary keys are considered Alternate Keys. In the example below, the first and last names are alternate keys in the database.
- Foreign key: A foreign key defines a property that can only take a value that is common to a value that exists in one table and an attribute that exists in another table. In the example below, the courseId in the Student table is a foreign key to the Course table, because both tables contain the courseId as one of their attributes.
- Composite key: A composite key is a combination of two or more columns that uniquely identifies each tuple in a table. In the following example, you can group studentId and firstname to uniquely identify each tuple in the table.
DBMS Common Plane Questions and Answers: Explain the difference between Layer 2 and Layer 3 architectures in DBMS.
DBMS Plane Problem Resolution: A two-tier architecture refers to a client-server architecture in which the client application communicates directly with the server-side database and does not participate in any middleware communication.
Example – A contact management system created using MS-Access or a rail reservation system, etc.
The diagram above represents a 2-tier architecture in a DBMS.
The 3-layer architecture consists of another layer between the client and the server providing a GUI to the user, making the system more secure and convenient. In this type of architecture, the client-side application interacts with the server-side application, which further communicates with the database system.
Examples—Design a sign-up form that contains text boxes, labels, buttons, or large websites on the Internet, etc.
The diagram above represents a 3-tier architecture in a DBMS.
