PLEASE HELP

Reading: Chapter 9 (Managing multi user database systems)

Part A

Question 1

1.1Describe the Lost Update problem. Show an example.

1.2What is/are solution(s) for lost update problems.

1.3What is the problem when DBMS use lock approach? How to solve this problem

Question 2

2.1 What is the checkpoint? What is the role of checkpoint (why we need it)?

2.2 This is an exercise for your understanding to the process of database recovery.

There are 3 transactions (T1, T2, T3) since last backup. T1 finished successfully before the latest checkpoint p1. T2 finished successfully after p1. T3 was aborted after p1 due to main memory crash. Explain

1. how a DBMS deal with these transactions during the recovery process if memory crashed.
2. How DBMS does recovery if the hard drive crashed.

Question 3

Describe two phase locking (2PL) and serializability (Why it is needed, what it is, how it works).

Question 4.

Describe the ACID features/principles ofa transaction. For each feature/principle,give an example thatviolatesthis feature. Namely, discuss a situation where a principle is violated and the corresponding problem(s). For example, what situation will cause a transaction not Atomic.

Part B SQL queries

Read Study the chapter 2 comparing the differencebetweenjoin operation and subquery.Formulate the SQL statements for the following queires:

1. List name of employee workon two or more projects (use join, group byand having.)

2. List the name of employee who do not work on any project (hint: USE "IN" and sub-query)

3. List the name of project that has budget over the average budget of all project in company. (use sub-query)

4. List the name of division 'chen' does not work for.