50 Best Multiple Choice Questions for RDBMS (Relational Database Management System)

Relational Database Management System

An RDBMS is a database engine or system based on the relational model specified by Edgar F. Codd, with the ability to use tables for data storage while maintaining and enforcing certain data relationships. Most modern commercial and open-source database applications are relational in nature.

Basics of RDBMS

Domain refers to a set of atomic values, where each value is indivisible. A common way of specifying a domain is by data type. A relation or table is a set of tupes, rows, entities or records, where each row is called a tuple. The number of columns or attributes of a relation is known as its degree, while the number of rows, tuples or records in a relational instance is known as its cardinality.

Properties of RDBMS

Cells in a relational table contain atomic values of the same kind in a unique row with a unique column name. In a relational schema, no two tables can have the same name. The sequence of rows and columns is insignificant.

Problems in RDBMS

The problems in RDBMS include Update Anomalies, Insertion Anomalies, Modification Anomalies, and Deletion Anomalies. These anomalies cause redundant and accidental loss of information, make independent piece of information difficult to record, require updates to occur at multiple locations, and can unintentionally remove other information.

Normalization

Normalization is a refinement process that includes creating tables and establishing relationships between those tables to eliminate redundancy and inconsistent dependency. Without normalization, database systems may be inaccurate, slow, and inefficient, and may not produce expected data.

RDBMS MCQs

There are no RDBMS MCQs in this text.

Full Form of RDBMS

The full form of RDBMS is:

Relational Database Management System

It is a software system that is used to manage relational databases.

Property of Transactions that Protects Data from System Failure

In a transaction, the property of durability protects data from system failure. It ensures that once the transaction is committed, the changes made to the data will survive any subsequent failures, such as power outages or crashes. This is achieved by synchronously writing the transaction data to disk or other durable storage before the transaction completes.

The other properties of a transaction are atomicity, which ensures that either all or none of the transaction is executed; consistency, which ensures that the data meets all the defined rules or constraints; and isolation, which ensures that each transaction is executed independently and does not interfere with other transactions.

Preservation of Consistency in Isolated Transaction Execution

In the context of transaction processing, consistency refers to a state in which a transaction preserves the integrity and validity of data and relationships within a database. In other words, a consistent transaction ensures that the database remains in a valid state before and after the transaction execution.

Isolation, on the other hand, ensures that concurrent executions of multiple transactions by different users do not interfere with each other, causing race conditions, dirty reads, or other inconsistencies.

Out of the ACID properties of transactions, atomicity, consistency, and durability are preserved even in isolated transaction execution. However, isolation is the specific property that ensures that all transactions execute independently and don't compromise consistency.

Therefore, the correct answer to the given question is "Consistency."

Lowest Abstraction Level for Data Storage

In terms of data storage, the lowest level of abstraction is the physical level. This level describes how data is physically stored and organized on the storage medium such as hard disk or memory.

Understanding Relations in RDBMS

In RDBMS, a relation refers to a table that contains columns (also known as keys) and rows (also known as tuples).

Each table is considered to be a relation, and the relations are used to store a collection of related data.

The keys refer to the unique identifier of each row in the table while the data types refer to the categories of data that can be stored in each column.

Having well-defined relations is essential in building a robust and reliable database for efficient data management.

 Example: 

Employee Table:
ID | Name | Job Title | Age
1  | John | Manager   | 30
2  | Jane | Analyst   | 25 

In this example, the Employee table is a relation that contains four columns (ID, Name, Job Title, and Age) and two rows that store data about each employee.

Answer

The given statement is true.

ACID properties in RDBMS (Relational Database Management System) stand for:

• Atomicity: It ensures that transactions are atomic, which means they are either fully completed or not at all. There is no in-between state for transactions.
• Consistency: The database stays in a consistent state before and after a transaction.
• Isolation: Transactions can't interfere with each other, ensuring that each transaction is isolated and independent.
• Durability: Once a transaction is committed, it will remain permanently even if an event such as power failure occurs.

Therefore, regardless of the nature of the data stored in the RDBMS, it will always have ACID properties. Hence, the statement is true.

Automatic Rollback During a Transaction Before Commit in Case of Shutdown

During a transaction before commit, if a shutdown occurs, there are several options for handling the transaction. In this case, the automatic action taken would be to rollback the transaction, ensuring that any changes made during the transaction are not committed.

This is the safest option, as it ensures that data integrity is preserved. If a commit were done automatically in case of a shutdown, data could potentially be lost or corrupted. Therefore, it is important to ensure that all transactions are explicitly committed before a shutdown is initiated.

