DBMS — Interview Questions and Answers(Level 1)

Are you preparing for a Database Management System (DBMS) interview? Look no further! In this Medium blog, we’ve curated a comprehensive list of DBMS interview questions and provided detailed answers to help you ace your Level 1 DBMS interviews. Whether you’re a beginner or seeking a refresher, our guide will equip you with the knowledge and confidence you need to excel in your next database-related interview. Dive in to boost your DBMS expertise and secure that dream job!

📚 What is a Database?

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of the real world and designed, built, and populated with data for a specific purpose.

🖥️ What is DBMS?

DBMS, or Database Management System, is a collection of programs that enables users to create and maintain a database. In other words, it’s general-purpose software that provides users with the processes of defining, constructing, and manipulating the database for various applications.

🌐 What is a Database System?

A database system is the combination of the database and the DBMS software. It encompasses both the data and the software used to manage it.

📈 What are the Advantages of DBMS?

1. Redundancy is controlled.
2. Unauthorized access is restricted.
3. Providing multiple user interfaces.
4. Enforcing integrity constraints.
5. Providing backup and recovery.

📉 What are the Disadvantages in File Processing Systems?

1. Data redundancy and inconsistency.
2. Difficulty in accessing data.
3. Data isolation.
4. Data integrity.
5. Concurrent access is not possible.
6. Security problems.

🔍 Describe the Three Levels of Data Abstraction.

There are three levels of abstraction:

1. Physical level: Describes how data is stored.
2. Logical level: Describes what data is stored and the relationships among them.
3. View level: Describes only a part of the entire database.

🔒 Define “Integrity Rules.” There are two Integrity rules:

1. Entity Integrity: Primary key cannot have NULL value.
2. Referential Integrity: Foreign Key can be either a NULL value or should be the Primary Key value of another relation.

📊 What is Extension and Intension?

1. Extension: The number of tuples present in a table at any instance. This is time-dependent.
2. Intension: A constant value that gives the name, structure of the table, and the constraints laid on it.

🧮 What is System R?

What are Its Two Major Subsystems? System R is a prototype relational database system developed by IBM. Its two subsystems are:

1. Research Storage.
2. System Relational Data System.

🗃️ How is the Data Structure of System R Different from the Relational Structure?

Unlike Relational systems, in System R:

1. Domains are not supported.
2. Enforcement of candidate key uniqueness is optional.
3. Enforcement of entity integrity is optional.
4. Referential integrity is not enforced.

💼 What is Data Independence?

Data independence means that application is independent of the storage structure and access strategy of data. It includes:

1. Physical Data Independence: Modification in the physical level should not affect the logical level.
2. Logical Data Independence: Modification in the logical level should not affect the view level. Note that logical data independence is more difficult to achieve.

👁️‍🗨️ What is a View?

How is It Related to Data Independence? A view may be thought of as a virtual table that does not physically exist but is derived from one or more underlying base tables. Views insulate users from the effects of restructuring and growth in the database, accounting for logical data independence.

🔍 What is a Data Model?

A data model is a collection of conceptual tools for describing data, data relationships, data semantics, and constraints.

🧩 What is E-R Model?

The Entity-Relationship (E-R) model is a data model based on real-world entities and the relationships among them. It describes entities using attributes and their relationships.

🪆 What is Object-Oriented Model?

The Object-Oriented model is based on collections of objects, each containing values stored in instance variables and methods to operate on the objects. Objects with the same attributes and methods are grouped into classes.

👤 What is an Entity?

An entity is a ‘thing’ in the real world with an independent existence, often represented in a database.

📦 What is an Entity Type?

An entity type is a collection (set) of entities that have the same attributes and characteristics.

🧩 What is an Entity Set?

An entity set is a collection of all entities of a particular entity type in the database.

📦 What is an Extension of Entity Type?

The extension of an entity type comprises the collections of entities of that particular entity type.

🗄️ What is a Weak Entity Set?

A weak entity set may not have sufficient attributes to form a primary key. Its primary key consists of its partial key and the primary key of its parent entity, making it dependent on its parent.

🏷️ What is an Attribute?

An attribute is a property that describes an entity.

📋 What is a Relation Schema and a Relation? A relation schema, denoted by R(A1, A2, …, An), consists of the relation name R and a list of attributes Ai that it contains. A relation is defined as a set of tuples.

📊 What is the Degree of a Relation?

The degree of a relation is the number of attributes in its relation schema.

🤝 What is a Relationship?

A relationship is an association among two or more entities.

🗃️ What is a Relationship Set?

A relationship set is a collection (or set) of similar relationships.

📊 What is a Relationship Type?

A relationship type defines a set of associations or a relationship set among a given set of entity types.

