Database & Operating Systems Interview Question
Database & OS are two sections that were overlooked by me a lot of the time while preparing for interviews and it has cost me a few good opportunities. Same was the case with a few other colleagues and friends of mine.
But truth is that if you are preparing for roles like Full Stack Developer, Backend developer, etc, it’s extremely important to be well aware of the basics & intermediate level of questions. As applications or software that use databases needs to be developed in such a way that it is capable enough to handle the edge cases & handle queries involving larger data sets.
This post will involve questions mainly from MySQL & MongoDB from databases.
Enough said & done, starting with questions ;)
1. What are the various types of relationships in Database? Define them.
- One-to-one: One table has a relationship with another table having a similar kind of column. Each primary key relates to only one or no record in the related table.
- One-to-many: One table has a relationship with another table that has primary and foreign key relations. The primary key table contains only one record that relates to none, one or many records in the related table.
- Many-to-many: Each record in both the tables can relate to many numbers of records in another table.
2. What are the advantages of DBMS?
- Redundancy control, Restriction for unauthorized access, Provides multiple user interfaces, Provides backup and recovery, Enforces integrity constraints, Ensure data consistency, Easy accessibility and Easy data extraction and data processing due to the use of queries
3. How many types of database languages are?
- Data Definition Language (DDL) e.g., CREATE, ALTER, DROP, TRUNCATE, RENAME, etc. All these commands are used for updating the data that's why they are known as Data Definition Language.
- Data Manipulation Language (DML) e.g., SELECT, UPDATE, INSERT, DELETE, etc. These commands are used for the manipulation of already updated data that’s why they are part of Data Manipulation Language.
- DATA Control Language (DCL) e.g., GRANT and REVOKE. These commands are used for giving and removing the user access to the database. So, they are part of Data Control Language.
- Transaction Control Language (TCL) e.g., COMMIT, ROLLBACK, and SAVEPOINT. These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML.
4. What is the Relationship?
The Relationship is defined as an association among two or more entities. There are three type of relationships in DBMS-
One-To-One: Here one record of any object can be related to one record of another object.
One-To-Many (many-to-one): Here one record of any object can be related to many records of other objects and vice versa.
Many-to-many: Here more than one record of an object can be related to n number of records of another object.
5. What do you understand by query optimization?
The term query optimization specifies an efficient execution plan for evaluating a query that has the least estimated cost. The concept of query optimization came into the frame when there were a number of methods, and algorithms that existed for the same task then the question arose that which one is more efficient, and the process of determining the efficient way is known as query optimization.
6. What is the E-R model?
7. What are the three levels of data abstraction?
Physical level: It is the lowest level of abstraction. It describes how data are stored.
Logical level: It is the next higher level of abstraction. It describes what data are stored in the database and what relationship among those data.
View level: It is the highest level of data abstraction. It describes only part of the entire database.
8. What do you understand by join?
In DBMS, a join statement is mainly used to combine two tables based on a specified common field between them. If we talk in terms of Relational algebra, it is the cartesian product of two tables followed by the selection operation.
Joins can be broadly categorized into: Inner Join & Outer Join
9. Explain Inner join & Outer Join?
Inner join is a join that can be used to return all the values that have matching values in both tables.
Outer Join is a join that can be used to return the records in both the tables whether it has matching records in both the tables or not.
10. What are different types of Inner Join?
- Equi Join — Inner join that uses the equivalence condition for fetching the values of two tables.
- Natural Join — Inner join that returns the values of the two tables on the basis of a common attribute that has the same name and domain. It does not use any comparison operator. It also removes the duplicate attribute from the results.
11. What are different types of Outer Join?
- Left-Outer Join — Returns all the values of the left table and the values of the right table that has matching values in the left table. If there is no matching result in the right table, it will return null values in that field.
- Right-Outer Join — Returns all the values of the right table and the values of the left table that has matching values in the right table
- Full-Outer Join — Contains all the values of both the tables whether they have matching values in them or not.
12. Explain ACID properties?
ACID properties are some basic rules, which have to be satisfied by every transaction to preserve integrity. These properties and rules are:
ATOMICITY: Atomicity is more generally known as the all or nothing rule. This implies all are considered as one unit, and they either run to completion or are not executed at all.
CONSISTENCY: This property refers to the uniformity of the data. Consistency implies that the database is consistent before and after the transaction.
ISOLATION: This property states that the number of the transaction can be executed concurrently without leading to the inconsistency of the database state.
DURABILITY: This property ensures that once the transaction is committed it will be stored in the non-volatile memory and a system crash can also not affect it anymore.
13. What is the difference between a DELETE command and TRUNCATE command?
DELETE command: DELETE command is used to delete rows from a table based on the condition that we provide in a WHERE clause.
TRUNCATE command: TRUNCATE command is used to remove all rows (complete data) from a table. It is similar to the DELETE command with no WHERE clause.
14. What is the difference between a shared lock and an exclusive lock?
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. 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 in the database.
15. What are the differences between SQL and NoSQL?
16. What is a Database Transaction?
A Database Transaction is a logical unit of processing in a DBMS which entails one or more database access operation. In a nutshell, database transactions represent real-world events of any enterprise.
All types of database access operation which are held between the beginning and end transaction statements are considered as a single logical transaction in DBMS. During the transaction the database is inconsistent. Only once the database is committed the state is changed from one consistent state to another.
17. Explain when does deadlock happen?
A deadlock is a condition where two or more transactions are waiting indefinitely for one another to give up locks. Deadlock is said to be one of the most feared complications in DBMS as no task ever gets finished and is in waiting state forever.
18. What are the deadlock detection methods?
19. What are the deadlock recovery methods?
20. Write a query to find second highest salary of an employee using a single query?
21. Write a query to find second highest salary of an employee using nested query?
22. Explain different types of database normalizations?
23. What is a Collection in MongoDB?
A collection in MongoDB is a group of documents. If a document is the MongoDB analog of a row in a relational database, then a collection can be thought of as the analog to a table.
24. How does Scale-Out occur in MongoDB?
The document-oriented data model of MongoDB makes it easier to split data across multiple servers. Balancing and loading data across a cluster is done by MongoDB. It then redistributes documents automatically.
The mongos acts as a query router, providing an interface between client applications and the sharded cluster.
Config servers store metadata and configuration settings for the cluster. MongoDB uses the config servers to manage distributed locks. Each sharded cluster must have its own config servers.
25. What are the data types in MongoDB?
- Null
{"x" : null} - Boolean
{"x" : true} - Number
{"x" : 4} - String
{"x" : "foobar"} - Date
{"x" : new Date()} - Regular expression
{"x" : /foobar/i} - Array
{"x" : ["a", "b", "c"]} - Embedded document
{"x" : {"foo" : "bar"}} - Object ID
{"x" : ObjectId()} - Binary Data
Binary data is a string of arbitrary bytes. - Code
{"x" : function() { /* ... */ }}
26. When to use MongoDB?
27. Explain the term “Indexing” in MongoDB.
In MongoDB, indexes help in efficiently resolving queries. What an Index does is that it stores a small part of the data set in a form that is easy to traverse. The index stores the value of the specific field or set of fields, ordered by the value of the field as specified in the index.
Indexes look at an ordered list with references to the content. These in turn allow MongoDB to query orders of magnitude faster. To create an index, use the createIndex collection method.
28. Explain the process of Sharding.
Sharding is the process of splitting data up across machines.We can store more data and handle more load without requiring larger or more powerful machines, by putting a subset of data on each machine.
MongoDB’s sharding allows you to create a cluster of many machines (shards) and break up a collection across them, putting a subset of data on each shard. This allows your application to grow beyond the resource limits of a standalone server or replica set.
29. What is the Aggregation Framework in MongoDB?
- The aggregation framework is a set of analytics tools within MongoDB that allow you to do analytics on documents in one or more collections.
- The aggregation framework is based on the concept of a pipeline. With an aggregation pipeline, we take input from a MongoDB collection and pass the documents from that collection through one or more stages, each of which performs a different operation on its inputs. Each stage takes as input whatever the stage before it produced as output. The inputs and outputs for all stages are documents — a stream of documents.
30. What is a Replica Set in MongoDB?
To keep identical copies of your data on multiple servers, we use replication. It is recommended for all production deployments. Use replication to keep your application running and your data safe, even if something happens to one or more of your servers.
Such replication can be created by a replica set with MongoDB. A replica set is a group of servers with one primary, the server taking writes, and multiple secondaries, servers that keep copies of the primary’s data. If the primary crashes, the secondaries can elect a new primary from amongst themselves.
31. What are some utilities for backup and restore in MongoDB?
The mongo shell does not include functions for exporting, importing, backup, or restore. However, MongoDB has created methods for accomplishing this, so that no scripting work or complex GUIs are needed. For this, several utility scripts are provided that can be used to get data in or out of the database in bulk. These utility scripts are:
- mongoimport
- mongoexport
- mongodump
- mongorestore
32. What is time- sharing system?
In a Time-sharing system, the CPU executes multiple jobs by switching among them, also known as multitasking. This process happens so fast that users can interact with each program while it is running.
33. Briefly explain FCFS. What is RR scheduling algorithm?
FCFS stands for First-come, first-served. It is one type of scheduling algorithm. In this scheme, the process that requests the CPU first is allocated the CPU first. Implementation is managed by a FIFO queue.
RR (round-robin) scheduling algorithm is primarily aimed for time-sharing systems. A circular queue is a setup in such a way that the CPU scheduler goes around that queue, allocating CPU to each process for a time interval of up to around 10 to 100 milliseconds.
34. What are necessary conditions which can lead to a deadlock situation in a system?
Deadlock situations occur when four conditions occur simultaneously in a system: Mutual exclusion; Hold and Wait; No preemption; and Circular wait.
35. How does dynamic loading aid in better memory space utilization?
With dynamic loading, a routine is not loaded until it is called. This method is especially useful when large amounts of code are needed in order to handle infrequently occurring cases such as error routines.
36. What is the basic function of paging?
Paging is a memory management scheme that permits the physical address space of a process to be noncontiguous. It avoids the considerable problem of having to fit varied sized memory chunks onto the backing store.
37. When does thrashing occur?
Thrashing refers to an instance of high paging activity. This happens when it is spending more time paging instead of executing.
38. What is preemptive multitasking?
Preemptive multitasking allows an operating system to switch between software programs. This, in turn, allows multiple programs to run without necessarily taking complete control over the processor and resulting in system crashes.
39. What is RAID structure in OS? What are the different levels of RAID configuration?
RAID (Redundant Arrays of Independent Disks) is a method used to store data on Multiple hard disks therefore it is considered as data storage virtualization technology that combines multiple hard disks. It simply balances data protection, system performance, storage space, etc. It is used to improve the overall performance and reliability of data storage. It also increases the storage capacity of the system and its main purpose is to achieve data redundancy to reduce data loss.
40. What is IPC? What are the different IPC mechanisms?
IPC (Interprocess Communication) is a mechanism that requires the use of resources like a memory that is shared between processes or threads. With IPC, OS allows different processes to communicate with each other. It is simply used for exchanging data between multiple threads in one or more programs or processes. In this mechanism, different processes can communicate with each other with the approval of the OS.
Different IPC Mechanisms:
- Pipes
- Message Queuing
- Semaphores
- Socket
- Shared Memory
- Signals
41. What is virtual memory?
It is a memory management technique feature of OS that creates the illusion to users of a very large (main) memory. It is simply space where a greater number of programs can be stored by themselves in the form of pages. It enables us to increase the use of physical memory by using a disk and also allows us to have memory protection. It can be managed in two common ways by OS i.e., paging and segmentation. It acts as temporary storage that can be used along with RAM for computer processes.
42. What is starvation and aging in OS?
When we use Priority Scheduling or Shortest Job First Scheduling, Starvation can happen, This algorithm is mostly used in CPU schedulers
Starvation: It is generally a problem that usually occurs when a process has not been able to get the required resources it needs for progress with its execution for a long period of time. In this condition, low priority processes get blocked and only high priority processes proceed towards completion because of which low priority processes suffer from lack of resources.
Aging: It is a technique that is used to overcome the situation or problem of starvation. It simply increases the priority of processes that wait in the system for resources for a long period of time. It is considered the best technique to resolve the problem of starvation as it adds an aging factor to the priority of each and every request by various processes for resources. It also ensures that low-level queue jobs or processes complete their execution.
43. What do you mean by Semaphore in OS? Why is it used?
Semaphore is a signaling mechanism. It only holds one positive integer value. It is simply used to solve the problem or issue of critical sections in the synchronization process by using two atomic operations i.e., wait() and signal().
44. What is Kernel and write its main functions?
The kernel is basically a computer program usually considered as a central component or module of OS. It is responsible for handling, managing, and controlling all operations of computer systems and hardware. Whenever the system starts, the kernel is loaded first and remains in the main memory. It also acts as an interface between user applications and hardware.
Functions of Kernel:
- It is responsible for managing all computer resources such as CPU, memory, files, processes, etc.
- It facilitates or initiates the interaction between components of hardware and software.
- It manages RAM memory so that all running processes and programs can work effectively and efficiently.
- It also controls and manages all primary tasks of the OS as well as manages access and use of various peripherals connected to the computer.
- It schedules the work done by the CPU so that the work of each user is executed as efficiently as possible.
45. What is Context Switching?
Context switching is basically a process of saving the context of one process and loading the context of another process. It is one of the cost-effective and time-saving measures executed by CPU the because it allows multiple processes to share a single CPU. Therefore, it is considered an important part of a modern OS. This technique is used by OS to switch a process from one state to another i.e., from running state to ready state. It also allows a single CPU to handle and control various different processes or threads without even the need for additional resources.
Front-end Interview Questions — https://harinathr95.medium.com/full-stack-developer-frequently-asked-interview-questions-5f76b5adff8f
PHP/Laravel Interview Questions — https://harinathr95.medium.com/full-stack-developer-interview-questions-2-php-laravel-4c5e3c6cf66a
Share your feedbacks :)
