Assignment 6.2

Suppose your database system has failed. Describe the database recovery process and the use of deferred-write and write-through techniques.

Suppose you are asked to evaluate a DBMS in terms of lock granularity and the different locking levels. Create a simple database environment in which these features would be important.

Research the Dining Philosophers problem on the Internet. Summarize what you find and explain how this classic problem relates to deadlock and concurrency control. Explain how you could prevent this problem from occurring.

There are several different kinds of locks used with databases. Using your text, the Internet, and any other sources you have available, study the concept of locking. Briefly describe each type of lock that you uncover during your studies, along with its advantages and disadvantages.

Write or find (online) SQL statements that might be used in transaction management and explain how they work. Here's an example with the answer:

The following transaction registers the credit sale of a product to a customer. Start transaction

INSERT INTO INVOICE -- Add record to invoice

(INV_NUM, INV_DATE, ACCNUM, TOTAL)

UPDATE INVENTRY -- Update the quantity on hand of the product

SET ON_HAND = ON_HAND - 100

WHERE PROD_CODE = '345TYX';

UPDATE ACCREC -- Update the customer balance account

The above statement have accomplished:

Create the invoice record.

Update the inventory data.

Update the customer account data.

The DBMS will ensure that all SQL statements are executed and completed before committing all work. If, for any reason, one or more of the SQL statements in the transaction cannot be completed, the entire transaction is aborted and the database is rolled back to its previous consistent state.

Assignment 6.3

This is a group assignment.

Make sure that your E-R diagram and your data dictionary are accurate in every detail. (entities and attributes are the same.)

Use the group E-R diagram and data dictionary from the previous two weeks to develop SQL statements that will create tables.

After your tables are created, enter a minimum of 3 sets of data for each table.

Use the SQL examples from the last two weeks and anything you can find on the Internet as examples on how to write the SQL instructions.

Attach your SQL code for creating the tables and adding data to each table to this assignment in a single text document.  Post your the ERD and Data Dictionary that you use to generate your code to your file exchange.

Note: The attached pvfc1.txt file used in week 1 is an example of what you code should look like.  Except that your code should include comments for each block of text.

Attachment:- Full_Requirement.zip