Advanced Database Assignment 1
Uploaded by
jukesAdvanced Database Assignment 1
Uploaded by
jukesCommon questions
Powered by AIRelational databases primarily scale vertically by upgrading existing hardware, although some support horizontal scaling through sharding. They are ideal for applications requiring data integrity, like banking systems, due to their structured schemas and relationship constraints. Non-relational databases, or NoSQL databases, are designed for horizontal scalability, distributing data across multiple servers, making them suitable for high-volume, rapidly changing applications like real-time web apps. They are preferred when the application demands flexible schema designs and high availability under significant loads .
Integrating machine learning (ML) into database systems enhances performance through query optimization, where ML analyzes historical patterns to suggest efficient execution plans; anomaly detection, identifying unusual activities for security alerts; and automated indexing, which simplifies database optimization. Examples include predictive maintenance for hardware failure prevention, data classification to improve retrieval speeds, and intelligent caching to enhance response times by predicting access patterns. This leads to adaptive, optimized database management .
NoSQL databases offer schema flexibility, horizontal scalability, high performance for read/write operations, and support for diverse data models. A use case where NoSQL is preferable is in real-time web applications requiring rapid scaling and flexible data representation, such as social media platforms. These systems need to handle large volumes of dynamic data across distributed networks efficiently, a scenario that aligns well with NoSQL's strengths in managing unstructured data and scaling horizontally .
Data privacy regulations like GDPR significantly influence database management by mandating secure handling and minimization of personal data to prevent unauthorized access and breaches. Core principles enforced include data protection and privacy, requiring secure data handling and adherence to legal frameworks to avoid penalties; data minimization, encouraging the collection of only necessary information; and accountability and transparency, demanding organizations be accountable and clear about data practices. This fosters trust and ensures responsible data governance .
Improper indexing can degrade database performance by slowing down write operations due to excessive indexes imposing additional overhead. Indexing rarely queried columns wastes resources, while too many indexes can complicate maintenance, leading to suboptimal query execution plans. To avoid these issues, careful analysis of query patterns should guide index creation, ensuring indexes support frequent queries without causing unnecessary bloat. Database administrators should regularly review and optimize indexes as access patterns change .
ACID properties ensure reliable transactions in distributed databases by maintaining atomicity, consistency, isolation, and durability. Atomicity is achieved through protocols like the Two-Phase Commit (2PC), which ensures that all transaction components succeed or fail as a unit. Consistency is upheld by consensus algorithms such as Paxos or Raft, ensuring all nodes agree on data states. Isolation is maintained via locking mechanisms and timestamp ordering to manage concurrent transactions safely. Durability is ensured through data replication and write-ahead logging, which guarantees that completed transactions persist in the face of failures .
Ensuring ACID compliance in distributed databases faces challenges like network latencies affecting isolation, difficulty in maintaining consistency across partitions, and overhead from protocols like Two-Phase Commit, which can cause blocking and introduce single points of failure. Durability requires robust mechanisms for data replication to prevent data loss during failures. These complexities demand sophisticated algorithms and infrastructural support, potentially increasing system cost and complexity while balancing trade-offs between consistency and availability .
Query optimization significantly impacts database management by reducing query execution time and resource usage, essential for efficient data processing. Machine learning contributes by analyzing query execution patterns to develop optimized execution plans, automating index creation based on access trends, and suggesting improvements. This reduces manual intervention, allowing dynamic adaptation to workloads and enhancing query performance even as data environments evolve .
Data minimization, a key GDPR principle, dictates collecting only essential data, impacting database design by necessitating efficient storage structures and streamlined data processes. This reduces risks of breaches, enhances operational efficiency, and requires databases to be designed for flexible data management that can adapt to changing data requirements. By minimizing unnecessary data retention, organizations not only comply with legal mandates but also optimize storage and processing resources, fostering sustainable data practices .
The Two-Phase Commit (2PC) protocol is crucial for maintaining data consistency in distributed systems. It involves a Prepare Phase, where a coordinator requests all participant nodes to prepare for commit, and a Commit Phase, where, based on participants' unanimous commit votes, the coordinator sends a commit or abort command. This ensures atomicity and consistency, though it introduces challenges like blocking if nodes fail. 2PC is significant as it guarantees all parts of a transaction are completed reliably across systems .