Unit6 - Subjective Questions

SQL Databases:

• Schema: Rigid and predefined schema. Data must follow a strict structure before insertion.
• Scalability: Generally scale vertically (scaling up by upgrading CPU/RAM of a single server).
• Data Model: Relational model using tables, rows, and columns. Uses Primary and Foreign keys for relationships.
• Transactions: Strictly adhere to ACID properties.

NoSQL Databases:

• Schema: Dynamic or schema-less. Data structures can vary from document to document.
• Scalability: Designed to scale horizontally (scaling out by adding more servers to a cluster).
• Data Model: Variety of models including Key-Value, Document-based, Column-family, and Graph.
• Transactions: Often follow BASE (Basically Available, Soft state, Eventual consistency) properties, though some (like MongoDB) now support ACID.

MongoDB is an open-source, NoSQL, document-oriented database designed to store and process large volumes of unstructured or semi-structured data.

Key Features:

• Document-Oriented: Stores data in BSON (Binary JSON) format, allowing for nested arrays and objects.
• Schema-less: Collections do not enforce document structure. Documents in the same collection can have different fields.
• High Availability: Uses replica sets (primary-secondary nodes) for automatic failover and data redundancy.
• Horizontal Scalability: Supports sharding to distribute data across multiple machines.
• Rich Query Language: Supports complex queries, aggregation pipelines, and secondary indexing.

MongoDB stores data in a hierarchical structure consisting of Databases, Collections, and Documents.

• Database: The top-level container for collections. It is equivalent to a Database in RDBMS.
• Collection: A grouping of MongoDB documents. It does not enforce a schema. It is equivalent to a Table in RDBMS.
• Document: A record in a MongoDB collection, consisting of field and value pairs (JSON-like structure). It is equivalent to a Row in RDBMS.
• Field: A name-value pair inside a document, equivalent to a Column in RDBMS.
• _id: A unique 12-byte identifier for every document, acting like a Primary Key.

CRUD stands for Create, Read, Update, and Delete.

• Create: Inserts new documents into a collection.
  • Syntax: db.collection.insertOne({ name: "Alice", age: 25 })
• Read: Retrieves documents from a collection.
  • Syntax: db.collection.find({ age: { $gt: 20 } })
• Update: Modifies existing documents.
  • Syntax: db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } })
• Delete: Removes documents from a collection.
  • Syntax: db.collection.deleteOne({ name: "Alice" })

Amazon DynamoDB is a fully managed, serverless, NoSQL database service provided by AWS that supports key-value and document data structures.

Core Components:

• Tables: The top-level data collection, similar to tables in relational databases. However, they are schema-less.
• Items: A group of attributes that is uniquely identifiable among all items in a table (similar to a row).
• Attributes: A fundamental data element, something that does not need to be broken down any further (similar to a column/field).
• Primary Key: Uniquely identifies each item. It can be a simple Primary Key (Partition Key) or a composite Primary Key (Partition Key + Sort Key).

In DynamoDB, the primary key uniquely identifies each item in a table. There are two types of primary keys:

1. Partition Key (Simple Primary Key):

   • Consists of a single attribute.
   • DynamoDB uses an internal hash function on the partition key value to determine the physical storage node for the item.
   • Only one item can have a given partition key value.

2. Partition Key and Sort Key (Composite Primary Key):

   • Consists of two attributes. The first is the partition key, and the second is the sort key.
   • Items with the same partition key are stored together, sorted by the sort key value.
   • This allows for complex querying (e.g., retrieving all orders for a specific customer sorted by date).

A Serverless Cloud Database is a database offering where the cloud provider dynamically manages the provisioning, scaling, and maintenance of the database servers. Developers do not need to configure or manage underlying instances.

Major Benefits:

• Auto-scaling: Automatically scales compute and storage resources up or down based on application traffic.
• Pay-as-you-go: Users are billed only for the resources consumed (e.g., read/write operations and storage) rather than a fixed hourly rate for idle servers.
• Zero Administration: No need to patch OS, manage backups, or provision hardware.
• High Availability: Built-in fault tolerance and multi-region replication managed by the provider.

Serverless Cloud Database:

• Capacity Planning: None required; scales automatically from zero to peak capacity.
• Pricing Model: Billed purely on consumption (e.g., per query, per RU/WU). Costs drop to zero (excluding storage) when idle.
• Management: Fully managed. Provider handles all updates, scaling, and failovers invisibly.
• Best For: Unpredictable, spiky workloads or applications with periods of inactivity.

