DBMS MCQ Questions for Placement

Correct Answer: A. To store and retrieve data in an organized way. A DBMS is designed to efficiently store, retrieve, and manage data within a database.

Correct Answer: A. Data redundancy. DBMS helps to reduce data redundancy, not increase it. Redundancy can lead to inconsistencies and inefficiencies.

Correct Answer: B. NoSQL DBMS. NoSQL databases are better suited for handling unstructured data due to their flexible schema design.

Correct Answer: A. Hide complex storage details from users. Data abstraction simplifies interaction with the database by hiding the physical storage details.

Correct Answer: B. Database administrator. The database administrator (DBA) has the highest level of authority in managing the database system.

Correct Answer: B. Hierarchical. Hierarchical models represent data as a hierarchy with a single root and branches of child nodes.

Correct Answer: B. Query processor. The query processor analyzes and executes queries to retrieve data from the database.

Correct Answer: C. View level. The view level presents a customized view of the data to specific users, hiding unnecessary details.

Correct Answer: C. Transaction manager. The transaction manager ensures that transactions are executed reliably and maintain database consistency.

Correct Answer: B. A description of the database structure. A schema defines the tables, attributes, and relationships within a database.

Correct Answer: B. A row in a table. A tuple represents a single record or entry in a table.

Correct Answer: A. To uniquely identify each tuple in a table. The primary key ensures that each row in a table can be uniquely identified.

Correct Answer: B. Selection. The selection operation filters rows based on a given condition.

Correct Answer: C. An attribute that references the primary key of another table. A foreign key establishes a link between tables by referencing the primary key of another table.

Correct Answer: C. Join. The join operation combines data from related tables.

Correct Answer: A. A minimal set of attributes that can uniquely identify a tuple. A candidate key is a potential primary key, and a table can have multiple candidate keys.

Correct Answer: B. To select specific columns from a table. Projection retrieves only the specified attributes from a relation.

Correct Answer: A. A set of possible values for an attribute. A domain defines the valid data type and range of values for an attribute.

Correct Answer: A. CREATE TABLE. CREATE TABLE defines the structure of a new table, including its columns and data types.

Correct Answer: B. ALTER TABLE. ALTER TABLE allows modifications to an existing table’s structure, such as adding or deleting columns.

Correct Answer: C. DROP TABLE. DROP TABLE permanently removes the table and all its data.

Correct Answer: A. WHERE. The WHERE clause specifies conditions that rows must meet to be included in the result.

Correct Answer: A. INSERT INTO. INSERT INTO inserts new rows into a table.

Correct Answer: B. UPDATE. UPDATE changes the values of existing rows in a table.

Correct Answer: A. DELETE. DELETE removes rows that match a specified condition.

Correct Answer: A. SELECT *. SELECT * retrieves all columns from the specified table.

Correct Answer: A. GRANT. GRANT gives specific permissions to a user or role.

Correct Answer: B. REVOKE. REVOKE takes away previously granted permissions.

Correct Answer: C. UNIQUE. The UNIQUE constraint prevents duplicate values in a column.

Correct Answer: D. NOT NULL. NOT NULL enforces that a column must have a value.

Correct Answer: A. UNION. UNION combines the result sets of two or more SELECT queries and removes any duplicate rows.

Correct Answer: B. INTERSECT. INTERSECT finds the common rows present in both SELECT queries.

Correct Answer: C. EXCEPT. EXCEPT (or MINUS in some databases) returns rows that are unique to the first query.

Correct Answer: C. GROUP BY. GROUP BY groups rows based on shared values in specified columns, often used with aggregate functions.

Correct Answer: D. HAVING. HAVING filters groups that result from a GROUP BY clause based on conditions involving aggregate functions.

Correct Answer: D. A real-world object or concept. Entities are the “things” about which we want to store data, like people, places, or objects.

Correct Answer: A. A characteristic or property of an entity. Attributes describe the properties of an entity, such as name, age, or address.

Correct Answer: B. A connection or association between two or more entities. Relationships define how entities are related to each other, such as “student enrolls in course.”

