Unit3 - Subjective Questions

The differences between a File Processing System and a DBMS are as follows:

• Data Redundancy and Inconsistency:
  • File System: Data is often duplicated in different files, leading to redundancy and inconsistency.
  • DBMS: Controls redundancy through normalization and ensures data consistency.
• Data Access:
  • File System: Accessing data requires writing specific programs; it is difficult to access data efficiently.
  • DBMS: Provides sophisticated techniques (like SQL) to store and retrieve data efficiently.
• Data Integrity:
  • File System: Integrity constraints are difficult to implement and maintain.
  • DBMS: Enforces integrity constraints (e.g., Primary Keys, Foreign Keys) automatically.
• Security:
  • File System: Security is generally weak; it is hard to restrict user access to specific data.
  • DBMS: Provides a robust security subsystem with user authentication and authorization.
• Concurrency:
  • File System: Does not support concurrent access well, leading to race conditions.
  • DBMS: Handles concurrent access and transactions effectively using locking and isolation protocols.

RDBMS (Relational Database Management System) is a type of DBMS based on the relational model introduced by E.F. Codd. In an RDBMS, data is represented in terms of tuples (rows) grouped into relations (tables).

Key Characteristics:

1. Tabular Structure: Data is stored in tables (relations) consisting of rows and columns.
2. Schema Independence: Physical data storage is independent of the logical data structure.
3. Key Constraints: It utilizes Primary Keys for unique identification and Foreign Keys for establishing relationships between tables.
4. Normalization: It supports normalization to reduce redundancy.
5. SQL Support: It uses Structured Query Language (SQL) for data definition and manipulation.
6. ACID Properties: It strictly follows Atomicity, Consistency, Isolation, and Durability for transactions.

In a Relational Database:

1. Table (Relation):

   • A table is a collection of related data held in a structured format within a database. It consists of columns and rows.
   • Example: A Student table containing details of all students.

2. Field (Attribute/Column):

   • A field represents a specific category of data within a table. It defines the type of data (integer, text, date) that can be stored.
   • Example: In the Student table, Student_ID, Name, and Grade are fields.

3. Record (Tuple/Row):

   • A record is a single entry in a table. It contains specific data values for each field representing a single entity.
   • Example: A row containing {101, "Alice", "A"} is a record.

Visual Representation:

SQL commands are categorized based on their functionality:

1. DDL (Data Definition Language):

   • These commands are used to define or modify the structure of the database objects (schema).
   • Examples:
     • CREATE: Creates a new table or database.
     • ALTER: Modifies the structure of an existing table.
     • DROP: Deletes a table or database.

2. DML (Data Manipulation Language):

   • These commands are used for managing and manipulating data within the database tables.
   • Examples:
     • INSERT: Adds new records to a table.
     • UPDATE: Modifies existing records.
     • SELECT: Retrieves data from the database.

3. DCL (Data Control Language):

   • These commands deal with the rights, permissions, and other controls of the database system.
   • Examples:
     • GRANT: Gives user's access privileges to the database.
     • REVOKE: Withdraws user's access privileges.

Database Normalization is the process of organizing data in a database. This involves creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Anomalies in Unnormalized Databases:

1. Insertion Anomaly:

   • Occurs when certain attributes cannot be inserted into the database without the presence of other attributes.
   • Example: In a combined Student-Course table, we cannot add a new course unless a student enrolls in it.

2. Deletion Anomaly:

   • Occurs when deleting a record causes the unintended loss of other data.
   • Example: If a student is the only one enrolled in a specific course, deleting the student record might delete the course details entirely.

3. Update Anomaly:

   • Occurs when data is redundant/duplicated. Updating a value in one location requires updates in all locations to maintain consistency.
   • Example: If a professor's address changes, and it is stored in every student record they teach, failing to update all records leaves the database in an inconsistent state.

1. First Normal Form (1NF):

• Condition: A relation is in 1NF if every attribute contains only atomic (indivisible) values. There should be no repeating groups or arrays.
• Goal: Eliminate duplicate columns from the same table and create separate tables for each group of related data.

2. Second Normal Form (2NF):

• Condition: A relation is in 2NF if it is in 1NF AND has no Partial Dependency.
• Explanation: No non-prime attribute (attribute not part of the candidate key) should be dependent on a proper subset of any candidate key. This applies to tables with composite primary keys.
• Goal:

3. Third Normal Form (3NF):

• Condition: A relation is in 3NF if it is in 2NF AND has no Transitive Dependency.
• Explanation: No non-prime attribute should be determined by another non-prime attribute.
• Rule: For a functional dependency , either:
  1. is a Super Key, OR
  2. is a Prime Attribute.
