Database Fundamentals Interview Questions

A database is an organized collection of structured information or data, typically stored electronically in a computer system. It allows efficient data storage, retrieval, management, and analysis, enabling businesses and organizations to manage large amounts of information systematically.

A database is the actual collection of data, while a Database Management System (DBMS) is the software that allows users to define, create, maintain, and control access to the database. DBMS provides an interface between the database and its end-users or application programs.

The main database models include: Relational (SQL), Hierarchical, Network, Object-Oriented, Document, Key-Value, and Graph databases. Each model has unique characteristics and is suited to different types of data storage and retrieval requirements.

Database normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves breaking down tables into smaller, more focused tables and defining relationships between them to minimize data duplication and potential anomalies.

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures transactions are completed entirely or not at all, Consistency maintains database integrity, Isolation prevents interference between concurrent transactions, and Durability guarantees that completed transactions are permanently recorded.

A primary key is a column or combination of columns that uniquely identifies each row in a database table. It ensures that no two rows have the same identifier and provides a way to establish relationships between tables.

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It creates a relationship between tables by referencing the primary key of another table, ensuring referential integrity.

The main types of database relationships are: One-to-One, One-to-Many, Many-to-One, and Many-to-Many. These relationships define how data is connected and how tables interact with each other in a relational database.

A database schema is a blueprint that defines the logical structure of a database, including tables, fields, relationships, views, indexes, and other database objects. It serves as a framework for organizing and representing data in a systematic manner.

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. It ensures that data remains unchanged during storage, retrieval, and processing, and is maintained through constraints, validation rules, and database design principles.

A data warehouse is a centralized repository designed to store large volumes of structured data from multiple sources. It is optimized for query and analysis, providing historical and consolidated data for business intelligence and reporting purposes.

OLTP (Online Transaction Processing) databases are optimized for handling numerous real-time transactions, while OLAP (Online Analytical Processing) databases are designed for complex analytical queries and reporting, typically used for business intelligence.

A database index is a data structure that improves the speed of data retrieval operations on a database table. It works similar to an index in a book, allowing faster lookup of rows based on the values of one or more columns.

A database view is a virtual table based on the result of a SQL statement. It provides a way to simplify complex queries, restrict access to data, aggregate information, and present data in a specific format without storing the data physically.

Denormalization is a database optimization technique where redundant data is intentionally added to improve read performance. It involves combining normalized tables to reduce the need for complex joins and speed up data retrieval at the cost of some data redundancy.

Database constraints are rules enforced on data columns to maintain data integrity. Common types include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT constraints, which ensure data accuracy and consistency.

Data modeling is the process of creating a visual representation of a database's structure. It involves defining data elements, their relationships, and rules to support business requirements. Common data modeling techniques include conceptual, logical, and physical modeling.

A stored procedure is a precompiled collection of one or more SQL statements stored in a database. It can be reused and called multiple times, accepts input parameters, performs operations, and can return results, providing a way to encapsulate complex database logic.

Database triggers are special stored procedures automatically executed when a specific event occurs in the database, such as INSERT, UPDATE, or DELETE operations. They are used to maintain data integrity, enforce business rules, and automatically perform actions in response to data changes.

A database cursor is a database object that allows traversal and manipulation of database records. It acts like a pointer to a specific row in a result set, enabling row-by-row processing of query results and supporting operations that require sequential data access.

Database partitioning is a technique of dividing large tables into smaller, more manageable pieces called partitions. Each partition can be managed and accessed separately, improving query performance, simplifying maintenance, and enabling more efficient data management.

A database transaction is a sequence of database operations that are treated as a single unit of work. It must be completed entirely or not at all, ensuring data consistency. Transactions follow the ACID properties and are crucial for maintaining database reliability.

Database keys include Primary Key (uniquely identifies a record), Foreign Key (links tables together), Candidate Key (potential primary key), Alternate Key (secondary unique identifier), and Composite Key (combination of multiple columns used as a key).

Database replication is the process of creating and maintaining multiple copies of a database across different servers. It improves data availability, provides load balancing, enables disaster recovery, and ensures data consistency through various replication strategies.

Database sharding is a horizontal partitioning technique that splits large databases into smaller, more manageable pieces called shards. Each shard contains a subset of the data, distributed across multiple servers to improve performance, scalability, and manageability.

The main components of a relational database include tables, rows, columns, keys, indexes, views, stored procedures, and relationships. These elements work together to organize, store, and manage structured data efficiently.

A data dictionary is a centralized repository of information about data, such as its meaning, relationships to other data, origin, usage, and format. It provides metadata about database objects, helping users and administrators understand the structure and semantics of the database.