Interview Q&A

MongoDB provides multi-document transactions starting with version 4.0. This allows you

to execute multiple operations in a transaction, ensuring ACID properties (Atomicity,

Consistency, Isolation, Durability) across multiple documents and collections.

Example:

const session = client.startSession();

try {

session.startTransaction();

db.collection1.updateOne({ _id: 1 }, { $set: { status: "completed"

} }, { session });

db.collection2.updateOne({ _id: 2 }, { $set: { status: "shipped" }

}, { session });

session.commitTransaction();

} catch (error) {

session.abortTransaction();

} finally {

session.endSession();

}

ccessed in the database. Since MongoDB is schema-less, data modeling involves deciding

how to structure documents, collections, and their relationships (embedded vs. referenced

documents) to balance performance, scalability, and ease of querying.

Data modeling in MongoDB refers to the design of how data will be stored, organized, and

accessed in the database. Since MongoDB is schema-less, data modeling involves deciding

how to structure documents, collections, and their relationships (embedded vs. referenced

documents) to balance performance, scalability, and ease of querying.

In MongoDB, relationships can be handled in two ways:

• Embedded documents: Storing related data within a single document (best for

one-to-many relationships).

Example: A blog post with comments as an embedded array.

• Referenced documents: Storing related data in separate collections and using

references (best for many-to-many or large datasets).

Example: A customer document may reference an order document by storing the

order’s _id in the customer document.

document schema defines the structure, rules, and validations for documents in a

collection. MongoDB is schema-less by default, but you can enforce schema validation rules

using MongoDB schema validation.

Example:

db.createCollection("users", {

validator: {

$jsonSchema: {

bsonType: "object",

required: ["name", "email"],

properties: {

name: { bsonType: "string" },

email: { bsonType: "string" }

}
}
}

});

Data validation in MongoDB can be implemented using JSON Schema or custom validation

rules to ensure data consistency.

Example (using JSON Schema):

db.createCollection("products", {

validator: {

$jsonSchema: {

bsonType: "object",

required: ["product_name", "price"],

properties: {

product_name: { bsonType: "string" },

price: { bsonType: "double", minimum: 0 }

}
}
}

});

MongoDB Atlas is a fully-managed cloud database service provided by MongoDB Inc. It

offers:

• Automated backups, scaling, and updates.
• Multi-cloud support (AWS, GCP, Azure).
• Advanced security features like encryption and IP whitelisting.
• Monitoring and performance optimization tools.
• Global replication across different regions.

• Indexing: Create appropriate indexes to optimize query performance.
• Sharding: Distribute large datasets across multiple servers.
• Caching: Cache frequently accessed data.
• Aggregation optimization: Use $match early in the aggregation pipeline.
• Avoid joins: MongoDB performs best when data is denormalized or embedded.

Database Security

Database security refers to the practices, tools, and measures used to protect a database

from unauthorized access, misuse, modification, or destruction of its data. It ensures the

confidentiality, integrity, and availability (often referred to as the CIA triad) of the data stored

within the database.

Why it's important:

• Confidentiality: Protects sensitive data from unauthorized access (e.g., personal

information, financial data).

• Integrity: Ensures data remains accurate, consistent, and unaltered by unauthorized

users.

• Availability: Ensures data is available when needed and prevents disruptions from

threats like ransomware or DDoS attacks.

ccess controls.

• Privilege Escalation: Attackers gaining elevated permissions or access levels.
• Malicious Insiders: Employees or authorized users intentionally or unintentionally

leaking or modifying data.

• Denial of Service (DoS): Attackers overwhelming the database with requests,

making it unavailable to legitimate users.

• Backup Exposure: Unencrypted or improperly secured backup files that are

ccessible to unauthorized users.

Common database security risks include:

• SQL Injection: Malicious queries that can compromise the database if the input is

not properly validated.

• Unauthorized Access: Users gaining access to sensitive data without permission.
• Data Breaches: Exposing sensitive data due to insufficient encryption or weak

access controls.

• Privilege Escalation: Attackers gaining elevated permissions or access levels.
• Malicious Insiders: Employees or authorized users intentionally or unintentionally

leaking or modifying data.

• Denial of Service (DoS): Attackers overwhelming the database with requests,

