custom writing services

Need help: Write an email to our support staff or

whatsapp us

order order
Average score 4.97 out of 5 based on 836 opinions

Database Homework Problems

James River Jewelry Project Questions for Chapter 6


The James River Jewelry database design that was used in the James River Jewelry Project Questions for

Chapter 3 in this Appendix was:

CUSTOMER (CustomerID, LastName, FirstName, Phone, Email)


PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID)


PURCHASE_ITEM (InvoiceNumber, ItemNumber, RetailPrice)


ITEM (ItemNumber, Description, Cost, ArtistName)


The referential integrity constraints are:


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

James River Jewelry has modified the database by adding two tables—OWNER and JEWELRY_ITEM—as

shown below:

OWNER (OwnerID, LastName, FirstName, Phone, Email, AmountOwed)


JEWELRY_ITEM (ItemNumber, DateReceived, DateSold, NegotiatedSalesPrice, ActualSalesPrice, CommissionPercentage, OwnerID)


OwnerID in JEWELRY_ITEM must exist in OwnerID in OWNER


ItemNumber in JEWELRY_ITEM must exist in ItemNumber in ITEM


The tables are used to record data and maintain owner data about jewelry accepted on consignment.

JEWELRY_ITEM (which is a subtype of ITEM—note 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 owners percentage of the ActualSalesPrice value. This third transaction is called a Sales Transaction.



A.              Explain why it is important for the changes made by each of these transactions to be atomic.



B.              Describe a scenario in which an update of AmountOwed could be lost.


C.              Describe a scenario for a nonrepeatable read and a scenario for a phantom read.


D.             Explain how locking could be used to prevent the lost update in your answer to part B.


E.              Is it possible for deadlock to occur between two Acceptance Transactions? Why or why not? Is it possible for deadlock to occur between two Sales Transactions? Why or why not? Is it possible for deadlock to occur between an Acceptance Transaction and a Sales Transaction? Why or why not?


F.              For each of these three types of transaction, describe whether you think optimistic or pessimistic locking would be better. Explain the reasons for your answer.


G.             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. Describe processing rights that you think would be appropriate for this situation. Use Figure 616 as an example.


H.             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 purchase and sales transactions for an entire year. If it ever loses its database, it plans to restore it from a backup and unrecorded transactions. Do you think this backup and recovery program is sufficient for James River Jewelry? What problems might occur? What alternatives exist? Describe any changes you think the company should make to this system.



Need an answer from similar question?Click here and get a solution now

order You have just landed to the most confidential, trustful essay writing service to order the paper from. We have a dedicated team of professional writers who will assist you with any kind of assignment that you have. Your work is handled confidentially with a specific writer who ensures you are satisfied. Be assured of quality, unique and price friendly task delivered in good time when you place an order at our website. banner We have a team of dedicated writers with degrees from all spheres of study. We readily accept all types of essays and we assure you of content that will meet your expectations. Our staff can be reached  via live chat, email or by phone at any given time with prompt response.  

Prices starting at:

High school
Undergrad.(yrs 1-2)
Undergrad.(yrs 3-4)
$10 / page
$10/ page
$12/ page
$15 / page
$17/ page

Note: The prices in the table above are applicable to orders completed within 14 days. Kindly see the full price table for more prices..

We urge you to provide as much information as possible to avoid many revisions. Set your deadline, choose your level, give payment information and relax while you track your work. We will deliver your paper on time. Are you in a quagmire and you are unable to complete your assignments? Do you doubt yourself on the quality of the essay you have written?  is here to assist you. We will offer you unmatched quality that is plagiarism free. Place an order at  for guaranteed  high grades. order download

we offer gifts to our paper help customersPayment Methods

you can request our services and pay via paypal

we offer gifts to our paper help customersOur Guarantees

  1. Zero plagiarism.
  2. 100% confidentiality.
  3. FREE Reference page
  4. High Quality papers
  5. 24/7 Customer support

Our Testimonials Testimonials

I ordered a 7 page college essay and was delivered on time. Thank you guys I will definitely order again!

, FL

Our statisticsOur Statistics

  • Orders Completed todate 72387

  • Number of writers 500

  • Satisfied customers 99.7%

  • Returning customers 92.3%

  • Orders placed today 87