Answered Essay: Suppose you are given the James River Jewelry database design as follows:

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)


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.

Expert Answer


First of all, it the fundamental property of a transaction that it should be atomic and thus all the three must be atomic.

Also all the three transactions should be atomic as:

  1. In Acceptance Transaction, the newly added item’s price should be updated in all the tables of the database, so as to make it available for the customers to choose.
  2. In Price Adjustment Transaction, the manager whenever changes the price of any item, it should be depicted in the main database.
  3. In Sales Transaction, the owner and the saler must have the date and the amount related information of the item sold.

Thus they should be made atomic, to have proper changes throughout the scenario.

Processing rights of:

  1. System Administrator: It has the right to make changes in the overall database schema or meta data of the database;
  2. Administrative Personnel: It has the right to administor the functioning and the transactions of the type, acceptance and sales transaction;
  3. Manager: It has the right to view the overall functioning and all the rights of administrative personnel along with the price related changes int Price adjustment transaction.

On an whole the company’s strategy is good, but it should have to use other means of storing data as CDs have less life expectancy then various other cheaper means of storage like metallic disk.

Buy Essay
Calculate your paper price
Pages (550 words)
Approximate price: -

Help Me Write My Essay - Reasons:

Best Online Essay Writing Service

We strive to give our customers the best online essay writing experience. We Make sure essays are submitted on time and all the instructions are followed.

Our Writers are Experienced and Professional

Our essay writing service is founded on professional writers who are on stand by to help you any time.

Free Revision Fo all Essays

Sometimes you may require our writers to add on a point to make your essay as customised as possible, we will give you unlimited times to do this. And we will do it for free.

Timely Essay(s)

We understand the frustrations that comes with late essays and our writers are extra careful to not violate this term. Our support team is always engauging our writers to help you have your essay ahead of time.

Customised Essays &100% Confidential

Our Online writing Service has zero torelance for plagiarised papers. We have plagiarism checking tool that generate plagiarism reports just to make sure you are satisfied.

24/7 Customer Support

Our agents are ready to help you around the clock. Please feel free to reach out and enquire about anything.

Try it now!

Calculate the price of your order

Total price:

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.


Let us write that nagging essay.


Submit Your Essay/Homework Instructions

By clicking on the "PLACE ORDER" button, tell us your requires. Be precise for an accurate customised essay. You may also upload any reading materials where applicable.


Pick A & Writer

Our ordering form will provide you with a list of writers and their feedbacks. At step 2, its time select a writer. Our online agents are on stand by to help you just in case.


Editing (OUR PART)

At this stage, our editor will go through your essay and make sure your writer did meet all the instructions.


Receive your Paper

After Editing, your paper will be sent to you via email.

× How can I help you?