making it unavailable to legitimate users.

• Backup Exposure: Unencrypted or improperly secured backup files that are

accessible to unauthorized users.

s data, not executable code.

• - Example in MySQL (using PDO in PHP)

$stmt = $pdo->prepare('SELECT * FROM users WHERE username =

:username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

• ● Input Validation: Always validate user input by checking for expected data types,

lengths, and ranges.

• Escaping User Input: If parameters cannot be parameterized, make sure all user

input is properly escaped.

• Least Privilege Principle: Limit database user permissions to only those necessary

to perform their tasks.

• Web Application Firewalls (WAF): Use WAFs to detect and block SQL injection

ttacks.

SQL Injection is a type of attack where an attacker inserts or manipulates malicious SQL

code into a query, which can compromise the database. It usually happens when user input

is improperly sanitized or validated.

Prevention techniques:

Use Prepared Statements/Parameterized Queries: This ensures that user input is treated

as data, not executable code.

• - Example in MySQL (using PDO in PHP)

$stmt = $pdo->prepare('SELECT * FROM users WHERE username =

:username AND password = :password');

$stmt->execute(['username' => $username, 'password' => $password]);

• Input Validation: Always validate user input by checking for expected data types,

lengths, and ranges.

• Escaping User Input: If parameters cannot be parameterized, make sure all user

input is properly escaped.

• Least Privilege Principle: Limit database user permissions to only those necessary

to perform their tasks.

• Web Application Firewalls (WAF): Use WAFs to detect and block SQL injection

attacks.

Answer: uthorized users based on roles. Each role defines a set of permissions (what actions can be performed on which database objects). Steps to implement RBAC:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: Role-Based Access Control (RBAC) is a method for restricting system access to authorized users based on roles. Each role defines a set of permissions (what actions can be performed on which database objects). Steps to implement RBAC:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

nd SQL statements in precompiled code, making it harder for attackers to inject malicious

code.

Roles in database security:

• Input Validation: Stored procedures allow you to validate user inputs at the

database level, preventing malicious input from being executed.

• Preventing Direct Access: By using stored procedures, users can be given

permissions to execute specific procedures rather than direct access to the

underlying tables.

• Encapsulation of Business Logic: The logic within stored procedures is not visible

to the end-user, reducing the attack surface.

• Audit Logging: Stored procedures can include logic to log user activity for auditing

nd compliance purposes.

Example: Instead of allowing users to execute arbitrary INSERT or UPDATE statements, you

can give them permission to execute a specific stored procedure that does the necessary

validation and modification of data.

CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL)

BEGIN

IF new_salary > 0 THEN
UPDATE employees SET salary = new_salary WHERE id = emp_id;

END IF;

END;

Stored Procedures can help improve database security by encapsulating business logic

and SQL statements in precompiled code, making it harder for attackers to inject malicious

code.

Roles in database security:

• Input Validation: Stored procedures allow you to validate user inputs at the

database level, preventing malicious input from being executed.

• Preventing Direct Access: By using stored procedures, users can be given

permissions to execute specific procedures rather than direct access to the

underlying tables.

• Encapsulation of Business Logic: The logic within stored procedures is not visible

to the end-user, reducing the attack surface.

• Audit Logging: Stored procedures can include logic to log user activity for auditing

and compliance purposes.

Example: Instead of allowing users to execute arbitrary INSERT or UPDATE statements, you

can give them permission to execute a specific stored procedure that does the necessary

validation and modification of data.

CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL)

BEGIN

IF new_salary > 0 THEN

UPDATE employees SET salary = new_salary WHERE id = emp_id;

END IF;

END;

LTER DATABASE MyDatabase SET ENCRYPTION ON;

• Column-Level Encryption: For encrypting specific columns.

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate';

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE MyCertificate;

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE

MyCertificate;

• PostgreSQL:

pgcrypto extension provides functions for encrypting data.

SELECT pgp_sym_encrypt('Sensitive Data', 'encryption_key');

• MySQL:

Encryption Functions: MySQL provides functions like AES_ENCRYPT and AES_DECRYPT.

SELECT AES_ENCRYPT('Sensitive Data', 'encryption_key');

SELECT AES_DECRYPT(encrypted_data, 'encryption_key');