Provisioned Cloud Database:

• Capacity Planning: Requires manual selection of instance types (CPU/RAM) and storage sizes.
• Pricing Model: Billed hourly for the provisioned instances, regardless of whether the database is being actively queried.
• Management: Partially managed. User may need to handle scaling operations (adding read replicas, upgrading instances).
• Best For: Predictable, steady, and high-throughput workloads where instance costs can be optimized.

A JSON database is a type of NoSQL document database designed to store, retrieve, and manage data represented in JSON (JavaScript Object Notation) format.

Utilization of JSON Format:

• Self-describing: JSON stores data in key-value pairs ("key": "value"), making it easily readable by both humans and machines.
• Nested Structures: It supports complex data types natively. An object can contain nested objects or arrays, eliminating the need for complex joins common in RDBMS.
• Flexibility (Semi-structured): Different documents in the same database can have different fields. A new field can be added to a JSON document without altering the entire database schema.
• Native Web Support: JSON is the native data format of modern web applications, meaning data can be sent directly from the database to the front-end with minimal transformation.

A JSON representation for a university student dataset can include basic strings, numbers, arrays (for lists of courses), and nested objects (for addresses).

{
"student_id": "S10293",
"first_name": "John",
"last_name": "Doe",
"enrollment_year": 2021,
"gpa": 3.8,
"contact_info": {
"email": "john.doe@university.edu",
"phone": "+1-555-0198"
},
"address": {
"street": "123 College Ave",
"city": "Techville",
"zipcode": "90210"
},
"enrolled_courses": [
{
"course_code": "CS101",
"course_name": "Intro to Programming",
"credits": 4
},
{
"course_code": "DB201",
"course_name": "Database Management Systems",
"credits": 3
}
],
"is_active": true
}

This structure allows all data related to the student to be retrieved in a single read operation.

BSON stands for Binary JSON. It is a binary-encoded serialization of JSON-like documents used by MongoDB as its primary data storage and network transfer format.

Reasons MongoDB uses BSON:

1. Data Types: BSON extends the JSON model to provide additional data types not supported by standard JSON, such as Date, ObjectId, and BinData (binary data).
2. Traversal Speed: BSON is designed to be highly traversable. It stores length metadata for documents and arrays, allowing the database engine to skip over elements quickly without parsing them entirely. This makes search operations faster, reducing complexity to for field lookups in some cases.
3. Encoding/Decoding: Binary encoding makes it faster for machines to parse and generate compared to text-based JSON, leading to better performance in high-throughput environments.

Definition: An index in MongoDB is a specialized data structure (typically a B-Tree) that stores a small portion of the collection's data set in an easy-to-traverse form. It stores the value of a specific field or set of fields, ordered by the value.

Importance of Index Creation:

• Performance Improvement: Without indexes, MongoDB must perform a collection scan (reading every document in the collection) to find matches. Indexes allow MongoDB to locate data quickly using binary search principles, reducing search time complexity to .
• Sorting: Indexes allow the database to return sorted results efficiently without needing to sort the data in memory.
• Uniqueness: Unique indexes prevent the insertion of duplicate values in specific fields (e.g., ensuring no two users have the same email).

Indexes in MongoDB are created using the createIndex() method on a collection.

1. Single-Field Index: Creates an index on a single field in ascending order (1).
   • Syntax: db.collection.createIndex({ "username": 1 })
2. Descending Index: Creates an index in descending order (-1), useful for queries that sort in reverse.
   • Syntax: db.collection.createIndex({ "createdAt": -1 })
3. Compound Index: Creates an index on multiple fields. The order of fields matters.
   • Syntax: db.collection.createIndex({ "department": 1, "salary": -1 })

Additionally, index options can be provided, such as creating a unique index:
db.collection.createIndex({ "email": 1 }, { unique: true })

The explain() method in MongoDB is used to return information on how the database executes a query or aggregation pipeline. It provides a detailed query plan and execution statistics.

Purpose and Assistance in Optimization:

• Query Plan: It reveals whether MongoDB used an index (IXSCAN) or performed a full collection scan (COLLSCAN).
• Execution Stats: By passing "executionStats" to the method, developers can see metrics like:
  • totalDocsExamined: Number of documents scanned.
  • totalKeysExamined: Number of index keys scanned.
  • executionTimeMillis: Total time taken to execute the query.
• Performance Tuning: By analyzing the output, developers can identify slow queries (e.g., those with a high ratio of totalDocsExamined to returned documents) and create appropriate indexes to reduce read operations and improve speed.

