DBMS

What is a table in a relational database?

In a relational database, a table is a collection of data organized into rows and columns. Each column represents a specific data type, and each row represents a record or instance of data.

What is a primary key in a database?

A primary key is a column or combination of columns in a table that uniquely identifies each row in the table. It is used to enforce data integrity and ensure that each record is uniquely identifiable.

What is a query in a database?

A query is a request for data from a database, typically written in SQL. Queries can retrieve data from one or more tables, filter and sort the data, and perform calculations or other operations on the data.

What is relational database?

A relational database is a database that is based on the relational model, which organizes data into tables with relationships defined by keys and foreign keys. This enables data to be efficiently linked and queried across multiple tables.

What is a schema in a database?

A schema is a logical container for database objects such as tables, views, and stored procedures. It provides a way to organize database objects and define access control and ownership.

What is a NoSQL database?

A NoSQL database is a type of database management system that uses non-relational data models and is designed to handle unstructured or semi-structured data. Unlike relational databases, NoSQL databases do not use tables with fixed columns and rows, and they often use different query languages than SQL.

What is cloud database?

A cloud database is a type of database that is hosted in a cloud computing environment, typically provided as a service by a cloud provider such as Amazon Web Services or Microsoft Azure. Cloud databases can provide scalability, availability, and cost savings compared to traditional on-premise databases.

What is a data dictionary?

A data dictionary is a collection of metadata that provides information about the structure and contents of a database. It includes descriptions of tables, columns, relationships, constraints, and other database objects.

What is a data warehouse? How it works, in brief?

A data warehouse is a large, centralized repository of data that is used for analysis and reporting. It is typically designed to support complex queries and data analysis, and may incorporate data from multiple sources or systems. The purpose of a data warehouse is to provide a comprehensive view of an organization's data, enabling insights and decision-making based on trends, patterns, and historical data. WORKING PROCESS: It does this by storing and organizing data in a way that is optimized for querying and reporting, and by supporting complex analytical queries that can span multiple databases or data sources. In order to achieve this, a data warehouse may incorporate data from multiple databases, data sources, and applications. This data is typically transformed and consolidated into a common format, and may undergo various cleaning, filtering, and aggregation processes in order to improve data quality and relevance.

What is a database index?

A database index is a data structure that is used to improve the performance of database queries by providing quick access to specific data values. In a database, an index is a copy of selected columns of data from a table that is stored separately from the original table. The index contains a sorted list of values from the selected columns along with a pointer to the corresponding row in the table. This enables the database to quickly locate specific rows of data that match a given query condition, without having to scan the entire table. For example, if a database contains a table of customer information with columns for name, address, and phone number, an index might be created on the phone number column. This would allow the database to quickly locate all customers with a given phone number, without having to scan the entire table. Indexes can significantly improve the performance of database queries, especially for large tables or tables that are frequently queried.

What is a database management system (DBMS)

A database management system is a software system that is used to manage structured data, typically stored in tables. It provides a set of tools and utilities to create, modify, and query relational databases. However, there are also database systems designed for managing unstructured or semi-structured data, such as document-oriented databases or graph databases

What is a database transaction?

A database transaction is a series of operations that are performed as a single unit of work in a database management system.

What are database views? How are they created?

A database view is a virtual table in a database that is derived from one or more tables in the database. A view does not actually store any data on its own, but rather provides a way to see data from multiple tables in a single, logical structure. A view is created by defining a SQL query that selects and combines data from one or more tables in the database. Once the view is created, it can be used like a regular table, with data being retrieved, updated, or deleted as needed.

What is a distributed database?

A distributed database is a database that is spread across multiple computers or servers. It enables data to be stored and accessed from multiple locations, and can improve performance and availability by distributing data processing and storage.

What are the uses of database transaction?

A transaction is used to ensure data consistency and integrity by either committing or rolling back the entire series of operations, depending on whether any errors or exceptions occur. In a transaction, a series of database operations are grouped together and treated as a single unit of work. The transaction is initiated by a request to the database, such as a query or an update, and it ends with either a commit or a rollback operation. If all of the operations within the transaction are completed successfully, the transaction is committed, and the changes made to the database become permanent. However, if any of the operations within the transaction fail, the transaction is rolled back, and the database is restored to its previous state before the transaction began. This ensures that the database remains consistent and that all changes are made correctly. Database transactions are critical for ensuring the accuracy and consistency of data within a database. They are commonly used in applications that require multiple database operations to be performed as a single unit, such as online transaction processing (OLTP) systems, financial systems, and e-commerce applications.

where does the changes made to the view stored?

A view is a virtual table that is created by defining a SQL query that selects and combines data from one or more tables in the database. Once the view is created, it can be used like a regular table, with data being retrieved, updated, or deleted as needed. However, any changes made to the view are actually made to the underlying tables that the view is based on.

What is ACID transaction?

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These are properties that are considered essential for ensuring data integrity and consistency in database transactions. Atomicity: Atomicity ensures that a transaction is treated as a single, indivisible unit of work. This means that either all of the changes made by the transaction are committed to the database, or none of them are. If any part of the transaction fails, the entire transaction is rolled back to its original state. Consistency: Consistency ensures that a transaction leaves the database in a consistent state. This means that any data that is written to the database must meet all of the integrity constraints and business rules that have been defined for the database. Isolation: Isolation ensures that transactions are executed in isolation from each other. This means that one transaction cannot access or modify data that is being modified by another transaction, and changes made by one transaction are not visible to other transactions until they are committed. Durability: Durability ensures that once a transaction is committed, the changes made by the transaction are permanent and can survive system failures. This means that the changes are written to non-volatile storage, such as a hard drive, and can be recovered even if the system crashes or loses power.

What is normalization in a database?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships between them using keys and foreign keys. The goal of normalization is to eliminate duplicate data and to ensure that each piece of data is stored in only one place. This improves data consistency, accuracy, and reduces the risk of data inconsistencies that can arise when the same data is stored in multiple places. Normalization is typically achieved through a series of steps called normal forms. There are several normal forms, with each subsequent normal form building on the previous one. The most commonly used normal forms are: First Normal Form (1NF): Each column in a table must have atomic values, meaning that it cannot be further subdivided into smaller values. Each row must also be uniquely identifiable, usually through a primary key. Second Normal Form (2NF): All non-key attributes (columns) in a table must be functionally dependent on the primary key. This means that each non-key attribute should be related to the primary key, and not to any other non-key attributes. Third Normal Form (3NF): All non-key attributes in a table must be functionally dependent only on the primary key. This means that each non-key attribute should be related only to the primary key, and not to any other non-key attributes. Normalization helps to avoid anomalies, such as insertion, deletion, and update anomalies that can occur when data is not properly organized in a database. By using normalization techniques, databases can be optimized for efficiency, scalability, and maintainability.

What is SQL?

SQL (Structured Query Language) is a programming language used to communicate with relational database management systems (RDBMS) and perform operations such as data retrieval, modification, and deletion.

What is the downside of database index?

They take up additional storage space and can slow down insert, update, and delete operations, as the database must update the index as well as the table itself. It is important to carefully consider which columns to index and how many indexes to create, balancing the benefits of improved query performance against the costs of increased storage and slower write operations.