Concurrency Control and ER Model in Database Systems

Concurrency Control in RDBMS

What are Transactions?

Transactions are sets of operations (like reading or writing data) treated as a single unit. Think of transferring money: multiple steps must happen together.

Isolation Levels

RDBMS uses isolation levels to manage how transactions interact:

• Read Uncommitted: Transactions see changes before they're finalized, which is risky due to potential inaccuracies.
• Read Committed: Transactions only see finalized changes, safer but still prone to inconsistencies.
• Repeatable Read: Data remains unchanged during a transaction, preventing some issues.
• Serializable: Transactions run sequentially, avoiding all problems but potentially slowing performance.

Concurrency Control Techniques

Techniques like locking data, timestamps, or data versioning manage transaction interactions.

Conflict Resolution

The system prioritizes transactions to avoid conflicts when multiple transactions attempt to change the same data simultaneously.

Entity-Relationship (ER) Model

Entities

Entities are categories of data, like Student, Teacher, or Course in a school database.

Attributes

Attributes are characteristics of entities. For Student, attributes could be StudentID, Name, and DateOfBirth.

Relationships

Relationships show how entities connect. Student might connect to Course through an Enrolls In relationship.

Diagram Notation

Entities are boxes with attributes inside. Relationships are lines connecting boxes, labeled to describe the connection.

Purpose of the ER Model

The ER model organizes information into categories, defines properties, and clarifies relationships for database design.

ER Model Example

A social media database might have User, Post, and Comment entities. User could have UserID and Username attributes, with relationships showing how users create posts and posts receive comments.