Cracking the DBMS Round
This blog is continuation of my previous blog in the Interview Preparation series 👩🏻💻 🚀.
In Part 4 of Cracking Dream Interview blog series, I will talk about “Must Practice Interview Questions of DBMS ”.
1. What is database and DBMS. Also, mention the different types of DBMS.
Database-
Database is organized collection of data .The data is stored and retrieved digitally from a remote or local computer system.
DBMS-
It allows the user to interact with database. It is a system software responsible for creation, updation and complete management of database.
Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.
The different types of DBMS are as follows:
- Relational DBMS (RDBMS)- In this type of DBMS, data is stored in the form of tables.
- Hierarchical DBMS- This type of DBMS has a structure similar to that of a tree, wherein the nodes represent records and the branches of the tree represent fields.
- Network DBMS- This type of DBMS supports many-to-many relations wherein multiple member records can be linked.
- Object-oriented DBMS- Uses small individual software called object to store pieces of data and the instructions for the actions to be done with the data.
2. What is database tuning?
It allows easy access to database.
Helps in accessing the database fastly.
3. What are relationships and mention different types of relationships in the DBMS?
Any association between two entity types is called a relationship.
For example, A teacher teaches students. Here, “teaches” is a relationship and this is the relationship between a Teacher entity and a Student entity.
It is represented by a diamond shape.
A relationship in DBMS is the scenario where two entities are related to each other.
4. Why is the use of DBMS recommended? Explain by listing some of its major advantages.
Controlled Redundancy:
DBMS supports a mechanism to control the redundancy of data inside the database by integrating all the data into a single database.
Data is stored at only one place, the duplicity of data does not happen.
Data Sharing:
Sharing of data among multiple users simultaneously can also be done in DBMS as the same database will be shared among all the users and by different application programs.
Backup and Recovery Facility:
Backup of data is also supported by DBMS.
Independence of data:
It simply means that you can change the structure of the data without affecting the structure of any of the application programs.
5. What is Database architecture?
6. What is database normalization?
It is a technique to remove or reduce redundancy from the table.
Duplicacy can be either Row Level or Column Level
Row level duplicacy can be removed by setting primary keys.
It is a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the following desirable properties:
1) Minimizing Redundancy
2) Minimizing the Insertion, Deletion, And Update Anomalies(problem at special occasion)
# One way to remove duplication is to divide the main table into sub tables.
7. What are advantages of DBMS over traditional file based systems?
Database management systems were developed to handle the following difficulties of typical File-processing systems supported by conventional operating systems:
1. Data redundancy and inconsistency
2. Difficulty in accessing data
3. Data isolation — multiple files and formats
4. Integrity problems
5. Atomicity of updates
6. Concurrent access by multiple users
7. Security problems
8. What are the ACID properties in DBMS (Imp)?
ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.
Atomicity-
Atomicity refers to those data transactions which are completely successful or failed. e.g online transaction (either fail or successful).
Either all instructions of the transaction will be executed or none of the instruction will be executed
Consistency-
If the database remain stable stable or consistent even after performing the transaction then is said to maintain consistency.
e.g Before and after the transaction the total sum of money should be same.
Isolation-
The main goal of isolation is concurrency control, achieve seriability.
If multiple transactions are executed together and none of the transactions affect each other then is called isolation/logical isolation.
Durability-
If you make some changes in the database then those changes must persist/remain in the database irrespective of any software, hardware failure.
All changes we are making in database should be permanent. A successful transaction ( after commit ) can not be reverted back.
9. What is Join?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:
- Inner Join
- Right Join
- Left Join
- Full Join
An SQL Join is used to combine data from two or more tables, based on a common field between them.
For example, consider the following two tables.
Following is join query that shows names of students enrolled in different courseIDs.
10. What is a view in SQL? How to create one.
A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.
A view in SQL is a single table, which is derived from other tables. So, a view contains rows and columns similar to a real table and has fields from one or more table.
To create a view, use the following syntax:
11. What is a Trigger?
Trigger is one of the very important codes or programs which get executed automatically in response to the events that occur in a table or a view.
For Example, If a new record is inserted in an employee database then the data gets created automatically in the related tables like salary, department and roles tables.
A Trigger is a code that associated with insert, update or delete operations. Triggers can be useful to maintain integrity in database.
12. Explain the Stored Procedure.
Stored Procedure is a group of SQL statements in the form of a function that has some unique name and is stored in relational database management systems(RDBMS) and can be accessed whenever required.
The stored procedure increases the reusability as here the code or the procedure is stored into the system and used again and again that makes the work easy, takes less time in processing and decreases the complexity of the system.
13. What integrity rules exist in the DBMS?
Data integrity is one significant aspect while maintaining the database.
There are 2 major integrity rules that exist in the DBMS.
14. Mention the differences between Trigger and Stored Procedures.
15. What is ER Diagram?
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.
Example-
ER Diagram is graphical representation of entity, attributes and relationship.
16. What is the main difference between UNION and UNION ALL?
UNION and UNION ALL are used to join the data from 2 or more tables but UNION removes duplicate rows and picks the rows which are distinct after combining the data from the tables whereas UNION ALL does not remove the duplicate rows, it just picks all the data from the tables.
17. What are different types of keys in DBMS?
Super key-
It is attribute or set of attributes that uniquely identifies the tuples.
Candidate key-
It is minimal subset of super key. If proper subset if any super key is a super key then that key can not be a candidate key.
Primary key-
That candidate key which is chosen to uniquely identify the rows of the table.
Alternate key-
All candidate keys that are not primary key are alternate keys.
Foreign Key-
That key in the table which is used to define it’s relation with other tables.
18. What is the difference between primary key and unique constraints?
Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constraints.
Constraints are used to specify the rules concerning data in the table. Constraints are used to limit the type of data that can go into a table.
19. What are the different subsets of SQL?
- DDL (Data Definition Language) — It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
- DML ( Data Manipulation Language) — It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
- DCL ( Data Control Language) — It allows you to control access to the database. Example — Grant, Revoke access permissions.
20. Do we consider NULL values the same as that of blank space or zero?
A NULL value is not at all same as that of zero or a blank space.
The NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas zero is a number and blank space is a character.
21. How is the pattern matching done in the SQL?
With the help of the LIKE operator, pattern matching is possible in the SQL.
’%’ is used with the LIKE operator when it matches with the 0 or more characters and ‘_’ is used to match the one particular character.
Example:
SELECT * from Emp WHERE name like ‘b%’;
SELECT * from Emp WHERE name like ‘hans_’;
22. What are the different levels of abstraction in the DBMS?
There are three levels of data abstraction in DBMS. They are:
- Physical Level: It is the lowest level of abstraction and describes how the data is stored.
- Logical Level: This is the next level of abstraction after the Physical level. This layer determines what data is stored in the database, and what is the relationship between the data points.
- View Level: The View Level is the highest level of abstraction and it describes only a part of the entire database.
23. What is Atomicity?
This property states that either all the instructions of the database are executed or none of them are executed. So, if one part of the transaction fails, then the entire transaction fails.
24. What do you understand by query optimization?
It identifies that plan for processing query that would take least 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
25. Mention a query to add email validation to your database
SELECT Email FROM Customers WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0–9._%+-]+@[A-Z0–9.-]+.[A-Z]{2,4}’, ‘i’);
26. Write a query to create a duplicate table with and without data present?
Consider you have a table named Customers, having details such as CustomerID, CustomerName and so on.
Now, if you want to create a duplicate table named ‘DuplicateCustomer’ with the data present in it, you can mention the following query:
CREATE TABLE DuplicateCustomer AS SELECT * FROM Customers;
Similarly, if you want to create a duplicate table without the data present, mention the following query:
CREATE TABLE DuplicateCustomer AS SELECT * FROM Customers WHERE 1=2;
27. What are the differences between network and hierarchical database model?
28. Explain the terms specialization and generalization
Specialization (Top Down)-
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 itself.
Generalization (Bottom Up)-
It is a process of finding common attributes for a particular set of entities; and finally defining a common superclass for them.
29. What do you understand by cursor? Mention the different types of cursor.
A cursor is a database object which helps in manipulating data, row by row and represents a result set.
The types of cursor are as follows:
- 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.
30. What is Lock Based Protocol?
To achieve consistency, isolation is most important idea. (i.e if one transaction is accessing the data item and performing some task then other transaction shouldn’t interrupt )
Locking is the simple idea to achieve isolation i.e First obtain lock on the data item then perform desired operation and then unlock it.
To provide better concurrency along with isolation we use different modes of locks. Example- Shared Mode and Exclusive Mode
Shared Lock-
Shared lock is required for reading a data item.
In the shared lock, many transactions may hold a lock on the same data item at the same time. When more than one transaction is allowed to read the data items then that is known as the shared lock.
Exclusive Lock-
When any transaction is about to perform the write operation, then the lock on the data item is an exclusive lock.
Because, if we allow more than one transaction then that will lead to the inconsistency (clash) in the database.
31. What is the 3-Tier architecture?
The 3-Tier architecture contains another layer between the client and server. Introduction of 3-tier architecture is for the ease of the users as it provides the GUI, which, make the system secure and much more accessible.
32. What do you mean by extension and intension
Extension: The Extension is the number of tuples present in a table at any instance. It changes as the tuples are created, updated and destroyed.
Intension: Intension is also known as Data Schema and defined as the description of the database, which is specified during database design and is expected to remain unchanged. The Intension is a constant value that gives the name, structure of tables and the constraints laid on it.
33. What is Denormalization?
Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data. Denormalization is a part of database optimization technique. This process is used to avoid the use of complex and costly joins.
34. When does checkpoint occur in DBMS?
The Checkpoint is a type of mechanism where all the previous logs are removed from the system and permanently stored in the storage disk.
A checkpoint is like a snapshot of the DBMS state. Using checkpoints, the DBMS can reduce the amount of work to be done during a restart in the event of subsequent crashes. Checkpoints are used for the recovery of the database after the system crash.When due to a system crash we need to restart the system then at that point we use checkpoints. So that, we don’t have to perform the transactions from the very starting.
35. What do you mean by transparent DBMS?
The transparent DBMS is a type of DBMS which keeps its physical structure hidden from users. Physical structure or physical storage structure implies to the memory manager of the DBMS, and it describes how the data stored on disk.
# Bonus Tips for your next Interview 💖
List of Questions to be asked from interviewer at end:
- What do you think would be a realistic goal for me to achieve in the first 60 to 90 days?
- What are the key responsibilities in this position?
- What kind of projects will I be handling in this position?
- What types of employees are most successful in this organization?
Do clap 👏🏻👏🏻 50 times and share the article if you like it. Thank you for giving your valuable time.
