Unit 6: Advance Databases - Subjective Questions

Major trends in database technology include:

• Cloud-native databases: Databases are increasingly deployed as managed, elastic services in public, private, or hybrid clouds.
• Distributed architectures: Data is partitioned and replicated across several nodes to improve scalability, availability, and fault tolerance.
• Multi-model databases: A single system may support relational, document, graph, key-value, and other data models.
• AI-assisted administration: Machine learning is used for query optimization, indexing, capacity planning, anomaly detection, and automatic tuning.
• Serverless databases: Infrastructure provisioning and scaling are handled automatically, with users paying according to actual consumption.
• Real-time analytics: Streaming platforms and hybrid transactional/analytical processing enable immediate analysis of operational data.
• Data privacy and security: Encryption, auditing, access control, data masking, and regulatory compliance are becoming central design requirements.
• Specialized databases: Graph, time-series, spatial, vector, and ledger databases are being developed for specific workloads.

Future systems are expected to become more autonomous, globally distributed, intelligent, interoperable, and capable of processing structured, semi-structured, and unstructured data through a unified interface.

Big Data refers to data collections whose size, complexity, and rate of generation exceed the practical processing capabilities of conventional database systems.

Its major characteristics are commonly described using the Vs model:

• Volume: The enormous quantity of data generated and stored, often measured in terabytes, petabytes, or exabytes.
• Velocity: The speed at which data is produced, transmitted, and processed, such as real-time sensor or financial data.
• Variety: The presence of structured, semi-structured, and unstructured data, including tables, JSON documents, images, videos, and logs.
• Veracity: The accuracy, reliability, and uncertainty associated with the data.
• Value: The useful knowledge or business benefit that can be extracted from the data.

Additional characteristics may include variability, which concerns changing data rates and meanings, and visualization, which concerns presenting complex analytical results effectively.

Traditional relational database systems may be inadequate for some Big Data workloads because:

• Vertical scaling limitations: They commonly scale by adding resources to one server, which becomes expensive and eventually reaches a hardware limit.
• Rigid schemas: A predefined schema makes it difficult to accommodate rapidly changing or heterogeneous data.
• Unstructured data: Images, videos, social media posts, logs, and nested documents do not always fit naturally into normalized tables.
• High ingestion rates: A single relational server may struggle with millions of continuous events or writes.
• Distributed processing requirements: Big Data analysis often requires computation to be distributed close to data across many machines.
• Join overhead: Complex joins over extremely large distributed datasets can be costly.
• Availability requirements: Global applications may require continuous availability despite network or node failures.

However, relational databases remain suitable when strong consistency, transactions, structured schemas, and complex SQL queries are primary requirements. Modern distributed SQL and NewSQL systems attempt to combine these relational strengths with horizontal scalability.

A typical Big Data architecture contains the following layers:

1. Data sources: Applications, sensors, websites, social networks, transaction systems, and external datasets generate data.
2. Ingestion layer: Batch and streaming tools collect data. Message brokers can buffer and distribute continuously arriving events.
3. Storage layer: Data lakes, distributed file systems, object stores, NoSQL databases, and relational warehouses store raw and processed data.
4. Processing layer: Distributed engines perform batch processing, stream processing, cleansing, transformation, and aggregation.
5. Serving layer: Databases and analytical systems expose processed data to applications, dashboards, and reporting tools.
6. Analytics layer: OLAP, machine learning, data mining, visualization, and ad hoc querying generate insights.
7. Governance and security layer: Metadata management, lineage, quality control, encryption, authorization, and auditing apply across the architecture.

Databases provide persistent storage, indexing, query execution, transaction management, metadata, and controlled access. Different databases may be combined through polyglot persistence, where each workload uses the database model best suited to it.

Online Analytical Processing, or OLAP, is a technology for performing fast, interactive, multidimensional analysis of large volumes of historical or aggregated data.

Principal OLAP operations include:

