DBMS Interview Questions Set 2
Q.1. What are advantages of DBMS over traditional file based systems?
Answer:- Database management systems were developed to handle the following difficulties of typical File-processing systems supported by conventional operating systems.
- Data redundancy and inconsistency
- Difficulty in accessing data
- Data isolation – multiple files and formats
- Integrity problems
- Atomicity of updates
- Concurrent access by multiple users
- Security problems
Q.2. What integrity rules exist in the DBMS?
Answer:- There are 2 major integrity rules that exist in the DBMS.
-
Entity Integrity: This states a very important rule that value of a Primary key can never have a NULL value.
-
Referential Integrity: This rule is related to the Foreign key which states that either the value of a Foreign key is a NULL value or it should be the primary key of any other relation.
Q.3. What do you understand by query optimization?
Answer:- Query optimization is the phase which identifies a plan for evaluation query that has the least estimated cost. This phase comes into the picture when there are a lot of algorithms and methods to execute the same task.
The advantages of query optimization are as follows:
- The output is provided faster
- A larger number of queries can be executed in less time
- Reduces time and space complexity
Q.4. What do you understand by aggregation and atomicity?
Answer:- Aggregation- This is a feature of the E-R model which allows a relationship set to participate in another relationship set.
Atomicity- this property states that a database modification must either follow all the rules or nothing at all. So, if one part of the transaction fails, then the entire transaction fails.
Q.5. What do you understand by functional dependency and transitive dependency in DBMS?
Answer:- Functional Dependency: A functional dependency is a constraint which is used in describing the relationship among different attributes in a relation.
Example: Consider a relation “A1” having attributes X and Y. The functional dependency among these two attributes will be X -> Y, this implies that Y is functionally dependent on X.
Transitive Dependency: A transitive dependency is a constraint which can only occur in a relation of three or more attributes.
Example: Consider a relation “A1” having attributes X, Y and Z. Now, X->Z is said to hold transitive dependency, only if the following functional dependencies holds true:
- X -> Y
- Y doesn’t ->X
- Y -> Z
Q.6. What is the difference between two and three-tier architectures?
Answer:-
| Two-tier architecture |
Three-tier architecture |
This is similar to
the client-server architecture. |
This architecture contains an extra layer between the client and the server. |
| Clients directly communicate with the database at the server-side |
Clients communicate with an application(GUI) on the server-side, that makes the system more secure and accessible. This application thereafter communicates with the database system. |
Q.7. What is a transaction? What are ACID properties?
Answer:- A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them.
An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them.
Q.8. What Is Weak Entity Set?
Answer:- An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.
Q.9. Why A Database Is Called As Relational Database Model?
Answer:- A database model represents the relationship between one or more databases. The relationship is known as the relational database model. It is an extension of the normal databases without relations. It provides flexibility and allows one database to be in relation with another database. It can access the data from many databases at one time over the network.
Q.10. What Is The Difference Between DBMS And RDBMS?
Answer:-
- DBMS is persistent and accessible when the data is created or exists, but RDBMS tells about the relation between the table and other tables.
- RDBMS supports a tabular structure for data and relationship between them in the system whereas DBMS supports only the tabular structure.
- DBMS provide uniform methods for application that has to be independently accessed, but RDBMS doesn’t provide methods like DBMS but provide relationship which link one entity with another.
Q.11. What Do You Understand By Cardinality ?
Answer:- Cardinality is important and used to arrange the data inside the database. It is related to the design part and need to be properly used in database. It is used in E-R diagrams and used to show the relationship between entities/tables. It has many forms like the basic is one to one, which associate one entity with another.
Q.12. What Do You Understand By Data Independence?
Answer:- Data independence tells about the independence of the data inside the application. It usually deals with the storage structure and represents the ability to modify the schema definition. It doesn’t affect the schema definition which is being written on the higher level. There are two types of data independence:
- Physical data independence: it allow the modification to be done in physical level and doesn’t affect the logical level.
- Logical data independence: it allow the modification to be done at logical level and affects the view level.
Q.13. What is a Relation Schema and a Relation?
Answer:- A relation Schema denoted by R(A1, A2, ..., An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).
Q.14. What is meant by Proactive, Retroactive and Simultaneous Update.
Answer:-
- Proactive Update: The updates that are applied to database before it becomes effective in real world.
- Retroactive Update: The updates that are applied to database after it becomes effective in real world.
- Simultaneous Update: The updates that are applied to database at the same time when it becomes effective in real world.
Q.15. What is DML Compiler?
Answer:- It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.
Q.16. Describe concurrency control?
Answer:- Concurrency control is the process managing simultaneous operations against a database so that database integrity is no compromised. There are two approaches to concurrency control. The pessimistic approach involves locking and the optimistic approach involves versioning.
Q.17. Explain the differences between structured data and unstructured data.
Answer:- Structured data are facts concerning objects and events. The most important structured data are numeric, character, and dates. Structured data are stored in tabular form. Unstructured data are multimedia data such as documents, photographs, maps, images, sound, and video clips. Unstructured data are most commonly found on Web servers and Web-enabled databases.
For any queries or doubts refer to comment section mentioning question number with doubt
For more Technical MCQ's and Interview Questions Click here
| 
| 
