Assignment1-fall2023-cs223

.pdf

School

University of California, Irvine *

*We aren’t endorsed by this school

Course

223

Subject

Information Systems

Date

Oct 30, 2023

Type

pdf

Pages

Uploaded by MegaMusicHornet18

Assignment 1 Due : October 25, 2023, 11:59:59PM PST on Canvas Remember, assignments should be done in groups of size 3. One submission per group. Question 1: Consider an online social network application. The database of the application consists of the following tables: ● Table 1 (User table): This table maintains users information including a unique id, name, date of birth, and email address. ● Table 2 (Friendship table): This table stores data about the friendships between users. It includes columns for the IDs of the two users involved in the friendship and a timestamp indicating when the friendship was established. ● Table 3 (Post table): This table holds information about the posts made by users. It includes fields for the post ID, the user who created the post, the content of the post, how many likes the post has, and a timestamp for when the post was created. ● Table 4 (Comment table): This table tracks comments made on posts. It includes columns for the comment ID, the user who made the comment, the post to which the comment was added, the comment text, and a timestamp. Write five transactions for this application. For each transaction, provide: ● A few lines describing the transaction. ● The pseudocode of the transaction. ● The transaction representation using b, r(), w(), c, notation. The granularity of these operations is the row level. All transactions you propose must access (read or write) more than one record, and at least one of the operations is a write. At least three of the transactions must access more than one table. Explain briefly why it is important to put the operations in a single transaction. Example (‘:x’ represents inputs): - Transaction 0: A transaction to add a friendship relation between users :1 and :2 - Pseudocode: BEGIN TRANSACTION; -- Check for existence of user with ID :1 user1_exists = SELECT COUNT(*) FROM User WHERE id = :1; -- Check for existence of user with ID :2 user2_exists = SELECT COUNT(*) FROM User WHERE id = :2; -- If both users exist, add a friendship relation IF user1_exists > 0 AND user2_exists > 0 THEN INSERT INTO Friendship(user1_id, user2_id, timestamp) VALUES(:1, :2, NOW()); ELSE ABORT TRANSACTION; END IF;

COMMIT TRANSACTION; - Transaction t representation: b_t r_t(Table User ; id :1) r_t(Table User ; id :2) w_t(Table Friendship ; insert) c_t - Why as a transaction?: Having this as a transaction ensures that a friendship is only added if both users exist (and that one of them is not deleted concurrent to adding the friendship relation). Question 2: Consider the application defined in question 1. For each one of the ACID properties, provide an example of a transaction in this application that would lead to violating one of the ACID properties and why it is incorrect behavior (i.e., provide four examples, one for each property.) Question 3: Consider the application defined in question 1. Construct an example with transactions on the application that would lead to violating isolation if interleaved. 1. Show how the interleaving would lead to incorrect behavior 2. Show how Two-Phase Locking (2PL) would prevent the incorrect behavior. 3. Show how releasing the locks before the end of the transaction (instead of the correct algorithm of 2PL to hold the locks until the end of the transaction) would still lead to incorrect behavior. Question 4: Consider the application in Question 1 and consider that the tables are distributed across different resource managers (come up with a distribution and explain it). - Show an example of a transaction that commits - using 2PC - across more than one participant. - Show a scenario with transactions that illustrate that if the prepare phase was not done (i.e., a commit message is sent without prepare messages and responses), then it would lead to incorrect behavior. - What happens if after the coordinator sends a prepare message - but before it receives the votes - it crashes? Would the transaction commit, abort, or something else? Question 5: Consider the application in Question 1. - Show an example of a transaction and how it can guarantee being recovered by using logging even if a crash happened while executing the transaction. - If the log entry is not persisted before the operation is performed, what will happen?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version