Interview Question for Database

The key steps include understanding the requirements, creating an ER diagram, designing the schema (tables, fields), and defining relationships. After designing, you normalize the data to avoid redundancy and ensure consistency.

An ER (Entity-Relationship) diagram is a visual representation of the database structure. It shows entities (like tables), their attributes (columns), and relationships (how tables connect), helping in designing the database efficiently.

In a database, entities stand in for actual things or ideas (such as “Customer” or “Order”). “Customer Name” and “Order Date” are examples of attributes, which are characteristics of entities that describe specifics about those entities.

Logical design focuses on what the data is and how it’s related, using diagrams and tables. Physical design focuses on how the data is stored in the database (e.g., file storage, indexes) for efficient access.

A junction table handles many-to-many relationships. This table links two tables by holding foreign keys from both, creating a relationship between them without data duplication.

Normalization reduces data duplication and ensures data consistency. It organizes the database into smaller, related tables, which makes it easier to maintain and reduces the chance of errors.

Denormalization is combining related tables into one to improve query performance. It’s used when the database has too many joins, which can slow down queries, but it may introduce some data redundancy.

The validity of values in one table must match those in another, and this is ensured by a foreign key. This helps maintain referential integrity, meaning related data across tables stays consistent and accurate.

Constraints (like Primary Key, Foreign Key, Unique, and Check) enforce rules on data in a table, ensuring the data is valid. They help prevent invalid or duplicate data from being inserted.

While partitioning breaks up enormous tables into smaller, more manageable sections, indexing creates pointers to data, which speeds up data retrieval. Both techniques improve the performance of large databases during searches.

SELECT, INSERT, UPDATE, and DELETE are the fundamental SQL commands for manipulating data. They are used to get data, add new records, update existing records, and remove records. These commands allow users to perform essential operations on database tables.

Rows from two or more tables are combined using an SQL JOIN based on a relevant column. The primary JOIN kinds are:

• INNER JOIN:  An inner join returns only matching rows from both tables.
• LEFT JOIN: A left-join returns all rows from the left table along with any matching rows from the right table. 
• RIGHT JOIN: Returns all matching rows from the right table and all matching rows from the left table.
• FULL JOIN: When there is a match in either table, it returns all rows.

Query optimization involves improving the performance of SQL queries to ensure they execute faster. This includes analyzing the query structure, using appropriate indexes, and minimizing data retrieval by filtering unnecessary rows.

Indexing improves query performance by creating a quick lookup for data retrieval. When a query searches for specific values, the database can use the index instead of scanning the entire table, which speeds up data access significantly.

Whereas the HAVING clause filters records after grouping, the WHERE clause filters records before to grouping. This means you can use WHERE to limit the data being processed and HAVING to limit the results of aggregate functions.

• Subquery: A subquery is a query (or question) inside another query. It lets you get data based on results from the first query. For example, you might want to find all customers who made purchases over a certain amount, and you’d use a subquery to first find those amounts.

• Join: A join, on the other hand, combines data from two or more tables based on a related column. For instance, you might join a table of customers with a table of their orders to see which orders each customer made.

You can: optimize complex SQL queries

• Use indexes on columns frequently searched or joined.
• Limit the number of rows returned using WHERE clauses.
• Avoid SELECT and specify only the needed columns.
• Break complex queries into simpler parts when possible.

Duplicate rows are eliminated, and the results of two or more SELECT queries are combined using UNION. The results are combined using UNION ALL, which includes all duplicates. For unique results, use UNION; for whole datasets without filtering, use UNION ALL.

Views are virtual tables created by SQL queries that simplify complex queries for users. They can enhance efficiency by storing complex joins or aggregations, allowing users to retrieve results without rewriting the underlying SQL each time.

A query execution plan is a detailed outline that shows how a database will execute a specific query to retrieve results. It specifies the database’s steps, including which tables to access, how to filter the data, and how to join the tables.

To analyze a query execution plan, you look at the plan to see if any steps might take a long time or use too many resources. You want to check for things like:

• Slow operations: Some steps might be slower than others.
• Indexes: These help speed up searches. If there are none, the query might be slow.
• Table scans: This is called a table scan, which refers to the database scanning every row in a table, which can lead to inefficiency.

