Notes for Unit 3, Unit 4, and Unit 5 (Database Management System)
Unit 3: Database Design & Normalization
---------------------------------------
1. Functional Dependencies:
- A relationship between two attributes, typically between a primary key and other non-key
attributes.
2. Normal Forms:
- First Normal Form (1NF): Eliminate duplicate columns and ensure each column holds atomic
values.
- Second Normal Form (2NF): Remove partial dependencies (each non-key attribute must depend
on the entire primary key).
- Third Normal Form (3NF): Remove transitive dependencies (non-key attributes should only
depend on the primary key).
3. Boyce-Codd Normal Form (BCNF):
- A stricter version of 3NF where every determinant is a candidate key.
4. Inclusion Dependencies:
- Ensures that a set of values in one table is a subset of values in another.
5. Lossless Join Decomposition:
- Splitting a relation into two or more relations such that the original relation can be reconstructed
without loss of data.
6. Normalization Using FD, MVD, and JD:
- FD (Functional Dependency): Guides decomposition to achieve normalization.
- MVD (Multivalued Dependency): Used for further decomposition.
- JD (Join Dependency): Ensures lossless joins.
7. Alternative Approaches to Database Design:
- Bottom-up, top-down, and hybrid approaches.
Unit 4: Transaction Processing Concepts
---------------------------------------
1. Transaction System:
- A sequence of operations performed as a single logical unit of work.
2. Testing Serializability:
- Ensures the schedule is conflict-serializable or view-serializable to maintain database
consistency.
3. Types of Schedules:
- Serializable, non-serializable, recoverable, and non-recoverable schedules.
4. Conflict and View Serializable Schedules:
- Conflict Serializable: Ensures no conflicting operations are reordered.
- View Serializable: Ensures the final state matches a serial execution.
5. Recoverability:
- A schedule is recoverable if transactions commit only after all transactions they depend on
commit.
6. Recovery from Transaction Failures:
- Techniques like undo/redo logs and shadow paging.
7. Log-based Recovery:
- Write-ahead logging (WAL) ensures logs are saved before actual database changes.
8. Checkpoints:
- Periodic save points in logs to reduce recovery time.
9. Deadlock Handling:
- Techniques like wait-die, wound-wait, and deadlock detection.
Unit 5: Concurrency Control Techniques
--------------------------------------
1. Concurrency Control:
- Mechanisms to ensure correct execution of transactions in the presence of concurrent
operations.
2. Locking Techniques:
- Shared and exclusive locks, two-phase locking protocol (2PL).
3. Time Stamping Protocols:
- Assign timestamps to transactions to resolve conflicts.
4. Validation-based Protocols:
- Transactions go through read, validation, and write phases to ensure serializability.
5. Multiple Granularity:
- Locking at different levels (e.g., row, table, database).
6. Multi-version Schemes:
- Maintain multiple versions of data items for concurrency.
7. Recovery with Concurrent Transactions:
- Ensures atomicity and consistency during recovery.
References:
- Date C.J.: An Introduction to Database Systems, Pearson Education.
- Other recommended resources as per syllabus.
