Interview Q&A

nother table. This prevents invalid references from being made.

• Enforcing Referencing Integrity:
• Use foreign keys to create relationships between tables.
• Enforce actions like ON DELETE CASCADE, ON UPDATE CASCADE, or

ON DELETE RESTRICT to define how changes in parent tables affect related

child tables.

Example:

• A Foreign Key in the Orders table references the CustomerID in the Customers

table. If you try to delete a customer that has associated orders, referential integrity

will prevent the delete unless actions like CASCADE are specified.

Referential integrity ensures that relationships between tables are consistent. Specifically,

it ensures that a foreign key in one table must match a primary key or a unique key in

another table. This prevents invalid references from being made.

• Enforcing Referencing Integrity:
• Use foreign keys to create relationships between tables.
• Enforce actions like ON DELETE CASCADE, ON UPDATE CASCADE, or

ON DELETE RESTRICT to define how changes in parent tables affect related

child tables.

Example:

• A Foreign Key in the Orders table references the CustomerID in the Customers

table. If you try to delete a customer that has associated orders, referential integrity

will prevent the delete unless actions like CASCADE are specified.

Answer: Data redundancy occurs when the same piece of data is stored in multiple places, which can lead to inconsistencies and wasted storage. To avoid redundancy:

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.

composite key is a combination of two or more columns in a table that can uniquely

identify each record in the table. It is used when a single column is not sufficient to uniquely

identify a row.

• Composite Key: Consists of multiple columns.

Example: A table storing student-course enrollments might use a composite key

consisting of StudentID and CourseID to uniquely identify each enrollment record.

• Primary Key: A primary key is a single column (or a composite of columns) that

uniquely identifies each record in a table. It can be a single column or a composite

of multiple columns, but it must be unique for each row.

How does it differ from a primary key?

A composite key is a combination of two or more columns in a table that can uniquely

identify each record in the table. It is used when a single column is not sufficient to uniquely

identify a row.

• Composite Key: Consists of multiple columns.

Example: A table storing student-course enrollments might use a composite key

consisting of StudentID and CourseID to uniquely identify each enrollment record.

• Primary Key: A primary key is a single column (or a composite of columns) that

uniquely identifies each record in a table. It can be a single column or a composite

of multiple columns, but it must be unique for each row.

n Entity-Relationship Diagram (ERD) is a graphical representation of entities and their

relationships to each other within a database. It is used to visualize the structure of a

database and how tables (entities) relate to one another.

• Entities are represented as rectangles.
• Attributes are shown as ovals connected to their entities.
• Relationships are represented as diamonds, showing how entities interact.

ERDs help in the conceptual design phase of a database, outlining entities, relationships,

nd keys before implementation.

surrogate key is an artificial, system-generated key used to uniquely identify a record in a

table. It has no business meaning and is typically a number (e.g., auto-incremented ID).

• Surrogate Key:
• No business meaning.
• Commonly used in data warehousing and large-scale systems.
• Example: A column UserID that is generated automatically.
• Natural Key: A key that has real-world business meaning and is used to uniquely

identify records.

• Example: SocialSecurityNumber or EmailAddress could serve as

natural keys.

How is it different from a natural key?

A surrogate key is an artificial, system-generated key used to uniquely identify a record in a

table. It has no business meaning and is typically a number (e.g., auto-incremented ID).

• Surrogate Key:
• No business meaning.
• Commonly used in data warehousing and large-scale systems.
• Example: A column UserID that is generated automatically.
• Natural Key: A key that has real-world business meaning and is used to uniquely

identify records.

• Example: SocialSecurityNumber or EmailAddress could serve as

natural keys.

Answer: pplication? Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

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: Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

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.

MongoDB is a NoSQL document-oriented database that stores data in flexible,

semi-structured documents rather than in rows and columns like SQL databases. Unlike

SQL databases, which rely on rigid schemas, MongoDB uses a schema-less approach

where each document can have different fields and structures.

Key differences:

• SQL Databases: Store data in tables with a fixed schema and use ACID

transactions for consistency.

• MongoDB: Uses a document model (e.g., JSON-like structures) and offers more

flexibility, scalability, and ease of replication but may sacrifice some consistency in

distributed environments.

dvantages:

• Scalability: MongoDB scales horizontally through sharding, which can handle large

datasets across distributed clusters.

• Flexibility: Schema-less design allows for dynamic data models and easier iteration

during development.

• Performance: It can perform high-throughput reads and writes for large datasets.
• High Availability: Through replica sets, MongoDB ensures data availability and fault

tolerance.

Disadvantages:

• Consistency: By default, MongoDB uses eventual consistency, which may not be

suitable for all applications (though it supports ACID transactions in some cases).

• Complexity in Transactions: While MongoDB now supports multi-document

transactions, working with them is more complex compared to SQL.