• Roll-up: Aggregates data to a higher level, such as changing daily sales into monthly or yearly sales.
• Drill-down: Moves from summarized data to more detailed data, such as from annual sales to quarterly and monthly sales.
• Slice: Selects one value of a dimension, such as sales for the year 2025.
• Dice: Selects a subcube using values or ranges from multiple dimensions, such as laptop sales in two regions during the first quarter.
• Pivot or rotate: Reorients the cube to present dimensions from another perspective, such as exchanging product rows with region columns.
• Drill-through: Moves from an aggregate cube value to the detailed source records behind it.

OLAP is optimized for complex, read-intensive analytical queries rather than frequent short transactions.

The three major OLAP storage approaches are:

MOLAP is suitable when rapid analysis and predictable dimensions are required. ROLAP is appropriate for very large datasets and flexible SQL-based analysis. HOLAP balances performance and storage by keeping summaries in cubes while retaining detailed records in relational tables.

A star schema has one central fact table directly connected to denormalized dimension tables. A snowflake schema normalizes dimensions into multiple related tables.

Star schema

• Has a simple structure resembling a star.
• Requires fewer joins.
• Usually provides faster and simpler analytical queries.
• Contains some redundant dimension data.
• Is easier for users and reporting tools to understand.

Snowflake schema

• Splits dimensions into normalized subdimension tables.
• Reduces redundancy and can improve integrity.
• Requires more joins and produces more complex queries.
• May reduce query performance compared with a star schema.
• Is suitable for large, hierarchical dimensions requiring controlled maintenance.

For example, in a star schema, a product dimension may directly contain category and department attributes. In a snowflake schema, product, category, and department may be stored in separate related tables. The choice depends on the balance required among query speed, simplicity, redundancy, and maintainability.

A multidimensional data cube organizes numerical measures, such as sales or profit, according to dimensions such as time, product, customer, and region.

Cube construction generally involves:

1. Selecting a fact table and its measures.
2. Identifying dimensions and their hierarchies.
3. Cleaning and integrating source data.
4. Computing group-by aggregations for selected dimension combinations.
5. Storing or indexing frequently used aggregates.
6. Refreshing the cube when source data changes.

If a cube contains independent dimensions and each dimension can either appear in an aggregation or be replaced by the value ALL, the number of possible cuboids is:

For example, a cube with four dimensions has:

possible cuboids, including the detailed base cuboid and the fully aggregated apex cuboid. If dimensions have multiple hierarchy levels, the number can be larger and may be expressed as:

where is the number of selectable hierarchy levels in dimension , and the additional level represents ALL. Because full materialization can consume substantial storage and refresh time, systems may use partial materialization based on query frequency and cost.

A cloud database is a database deployed on cloud infrastructure and accessed through a network. It may be self-managed on virtual machines or offered as a fully managed Database as a Service.

Its architecture commonly includes:

• Distributed compute and storage resources.
• Replication across availability zones or geographical regions.
• Load balancers and connection endpoints.
• Automated monitoring, backup, patching, and recovery services.
• Elastic scaling and usage-based billing.

Benefits:

• Rapid provisioning and reduced infrastructure setup.
• Elastic scaling according to workload demand.
• Automated backup, patching, monitoring, and failover.
• High availability through replication.
• Global accessibility and support for geographically distributed applications.
• Lower initial capital expenditure.

Limitations:

• Dependence on reliable network connectivity.
• Latency for geographically distant clients.
• Vendor lock-in due to proprietary services and interfaces.
• Security, privacy, and data-residency concerns.
• Unpredictable costs when usage is not monitored.
• Reduced administrative control in fully managed services.

Cloud databases can operate under several deployment models:

• Public cloud: Infrastructure is operated by a cloud provider and shared securely among customers.
• Private cloud: Cloud infrastructure is dedicated to one organization, providing greater control and customization.
• Hybrid cloud: Private infrastructure is integrated with public cloud resources.
• Multi-cloud: Database workloads use services from more than one cloud provider.

Relevant service models include:

• Infrastructure as a Service: The customer installs and administers the database on cloud virtual machines. This provides control but requires patching, backup, and tuning.
• Platform as a Service or Database as a Service: The provider manages infrastructure, installation, backup, patching, scaling, and often high availability.
• Serverless database: Capacity is automatically allocated and billing is based largely on actual usage, requests, or consumed resources.

