Top 50 DBMS Viva Questions and Answers

Database Management System(DBMS ) VIVA

The DBMS viva or interview will generally examine your knowledge of database fundamentals, SQL, normalization, transactions, and database design. Expect questions about core subjects such as database types, ER diagrams, ACID attributes, and SQL command differences (e.g., DDL vs. DML). You may also be asked to describe terms such as primary keys, foreign keys, indexing, joins, and normalization forms. Prepare to discuss real-world DBMS applications, solve SQL queries, and explain their reasoning.

So, In this DBMS Tutorial, We will learn how to give DBMS Viva and ace the exam, So keep reading..! keep growing..!

1. What is a database?

A database is a structured collection of data that represents a real-world element and is created for a specific purpose.

2. What is DBMS?

It is a set of programs that allow users to construct and manage databases. General-purpose software allows users to define, construct, and manipulate databases for numerous applications.

3. What is a Database system?

The database and DBMS software together is called a Database system.

4. What are the advantages of DBMS?

• Redundancy is controlled.
• Unauthorized access is restricted.
• Providing multiple user interfaces.
• Enforcing integrity constraints.
• Providing backup and recovery.

5. What are the disadvantages of a File Processing System?

1. Data redundancy and inconsistency.
2. Difficult in accessing data.
3. Data isolation.
4. Data integrity.
5. Concurrent access is not possible.
6. Security Problems.

6. Describe the three levels of data abstraction.

The are three levels of abstraction:

• 1. Physical level: The lowest level of abstraction describes how data are stored.
• 2. Logical level: The next higher level of abstraction, describes what data are storedin the database and what relationship is among those data.
• 3. View level: The highest level of abstraction describes only part of the entire database.

7. Define the "integrity rules"?

There are two Integrity Rules.

• 1. Entity Integrity: "Primary key cannot have NULL value"
• 2. Referential Integrity: A foreign key can be either NULL or the primary key of another relation.

8. What is Data Independence?

Data independence refers to an application's ability to function independently of data storage and access strategies. Modifying the schema definition at one level should not affect the next higher level.

There are two forms of data independence:

• 1. Physical Data Independence: Modifications at the physical level should not influence logical levels.
• 2. Logical Data Independence: Any changes made at the logical level should be reflected at the view level.

9. What is a view? How it is related to data independence?

A view is a virtual table that is derived from one or more base tables, rather than existing independently. The view definition is contained in the data dictionary rather than in a separate file. Views do not reflect the growth or restructure of basic tables. The view protects users from the effects of database restructure and growth. This accounts for logical data independence.

10. What is a Data Model?

A collection of conceptual tools for describing data, data relationships data semantics, and constraints.

11. What is the E-R model?

The data model is based on the real world, consisting of basic things and their relationships. In a database, entities are defined by a set of attributes.

12. What is Object Oriented model?

This concept is based on the collection of items. Instance variables store values within an object. An object also contains code that performs operations on it. These code bodies are referred to as methods. Classes are created by grouping objects with similar properties and actions.

13. What is an Entity?

It is a 'thing' in the real world with an independent existence.

14. What is an Entity type?

It is a collection (set) of entities that have the same attributes.

15. What is an Entity Set?

It is a collection of all entities of a specific entity type in the database.

16. What is an Extension of Entity Type?

An entity set is made up of collections of entities of the same kind.

17. What is a Weak Entity Set?

A weak entity set is one with insufficient properties to generate a primary key, which compromises its partial key and the primary key of its parent entity.

18. What constitutes an attribute?

It is a specific property that defines the entity.

19. What is the difference between a relation schema and a relation?

• A relation schema, indicated by R(A1, A2,..., An), consists of the relation name R and a list of attributes (Ai).
• A relation is defined as a collection of tuples
• . Let r be the relation that includes set tuples (t1, t2, t3,..., tn). Each tuple (v1, v2,..., vn) is an ordered list of n-values.

20. What is the Degree of a Relationship?

It represents the number of attributes in the relation schema.

21. What is Relationship?

It is a relationship between two or more entities.

22. What is a Relationship Set?

A collection (or set) of similar relationships.

23. What is the relationship type?

The term "relationship type" refers to a set of associations or relationships between entity kinds.

24. What is the Degree of Relationship Type?

It is the number of entity types that participate.

25. What is Data Definition Language (DDL)?

A database schema is specified by a set of definitions stated in a particular language known as DDL.

26. What is Data Manipulation Language (DML)?

This language allows users to access or manipulate data arranged by an acceptable data model.

• 1. Procedural DML, also known as low-level DML, requires users to declare data requirements and methods for retrieval.
• 2. Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.