Common challenges include identifying the correct dependencies among data, managing complex relationships, and balancing the need for normalization with performance considerations. Over-normalization can lead to excessive joins and slower query performance.

Normalization aims to reduce redundancy by organizing data into related tables. By eliminating duplicate data, normalization ensures that updates, deletions, and insertions maintain data consistency across the database.

Denormalization can improve read performance by speeding up data retrieval through fewer joins required in queries. However, it may also lead to increased data redundancy, which can complicate data updates and maintenance.

To increase query efficiency, normalized data can be combined into larger tables through a process called denormalization. It should be applied when read operations are frequent and the cost of joining multiple tables outweighs the benefits of maintaining a fully normalized structure.

Normalization improves database design by organizing data into structured tables that minimize redundancy. This leads to less data duplication, easier maintenance, and more efficient data retrieval, ultimately enhancing overall database performance.

A more advanced variant of 3NF called BCNF deals with specific abnormalities that 3NF does not cover. It requires that the left side of every functional dependency be a superkey. This further reduces redundancy and ensures better data integrity.

The advantages of 3NF include reduced data redundancy and improved data integrity. 3NF helps preserve consistency and streamlines data management by making sure that non-key attributes don’t depend on other non-key attributes.

Second Normal Form (2NF) differs from First Normal Form (1NF) by not only requiring that a table has atomic values and no repeating groups but also ensuring that all non-key attributes are fully dependent on the entire primary key. In contrast, 1NF does not address partial dependencies, which can lead to data redundancy.

In database normalization, different “normal forms” are rules that help organize data in a structured way. Here are the main normal forms:

• First Normal Form (1NF): Each table has unique data in each cell, with no repeating groups or multiple values in a single cell. Every piece of information should be atomic, meaning it can’t be broken down further.

• Second Normal Form (2NF): To be in 2NF, the table must first meet the rules of 1NF. Additionally, every non-key column must be fully dependent on the primary key, meaning no partial dependency on just part of the primary key.

• Third Normal Form (3NF): In 3NF, the table must already meet 2NF requirements. Also, non-key columns should not depend on other non-key columns. Every piece of data should depend only on the primary key.

• Boyce-Codd Normal Form (BCNF): BCNF is a stricter version of 3NF. It ensures that for every functional dependency, the left side of the dependency is a super key (a unique identifier of the table).

Each normal form builds on the previous one, aiming to create a well-structured database that minimizes data duplication and potential inconsistencies.

A database table is guaranteed to be arranged in First Normal Form (1NF) if every column has a single value and no repeating groups of data. This means that every cell in the table should contain a single, indivisible piece of information and that every entry (row) in the table must be unique. Data gets more organized and simpler to handle without duplication when 1NF is followed.

Transactions in a DBMS are sequences of operations that perform a single logical task, such as transferring money or updating records. By ensuring that every transaction is correctly completed, they protect the integrity of the data.

The ACID properties of transactions ensure that database operations are processed reliably. ACID stands for:

• Atomicity: Atomicity in ACID ensures that a database transaction is treated as a single, indivisible unit. This means either the entire transaction is completed successfully or none of it is, ensuring no partial updates occur.

• Consistency: This guarantees that a transaction converts a valid database state into another. The data must continue to be correct and follow all guidelines and limitations.

• Isolation: This means that transactions run independently of each other. Even if they occur at the same time, they won’t interfere with one another, so the results are as if they were processed one after the other.

• Durability: Even in the event of a crash or power outage, the outcomes of a transaction are irreversibly stored in the database once it has been completed.

Together, these properties help maintain the integrity and reliability of the database during transactions.

The isolation level determines how transaction integrity is visible to other transactions. Higher isolation levels (like Serializable) reduce concurrency and may slow performance, while lower levels (like Read Uncommitted) allow more concurrency but can lead to issues like dirty reads.

A deadlock occurs when two or more transactions block each other, each waiting for resources held by the other. It can be prevented by using techniques like timeout, resource ordering, or deadlock detection algorithms to resolve conflicts.

