Database system concepts Questions & Answers

The primary purpose of a DBMS is to provide a systematic way to create, maintain, and access large amounts of data efficiently and securely. It abstracts the complexities of data storage and retrieval, offering features like data independence, concurrent access, data integrity, security, and recovery from failures. A DBMS allows users and applications to interact with data without needing to know the physical storage details, ensuring data consistency and reliability across various operations.

The three-schema architecture, also known as ANSI/SPARC architecture, proposes three levels of data abstraction: the internal schema, the conceptual schema, and the external schema. The internal schema describes the physical storage structure of the database. The conceptual schema describes the entire database structure for a community of users, hiding the physical details. The external schema (or view schema) describes a part of the database relevant to a particular user group, further abstracting from the conceptual schema. This architecture promotes data independence.

The four ACID properties are Atomicity, Consistency, Isolation, and Durability. Atomicity ensures transactions are all-or-nothing. Consistency guarantees transactions move the database from one valid state to another. Isolation ensures concurrent transactions appear to execute serially. Durability means committed changes are permanent. These properties are crucial because they collectively guarantee the reliability and integrity of data in a database system, especially in the face of concurrent access and system failures, preventing data corruption and ensuring business logic is correctly applied.

File-processing systems manage data in separate files, leading to data redundancy, inconsistency, and difficulty in accessing related data. They lack centralized control, data independence, and robust security or recovery mechanisms. A DBMS, in contrast, offers centralized data management, reducing redundancy and improving consistency. It provides data independence, allowing schema changes without affecting applications, and includes sophisticated features for data integrity, security, concurrency control, and crash recovery, making it superior for complex data management needs.

Data independence refers to the ability to modify the schema at one level of a database system without affecting the schema at the next higher level. There are two types: physical data independence (changing physical storage without affecting conceptual schema) and logical data independence (changing conceptual schema without affecting external schemas). It's important because it reduces maintenance costs, allows for system evolution without rewriting applications, and simplifies database administration, making the system more flexible and adaptable to changing requirements.

The ER model helps in database design by providing a high-level conceptual view of the data. It allows designers to represent real-world entities, their attributes, and the relationships between them in a clear, graphical manner. This model facilitates communication between users and designers, ensuring that business requirements are accurately captured. It serves as a blueprint that can then be systematically translated into a relational schema, forming the basis for the logical design of the database, minimizing ambiguities and design flaws early on.

Normalization is a process of organizing a relational database schema to minimize data redundancy and improve data integrity. 1NF requires that all attributes contain atomic values and there are no repeating groups. 2NF requires 1NF and that all non-key attributes are fully functionally dependent on the primary key, eliminating partial dependencies. 3NF requires 2NF and that all non-key attributes are non-transitively dependent on the primary key, removing transitive dependencies. These forms progressively reduce anomalies and improve schema quality.

A transaction schedule is a sequence of operations (read, write, commit, abort) from a set of concurrent transactions, indicating the chronological order in which these operations are executed. Its role in concurrency control is to analyze and ensure that concurrent execution of transactions is correct, meaning it produces the same result as some serial execution of those transactions. Schedulers use techniques like locking or timestamping to order operations, preventing conflicts and maintaining the ACID properties, particularly isolation and consistency.

Locks are mechanisms used to control concurrent access to data items. When a transaction wants to read or write a data item, it first requests a lock on that item. Shared locks (S-locks) allow multiple transactions to read concurrently but prevent writes. Exclusive locks (X-locks) allow only one transaction to access the item for reading or writing, blocking all other access. By acquiring and releasing appropriate locks, transactions prevent conflicts like dirty reads, lost updates, and unrepeatable reads, thereby ensuring serializability and data consistency.

The purpose of indexing is to speed up data retrieval operations on database tables. An index is a data structure (like a B-tree or hash table) that stores a small, ordered subset of the data from a table, along with pointers to the full records. When a query searches for data based on an indexed column, the DBMS can quickly navigate the index to find the relevant records, avoiding a full table scan. This significantly reduces I/O operations and CPU usage, leading to faster query execution, especially for large tables.