What does a Relational Database Developer refer to a record as?

A record in a relational database is commonly referred to as a Tuple.

// Example of creating a table to store tuples/records in a relational database using SQL syntax:
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

In the above example, each row in the "users" table will be a tuple/record consisting of an ID, a name, and an email.

Which Predicate is Always Expected to be Satisfied by the Database?

The condition that is always expected to be satisfied by a database is called an assertion. Therefore, the correct answer is "Assertion."

An assertion is a statement that expresses a specific property that must remain true at all times in a database. It is a vital tool for ensuring the consistency and accuracy of data in a database.

By definition, an assertion must always be satisfied, meaning that it is always true. If an assertion is not satisfied, it means that there is a bug or issue in the database.

Therefore, it is crucial to design and implement effective assertions when creating and maintaining a database.

// Example of an assertion in SQL
CREATE ASSERTION assert_credit_limit
CHECK (
    NOT EXISTS (
        SELECT *
        FROM orders o, customers c
        WHERE o.customer_id = c.customer_id AND
        c.credit_limit < o.total_price
    )
);

Optimizing SQL Query

The first query:

SELECT name, course_id  
FROM instructor, teaches  
WHERE instructor_ID = teaches_ID;

can be replaced with the following query:

SELECT name, course_id  
FROM instructor 
JOIN teaches 
ON instructor.instructor_ID = teaches.teaches_ID;

This query uses the JOIN keyword and specifies the joining columns using the ON keyword. This makes the query more readable and less prone to errors.

Option A is the correct answer that uses the recommended syntax of the join. Option B uses the wrong column names to join. Option C only returns data from the instructor table. Option D only selects the course_id.

Information Stored in the Database System Catalog

In a database system, the catalog is used to store important information about the data stored in the database. The information stored in the database system catalog includes:

- Number of tuples - Number of blocks - Size of tuples mentioned - Blocking factor

All of these pieces of data are stored as statistical information in the database system catalog.

Explanation of Variable Write Operations in Database Work Phase

In the process of performing database operations, it is important to understand the different phases involved. The Write Phase, specifically, is responsible for copying the local variables that hold write operations to the database work. This means that any changes made to the database data through write operations are only copied to the database work during this phase.

It is essential to note that the Write Phase occurs before the Validation Phase, during which the updated data is validated and checked for any inconsistencies. Understanding the phases of database operations can help optimize and improve the efficiency of the overall process.

Explanation of SQL Query

This SQL query uses the UPDATE command to modify the "marks" column of the "student" table. The SET command increases the value of "marks" by 20% of its initial value. Therefore, the answer is option C - "Increase marks by 20%."

UPDATE student
SET marks = marks * 1.20;

It's important to note that this query will modify all rows in the "student" table. If you want to update specific rows, you should add a WHERE clause to specify the condition that needs to be met by the rows you want to modify.

Relational Model of RDBMS

The relational model in RDBMS is concerned with Data Structure, Data Manipulation, and Data Integrity. It defines how data is organized into tables (data structure), how it can be accessed, updated, or deleted (data manipulation), and how it is maintained to ensure accuracy and consistency (data integrity).

 // No code provided in the original prompt 

Understanding Primary Key Attribute in Database

In a relational database, a primary key is a column or a combination of columns that uniquely identifies each record in a table. The value of a primary key should never be changed, as it serves as a unique identifier for that record.

Therefore, the answer to the question is "Changed", as the value of a primary key should not be changed in order to maintain the integrity of the database and ensure that each record is uniquely identified.

Note: It is worth noting that there might be some cases where changing the primary key value is necessary, but such changes should be handled with great care and consideration to avoid causing any problems with the data.

Explanation of Referential Integrity Constraints in Relational Databases

In relational databases, referential integrity constraints ensure that the relationships between tables remain consistent. A foreign key is a field in one table that refers to the primary key of another table. When a foreign key constraint is created, it ensures that only values that exist in the referenced table's primary key column can be used in the referring table's foreign key column, thereby maintaining referential integrity.

Therefore, the correct answer to the given question is: Foreign Key.

Minimal Superkeys also referred to as Candidate Keys

When it comes to database design, a superkey is a set of one or more attributes that can be used to uniquely identify a record. A minimal superkey is one where no subset of the key attributes can uniquely identify a record. In other words, it's the smallest set of attributes that can be used to guarantee uniqueness.

When a minimal superkey is selected to serve as a primary key for a table, it is also known as a candidate key. Therefore, the answer to the given question is that Minimal Superkeys are also called candidate keys.

Part of Database Design Related to Logical Design

