Comprehensive Viva Questions and Answers on Database Design
Unit 1: Database Environment
Q: What is the role of a Database Management System (DBMS)?
A: A DBMS provides a systematic way to store, retrieve, and manage data efficiently.
Q: List some disadvantages of traditional file processing systems.
A: Disadvantages include data redundancy, inconsistency, lack of security, and poor scalability.
Q: What are the main components of a database system?
A: Components include data, software, hardware, users, and procedures.
Q: What is the purpose of the three-schema architecture?
A: It separates the database's physical storage, logical design, and user views to ensure abstraction
and data independence.
Unit 2: Enhanced E-R Model
Q: What is specialization in the Enhanced E-R Model?
A: Specialization defines subclasses from a superclass based on specific attributes.
Q: What is a subtype discriminator?
A: It is an attribute in a supertype that indicates which subtype an entity instance belongs to.
Q: Explain the concept of generalization.
A: Generalization combines similar subclasses into a single superclass by abstracting common
features.
Q: What is BCNF in normalization?
A: Boyce-Codd Normal Form (BCNF) eliminates redundancy by ensuring every determinant is a
candidate key.
Unit 3: Relational Algebra and Calculus SQL
Q: What is the importance of relational algebra in database design?
A: Relational algebra provides a foundation for query operations and ensures consistent and
systematic data retrieval.
Q: Explain the difference between UNION and INTERSECTION in relational algebra.
A: UNION combines all tuples from two relations, eliminating duplicates, while INTERSECTION
returns only the common tuples.
Q: What is the significance of Cartesian Product in relational algebra?
A: The Cartesian Product combines tuples from two relations in every possible way, useful for
creating intermediate results.
Q: Define Division operation in relational algebra.
A: Division finds tuples in one relation that are associated with all tuples in another relation.
Q: What is domain relational calculus?
A: A declarative query language that uses domain variables to specify the data to retrieve, focusing
on what to fetch.
Q: Differentiate between tuple relational calculus and domain relational calculus.
A: Tuple calculus deals with tuples as variables, while domain calculus uses domain values as
variables.
Q: What are subqueries in SQL?
A: Subqueries are nested queries used within another SQL query to refine results.
Q: How do JOIN operations work in SQL?
A: JOIN operations combine rows from two or more tables based on a related column, such as
INNER JOIN or OUTER JOIN.
Q: Explain the use of GROUP BY in SQL.
A: GROUP BY groups rows that have the same values in specified columns and is often used with
aggregate functions.
Q: What is the difference between HAVING and WHERE clauses in SQL?
A: WHERE filters rows before grouping, while HAVING filters groups after aggregation.
Q: How do triggers differ from stored procedures?
A: Triggers execute automatically in response to database events, while stored procedures are
explicitly called.
Q: What is a correlated subquery?
A: A subquery that uses values from the outer query and is evaluated once for each row processed
by the outer query.
Q: Explain the concept of views in SQL.
A: Views are virtual tables created by querying data from one or more tables, offering simplified and
secure data access.
Q: What is a recursive query in SQL?
A: A query that references itself, typically using a Common Table Expression (CTE) for hierarchical
or iterative data retrieval.
Q: What is the difference between DDL and DML in SQL?
A: DDL (Data Definition Language) defines the schema (e.g., CREATE, ALTER), while DML (Data
Manipulation Language) manages data (e.g., INSERT, UPDATE).
Unit 4: Overview of Storage and Indexing
Q: What is file organization in a database?
A: It refers to the way data is stored in files to optimize access and performance.
Q: Explain the difference between B-tree and B+ tree indexing.
A: B-trees store keys and data in internal and leaf nodes, whereas B+ trees store data only in leaf
nodes, making searches faster.
Q: What is static hashing?
A: A hashing method where the hash table size is fixed, potentially leading to overflow issues.
Q: What are the advantages of dynamic hashing?
A: It adjusts the hash table size dynamically, reducing collisions and improving space utilization.
Unit 5: Transaction Management
Q: What are the ACID properties of a transaction?
A: ACID properties include Atomicity, Consistency, Isolation, and Durability, ensuring reliable
transaction processing.
Q: What is two-phase locking (2PL)?
A: 2PL is a concurrency control method with a growing phase (acquiring locks) and a shrinking
phase (releasing locks).
Q: How is deadlock detected in transaction management?
A: Deadlocks are detected using wait-for graphs or algorithms to identify cycles in lock
dependencies.
Q: What is serializability in concurrency control?
A: It ensures the result of executing transactions concurrently is equivalent to executing them
serially.