Correct Answer: C. The minimum and maximum number of instances of one entity that can be associated with instances of another entity. Cardinality specifies how many instances of each entity participate in a relationship (e.g., one-to-many).

Correct Answer: B. Whether the existence of an entity depends on its relationship with another entity. Participation constraints determine if an entity must participate in a relationship (total participation) or not (partial participation).

Correct Answer: A. To visually represent the entities, attributes, and relationships of a database. ER diagrams provide a graphical representation of the database design.

Correct Answer: C. As foreign keys. Relationships are implemented using foreign keys that link tables together.

Correct Answer: C. Relationships between attributes where the value of one attribute determines the value of another attribute. Functional dependencies define how one attribute determines the value of another within a table.

Correct Answer: B. To reduce data redundancy and improve data integrity. Normalization minimizes redundancy and ensures data consistency.

Correct Answer: A. 1NF. First Normal Form (1NF) requires that all attributes contain atomic values (no repeating groups).

Correct Answer: B. 2NF. Second Normal Form (2NF) addresses redundancy issues in tables with composite primary keys.

Correct Answer: C. 3NF. Third Normal Form (3NF) removes transitive dependencies to further reduce redundancy.

Correct Answer: B. A higher normal form than 3NF that addresses certain redundancy issues not covered by 3NF. BCNF is a stricter form of 3NF that deals with specific redundancy cases.

Correct Answer: B. Breaking down a table into smaller tables to reduce redundancy. Decomposition splits a table into multiple tables to adhere to normal forms.

Correct Answer: A. A property that ensures that data is not lost when tables are joined back together after decomposition. Lossless join guarantees that joining the decomposed tables will reconstruct the original table without data loss.

Correct Answer: B. To speed up data retrieval. Indexes are like lookups that allow the database to find data more quickly.

Correct Answer: A. B-tree index. B-tree indexes are efficient for searching ranges of values and are widely used in relational databases.

Correct Answer: B. Hash index. Hash indexes are good for equality searches but not ideal for range queries.

Correct Answer: A. An index that stores the actual data rows in the leaf nodes of the index. A clustered index determines the physical order of data rows on disk, and there can be only one clustered index per table.

Correct Answer: B. They can increase storage space requirements. Indexes require additional storage space and can slow down data modification operations (insert, update, delete).

Correct Answer: B. A step-by-step plan that the database uses to execute a query. The query execution plan outlines how the database will access and process the data to answer the query.

Correct Answer: B. To find the most efficient way to execute a query. Query optimization aims to minimize the time and resources needed to retrieve the requested data.

Correct Answer: A. By reducing the amount of data that needs to be scanned. Indexes help the database quickly locate relevant data, avoiding full table scans.

Correct Answer: B. A query optimization technique that estimates the cost of different execution plans and chooses the least expensive one. Cost-based optimization analyzes various execution strategies and selects the one with the lowest estimated cost.

Correct Answer: D. All of the above. All these factors, and more, contribute to the overall cost of executing a query.

Correct Answer: A. Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work.

Correct Answer: B. Consistency. Consistency guarantees that a transaction brings the database from one valid state to another.

Correct Answer: B. Isolation. Isolation ensures that concurrent transactions do not interfere with each other.

Correct Answer: A. Durability. Durability ensures that once a transaction is committed, its changes are permanent, even in case of system failures.

Correct Answer: B. To ensure data reliability and consistency. ACID properties are crucial for maintaining data integrity and consistency, especially in concurrent environments.

Correct Answer: B. A logical unit of work that consists of one or more database operations. A transaction is a sequence of operations that are executed as a single unit.

Correct Answer: A. Active, committed, aborted. A transaction can be active (in progress), committed (successfully completed), or aborted (rolled back due to an error).

Correct Answer: A. Managing multiple users accessing the database simultaneously. Concurrency control mechanisms ensure that simultaneous transactions do not interfere with each other and maintain data consistency.

Correct Answer: A. A situation where two or more transactions are blocked indefinitely, waiting for each other to release resources. Deadlocks occur when transactions are stuck in a circular wait for resources.

Correct Answer: A. Locking. Locking mechanisms (shared locks, exclusive locks) control access to data items to prevent conflicts.