In database design, the part that is linked to logical design is the database schema. It is the blueprint or the structure of the database that defines how the data is organized and how it relates to each other. It is a visual representation of the database's logical design.

The schema defines tables, attributes, relationships, and constraints that make up the database. It provides a framework for organizing the information in a consistent and efficient way. The schema is a crucial part of the database design process as it ensures that data is organized and stored in a way that maximizes efficiency and accuracy.

Therefore, the correct answer to the given question is Database Schema.

Code:

None required.

Applications of Dynamic Hashing

Dynamic Hashing has two main applications:

1. Accommodating growth and shrinkage of the database.
2. Allowing modification of the hash function.

Therefore, option C, "Both A and B," is the correct answer.

Deleting Entries in an RDBMS

In order to delete entries from a Relational Database Management System (RDBMS), the "DELETE" command is used. This command removes one or more rows from a table within the database. It is commonly used to remove outdated, irrelevant, or inaccurate data from the database tables.

To delete specific entries in a table, you will need to specify a "WHERE" clause in the DELETE statement. The WHERE clause specifies the condition that must be satisfied for the row to be deleted.

Overall, the DELETE command is a fundamental operation in managing the data stored in a relational database.

How to Select the Correct Foreign Key Constraint?

When dealing with database design, it's important to ensure that your data is consistent and accurate. One way to do this is by using referential integrity, which is enforced through the use of foreign key constraints. The correct foreign key constraint to use depends on the specific situation and relationships between tables. The available options are:

• Cascade
• Set Null
• No Action
• Restrict

The "Cascade" option should be used when a related record is deleted or updated, and you want those changes to be reflected in the foreign key table. The "Set Null" option should be used when a related record is deleted or updated, but you want the foreign key value to be set to null. The "No Action" option should be used when a related record is deleted or updated, but you don't want any action taken on the foreign key table. The "Restrict" option should be used when you want to prevent a related record from being deleted or updated if it is used in the foreign key table.

By choosing the appropriate foreign key constraint, you can help ensure that your data remains consistent and accurate, even as you make changes to your database.

So, the correct answer to the given question is "Referential Integrity" as it is also known as Foreign Key Constraint.

Example:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_id INT,
    qty INT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

What is a subquery?

A subquery, also known as an inner query or nested query, is a query placed within the WHERE or HAVING clause of another query. It is used to retrieve data that will be used in the main query. A subquery is enclosed within parentheses and must return a single value, unlike regular queries that can return multiple rows of data.

Code:

SELECT column1, column2, ...
FROM table1
WHERE column3 = (SELECT column4 FROM table2 WHERE column5 = 'value');

In the above example, the subquery is `(SELECT column4 FROM table2 WHERE column5 = 'value')`. It will return a single value which will be used to filter the rows in the main query's WHERE clause.

Subqueries can be used with various operators such as IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS to perform complex queries.

Which operation is followed by Modify operation?

The Modify operation is executed after performing the Lookup operation.

Options:

• Lookup
• Insert
• Delete
• None of the above

Answer: Lookup

Note: It is important to provide clear and concise instructions for users to easily understand the question and available options.

Proper Format for Inserting Date into a Database

The proper format for inserting a date into a database is using the following format:

'YYYY-MM-DD'

For example, if the date is March 15th, 2022, the correct format for inserting the date into the database would be:

'2022-03-15'

It is very important to use this format to ensure proper sorting and querying of dates in the database. Using a different format, such as 'DD-MM-YYYY' could cause errors and inconsistencies in the data.

Understanding Database Views

In database management, there are three types of views which are physical view, conceptual view, and internal view. The total or overall view of a database is referred to as the conceptual view.

The conceptual view is the most abstract view of the database and presents the data in a manner that is easy to understand for users. It hides the complex details of how the data is actually stored in the database and instead allows users to focus on the information that they need.

The physical view, on the other hand, provides a detailed look into how the data is physically stored on the database server. This type of view is typically used for administrative purposes and is not meant for general users.

The third type of view is the internal view, which describes the representation of data inside the database. It is used by database administrators to optimize the performance of the database and is not visible to users.

Therefore, the correct answer to the above question is: Conceptual View.

Choosing Particular Columns with Projection

In SQL, the term "projection" refers to the process of selecting specific columns from a table. If you need to retrieve only certain data from a table, rather than the entire table, you can use projection to do so.

Therefore, the correct answer to the question "To select some particular columns, which of the following columns is used?" is PROJECTION.

What is NTFS?