🔗 What is the Degree of a Relationship Type?

The degree of a relationship type is the number of entity types participating in it.

📝 What is DDL (Data Definition Language)?

DDL, or Data Definition Language, is a language used to define and manage the structure and organization of data in a database.

🔍 What is VDL (View Definition Language)?

VDL, or View Definition Language, specifies user views and their mappings to the conceptual schema.

📦 What is SDL (Storage Definition Language)?

SDL, or Storage Definition Language, is used to specify the internal schema and may specify the mapping between two schemas.

🗄️ What is Data Storage-Definition Language?

Data Storage-Definition Language specifies the storage structures and access methods used by a database system.

📝 What is DML (Data Manipulation Language)?

DML, or Data Manipulation Language, is a language that enables users to access or manipulate data as organized by an appropriate data model. It can be categorized as procedural (low-level) or non-procedural (high-level).

🔧 What is DML Compiler?

A DML compiler translates DML statements in a query language into low-level instructions that the query evaluation engine can understand.

⚙️ What is Query Evaluation Engine?

The query evaluation engine executes the low-level instructions generated by the DML compiler.

📋 What is DDL Interpreter?

A DDL interpreter interprets DDL statements and records them in tables containing metadata.

📄 What is Record-at-a-Time?

Record-at-a-Time refers to the retrieval of each record from a set of records, typically associated with low-level or procedural DML.

📊 What is Set-at-a-Time or Set-Oriented?

Set-at-a-Time, or Set-Oriented, refers to the retrieval of many records in a single DML statement, typically associated with high-level or non-procedural DML.

📉 What is Relational Algebra?

Relational Algebra is a procedural query language consisting of a set of operations that take one or two relations as input and produce a new relation as output.

📝 What is Relational Calculus?

Relational Calculus is an applied predicate calculus specifically tailored for relational databases, proposed by E.F. Codd. Examples of languages based on it include DSL ALPHA and QUEL.

📊 How Does Tuple-Oriented Relational Calculus Differ from Domain-Oriented Relational Calculus?

1. The tuple-oriented calculus uses tuple variables, while the domain-oriented calculus uses domain variables.
2. Tuple-oriented calculus variables range over tuples of a relation, whereas domain-oriented calculus variables range over underlying domains.

📂 What is Normalization?

Normalization is a process of analyzing given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve properties like minimizing redundancy and reducing insertion, deletion, and update anomalies.

🔗 What is Functional Dependency?

Functional Dependency (FD) denoted by X -> Y between two sets of attributes X and Y specifies a constraint on the possible tuples in a relation, indicating that if two tuples share the same X value, they must also have the same Y value.

🔄 What is Lossless Join Property?

Lossless Join Property guarantees that the decomposition of a relation into smaller relations does not result in spurious tuple generation during join operations.

🔍 What is 1NF (First Normal Form)?

1NF requires that the domain of an attribute must include only atomic (simple, indivisible) values.

📊 What is Fully Functional Dependency?

A functional dependency X -> Y is fully functional if the removal of any attribute A from X means that the dependency no longer holds.

🔄 What is 2NF (Second Normal Form)?

A relation schema R is in 2NF if it is in 1NF, and every non-prime attribute A in R is fully functionally dependent on the primary key.

📉 What is 3NF (Third Normal Form)?

A relation schema R is in 3NF if it is in 2NF, and for every FD X -> A, either X is a superkey of R or A is a prime attribute of R, ensuring that every non-prime attribute is non-transitively dependent on the primary key.

🔗 What is BCNF (Boyce-Codd Normal Form)?

A relation schema R is in BCNF if it is in 3NF and satisfies the additional constraint that for every FD X -> A, X must be a candidate key.

📄 What is 4NF (Fourth Normal Form)?

A relation schema R is said to be in 4NF if, for every multivalued dependency X ->> Y that holds over R, one of the following is true:

1. X is a subset or equal to Y.
2. X is a superkey.

📊 What is 5NF (Fifth Normal Form)?

A relation schema R is said to be in 5NF if, for every join dependency {R1, R2, …, Rn} that holds R, one of the following is true:

1. Ri = R for some i.
2. The join dependency is implied by the set of FDs over R in which the left side is a key of R.

📈 What is Domain-Key Normal Form?

A relation is in DKNF if all constraints and dependencies that should hold can be enforced by simply enforcing the domain constraint and key constraint on the relation.

🔑 What are Partial, Alternate, Artificial, Compound, and Natural Keys?

