USA: +1 (725) 712-1730 
Sign in
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.
A) Which of the three application transactions must be made atomic (e.g., specify transaction boundaries)to prevent possible lost update? Why and why not?
B) Describe processing rights that you think would be appropriate for the identified three groups of company users.
C) 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
Question 1
Atomic Transaction
In database, atomicity is one of the ACID transaction properties. An atomic transaction is the one which is an indivisible and irreducible series of database operations. In this either all transactions are reflected in database or none are reflected. Thus it gives a guarantee that updates made to database are never partial.
James River Jewelry has three transaction. They are Acceptance transaction, Price Adjustment transaction and Sales transaction.
- The Acceptance transaction modifies the table’s
- OWNER ( OwnerID, Name, Phone, Email, AmountOwed)
- ITEM ( ItemNumber, Description, Cost, ArtistName)
- JEWELRY_ITEM ( I temNumber, DateReceived, DateSold, NegotiatedSalesPrice, ActualSalesPrice, CommissionPercentage, OwnerID)
Since Acceptance transaction affects or modifies data values in three tables as listed above, it should be atomic. It means that either data in all tables get updated or no tables are updated.
- The Price Adjustment transaction modifies the table
- JEWELRY_ITEM ( I temNumber, DateReceived, DateSold, NegotiatedSalesPrice, ActualSalesPrice, CommissionPercentage, OwnerID)
It is clearly seen that this transaction affects only one table and hence it need not be atomic. This transaction happens only when an item is not sold.
- The Sales transaction modifies the tables
- OWNER ( OwnerID, Name, Phone, Email, AmountOwed)
- ITEM ( ItemNumber, Description, Cost, ArtistName)
The sales transaction works when an item is sold. It modifies values in two tables as listed above. Thus, sales transaction has to be kept atomic so that changes are shown in all tables or not in any.
Question 2
Processing Rights
These rights and responsibilities are assigned to user by organizational policies. The policies clearly suggest which group of users can take what action on a data collection. They are not imposed by any database application but are explained or assigned to user during systems training. The users of James River Jewelry are:
- Manager
As the deciding person, a Manager can have access to all the transaction and tables in the James River Jewelry. All database write operation can be done through a manager thus following all the ACID properties which very essential.
- Administrative Personnel
They have very limited access to the system. They are responsible for the acceptance and sales transaction.
- System Administrator
A system administrator or database administrator (DBAs) is responsible for configuration, performance monitoring, security, troubleshooting, backup and recovery. They are responsible for installing and upgrading the database server and application tools. This person can control and monitor all the other users’ access to the database. They are also responsible for optimizing the database, managing and monitoring data replication. They can also look onto overall network performance and hardware.
Question 3
Database Backup and Recovery
The purpose of creating a Server backups is to enable you to recover a damaged or lost database. A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements
James River Jewelry currently 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 there is any data lose, it plans to restore it from a backup and reprocess all service requests.
This backup and recovery procedure looks good, especially the existing process of backing up data on a network computer every night. It is an ideal solution to prevent data lose and incurring inconsistency in data. It is also a good approach to keep the paper record for an entire year. Moreover, keeping more than one method of backup (CD and paper) can make recovery more easy and reliable.
The only issue found in this backup plan is that it takes a CD backup only once a month. Since it a business which has lots of transaction every day, backing up to CD can be done every week.
