Interview Q&A

Denormalization is the process of intentionally introducing redundancy into a database by

merging tables or adding redundant data to reduce the complexity of database queries,

improve read performance, and simplify the schema.

• When to use:
• When read performance is more critical than data integrity and

normalization (e.g., in OLAP systems or reporting).

• In cases where complex JOINs are causing performance bottlenecks.
• In high-performance environments where frequently accessed data can be

stored redundantly to reduce query times.

Answer: ttributes like CourseName only depend on CourseID would violate 2NF because CourseName is partially dependent on the composite key. You would separate this into two tables (one for Courses and one for Students).

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.

• 1NF (First Normal Form): Ensures that the table has atomic columns (no

multi-valued or repeating groups) and each row has a unique identifier (Primary Key).

• 2NF (Second Normal Form): The table must be in 1NF and all non-key columns

must depend on the entire primary key (i.e., there should be no partial

dependencies).

Example:

• A table with a composite primary key (e.g., StudentID, CourseID) where non-key

attributes like CourseName only depend on CourseID would violate 2NF because

CourseName is partially dependent on the composite key. You would separate this

into two tables (one for Courses and one for Students).

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.

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.

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.

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).

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

]);

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.

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" }

}
}
}

});

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.