• For all databases, consider using SSL/TLS for encrypting data in transit.

Encryption is essential for protecting sensitive data both at rest (on disk) and in transit

(during communication).

SQL Server:

Transparent Data Encryption (TDE): Encrypts data at rest without requiring application

changes.

CREATE DATABASE ENCRYPTION KEY;

ALTER DATABASE MyDatabase SET ENCRYPTION ON;

Column-Level Encryption: For encrypting specific columns.

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate';

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE MyCertificate;

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE

MyCertificate;

PostgreSQL:

pgcrypto extension provides functions for encrypting data.

SELECT pgp_sym_encrypt('Sensitive Data', 'encryption_key');

MySQL:

Encryption Functions: MySQL provides functions like AES_ENCRYPT and AES_DECRYPT.

SELECT AES_ENCRYPT('Sensitive Data', 'encryption_key');

SELECT AES_DECRYPT(encrypted_data, 'encryption_key');

For all databases, consider using SSL/TLS for encrypting data in transit.

Answer: Backup and restore security involves ensuring that backup files are stored securely and that access to backup data is tightly controlled. Steps for Secure Backup:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

There are three primary types of database backups:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

SQL Server:

To back up a database in SQL Server, you can use the BACKUP DATABASE command.

• - Full Backup

BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak';

• - Transaction Log Backup

BACKUP LOG MyDatabase TO DISK = 'C:\Backups\MyDatabase_log.trn';

PostgreSQL:

In PostgreSQL, you can use the pg_dump command for backing up the database.

• - Full Backup

pg_dump mydatabase > /path/to/backup/mydatabase.sql

For backing up with compression or other options:

pg_dump -Fc mydatabase > /path/to/backup/mydatabase.dump

For transactional backups, you can use WAL (Write-Ahead Logging) archiving, typically

managed through archive_mode and archive_command settings in the

postgresql.conf.

MySQL:

In MySQL, you can use the mysqldump command for backing up the database.

• - Full Backup

mysqldump -u username -p mydatabase > /path/to/backup/mydatabase.sql

For binary logging (transaction logs), MySQL uses binlog:

• - Enabling binary log

[mysqld]

log-bin=mysql-bin

SQL Server:

To restore a full backup in SQL Server:

• - Full Restore

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

• - Transaction Log Restore

RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn';

To restore a database to a specific point in time (using point-in-time recovery), you would

restore the full backup and apply transaction logs.

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn'

WITH STOPAT = '2023-09-14T15:30:00'; -- Restore to a specific time

PostgreSQL:

To restore a backup from a pg_dump:

• - Full Restore from SQL dump

psql -U username -d mydatabase < /path/to/backup/mydatabase.sql

For binary dump (pg_dump -Fc):

pg_restore -U username -d mydatabase /path/to/backup/mydatabase.dump

MySQL:

To restore a MySQL database:

• - Full Restore from SQL dump

mysql -u username -p mydatabase < /path/to/backup/mydatabase.sql

For restoring binary logs:

mysqlbinlog /path/to/binlog/mysql-bin.000001 | mysql -u username -p

fter the full backup until the specified point in time.

SQL Server Example:

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn'

WITH STOPAT = '2023-09-14T15:30:00'; -- Restore up to a specific

time

PostgreSQL Example:

To enable point-in-time recovery, you need to restore a base backup, then use WAL

(Write-Ahead Logging) archives to apply changes until the desired point.

• You would also set the restore_command in recovery.conf and specify the

recovery_target_time.

Point-in-time recovery (PITR) allows you to restore a database to a specific moment in

time, which can be crucial in scenarios where:

• Data corruption or accidental deletion occurs.
• You want to rollback to a specific moment before a harmful event.

How it works:

• First, a full backup is restored.
• Then, transaction log backups are applied, allowing you to replay changes made

after the full backup until the specified point in time.

SQL Server Example:

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn'

WITH STOPAT = '2023-09-14T15:30:00'; -- Restore up to a specific

time

PostgreSQL Example:

To enable point-in-time recovery, you need to restore a base backup, then use WAL

(Write-Ahead Logging) archives to apply changes until the desired point.

• You would also set the restore_command in recovery.conf and specify the

recovery_target_time.