RDBMS Design Interview Questions

Checkout Vskills Interview questions with answers in RDBMS Design to prepare for your next job role. The questions are submitted by professionals to help you to prepare for the Interview.

Q.1 What is RDBMS design?
Relational database design (RDD) models information and data into a set of tables with rows and columns. Each row of a relation/table represents a record, and each column represents an attribute of data. The Structured Query Language (SQL) is used to manipulate relational databases.
Q.2 What is RDBMS architecture?
A relational database management system (RDBMS) is a database management system (DBMS) that is based on a relational model in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.
Q.3 What are the steps to design a relational database?
The steps to design a relational database are: define your purpose and objectives, analyze data requirements, create a list of entities and a list of attributes, model the tables and fields, establish table relationships, establish business rules and implement in the database.
Q.4 What is in database design?
Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model. Database management system manages the data accordingly.
Q.5 What is good database design?
A good database design: divides information into subject-based tables to reduce redundant data, provides access with the information it requires to join the information in the tables together as needed and helps support and ensure the accuracy and integrity of information.
Q.6 What are the types of database models?
The types of database models are: Relational model, Network model and Object-oriented database model.
Q.7 What are the steps of database design?
The steps of database design are the three main phases ,which are: conceptual, logical, and physical design.
Q.8 What do you understand by PK in database?
A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table.
Q.9 Can primary key be null?
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.
Q.10 What do you understand by Normalization in database design?
Normalization is the process of reorganizing data in a database so that it meets two basic requirements: there is no redundancy of data and all data is stored in only one place. Data dependencies are logical, all related data items are stored together.
Q.11 Differentiate between PK and FK in database design
A primary key constrain is a column that uniquely identifies every row in the table of the relational database management system, while foreign key is a column that creates a relationship between two tables. Primary Key never accepts null values whereas foreign key may accept multiple null values.
Q.12 What are the levels of data abstraction in database design?
There are mainly three levels of data abstraction: Internal Level: the actual physical storage structure and access paths, Conceptual or Logical level: structure and constraints for the entire database and External or View level: Describes various user views.
Q.13 Differentiate between primary key and unique key
A primary key is a column of table which uniquely identifies each tuple (row) in that table. Unique key constraints also identifies an individual tuple uniquely in a relation or table. A table can have more than one unique key unlike primary key. Unique key constraints can accept only one NULL value for column.
Q.14 What are SQL indexes?
An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables database Server to find the row or rows associated with the key values quickly and efficiently.
Q.15 Can a table have both primary key and composite key?
A Table can have a Composite Primary Key which is a primary key made from two or more columns.
Q.16 Can two tables have same primary key?
Yes, we can have same column name as primary key in multiple tables. Column names should be unique within a table. A table can have only one primary key, as it defines the Entity integrity.
Q.17 Can foreign key be null?
Yes, it can be NULL or duplicate. A foreign key simply requires that the value in that field must exist first in a different table (the parent table).
Q.18 How is data organized in a RDBMS?
In tables or relations, a relational database organizes data. A table is composed of columns and rows. A record or tuple is also known as row. DBMS stores data as a file whereas in RDBMS, data is stored in the form of tables.
Q.19 What is better clustered or nonclustered index?
If you want to select only the index value that is used to create and index, non-clustered indexes are faster. For example, if you have created an index on the “name” column and you want to select only the name, non-clustered indexes will quickly return the name.
Q.20 What are SQL views?
A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Q.21 Can table have multiple foreign keys?
A table may have multiple foreign keys, and each foreign key can have a different parent table. Each foreign key is enforced independently by the database system.
Q.22 Can we drop a table or a column from a table which has a primary key?
We can remove PRIMARY KEY constraint from a column of an existing table by using DROP keyword along with ALTER TABLE statement.
Q.23 Can two tables have same column name?
Yes, two tables can have exactly the same column names but if they are not connected in some other way then they have nothing to do with each other.
Q.24 Can a table have no primary key?
Every table can have (but does not have to have) a primary key. The column or columns defined as the primary key ensure uniqueness in the table; no two rows can have the same key. The primary key of one table may also help to identify records in other tables, and be part of the second table's primary key.
Q.25 What happens when you delete a foreign key?
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. A foreign key with cascade delete can be created using either a CREATE TABLE statement or an ALTER TABLE statement.
Q.26 Do we always need a clustered index?
As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases–such as an identity column, or some other column where the value is increasing–and is unique. With few exceptions, every table should have a clustered index.
Q.27 What is difference between view and table?
The main difference between view and table is that view is a virtual table based on the result set of an SQL statement, while a table is a database object that consists of rows and columns that store data of a database. In other words, there should be one or multiple tables to create views.
Q.28 Can primary key be multiple columns?
Well, a primary key that is made up of 2 or more columns is called a composite primary key. A common real world use case for this is, when you have a many-to-many relationship between two tables i.e. when multiple rows in one table are associated with multiple rows in another table.
Q.29 Can we delete parent table without deleting child table?
You can't drop a parent table if you have a child table with a foreign key constraint in place, unless you specify the CASCADE CONSTRAINTS clause: DROP TABLE P CASCADE CONSTRAINTS; this command drops the FK constraint too. Deleting a table will necessarily drop all constraints related to this table.
Q.30 What is truncate in database?
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
Q.31 What is an RDBMS, and how does it differ from a DBMS?
An RDBMS (Relational Database Management System) is a type of DBMS that organizes data into structured tables with rows and columns. RDBMS enforces relationships between tables using keys.
Q.32 What is a database schema in RDBMS design?
A database schema is a logical blueprint that defines the structure and organization of tables, columns, relationships, constraints, and indexes within a database.
Q.33 Explain the concepts of tables, rows, and columns in RDBMS.
Tables are entities that store data, rows represent individual records, and columns define the attributes or properties of each record.
Q.34 What is a primary key in RDBMS, and why is it important?
A primary key is a unique identifier for each record in a table. It enforces data integrity and ensures that each row can be uniquely identified.
Q.35 Define a foreign key and its role in RDBMS design.
A foreign key is a field in one table that establishes a link to the primary key in another table. It enforces referential integrity and maintains relationships between tables.
Q.36 What is normalization, and why is it important in RDBMS?
Normalization is the process of structuring tables to reduce data redundancy and improve data integrity. It minimizes the risk of anomalies in the database.
Q.37 Explain the different normal forms in RDBMS design.
Normal forms (e.g., 1NF, 2NF, 3NF, BCNF) are rules for reducing data redundancy and eliminating anomalies by organizing data in a structured way. Each normal form has specific criteria.
Q.38 What is denormalization, and when is it used in RDBMS?
Denormalization is the process of intentionally introducing redundancy into a database design to improve query performance. It is typically used in read-heavy scenarios.
Q.39 What is an index in RDBMS, and why is it important?
An index is a data structure that enhances data retrieval speed by providing fast access to rows in a table. It's crucial for improving query performance.
Q.40 Explain the differences between clustered and non-clustered indexes.
A clustered index determines the physical order of data rows in a table and can exist only on one table per database. Non-clustered indexes do not affect the physical order and can exist on multiple tables.
Q.41 What is a composite key, and when is it used in RDBMS design?
A composite key consists of two or more columns used together as a unique identifier. It's used when no single column can uniquely identify rows in a table.
Q.42 What is a stored procedure in RDBMS, and why is it useful?
A stored procedure is a precompiled set of SQL statements stored in the database. It improves performance, reusability, and security of database operations.
Q.43 Explain the concept of data integrity constraints in RDBMS.
Data integrity constraints are rules that ensure data accuracy and consistency, including primary key, foreign key, unique, check, and default constraints.
Q.44 What is referential integrity, and why is it important?
Referential integrity ensures that relationships between tables are maintained, and foreign key values correspond to valid primary key values in related tables. It prevents orphaned records.
Q.45 What are the ACID properties in the context of RDBMS transactions?
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are reliable, maintain data integrity, and recover from failures.
Q.46 What is a view in RDBMS, and how does it differ from a table?
A view is a virtual table created by defining a query on one or more base tables. Unlike a table, a view does not store data but presents a filtered or computed result set.
Q.47 Explain the differences between an inner join and an outer join.
Inner join retrieves only matching rows from both tables, while outer join (left, right, full) retrieves matching rows along with non-matching rows from one or both tables.
Q.48 What is a self-join, and when is it used in RDBMS?
A self-join is a join operation where a table is joined with itself. It's used when the data in a single table has relationships that need to be represented.
Q.49 What is a transaction in RDBMS, and how is it defined?
A transaction is a sequence of one or more SQL statements treated as a single unit of work. It is defined using the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.
Q.50 What is a database index, and how does it improve query performance?
A database index is a data structure that provides a quick lookup mechanism for rows in a table. It improves query performance by reducing the number of rows that need to be scanned.
Q.51 Explain the concept of data redundancy in RDBMS design.
Data redundancy occurs when the same data is stored in multiple places in the database. It can lead to inconsistencies and increased storage requirements.
Q.52 What is a surrogate key, and when is it used in RDBMS design?
A surrogate key is an artificially generated key, typically an auto-incremented integer, used as the primary key. It's used when there is no natural key or to improve data integrity.
Q.53 Describe the differences between a unique key and a primary key.
A unique key enforces uniqueness but allows for one null value, while a primary key enforces uniqueness and does not allow null values.
Q.54 What is the purpose of the SQL JOIN operation in RDBMS?
The JOIN operation combines rows from two or more tables based on a related column between them. It's used to retrieve data from multiple tables in a single query.
Q.55 Explain the concepts of "one-to-one," "one-to-many," and "many-to-many" relationships in RDBMS design.
These describe the cardinality of relationships between tables: one-to-one means each row in one table corresponds to one row in another, one-to-many means one row corresponds to multiple rows, and many-to-many means multiple rows correspond to multiple rows.
Q.56 What is an RDBMS constraint, and why are constraints important?
An RDBMS constraint is a rule applied to a column or a set of columns to maintain data integrity. Constraints ensure that data follows predefined rules and meets business requirements.
Q.57 Explain the differences between a heap table and a clustered table in RDBMS.
A heap table has no defined order for its data, while a clustered table has its data physically ordered based on the clustered index.
Q.58 What is a database trigger in RDBMS, and when is it used?
A database trigger is a set of actions automatically executed in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers are used to enforce business rules and maintain data consistency.
Q.59 What is the purpose of a data dictionary in RDBMS design?
A data dictionary is a repository that stores metadata about the database, including information about tables, columns, constraints, and relationships. It serves as a reference for developers and administrators.
Q.60 Explain the concept of "acid tests" in RDBMS transactions.
Acid tests are a set of criteria that determine whether a database management system ensures reliable transaction processing, including Atomicity, Consistency, Isolation, and Durability.
Q.61 What is a subquery in RDBMS, and when is it used?
A subquery is a query nested within another query. It is used to retrieve data based on the result of another query and is commonly used in WHERE, FROM, or HAVING clauses.
Q.62 Describe the differences between a candidate key and a super key.
A candidate key is a minimal set of attributes that uniquely identify a row, while a super key is any set of attributes that can uniquely identify a row, including candidate keys and other attributes.
Q.63 What is the purpose of indexing columns in RDBMS tables?
Indexing columns improves the efficiency of data retrieval by providing a quick lookup mechanism. It reduces the need for full table scans when querying data.
Q.64 Explain the concept of "concurrency control" in RDBMS.
Concurrency control ensures that multiple transactions can run concurrently without interfering with each other. It manages access to shared resources to prevent conflicts.
Q.65 What is a database transaction log, and why is it important?
A transaction log records all changes made to the database, enabling data recovery, rollbacks, and maintaining data consistency in the event of a system failure or error.
Q.66 Describe the differences between the DELETE and TRUNCATE statements in SQL.
DELETE removes specific rows from a table and logs each deletion, while TRUNCATE removes all rows from a table without logging individual deletions.
Q.67 What is the purpose of database normalization in RDBMS?
Database normalization is the process of organizing data to reduce data redundancy and improve data integrity. It ensures that data anomalies are minimized.
Q.68 Explain the concept of "data integrity" in RDBMS design.
Data integrity refers to the accuracy and consistency of data in a database. It ensures that data remains reliable and follows predefined rules.
Q.69 What is a database view, and why is it used in RDBMS?
A database view is a virtual table based on the result of a SQL query. It simplifies data access, provides a security layer, and hides complex queries from users.
Q.70 Describe the differences between a left outer join and a right outer join.
A left outer join retrieves all rows from the left table and the matching rows from the right table. A right outer join retrieves all rows from the right table and the matching rows from the left table.
Q.71 What is a surrogate primary key, and why might it be preferred over a natural key?
A surrogate primary key is an artificial key used as the primary key, often an auto-incremented integer. It is preferred when natural keys are not suitable, and data integrity needs to be enforced.
Q.72 Explain the concept of "database indexing" in RDBMS design.
Database indexing is the creation of data structures (indexes) to improve the speed of data retrieval operations on specific columns, reducing the need for full table scans.
Q.73 What are the advantages of using stored procedures in RDBMS?
Advantages of stored procedures include improved performance, code reusability, security, reduced network traffic, and the ability to encapsulate business logic.
Q.74 What is a database constraint, and what types of constraints are commonly used in RDBMS?
A database constraint is a rule applied to a column or a set of columns to maintain data integrity. Common constraint types include primary key, foreign key, unique, check, and default constraints.
Q.75 Explain the concept of "transaction isolation levels" in RDBMS.
Transaction isolation levels define the degree of isolation between concurrent transactions. Common levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Q.76 What is a materialized view, and how does it differ from a regular view in RDBMS?
A materialized view is a database object that stores the result of a query as physical data. Unlike regular views, materialized views precompute and store data, which can improve query performance.
Q.77 What is a database schema, and why is it used in RDBMS design?
A database schema is a logical container for organizing database objects, such as tables, views, and procedures. It helps manage object ownership and provides a namespace for objects.
Q.78 Explain the concept of "locking" in RDBMS transactions.
Locking is a mechanism that prevents multiple transactions from accessing or modifying the same data simultaneously to maintain data integrity.
Q.79 What is the purpose of database constraints, and what are some common types?
Database constraints ensure data integrity by enforcing rules on table columns. Common types include primary key, foreign key, unique, check, and default constraints.
Q.80 Describe the differences between a heap file and a clustered file in RDBMS.
A heap file stores data without any particular order, while a clustered file stores data in a specific order based on the clustered index.
Q.81 What is the purpose of an RDBMS "transaction"?
A transaction in RDBMS ensures that a series of database operations are treated as a single unit of work. It guarantees data consistency and integrity, with the ability to roll back changes in case of an error.
Q.82 Explain the concept of "database normalization" in RDBMS design.
Database normalization is a process that reduces data redundancy and ensures data integrity by organizing data into separate related tables and defining relationships between them.
Q.83 What is a "checkpoint" in RDBMS, and why is it important?
A checkpoint is a mechanism that writes all committed transactions from the transaction log to the data files. It ensures data durability and reduces recovery time in case of a system crash.
Q.84 Describe the differences between the MERGE and UPDATE statements in SQL.
The MERGE statement performs INSERT, UPDATE, or DELETE operations based on a condition, while the UPDATE statement modifies existing rows in a table based on a condition.
Q.85 What is an "entity-relationship diagram" (ERD), and how is it used in RDBMS design?
An ERD is a visual representation of the relationships between entities (tables) in a database, showing how data is structured and related. It aids in database design and understanding.
Q.86 What is "database normalization," and why is it important in RDBMS design?
Database normalization is the process of organizing data into separate tables to minimize data redundancy and improve data integrity. It reduces anomalies and ensures efficient data storage.
Q.87 Explain the differences between an "inner join" and a "left join" in SQL.
An inner join returns only the rows that have matching values in both tables, while a left join returns all rows from the left table and the matching rows from the right table.
Q.88 What is the purpose of the SQL GROUP BY clause in RDBMS queries?
The GROUP BY clause is used to group rows that have the same values in specified columns and perform aggregate functions (e.g., SUM, AVG) on the grouped data.
Q.89 How do you handle many-to-many relationships in RDBMS design?
Many-to-many relationships are typically resolved using an intermediary table (junction table) that contains foreign keys from both related tables.
Q.90 What is the role of the SQL HAVING clause in RDBMS queries?
The HAVING clause is used to filter the results of a GROUP BY query based on aggregate functions. It operates on grouped data after the GROUP BY operation.
Q.91 Explain the concept of "database triggers" in RDBMS design.
Database triggers are sets of instructions that automatically execute in response to specific events or conditions, such as data changes. They are used to enforce business rules or maintain data integrity.
Q.92 What is a "relational schema," and how does it relate to RDBMS design?
A relational schema is a blueprint that defines the structure of tables, columns, constraints, and relationships in a relational database. It forms the basis for database design.
Q.93 What is a "composite key," and when is it used in RDBMS design?
A composite key is a key that consists of multiple columns, used together as a unique identifier for rows in a table. It is used when a single column cannot uniquely identify rows.
Q.94 Describe the differences between a "candidate key" and a "super key" in RDBMS.
A candidate key is a set of attributes that uniquely identifies rows, while a super key is a set of attributes that includes the candidate key and possibly additional attributes.
Q.95 What is the purpose of the SQL UNION operator in RDBMS queries?
The UNION operator combines the result sets of two or more SELECT statements into a single result set, removing duplicate rows by default.
Q.96 Explain the concept of "database transactions" in RDBMS.
A database transaction is a sequence of one or more SQL statements treated as a single unit of work. Transactions ensure data consistency and integrity, with options to commit or roll back changes.
Q.97 What is "data redundancy," and why should it be minimized in RDBMS design?
Data redundancy occurs when the same data is stored in multiple places, leading to potential inconsistencies and increased storage requirements. Minimizing redundancy improves data integrity.
Q.98 What is the role of the SQL ORDER BY clause in RDBMS queries?
The ORDER BY clause is used to sort the result set of a query based on specified columns in ascending or descending order. It affects the presentation of query results.
Q.99 What is a "transaction log" in RDBMS, and why is it important?
A transaction log records all changes made to the database, serving as a history of transactions. It is crucial for data recovery, rollback, and maintaining data consistency.
Q.100 Explain the differences between "DELETE" and "TRUNCATE" operations in SQL.
DELETE removes specific rows from a table, logging each deletion. TRUNCATE removes all rows from a table without logging individual deletions and is typically faster.
Q.101 What is the purpose of "database triggers" in RDBMS?
Database triggers are sets of actions that automatically execute in response to specific events or conditions in the database. They are used to enforce business rules and maintain data integrity.
Q.102 What are "acid tests" in RDBMS transactions, and why are they important?
Acid tests are criteria that ensure the reliability and integrity of database transactions. They stand for Atomicity, Consistency, Isolation, and Durability, which are vital properties of a transaction.
Q.103 What is a "database constraint," and what types of constraints are commonly used in RDBMS?
A database constraint is a rule applied to a column or set of columns to maintain data integrity. Common constraint types include primary key, foreign key, unique, check, and default constraints.
Q.104 What is a "database schema," and why is it used in RDBMS design?
A database schema is a logical container that defines the organization and structure of database objects, such as tables, views, and procedures. It helps manage object ownership and access.
Q.105 What is a "surrogate primary key," and why might it be preferred over a natural key?
A surrogate primary key is an artificial key used as the primary key in a database, often an auto-incremented integer. It is preferred when natural keys are unavailable or unsuitable.
Q.106 Describe the differences between a "heap file" and a "clustered file" in RDBMS.
A heap file stores data without any particular order, while a clustered file stores data in a specific order based on the clustered index, which affects physical storage.
Q.107 What is the role of the SQL GROUP BY clause in RDBMS queries?
The GROUP BY clause groups rows with the same values in specified columns and allows aggregate functions (e.g., SUM, AVG) to operate on grouped data, summarizing results.
Q.108 How do you handle "many-to-many" relationships in RDBMS design?
Many-to-many relationships are typically resolved using an intermediary table (junction table) that contains foreign keys from both related tables. It represents associations between records.
Get Govt. Certified Take Test