DBMSD MCQs
Q.1 A query plan in a database system is:
A. A set of operations used to secure the database
B. A roadmap for data backup procedures
C. A detailed plan for executing a query, outlining steps and resource use
D. A user guide for database maintenance
Q.2 Which of the following best describes the concept of “transaction isolation level”?
A. The ability to execute SQL transactions in parallel
B. The degree to which transaction changes are visible to other transactions
C. The speed at which transactions are executed
D. The security level applied to transactions
Q.3 What does the term ‘cardinality’ refer to in the context of databases?
A. The uniqueness of data in a column
B. The number of rows in a table
C. The number of tables in a database
D. The relationships between tables
Q.4 The primary purpose of normalizing a database is to:
A. Reduce the database size
B. Increase query speed
C. Reduce data redundancy and improve data integrity
D. Simplify security management
Q.5 In SQL, the JOIN clause is used to:
A. Combine rows from only two tables based on related columns
B. Delete matching rows in two tables
C. Update matching columns in two tables
D. Combine rows from two or more tables based on related columns
Q.6 Which of the following is a benefit of query optimization in databases?
A. Reduces the need for indexes
B. Increases the complexity of queries
C. Improves query performance
D. Decreases database security
Q.7 An index in a database is used to:
A. Decrease storage space
B. Increase data redundancy
C. Speed up data retrieval
D. Secure the database
Q.8 Which SQL statement is used to extract data from a database?
A. INSERT INTO
B. UPDATE
C. SELECT
D. DELETE
Q.9 In an ER diagram, an entity set that does not have sufficient attributes to form a primary key is marked as:
A. Weak entity
B. Strong entity
C. Derived entity
D. Composite entity
10. An ER diagram incorrectly shows two entities directly connected by more than one relationship.
What is this error known as?
A. Relationship redundancy
B. Cardinality mismatch
C. Entity ambiguity
D. Attribute duplication
Q.11 What is the correct SQL statement to create a foreign key in the “Orders” table that references the “Customers” table’s primary key?
A. CREATE FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
B. ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
C. ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers
D. CREATE TABLE Orders ADD FOREIGN KEY CustomerID REFERENCES Customers
Q.12 To add a column named “Email” of type VARCHAR to an existing table named “Users,” which SQL statement is correct?
A. ALTER TABLE Users ADD COLUMN Email VARCHAR
B. ALTER TABLE Users ADD Email VARCHAR
C. UPDATE TABLE Users ADD Email VARCHAR
D. INSERT INTO Users (Email) VALUES (‘VARCHAR’)
Q.13 Which SQL keyword is used to remove a table from a database?
A. DROP
B. DELETE
C. REMOVE
D. ALTER
Q.14 An attribute that uniquely identifies each entity in an entity set is called a:
A. Composite attribute
B. Derived attribute
C. Multi-valued attribute
D. Key attribute
Q.15 In a “many-to-many” relationship, which of the following is required to model this relationship in a relational database?
A. A composite primary key
B. A junction table
C. A foreign key constraint
D. An index
Q.16 Which of the following is NOT a type of integrity constraint in a relational database?
A. Referential integrity
B. Semantic integrity
C. Structural integrity
D. Domain integrity
Q.17 A relationship in an ER model that involves more than two entity types is known as:
A. A binary relationship
B. A ternary relationship
C. A primary relationship
D. An associative relationship
Q.18 The uniqueness of each row in a database table is ensured by:
A. The primary key
B. The foreign key
C. An index
D. A trigger
Q.19 Which of the following best describes an attribute in an ER model?
A. A link between entities
B. A primary key
C. A characteristic or property of an entity
D. A type of relationship
Q.20 In an ER diagram, what do entities represent?
A. Relationships between tables
B. Physical storage locations
C. Real-world objects or concepts
D. Database queries
Q.21 What is the purpose of an Entity-Relationship (ER) diagram?
A. To document the syntax of SQL
B. To model physical database storage
C. To illustrate database relationships
D. To monitor database performance
Q.22 A database query returns unexpected duplicates of some records. What is the most likely reason for this occurrence?
A. The SELECT statement includes a JOIN without proper conditions
B. Incorrect use of DISTINCT
C. The database has been corrupted
D. A WHERE clause is missing
Q.23 What does the following SQL query do? SELECT COUNT(*) FROM Orders;
A. Counts the number of orders in the Orders table
B. Updates the number of orders
C. Deletes the Orders table
D. Creates a new Orders table
Q.24 Which of the following is a correct syntax to select all columns from a table named “Customers”?
A. SELECT * FROM Customers
B. SELECT Customers
C. SELECT all FROM Customers
D. GET * FROM Customers
Q.25 In the context of database security, what does the term ‘authentication’ refer to?
A. Ensuring data is not corrupted
B. Verifying user identity
C. Encrypting data
D. Backing up data
Q.26 Which of the following SQL statements is used to insert a new record into a table?
A. SELECT
B. UPDATE
C. INSERT INTO
D. DELETE
Q.27 The process of organizing data into tables and establishing relationships between them is known as:
A. Data mining
B. Data modeling
C. Data warehousing
D. Database design
Q.28 Which of the following is NOT a characteristic of a DBMS?
A. Data redundancy
B. Data integrity
C. Data security
D. Data independence
Q.29 What is the primary function of a Database Management System (DBMS)?
A. Managing software installation
B. Data processing
C. Data storage and retrieval
D. Computer networking
Q.30 Which of the following best defines a database?
A. A collection of programs
B. A type of software
C. A collection of data
D. A type of computer
Q.31 What is a deadlock in the context of transaction management?
A. A situation where transaction operations are waiting indefinitely for each other to release locks
B. A failed transaction that cannot be rolled back
C. A transaction that does not comply with ACID properties
D. An isolation level that has been incorrectly set
Q.32 Isolation in database transactions ensures that:
A. Transactions are processed in a linear fashion
B. The operations of one transaction are visible to another transaction
C. Multiple transactions can be made to appear as if they are running in parallel
D. Each transaction is aware of all other transactions in the system
Q.33 In the context of transactions, what does the consistency property ensure?
A. The database remains in a consistent state before and after the transaction
B. The transaction only shows committed data to external users
C. Transactions are isolated from each other
D. The results of the transaction are permanently stored in the database
Q.34 Which of the following is NOT a property of transactions (ACID properties)?
A. Authenticity
B. Atomicity
C. Consistency
D. Isolation
Q.35 The durability property of a transaction ensures that:
A. Changes made by a transaction are not permanent
B. Changes are visible even before the transaction is committed
C. Once a transaction commits, its changes are lost on system failure
D. Once a transaction commits, its changes are permanent, surviving system failures
Q.36 Which property of a transaction ensures that all operations within the transaction are completed successfully, or none of them are?
A. Atomicity
B. Consistency
C. Isolation
D. Durability
Q.37 During schema refinement, a designer notices that certain attributes depend on other non-primary attributes.
Which normal form is not being adhered to?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Q.38 A table contains a non-primary key column that depends on only part of a composite primary key.
What normalization issue does this indicate?
A. Partial dependency, indicating a violation of 2NF
B. Transitive dependency, indicating a violation of 3NF
C. Non-atomic values, indicating a violation of 1NF
D. None, this is an acceptable design
Q.39 A database table is noticed to have duplicate rows.
Which normalization form is most likely being violated?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Q.40 What is the correct way to change the data type of a column in an existing table in SQL?
A. MODIFY TABLE table_name CHANGE column_name column_name NEW_DATA_TYPE
B. ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE NEW_DATA_TYPE
C. ALTER TABLE table_name MODIFY COLUMN column_name NEW_DATA_TYPE
D. ALTER TABLE table_name CHANGE column_name column_name NEW_DATA_TYPE
Q.41 Which SQL statement is used to delete a constraint from a table?
A. DELETE CONSTRAINT constraint_name FROM table_name
B. REMOVE CONSTRAINT constraint_name FROM table_name
C. ALTER TABLE table_name DROP CONSTRAINT constraint_name
D. DROP CONSTRAINT constraint_name FROM table_name
Q.42 In SQL, how can you modify an existing table to add a foreign key constraint referencing another table?
A. ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column1) REFERENCES other_table(column2)
B. ALTER TABLE table_name MODIFY COLUMN column_name FOREIGN KEY REFERENCES other_table(column_name)
C. ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table
D. USE table_name ADD FOREIGN KEY REFERENCES other_table
Q.43 Which SQL keyword is used to create a unique constraint on a column to enforce uniqueness?
A. UNIQUE
B. PRIMARY KEY
C. CHECK
D. FOREIGN KEY
Q.44 In the context of database normalization, what is denormalization used for?
A. Increasing the complexity of the database
B. Undoing the normalization process to improve query performance
C. Secure data storage
D. Create redundancy for backup purposes
Q.45 Dependency preservation in database normalization ensures that:
A. All functional dependencies are represented in one table
B. Functional dependencies are preserved across table decompositions
C. Only primary keys have dependencies
D. All tables are dependent on a single table
Q.46 Lossless join property ensures that:
A. The join operation increases data redundancy
B. Data can be reconstructed exactly after decomposition
C. Data is lost during normalization
D. The join operation reduces query performance
Q.47 A relation R is in which normal form if every non-prime attribute of R is non-transitively dependent on every key of R?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Q.48 Which normal form is concerned with removing partial dependency between columns of a database table?
A. 1NF
B. 2NF
C. 3NF
D. BCNF
Q.49 The process of minimizing redundancy and dependency by organizing fields and table relationships is known as:
A. Denormalization
B. Normalization
C. Indexing
D. Partitioning
Q.50 Which of the following is NOT a normal form?
A. First Normal Form (1NF)
B. Zero Normal Form (0NF)
C. Second Normal Form (2NF)
D. Third Normal Form (3NF)
Q.51 What is the goal of normalization in database design?
A. To reduce data redundancy and ensure data integrity
B. To increase database size
C. To make database security more complex
D. To speed up query processing
Q.52 During the execution of a transaction, a deadlock occurs.
What is a typical solution to resolve this issue?
A. Increasing the transaction timeout period
B. Rolling back one of the transactions involved in the deadlock
C. Eliminating all indexes on the tables involved
D. Splitting the transaction into smaller transactions
Q.53 A complex query involving multiple JOIN operations is performing poorly.
What is a common method to improve its performance?
A. Reducing the number of columns in the SELECT clause
B. Using subqueries instead of JOINs
C. Optimizing the join order
D. Increasing the database server’s memory
Q.54 After adding a new index to a table, a previously fast query has become significantly slower.
What is the most likely reason?
A. The index has increased the data retrieval time
B. The new index is not used by the query
C. The index has caused additional overhead for data modifications
D. The database requires reorganization
Q.55 A developer runs a query to select all records from a table, but it returns no results despite the table not being empty.
What is the likely cause?
A. A syntax error in the SELECT statement
B. The WHERE clause is filtering out all records
C. The table is locked
D. The database connection is lost
Q.56 What is the purpose of the SQL command EXPLAIN?
A. To document the structure of the database
B. To describe the syntax of SQL commands
C. To provide a detailed execution plan of a query
D. To correct errors in SQL queries
Q.57 Given the following SQL statement:
SELECT Name FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Budget > 100000); What does this query do?
A. Retrieves the names of all employees who work in departments with a budget greater than 100000
B. Deletes names of employees in departments with high budgets
C. Updates the names of employees in well-funded departments
D. Inserts names into Employees for departments with large budgets
Q.58 To update the “Salary” column in the “Employees” table by increasing all salaries by 10%, which SQL statement is correct?
A. UPDATE Employees SET Salary = Salary * 1.1
B. UPDATE Employees INCREASE Salary BY 10%
C. ALTER TABLE Employees MODIFY Salary = Salary * 1.1
D. CHANGE Employees SET Salary = Salary + (Salary * 0.1)
Q.59 Which SQL clause is used to filter groups of rows that have been aggregated?
A. WHERE
B. HAVING
C. GROUP BY
D. ORDER BY
Q.60 What is the correct SQL syntax to return only distinct (different) values from the “Name” column in the “Employees” table?
A. SELECT DISTINCT Name FROM Employees
B. SELECT UNIQUE Name FROM Employees
C. SELECT DIFFERENT Name FROM Employees
D. SELECT Name FROM Employees DISTINCT
Q.61 Which SQL statement is used to create a full-text index in MySQL?
A. CREATE FULLTEXT INDEX ft_index ON Articles(content)
B. FULLTEXT INDEX CREATE ON Articles(content)
C. INDEX FULLTEXT CREATE Articles(content)
D. CREATE INDEX FULLTEXT ON Articles(content)
Q.62 How can you create a non-clustered index on the “email” column of the “Users” table in SQL?
A. CREATE NONCLUSTERED INDEX idx_email ON Users(email)
B. CREATE INDEX idx_email ON Users(email) NONCLUSTERED
C. INDEX CREATE ON Users(email) NONCLUSTERED
D. CREATE INDEX idx_email ON Users USING NONCLUSTERED(email)
Q.63 What is a B-tree index suitable for?
A. Only equality searches
B. Only range searches
C. Both equality and range searches
D. Neither equality nor range searches
Q.64 Which of the following best describes the difference between a clustered and a non-clustered index?
A. A clustered index speeds up data insertion, whereas a non-clustered index speeds up data deletion
B. A clustered index affects the physical order of rows, whereas a non-clustered index does not
C. A clustered index can only be created on primary keys, whereas a non-clustered index can be created on any column
D. A clustered index is automatically created when a table is created, whereas a non-clustered index must be created manually
Q.65 In a relational database, what does a composite index refer to?
A. An index that combines multiple tables
B. An index built on a single column
C. An index that includes multiple columns from a single table
D. An index used exclusively for joins
Q.66 What is a full-text search primarily used for?
A. Searching for numeric data
B. Searching for exact matches in textual data columns
C. Performing complex searches within textual data columns
D. Indexing foreign keys
Q.67 Which type of database index is optimized for equality searches?
A. Clustered index
B. Non-clustered index
C. Full-text index
D. Bitmap index
Q.68 What is the primary purpose of indexing in a database?
A. To increase transaction speeds
B. To decrease database storage requirements
C. To speed up the retrieval of records based on attribute values
D. To secure the database
Q.69 You notice that a particular query within a transaction is causing lock contention.
What is a first step in addressing this issue?
A. Re-writing the query to access fewer rows
B. Increasing the database’s hardware resources
C. Switching to table-level locks
D. Disabling locks for the transaction
Q.70 After implementing row-level locking, the system experiences deadlocks.
Which approach can help minimize this issue?
A. Using table-level locks instead
B. Applying a consistent locking order
C. Reducing the transaction isolation level
D. Implementing lock escalation
Q.71 A transaction frequently fails due to lock timeouts.
What is a simple strategy to reduce the likelihood of this happening?
A. Decrease the transaction isolation level
B. Increase the lock timeout duration
C. Split the transaction into smaller parts
D. All of the above
Q.72 How do you enable automatic detection and resolution of update conflicts in Oracle when using optimistic concurrency control?
A. ENABLE AUTOMATIC CONFLICT RESOLUTION
B. SET TRANSACTION RESOLVE CONFLICTS AUTOMATICALLY
C. DBMS_LOCK.RESOLVE
D. DBMS_CONFLICT.RESOLVE_AUTOMATICALLY
Q.73 Which command is used in MySQL to start a transaction with a consistent snapshot, providing repeatable reads for the duration of the transaction?
A. START TRANSACTION WITH CONSISTENT SNAPSHOT
B. BEGIN WORK WITH CONSISTENT SNAPSHOT
C. CREATE TRANSACTION WITH SNAPSHOT
D. BEGIN TRANSACTION CONSISTENT SNAPSHOT
Q.74 In PostgreSQL, how can you acquire an exclusive lock on a table to prevent other transactions from reading or writing to it?
A. LOCK TABLE table_name IN EXCLUSIVE MODE
B. EXCLUSIVE LOCK ON table_name
C. SET LOCK table_name EXCLUSIVE
D. LOCK table_name EXCLUSIVE
Q.75 To set the transaction isolation level to Serializable in SQL Server, which command should be used?
A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. SET ISOLATION LEVEL SERIALIZABLE
C. TRANSACTION SET ISOLATION LEVEL SERIALIZABLE
D. SET TRANSACTION LEVEL AS SERIALIZABLE
Q.76 In database systems, the phenomenon where a transaction re-reads data it has previously read and finds that another transaction has modified it is called:
A. Dirty read
B. Non-repeatable read
C. Phantom read
D. Serialization anomaly
Q.77 The isolation level that allows dirty reads, non-repeatable reads, and phantom reads is:
A. Read Uncommitted
B. Read Committed
C. Repeatable Read
D. Serializable
Q.78 Which of the following scenarios is a potential drawback of using optimistic concurrency control?
A. Transactions are serialized automatically
B. Deadlocks are more frequent
C. Increased overhead due to rollback of transactions
D. Reduced throughput due to excessive locking
Q.79 What is the primary goal of the two-phase locking (2PL) protocol?
A. To ensure all transactions are executed in parallel
B. To allow transactions to access any data at any time
C. To prevent deadlocks
D. To ensure serializability of transactions
Q.80 In the context of concurrency control, what does the term “deadlock” refer to?
A. A lock that cannot be released
B. A transaction that cannot be rolled back
C. A situation where transactions wait indefinitely for each other
D. A failed concurrency control mechanism
Q.81 Which concurrency control technique involves locking resources to prevent concurrent transactions from accessing them simultaneously?
A. Timestamp ordering
B. Lock-based protocols
C. Validation-based protocols
D. Snapshot isolation
Q.82 A transaction is experiencing repeated failures due to lock timeouts.
What strategy can help resolve this issue?
A. Decreasing the isolation level of the transaction
B. Increasing the lock timeout value
C. Splitting the transaction into smaller transactions
D. All of the above
Q.83 After changing the isolation level of transactions, a significant decrease in performance is observed.
What could be the reason?
A. Increased lock contention due to a higher isolation level
B. Decreased cache utilization
C. Hardware malfunctions
D. Network issues
Q.84 When attempting to execute multiple transactions concurrently, you notice that some transactions are waiting indefinitely. What is the likely cause?
A. Network latency
B. Deadlocks
C. Inefficient query execution
D. Insufficient database indexing
Q.85 A transaction fails to commit and is rolled back.
What is a common reason for this behavior?
A. The transaction was manually rolled back by the user
B. A deadlock was detected
C. A data validation rule was violated
D. The database system crashed
Q.86 Which SQL command is used to save a point within a transaction that can be rolled back to, without affecting the preceding statements of the transaction?
A. SAVEPOINT
B. CHECKPOINT
C. COMMIT TO POINT
D. ROLLBACK TO SAVEPOINT
Q.87 How can the isolation level of a transaction be set in SQL Server to prevent dirty reads?
A. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
B. SET TRANSACTION ISOLATION LEVEL READ COMMITTED
C. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
D. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Q.88 In SQL, how can you force a transaction to roll back?
A. ROLLBACK TRANSACTION
B. UNDO TRANSACTION
C. RESET TRANSACTION
D. CANCEL TRANSACTION
Q.89 Which SQL statement is used to start a transaction in most RDBMS?
A. BEGIN TRANSACTION
B. START
C. BEGIN
D. INITIATE TRANSACTION
Q.90 What mechanism is commonly used to ensure the durability of transactions in database systems?
A. Log-based recovery
B. Two-phase locking
C. Timestamp ordering
D. Savepoints
Q.91 What distinguishes data warehousing from databases in terms of data analysis?
A. Data warehousing focuses on capturing data from diverse sources, whereas databases focus on storing real-time transactional data
B. Data warehousing is optimized for read-intensive operations, whereas databases are optimized for write-intensive operations
C. Data warehouses primarily use SQL for querying, whereas databases use NoSQL
D. Data warehouses store unstructured data, whereas databases store structured data
Q.92 In data mining, what does the term “classification” refer to?
A. The process of organizing data into categories for efficient storage
B. Dividing a database into smaller, manageable parts
C. The task of predicting the class label of given input data
D. The restructuring of a data warehouse
Q.93 What is a data warehouse?
A. A database optimized for transaction processing
B. A collection of tools for web data mining
C. A centralized repository for integrating data from various sources
D. A type of NoSQL database
Q.94 How can “hotspotting” issues be mitigated in a Big Data application using a key-value store for time-series data?
A. By writing all data to a single key
B. By using sequential keys for data insertion
C. By sharding data across keys using a hash of the timestamp
D. By increasing the write throughput limits on the database
Q.95 After adding a new node to a NoSQL database cluster, data is not evenly distributed across nodes.
What action can help redistribute the data more evenly?
A. Manually transferring data between nodes
B. Rebooting the cluster
C. Updating the database schema
D. Triggering a rebalance operation across the cluster
Q.96 A developer notices slow query performance on a document store database when querying by a frequently accessed field.
What is a potential solution?
A. Adding an index on the frequently accessed field
B. Increasing the memory allocated to the database
C. Splitting the database into smaller, separate databases
D. Replicating the database for read-heavy workloads
Q.97 Which command in Redis is used to set a key “user:100” with a value “John Doe” that expires after 10 minutes?
A. SET user:100 “John Doe” EXPIRE 600
B. SET user:100 “John Doe” TTL 600
C. SET user:100 “John Doe” WITH EXPIRY 600
D. SETEX user:100 600 “John Doe”
Q.98 In Cassandra, how can you define a table with automatic expiration of data (TTL) for a column named “message”?
A. CREATE TABLE messages (id UUID PRIMARY KEY, message text, TTL int)
B. CREATE TABLE messages (id UUID PRIMARY KEY, message text) WITH default_time_to_live=3600
C. ALTER TABLE messages ADD TTL (message, 3600)
D. None of the above
Q.99 How do you create a collection named “users” in MongoDB?
A. db.createCollection(“users”)
B. CREATE COLLECTION users
C. db.users.create()
D. mongodb.create(“users”)
Q.100 How does data consistency in eventual consistency models compare to that in traditional ACID transaction models?
A. It guarantees immediate consistency
B. It never achieves consistency
C. It achieves consistency over time, after all updates propagate
D. It prioritizes availability over consistency
Q.101 In big data processing, what does the term “MapReduce” refer to?
A. A data storage technique for large datasets
B. A programming model for processing large data sets with a parallel, distributed algorithm on a cluster
C. A type of NoSQL database
D. A data backup and recovery strategy
Q.102 What is “sharding” in the context of NoSQL databases?
A. The process of replicating data across multiple servers for fault tolerance
B. Partitioning data across multiple servers to improve performance
C. Encrypting data at rest
D. Compressing data to save storage space
Q.103 Which NoSQL database type is optimized for storing and querying connected data, such as social networks or recommendation systems?
A. Document store
B. Key-value store
C. Graph database
D. Column-family store
Q.104 What distinguishes NoSQL databases from traditional relational databases?
A. Strict schema enforcement
B. Support for SQL syntax
C. Ability to handle unstructured and semi-structured data efficiently
D. Primarily used for transactional data
Q.105 How can data inconsistency issues be resolved in a system using eventual consistency?
A. By immediately synchronizing all data replicas
B. By using a conflict resolution mechanism such as last write wins
C. By reducing the number of database replicas
D. By avoiding updates to data
Q.106 After partitioning a table across multiple distributed database nodes, some transactions fail due to lost updates. What is a likely cause and solution?
A. The transactions are accessing non-partitioned data, requiring data replication
B. Transactions are not being distributed correctly, necessitating a review of the partitioning strategy
C. Locks are not being acquired on the data, suggesting the need for explicit locking mechanisms
D. Partitioned data is not being synchronized properly, indicating the need for transaction coordination
Q.107 A distributed database system is experiencing slow query responses.
What could be a reason for increased latency?
A. A single overloaded server
B. Network latency between distributed nodes
C. Incorrectly configured indexes
D. All of the above
Q.108 For ensuring strong consistency across replicas in a Cassandra cluster, which consistency level should be used for both reads and writes?
A. ONE
B. QUORUM
C. ALL
D. LOCAL_QUORUM
Q.109 How do you enable automatic sharding in a MongoDB distributed database to distribute data across multiple servers?
A. Use the sh.enableSharding(“databaseName”) command
B. Set up a shard cluster and define shard keys for collections
C. Implement a custom sharding algorithm
D. Configure replication sets for automatic sharding
Q.110 In a distributed SQL database, how can you query data from a remote table named “RemoteOrders” that resides on a server named “RemoteServer”?
A. SELECT * FROM RemoteServer.RemoteOrders
B. SELECT * FROM RemoteOrders AT RemoteServer
C. SELECT * FROM LINKED.RemoteServer.RemoteOrders
D. SELECT * FROM RemoteOrders@RemoteServer
Q.111 In distributed databases, what does “eventual consistency” mean?
A. That the database will be immediately consistent after any transaction
B. That the database may temporarily have different data copies, which will become consistent over time
C. That consistency is not guaranteed in any form
D. That consistency between database copies is achieved through periodic synchronization
Q.112 Which of the following best describes the concept of “horizontal scaling” in distributed databases?
A. Adding more columns to a database table
B. Splitting a database across different physical locations
C. Increasing the storage capacity of a single database server
D. Adding more servers or nodes to manage increased load
Q.113 What challenge is primarily addressed by the two-phase commit protocol in distributed database systems?
A. Data consistency across multiple sites
B. Encrypting data transmitted between sites
C. Optimizing query performance across networks
D. Managing distributed database schema changes
Q.114 In a distributed database system, what does the term “data fragmentation” refer to?
A. The process of breaking down data into smaller, manageable parts for storage
B. The unintended loss of data due to network issues
C. The division of data into different types for analysis
D. Segmenting and storing parts of a database at different locations based on certain criteria
Q.115 What is a primary benefit of using distributed databases over centralized databases?
A. Increased data redundancy
B. Simpler data management
C. Improved data availability and disaster recovery
D. Reduced data security
Q.116 You observe that a specific query is not using an available index, leading to poor performance.
What could be done to encourage the use of the index?
A. Rebuild the index
B. Update the database statistics
C. Increase the query timeout
D. Change the query’s isolation level
Q.117 After adding a new index to a table, certain update operations on the table have become slower.
What is a likely explanation?
A. The new index requires additional storage space
B. The update operations do not use the index
C. Updating the table now requires updating the index as well
D. The index has caused a lock on the table
Q.118 A query that used to run quickly is now running slowly, even though no changes were made to the query itself.
What is a likely cause?
A. The database has been indexed incorrectly
B. The underlying data has significantly increased in size
C. A network issue is causing delayed responses
D. The query optimizer is malfunctioning
Q.119 How do you enforce uniqueness on a composite index in SQL Server for columns “FirstName” and “LastName” in the “Employees” table?
A. CREATE UNIQUE INDEX ux_name ON Employees(FirstName, LastName)
B. UNIQUE INDEX CREATE Employees(FirstName, LastName)
C. CREATE INDEX UNIQUE Employees ON (FirstName, LastName)
D. INDEX CREATE UNIQUE ON Employees(FirstName, LastName)
Q.120 In PostgreSQL, how do you add a GIN index to an array column named “tags” in a table named “Posts”?
A. CREATE INDEX gin_tags ON Posts USING GIN(tags)
B. GIN INDEX CREATE ON Posts(tags)
C. CREATE GIN INDEX ON Posts(tags)
D. INDEX GIN CREATE ON Posts USING (tags)
Q.121 What is a common challenge when integrating IoT devices with real-time databases, and how can it be addressed?
A. Data volume exceeds storage capacity; addressed by increasing storage space
B. Data arrives faster than it can be processed; addressed by implementing stream processing solutions
C. IoT devices use incompatible data formats; addressed by standardizing data formats
D. All devices require constant internet connection; addressed by creating offline data processing capabilities
Q.122 How can “query sprawl” in big data applications be managed effectively?
A. By limiting the number of users accessing the database
B. By using stricter schema definitions
C. By optimizing query execution and caching frequently accessed data
D. By reducing the size of the dataset
Q.123 A NoSQL database’s response times degrade as data volume grows.
What strategy can improve performance?
A. Normalizing the database schema
B. Implementing sharding or partitioning strategies
C. Converting the NoSQL database to a SQL database
D. Decreasing the write consistency level
Q.124 How can you enable automatic scaling in a cloud-based NoSQL database service?
A. SET AUTOSCALE ON
B. MODIFY DATABASE SET SCALING=AUTOMATIC
C. UPDATE SCALING POLICY AUTO
D. Configure auto-scaling settings through the database’s management console
Q.125 What SQL extension is used to query JSON data within a PostgreSQL database?
A. JSON_QUERY
B. SELECT JSON
C. JSONB_PATH_QUERY
D. GET_JSON_ITEM
Q.126 What is the significance of “Edge Computing” in the context of database technologies?
A. Reducing cloud computing costs
B. Increasing the physical security of data centers
C. Minimizing latency by processing data closer to the source of data generation
D. Maximizing the efficiency of centralized databases
Q.127 How do distributed ledger technologies (such as blockchain) enhance data security in database applications?
A. By centralizing data storage
B. By making data immutable and enabling transparent access
C. By encrypting all stored data
D. By limiting data access to administrators
Q.128 What differentiates Graph databases from traditional relational databases?
A. Graph databases use SQL for querying, while relational databases do not
B. Graph databases are primarily used for data warehousing
C. Graph databases are optimized for storing and querying data relationships
D. Relational databases cannot store complex, interconnected data
Q.129 In the context of database technologies, what is a “Data Lake”?
A. A type of SQL database optimized for large-scale transactions
B. A storage repository that holds a vast amount of raw data
C. A new algorithm for data encryption
D. An advanced form of data masking technique
Q.130 Which technology is commonly associated with real-time analytics and data processing?
A. Hadoop Distributed File System (HDFS)
B. Online Analytical Processing (OLAP)
C. Stream Processing
D. Data Warehousing
Q.131 How can SQL injection vulnerabilities be prevented?
A. Validating user inputs
B. Sanitizing user inputs
C. Using prepared statements
D. Sanitizing and using prepared statements
Q.132 How can “privilege escalation” vulnerabilities be addressed?
A. Regular database software updates
B. Strict password policies
C. Regular security audits
D. Security patch implementation
Q.133 A user reports losing access to a table.
What could be the cause?
A. Permissions were revoked
B. The table was deleted
C. Database restored to a previous state
D. Permissions error
Q.134 How do you revoke all privileges from a user in SQL?
A. REVOKE ALL PRIVILEGES FROM user_name
B. REVOKE ACCESS FROM user_name
C. REMOVE ALL PRIVILEGES user_name
D. DENY ALL FROM user_name
Q.135 How do you create a new user with specific privileges in MySQL?
A. CREATE USER ‘user_name’ IDENTIFIED BY ‘password’; GRANT privileges ON database_name.* TO ‘user_name’
B. CREATE USER ‘user_name’ WITH privileges
C. USE ‘user_name’ WITH privileges
D. INITIATE USER ‘user_name’ WITH privileges
Q.136 How can you grant a user read-only access to a specific table in SQL?
A. GRANT SELECT ON table_name TO user_name
B. ALTER TABLE table_name ALLOW READ FOR user_name
C. ENABLE READ ON table_name TO user_name
D. SET READ PERMISSION ON table_name FOR user_name
Q.137 How does role-based access control (RBAC) enhance database security?
A. By encrypting all data
B. By allowing unrestricted access
C. By assigning permissions to roles, not individuals
D. By using complex passwords
Q.138 What mechanism manages and validates user access in a database?
A. Data encryption algorithms
B. Data indexing strategies
C. Authentication and authorization
D. Data normalization techniques
Q.139 What is SQL injection?
A. A method for encrypting SQL queries
B. A technique for improving SQL query performance
C. An attack by inserting malicious SQL into a query
D. A query optimization tool
Q.140 Which database security feature limits data exposure by hiding certain data elements from unauthorized users?
A. Data masking
B. Data encryption
C. Data replication
D. Data indexing
Q.141 What does the principle of least privilege in database security entail?
A. Granting users minimal permissions necessary
B. Granting all users admin privileges
C. Storing all data centrally
D. Encrypting all data
Q.142 What is the primary purpose of database encryption?
A. To increase database performance
B. To reduce data storage requirements
C. To protect sensitive data from unauthorized access
D. To improve data integrity
Q.143 How can “dimensional skew” in a data warehouse be identified and corrected?
A. By normalizing all tables to 3NF
B. By redistributing data to ensure uniform access patterns
C. By converting all OLAP cubes to ROLAP models
D. By implementing data compression techniques
Q.144 During data mining, the model’s accuracy drastically decreases when applied to new datasets.
What is a likely cause?
A. Overfitting to the training data
B. Underfitting to the training data
C. Insufficient data preprocessing
D. The data mining algorithm is not suitable for the task
Q.145 A materialized view in a data warehouse is not updating correctly.
What could be a reason?
A. The source data has not changed
B. The view is not scheduled for regular refreshes
C. The database lacks sufficient storage space
D. The view’s definition does not include all necessary joins
Q.146 In a data warehousing environment, how is a materialized view refreshed to ensure it contains the latest data?
A. Refreshing it manually by executing a specific SQL command
B. Automatically, based on a schedule defined within the database management system
C. By triggering a script when the underlying data changes
D. All of the above
Q.147 Which command is used to initiate the mining process on a dataset named “customer_data” using the Apriori algorithm in a data mining software tool?
A. MINING START customer_data USING Apriori
B. APRIORI MINING ON customer_data
C. USE Apriori ON customer_data
D. START MINING customer_data WITH Apriori
Q.148 How can you create an OLAP cube in SQL Server Analysis Services (SSAS)?
A. Using the CREATE CUBE statement in SQL
B. Through the SQL Server Management Studio (SSMS) interface
C. By executing a special OLAP cube creation script
D. Defining it in an XMLA script file
Q.149 Which data mining technique is used to uncover hidden patterns and relationships in data, but does not predict outcomes?
A. Classification
B. Regression
C. Clustering
D. Decision trees
Q.150 In the context of data warehousing, what is “dimensional modeling”?
A. The process of converting unstructured text into a structured format
B. A technique for the physical design of databases
C. The creation of a conceptual model for analyzing data, typically involving facts and dimensions
D. The use of 3D models to represent data visually