A two-phase commit is a protocol used to ensure all participants in a distributed transaction either commit or rollback changes. The first phase involves preparing all participants, and the second phase involves either committing the transaction if all are ready or rolling back if any participant fails.

Assuming that there are no conflicts, optimistic concurrency control permits transactions to move forward without locking resources. Conflicts are checked before committing. Pessimistic concurrency control locks resources for a transaction, preventing others from accessing them until the transaction is complete.

Database locks to control access to data during transactions. The main types are:

• Shared Lock: Multiple transactions can read data using a shared lock, but they cannot change it.

• Exclusive Lock: Only one transaction is able to read and alter data when there is an exclusive lock.

• Intent Lock: Indicates a transaction that intends to acquire a lock on a lower-level resource.

A transaction log records all transactions and their changes to the database. It is essential for recovery, allowing the system to restore the database to a consistent state in case of failure by replaying or undoing transactions.

A COMMIT function finalizes and renders permanent all changes made during a transaction. A ROLLBACK method returns the database to its original state by rolling back every change made during the transaction.  

Using appropriate isolation levels can handle concurrency issues. For example, setting the isolation level to Serializable prevents phantom reads, while Read Committed prevents dirty reads. Additionally, using locks can help manage concurrent access to data effectively.

The different types of database backups include:

• Full Backup: A complete copy of the entire database.

• Incremental Backup: Only backs up data that has changed since the last backup.

• Differential Backup: Backs up all changes made since the last full backup.

• Transaction Log Backup: Backs up the transaction log to capture all changes made to the database.

A full backup copies the entire database, while an incremental backup only copies data that has changed since the last backup (full or incremental). Full backups take longer and require more storage, but incremental backups can save time and space.

Every modification made since the last complete backup is captured in a differential backup. Since only the most recent differential backup and the most recent complete backup are required to restore the database, this enables faster recovery than incremental backups.

To ensure database consistency after recovery, you should:

• Use transaction logs to replay changes.
• Validate the integrity of the data after restoration.
• Perform consistency checks on the database to identify and fix any issues.

Best practices for scheduling backups include:

• Regularly scheduling full backups (e.g., weekly).
• Performing incremental or differential backups daily.
• Automating backup processes to ensure consistency.
• Testing backups regularly to confirm they can be restored.

Point-in-time recovery allows you to restore the database to a specific moment by using a combination of the last full backup and transaction log backups. This is useful for recovering from accidental data loss or corruption.

Different recovery techniques include:

• Crash Recovery: Automatically recovering from a sudden failure using logs.
• Media Recovery: Restoring from backups due to hardware or media failures.
• Point-in-Time Recovery: Restoring the database to a specific moment using backups and logs.

Checkpoints are points in time when the database system saves a snapshot of the current state. They help reduce recovery time by minimizing the amount of log data that needs to be processed during recovery, as only changes made after the last checkpoint need to be applied.

Database recovery involves restoring backups and applying any necessary transaction logs to bring the database back to a consistent state. The process typically involves identifying the last good backup and replaying the logs to recover changes made since that backup.

A transaction log backup is important because:

• It captures all changes made to the database since the last backup.
• Helps restore data in case of a failure, ensuring no data loss between full backups.
• Allows point-in-time recovery, meaning you can restore the database to a specific moment.
• Prevents the transaction log from growing excessively by clearing old entries after each backup.

A data structure called an index in a database management system (DBMS) expedites the process of retrieving data from a database table. It is significant because it improves query performance overall by enabling the database to find and access the data quickly without having to search through the entire table.

In simple terms, here’s the difference between clustered and non-clustered indexes:

Clustered Index:

• It’s like the main phone book of a city.
• The data is physically sorted and stored in the same order as the index.
• There can only be one clustered index per table because the data can only be sorted one way.

Non-Clustered Index:

• It’s like an index at the back of a book.
• The index has pointers to the actual data, which is stored separately.
• There can be many non-clustered indexes in a table because they don’t affect the physical order of the data.

In short:

• Clustered index => Data is stored and sorted in the same way as the index.

• Non-clustered index => The index is separate and points to the data.

