Database - SS3 ICT Past Questions and Answers - page 2

A primary key in a relational database is a unique identifier for each record or row in a table. Its importance lies in data integrity and record identification. By enforcing uniqueness, it ensures that no duplicate records exist in the table, preventing data inconsistencies. Additionally, it serves as a reference point for establishing relationships between tables through foreign keys. This helps maintain data consistency and enables efficient data retrieval, as the primary key is often used in querying and indexing.

In a hierarchical database, data is organized in a tree-like structure with parent-child relationships, where each child can have only one parent. In contrast, a network database also uses a tree structure but allows each child to have multiple parents. Key differences include:

• Flexibility: Network databases are more flexible due to multiple parent-child relationships, while hierarchies are more rigid.
• Complexity: Network databases can represent complex relationships better.
• Data Integrity: Hierarchical databases are simpler to maintain in terms of data integrity, as they have stricter rules.

ACID stands for Atomicity, Consistency, Isolation, and Durability, and they are critical for ensuring the reliability of database transactions:

Atomicity: This property ensures that a transaction is treated as a single unit of work. It either completes entirely or leaves no trace if it fails, preventing partial changes to the database, which could lead to inconsistencies.

Consistency: It guarantees that a transaction brings the database from one consistent state to another. It enforces data integrity constraints, ensuring that only valid data changes occur.

Isolation: Isolation ensures that concurrent transactions do not interfere with each other. Each transaction appears to be executed in isolation from others, preventing data corruption due to simultaneous access.

Durability: Durability guarantees that once a transaction is committed, its effects are permanent and survive system failures. It ensures that data changes are not lost.

These properties collectively provide data reliability and integrity, making them essential for database systems.

Indexing enhances query performance by creating a data structure that allows the database management system to quickly locate the rows that match specific query conditions. When creating indexes, consider:

Selectivity: Indexes should be selective to filter out a significant portion of the data. Highly selective indexes improve query performance.

Index Maintenance: Indexes incur overhead during data updates, so balance the benefits of improved queries with the cost of maintaining indexes.

Composite Indexes: Combine multiple columns into a composite index to support queries involving those columns.

Index Size: Large indexes consume more storage, so consider storage constraints.

Query Patterns: Design indexes based on the most common query patterns to optimize those queries.

Database Engine: Different database engines have specific index implementation options; choose the appropriate type for your use case.

In-Memory Database:

Advantages: Extremely fast data retrieval due to data residing in memory, ideal for applications requiring low-latency access.

Limitations: Limited by available memory size, data is volatile and can be lost in case of system failure without proper backup mechanisms.

Distributed Database:

Advantages: Scalability and fault tolerance through data distribution across multiple servers or locations, suitable for large-scale applications.

Limitations: Complex to set up and maintain, potential for network latency, and data consistency challenges in distributed environments.