Q1. What is the E-R model in the DBMS?
E-R model is known as an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.It is a diagrammatic approach to database design, where you represent real-world objects as entities and mention relationships between them. This approach helps the team of DBAs’ to understand the schema easily.
Q2. What do you understand by query optimization?
Query optimization is the phase that 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.
Q3. Mention the advantages of query optimization.
1). The output is provided faster
2). A larger number of queries can be executed in less time
3). Reduces time and space complexity
Q4. What do you understand by intention and extension?
Intension: Intension or most commonly known as Database schema defines the description of the database. This is specified during the database design and mostly remains unchanged.
Extension: Extension is the number of tuples available in the database at any instance of time. This value keeps changing as and when the tuples are created, updated, and destroyed. So, the data present in the database at a specific instance of time is known as the extension of the database or most commonly known the snapshot of the database.
Q5. What do you understand by cursor?
A cursor is a database object which helps in manipulating data, row by row, and represents a result set.
Q6. Mention the different types of the cursor.
Implicit cursor: This type of cursor is declared automatically as soon as the execution of SQL takes place. Here, the user is not indicated about the declaration of the cursor.
Explicit cursor: This type of cursor is defined by the PL/ SQL, as it handles a query in more than a single row.
Q7. Explain the terms specialization and generalization
Specialization: Specialization is a process of defining a set of subclasses of the entity type. Here, each subclass will contain all the attributes and relationships of the parent entity. Apart from this, the subclasses may contain additional attributes and relationships specific to themselves.
Generalization: Generalization is a process of finding relations, and common attributes for a particular set of entities, and finally defining a common superclass for them.
Q8. What do you understand by Data Independence?
When you say an application has data independence, it implies that the application is independent of the storage structure and data access strategies of data.
Q9. What is Index hunting and how does it help in improving query performance?
The process of boosting a collection of indexes is known as Index hunting. This is done as indexes improve the query performance and the speed at which they are processed.
1). The best queries are suggested using the query optimizer.
2). Index, query distribution, and their performance are used as metrics to check the effect
3). Databases are tuned into a small collection of problem queries.
Q10. Explain what is a deadlock and mention how it can be resolved.
Deadlock is a situation that occurs when two transactions wait on a resource that is locked or another transaction holds. Deadlocks can be prevented by making all the transactions acquire all the locks at the same instance of time. So, once deadlock occurs, the only way to cure is to abort one of the transactions and remove the partially completed work.
Q11. What are the differences between an exclusive lock and a shared lock?
Exclusive Lock: An exclusive lock is a lock on a data item when a transaction is about to perform the write operation.
Shared Lock: A shared lock allows more than one transaction to read the data items.
Q12. Mention the issues with traditional file-based systems that make DBMS a better choice.
The absence of indexing in a traditional file-based system leaves us with the only option of scanning the full page and hence making the access of content tedious and super slow. The other issue is redundancy and inconsistency as files have many duplicate and redundant data and changing one of them makes all of them inconsistent. Accessing data is harder in traditional file-based systems because data is unorganized in them.
Another issue is the lack of concurrency control, which leads to one operation locking the entire page, as compared to DBMS where multiple operations can work on a single file simultaneously.
Integrity check, data isolation, atomicity, security, etc. are some other issues with traditional file-based systems for which DBMSs have provided some good solutions.
Q13. Explain the difference between intension and extension in a database.
Intension: Intension or popularly known as database schema is used to define the description of the database and is specified during the design of the database and mostly remains unchanged.
Extension: Extension on the other hand is the measure of the number of tuples present in the database at any given point in time. The extension of a database is also referred to as the snapshot of the database and its value keeps changing as and when the tuples are created, updated, or destroyed in a database.
Q14. Describe the types of keys.
Primary key: The Primary key is an attribute in a table that can uniquely identify each record in a table. It is compulsory for every table.
Candidate key: The Candidate key is an attribute or set of an attribute that can uniquely identify a tuple. The Primary key can be selected from these attributes.
Super key: The Super key is a set of attributes that can uniquely identify a tuple. Super key is a superset of the candidate key.
Foreign key: The Foreign key is a primary key from one table, which has a relationship with another table. It acts as a cross-reference between tables.
Q15. What is System R?
System R is the first implementation of SQL, which is the standard relational data query language, and it was also the first to demonstrate that RDBMS could provide better transaction processing performance. It is a prototype that is formed to show that it is possible to build a Relational System that can be used in a real-life environment to solve real-life problems.
Q16. What is Data Independence?
Data independence specifies that "the application is independent of the storage structure and access strategy of data." It makes you able to modify the schema definition at one level without altering the schema definition to the next higher level.
It makes you able to modify the schema definition in one level and should not affect the schema definition in the next higher level.
Q17. Mention types of Data Independence.
Physical Data Independence: Physical data is the data stored in the database. It is in the bit format. Modification at the physical level should not affect the logical level.
Logical Data Independence: Logical data in the data about the database. It basically defines the structure. Such as tables stored in the database. Modification at the logical level should not affect the view level.
Q18. What is Join?
The Join operation is one of the most useful activities in relational algebra. It is the most commonly used way to combine information from two or more relations.
Q19. Different types of Join.
Theta join
Natural join
Equi join
Left outer join
Right outer join
Full outer join
Q20. How do you communicate with an RDBMS?
You have to use Structured Query Language (SQL) to communicate with the RDBMS. Using queries of SQL, we can give the input to the database, and then after processing the queries database will provide us the required output.
Q21. What is 2-Tier architecture?
The 2-Tier architecture is the same as the basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database on the server side.
Q22. What is the 3-Tier architecture?
The 3-Tier architecture contains another layer between the client and server. The introduction of 3-tier architecture is for the ease of the users as it provides the GUI, which, makes the system secure and much more accessible. In this architecture, the application on the client-end interacts with an application on the server which further communicates with the database system.
No comments:
Post a Comment