Suppose you are given the James River Jewelry database desig

Suppose you are given the James River Jewelry database design as follows: CUSTOMER ( CustomerID, LastName, FirstName, Phone, Email) PURCHASE ( InvoiceNumber, Date, PreTaxAmount, CustomerID) PURCHASE_ITEM ( InvoiceNumber , ItemNumber, RetailPrice) ITEM ( ItemNumber, Description, Cost, ArtistName) OWNER ( OwnerID, Name, Phone, Email, AmountOwed) JEWELRY_ITEM ( I temNumber, DateReceived, DateSold, NegotiatedSalesPrice, ActualSalesPrice, CommissionPercentage, OwnerID) where CustomerID in PURCHASE must exist in CustomerID in CUSTOMER InvoiceNumber in PURCHASE_ITEM must exist in InvoiceNumber in PURCHASE ItemNumber in PURCHASE_ITEM must exist in ItemNumber in ITEM OwnerID in JEWELRY_ITEM must exist in OwnerID in OWNER ItemNumber in JEWELRY_ITEM must exist in ItemNumber in ITEM The OWNER and JEWELRY_ITEM tables are used to record data and maintain owner data about jewelry accepted on consignment. JEWELRY_ITEM (which is a subtype of ITEM-n ote the referential integrity constraint) is used to record the negotiated sales price, the commission percentage, and the actual sales price for each item of consigned jewelry. Assume that office personnel at James River Jewelry use a database application to record consignment data. When an item is received on consignment, owner data are stored in OWNER if the owner is new; otherwise existing owner data are used. New ITEM and JEWELRY_ITEM rows are created. In ITEM, ItemNumber and Description are recorded, Cost is set to $0.00, and if there is an artist associated with the piece, ArtistName is entered. For JEWELRY_ITEM, data are stored for all columns except DateSold and ActualSalesPrice. James River Jewelry personnel refer to these actions as an Acceptance Transaction. Later, if the jewelry item does not sell, the NegotiatedSalesPrice and CommissionPercentage values may be reduced. This is called a Price Adjustment Transaction. Finally, when an item sells, the DateSold and ActualSalesPrice fields for the item are given values, and the AmountOwed value in OWNER is updated by increasing AmountOwed by the owner\'s percentage of the ActualSalesPrice value. This third transaction is called a Sales Transaction Suppose James River Jewelry identifies three groups of users: managers, administrative personnel, and system administrators. Suppose further that managers and administrative personnel can perform Acceptance Transactions and Sales Transactions, but only managers can perform Price Adjustment Transactions. James River Jewelry has developed the following procedure for backup and recovery. The company backs up the database from the server to a second computer on its network each night. Once a month, it copies the database to a CD and stores it at a manager’s house. It keeps paper records of all services provided for an entire year. If it ever loses its database, it plans to restore it from a backup and reprocess all service requests. 1. Which of the three application transactions must be made atomic (e.g., specify transaction boundaries) to prevent possible lost update? Why and why not? 2. Describe processing rights that you think would be appropriate for the identified three groups of company users. 3. Comment on the company’s strategy on DB backup and recovery. Describe any changes you think the company should make to its current backup and recovery scheme.

Solution

Answer

According to the given description, the application has the below transactions:

1. Acceptance Transaction

2. Price Adjustment Transaction

3. Sales Transaction

Among these, acceptance transaction and price adjustment transaction can be made atomic tp prevent possible lost update as acceptance transaction happens only once at the time of acceptance of the jewelry and price adjustment transaction happens only when the jewelry item does not sell, which may take long time, so these two don\'t need an update in short duration. But sales transaction cannot be atomic because it needs update very frequently so it needs to be connected to the server or paper track in order to keep in check that all the updates are happening correctly.

Possible processing rights for the below users:
managers:can perform Acceptance, Sales and Price Adjustment Transactions. In order to perform Sales Transaction the manager can have the right to act on his own but in order to perform acceptance and price adjustment transaction he will need to take concent from the administration, though he along can make changes for price adjustment.
administrative personnel: can perform Acceptance and Sales Transaction. Though administration is above manager, it will also have the right for acceptance and sales transaction and can perform these transaction in the absence of manager, in order to avoid ambiguity.
system administrators: (not given in the question description) Assuming that this user looks over the proper functioning of system and servers.

If you are thinking of DB backup and recovery, then backing up the database from the server to a second computer on its network each night and copying it to a CD and storing it a manager\'s house sounds pretty OK. And also the company keeps paper records of all services provided for an entire year which I think is not necessary as CDs are already doing this job.

I think keeping a paper record is un-necessary, it is just being too cautious in the part of data loss and they must focus more on data protection and security.