• Goal: Remove dependencies where and (implies determines via ).

Database Keys are attributes or sets of attributes that uniquely identify a record in a table or establish relationships between tables.

Differentiation:

1. Super Key:

   • A set of one or more attributes that, taken collectively, allows us to identify uniquely a tuple in the relation.
   • Example: In a table with ID and Name, {ID}, {ID, Name}, {ID, Phone} are all Super Keys if ID is unique.

2. Candidate Key:

   • A minimal Super Key. It is a Super Key for which no proper subset is a Super Key.
   • It has no redundant attributes.
   • Example: If {ID} identifies a record, then {ID} is a Candidate Key. {ID, Name} is not, because {ID} alone suffices.

3. Primary Key:

   • One specific Candidate Key chosen by the database designer to uniquely identify tuples within the table.
   • It cannot contain NULL values.
   • Example: From Candidate Keys {Student_ID} and {Email}, {Student_ID} might be chosen as the Primary Key.

Relationship:

ACID properties ensure that database transactions are processed reliably. They are:

1. A - Atomicity:

   • Definition: This property states that a transaction must be treated as an atomic unit. It implies "All or Nothing."
   • Explanation: Either all operations of the transaction are reflected in the database, or none are. If a transaction fails halfway, the database rolls back to the state before the transaction started.

2. C - Consistency:

   • Definition: The database must remain in a consistent state before and after the transaction.
   • Explanation: The integrity constraints must be maintained so that the database does not violate any rules. (e.g., The total sum of money transferred between accounts remains constant).

3. I - Isolation:

   • Definition: Multiple transactions occurring simultaneously must not affect each other's execution.
   • Explanation: The intermediate state of a transaction should be invisible to other concurrent transactions. The result should be the same as if the transactions were executed serially.

4. D - Durability:

   • Definition: Once a transaction is committed, the changes are permanent.
   • Explanation: Even in the event of a system failure (crash or power loss) immediately after the commit, the data remains saved.

Foreign Key:
A Foreign Key is a field (or collection of fields) in one table, that refers to the Primary Key in another table. It is used to link two tables together.

Referential Integrity:
Referential Integrity is a database concept that ensures relationships between tables remain consistent. The Foreign Key enforces this by:

1. Valid Reference: Ensuring that a value entered in the Foreign Key column actually exists in the referenced Primary Key column of the parent table (or is NULL).
2. Preventing Orphan Records: It prevents the deletion of a record from the parent table if there are related records in the child table (unless CASCADE DELETE is set).

Example:

• Parent Table: Departments (DeptID [PK], Name)
• Child Table: Employees (EmpID, Name, DeptID [FK])
• Enforcement: You cannot assign an employee to a DeptID (e.g., 50) if DeptID 50 does not exist in the Departments table.

A transaction in a DBMS goes through the following states:

1. Active:

   • The initial state. The transaction stays in this state while it is executing read or write operations.

2. Partially Committed:

   • After the final operation is executed, but before the changes are permanently saved to the database on disk.

3. Failed:

   • The state entered if a normal check discovers that the transaction can no longer proceed (due to hardware failure or logical error).

4. Aborted:

   • After the transaction has failed and the database has been rolled back to the state prior to the start of the transaction. The transaction effectively ceases to exist.

5. Committed:

   • The state after the transaction has successfully completed and changes are permanently recorded in the database.

Flow:

SQL Joins are used to combine rows from two or more tables, based on a related column between them.

1. Inner Join:

   • Returns records that have matching values in both tables.
   • Venn Diagram: Intersection of Set A and Set B.
   • SELECT * FROM A INNER JOIN B ON A.id = B.id;

2. Left (Outer) Join:

   • Returns all records from the left table (A), and the matched records from the right table (B). If there is no match, the result is NULL on the right side.
   • SELECT * FROM A LEFT JOIN B ON A.id = B.id;

3. Right (Outer) Join:

   • Returns all records from the right table (B), and the matched records from the left table (A). If there is no match, the result is NULL on the left side.
   • SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

4. Full (Outer) Join:

   • Returns all records when there is a match in either left or right table records. It combines the result of both Left and Right joins.
   • SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;

Functional Dependency (FD):
It is a relationship between attributes in a table. It is denoted as . This means that the value of attribute set uniquely determines the value of attribute set . Here, is the determinant and is the dependent.

1. Trivial Functional Dependency:

   • A dependency is trivial if is a subset of ().
   • Example: If we have {Student_ID, Name}, then {Student_ID, Name} \rightarrow Student_ID is trivial because Student_ID is part of the determinant.