The chosen model determines the division of responsibility. Greater provider management reduces operational effort but can limit low-level control and increase dependency on provider-specific features.

Important cloud database challenges include:

• Consistency: Replication across distant regions introduces delay. Strong consistency simplifies application logic but can increase latency, while eventual consistency improves responsiveness but may expose stale values.
• Availability: Systems must tolerate machine, zone, network, and regional failures. Replication and automatic failover are essential but must be tested.
• Backup and recovery: Providers may automate snapshots and point-in-time recovery, but organizations must define retention, recovery point objectives, and recovery time objectives.
• Security: Data should be encrypted both at rest and in transit. Strong identity management, least-privilege authorization, key management, auditing, and network isolation are required.
• Compliance and residency: Laws may restrict where personal or sensitive data can be stored and processed.
• Shared responsibility: The provider secures cloud infrastructure, while the customer remains responsible for proper identities, permissions, schemas, data classification, and application security.

A sound design combines replication with independent backups because replication alone cannot protect against accidental deletion, corruption, or malicious changes.

NewSQL refers to modern relational database systems designed to provide the horizontal scalability and distributed availability associated with NoSQL while preserving SQL, relational schemas, and ACID transactions.

NewSQL emerged because traditional relational systems often had difficulty scaling write-intensive applications across commodity nodes, while many NoSQL databases sacrificed joins, standard SQL, or strong transaction guarantees to achieve scalability.

Typical NewSQL characteristics include:

• SQL-based access and relational data models.
• Distributed query execution.
• Automatic sharding or partitioning.
• Replication and fault tolerance.
• Horizontal scaling through additional nodes.
• ACID transactions across records or partitions.
• Concurrency control designed for distributed environments.

NewSQL is suitable for applications such as banking, retail, reservations, and global transaction processing where both scalability and transactional correctness are required.

Traditional relational systems are effective for centralized structured workloads. NewSQL retains relational guarantees while scaling across nodes. NoSQL prioritizes flexible models, distribution, and workload-specific performance. These categories overlap because modern products increasingly adopt features from one another.

NewSQL systems use several techniques to combine scalability with transactional correctness:

• Data partitioning: Rows are divided among nodes using ranges, hashes, or other partitioning strategies.
• Replication: Each partition is copied to multiple nodes for availability and durability.
• Consensus protocols: Replicas agree on the order and commitment of updates even when failures occur.
• Distributed transactions: Protocols coordinate atomic changes involving more than one partition.
• Distributed concurrency control: Systems may use locking, optimistic validation, multiversion concurrency control, or deterministic transaction ordering.
• Distributed query planning: Queries are broken into operations executed near the relevant partitions.
• Automatic rebalancing: Data is relocated when nodes are added, removed, overloaded, or unavailable.
• Clock or timestamp mechanisms: Logical or carefully managed physical timestamps help establish serializable ordering.

Performance improves when transactions access a single partition. Cross-partition transactions require additional coordination, network communication, and logging, so effective schema and partition-key design remain important.

NoSQL, commonly interpreted as Not Only SQL, describes non-relational or alternative database systems designed for flexible data models, distributed operation, high availability, and horizontal scalability.

The four principal categories are:

1. Key-value databases: Store each value under a unique key. They support rapid lookup and are useful for caching, sessions, and user preferences.
2. Document databases: Store self-describing documents, commonly in JSON-like form. They are useful for content management, catalogs, and applications with evolving schemas.
3. Column-family databases: Store sparse data in rows organized into column families. They are suitable for massive distributed datasets, event records, and time-series-like workloads.
4. Graph databases: Represent data as vertices and edges with properties. They are effective for social networks, recommendations, fraud detection, and knowledge graphs.

NoSQL systems differ significantly in consistency, transaction support, query capabilities, and indexing. Therefore, the database should be selected according to access patterns and correctness requirements rather than merely data size.

A key-value database treats data as pairs consisting of a unique key and an associated value. The database may not understand the internal structure of the value.

• Common operations include insert, retrieve, update, and delete by key.
• It offers simple distribution and very fast direct access.
• Typical uses include caching, session management, shopping carts, and configuration data.
• Complex searches within values may be limited.