• Data Duplication: The flexibility can sometimes lead to data duplication and

inconsistency if not properly managed.

document in MongoDB is a record represented as a JSON-like object (BSON format)

that contains field-value pairs. It can have a flexible structure, meaning each document in a

collection may contain different fields and data types.

Differences from SQL Rows:

• In SQL, a row is a fixed set of columns (defined by the schema), while in MongoDB,

document is more flexible and can vary in structure.

• A row in SQL is constrained by a schema, whereas a document in MongoDB can

store complex, nested data structures (arrays, sub-documents).

How does it differ from rows in

SQL databases?

A document in MongoDB is a record represented as a JSON-like object (BSON format)

that contains field-value pairs. It can have a flexible structure, meaning each document in a

collection may contain different fields and data types.

Differences from SQL Rows:

• In SQL, a row is a fixed set of columns (defined by the schema), while in MongoDB,

a document is more flexible and can vary in structure.

• A row in SQL is constrained by a schema, whereas a document in MongoDB can

store complex, nested data structures (arrays, sub-documents).

Answer: Database: A container for collections. MongoDB allows you to have multiple databases within the same instance. Collection: A group of MongoDB documents. Collections are similar to tables in SQL but don’t have a fixed schema.

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.

Create:

db.collection.insertOne({ name: "Alice", age: 25 });

db.collection.insertMany([{ name: "Bob", age: 30 }, { name:

"Charlie", age: 35 }]);

• Read:

db.collection.find({ age: { $gt: 30 } });

db.collection.findOne({ name: "Alice" });

• Update:

db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } });

db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status:

"young" } });

• Delete:

db.collection.deleteOne({ name: "Alice" });

db.collection.deleteMany({ age: { $lt: 30 } });

mount of data the system needs to scan. MongoDB creates indexes on fields that are

queried frequently.

Differences from SQL indexes:

• MongoDB uses B-tree indexes (by default) but also supports other types like

hashed indexes, geospatial indexes, and text indexes.

• SQL indexes are typically built on a fixed schema and are more rigid, while MongoDB

indexes can be dynamic, allowing indexing on any field within a document.

• MongoDB supports compound indexes and array indexes for more complex

queries.

How is it different from SQL indexes?

Indexing in MongoDB is a mechanism to improve query performance by reducing the

amount of data the system needs to scan. MongoDB creates indexes on fields that are

queried frequently.

Differences from SQL indexes:

• MongoDB uses B-tree indexes (by default) but also supports other types like

hashed indexes, geospatial indexes, and text indexes.

• SQL indexes are typically built on a fixed schema and are more rigid, while MongoDB

indexes can be dynamic, allowing indexing on any field within a document.

• MongoDB supports compound indexes and array indexes for more complex

queries.

The $in operator is used to match values in a specified array. It checks if the value of a field

is equal to any value in the provided array.

Example:

db.collection.find({ age: { $in: [25, 30, 35] } });

This query finds all documents where the age field is 25, 30, or 35.

utomatically.

• Sharded Cluster: Used for horizontal scaling. It splits data across multiple servers

(shards) based on a shard key. Each shard holds a portion of the data, and a

mongos router directs queries to the correct shard. Sharding helps manage large

datasets by distributing the load.

• Replica Set: A group of MongoDB servers that maintain the same data. It provides

high availability and data redundancy by replicating data across multiple servers. If

the primary server goes down, one of the secondaries can be promoted to primary

automatically.

• Sharded Cluster: Used for horizontal scaling. It splits data across multiple servers

(shards) based on a shard key. Each shard holds a portion of the data, and a

mongos router directs queries to the correct shard. Sharding helps manage large

datasets by distributing the load.

find(): Returns a cursor to all documents that match the query. You can iterate over the

results using .forEach() or convert them to an array with .toArray().

Example:

db.collection.find({ age: { $gt: 30 } });

• findOne(): Returns the first document that matches the query, or null if no document is

found.

Example:

db.collection.findOne({ name: "Alice" });

Answer: nd count occurrences { $sort: { count: -1 } } // Sort by count in descending order ]);

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.

How do you use

it?

The Aggregation Framework in MongoDB is used to perform complex data processing

tasks such as grouping, filtering, sorting, and reshaping documents.

It uses a pipeline of stages to process documents:

Example:

db.collection.aggregate([

{ $match: { status: "active" } }, // Filter documents

{ $group: { _id: "$age", count: { $sum: 1 } } }, // Group by age

and count occurrences

{ $sort: { count: -1 } } // Sort by count in descending order

]);

Answer: You can use the updateOne(), updateMany(), or replaceOne() methods to update data. Example: db.collection.updateOne({ name: "Alice" }, { $set: { age: 27 } }); $set updates the value of a field. $inc increments a field's value.

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.