2. Non-Trivial Functional Dependency:

   • A dependency is non-trivial if is NOT a subset of .
   • Example: Student_ID Name. Name is not a subset of Student_ID. This conveys actual information.

Boyce-Codd Normal Form (BCNF) is an advanced version of 3NF, often called 3.5NF. A table is in BCNF if it is in 3NF and satisfies a stricter condition for functional dependencies.

Condition:
For every non-trivial functional dependency , must be a Super Key.

Difference from 3NF:

• 3NF Rule: is allowed if is a Super Key OR if is a Prime Attribute (part of a candidate key).
• BCNF Rule: is allowed ONLY if is a Super Key.

Implication: BCNF removes anomalies in tables where a prime attribute depends on a non-prime attribute (which 3NF allows).

Concurrency Control is the procedure in DBMS for managing simultaneous operations without conflicting with each other. It ensures that database transactions are performed concurrently without violating data integrity.

Need for Concurrency Control:
Without it, simultaneous transactions can lead to data inconsistency. Common problems include the Lost Update, Dirty Read, and Unrepeatable Read.

The Lost Update Problem:
Occurs when two transactions update the same data item, but one update overwrites the other.

1. Transaction T1 reads .
2. Transaction T2 reads .
3. T1 updates to and writes it.
4. T2 updates to and writes it.
5. Result: The value is 80. The addition of 50 by T1 is "lost" because T2 overwrote the value based on the old read. Correct value should be 130.

Two-Phase Locking (2PL) is a concurrency control method that guarantees serializability. It ensures that if transactions lock data items, they follow a specific protocol divided into two phases:

1. Growing Phase:

   • In this phase, a transaction may obtain locks (Shared or Exclusive) but may not release any lock.
   • The number of locks held by the transaction increases.

2. Locked Point:

   • The point where the transaction has acquired all necessary locks.

3. Shrinking Phase:

   • In this phase, a transaction may release locks but may not obtain any new locks.
   • Once a lock is released, the transaction enters the shrinking phase and cannot acquire locks again.

Benefit: 2PL ensures conflict serializability.
Drawback: It does not prevent deadlocks.

Deadlock:
A deadlock is a situation where two or more transactions are waiting indefinitely for one another to give up locks.

• Example: T1 holds Lock A and waits for Lock B. T2 holds Lock B and waits for Lock A. Neither can proceed.

Methods to Handle Deadlocks:

1. Deadlock Prevention:

   • Ensures the system never enters a deadlock state.
   • Wait-Die Scheme: Older transaction waits for younger; younger dies if it requests resource held by older.
   • Wound-Wait Scheme: Older transaction wounds (rolls back) younger transaction to get resource.

2. Deadlock Detection and Recovery:

   • The system allows deadlocks to occur but periodically checks for them.
   • Wait-for Graph: A cycle in the wait-for graph indicates a deadlock.
   • Recovery: Once detected, the system selects a Victim transaction to roll back to break the cycle.

1. Create Table Query:

sql
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
JoinDate DATE
);

2. Update Salary Query:

sql
UPDATE Employee
SET Salary = Salary * 1.10;

Serializability is the concept used to determine if a concurrent schedule of transactions produces the same result as if those transactions were executed serially (one after the other).

• Serial Schedule: Transactions are executed one by one (no interleaving). This is always consistent but inefficient.
• Concurrent Schedule: Operations of transactions are interleaved.

Goal:
A concurrent schedule is considered "correct" or Serializable if it is equivalent to some serial schedule.

Types:

1. Conflict Serializability: Determined by checking if the order of conflicting operations (Read/Write on same data item by different transactions) can be swapped without changing the result. If a schedule has no cycles in its precedence graph, it is conflict serializable.
2. View Serializability: A less strict condition based on the views (Read operations) of the transactions.

Log-Based Recovery relies on maintaining a log file (journal) that records every operation performed by transactions.

1. Deferred Database Modification (Deferred Update):

   • Updates are not written to the database on disk until the transaction reaches its commit point.
   • Log: Records [Start, T1], [Write, T1, X, NewVal], [Commit, T1].
   • Recovery: If a crash occurs before commit, no Undo is needed (changes weren't written). If after commit, Redo is performed using the log.

2. Immediate Database Modification (Immediate Update):

   • Updates are applied to the database as they occur (even before commit), but a log record is written before the database update (Write-Ahead Logging).
   • Log: Records both Old Value and New Value.
   • Recovery: If a crash occurs, uncommitted transactions are Undone (rolled back using Old Value). Committed transactions are Redone (using New Value) to ensure durability.