1. Partial Key: A set of attributes that can uniquely identify weak entities related to the same owner entity.
2. Alternate Key: All candidate keys excluding the primary key are alternate keys.
3. Artificial Key: A unique number assigned to each record when no obvious key is available.
4. Compound Key: A unique identifier created by combining multiple elements.
5. Natural Key: When one of the data elements stored within a construct is used as the primary key, it’s called a natural key.

🔍 What is Indexing, and What are the Different Kinds of Indexing? Indexing is a technique for determining how quickly specific data can be found. Types of indexing include:

1. Binary search-style indexing.
2. B-Tree indexing.
3. Inverted list indexing.
4. Memory resident table.
5. Table indexing.

📑 What is a System Catalog or Catalog Relation?

A system catalog, also known as a data dictionary, is a collection of tables and database objects that store metadata and information about the database’s structure and content.

🛠️ What is Meant by Query Optimization?

Query optimization is the phase in database management where an efficient execution plan for evaluating a query is identified to minimize the estimated cost.

🔐 What is Durability in DBMS?

Durability in DBMS refers to the property that once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all changes are reflected on disk.

⚛️ What Do You Mean by Atomicity and Aggregation?

1. Atomicity: Ensures that either all actions in a transaction are carried out or none are, preventing incomplete transactions.
2. Aggregation: Used to model a relationship between a collection of entities and relationships, expressing a relationship among relationships.

❌ What is a Phantom Deadlock?

Phantom deadlock occurs in distributed deadlock detection when delays in propagating local information lead to the detection of deadlocks that do not actually exist, resulting in unnecessary aborts.

📷 What is a Checkpoint, and When Does It Occur? A checkpoint is a snapshot of the DBMS state. It occurs to reduce the amount of work needed during system restart after crashes.

🔄 What are the Different Phases of a Transaction?

The phases of a transaction include:

1. Analysis phase.
2. Redo phase.
3. Undo phase.

📄 What Do You Mean by Flat File Database?

A flat file database is a database that lacks programs or user access languages, providing a user-friendly interface for managing data but without cross-file capabilities.

👤 What is a Transparent DBMS?

A transparent DBMS is one that keeps its physical structure hidden from the user, providing abstraction and simplifying data access.

❓ What is a Query?

A query, in the context of a DBMS, refers to user commands used to interact with a database. It includes both data definition language (DDL) and data manipulation language (DML) statements.

❔ What Do You Mean by Correlated Subquery?

A correlated subquery is a subquery executed once for each row returned by the parent query. It can reference parent subquery columns in its WHERE clause.

⚙️ What Are the Primitive Operations Common to All Record Management Systems? Primitive operations common to all record management systems include addition, deletion, and modification of records.

📂 Name the Buffer in Which All the Commands That Are Typed In Are Stored? The buffer in which all the commands that are typed in are stored is called the ‘Edit’ buffer.

🔄 What Are the Unary Operations in Relational Algebra?

The unary operations in relational algebra are PROJECTION and SELECTION.

❌ Are the Resulting Relations of PRODUCT and JOIN Operations the Same? No, the resulting relations of PRODUCT and JOIN operations are not the same. PRODUCT concatenates every row in one relation with every row in another, while JOIN combines rows from one relation with related rows from another.

🛠️ What is RDBMS Kernel?

The RDBMS kernel is a crucial component of the RDBMS architecture responsible for database management, including tasks like access control, memory management, and transaction management.

🔄 Name the Sub-Systems of an RDBMS.

The sub-systems of an RDBMS include I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, and Lock Management.

📖 Which Part of the RDBMS Takes Care of the Data Dictionary? How?

The data dictionary is maintained by the kernel, and it stores metadata about the database objects and their relationships. It ensures data consistency and accessibility.

📋 What is the Job of the Information Stored in the Data Dictionary?

The information in the data dictionary validates the existence of objects, provides access to them, and maps the actual physical storage location of database elements.

💬 How Do You Communicate with an RDBMS?

You communicate with an RDBMS using Structured Query Language (SQL).

📜 Define SQL and State the Differences Between SQL and Other Conventional Programming Languages.

SQL (Structured Query Language) is a nonprocedural language designed for data access operations. It specifies what data operations should be performed rather than how to perform them. The primary difference is that SQL is declarative, while conventional programming languages are procedural.

📦 Name the Three Major Sets of Files on Disk That Compose a Database in Oracle. In Oracle, the three major sets of files on disk that compose a database are:

1. Database files (containing actual data).
2. Control files (managing database metadata).
3. Redo logs (supporting database recovery).

🔄 What is Meant by Proactive, Retroactive, and Simultaneous Update?

1. Proactive Update: Updates applied to the database before they become effective in the real world.
2. Retroactive Update: Updates applied to the database after they become effective in the real world.
3. Simultaneous Update: Updates applied to the database at the same time they become effective in the real world.