IT 344 SEU Physical Database Design Computer Science Worksheet

Learning Outcome(s):

LO2 Apply the concepts of transaction management, concurrency, and recovery of a database.

1. What are the important factors that influence physical database design?

2. Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below. Transaction T1: r1(x); r1(z); w1(x) Transaction T2: r2(z); r2(y); w2(z); w2(y) Transaction T3: r3(x); r3(y); w3(y) recovery of a database. Schedule S1: r1(x); r2(z); r1(x); r3(x); r3(y); w1(x); w3(y); r2(y); w2(z); w2(y) Schedule S2: r1(x); r2(z); r3(x); r1(z); r2(y); r3(y); w1(x); w2(z); w3(y); w2(y) Draw the serializability (precedence) for S1 and S2 and state whether each schedule is conflict serializable. Write down the equivalent serial schedule(s) if a schedule is serializable.

3. Explain the violations caused by each of the following: a) Dirty Read b) Non-repeatable Read c) Phantoms

4. Apply the Schedule below to determine the undo, redo, and rollback transaction concepts of transaction management, concurrency, and recovery of a database. LSN 2 Marks Log 00 UpdateT1 writes P5 05 Update T2 writes P3 10 T2 commit 15 T2 End update 20 Update T3 writes P1 25 Update: T3 writes P3 30 T4 read P5 35 Update: T4 write P5 40 T4 commit 45 T4 End update CRASH Purchase answer to see full attachment

Overview

Mastering Transaction Management, Concurrency, and Database Recovery

Databases are the backbone of modern applications, ensuring that data remains accurate, consistent, and available even when accessed by multiple users simultaneously. To achieve this, we must understand transaction management, concurrency control, and recovery mechanisms, all of which play a crucial role in maintaining the reliability of databases.

1. Key Factors Influencing Physical Database Design

When designing a database, several factors must be considered to ensure efficiency, speed, and reliability. Here are some key considerations:

• Data Access Patterns: How often is the data accessed and by how many users? Optimizing indexes and queries based on access patterns can significantly enhance performance.
• Storage & Performance Needs: Choosing between SSD and HDD, distributed databases, and cloud or on-premise solutions affects speed and scalability.
• Normalization vs. Denormalization: Striking the right balance helps minimize redundancy while ensuring fast query performance.
• Security & Access Control: Encrypting sensitive data and establishing proper user permissions prevents unauthorized access.
• Backup & Recovery Strategies: A solid backup plan ensures that data can be restored quickly in the event of a system failure or crash.

https://www.brainkart.com/article/Factors-That-Influence-Physical-Database-Design_11552/