1) What is DBMS?
DBMS (Database Management System) is software for storing and retrieving users’ data while also considering appropriate security measures. The system is composed of a number of programs that manipulate the Database. It accepts the request for data from an application and instructs the operating system to provide the specific data.
2) What is RDBMS?
RDBMS is a short form of Relational Database Management system. It is a software system that stores only data that needs to be stored in related tables. In RDBMS, data is managed and stored in rows and columns, known as tuples and attributes.
3) What are the Important differences between DBMS and RDBMS?
Here are some important differences between DBMS and RDBMS:
4) What are the Advantages of Using a DBMS system?
Here are advances in using a DBMS system:
DBMS offers various techniques and powerful functions to efficiently store & retrieve data. It is an efficient handler to balance the needs of multiple applications using the same data. Provides Uniform administration procedures for data management. Application programs that are never exposed to details of data representation and storage. It implies integrity constraints to get a high level of protection against prohibited access to data. It helps you to reduce Application Development Time.
5) What is Live Lock? Give one example.
Livelock is a situation in which an exclusive lock request is repeatedly denied as many overlapping shared locks keep interfering with each other. The processes keep changing their status, preventing them from completing the task.
Example 1:
The most straightforward example of Livelock would be two people who meet face-to-face in a corridor, and both move aside to allow the other people to pass. However, they end up moving from side to side without making any progress as they move the same way at the time.
Example2:
You can see in the given image that each of the two processes needs two resources. They attempt to obtain locks by using the primitive polling enter registry. However, in case the attempt fails, the method works again.
6) What is Database Partitioning?
Division of a logical database into complete independent units for improving its management, availability, and performance is called Database partitioning.
7) What is a Deadlock?
A Deadlock is a situation that occurs in OS when any process enters a waiting state as another waiting process is holding the demanded resource. It is a common problem in multi-processing where several processes share a specific type of mutually exclusive resource known as a soft lock. Example of Deadlock: A real-world example should be traffic, which goes only in one direction. Here, a bridge is considered a resource.
So, a Deadlock can be quickly resolved if one car backs up (Pre-empt resources and rollback). Numerous cars may have to be backed up if a deadlock situation happens. At that time, starvation is possible.
8) What is Relational Algebra in the DBMS system?
Relational Algebra is a procedural query language that collects relations as input and gives occurrences of relations as output. It uses various operations to perform this action.
9) Define Unary operations in Relational Algebra?
In relational algebra, PROJECTION and SELECTION are unary operations. Unary operations are those kinds of operations that use only single operands. As in SELECTION, relational operators are used, for example – =, <=,>=, etc.
10) What is Concurrency Control?
Concurrency Control is a procedure that helps to manage simultaneous operations without conflicting with each other. It helps you to ensure that Database transactions are executed concurrently and accurately.
11) What are the different types of languages available in the DBMS?
Here are three types of languages used in DBMS:
DDL: Data Definition Language, which defines the database and schema structure using CREATE, ALTER, DROP, and RENAME. DCL: DCL is a Data Control Language used to control the users’ access inside the Database using some SQL Queries like REVOKW and GRANT. DML: Data Manipulation Language, which is used to manipulate data in databases, such as inserting, deleting, etc., by using some SQL Queries like SELECT, UPDATE, INSERT, and DELETE.
12) What is Functional Dependency?
Functional Dependencies (FD) in Database Management systems define the relationship between an attribute and another. It allows you to maintain the quality of data in the Database.
13) What is ER model?
ER Model or Entity-Relationship Model is based on the natural world and deals with entities and their relationships.
14) What is an Attribute to ER Model?
An attribute is a particular property that describes an entity. For example, if the student is an entity, then Student_id, Std_firstname, and Std_lastname are attributes that describe the student.
15) What is Primary Key? Explain with an example.
Primary keys are columns or sets of columns that uniquely identify every row in the table of an RDMS system. This key can’t be duplicated means the same value should not appear more than once in the table. Syntax: In this example,
Table_Name is the name of the table you have to create. Column_Name is the name of the column having the primary key.
Example: In the given an example, we have created a student table with columns StdID, Roll No, First_Name, Last_Name, and Email id. It should be selected as a primary key because it can uniquely identify other rows in the table.
16) What is Foreign Key? Give one example of it
A foreign key is a column that creates a relationship between two tables. They are used to maintain data integrity and facilitate navigation between two instances of the same entity. Moreover, it serves as a cross-reference between two tables since it refers to another table’s primary key. Example: In this given an example, we have created a student table with columns like Stu_ID, Roll No, First Name, Last Name, and Email. Stu_ID is chosen as a primary key because it can uniquely identify other rows in the table. Example of Foreign Key Syntax: Here is the syntax of Foreign Key:
17) What is Database Relationship?
The database relationship is associations between single or multiple tables that are developed using join statements. Data can be efficiently retrieved from the Database using this method. There are primarily three types of relationships in DBMS:
One-to-One, One-to-many, Many-to-many.
18) What is Starvation?
Starvation is a situation where all the low-priority processes get blocked. In any system, requests for high and low-priority resources keep happening dynamically. Therefore, some policy is required to decide who gets support and when.
19) What are ACID Properties?
The ACID in DBMS stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity: It is a transaction that is a single unit of operation. You can execute it entirely or do not execute it at all, but there can’t be partial execution. Consistency: Once the transaction is executed, which should move from one consistent state to another. Isolation: The transaction should be executed in isolation from other transactions (no Locks). Concurrently executed transactions should not share intermediate transaction results. Durability: After successfully competing for the transaction, all the changes in the Database need to persist even in the case of system failures.
Example of ACID Property in DBMS:
20) What is a Schedule?
A Schedule is creating a group of multiple parallel transactions and executing them one after one. The order in which the instructions appear should be preserved in every transaction. In case two transactions are executed simultaneously, the result of one transaction may affect the output of the other.
21) What are the main differences between UNION and UNION ALL?
UNION and UNION ALL are used to join the data from two or more tables. However,
UNION removes duplicate rows and picks. The rows are distinct after combining the data from the tables were UNION ALL does not remove the duplicate rows; it just picks all the data from the tables.
22) What is Correlated Subquery in DBMS?
Correlated Subqueries executed for each row of the outer query are known as Correlated Subqueries. They are also called nested queries, i.e., a query written inside some query.
23) What is the aim of Normalization in DBMS?
Normalization is a vital process that removes redundancy from a set of relations. It helps reduce irregularity in the insert, delete and update commands by dividing large tables into smaller tables and using keys to link them.
24) What is Index Hunting?
Index Hunting is a data structure that improves the speed of data retrieval operations on a database. It is the procedure of boosting the collection of indexes. It is done by using methods like query distribution and query optimization.
25) What is a Distributed Database System?
A Distributed Database System is a set of interconnected databases spread physically across various locations. It can be on the same network or multiple networks.
26) What is Data Warehousing?
Data Warehousing is a process for collecting and managing data from varied sources to provide meaningful business insights. This method is typically used to connect and analyze business data from heterogeneous sources.
27) What is Database Architecture?
Database Architecture is a DBMS design representation that helps you Design, develop, implement, and maintain the DBMS system. It divides the DBMS into separate components that can be independently changed, modified, replaced, and altered.
28) What do you mean by transparent DBMS?
The transparent DBMS keeps its physical structure hidden from users. Physical structure or physical storage structure implies the memory manager of the DBMS.
29) What is Join?
Join operation is one of the most valuable activities in relational algebra. It is widely used to combine information from two or more relations. It is performed based on the same or related column. The JOIN command is used in most complex SQL queries.
30) What is Inner Join?
The inner JOIN enables you to return rows from both tables when specific conditions are satisfied. For example, you want a list of members who have rented movies and the titles of the films they rented. Using an INNER JOIN, you can return rows from both tables that satisfy the specified criteria.
31) What is Right Join?
The Right Join returns all the columns from the table on the right, even when no matching rows have been found on the left table. In our example, let’s assume you need to get the members’ names and movies rented by them.
32) What is 1NF?
1NF is the First Normal Form. It is the simplest type of Normalization which you can implement in a database. Every column must have an atomic (single value)
It helps you to Remove duplicate columns from the same table It should have created separate tables for each group of related data and helped you to identify each row with a unique column
33) What is 2NF in the DBMS?
2NF is the Second Normal Form. Any table said to have in the 2NF should satisfy the following 2 conditions:
A table is in the 1NF. Each non-prime attribute of a table is functionally dependent on the primary key.
34) What is BCNF?
BCNF is a sort from the Boyce-Codd Normal Form. It is an advanced version of the 3rd Normal Form, also called 3.5NF. A table that complies with BCNF and satisfies the following conditions:
It is in 3NF To have functional dependency X->Y, X should be the super key of the entire table. It means X can’t be a non-prime attribute if Y is a prime attribute.
35) How Can you communicate with an RDBMS?
You have to use SQL to communicate with the RDBMS using queries of SQL to provide the input to the Database. After processing the queries, the Database will provide us with the required output.
36) When does a checkpoint occur in DBMS?
A checkpoint is a snapshot of the DBMS’s current state. The DBMS uses checkpoints to limit the amount of work required during a restart in the event of a subsequent crash. Mainly, the log-based recovery solution employs checkpoints, so we won’t have to execute the transactions from the beginning.
37) What integrity rules are in the DBMS?
Two important integrity rules exist in the DBMS:
Entity Integrity: This states an essential rule that the value of a Primary key can’ be a NULL value. Referential Integrity: This rule pertains to the foreign key, which may either have a NULL value or maybe the primary key for another relation.
38) What is Indexing?
Indexing is a data structure method that helps you quickly retrieve records from a database file. An index is a small table with only two columns. The first column comprises a table’s primary or candidate key. The second column includes a set of pointers for holding the address of the disk block where a particular key value is stored.
39) What is Data Independence?
Data independence is a technique that helps you access data strategy.” It allows you to modify the schema definition in one level and should not affect it in the next higher level.
40) What is ER Diagram?
Entity Relationship Diagram, also called ERD, displays the relationship of entity sets stored in a database. This diagram using in DBMS to help to explain the logical structure of databases. These DBMS diagrams are created based on three basic concepts: entities, attributes, and relationships.
41) What is Hashing in DBMS?
In a DBMS system, hashing is a method to directly find the location of requested data on the disk need to use an index structure. A shorter hashed critical value is used in place of the original critical value when indexing and retrieving items in the Database.
42) What is Transparent DBMS?
The transparent DBMS is a type of Database Management System that conceals its physical structure from users. Physical structure. It is also known as physical storage structure, refers to the DBMS’s memory manager, and explains how data is saved on a disc.
43) What is the Degree of Relation?
The Degree of a Relationship is the relation schema’s attributes. Cardinality refers to the number of times an entity occurs about the same number of times as another entity.
One-to-one One-to-many Many-to-one
44) What is Conceptual Design in DBMS?
Conceptual Design is the primary stage in the Database design process. The main goal at this stage is to design a database independent of database software and physical details. A conceptual data model describes this process’s main data entities, attributes, relationships, and constraints.
45) What is Data Mining?
Data mining is a step-by-step process of sorting through a large amount of data that allows you to identify trends and patterns. It uses complex statistical and mathematical Algorithms to segment data to predict likely outcomes. There are numerous tools for data mining, like RapidMiner, Teradata, Solver, Silences, etc.
46) What is Query Optimization?
Query optimizations is a process of identifying an execution plan with the least estimated cost and time for evaluating and executing every query in the DBMS system.
47) Define the term ‘Store Procedure’.
The concept of a stored procedure is quite similar to that of a function, as it contains a series of coordinated operations. It includes a set of processes frequently used in applications to perform database activities.
48) What is Timestamp-based Protocols?
Timestamp-based Protocol in DBMS is an algorithm that uses the System Time or Logical Counter as a timestamp. It helps to serialize the execution of concurrent transactions. This protocol ensures that every conflicting read and write operation is executed in timestamp order. Example: Suppose there are three transactions A1, A2, and A3.
A1 has entered the system at time 0010 A2 entered the system at 0020 A3 entered the system at 0030
Priority will be given to transaction A1, transaction A2, and Transaction A3.
49) What is Durability in DBMS?
In a DBMS system, Durability guarantees that once a transaction is committed to the Database, it will be stored in non-volatile memory to be safe against system failure.
50) What is a Catalog?
A catalog is a table containing information like each file’s structure, the type and storage format of all the data items, and various constraints on the data. The information stored in the catalog is known as metadata.
51) What is Storage Manager?
Storage Manager is a software module that provides the interface between the low-level data stored in the Database and application applications and queries submitted to the DBMS system.
52) What is Buffer Manager?
Buffer Manager is a program module responsible for fetching data from disk storage into main memory and determining what data to cache in memory.
53) What is QBE?
Query-by-example represents a graphical approach for accessing information in a database using query templates called skeleton tables. QBE is used by entering example values directly into a query template to represent what is to be achieved.
54) What is Identity?
Identity is a column that automatically generates numeric values. It is also known as an auto number. A start and increment value can be set. However, most DBMS leave these at 1.
55) What is a Trigger?
It is a code associated with insert, update or delete operations. It is executed automatically whenever the associated query is executed on a table. This code helps maintain integrity in the Database.
56) What is Correlated Subquery in DBMS?
Correlated subqueries are executed for each row of the outer query. It is also known as a nested query, that is, a query that is embedded within another query. Example:
57) What is a Clustered index?
Cluster indexes sort the rows of data in the table according to their fundamental values. In the DBMS system, there is only one clustered index per table. A clustered index helps the DBMS system to define the order in which data is stored in the table. Therefore, there can be only a single clustered index for every table.
58) What is a non-clustered index?
Non-clustered indexes store the data at one location and the indices at another. It contains pointers to the location of that data. There can be many non-clustered indexes within a single table since an index in a non-clustered index is stored in different locations.
59) What is a Unique Key?
A unique key consists of a group of one or more fields or columns of a table that uniquely identifies each record in a database. It is the same as a primary key but can accept one null value for a table column. It does not have identical values. There are some tables whose foreign keys refer to unique constraints.
60) What is Static SQL?
Static SQL is the SQL (Structure query language) statements embedded or hardcoded in the application, and they do not change at runtime. The process for data access is predetermined, hence swifter and more efficient.
61) What is Dynamic SQL?
In a Dynamic SQL database, SQL statements are generated by the application at runtime, where the user is empowered to create queries. You can build your query at runtime. It is slower than static SQL as the query is compiled at runtime.
62) What is Relational Calculus?
Relational Calculus is a non-procedural query language. It uses mathematical predicate calculus. Therefore, its main focus is on what to do with the relations (tables) rather than how to do it.
63) What is BI?
Business Intelligence refers to processes, architectures, and technologies that transform raw data into meaningful information. As a result, profitable business actions are driven. It is a software suite to transform data into actionable intelligence and knowledge.
64) What is Network Model in DBMS?
The Network Model helps each child to have multiple parents. You can use it to model more complex relationships, such as the many-to-many relationship between orders and parts. In this DBMS model, entities are organized in a graph that can be accessed through several paths.
65) What are Temporary Tables? When are they useful?
Temporary tables exist for a particular session, or their data persists throughout the transaction. These tables are commonly used to support specialized rollups and specific application processing requirements. Initially, no space is allocated to a temporary table, but as rows are inserted, space will be dynamically allocated.