Indexing improves query performance by providing quick access paths to data.  The index helps the database find the needed rows much more quickly than it could if it had to scan the entire table, which would cut down on how long it takes to execute a query.

Because more indexes need to be updated whenever data changes, having too many indexes might result in higher storage requirements and slower writing operations (INSERT, UPDATE, and DELETE). It can also complicate maintenance and slow down performance for certain queries.

A hash index uses a hash table to provide fast lookups for equality comparisons but is not suitable for range queries. A B-tree index, on the other hand, maintains a sorted structure, allowing for both equality and range queries. B-trees are generally more versatile and widely used in databases.

• Divide large tables into smaller parts: This makes it easier to manage and query huge amounts of data by splitting big tables into smaller, more manageable chunks.
• Faster data retrieval: Queries can target specific partitions (sections) of data, reducing the time needed to search through the entire table.
• Efficient storage: Frequently accessed data can be stored on faster storage systems, while less important data can be stored on slower, cheaper storage.
• Better load balancing: Spreads out the data across multiple storage devices, which can improve system performance by balancing the load.

Indexing can negatively impact insert, update, and delete operations because the database must maintain the indexes whenever data changes. This can lead to increased overhead and slower performance for these operations, especially if there are many indexes.

A covering index is an index that includes all the columns needed by a query. It helps in query optimization by allowing the database to retrieve all necessary data directly from the index, avoiding the need to access the actual table, which improves performance.

To analyze and improve database performance, you can use tools to monitor query execution times, identify slow queries, and analyze execution plans. Tuning queries, optimizing indexes, and adjusting database configurations can also help enhance performance.

Query execution plans provide a detailed breakdown of how a database engine executes a query. They help identify potential bottlenecks, inefficiencies, and the best execution strategy, allowing developers and DBAs to optimize queries for better performance.

Data integrity in a DBMS refers to the accuracy and consistency of data over its entire lifecycle. It ensures that data is valid, reliable, and protected from unauthorized access or corruption, maintaining its quality and trustworthiness.

It is a rule that limits the values that can be entered in a column. It is used in data validation to enforce specific criteria, such as ensuring that a value falls within a certain range or matches a particular format, enhancing data integrity.

A primary key constraint ensures that each record is unique and not null, while a foreign key constraint enforces relationships between tables, ensuring that data referenced in one table exists in another. Together, they help maintain data accuracy and prevent inconsistencies.

Security risks associated with databases include unauthorized access, SQL injection attacks, data breaches, insider threats, and inadequate backup and recovery procedures. These risks can compromise sensitive data and disrupt database operations.

Encryption converts data into a coded format that authorized users can only read with the correct decryption key. It protects sensitive data at rest (stored data) and in transit (data being transmitted), ensuring confidentiality and preventing unauthorized access.

Here’s the difference between entity integrity and referential integrity:

Entity Integrity:

• Ensures that every record in a database table is unique and identifiable.
• It’s like making sure every person has a unique ID number (like a passport or Social Security number).
• In a database, each row (record) in a table must have a unique primary key, which cannot be empty (null).

Referential Integrity:

• Ensures the relationships between tables are correct and consistent.
• Think of it like a parent-child relationship. If the parent exists, the child can exist, but if the parent is removed, the child shouldn’t remain without a reference to it.
• In a database, this means that a foreign key (a key in one table that points to another table) must always point to a valid record in the related table.

User roles define the level of access and permissions that users have within a database. Permissions determine what actions users can perform, such as SELECT, INSERT, UPDATE, or DELETE. Properly managing roles and permissions helps control access and enhances security.

By inserting malicious code, SQL injection is a security flaw that enables attackers to alter SQL queries. It can be prevented by using parameterized queries, prepared statements, and input validation, ensuring that user input is safely handled and not executed as SQL code.

The GRANT command provides specific permissions to users or roles, allowing them to perform particular actions on database objects. The REVOKE command removes previously granted permissions, helping to control and manage access effectively.

Auditing involves tracking and logging database activities to monitor access and changes to data. It helps identify unauthorized access, detect anomalies, and ensure compliance with security policies, providing a record for forensic analysis if needed.