NTFS stands for New Technology File System. It is a proprietary file system used by Windows operating systems since the release of Windows NT 3.1 in 1993. NTFS offers a number of improvements over the older FAT (File Allocation Table) file system, including better data organization, improved security, and support for larger hard drives and files.

NTFS is the default file system used by modern versions of Windows, including Windows 10, 8, 7, Vista, and XP. It is designed to provide improved performance, reliability, and security over its predecessors.

Properties of Entities

In database design, entities are objects or concepts that are represented in the database and have attributes that describe them. The possible properties of entities are:

• Attributes
• Relationships
• Primary keys
• Foreign keys

Therefore, the correct answer is: Attributes.

Which Constraints RDBMS Checks Before Creating Tables?

In RDBMS, before creating tables, all of the following constraints are checked:

• Not Null
• Primary Key
• Data Integrity

Therefore, the correct answer is "All of the above".

Definition of Atomic Domain

An atomic domain is a set of elements that cannot be further divided or broken down into smaller parts. Therefore, when each element of a domain is indivisible, the domain is called an atomic domain. This property is essential in many fields, including math, computer science, and chemistry.

Database Terminology: Identifying Records with Keys

In a database, there are different ways to identify a specific record or row of data. One such way is by using a key, which is a set of attributes that can uniquely identify the record.

The different types of keys in a database include:

• Super Key
• Candidate Key
• Foreign Key
• Primary Key

In this case, the correct answer is Super Key, which is a set of one or more attributes taken collectively to uniquely identify a record.

What is the Language Used for Requesting Data from a Database?

Query language is used by users to request data from a database. It is not relational or assembly language. Therefore, the correct answer is C - Query.

Code:

//Example query language code
SELECT * FROM customers WHERE customer_id = '123';

In the above code, the query language is used to select all columns from the customers table where the customer ID is '123'. The result of this query would be all the information about the customer with ID '123'.

Explanation

The union operation combines the rows of two tables that have the same structure. It performs the set union of two 'Similarly Structured' tables. Therefore, the correct answer is 'Similarly Structured.'

Which Type of Statement Includes the Select Command?

The select command is a part of Data Manipulation Language (DML) statements.

DML statements are used to manipulate the data in a database. Examples of DML statements other than SELECT are INSERT, UPDATE, and DELETE.

DDL (Data Definition Language) statements modify the structure of the database, such as adding or altering tables and columns. Views are not statements but rather database objects that are used to create virtual tables derived from the result of a SELECT statement.

Therefore, among the given options, the correct answer is DML.

SELECT * FROM table_name;

Updates not allowed in RDBMS

Integrity constraints

and rules are defined in the RDBMS schema to guarantee the accuracy and consistency of data. Any update violating the integrity constraints is not allowed in RDBMS systems. Examples of such updates include deleting a row from a parent table without deleting its corresponding rows from a child table, inserting values that do not match the data type, and exceeding the size limit of a field. Therefore, it is necessary to define and enforce integrity constraints in RDBMS to maintain the data quality and reliability.

Relation Attribute Count

In a relational database, the number of attributes in a relation is known as its degree.

int degree = relation.getAttributes().size();

Where

relation

is an instance of a relation in the database and

getAttributes()

returns a list of attributes in the relation.

Default Join in SQL

In SQL, the default join type is the Inner Join. It returns only the matched rows between the tables being joined. The other types of joins, such as Outer Join and Self Join, are not considered as the default in SQL.

-- Example Inner Join Syntax:
SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

What is the primary key?

According to database design, the primary key is a field or a set of fields used to uniquely identify each record or row in a table. It is important for the primary key to be distinct and not null, which means it must have a value for each record.

Therefore, the correct answer to the question "What must the primary key be?" is both A and B, which means that it must be unique and not null.

  CREATE TABLE Customers (
    CustomerID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Email varchar(255),
    PRIMARY KEY (CustomerID)
  );

In the above example, "CustomerID" field is assigned as a primary key, and since it is NOT NULL and UNIQUE, it meets the requirements to be a primary key.

Examples of Aggregate Functions in SQL

In SQL, SUM, MAX, and MIN are all examples of aggregate functions.

Aggregate functions in SQL are used to perform calculations on groups of rows and return a single value. They can be applied to numerical or textual data, and are commonly used in conjunction with the GROUP BY clause to group data based on one or more columns.

Therefore, the correct answer is: All of the above.

SELECT SUM(salary) FROM employees;
SELECT MAX(age) FROM customers;
SELECT MIN(price) FROM products;

Types of SQL Update Statements

There are several types of SQL statements that can be used to update data in a database. One type of SQL update statement is the Scalar Subquery. This statement can only be used in the Set clause of an update statement.