27. What is the Query Evaluation Engine?

It executes low-level instructions produced by the compiler.

28. What is normalization?

The approach involves analyzing relation schemas based on Functional Dependencies (FDs) and main keys to obtain desired attributes (1).(2) Reduce redundancy. Keeping insertion, deletion, and update anomalies to a minimum.

29. What is Functional Dependence?

A functional dependency (X Y) between two subsets of R specifies a constraint on the possible tuple for a relation state (r) of R. The constraint states that for every two tuples t1 and t2 in r, if t1[X] = t2[X], t1[Y] = t2[Y]. This means that the value of X in a tuple dictates the value of Y.

30. What is the Lossless Join Property?

It prevents spurious tuple formation in relation to schemas following decomposition.

31. What is one NF (Normal Form)?

The domain of the property shall only contain atomic (simple, indivisible) values.

32. What is a Fully Functional Dependency?

It is founded on the idea of full functional reliance. A functional dependency X Y is full if removing any property A from X causes the dependency to no longer hold.

33. What is 2NF?

A relational schema. R is in 2NF if it is in 1NF and all non-prime attributes in R are totally dependent on the primary key.

34. What is 3NF?

A relational schema. R is in 3NF if it is in 2NF and for every FD X A, one of the following is true.

• 1. X is a super-key for R.
• 2. A is R's prime property.

In other words, if all non-prime attributes are not transitively dependent on the primary key

35. What is the BCNF (Boyce-Codd Normal Form)?

A relation schema R is in BCNF if it is in 3NF and satisfies an extra constraint: for every FD A, X must be a candidate key.

36. What exactly is meant by query optimization?

The phase that determines an effective execution strategy for evaluating a query using the least predicted cost is referred to as query optimization.

37. What is the definition of durability in database management systems?

When the DBMS informs the user that a transaction has successfully completed, the effects should persist even if the system fails before all of its changes are written to disk. The property is referred to as durability.

38. What exactly do you mean by atomicity and aggregation?

• 1. Atomicity: Either all actions are completed or none are. Users should not worry about the impact of unfinished transactions. The DBMS achieves this by undoing unfinished transactions.
• 2. Aggregation: A factor that represents the interaction between entities and relationships. It is utilized when we want to express a relationship between partnerships.

39. What is a query?

• A query in DBMS refers to user commands used to communicate with a database.
• There are two types of query languages: data specification and data manipulation.

40. What do you mean by Correlated subquery?

• Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query.
• Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
• A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in
• the parent query. The following example demonstrates a non-correlated subquery.

Example

SELECT * FROM CUST WHERE '10/03/1990' IN (SELECT ODATE FROM ORDER WHERE
CUST.CNUM = ORDER.CNUM)

41. What are the primitive operations common to all record management systems?

Addition, deletion, and modification.

42. What are the unary operations in Relational Algebra?

PROJECTION and SELECTION.

43. Are the resultant relations from the PRODUCT and JOIN operations the same?

•  PRODUCT: Concatenation of all rows from one relation into another.
• JOIN Concatenation of rows from one relation and related rows from another.

44. Which part of the RDBMS manages the data dictionary? How?

The kernel is solely responsible for maintaining the data dictionary, which is a collection of tables and database objects stored in a certain database region.

45. What purpose does the information in the data dictionary serve?

The data dictionary confirms items' existence, allows access, and maps their physical storage location.

46. How does one interface with an RDBMS?

SQL is the language you use to communicate with an RDBMS.

47. Define SQL and explain how it differs from other programming languages.

• SQL is a non-procedural language designed for accessing data in normalized relational databases. 
• SQL differs from other programming languages in that it specifies data operations, not how to implement them.

48. Name the three major sets of disk files that make up an Oracle database.

A database consists of three major collections of files on disk. All of the files are binary. 

These are:

1.  Database files.
2. Control files.
3. Redo logs.

The most significant of these are the database files, which hold the actual data. The design relies on control files and redo logs to work properly. Oracle requires all three sets of files to be present, open, and available for database use. Without these files, the database cannot be accessed, and the administrator may need to use a backup to retrieve some or all of the data.

49. What is a Database Trigger?

A database trigger is a PL/SQL block that automatically executes insert, update, and delete operations on a table. The trigger can be set to execute once for the entire statement or for each row inserted, changed, or deleted. Database triggers can be defined for up to twelve events per table. A database trigger can execute procedures written in PL/SQL.

50. What is a Transaction Manager?

This program module keeps the database stable even during system failures and prevents conflicts between ongoing transactions.

Conclusion