CMSC4003

Homework6: Transactions

Due: See the due date in D2L calendar.

Requirement: Your answers must be typed and printed. You may attach hand-drawndiagrams if needed.

  1. N/A
  1. Assessment question (2extra course pts)

Consider the following EER diagram:

The EER diagram provides a conceptual model based on the data requirements for a vehicle management application. It shows a specialization that contains a super class and two subclasses. Based on the EER diagram, a relation model should be constructed as the logical design. Generally, there are two ways to design relational tables based on a specialization. One is a single-table approach, while the other uses multiple tables.

You are required to complete the following items.

a)Discuss the pros and cons of the two different approaches (single vs. multiple tables) to mapping a specialization into relational tables.

b)Decide which approach you choose for the specialization in the EER diagram and justify your choice.

c)According to your choice, provide the relational schema which is mapped from the EER diagram. Note that you do not need to provide the domains for the attributes.

  1. For each of the following schedules, decide whether it is an unrecoverable, recoverable, cascadeless or strict schedule. If a schedule fits several of the categories, then specify the strictest category. You are required to briefly explain your answer for each schedule.(18 pts)

a)r3[z], r1[y], w2[x], c2, w3[z], c3, r1[x], w1[z].

b)r1[x], w2[y], w1[x], r3[z], c1, r3[x], w3[y], c2.

c)w2[y], w1[y], r3[y], w3[y], c2, c3.

  1. Consider the following schedule:

w2[x], r1[x], r3[y], w3[y], w1[x], r2[y], w1[y]

(a) Find all the conflicting pairs in the schedule.(6 pts)

(b) Draw a serializable graph for the above schedule.(6 pts)

(c)Based on your serializable graph, decide whether the schedule is serializable or not. If yes, give the corresponding serial schedule. If no, explain the reason.(6 pts)

  1. What is the benefit of using 2-Phase Locking Protocol (2PL)? What are its problems?(10 pts)
  1. Explain what the Nonrepeatable Read phenomenon is. (6 pts)
  1. Consider the following application functions and then describe appropriate concurrency control implementation in Oracle accordingly. Your description should address the following items: isolation levels (read committed or serializable), locks (level: row/table), and long durationlock or not. The following function is an example.

A customer books a seat on a flight id x on date d. The application checks the number of passengers on the flightxon date d. If the flight has reached its capacity, then the transaction will fail. Otherwise, the reservation will complete.

Schema:

Flight (flight_id, datetime, max_seats, …), PK:(flight_id, datetime)

Reservation(flight_id, datetime, SSN),PK:(flight_id, datetime, SSN), FK: (flight_id, datetime) refs Flight

Answer: Using the Read Committed level, the application should write-lock only the record in the flight table for the flight id x on date d.The write lock should be applied only when the customer is submitting the reservation request. So the lock is not long-duration since it is not applied when the customer is searching and/or reading the details of the flight. After the lock is applied, the application should check if the flight has already reached its capacity. If true, then the transaction with the customer should be cancelled; otherwise, reservation is complete (and the lock will be released upon commit).

a)A journalist retrieves an articlefrom the database by specifying an article idx. The journalist can then edit the article and then save it back to the database.Assume that an article may have multiple authors. If another author wants to access the same article x to edit it, the request should be rejected. (16 pts)

Schema:

Article(id, title, content), PK: id

b)A user transfers money from account no. x to account no. y. Data consistency in all accounts should be maintained for such transactions.(16 pts)

Schema:

Account(acct_no, balance), PK: acct_no

c)A student wants to enroll into a sectionx. The application checks the number of students in section x. If the section is full, then the enrollment operation will not proceed. Otherwise, the student will be enrolled into the section x.(16 pts)

Schema:

Section(eid, capacity), PK: eid

Enrollment(sid, eid), PK: (sid, eid), FK: eid refs Section