Correct Answer: B. To restore the database to a consistent state after a failure. Log-based recovery uses a transaction log to undo or redo operations and recover from crashes.

Correct Answer: A. A record of all database operations. The transaction log keeps track of all changes made to the database.

Correct Answer: C. A technique to reduce the amount of data that needs to be processed during recovery. Checkpointing periodically writes modified data blocks to disk, reducing the work needed for recovery.

Correct Answer: A. Write-ahead logging (WAL). WAL ensures that changes are written to the log before they are applied to the database.

Correct Answer: B. To redo the changes made by a committed transaction that were not written to disk before a failure. REDO reapplies changes from the log to ensure that committed transactions are reflected in the database after recovery.

Correct Answer: D. Relational database. Relational databases are a traditional type of database, not a NoSQL database.

Correct Answer: A. Key-value store. Key-value stores are simple and efficient for storing and retrieving data using a unique key.

Correct Answer: B. Document database. Document databases are suitable for storing data with varying structures, like product catalogs or user profiles.

Correct Answer: C. Graph database. Graph databases excel at storing and querying relationships between data points.

Correct Answer: A. A distributed database can only guarantee two out of three properties: Consistency, Availability, and Partition tolerance. The CAP theorem highlights the trade-offs between consistency, availability, and partition tolerance in distributed systems.

Correct Answer: A. Storing the same data on multiple servers. Data replication creates copies of data across multiple nodes for redundancy and fault tolerance.

Correct Answer: B. Dividing data into smaller chunks and storing them on different servers. Data partitioning (sharding) distributes data across multiple servers to improve scalability and performance.

Correct Answer: A. To ensure that transactions involving data on multiple servers are executed consistently. Distributed transaction management coordinates transactions across multiple nodes to maintain data integrity.

Correct Answer: D. All of the above. Distributed databases introduce complexities in maintaining consistency, handling failures, and ensuring availability.

Correct Answer: B. A database that is spread across multiple servers. Distributed databases store data across multiple interconnected nodes.

Correct Answer: B. A large, centralized repository of historical data from various sources. Data warehouses store integrated data from operational systems for analysis and decision-making.

Correct Answer: A. A technique for querying and analyzing data in data warehouses. OLAP enables multi-dimensional analysis of data to identify trends and patterns.

Correct Answer: A. The process of extracting knowledge and patterns from large datasets. Data mining uses algorithms to discover hidden insights and relationships in data.

Correct Answer: D. All of the above. These are all common data mining techniques used for various purposes like grouping similar data points, predicting categories, and finding relationships between variables.

Correct Answer: A. To support decision-making and gain insights from data. Data warehousing and data mining help organizations extract valuable information from their data to make informed decisions.

Correct Answer: D. All of the above. All the options highlight the key differences between DELETE and TRUNCATE.

Correct Answer: C. Both A and B. Both A and B accurately describe the differences between primary and unique keys.

Correct Answer: A. A virtual table based on the result-set of an SQL statement. A view is a logical representation of data derived from one or more base tables.

Correct Answer: B. The process of intentionally introducing redundancy into a database to improve performance. Denormalization is sometimes used to optimize read operations by adding redundant data.

Correct Answer: A. A pre-compiled SQL code that can be reused. Stored procedures are pre-written SQL code blocks that can be executed with a single call.

Correct Answer: D. All of the above. Stored procedures offer benefits in terms of performance, security, and reduced network load.

Correct Answer: A. A stored procedure that automatically executes in response to certain events. Triggers are special stored procedures that are invoked when specific actions (insert, update, delete) occur on a table.

Correct Answer: A. INNER JOIN, OUTER JOIN (LEFT, RIGHT, FULL), SELF JOIN. These are the main types of joins used to combine data from multiple tables.

Correct Answer: A. INNER JOIN returns only the matching rows from both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table. This accurately describes the difference between INNER JOIN and LEFT JOIN.

Correct Answer: A. WHERE clause is used to filter rows before grouping, while HAVING clause is used to filter groups after grouping. This correctly explains the distinction between the WHERE and HAVING clauses.