A document database stores structured documents containing nested fields, arrays, and values.

• It can index and query fields within a document.
• Documents in the same collection may have different structures.
• Typical uses include product catalogs, user profiles, content systems, and web applications.
• Cross-document joins may be limited or less efficient than relational joins.

Thus, key-value databases are preferred for predictable access by a known key, while document databases are appropriate when applications require flexible records and queries over internal document attributes.

A column-family database stores data by row keys and groups related columns into column families. Rows can be sparse, and different rows need not contain identical columns.

Its characteristics include:

• Horizontal partitioning using row keys.
• Efficient storage of wide and sparse datasets.
• High write throughput on distributed clusters.
• Query patterns that should generally be considered during schema design.

Suitable scenarios include event logs, Internet of Things data, messaging records, large-scale metrics, and distributed activity feeds.

A graph database stores:

• Vertices or nodes representing entities.
• Edges or relationships connecting entities.
• Properties describing nodes and relationships.

Graph databases efficiently perform traversals such as finding friends of friends, shortest paths, or suspicious relationship patterns. Suitable scenarios include social networking, recommendation engines, route planning, network management, identity resolution, and fraud detection.

Column-family databases focus on scalable storage and key-oriented access, whereas graph databases focus on highly connected data and relationship traversal.

The CAP theorem states that when a network partition occurs, a distributed system cannot simultaneously guarantee both complete consistency and complete availability.

Its three properties are:

• Consistency: Every successful read observes the most recent committed write or an error.
• Availability: Every request to a functioning node receives a non-error response, although that response might not contain the latest data.
• Partition tolerance: The system continues operating despite communication failures that divide nodes into disconnected groups.

Because network partitions are unavoidable in distributed environments, a system must decide how to behave during a partition:

• A CP-oriented design preserves consistency by rejecting or delaying some operations, thereby reducing availability.
• An AP-oriented design keeps serving requests but may return stale or conflicting values that must later be reconciled.

CAP does not mean that only two properties are permanently available under normal conditions. The key trade-off arises specifically during a partition. Database designers must select consistency levels according to business needs; for example, financial balances may favor consistency, while social media feeds may tolerate temporary staleness.

ACID emphasizes reliable transactions:

• Atomicity: A transaction is completed entirely or not at all.
• Consistency: A transaction preserves defined integrity rules.
• Isolation: Concurrent transactions behave according to the selected isolation level, ideally as if executed independently.
• Durability: Committed changes survive failures.

BASE emphasizes availability and distributed scalability:

• Basically Available: The system generally remains operational despite failures.
• Soft state: Replicated state may change over time even without new user input because updates are propagating.
• Eventual consistency: If no new updates occur, replicas are expected to converge eventually.

ACID is usually preferred for banking, reservations, inventory control, and other operations requiring immediate correctness. BASE can be appropriate for feeds, analytics, logs, and applications that tolerate temporary inconsistency. The distinction is not absolute: many modern databases support tunable consistency, local ACID transactions, or distributed transactions while also using asynchronous replication.

A single database is unlikely to optimize all the workloads, so polyglot persistence is appropriate.

• Product catalog: A document database is suitable because products can have different attributes, nested specifications, and frequently evolving schemas. Secondary indexes can support searches by category, brand, and price.
• Payment transactions: A relational or NewSQL database is appropriate because payments require ACID transactions, uniqueness constraints, auditing, and strong consistency. NewSQL is especially useful when the service must scale across regions.
• Recommendation relationships: A graph database can represent customers, products, purchases, views, and similarities as connected nodes and edges. Traversals can discover related products or users.
• Shopping sessions and carts: A key-value database can provide rapid access through session or customer identifiers.
• Clickstream ingestion: A distributed column-family or time-series database can support high write rates and time-based access.
• Historical analytics: Data can be transferred to a cloud data warehouse, lakehouse, or OLAP platform for aggregation and multidimensional analysis.

The architecture must also include data pipelines, metadata governance, security, monitoring, and clear ownership of authoritative data. Replication should not create uncontrolled duplicate sources of truth, and consistency requirements should be defined separately for each workload.