The Database Administrator (DBA) is responsible for the overall management and maintenance of a database system. Their roles include schema definition, storage structure and access method definition, schema and physical organization modification, granting user access and authorization, specifying integrity constraints, and performing backup and recovery. The DBA ensures the database's security, performance, availability, and integrity, acting as the central authority for all database-related operations and policies within an organization.

SQL offers several types of joins. An INNER JOIN returns only rows where there is a match in both tables, used when you need records with corresponding entries in both. A LEFT (OUTER) JOIN returns all rows from the left table and matching rows from the right, or NULLs if no match, useful for seeing all records from one table and their related data. A RIGHT (OUTER) JOIN is similar but prioritizes the right table. A FULL (OUTER) JOIN returns all rows from both tables, with NULLs where no match, used when you need all records from both tables regardless of matches.

A recovery manager ensures durability by using logging and checkpointing mechanisms. Before any change is made to the database, a log record is written to stable storage, detailing the operation. In case of a crash, the recovery manager uses these logs to either undo uncommitted transactions (rollback) or redo committed transactions (rollforward) that might not have been fully written to disk. Checkpoints periodically flush modified buffer blocks to disk and record the state, reducing the amount of log scanning needed during recovery, thus restoring the database to a consistent state.

DDL (Data Definition Language) is used to define and manage the database schema. Commands like CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and GRANT/REVOKE permissions fall under DDL. DML (Data Manipulation Language) is used for managing data within the schema. Commands like SELECT, INSERT, UPDATE, and DELETE are DML commands. DDL statements are typically executed by DBAs or designers to structure the database, while DML statements are used by applications and users to interact with the data stored within that structure.

Referential integrity is a database concept that ensures that relationships between tables remain consistent. It dictates that if a foreign key in one table refers to a primary key in another table, then every value of the foreign key must either be NULL or exist as a primary key value in the referenced table. Foreign keys enforce this by preventing actions that would destroy these links, such as deleting a primary key record that is still referenced by a foreign key, or inserting a foreign key value that has no corresponding primary key.

In the ER model, a strong entity set is an entity set that has a primary key and can exist independently. Its existence does not depend on the existence of another entity set. A weak entity set, on the other hand, does not have sufficient attributes to form a primary key on its own. Its existence is dependent on a strong entity set, called its identifying or owner entity set. The primary key of a weak entity set is formed by combining its partial key with the primary key of its identifying strong entity set.

Query optimization is the process of selecting the most efficient execution plan for a given SQL query. When a query is submitted, the query optimizer analyzes it, generates multiple possible execution plans (e.g., different join orders, index usage, access methods), and estimates the cost (CPU, I/O, network) for each plan using statistics about the data. It then chooses the plan with the lowest estimated cost. This process aims to minimize the resources consumed and maximize the speed of query execution, often transparently to the user.

Distributed databases present several challenges compared to centralized ones. These include increased complexity in design and management due to data fragmentation and replication across multiple sites. Concurrency control and recovery become more intricate, requiring distributed protocols to maintain global consistency and atomicity. Network delays and failures introduce performance and reliability issues. Security management is also more complex across multiple nodes. Additionally, achieving data transparency (location and fragmentation transparency) while maintaining performance is a significant hurdle.

Views in SQL are virtual tables based on the result-set of an SQL query. They do not store data themselves but rather provide a dynamic window into the underlying base tables. Their primary purposes include simplifying complex queries by pre-joining tables or aggregating data, enhancing security by restricting users to specific rows or columns of a table, and providing data independence by allowing changes to the base tables without affecting applications that access data through the view, as long as the view's definition remains valid.

A functional dependency (FD) is a constraint between two sets of attributes in a relation. It states that if two tuples have the same values for a set of attributes A, then they must also have the same values for a set of attributes B. This is written as A → B, meaning A functionally determines B. FDs are fundamental to relational database design, particularly in normalization, as they help identify and eliminate redundancy and update anomalies by guiding the decomposition of relations into smaller, well-structured tables.