Scenario: A collection users has 1 million documents. We want to find a user by their email.

Before Index Creation:
Query: db.users.find({ email: "test@example.com" }).explain("executionStats")

• Result Analysis: The winningPlan shows a COLLSCAN (Collection Scan). The totalDocsExamined is 1,000,000, meaning MongoDB had to look at every document. The executionTimeMillis might be around 500ms.

Action: Create an index.
Command: db.users.createIndex({ email: 1 })

After Index Creation:
Query: db.users.find({ email: "test@example.com" }).explain("executionStats")

• Result Analysis: The winningPlan now shows an IXSCAN (Index Scan) followed by a FETCH. The totalKeysExamined is 1, and totalDocsExamined is 1. The executionTimeMillis drops dramatically to ~1ms.

Conclusion: The EXPLAIN output proves that index creation reduced the search space from to , massively improving query performance.

A Vector Database is a type of database designed to store, manage, and search vector embeddings—mathematical representations of data (text, images, audio) in high-dimensional space.

Importance in AI/ML:

• Semantic Search: Traditional databases rely on keyword matching (lexical search). Vector databases allow for semantic search, finding data that means the same thing even if the exact keywords aren't used.
• Handling Unstructured Data: AI models (like Large Language Models) convert unstructured text or images into dense vectors. Vector databases are optimized to store these high-dimensional arrays.
• Similarity Search: They excel at nearest-neighbor search, rapidly finding vectors close to a query vector using distance metrics, which is crucial for recommendation systems, facial recognition, and Retrieval-Augmented Generation (RAG) in LLMs.

Similarity search is the process of finding the most similar items (nearest neighbors) to a given query item within a dataset. In a vector database, items are represented as high-dimensional vectors, and similarity is calculated by measuring the geometric distance between these vectors.

Common Distance Metrics:

1. Euclidean Distance (L2 Norm): Measures the straight-line distance between two vectors and . Calculated as:
   
   Lower values indicate higher similarity.
2. Cosine Similarity: Measures the cosine of the angle between two vectors, focusing on their direction rather than magnitude. It is highly effective for text embeddings.
   
3. Dot Product: Multiplies corresponding elements and sums them up. Used when vectors are normalized.

The CAP Theorem states that a distributed data store can provide at most two of the following three guarantees simultaneously:

1. Consistency (C): Every read receives the most recent write or an error.
2. Availability (A): Every request receives a non-error response, without the guarantee that it contains the most recent write.
3. Partition Tolerance (P): The system continues to operate despite an arbitrary number of messages being dropped by the network.

Application in NoSQL:
Since network partitions are inevitable in distributed systems, databases must choose between Consistency and Availability (CP vs AP).

• MongoDB: Typically categorized as a CP system. In a replica set, if the primary node goes down, the system stops accepting writes (loss of Availability) until a new primary is elected, ensuring Consistency.
• DynamoDB: Highly tunable, but often defaults to an AP system (offering eventual consistency for fast, highly available reads). However, it allows developers to request Strongly Consistent reads, temporarily sacrificing availability for consistency.

The Aggregation Pipeline in MongoDB is a framework for data aggregation modeled on the concept of data processing pipelines. Documents enter a multi-stage pipeline that transforms them into aggregated results.

Key Stages:

• $match: Filters documents to pass only those that match specified conditions.
• $group: Groups documents by a specified key and applies accumulator expressions (e.g., sum, average).
• $sort: Reorders the documents.

Example:
To find the total sales per department for sales over $100:
javascript
db.sales.aggregate([
{ gte: 100 } } },
{ department", totalSales: { amount" } } },
{ $sort: { totalSales: -1 } }
])

Here, the output of the group, and its output flows into $sort.

Eventual Consistency:
In distributed NoSQL systems, when data is updated on one node, it takes time for that update to replicate to all other nodes. Eventual consistency guarantees that, if no new updates are made, eventually all accesses will return the last updated value. During the replication window, a read operation might return stale data. This model prioritizes low latency and high availability.

Strong Consistency:
In a strongly consistent model, once a write is acknowledged, any subsequent read will reflect that write, regardless of which node is accessed. This often requires locking the nodes until data is fully replicated, ensuring accuracy but potentially increasing latency and reducing availability during network partitions.

Comparison: While RDBMS generally strictly enforce strong consistency, databases like DynamoDB offer developers the flexibility to choose between eventual and strong consistency on a per-query basis.