Scalar Subqueries are used to return one value from a subquery result set, and then assign that value to a column in the update statement. They are useful when updating a table based on values from another table.

Other types of SQL update statements include Conjoined Query and Multiple Subqueries.

Explaining the On Condition for Joining Relations in SQL

In SQL, the On condition is used to join two or more tables by specifying a condition that relates columns in those tables. This condition allows a general predicate over the relations being joined and helps to filter the rows that are returned in the final result set.

Other conditions that are often used in the context of joining tables include the Where and Having conditions, however, they are different from the On condition in terms of their functionality and scope.

To summarize, the On condition is a critical component of joining tables in SQL and allows the user to specify how the data in different tables are related to each other, which can provide powerful insights into the data for various applications.

What is the maximum number of tables that can be joined using a join?

In SQL, we can join multiple tables together using various join types such as inner join, outer join, left join, and right join. There isn't any fixed limit to the number of tables that can be joined in a single query. However, the more tables you join, the more complex your query will become and it may have an impact on the query's performance. Therefore, it's recommended to join only the required tables and to optimize the query to improve its performance.

How to Commit a Transaction to Make it Permanent in the Database?

To make a transaction permanent in a database, we need to commit it. Committig a transaction means that the changes made during the transaction are saved permanently in the database, without the ability to undo them.

Code example:

BEGIN TRANSACTION

-- SQL statements executed during the transaction go here

COMMIT TRANSACTION

After executing the SQL statements within a transaction, we use the COMMIT statement to make the changes permanent. If any error occurs while making changes to the database, we use the ROLLBACK statement to undo them.

Therefore, the correct answer is:

View: None of the above.

Maximum Number of Children in a B-Tree

The maximum number of children for a B-tree of order N can be calculated as N. Therefore, the answer to the given question is N.

Query Ranking in SQL

In SQL, the ranking of queries is determined by the use of the

ORDER BY

clause. The

ORDER BY

clause is used to sort the result set in ascending or descending order based on one or more columns. It can be used with various data types such as numbers, strings, and dates.

The

GROUP BY

clause is used to group data based on one or more columns and perform aggregate functions on the data such as summing or averaging. The

WHERE

clause is used to filter data based on certain conditions, while the

HAVING

clause is used to filter the results of aggregate functions.

However, when it comes to the ranking of queries, the

ORDER BY

clause takes precedence over the other clauses. It is executed after all the other clauses have been executed, and it is used to produce the final result set in the desired order.

Therefore, if you want to rank queries in SQL, you should use the

ORDER BY

clause.

Example:

SELECT * FROM students
ORDER BY grade DESC;

In the above example, the

ORDER BY

clause is used to sort the students' table based on the students' grades in descending order.

Explanation:

The AS clause is used for renaming a table or a column in SQL. It allows us to assign a new name to a table or a column for the duration of a query. We can use the AS clause with SELECT statements for column aliases or with FROM statements for table aliases.

For example:

SELECT employee_id AS id, first_name AS name, last_name AS surname FROM employees;

In the above example, the AS clause is used to give aliases to column names for readability purposes.

What Does OLAP Stand For?

OLAP stands for Online Analytical Processing.

In OLAP, data is analyzed and processed to generate information for decision-making. It involves creating multidimensional data models that allow for complex queries and efficient data retrieval.

Types of Data that can be Modeled as Dimensional and Measure Attributes

In the context of data modeling, the type of data that can be modeled as dimensional and measure attributes is known as multidimensional data. This means that the data is organized into multiple dimensions, with each dimension representing a different attribute or characteristic of the data.

For example, suppose you have data on sales performance in a company. You could organize this data into dimensions such as region, time, product, and salesperson. Each of these dimensions would have corresponding measure attributes, such as sales revenue, number of units sold, and profit margins.

This type of multidimensional data is commonly used in data warehousing and business intelligence applications, where it can be analyzed using OLAP (Online Analytical Processing) tools to gain insights and inform decision-making.

Creating Cross Tabs in SQL

In SQL, cross tabs can be created using the "Pivot" function. This function allows you to transform rows into columns, resulting in a summary table that provides a multidimensional view of the data. The "Slice" and "Dice" terms are not used in SQL to create cross tabs. Therefore, the correct answer is:

Pivot

What is an Entity Set?

In database management systems, a group of entities of the same type that shares the same properties or attributes is known as an entity set. It represents a collection of similar type of entities.

• Entity Set
• Attribute Set
• Relation Set
• Entity Model

The correct answer is: Entity Set.